Excelで作成している送付状に簡単に相手先の情報を呼び出す方法につい

質問

Excelで作成している送付状に簡単に相手先の情報を呼び出す方法について教えてください。

社内で使える送付状をExcelで作ろうとしています。
VBAは使わずに、一般の機能で作成したいです。

宛名のセルで相手先の会社名や担当者名を選択したら
自動的に電話番号とFAX番号が表示されるようにしたいと思っています。

宛先は別シートで管理して、数式をいじらずに宛先や名前を
変更・追加できるようにしたいです。

お知恵をお貸し下さい。
よろしくお願いします。

回答No.5

kagakusuki

回答番号:ANo.3，4です。

　それから、御質問内容からは少し外れますが、Sheet2に重複したデータが入力される事を避けるために、以下の操作を行って、Sheet2の全セルに入力規則と条件付き書式を設定しておくのも良いかも知れません。

　まず、Sheet3のH2セルに次の数式を入力して下さい。

=IF(AND($G2<>"",COUNTIF($G:$G,$G2)>1),ROW(),"")

　次に、Sheet3のH2セルをコピーして、同じ列の3行目以下に、Sheet2でデータを入力する予定の行数を、上回るのに充分な行数になるまで貼り付けて下さい。
　次に、Sheet2でデータを入力する全ての列を選択してから、以下の操作を行って下さい。

メニューの[データ]をクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリックして、現れた選択肢の中にある「ユーザー設定」をクリック
　　↓
「数式」欄をクリックしてから、「数式」欄に次の数式を入力

=COUNTIF(INDIRECT("Sheet3!H:H"),"="&ROW())=0

　　↓
「データの入力規則」ウィンドウの「エラーメッセージ」タグをクリック
　　↓
「スタイル」欄が「停止」となっている事を確認
　　↓
「タイトル」欄に
「データの重複」
と入力
　　↓
「エラーメッセージ」欄に
「同じ会社に所属している同じ名前の担当者のデータが既に入力されています」
と入力
　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ウィンドウの右端の欄に次の数式を入力

=COUNTIF(INDIRECT("Sheet3!H:H"),"="&ROW())

　　↓

「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
　　↓
赤色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック

　これで、同じ会社名で、且つ同じ担当者名のデータを、キーボード入力で入力しようとしても、エラーメッセージが表示されて、入力が拒否されます。
　又、セルのコピー＆ペーストでデータが重複する行が作成された場合には、重複したデータが存在する行のセルの色が赤くなりますから、重複データである事が判別出来る様になります。

回答No.4

kagakusuki

回答番号:ANo.3の続きです。

　次に、Sheet1のC2セルをコピーして、Sheet1のD2セルとE2セルに貼り付けて下さい。
　次に、以下の操作を行って下さい。

Sheet1のA2セルを選択
　　↓
メニューの[データ]をクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリックして、現れた選択肢の中にある「リスト」をクリック
　　↓
「元の値」欄をクリックしてから、「元の値」欄に次の数式を入力する（数式中の"゜"は半濁点です）

=INDIRECT("Sheet3!B1:B"&MATCH("゜",INDIRECT("Sheet3!B:B"),-1))

　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

　次に、Sheet1のB2セルを選択してから、同様の操作を行って、「元の値」欄に次の数式を入力してから、「データの入力規則」ウィンドウの[OK]ボタンをクリックして下さい。

=IF($A2="","",INDIRECT("Sheet3!F"&VLOOKUP($A2,INDIRECT("Sheet3!B:D"),2,0)&":F"&VLOOKUP($A2,INDIRECT("Sheet3!B:D"),3,0)))

　後は、Sheet2の2行目以下に、各項目毎のデータを入力しておき、Sheet1のA2セルとB2セルのドロップダウンリストを使用して、会社と担当者を指定すると、C2～E2に、住所、TEL番号、FAX番号が表示されます。
　尚、Sheet2に関しては、項目名が1行目無かったり、項目名の「会社」と「担当者」を別の名称（例えば「会社名」）に変えたりしない限りは、レイアウトは自由で、セルや行、列のコピー、切り取り、貼り付け、挿入も自由です。
　又、Sheet2の適当な列に新しい項目を設けてから、Sheet1のC列をコピーして、別の列に貼り付けて、コピー先の列の項目名を、Sheet2の新規項目名に書き換えれば、別のデータも表示させる事が出来ます。
　それから、Sheet1のA2～E2の範囲をコピーして、3行目以下に張り付ければ、複数の担当者のデータを、同時に表示させる事も可能です。

回答No.3

kagakusuki

今仮にSheet2で宛先の管理を行い、Sheet3のA列～G列を作業列として使用し、Sheet1で宛先の入力と検索結果の表示を行うものとします。
　まず、Sheet2の1行目のセルに、

会社
担当者
住所
TEL
FAX

等の各項目名を、１つずつ入力して下さい。
　この時、どの列に、どの項目名を入力されても構いませんし、後で別の列に移動されても構いませんが、必ず1行目に入力して下さい。

　次に、Sheet3のA1セルに次の数式を入力して下さい。

=IF(COUNTIF(OFFSET(INDIRECT("Sheet2!A2:A"&ROWS($1:2)),,MATCH("会社",Sheet2!$1:$1,0)-1),INDIRECT("Sheet2!R"&ROWS($1:2)&"C"&MATCH("会社",Sheet2!$1:$1,0),0))=1,ROWS($1:2),"")

