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

2024-08-31

複数シートから集約する関数テンプレ

支店ごとに売上シートがあり、1枚のシートに全支店分並べて表示したい。
複数の部門別損益計算書シートから1枚の全部門一覧シートを作りたい。
月別の12枚のシートから1枚のシートで月次推移を作りたい。

これらは二つの関数を使えば、けっして難しくありません。

実際に、 本文の事例のとおり一つのセルに関数を入力し、あとはそのコピペで複数シートから1枚のシートに一覧集約ができました。

もし、冒頭のようなシートを作りたいと思っているなら、ぜひ、このページを参考にしてみてはいかがでしょうか。

難しい仕組みとか気にせず、ただ事例のやり方を真似れば、望んだデータ集約ができると思います。

使える場面の例(別のシート、複数のシートを参照できる)

使える場面の例として、まずは下記の画像を見てください。

Excel2016で同じ様式の複数シートの情報を一覧形式で集約 indirect vlookup

例えば上記画像のような次の特徴がある場合、本記事の方法が有効です。
●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月の数値を一覧表示する。

このシートのB6~C8の範囲が参照される。

(手順)
 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スプレッドシートでもやり方は同じ。