エクセルで作成した住所録から任意のデータ抽出

質問

まず住所録ですが、形式はＡからＤ列に番号、略号、氏名、住所、行は2行目から最大100行まで同じ形式のものが、10シート以上あります。別形式の証明書用のシートがあり、そこに空欄の氏名と住所が別行にあります。任意の証明書に複数ある住所録から任意の氏名と住所を番号を検索して、1発抽出したいのが今回の質問です。
住所録、証明書用のシートが１つづつならばＶＬＯＯＫＵＰ関数で簡単に抽出できました。
しかし、各々が10シート以上にせざるを得なくなりました。
このような操作の場合、そもそも関数で対応できますか？証明書用シートそれぞれに住所録全部を範囲とすることもできず、困っております。
　もし関数でできるならばその方法を教えてください。
ＶＢＡやマクロでないと対応ができないとなるとその知識がないに等しいのでご教授の際は、細かく指示してもらえればありがたいです。
　コピペでやれば手間でもできますが、かなりの人数のものが利用しますので必ず氏名と住所を違うものをコピーしてきて貼り付けるのでこの方法は、不採用にしました。
以上、長くなりましたがよろしくご教授お願いいたします。

回答No.3

dogs_cats

NO2です。
今までVLOOKUPを使用されていたのでしたら分かると思うのですが。

各県シートのA列の番号を入力して氏名、住所を検索すれば良いのではありませんか。
番号は重複していては検索出来ないので、全て連番にするか文字列と番号を組み合わせる等が必要（北海道ならH1、北1等々）、画像は全て連番としています。

セル範囲はA2~E100、１行目に項目名入っていると仮定しています。よってデータは２行目から。

氏名の関数
=VLOOKUP($B$1,INDIRECT($B$14&"!A2:E100"),3,FALSE)

住所の関数
=VLOOKUP($B$1,INDIRECT($B$14&"!A2:E100"),3,FALSE)
E列データ不使用であればA2:D100でも構いません。

申請シートの住所、氏名は集計用シートの検索結果の住所、氏名のセルを参照です。

D12に=を入力後、集計用シートの住所を検索したセルを選択すれば、自動で参照式を作成してくれますよね。（例：=集計用!E2）

集計用シートの検索結果の位置を前回と変更していますが、画像を添付しますので確認下さい。

シート名の記載はどこのセル範囲でも構いませんが、セル範囲の変更は必要です。
=INDEX(A4:A13,MATCH(1,B4:B13,0))のA4~A13、B4~B13のセル範囲を変更すれば良いだけです。

回答No.2

dogs_cats

各シートのレイアウトが同じであれば、抽出用シートを１つ作成します。

添付画像に沿って説明。
B１にVLOOKUPで検索する検索ワードを入力

検索シート名をA4~A13に入力
B4式
=COUNTIF(INDIRECT(A4&"!A:A"),$B$1)
B13までコピペ
検索ワードが各シートのA列に存在すると仮定して数式を作成しています。
検索ワードが存在するシート名のB欄だけが1になる。countif関数

B14式（検索シート名取得）
=INDEX(A4:A13,MATCH(1,B4:B13,0))

B14のシート名でvlookupでデータを取得
D４式
=VLOOKUP($B$1,INDIRECT($B$14&"!A2:E100"),2,FALSE)
検索セル範囲をA2~E100としています。10シートの中でデータが存在する最大行番号を指定して下さい。

証明用シートに抽出シートのセルデータを参照させるだけです

補足

早速のご教授ありがとうございました。ただ職場のパソコンでしか操作できない状況（守秘義務で持ち出せない）で他の重要な業務で手がつけれない状況でしたが、ご指示の内容で作成しましたが未解決な部分があり、面倒とは思いますが、ご指示お願いします。
まずエクセルバージョンは2007で作成しています。ただ2010のパソコンと混在していますが、私のパソコンは2007です。
住所シートはＡ列は番号で１から最大１００までＢ列は略号ですが使用する気はなしＣ列は会社や個人の名称　Ｄ列はその住所　Ｅ列は特記事項という構成で地域別に個別名を付けて１６シートあります。
証明書は住所がＤ１３　氏名はＤ１４に存在し、ここに住所シートの任意の住所、氏名を飛ばして印刷するというのが今回の相談です。
以前は住所シートと証明書が１対でしたのでＶＬＯＯＫＵＰ関数で証明書シートに検索欄を設けて住所シートから飛ばせましたが、各々１０を超えるシート数となり、できなくなりました。
ご指示とおり(1)抽出用シートを作成(2)Ｂ１にＶＬＯＯＫＵＰで検索する検索ワード欄を作成しましたが、具体的にここへはどういうワードを入れるのでしょうか？(3)検索シートＡ列にはシート名として住所シートを１６シートの北海道から九州までの存在する住所シート名を入れましたが、間違いないですか？(4)Ｂ４式は実際の住所シート数１６をＢ４から１６までコペピしました(5)添付画面のＤからＧ列の検索１から４まではどういう設定で作るのでしょうか？
ここまで作成し、検索ワードに検索シートのＡ列の住所名たとえば北海道といれても全部０となります。
さらに語句ですが証明用シートに抽出シートのセルデータを参照させるだけとは、具体的にどうすることでしょうか？この参照の意味がよくわかりません。
関数でやるしかない状況でそれを理解するため解読本を買い込んで読んでいますが、うまくいかずすがるような思いですので、あまりにレベルの低い相談でしょうが、よろしくご指示お願いします。

回答No.1

keithin

具体的に何をどうしたいのかイマイチ言葉足らずですが、たとえば「（一意の）番号」を入力し、該当する氏名・住所を取ってくる（略号は要らない）とかで良いんでしょうか？

「簡単」な方法から「メンドクサイ」方法まで、やりようは幾らでもあります。

たとえば..
＞同じ形式のものが、10シート以上あります

「集約」シートを１枚用意
１枚目の住所録シートの１００行をそのままコピー、集約シートの１行から１００行までにリンク貼り付け
２枚目の住所録シートの１００行をそのままコピー、集約シートの１０１行から２００行までにリンク貼り付け
　：
１０枚目の住所録シートの１００行をそのままコピー、集約シートの１００１行から１１００行までにリンク貼り付け

以上の準備を一度アナタが手を動かしておけば、あとは証明書シートには、ごく簡単な数式で
=IF(A1="","",VLOOKUP(A1,集約!A:D,3,FALSE))
とか、ご利用のエクセルのバージョンも不明のご相談ですが
=IF(A1="","",IFERROR(VLOOKUP(A1,集約!A:D,4,FALSE),"NA"))
とかの数式を記入しておけば、出来上がりです。