　続いて、Sheet3のA1セルをコピーして、同じ列の2行目以下に、Sheet2でデータを入力する予定の行数を、上回るのに充分な行数になるまで貼り付けて下さい。
　次に、Sheet3のB1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($A:$A),"",INDIRECT("Sheet2!R"&SMALL($A:$A,ROWS($1:1))&"C"&MATCH("会社",Sheet2!$1:$1,0),0))

　次に、Sheet3のE1セルに次の数式を入力して下さい。

=IF(OR(INDIRECT("Sheet2!R"&ROWS($1:2)&"C"&MATCH("会社",Sheet2!$1:$1,0),0)="",INDIRECT("Sheet2!R"&ROWS($1:2)&"C"&MATCH("担当者",Sheet2!$1:$1,0),0)=""),"",MATCH(INDIRECT("Sheet2!R"&ROWS($1:2)&"C"&MATCH("会社",Sheet2!$1:$1,0),0),$B:$B,0)*100000+ROWS($1:2))

　次に、Sheet3のC1セルに次の数式を入力して下さい。

=IF($B1="","",COUNTIF($E:$E,"<"&MATCH($B1,$B:$B,0)*100000)+1)

　次に、Sheet3のD1セルに次の数式を入力して下さい。

=IF($B1="","",COUNTIF($E:$E,"<"&(MATCH($B1,$B:$B,0)+1)*100000))

　次に、Sheet3のB1～D1の範囲をコピーして、同じ列の2行目以下に、リストに入力予定の会社の数を、上回るのに充分な行数になるまで貼り付けて下さい。
　次に、Sheet3のF1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($E:$E),"",INDIRECT("Sheet2!R"&MOD(SMALL($E:$E,ROWS($1:1)),100000)&"C"&MATCH("担当者",Sheet2!$1:$1,0),0))

　次に、Sheet3のG2セルに次の数式を入力して下さい。

=INDIRECT("Sheet2!R"&ROWS($1:1)&"C"&MATCH("会社",Sheet2!$1:$1,0),0)&INDIRECT("Sheet2!R"&ROWS($1:1)&"C"&MATCH("担当者",Sheet2!$1:$1,0),0)

　次に、Sheet3のE1～G1の範囲をコピーして、同じ列の2行目以下に、Sheet2でデータを入力する予定の行数を、上回るのに充分な行数になるまで貼り付けて下さい。

　次に、Sheet1の
A1セルに　　会社
B1セルに　　担当者
C1セルに　　住所
D1セルに　　TEL
E1セルに　　FAX

と入力して下さい。
　次に、Sheet1のC2セルに次の数式を入力して下さい。

=IF(OR($A2="",$B2=""),"",IF(COUNTIF(Sheet3!$G:$G,$A2&$B2)=0,"該当無し",INDIRECT("Sheet2!R"&MATCH($A2&$B2,Sheet3!$G:$G,0)&"C"&MATCH(C$1,Sheet2!$1:$1,0),0)&""))

※Sheet2における編集を自由に行える様にするために、内容が複雑になり、入力可能な文字数を超えるため、規約違反になりますが、続きは次の回答に記させて頂きます。

回答No.2

imogasi

回答に色々制約をかけると、かえってむつかしくなったり、出来なかったりする。
エクセルをもっと勉強して「出来るか出来ないか」自分で判断できるようになること。
＞数式をいじらずに宛先や名前を変更・追加できるようにしたいです
今回はそれが必要ないが、式の複写・最大行の増加などはやらざるをえないことが多い。
ーー
VLOOKUP関数をつかえば出来る。この関数を知らないということは、エクセル（の関数）を系統的に勉強してない証拠。ある本にエクセルを勉強したかどうか、この関数で判別すると、書いた記事があった。私もそう思う。丸投げして、回答者に頼らず、WEBでも照会すること。
ーー
Sheet2に
A-C列に
氏名 住所 電話番号
山田 目黒区 03-2345-6789
・・・
以下データを作っておく
Sheet1に
氏名（B3セルとする）
住所
電話番号
の見出しを作っておく（必ずしも作らなくて良いが）。下記式を入れるセル（場所）は、送付状のレイアウトに従ってセルを決める。
B4に氏名の山田を入れる。
C４には
=VLOOKUP($C$3,Sheet2!$A$1:$D$10,2,FALSE)の式を入れる。
C５には
=VLOOKUP($C$3,Sheet2!$A$1:$D$10,3,FALSE)のしきを入れる。
１０行以内の例、３項目の例ですが、適当に増やしてください。
Googleで｛エクセル　VLLOKUP関数」で勉強し、補強してください。VLOOKUPの記事、エクセル関数の記事は腐るほどある。
ーー
上野２つの式の違いは第3引数の２，３です。これは氏名列から2列目、３列目にあるから。
＄はこの場合はつけておくこと。
ーー
普通はワードの差込印刷の機能を作ったりする。
少し仕事関連のことに使おうとするとVBAが必要になるというのが持論です。
普通は顧客番号など、あいまいさをなくしたデータを指定する（同姓同名などあれば上記は破綻する）
それも考えておくこと。
送付状を作成は、印刷すると言うことだろうが、この点の省力化をするには、絶対VBAが必要です。
＞VBAは使わずに、一般の機能で作成したいです
なんて、勉強したくない、といっているようなもの。

回答No.1

noname#164823

別シートに一覧表を作成し、1レコードごとにナンバー（連番）を振り、
そのナンバーを入力したら、該当の会社名などが出るようにVLOOKUP関数を使います。

方法は下に貼ります。他にもこの関数名で検索すれば、沢山出てきます。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/vlookup2.htm