エクセルで、複数のブックの複数のシートから検索できますか？

エクセルで、複数のブックの複数のシートから INDEX MATCH を使用して検索できますか？
宜しくお願いいたします。

質問者からの補足コメント

• 

  まとめる際に、全てを1列に並べるのではなく、（項目数可変で並べるのは面倒なので）平面的な表としてまとめた場合は、違う関数を利用する方が検索はしやすくなると思います。
  こちらの方法なら、各シート（2列分）をそのまま横に並べる形で表にまとめればよくなるので、「まとめ」の処理の方は簡単になると考えられます。
  
  非常に興味があります。具体的に教えていただけないでしょうか？

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/10/27 07:45

No.5 ベストアンサー 回答者： goomania 回答日時： 2020/10/27 19:35

ご質問者が別の質問でお示しになった「野菜.xlsx」「果物.xlsx」「魚.xlsx」というファイルが、

仮にCドライブの「教えてgoo」というフォルダにあるとして、別の検索用ブックに「検索シート」がある場合、
検索シートのA2に「シャインマスカット」と入力したときB2に「H-3」が表示される関数は以下のようなものです。
=IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]キャベツ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]にんじん'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]じゃがいも'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]たまねぎ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]りんご'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]いちご'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]すいか'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]ぶどう'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]あじ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]いわし'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]たい'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]マグロ'!$A:$B,2,FALSE),""))))))))))))
INDIRECT関数を使用するともっとすっきりしたものになるかも知れませんが、No.3さんの回答のとおり、INDIRECT関数は対象ブックを開いておかないと使えません。
このため、VLOOKUP関数を使用しました。
「野菜」「果物」「魚」以外のカテゴリーが追加された場合は、その内容を「新カテゴリー名.xlsx」というブックに作成し、Cドライブの同じフォルダに保存した上で、数式に新たに追記していくことになります。
数式の作成にはテキストエディタを活用されると良いと思います。

この回答へのお礼

ありがとうございました。

お礼日時：2020/10/31 14:39

No.4 回答者： fujillin 回答日時： 2020/10/27 11:22

No2です

＞非常に興味があります。具体的に教えていただけないでしょうか？
もとのご質問が抽象的なので、具体的にと言われても限界がありますが・・・

各シートが２列構成と仮定して、まとめシートは（A、B)(C、D)・・・列をセットに考えて、各シートを参照する式を設定しておきます。
単純参照だと、空白欄が「0」表示になってしまうので、空白欄は空白になるような式にしておくほうが良いでしょう。
以上でまとめシートは作成できると思います。
（各シートで項目を変更や追加したりすると、自動的にまとめに反映されるようになります）
（ブックが異なる場合は、両方開いていないと即時反映はされませんが…）

検索は、このまとめシートから検索することになりますが、「奇数列で値が一致する」ことを条件に検索し、ヒットしたセルの右隣にある値を返すようにすれば良いです。
複数条件検索かつ二次元からの検索になるので、配列計算になると思いますが、対象範囲を列全体などにしてしまう計算時間がかかってしまう可能性が高いので、計算範囲はある程度限定した式にしておくほうが良いと考えられます。


面的な表からの検索方法はいろいろあると思いますが、例えば、
AGGREGATE(15,6,(ROW(セル範囲)+COLUMN(セル範囲)*定数)/(セル範囲=検索値)/(MOD(COLUMN(セル範囲),2)=1),1)
を計算すると、ヒットした「行番号+列番号*定数」の値(＝nとする)が返されます。
（定数はセル範囲に応じて、適切な値を任意に設定しておけば良いです）

上記の値に対して、MOD(n, 定数）で行番号が、INT(n/定数)で列番号が逆算できますので、これを利用して、INDEX関数で右隣を得れば良いので、
　=INDEX(OFFSET(A:A,,列番号),行番号)
で、最終的に求めている値を得ることができます。

全体をひとつの式に纏めたければ、式を組合わせることで（多少長くはなりますが）可能です。
また、ヒットする値が存在しなければ、当然、結果はエラー値になります。

この回答へのお礼

ありがとうございました。

お礼日時：2020/10/31 14:39

No.3 回答者： d-q-t-p 回答日時： 2020/10/27 06:59

やろうと思えばできますが対象の全てのブックの全てのシートに対し

て数式を一個ずつ作って計算したものを最後に集約することになるか
と思います。 INDIRECTは対象ブックを開いておかないといけなくな
るので使えません。要は１つ検索するのに全シート分の数式を作って
まとめる作業が必要です。

結局のところ情報をブックやシートでやたらめったら分けた時点で数
式では何一つ上手くいかなくなります。検索対象のデータは分けない
のが鉄則です。

どうしても数式でやりたいのなら（それに意味があるかどうかは置い
といて）マクロかクエリの追加でデータを一箇所に集約してからにし
た方がいいかと思います。外部参照を数式で直接やること自体お勧め
できかねます。

この回答へのお礼

「検索対象のデータは分けないのが鉄則」なのはわかります。
検索対象のデータが増えたり減ったり変更したりで把握しにくいので分けていたのですが、、、、、、
やっぱりひとつにまとめないとダメですよね。

お礼日時：2020/10/27 07:43

No.2 回答者： fujillin 回答日時： 2020/10/26 22:15

こんばんは

完全一致検索でよいのですよね？

INDEX MATCHのみでは難しいと思いますが、シート数が大量でなければ、不可能ではないでしょう。

ひとつのシートで検索して、存在すれば値を、しなければ空白を返す関数を作成します。
後は、それをシートの数だけ連結してしまえば可能と思います。


ブックに仕組みを作っておいても良ければ、直接の検索の式をもっと簡単な式にすることも可能と思います。
例えば、各ブック内のシートをひとつのシートにまとめるように関数式で設定しておきます。
次に、同様にして各ブックのまとめシートをひとつのシートにまとめます。

この「全体まとめシート」から検索するようにすれば、直接の検索の関数式は、ご想像のような
＞ INDEX MATCH を使用して検索できますか？
という検索に近いものでも可能になると思います。

まとめる際に、全てを1列に並べるのではなく、（項目数可変で並べるのは面倒なので）平面的な表としてまとめた場合は、違う関数を利用する方が検索はしやすくなると思います。
こちらの方法なら、各シート（2列分）をそのまま横に並べる形で表にまとめればよくなるので、「まとめ」の処理の方は簡単になると考えられます。

この回答へのお礼

ありがとうございます。
やっぱり「まとめ」ないとダメですよね。

お礼日時：2020/10/27 07:34

No.1 回答者： くんこば 回答日時： 2020/10/26 21:40

できん

この回答へのお礼

できん　ですか・・・

お礼日時：2020/10/27 07:32