VSTACK関数で結合するリストを動的にする
VSTACK関数は、複数のリスト(表)を縦に結合してくれる関数です。
結合する各元データは、上図のように、"テーブル"形式にしてください。それがセオリーです。元データがテーブルではなく、さらに各元データの件数(行数)が不定の場合は、下記のページで解説している"トリム参照"を使ってください。
このVSTACK関数は、実務でメッチャ重宝しますし、スピル系関数のデモで紹介すると、ほとんどの方が「おぉ～」と感嘆する鉄板です。なんですけど、以前からひとつ不満がありました。VSTACK関数で結合する元データって、たとえば"月別"とか"支店別"や"商品別"などのように、それぞれ種類別リストのはずです。もし"月別"データだったら、翌月には新しい元データ(テーブル)が追加されますよね。そしたら、VSTACK関数の引数を修正しなければなりません。これ、毎回手作業でやるのって、ちょっと効率悪いです。VSTACK関数で「ブック内に存在する全テーブルを、自動的に縦結合する」ってのは不可能です。ちなみに、自動的に縦結合するのでしたら、Power Queryを使ってください。やり方は、下記のページに詳しく書きました。
余談ですが、この手の話をすると、よく「VSTACK関数でやるのと、Power Queryでやるのは、どちらがいいですか？」と質問されます。どちらがいいかは、あなたのスキルや、職場環境や、業務の内容などによって決まります。単純に、両者のポテンシャルで決まるような話ではありません。複数のやり方を解説すると、かなりの頻度で"どちらがいい？"って聞かれます。私の経験では、このときの質問者さんは「今はまだ、どちらもよく分からないけど、これからやってみようかと。で、私はどちらのルートを進めばいいですか？」みたいな真意が多いです。だったら、"どちらがいい？"って思った時点でNGです。正解は「両方できるようになってるのがいい」です。まずは、両方とも学習してください。最初から、片方のルートを捨てないでください。道具は、ケースによって使い分けます。実務では、できるだけ多くの選択肢を持つように心がけてください。
さて、そんな選択肢のひとつとして、VSTACK関数で結合する元データを、動的に指定する方法をご紹介します。たくさんの関数を組み合わせますので、ちょっと難しいですが、がんばってマスターしてください。ここでは、次のような動作を目指します。
まず、ブック内に複数のテーブルがあります。
これらのテーブルを、別シートのVSTACK関数で結合します。
F列には、結合するテーブルを指定するための領域を作ります。ここもテーブルです。名前は「List」としました。「List」には、結合したいテーブルの名前を入力します。「List」を編集することで、結合する順序を指定したり、結合するテーブルの追加・削除ができます。なお、各テーブルの名前は、テーブル内にアクティブセルを置き、[テーブルデザイン]タブ左端の[テーブル名]で確認できますし、任意に変更も可能です。
「テーブル名」は、一般的に"テーブル1"や"テーブル2"のように便宜的な名前が設定されますが、適時分かりやすい名前に変更することをお勧めします。なお、ここで指定した「テーブル名」は、セルに指定する"名前機能"の「名前」としても登録されます。"名前機能"の名前一覧は、[数式]タブ[定義された名前]グループの「名前の管理」ボタンをクリックするか、Ctrl+[F3]キーを押して表示される[名前の管理]ダイアログボックスで確認できます。
文字からセル範囲を参照する
テーブル「List」に入力されているのは"Data1"などの文字列です。テーブル内のセルを、ただ参照するだけでは、その結果も文字列になります。
この、文字列である"Data1"を使って「"Data1"という名前のテーブル」を参照するには、INDIRECT関数を使います。
最終的にやりたいのは、テーブル「List」内に指定した各テーブル名を、VSTACK関数で縦に結合することです。これには、LAMBDA関数と、LAMBDA関数のヘルパー関数であるREDUCE関数を使います。
すみません、ここではLAMBDA関数とREDUCE関数の動作に関して詳細な解説は割愛します。なお、このやり方では、上図のように1行目がエラーになります。これはREDUCE関数の仕様なので、どうしようもありません。なので、DROP関数で1行目を削除します。
=DROP(REDUCE("",List,LAMBDA(a,b,VSTACK(a,INDIRECT(b)))),1)
これで完成です。
ブック内に、新しいテーブルを追加したときは、テーブル「List」に登録してください。
結合するテーブルを選択する
せっかくですから、もう少し拡張しましょう。上記の方法では、テーブル「List」に登録したテーブルがすべて縦結合されます。テーブル「List」内で位置を入れ替えれば、結合される順番も変わります。これはこれで便利ですけど、じゃぁ、結合するテーブルを選択できるようにしてみます。
まず、結合するテーブル名を登録する「List」を編集します。上記手順で、すでに「List」を作っている方は、「List」内のセルを右クリックして、表示されるメニューから[挿入]-[テーブルの列(左)]をクリックします。
実行すると「リスト」列の左に新しい列が挿入されます。ここでは、新しい列の名前を「選択」に変更しました。これは、必須ではありませんが、あとで数式内で列名を使うので、分かりやすい方がいいでしょう。
挿入した「選択」列にチェックボックスを挿入します。挿入したいセル範囲を選択し、[挿入]タブ[コントロール]グループの「チェックボックス」をクリックします。
実行すると、選択したセルにチェックボックスが挿入されます。
このチェックボックスは、Microsoft 365からインストールしたProPlusで使用できます。Excel 20xxなどの永続ライセンス版でしたら、別の方法でチェックボックスを挿入できますが、手順が煩雑になるので、今回はこちらで解説します。セルに挿入したチェックボックスは、マウスのクリックだけでなく、セルを選択してスペースキーを押すことでオンとオフを切り替えられます。
チェックボックスの状態(オンかオフか)を切り替えると、そのチェックボックスが挿入されているセル内に「オン→TRUE」「オフ→FALSE」という値が入力されます。
したがって、FILTER関数で、チェックボックスがオンの「リスト」だけを抽出できます。
実際には「=FILTER(List[リスト],List[選択])」のように"=TRUE"がなくても動作しますけど、書いた方が分かりやすいです。さて、このようにFILTER関数で「チェックボックスがTRUEのテーブル名」だけを抽出できるので、これを先の数式のうち「List」部分に指定します。
=DROP(REDUCE("",FILTER(List[リスト],List[選択]=TRUE),LAMBDA(a,b,VSTACK(a,INDIRECT(b)))),1)
今回は、このくらいにしておきましょう。キリがないですからねｗ VSTACK関数は確かに便利です。スピル系関数の中で、気になっている方も多いのでは？機能としては画期的ですが、本稿のように「結合したいリスト(テーブル)を、動的に変更したい」となると、けっこう難しくなります。まぁ、LAMBDA関数の使用例の中では、比較的簡単な方なので、チャレンジしてみてはいかがでしょう。