はじめに
月別に分かれた売上などのデータを、通期でSUMIFS関数やピボットテーブルで集計したいのに「シート結合が面倒」…という方向けに、VSTACK関数で複数シートの表を1つにまとめる方法を解説します。3D参照で12か月分も一括化でき、さらに空白行やヘッダーを処理する方法もご紹介しています。
※VSTACK関数は主に「Excel for Microsoft 365（Web版含む）」で利用できる動的配列関数です。Excelのバージョンによっては使えない場合があります。
この記事の要点
- VSTACK関数は縦方向に範囲(配列)を結合できる関数
- VSTACK(範囲1,範囲2,....)のように記述
- 下方向への範囲はとりあえず広めにとってOKだが、その後で不要セルの処理が必要になる
- FILTER関数も組み合わせれば、不要な行は表示せず結合可能だが、式が若干煩雑になる
- 手軽な方法は①VSTACK関数により表を結合、②値貼り付け、③オートフィルターにより不要な行のみ表示、➃行ごと削除
必要なデータが複数シートに分割されている
例えば左側のように、1月1シートとして作成されているデータがあるとします。これを通期分として12か月まとめたデータに結合したい場合に活躍します。右側の画像は間のデータをグループ化していますが、12か月分すべて含まれています。
(なお、複数の表の見出しの列がそろっていることが前提になります。そうでない場合はパワークエリ等別の手段が有効です。)
VSTACK関数の使い方
VSTACK関数は、複数の指定した範囲について垂直（縦方向）にデータを結合する関数です。
関数は以下のように記述します。範囲2～以降は省略可能です。
=VSTACK(範囲1,範囲2...)
例えば以下の画像のように、B～I列にある2つの表を結合するシンプルな場合では、以下のように2つの範囲を続けて指定するだけです。
シート数が多い場合は串刺し集計を使う
シート数が多い場合、すべての範囲を個別に指定するのが大変なため、串刺し集計(3D参照)を使用するのが便利です。
串刺し集計は、関数を入力した最初のシートをクリックし、その後でShiftキーを押しながら最後のシートをクリックします。そのあとでセル範囲を選択します。
表記としては以下のようになります。
=関数(最初のシート名:最後のシート名!セル番地)
冒頭の12か月分のシートを結合する場合はこのようになります。
セル範囲については上記画像のように、各シートのデータ数を上回る数を指定しておけば漏れずに結合されます。
ただし、各シートにて行数を多めに取ると、空白行が混ざって0が表示される行が並びます。以下はVSTACK関数で空白行まで取り込んだ結果です。
FILTER関数を使用して、A列が0以外が表示されるように書けば以下のように0は表示せずに済みます。
ただ、FILTER関数で書こうとすると数式が長くなることや、各シートにある見出し(Code、日付、商品名等)は表の中に繰り返し出てきてしまいます(一応これも範囲指定などで対処は可能です)。
幸いオートフィルターをかければ規則性をもって表示されますので、見出しと0のみ表示して、それを行ごと削除するのが手軽な対処法です。VSTACK関数のままだとオートフィルターをかけてから行を削除できないため、事前に値貼り付けしておく必要があります。
一連の手順
一連の手順と、それを動画にしたものが以下になります。
- VSTACK関数によりすべてのシートを結合
- 値貼り付け
- オートフィルターで見出しと0のみ表示
- 不要な行を削除
上記の操作例では12シート分結合するのに1分かからず、すべてのデータをコピーペーストするよりは遥かに効率的です。必要なケースではぜひお試しください。
