エクセルで封筒の宛名印刷

様式フォーマット ,

e2-1.png
手書きの方がアジがあり、ぬくもりがありますので手書きしましょう。
と冒頭に言ってしまいましたが、やはり手間。数100社の宛先をすべて手書きで書くなんて腱鞘炎になりそう。住所録をエクセルデータに出来るのであればエクセルで封筒枠を作って印刷すればあっという間です。
記事最後にファイルダウンロードが可能です。

住所リスト

まずは印刷する宛先のリストを作ります。A列にはNoを入れておきましょう。
e2-2.png
住所が長く折り返したい場合、E列に住所2という形で分けてもいいでしょう。
この様式で作りなさい!という事ではなく、説明用のリストなのでB列に郵便番号が来たりしても全然問題ありません。あなたが持ってるリストをそのまま使ってください。

封筒設定

住所リストとは別シートに封筒ページを作ります。ページ設定で用紙サイズを指定の封筒にします。封筒サイズ一覧を参考に。
※また、この用紙サイズ設定はご使用のプリンタに依存するため封筒サイズが無い場合もあります。ユーザー定義による用紙サイズがあるのであれば、封筒サイズ一覧のサイズに定義して設定してください。
もしユーザー定義設定も無く、封筒用紙サイズも無いのであれば・・・・ここでの説明は終わりです。他の方法を検索してください。
e2-3.png
で、枠幅調整、郵便番号枠追加、住所・宛名のセル結合、縦書き、と書式を変更する。
e2-4.png

宛名・住所表示

住所枠の表示の前に、フォームコントロールでコンボボックスを付けます。
e2-5.png
コンボボックスの書式設定で入力範囲を住所リストの社名を指定します。この説明ですと住所リストシートのB2:B5(B5までじゃなくもっと下のほうまで設定したほうが追加しやすい)。
また、リンクするセルを仮にここではR4セルに指定しておきます。
e2-6.png
このコンボボックス、○○株式会社を選択するとリンクするセル(R4)が「1」になり、△●▽商店を選択すると「2」になります。
そのリンクするセル(R4)を使って住所表示するセルにVLOOKUPで計算式
=VLOOKUP(R4,住所リスト!A2:D16,4,0)
宛名・社名を表示するセルには
=VLOOKUP(R4,住所リスト!A2:D16,2,0)
郵便番号は
左の桁から

=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),1,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),2,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),3,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),4,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),5,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),6,1)
=MID(VLOOKUP(R4,住所リスト!A2:D16,3,0),7,1)

 

と、一つ一つのセルに計算式を入れます。
VLOOKUPで求めた郵便番号7ケタをMIDで一文字ずつ切り取ってるだけです。

最後に

最後に各々のプリンタの設定が必要になるかと思います。私のプリンタ(NEC MultiWriter8600N)では手差し給紙に封筒を、ケツから入れる為、180°回転設定をしないとま逆に印刷されてしまいます。
しかも入れる封筒が糊付きの為にローラーの熱で糊が溶け、ローラーに巻きついてしまうこともしばしば・・・^^;
これで故障したら保障してもらえないんだろうなーと思いつつ使ってます。
あ、それとコンボボックスや郵便番号枠は印刷しない設定にしておきましょう。
e2-7.png

ダウンロード


e2-8.png

 

