VSTACK関数を使えば、複数シートのデータを1つにまとめることができます。
例えば、月別や担当者別にシートを分けている場合、こんな式を書いたことはありませんか？
=VSTACK('1月'!A2:C100,'2月'!A2:C100,'3月'!A2:C100)この方法はとても便利ですが、実際に使い続けていると、こんな問題が出てきます。
「シートが増えるたびに、数式を修正しないといけない・・・」
例えば、
・4月のシートを追加 → 式を修正
・新しい担当者が増えた → また修正
・追加し忘れて、データが抜けていた…
など。
そのときに、
・Excelで複数シートにまとめたい
・シートが増えても自動で集計したい
と考えたことがある人も多いと思います。
実はこれ、「シート名を追加するだけ」で自動対応させる方法があります。
今回は、VSTACKの応用として、シートが増えても数式を直さなくていい統合方法を紹介します。
月別売上や担当者別の日報など、実務でもかなり役立つテクニックです。
ぜひチェックしてみてください。
VSTACK関数で複数シートを統合する基本
本記事では、VSTACK関数を応用して「シートが増えても自動で統合する方法」を紹介しています。
もし、
・VSTACK関数をまだ使ったことがない
・基本的な使い方を知りたい
・複数シートをまとめる基本から知りたい
という場合は、まずこちらの記事から読むのがおすすめです。
👉 VSTACK関数の基本的な使い方はこちら
基本を理解してから本記事を読むと、今回紹介する応用テクニックがより理解しやすくなります。
VSTACK関数の弱点｜シート追加のたびに修正が必要
まずは、よくあるVSTACKの使い方を確認してみましょう。
たとえば、売上データが月別シートにあるファイルがあるとします。
シート構成としては、「1月」、「2月」、「3月」の3シートがあり、この3シートを統合するとします。
これをまとめるには、VSTACK関数を以下のように使用します。
=VSTACK('1月'!A2:D6,'2月'!A2:D6,'3月'!A2:D5)
VSTACK関数を使用することで、既にあるシートを1シートに統合することができます。
手作業でコピペしたり、難しいVBAを使わなくても、このVSTACK関数を使うことで、ものすごく簡単にデータを統合することができるようになりました。
しかし、統合したいシートを増やすときはちょっと手間がかかります。
例えば、先ほどのデータに「4月」のシートを追加したいとします。
この場合、当然ながら自動では増えず、VSTACK関数で「4月」シートを追加してあげる必要があります。
=VSTACK('1月'!A2:D6,'2月'!A2:D6,'3月'!A2:D5,'4月'!A2:D6) ←4月追加つまり、シートが増えるたびに、式を手動で修正する必要があるということです。
これは地味に面倒ですよね。
さらに、
・担当者が増えた
・新しい月が始まった
など、シートが増え続けるケースは多いと思います。
そのたび、毎回手動で数式を増やす必要があり、管理がどんどん大変になってしまいます。
解決策｜シート名一覧で複数シートを自動統合する方法
では、この問題を解決できるいい方法はないのでしょうか。
ここからが、この記事の本題です。
実は、シート名をセルに一覧で書くだけで、増えたシートも自動統合することができます。
先ほどは、月別シートを直接範囲指定→シート統合しました。
今度は「シート一覧」を作成し、そのシートに記載があるシートを自動統合していきます。
シート名一覧を作る
まず統合するシート名を記載するシートを作成します。
今回は「シート一覧」というシートを作成し、そこに統合するシート名を記載します。
ここに記載したシート名のデータを、最終的に統合することができるようになります。
今回の例でいうと、「1月」・「2月」・「3月」シートを統合したいので、「シート一覧」に以下のように入力します。
もし「4月」シートを追加したい場合は、シート一覧に「4月」を追記するだけでOKです。
これで、「4月」シートのデータも自動で統合することができます。
手動で数式修正→データ統合するよりかなり楽で効率的ですよね。
【完成形】シート追加に対応した自動統合の数式
では、実際にデータを自動で統合する場合、どのような数式を入力したらいいでしょうか。
結論、次の数式を入力することで自動化することができます。
=DROP(
REDUCE(
"",
FILTER(シート一覧!A:A,シート一覧!A:A<>""),
LAMBDA(x,y,
VSTACK(
x,
FILTER(
INDIRECT("'"&y&"'!A2:D1000"),
INDIRECT("'"&y&"'!A2:A1000")<>""
)
)
)
),
1)
これだけで、シート名を追加するだけで、自動的に統合されるようになります。
「シート一覧」シートに、「1月」・「2月」・「3月」と記載していた場合、以下のように3シート分が自動的に統合されます。
ちなみにお気づきの方もいらっしゃると思いますが、「3月」のデータ範囲は、「1月」「2月」シートより1行少ないです。
VSTACK関数では、各シートのデータ範囲に合わせる必要がありましたが、FILTER関数で空白行を除外しているため、結果的に可変範囲のように扱えることができます。
この「シート一覧」に「4月」を追加した場合、統合されるデータは以下のように変わります。
数式を手動で修正しなくても、自動的に4月データが統合されていることが確認できますね。
数式の仕組みをわかりやすく解説（REDUCE×LAMBDA）
この数式は、「複数シートのデータを空白除外して縦に結合する式」となります。
なぜこのようなことができるのか、この数式を分解して解説していきます。
① DROP関数｜先頭の空白行を削除
DROP関数とは、指定した配列から、先頭または末尾の行・列を「除外」する関数です。
これがなぜ必要かというと、REDUCE関数で初期値が空白データとして積まれてしまうからです。
実際にDROP関数を外した場合のデータが以下の通りです。
先頭に空白行が入ってしまっていることが確認できると思います。
この空白行を削除するために、DROP関数を使っています。
② REDUCE×LAMBDA｜シートを順番に処理して蓄積
REDUCE関数とは、配列の要素を1つずつ処理しながら累積計算を行い、最終的に1つの結果にまとめる関数です。
※注意※REDUCE関数はLAMBDA関数を前提に設計された関数のため、ほぼ単体で使うことは使えません）
REDUCE関数の構文は以下のとおりです。
LAMBDA関数と組み合わせることがベースとなっている関数です。
REDUCE( "", array, LAMBDA )
簡単に言うと、シート名を1つずつ取り出し、順番にLAMBDA処理をしています。
LAMBDAでは、パラメーター（x,y）を設定しています。
パラメーター（x,y）はそれぞれ以下の役割を持っています。
・x：これまでに積み上がったデータ（途中結果）
・y：現在処理しているシート名
つまり、各シートのデータを順番に処理しながら、xにどんどん蓄積していくイメージです。
パラメーターがどのように設定されているかは、以下のイメージをご確認ください。
つまりREDUCE × LAMBDA関数を使うことで、
① 空白（初期値）と1月シートデータを結合
② ①の結果と2月シートデータを結合
③ ②の結果と3月シートデータを結合
と各シートをループして、データを蓄積することができるというわけです。
ちなみにLAMBDA関数は非常に応用の効く関数で、使い方次第でかなり活躍する関数です。
別の記事でLAMBDA関数に触れているので、興味のある方はチェックしてみてください。
👉 LAMBDA関数に関連した記事はこちら
③ FILTER関数｜シート名一覧を取得
FILTER(シート一覧!A:A,シート一覧!A:A<>"")
これは、「シート一覧」シートのA列に記載した、空白を除いた一覧（シート名）を取得しています。
つまり、以下枠内の一覧をシート名として読み込むための数式です。
FILTER関数は非常に便利な関数です。
以下記事にて基本的な使い方から応用方法まで解説しています。
👉 FILTER関数の活用例をまとめた記事はこちら
④ INDIRECT関数｜シート参照を動的に生成
INDIRECT関数は、文字で書かれたセル番地を、本物のセル参照として使えるようにする関数です。
例えば、以下のように文字列で記述することで、セルA1を参照することができます。
=INDIRECT("A1")
INDIRECT関数は便利な関数で、特にシート参照など活用できる機会も多いです。
以下の記事で活用例を紹介しておりますので、ぜひチェックしてみてください。
👉 INDIRECT関数の活用例を紹介している記事はこちら
ここでは、INDIRECT関数を以下のように使用します。
INDIRECT("'"&y&"'!A2:D1000")つまりこの数式を実行していくと、
'1月'!A2:D1000
'2月'!A2:D1000
'3月'!A2:D1000
のように指定することができます。
- INDIRECT関数は便利な反面、ブックが重くなる原因になることがあります。
- シート数やデータ量が多い場合は、動作が遅くなる可能性があるため注意が必要です。
⑤FILTER関数｜空白行を除外
次に統合するデータの範囲を、FILTER関数で指定します。
FILTER(
INDIRECT("'"&y&"'!A2:D1000"),
INDIRECT("'"&y&"'!A2:A1000")<>""
)ここでは、統合する各シートから
・「A2:D1000」範囲から、「A2:A1000」が空白でないもの
を条件としてフィルターを書けています。
ここは実際のデータ条件に合わせて変更するべきポイントです。
・データ範囲が1000行目以降にもある → 1000行目以降に範囲指定をする
・A列が空白のデータもある → 空白がない列を参照するようにする
など、必要に応じて条件を変更してください。
⑥ VSTACK関数｜データを縦に結合
ここでようやくVSTACK関数の登場です。
先ほどINDIRECT関数で指定した参照範囲を、VSTACK関数で積み上げます。
VSTACK(x, 新しいデータ)
こうすることで、前の結果の下に、次のシートのデータを追加することができます。
この方法のメリット｜シートが増えても自動対応
この方法の最大のメリットは、
・シートが増えても自動でデータ統合が反映される
・各シートの範囲が違っても自動的に必要な範囲を結合する
ことができる点です。
この数式を活用できるケースは多いと思います。
実際に活用が想定できるケースを紹介します。
活用例①：月別売上の統合
この方法は、月別データの統合に非常に向いています。
例えば、
・月別売上
・月別経費
など、シート別に月別の売上や経費データがまとまっていることは多いですよね。
このような場合、毎月シートが増えていくとなると、毎回データを手動で蓄積しないといけなかったり
して、手間がかかってしまいますよね。
そういう時には、この方法だとシート名を追加するだけで自動的にデータを統合することができます。
非常に便利で強力な方法です。
活用例②：担当者別の日報を統合
この方法は、担当者別シートにも非常に向いています。
例えば、以下のように担当者別のシートにそれぞれの日報を入力していたとします。
先ほどの数式を使うことで、簡単にデータを統合することができます。
そこに、新しく鈴木さんが入ってきた場合、こちらもシート追加するだけで自動的にデータを統合することができます。
人が増える現場では、かなり便利な方法です。
注意点｜失敗しやすい3つのポイント
この方法には、いくつかの注意点があります。
① 各シートの列構成は同じにする
データを統合する際は、各シートの列構成は同じにしておく必要があります。
そうしないと、同じ列に違う情報が混ざってしまう可能性があります。
| A | B | C |
のように、同じ列構成であるか、事前に確認しましょう。
② データは同じ範囲に置く
データは、同じ列範囲に置く必要があります。
列構成は同じでも、シート1ではA列から、シート2ではB列から、などであればデータをうまく統合することはできません。
同じ位置に配置されているかどうか確認しておきましょう。
③ A列が空白の行は除外される
紹介した数式では、A列が空白であるかどうかを判断しています。
つまり、A列が空白の場合、それ以降にデータが入っていてもデータを拾うことができません。
データがあるかどうかを判定する列には、空白データが存在しないようにしましょう。
もう数式を直さない！シート追加だけで自動統合する方法まとめ
VSTACKは非常に便利な関数ですが、シートが増えるたびに式を修正するという弱点があります。
しかし今回紹介した方法を使えば、
・担当者が増えても
・月が増えても
シート名を追加するだけで、自動的にデータを統合することができます。
月別データや担当者別データを扱う方には、特におすすめのテクニックです。
このような“手作業を減らす関数テクニック”を知っているかどうかで、作業効率は大きく変わります。
ぜひ一度、試してみてください。
なお、複数シートの統合はPower Queryでも可能ですが、
「関数だけで完結したい」「リアルタイムに反映したい」場合は、
今回の方法がシンプルで扱いやすいです。
最後まで読んでいただき、ありがとうございました。
コメント