初心者です。教えて下さい！

質問

Ｅｘｃｅｌで質問です。Ver2003。

複数シートから抽出条件に合致する行を
別シートに抽出する関数を教えてください。

例）ある町内の住所録のブック（１３シート）があります。
A-1氏名、b-1住所。。。というフォーマットで作成。
仮にこれにキーを追加して（A,B,C,C-2...）,別シートにそのキーを入力するとキーに一致する行を各シートから抽出して、自動的に入力させたいのです。

ＶＬＯＯＫＵＰ関数等で試しているのですが、うまくいきません。
どうぞ教えて下さい。宜しくお願いします。

回答No.4

KURUMITO

かなり時間を使って試験してみたので解答をしましますが、初心者では難しいかもしれません。こちらの指定した通りで一度試験してみてください。
Sheet1を仮にまとめのシートにします。Sheet2は町内1、Sheet2には町内2、Sheet3には町内3・・・・のように各シートがあるとします。
Sheet2からのシートでは1行目に氏名、住所、係名などの項目名が同じ順序で列に入力されているとします。
そこでSheet2をシート名タブで選択したのちに、Shiftキーを押しながらSheet2以降のシート名(Sheet1を除く)をクリックし、同じ作業グループを形成します。
その後にSheet2の例えばＪ2セルに次の式を入力して下方にオートフィルドラッグします。
=IF(INDIRECT(Sheet1!$A$1&ROW(A2))=Sheet1!$B$1,MAX(J$1:J1)+1,"")
この操作でSheet2以降のシートにはＪ2セルから下方に同じ式が入力されることになります。
そこで、まとめのシートであるSheet1を選択して次のような操作を行います。
Ａ1セルには各シートで抽出に使いたい列番号を例えばＣなどと入力します。
また、Ｂ1セルには、選んだ列で抽出したい文字列など(Ｃ列に係名があるとしたら班長などと）を入力します。
Ａ2セルから横の列にはSheet2,Sheet3,Sheet4・・・などとシート名を入力します。
Ａ3セルには次の式を入力して右横方向にオートフィルドラッグします。
=IF(A$2="","",MAX(INDIRECT(A$2&"!J:J")))
Ａ4セルには次の式を入力して右横方向にオートフィルドラッグします。
=IF(A3="","",SUM($A3:A3))
Ａ5セルから横方向には各シートに入力してある項目名を同じ順序で入力します。
Ａ6セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR(ROW(A1)>MAX($4:$4),A$5=""),"",IF(ROW(A1)<=$A$4,INDIRECT($A$2&"!"&ADDRESS(MATCH(ROW(A1),INDIRECT($A$2&"!J:J"),0),COLUMN(A1))),INDIRECT(INDEX($2:$2,MATCH(ROW(A1),$4:$4,1))&"!"&ADDRESS(MATCH(ROW(A1)-COUNTA($A$5:$A5)+1,INDIRECT(INDEX($2:$2,MATCH(ROW(A1),$4:$4,1))&"!J:J"),0),COLUMN(A1)))))
これで6行目以降にはＡ1に入力された列番号で、Ｂ1に入力された文字列を含む各シートの行が表示されます。

回答No.3

imogasi

>仮にこれにキーを追加して（A,B,C,C-2...）,別シートにそのキーを入力すると
私もこの意味がわかりません。
実例でも挙げて丁寧に説明のこと。
Ａ－１セルはＡ１セルと書くのが慣わしです。質問者はエクセルの経験（本をよむ、ＷＥＢ記事を読むなど）が少ないようだ。
ーーー
むつかしさは、質問者がＶＢＡはわからず、関数ぐらいしか使えないのではと思うが、関数では抜き出し問題は、式が複雑になり、難しいと思う。
それは１シート１語でも難しいので、下記の（１）などになると
ほんとに難しい。
ーー
検索だが、
（１）１語について、全シートを探索するのか。そして該当を１シートにだすか。
（２）検索語は数語あるのか。ＡＮＤ条件で数語か。
（３）１シート辺り１語を条件として探索する。各シートに検索語が別で違うのか。そしてそれらが複数（１３シート）あるのか
などはっきりしない。
ーー
結局、特徴を掴んで、実例を挙げる力が無いのではないか。
そういう人は回答が出ても回答を丸写しするだけで、意味など理解できない、修正が出来ないかも。
ーー
１シートで１列の１語を検索して抜き出し問題でも。Ｇｏｏｇｌｅで
「imogasi方式]で検索すれば、抜き出しの質問が沢山有り、私の回答や、他の方の回答が見られる。しかし複数シート対象は念頭になく
ほとんどそこの回答方式は使えないかも。
ーーー
結局ＶＢＡを使い慣れた人で無いと無理かもしれない。
エクセルの「検索」では「検索場所」で「ブック」があるので、これが使えるかも知れない。この操作のマクロの記録を採って、抜き出しを考え、その他を考えることになろう。
ーーー
シートを別にすることは、エクセルでは、したいことによっては、非常に難しくなるのだ。

回答No.2

yama1718

検索対象が複数シートに分かれていると単純なVLOOKUPではできませんよね。
複数シートに分かれる処理などはマクロの方が簡単にできるですが。
シートが１つの場合のVLOOKUPでの検索の方法は判りますよね？
キーを自由に決められるなら、シート１は1000番台、シート２は2000番台などにすると
キーからシートが判るようにできますよね、
VLOOKUPは各シート用を用意して、それをIFで選択させると言う方法はどうでしょうか？
でも、数式が長くなりすぎて無理かな。

もう一つの提案が検索用に１シートにまとめると言う方法ですね

=OFFSET(基準セル,ROW(),COLUMN())
※基準セルは絶対参照で、ROW(),COLUMN()は数式で調整する必要があります。

この数式を縦横にコピーすると参照元の住所録と同じ内容のデータが並び、しかも参照元に連動します。
これを縦に１３シートの住所録の分並べればVLOOKUP１つで検索できますね。
ただし、数が多くて１３シートに分割していて、65535件より多いのならこの方法も無理ですが。

本来この様な用途ではデータベースソフトを使うべきで、Accessなどなら簡単にできるのですけどね。

回答No.1

MackyNo1

>仮にこれにキーを追加して（A,B,C,C-2...）,別シートにそのキーを入力するとキーに一致する行を各シートから抽出して、自動的に入力させたいのです。

質問内容が、具体的でないため適切な回答ができません。

＞仮にこれにキーを追加して（A,B,C,C-2...）,

キーはC列の右に補助列を挿入して（またはD列を使用して）、その部分に検索のキーワードを入力したのでしょうか？

また、このキーはすべてのデータで異なる（１対１に対応している）ものなのでしょうか？

＞キーに一致する行を各シートから抽出して、自動的に入力させたいのです。

一致する行は1つしかないのでしょうか？

＞ＶＬＯＯＫＵＰ関数等で試しているのですが、うまくいきません。

どのような部分で「うまくいかない」のか、もう少し具体的にわからない部分を例示してください。
たとえば数式が長すぎるエラーが出る、1つのシートならできるけど複数シートのためできないなど、現在の問題点をわかりやすく例示し、かつシートのレイアウトやキーの値を具体的に提示してください。