S045｜複数ブックからブック名と検索値に該当するデータを抽出【XLOOKUP関数、VLOOKUP関数、SWITCH関数、CHOOSE関数】｜Excel関数によるデータ抽出方法

抽出先のブックだけを開き、ブック名と検索値で検索抽出するなら、SWITCH関数（Excel2016ではCHOOSE関数）で抽出元のブックのセル範囲を切り替えて抽出しよう。

例題１｜複数のブックから検索抽出する

1. 抽出するブックはすべて開く。
2. 「電話番号」を求めるセル（C3セル）を選択し、「=VLOOKUP(」と入力する。
3. ［検索値］…「ID検索」のセル（B3セル）を選択する。
4. ［範囲］…「SWITCH(」と入力する。
5. ［式］…A3セルを選択する。
6. ［値1］…「”220-01”」と入力する。
7. ［結果1］…［ウィンドウの切り替え］ボタンから「220-01」ブックを選択して抽出するセル範囲を選択する。
8. ［値２］…「”220-02”」と入力する。
9. ［結果２］…［ウィンドウの切り替え］ボタンから「220-02」ブックを選択して抽出するセル範囲を選択する。
10. ［規定または値3］…［””］（空白）を入力する。
11. ［列番号］…抽出する列の番目「3」を入力する。
12. ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する。

【数式】=VLOOKUP(B3,SWITCH(A3,”220-01″,'[220-01.xlsx]A’!$A$2:$C$3,“220-02″,'[220-02.xlsx]B’!$A$2:$C$2,””),3,0)

数式の解説

「SWITCH(A3,“220-01”,‘[220-01.xlsx]A’!$A$2:$C$3,“220-02”,‘[220-02.xlsx]B’!$A$2:$C$2,“”)」の数式は、A3セルのブック名が「220-01」なら「220-01.xlsx」のA2セル～C3セル、「220-02」なら「220-02.xlsx」のA2セル～C2セルのセル範囲を抽出する。VLOOKUP関数の引数［範囲］に組み合わせることで、B3セルの「A002」をA3セルの「220-01」ブックのA2セル～C3セルのセル範囲から検索し、一致した行にある3列目の電話番号「080-****-0002」が抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample00.xlsx  
Excel-sample01.xlsx  
Excel-sample02.xlsx  

例題２｜複数のブックから検索抽出する

1. 抽出するブックはすべて開く。
2. 表外のセル（A4セル）を選択し、A3セルに入力したら「1」からの連続した番号が入力されるようにIF関数で条件式を入力する。
3. 「電話番号」を求めるセル（C3セル）を選択し、「=VLOOKUP(」と入力する
4. ［検索値］…「ID検索」のセル（B3セル）を選択する。
5. ［範囲］…「CHOOSE(A4,」と入力する。
6. ［ウィンドウの切り替え］ボタンからブックを切り替えながら、［値1］に「220-01」ブックの抽出するセル範囲を選択、［値2］に「220-02」ブックの抽出するセル範囲を選択する。
7. ［列番号］…抽出する列の番目「3」を入力する。
8. ［検索方法］…完全一致なので「0」と入力して、「Enter」キーで数式を確定する。

【数式】=VLOOKUP(B3,CHOOSE(A4,'[220-01.xlsx]A’!$A$2:$C$3,

                                                                            ‘[220-02.xlsx]B’!$A$2:$C$2),3,0)

数式の解説

「=IF(A3=“220-01”,1,2)」の数式は、A3セルに「220-01」と入力したら「1」、「220-02」と入力したら「2」が求められるように条件式を作成する（シートの数だけIF関数を組み合わせて条件式を作成する）。

「CHOOSE(A4,‘[220-01.xlsx]A’!$A$2:$C$3,‘[220-02.xlsx]B’!$A$2:$C$2)」の数式は、A4セルに「1」が入力されると引数［値1］の「220-01」ブックのA2セル～C3セル、A4セルに「2」が入力されると引数［値2］の「220-02」ブックのA2セル～C2セルのセル範囲を抽出する。

VLOOKUP関数の［範囲］に組み合わせることで、B3セルの「A002」をA3セルの「220-01」ブックのA2セル～C3セルのセル範囲から検索し、一致した行にある3列目の電話番号「080-****-0002」が抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample00.xlsx  
Excel-sample01.xlsx  
Excel-sample02.xlsx  

応用１｜

ブック名を入力せずに、検索値だけで該当のデータを抽出するなら、

❶数式を作成したブック内の別シートにすべてのブックを1つにまとめたテーブルとしてPower Queryで取り込んでおき、そのテーブルをもとに検索抽出する数式を作成しましょう。この場合、同じブック内のシートから検索抽出できるので、❷XLOOKUP/VLOOKUP関数だけで検索抽出でき、ブックの追加や変更が合っても［更新］ボタン1つで反映させることができます。

また、参照元のブックがたとえ1つの場合でも、共有フォルダ内などのネットワーク上にブックを作成しているとき、数式を入れた参照先のブックを開き、次に参照元のブックを開いて編集を有効にするボタンを押すとエラーになってしまい、正しく参照できないトラブルが起きることがあります。そんなときの対処法にはいろいろありますが、数式を作成したブック内のシートに、参照元のブックをPower Queryで取り込んでおけば、そのテーブルをもとに検索抽出する数式を作成することで対処できます。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample00.xlsx  
Excel-sample01.xlsx  
Excel-sample02.xlsx