Excelで複数のシートからvlookup関数でデータを集約する算式テンプレ(スプシでも使える)

支店ごとに売上シートがあり、1枚のシートに全支店分並べて表示したい。
複数の部門別損益計算書シートから1枚の全部門一覧シートを作りたい。
月別の12枚のシートから1枚のシートで月次推移を作りたい。
これらは二つの関数を使えば、けっして難しくありません。
実際に、 本文の事例のとおり一つのセルに関数を入力し、あとはそのコピペで複数シートから1枚のシートに一覧集約ができました。
もし、冒頭のようなシートを作りたいと思っているなら、ぜひ、このページを参考にしてみてはいかがでしょうか。
難しい仕組みとか気にせず、ただ事例のやり方を真似れば、望んだデータ集約ができると思います。
使える場面の例(別のシート、複数のシートを参照できる)
使える場面の例として、まずは下記の画像を見てください。

例えば上記画像のような次の特徴がある場合、本記事の方法が有効です。
●A店、B店、C店と店ごとに1枚ずつシートがある
●各シートの様式が同じ
●画像左側のようにA,B,Cと各シートの内容を並べて一覧にしたい
同じく、商品別のシートから全商品一覧データを作成することもできますし、
1月、2月、3月、、、と月別のシートから、1枚のシートで1月~の各月を並べた推移表を作ることも可能です。
ここで紹介する方法であれば、
・関数を入力するセルは一つだけ
・他の該当セルにはそれをコピペするだけ
と、けっこう簡単にできます。
(算式のテンプレートと活用事例をご用意しました。よかったら活用してください。)
シート名とセル範囲を参照してvlookupを使う手順
事前準備
1.集計したいシートを用意します。先ほどの画像では「全社」というシートを作成しています。
2.「全社」シートには各個別シートと同じ様式の表を用意します。

3.集計したいシートのシート名を横に並べます。
(最終的には、 A店、B店などこの行に入力された名前のシートを参照するため、実際のシート名とこの行の名称は一致させる必要があります。)

なお、この事例で集計したいシートはA店,B店,C店の3枚だけなので、集約箇所へは手入力でも余裕です。
ただ、実際は1~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」、「シート名のセル」、「参照セル範囲」、「2」の部分をご自身のデータに置き換えて使ってください。
「”」「’」なども削除しないよう、このまま漏れなく貼り付けてください。
置き換える部分は下記の色付きの文字部分です。

テンプレート活用例
(題材)
1月~4月の業績が入力された4枚のシートがある。
「月次推移」というシートを作り、左から右へ1~4月の数値を一覧表示する。

(手順)
1.「月次推移」というシートを作成する。

2.C6のセル内に上記テンプレートの算式をコピペする。

3.算式をアレンジ
①青い枠をB6へ、赤い枠をC5へドラッグして変更する。
②「$B$4:$C$6」を上記題材のセル範囲「$B$6:$C$8」へ変更する。
(算式右側「2」はこの事例でも参照範囲の2列めを参照するため、「2」のままでよい。)

4.enterを押せば、1月シートの売上高1000が表示される。

5.いま入力した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秒ぐらいでした。
便利なのでぜひ体得してみてはいかがでしょうか。
Googleスプレッドシート(スプシ)での使い方
2024/8/31追記
やり方は本記事と同じです。
仕事でGoogleスプレッドシートを使うこともあるため、そちらでも同じ方法を試してみました。
下記画像のとおり、この記事でお伝えしたテンプレで、複数の店舗シートから数値を集計できました。
まとめ
●Excelで複数のシートからデータを集約するには、Indirect関数とvlookup関数を使えば楽。
●関数の理解が難しければ、この記事の算式テンプレを使えば楽。
●ついでに、Excelシート名の一覧を瞬殺で一括取得する方法も紹介。
●Googleスプレッドシートでもやり方は同じ。