エクセルにて複数のファイル（一覧表）から参照を切替えて検索するには？

質問

部品調達部門で仕事をしていますが、今、新たに関連資料の作成で頭を痛めています。
　　　A 　 　　 B 　 　　　　C 　 　 　　D
　 　 　　　マークII　　　クレスタ　　　チェイサー

1 タイヤ 　　○○　　　　○○　　　　　　○○
2ハンドル　　○○　　　　○○　　　　　　○○
3ホイール　　○○　　　　○○　　　　　　○○
4シート　　　○○　　　　○○　　　　　　○○
5ダッシュ　　○○　　　　○○　　　　　　○○

と言うような表があり、別のシートに部品別に納品予定表を組もうと考えています。A列の構成部品は車種により違います。（車種により部品有/無がある）
　またこの一覧表はシリーズ毎に複数あり、（カローラ/スプリンター）（ハイエース/グランビア）色んなシートが存在します。
　そこで予め、生産予定に従って部品別の納品予定を次のようにしたいのです。

　１１月度予定　　計　　　タイヤ番号

　１マークII　　　300　　　　○○
　２ハイエース 　50 　　　　 ○○
　３クレスタ 　　 100 　　 　 ○○
　４カローラ 　　 500 　 　　 ○○
と言う風に、VLOOKUP+MATCH,又はINDEX+MATCH+INDIRECT関数を複合、切替えてA列車種に応じた一覧表ファイルからタイヤ番号を表引きしたいのです。
　元データー一覧表が複数あるためにこれをINDIRECT+セル範囲名前で切替えたいのですが、テキスト活用例にあまり載っていないのでよく分かりません。INDIRECTをどこに入れれば良いでしょうか?他に簡単に切替える方法、関数の組み合わせはありますか?
　車種がマークIIならマークII一覧表のタイヤデーター番号を検索し別表に表示させたいのです。IF+OR/IF+AND/IF+文字検索関数ではかなり式が長くなるように思います。一番簡単に表引きが可能な方法を教えてください。

回答No.3

odessa7 · ベストアンサー

＃１です。
　参考になったようで嬉しいです。

＞苦手な関数ってお有ですか?

　たくさんありますよ。行列系の関数は特に苦手です。^_^;

＞何故、この式でこの戻り値が返るのか？

　作成したファイルの「予定表シート　Ｃ４」セルを見てください。ここでは最終的にINDIRECT(A!B2)と言う結果を得たいわけです。なので、

■Aの部分はF4セルのVLOOKUP関数で取得した値を参照しています。

■続いてB2の部分ですが、これは「タイヤ」と「クラウン」に一致するセルの位置をシートAより取得する必要があります。そこでMATCH関数で『「タイヤ」と書かれたセルはシートAの部品名の列の何番目か？』という情報を取得し、同様に『「クラウン」は行の何番目か？』を取得する、ということをやっています。

　言葉にするとややこしいですが、そんなに複雑なものではありません。

　本当はこういうデータはエクセルではなくアクセスを使うべきなんでしょうが、残念ながら私は使いこなせません。しかもエクセルのマクロも使えないので、自然と無理やり関数を組んでしまうことが多いのです。このあたりは今後の課題です。

回答No.2

odessa7

＃１です。

　後で少し考えてみましたが、先ほどのやり方はあまり格好の良いものではないんじゃないかと思い始めてます。

　やはり、データベース用のワークシートを1つ作って、そこに下記のように対応表を展開し、（情報は各ワークシートから取得）VLOOKUP関数などで参照するのが良いかもしれません。

　　　　　　┌─シート１─┐　　　　　┌─シートX─┐
　　　　　　クラウン　セルシオ　・・・・・・MR-S　セリカ
タイヤ
ハンドル
シート
　・
　・
ダッシュ

　ただし、あまりにシート数が多い場合は、上記の方法も苦しいかもしれませんが。

　それと、これは余談ですが、予定表の列に車種を記載されるおつもりなら、基になる部品対応表も列に車種を入力されるほうが良いと思います。（HLOOKUP使えば良いと言えばそれまでかもしれませんが、他の人が関数を見たときなど、すぐには意味が理解できないかもしれません。デバッグなどで苦労されると思います。）

お礼

何度も回答有難うございます。レベルの低い私の質問にわざわざ、関数式を組んでいただいて感謝いたしております。
　VLOOKやHLOOKでもやれないことはないのですが、
元となる、一覧表は同じシリーズ毎に複数あるのですが、
１．特定モデルのみの専用部品がある
２．行/列のセルの数、範囲が一定でない
３．セルの結合がやたら多い
４．空白セルが多い
５．IF+VLOOKUPやIF+HLOOKUPでやる場合、車名の例に置き換えています型番の数が多くてIFの条件区別が細かく設定する必要があります。

　LEFT,MID,FIND,ISERROR,OR,ISBLANKなど、型番区別条件にかなりネスト数が増えて式だらけになりそうです。
　これらの関数をネストしてできるのですが、元からある一覧表を編集修正する必要があり、(１～５の項目）元の一覧表をいじることなく（編集修正に膨大な時間が掛かる）早くできる方法としてINDEX+MATCHの方法を考えたのですが、これをVLOOKUP+INDIRECTの活用例のような参照範囲をINDIRECT(セル範囲名前で切替）を用いてINDEX+MATCHでもできないかと考えました。
　○極力、元一覧表の編集修正をしたくない。

　問題はINDEXの配列の範囲と行/列をMATCHでデーターを検索しますが、配列、行列にどのようにINDIRECTを挿入するのか調べましたが、（本、ネット）見当たりませんでした。

　やりたいことはA1セルがクラウンであればクラウン一覧表からINDEX+MATCHで表引きをし、A2セルがセリカであれば別のセリカ一覧表から同様にデーターを探したいのですが、表切替をするためにINDIRECTをどこへ、どう挿入するのか分かりませんでした。

　けれどODESSA7さんが昨日回答していただいて、本日新たに続きの回答をして下さったので大変、参考になりました。

　初回答の力技の式を拝見して『こういう使い方もあるのだな』と感心しました。何故、この式でこの戻り値が返るのか、検証しているところです。

　私はVLOOK,HLOOK,INDEXは資料作りに活用するのですが、ADRESSやOFFSET関数が今一理解できておらず、あまり使えません。（苦手な関数ってお有ですか?）

　同じ検索/行列関数なのに『こちらはしくみが分かっているから慣れてる関数ばかり使ってしまいます。

　長々となりましたが、私ごときのために、時間を割いて回答していただいたこと、重ね重ね有難うございました。

回答No.1

odessa7

ご質問内容にやや不明点はありますが、要するに「複数のシートの情報を参照するには？」、と言うことでよろしいでしょうか？（タイトルには「複数ファイル」とありますが、内容からは「複数シート」としか読み取れませんでした。）

　ちょっと文章では表現が難しいですが、まずシート名参照用のワークシートを作りシート名を取得、あとはINDIRECTとMATCH関数で希望の値を取得する方法をとっています。

　私の頭では、こんなのしか思い浮かびませんでした。もっとスマートな方法もあるでしょうが、それは他の方に譲ります。(^_^;)

お礼

ご回答有難うございます。説明が不十分で申し訳ありませんでした。色んな本、サイトでINDEX+MATCH+INDIRECT関数を組み合わせた事例を探しましたが、見つかりませんでした。
　VLOOKUP+INDIRECTはたまに使いますが、上記の３通りは使ったことがありません。
　まる１日しても回答が得られなかったので削除か?と思っていましたが、返答いただき、感謝申し上げます。