Excelで複数のシートからvlookup関数でデータを集約する算式テンプレ
支店ごとに売上シートがあり、1枚のシートに全支店分並べて表示したい。
複数の部門別損益計算書シートから1枚の全部門一覧シートを作りたい。
月別の12枚のシートから1枚のシートで月次推移を作りたい。
これらは二つの関数を使えば、けっして難しくありません。
実際に、 本文の事例のとおり一つのセルに関数を入力し、あとはそのコピペで複数シートから1枚のシートに一覧集約ができました。
もし、冒頭のようなシートを作りたいと思っているなら、ぜひ、このページを参考にしてみてはいかがでしょうか。
難しい仕組みとか気にせず、ただ事例のやり方を真似れば、望んだデータ集約ができると思います。
使える場面の例（別のシート、複数のシートを参照できる）
使える場面の例として、まずは下記の画像を見てください。
例えば上記画像のような次の特徴がある場合、本記事の方法が有効です。
●A店、B店、C店と店ごとに1枚ずつシートがある
●各シートの様式が同じ
●画像左側のようにA,B,Cと各シートの内容を並べて一覧にしたい
同じく、商品別のシートから全商品一覧データを作成することもできますし、
1月、2月、3月、、、と月別のシートから、1枚のシートで１月～の各月を並べた推移表を作ることも可能です。
ここで紹介する方法であれば、
・関数を入力するセルは一つだけ
・他の該当セルにはそれをコピペするだけ
と、けっこう簡単にできます。
（算式のテンプレートと活用事例をご用意しました。よかったら活用してください。）
シート名とセル範囲を参照してvlookupを使う手順
事前準備
１．集計したいシートを用意します。先ほどの画像では「全社」というシートを作成しています。
２．「全社」シートには各個別シートと同じ様式の表を用意します。
3．集計したいシートのシート名を横に並べます。
（最終的には、 A店、B店などこの行に入力された名前のシートを参照するため、実際のシート名とこの行の名称は一致させる必要があります。）
なお、この事例で集計したいシートはA店,B店,C店の3枚だけなので、集約箇所へは手入力でも余裕です。
ただ、実際は１～12月で12枚だとか、支店が40店舗あるとか、
シート名が多すぎて手入力するのはしんどいという場合があります。
そのような場合は、全シートの名前の一覧を一括取得する方法があるので、後述の「シートが多い場合に便利！全シート名を一括取得する方法」をご覧ください。
関数の入力
集約用のシートが準備できれば、いよいよ関数を入力します。
A店の売上高のセルには、次のように入力します。
=VLOOKUP($B4,INDIRECT(“‘”&D$3&”‘!$B$4:$C$6”),2,0)
すると、下の画像のようにA店の売上高100が参照されました。
関数の説明
「説明なんかいらん、使い方だけ知りたい」という方は、ここは飛ばして「複数シートを参照する場合の算式テンプレート」をご参照ください。
さて、ザクっと説明しておきます。
言葉で説明するスキルが弱いので、下の画像で感覚的につかんでいただけると助かります。
算式と表の関係は下図のとおりです。
【この算式全体の意味】
「A店」のシートの青字の範囲（B4~C6のセル）のうち、「売上高」の行の2列目のセルを参照する
【indirect関数の部分の意味】
下図のとおり、オレンジの部分が参照するシートを、青字の部分が参照するセル範囲を指定しています。
ちなみに下図の3行目の式のように入力しても同じ「100」という結果が表示されます。
上の図の1行目のようにD$3セルを参照する理由は、E列、F列にコピペできるからです。
（1行目の式を右隣にコピペすればB店の情報を表示してくれます。
3行目の式を右隣りにコピペすれば、B店の列なのにA店の値が表示されてしまいます。）
複数シートを参照する場合の算式テンプレート
上記の説明をみても、わかりにくいうえに読むのも面倒だと思いますので、算式のテンプレートを記載しておきます。
下記のテンプレートにあてはめて使えば、意味が分からなくても他のシートを参照できます。
（算式テンプレート ）
=vlookup($B4,indirect(“‘”&シート名のセル&”‘!参照セル範囲”),2,0)
上記の式をコピぺして、「＄B4」、「シート名のセル」、「参照セル範囲」、「２」の部分をご自身のデータに置き換えて使ってください。
「”」「’」なども削除しないよう、このまま漏れなく貼り付けてください。
置き換える部分は下記の色付きの文字部分です。
テンプレート活用例
（題材）
1月～4月の業績が入力された4枚のシートがある。
「月次推移」というシートを作り、左から右へ１～4月の数値を一覧表示する。
（手順）
１．「月次推移」というシートを作成する。
２．C6のセル内に上記テンプレートの算式をコピペする。
３．算式をアレンジ
①青い枠をB６へ、赤い枠をC5へドラッグして変更する。
②「＄B$4:$C$6」を上記題材のセル範囲「＄B$6:$C$8」へ変更する。
（算式右側「２」はこの事例でも参照範囲の2列めを参照するため、「２」のままでよい。）
４．enterを押せば、1月シートの売上高1000が表示される。
５．いま入力したC6のセルをコピーし、C6~F8までペーストする。
以上で、月次推移の集約完了です。
Excelシートが多い場合に便利！全シート名を一括取得する方法
Excelシートが20枚も30枚もある場合、シート名をいちいち入力するのは手間ですよね。
そんな場合は、なれたら10秒ぐらいでシート名を取得できる方法があります。
おすすめの下準備
ここで紹介する方法は、ある場所で「For Each i In ThisWorkbook.Sheets: debug.print i.name : next」という文字を入力する必要があります。
私は仕事でいろんなExcelファイルを触る機会があり、シート名を一括取得したい場面がちょこちょこあります。
そのたびに上記の長い文字列をいちいち打ったり、どっかのサイトからコピペするのは面倒です。
そこで、辞書登録しています。
下記の文字を全選択した状態で、Windows画面の下とか右とかにあるIMEオプションを右クリックします。
For Each i In ThisWorkbook.Sheets: debug.print i.name : next
下記「あ」のところで右クリックです。
右クリックすると、真ん中あたりに「単語の登録」があるので、それをクリックします。
そこで、上記の長い文字列を登録します。
（私は、「しーと」と入力すると、上記文字列が変換候補に表示される設定で登録しています。
これで下準備は完了です。
Excelシート名のリスト 一括取得する方法（慣れれば10秒以内）
①「Alt」+「F11」を押す （下の画面が表示されます）
②「Ctrl」+「G」を押す （イミディエイトという下段の白い部分にカーソルが点滅します。
③「For Each i In ThisWorkbook.Sheets: debug.print i.name : next」と入力（上の下準備があれば簡単）し、「ENTER」
すると、シート名が縦にズラッと並びます。
④「Ctrl」+「A」 もしくはマウスで範囲選択してコピーして、どこかへ貼付ければ、シート名が一括取得できます。
このシート名一括取得を、ストップウォッチで測りながらやってみました。
手順が頭に入っているため、急がなくても10秒ぐらいでした。
便利なのでぜひ体得してみてはいかがでしょうか。
まとめ
●Excelで複数のシートからデータを集約するには、Indirect関数とvlookup関数を使えば楽。
●関数の理解が難しければ、この記事の算式テンプレを使えば楽。
●ついでに、Excelシート名の一覧を瞬殺で一括取得する方法も紹介。