Comment

  1. ヒロセトシアキ より:

    教えて下さい

  2. 武井哲也 より:

    こんにちは、エクセルで宛名印刷見せて頂いております。使いやすそうなので、同じ物を作ろうと思いまして、本を買ってきて勉強して挑戦してみましたがうまく動きません。形は出来ましたが動きません。残念です。何かヒントをいただけますか? よろしくお願いします。

  3. メディケア・くれ より:

    はじめまして。
    仕事でこちらのファイルを利用させてもらっております。
    リストが多くなり、ただいま84行まで使っているのですが、
    まぜか84行目以降が封筒画面に反映されません。
    数式は例えば住所のところは
     VLOOKUP(M4,リスト!$A$2:$G$102,6,0)
    のようにしています。
    どうやれば問題が解決できるか教えてください。
    よろしくお願いいたします。

  4. Isakat より:

    リストのA列(No欄)は入っていますか?数字がダブっていませんか?
    リストのF列(6列目)に住所は入っていますか?
    83行目までは普通に表示出来てるとの事なので、封筒画面Sheetの間違いでは無さそうなのですが・・。

  5. 困ったさん より:

    はじめまして。仕事で活用させて頂きたいと思っております。
    さて、上記にて
    「リンクするセルを仮にここではR4セルに指定しておきます。」
    とあるのですが、実際に使用する際 "R4"の箇所は
    何にあたるのでしょうか?
    初歩的な質問で恐縮ですが、ご回答頂ければ幸いです。

  6. isakat -管理人- より:

    はじめまして。困ったさん。
    R4というのは画像でも解るように、封筒の枠と全く関係のない箇所です。
    とりあえずで、コンボボックスの結果を吐き出す為だけのセル。と考えてくだい。
    ・リストからコンボボックスに表示させる。
    ・コンボボックスでどの取引先を選んでいるかをR4に行数で吐き出す。
    ・郵便番号や、社名はR4の行数とVlookupでリストから探しだす。
    こんな感じです。
    別にR4じゃなくても、印刷枠に入ってなければドコでも良いのです。

  7. 困ったさん より:

    遅くなりまして…
    ご回答の頂き、ありがとうございます。
    なるほど~。
    頭が硬くてダメですね…
    結果を表示するための場所ですね!
    ご伝授頂い様に、印刷範囲外にセルを設定して
    問題解決出来ました。
    ご丁寧に、ありがとうございました。

  8. エクセル初心者 より:

    初めまして。
    仕事で使わせて頂こうと思っています。
    初歩的な質問ですみません。
    一度にいっぺんに印刷するにはどうすればいいですか?
    現在、ドロップダウンを一件ずつ開いて、印刷しているのですが、手間がかかります…。
    何か方法があるんでしょうか??

  9. エクセル初心者 より:

    先程、一度に印刷する方法を質問した「エクセル初心者」です。
    メールアドレスが間違っておりました。
    正しくは「y-umehara@marutake1.co.jp」です。
    もしよろしければ、お答えいただけたら嬉しいです。
    宜しくお願い致します。

  10. 事務員 より:

    はじめまして
    さっそく封書雛形を作成してみました。
    今後活用させていただきます。
    ところで 御中はどのように記入しましたか?
    普通に社名のセルの後ろに御中と入力しておくと
    長い社名の場合重なってしまいます。
    ご回答頂ければ幸いです。

  11. Isakat より:

    御中は、社名のセルの計算式最後に、&"御中" で長い社名でも大丈夫です。

  12. 事務員 より:

    初歩的な疑問ですみませんでした。
    大丈夫でした。ありがとうございました。

  13. 匿名 より:

    はじめまして。
    仕事でこちらのファイルを利用させていただいております。
    質問ですが、リストに登録した複数の住所を一括で印刷することは
    可能でしょうか?

  14. どしろうと より:

    数百の住所録を一気に宛名印刷しなければならず、大変助かりました。
    ところで、コンボボックスで選択でき、1つ1つは印刷できるのですが、一斉に印刷するには、どのようにすればよいのでしょうか?どうぞよろしくお願い申し上げます。

  15. 現場作業員 より:

    ダウンロードさせて頂きました。
    わかりやすくてとても助かります。
    ありがとうございました。

  16. and より:

    素晴らしいツールをありがとうございます。
    コンボボックスやVLOOKUP関数の勉強ができました。
    ありがとうございます。

  17. モモ より:

    私学で働いているものです。
    生徒名で大量に用意することが多いので活用させて頂きたいと思っております。
    印刷に関してですが、マクロ実行しますとプレビューがでてきます。
    一枚づつ印刷実行を押している状態なのですが連続で印刷するには
    どのようにしたらよろしいでしょうか。
    ご教授くださいーお願いしますっ。

  18. isakat より:

    >モモ様
    印刷マクロはそちらで用意されたものでしょうか?
    印刷プレビューが出てしまうのは
    Worksheets("封筒").PrintPreview
    というコードがあるからだと思われます。
    これを削除し、
    Worksheets("封筒").PrintOut
    に変えてしまえば印刷プレビュー無しで即印刷出来るはずです。
    また、連続で全リストを印刷するのはDo Until  ~ Loopを使います。
    下記コードでリスト全件印刷が実行されます。

    i = 2
    Do Until Sheets("リスト").Cells(i,1) = ""
        Sheets("封筒").Cells(4,"R") = i - 1
        Worksheets("封筒").PrintOut
        i = i + 1
    Loop
  19. Kana より:

    はじめまして。
    ファイルダウンロードさせていただきました。
    質問なんですが、住所リストの郵便番号の最初が「0」だと
    ひとつずれて表示されてしまうんですがどうすればいいでしょうか?
    よろしくお願いします。

  20. 匿名 より:

    有用なファイルを提供して下さり、ありがとうございます。
    こちらのファイルに関して質問ですが、非表示の列(P列以降)はどのように表示すればよいのでしょうか?
    とても初歩的な質問でお恥ずかしいですが、宜しくお願い致します。

  21. masa より:

    はじめまして。とても参考になる術でしたのでうれしかったです。
    それで、以前に「どしろうと」様が質問されているのですが、私も同じことで悩んでいるため、教えていただけるとうれしいです。
    【質問内容】
    コンボボックスで選択でき、1つ1つは印刷できるのですが、一斉に印刷するには、どのようにすればよいのでしょうか?
    順番に手作業で行ったため、後からファイルダウンロードを知りました。
    自分でエクセルで作ってみたため、この設定がわかりません。
    お手数ですがご回答をよろしくお願いいたします。

    • 初心者 より:

      お返事いただけますと幸いです。。。
      エクセル初心者の者です。
      本当にお恥ずかしいのですが、封筒長3にエクセルで作成した住所録を一括印刷したいのですが、、住所が長くE4を使用したいのです。
      ご教示頂けますと大変大変助かります。どうぞ宜しくお願いいたします。

  22. Ken より:

    とても重宝しています。ありがとうございます。
    全て書式を入力した後、プリンターで複数の宛名を印刷する場合、どうしたら良いのでしょうか?
    印刷のページ設定では1人分しか印刷できません。

  23. 総務課 より:

    はじめまして。仕事で利用させていただいております。
    質問なんですが、こちらのファイルで色んなサイズの封筒を設定したいのですが、うまくいきません。
    横書き用にした時にセルが足りず範囲を指定できない(見切れてしまう)のですが、どのようにしたら良いでしょうか?

  24. 管理人 より:

    > はじめまして。仕事で利用させていただいております。
    > 質問なんですが、こちらのファイルで色んなサイズの封筒を設定したいのですが、うまくいきません。
    > 横書き用にした時にセルが足りず範囲を指定できない(見切れてしまう)のですが、どのようにしたら良いでしょうか?
    セルが足りないという事は、非表示にしているセルを表示していないのでしょうか?
    セルの全選択をし、A列のあたりで右クリックし、再表示 を選んでセルを表示してください。

  25. 総務課 より:

    早急のご対応ありがとうございます。
    ご指示の通り、再表示をやってみましたが変化がありません。
    列はO列までで、行は39行までしか表示されていません。
    他はグレーになって選択できない状態です。
    印刷範囲でも上記内しか選択できません・・・。

  26. isakat より:

    > 早急のご対応ありがとうございます。
    > ご指示の通り、再表示をやってみましたが変化がありません。
    > 列はO列までで、行は39行までしか表示されていません。
    > 他はグレーになって選択できない状態です。
    > 印刷範囲でも上記内しか選択できません・・・。
    すいません、確かに列の再表示で出なかったですね。(行のほうは出ましたが)
    こちらを試してみてください。
    O列の幅調整をするところへマウスを持っていく。
    ←|→ というポインタになりますが、
    ←||→ こういうポインタになるところを探してください。
    その状態で幅を広げようとする隠れた列が現れるはずです。

  27. 総務課 より:

    ←|→と↓は表示されるのですが、←||→は現れてくれません。
    O列以降が完全にないような感じなんですが、なにか設定でそうなっているのでしょうか?(表現が下手ですみません)

  28. isakat より:

    > ←|→と↓は表示されるのですが、←||→は現れてくれません。
    > O列以降が完全にないような感じなんですが、なにか設定でそうなっているのでしょうか?(表現が下手ですみません)
    特別な設定は行なってません。O列以降の非表示を行なっているだけです。
    再表示、もしくは ←||→ で出せるはずです。こちらの環境では出せました。
    どうしても出てこないようであれば、こちらで出したExcelファイルをメールで送付させて頂きますが?
    その場合、「管理者にだけ表示を許可する」にチェックを入れてメールアドレスを記載してください。

  29. 総務課 より:

    ご親切にありがとうございます。
    メールアドレスを記載いたします。

  30. 名無し より:

    ダウンロードをしたものですが、あて先が会社ではないので「御中」を外したいのですが、方法を教えてください。お願いします。

  31. 管理人(isakat) より:

    > ダウンロードをしたものですが、あて先が会社ではないので「御中」を外したいのですが、方法を教えてください。お願いします。
    封筒画面の「なし」のボタンを押せば御中を外せます。

  32. hirotomo sakimura より:

    ダウンロードしましたが、どうしてもO列から先が表示しません。
    お手数ですが表示したものを送付してもらえないでしょうか??
    よろしくお願いします。
    Re: Re: エクセルで封筒の宛名印刷
    > ←|→と↓は表示されるのですが、←||→は現れてくれません。
    > O列以降が完全にないような感じなんですが、なにか設定でそうなっているのでしょうか?(表現が下手ですみません)
    特別な設定は行なってません。O列以降の非表示を行なっているだけです。
    再表示、もしくは ←||→ で出せるはずです。こちらの環境では出せました。
    どうしても出てこないようであれば、こちらで出したExcelファイルをメールで送付させて頂きますが?
    その場合、「管理者にだけ表示を許可する」にチェックを入れてメールアドレスを記載してください。

  33. 名無し より:

    はじめまして。
    初歩てきなのですが、リストの住所に住所を入力する際に
    1丁目2‐3のようにハイフンが封筒には横書きになてしまいます。
    縦書きにするにはどのようにしたらよろしいでしようか

  34. 管理人 より:

    半角ハイフンを使ってはどうでしょうか?

    全角と半角で縦書の扱いが違ったりします。

  35. 名無し より:

    >1丁目2‐3のようにハイフンが封筒には横書きになてしまいます。
    >縦書きにするにはどのようにしたらよろしいでしようか
    半角ハイフンを使っても封筒には横書き全角で表示されるようです。
    私の表示形式が何か違っているのでしょうか。

  36. 管理人 より:

    半角ハイフンでも出来なかったですね、すいません。

    これならばいけるはずです。
    「だっしゅ」で変換で出てきます、もしくは「ー」の変換をする。

  37. 名無し より:

    >1丁目2‐3のようにハイフンが封筒には横書きになてしまいます。
    >縦書きにするにはどのようにしたらよろしいでしようか
    「だっしゅ」で変換でできました。
    ありがとうございました。

  38. hiro より:

    上記までの事例解説非常に参考になりました。
    封筒のExcelファイルの、枠幅調整、郵便番号枠追加、等の設定についてご教示頂けないでしょうか? また上記ファイルのページ設定のサイズはA5で作成していると思いますが(理由?)、エプソンのプリンターに封筒をセットしたら、セット用紙が違うとのことで印刷できないのですが? 以上申し訳ありませんがお願いいたします。

  39. 管理人 より:

    枠幅調整・枠追加などは列幅の調整などで簡単に編集出来ると思います。
    ページ設定A5の件ですが、
    これはご使用のプリンタに依存しますね。
    私の環境では用紙サイズは「長形3号封筒(120x235mm)となっています。
    これは私のパソコンもしくはプリンタにその用紙サイズの情報があるからです。
    エプソンの家庭用プリンタ等でしたら、もしかしたらこの用紙サイズが情報として無いのかもしれません。
    不定形サイズというのがもし有るようでしたら、そこを手動で120x235mmに変えればいけるかもしれません。ただこれは各プリンタの状況に寄るのでなんとも言えません。

  40. hiro より:

    早速ありがとうございました。
    上記封筒印刷エクセルファイルの件ですが、書式設定し定位置に印刷するため(今回は郵便番号枠)の方法についてもう少し解説の願いできないでしょうか。

  41. 管理人 より:

    枠調整して封筒の既定郵便番号位置をズラすという方法は、
    その郵便封筒にもよりますので、大変かとは思いますが
    何回も印刷を繰り返して位置調整するのがてっとり早いかと。
    おおよそですがエクセルの行列1ピクセル=約0.35mmなので実寸測って調整という方法もありますが、やはりこれもプリンタとの調整が必要なので、何回も印刷繰り返し微調整が必要でしょう。

  42. hiro より:

    郵便番号の枠の調整はお陰様でできました。色々ありがとうございました。会社では提供されたファイルはダウンロードができないため(セキュリティの問題で)各所の関数等の設定について理解したいため下記設定についてご教示お願いいたします。
    ①・様 ・御中・ なしの設定について、解説では&”様”を追加すると記載ありますが設定している場所?セルがわからないのですが?
    ②宛先の選択コンボボックスの設定方法、様、御中等の選択チェックボックスの作成方法等について、いろいろ申し訳ありませがよろしくお願いいたします。

  43. 教えて下さい より:

    このような便利なツールを公開していただきありがとうございます。
    データを貼り付けて、用意されてるマス?セル?(B16)16行目
    までは封筒印刷が可能ですが、17行目からあて先を追加した場合、
    封筒画面に会社名や住所が反映されません。
    計算式のどの値を変えれば追加したデータを反映させれますか?
    初歩的なのでしょうが宜しくお願い致します。

  44. 管理人 より:

    >教えて下さい さん
    計算式をイジったり、コンボボックス(リストから選ぶやつ)のプロパティをイジれば出来るんですが、
    それより初心者向きな追加方法は、
    「リストSheetの15行目と16行目の間に行を挿入する」です。
    具体的には16行目(用意されているリストの一番下行)を右クリックし、「挿入(I)」を押します。
    すると1行増えますので、これで追加可能です。もっと増やしたい場合も同じく行の挿入で可能です。
    理屈は、1~16行目のどこでも良いので、その間で挿入することで、計算式やコンボボックスの設定が自動的に1行ズレてくれる為です。

  45. 嶋田 より:

    教えてください。郵便番号が全く枠には入らないのですが、どうするばよいのでしょうか。

    • 管理人 より:

      全く枠に入らないというのは、
      ・郵便番号の数字が全く表示されないのか
      ・表示上、数字が枠からズレるのか
      ・印刷時に封筒の郵便番号枠からズレるのか
      ・または全く別の問題なのか
      どれでしょうか?

      • sano より:

        お世話になってます。ファイルをダウンロードさせて頂きました。郵便番号の枠を、自社の封筒の枠に合わせたいのですが、どのように合わせたらよいでしょうか?合わせ方がわかりません。会社の郵便番号の枠は右端によって印刷されてます。

  46. なんだ朗 より:

    お世話になります。
    宛先の御中、様を△●▽商店の後に若干空間を空けて記入したいのですがR7,R8の御中、様の前に半角のスペースを挿入しても結果としては全角のスペース分空いてしまいます。
    半角くらいの空間を設けるにはどのようにしたらよろしいでしようか?宛先のフォントはMS-P明朝にしてあります。

    • isakat より:

      縦書きの場合、半角であろうとも全角分の空間を使います。
      半角であろうが全角であろうが縦サイズは1文字分だからです。
      A(半角)とA(全角)で考えて頂きたいですが、たしかに横のサイズは半角のほうが半分ですが、縦サイズは同じですよね。
      縦書きだと以下のようになるので、空白も1文字分の空間を使うことになります。
      A A
      B B
      C C

      解決策としては縦書きを使用しないという方法か、
      御中の前のスペースの文字サイズを小さくする方法くらいですかね。

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

図形 INDEX 置換 フロー図 TODAY COLUMN グラフ ユーザー定義関数 ワイルドカード テンプレート COUNTIF 作業効率UP NA() HLOOKUP 検索 バーコード DATE XLSTART 日付 MATCH 無料 SUMIF 目標 SUM VLOOKUP コピペ 条件付き書式 印刷 SUMIFS 変換
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