Excelで資料を作成する時、大きな1枚のワークシートへ全ての情報をつめ込まず、小さなテーブルや、複数のワークシートへ情報を分散させることがあります。検索性を高めたり、万が一ファイルがこわれても、ダメージを低減させられるためです。
複数のテーブルやワークシートにちらばった情報を結合させて、より複雑な資料を作り上げる作業も、多くの職場で一般的でしょう。
たとえば、「商品台帳」と「顧客台帳」の必要情報を結合し、顧客別の納品書や請求書をつくるような作業のことです。
複数のワークシートを結合する主な手段としては、パワークエリ・エディタを使ったマージや、VBAマクロ・プログラムによる転記自動化などがあります。
しかし、Microsoft 365ユーザーなら、パワークエリやVBAを使わなくても、Excel関数ひとつで、ラクラク結合できるってご存知でしたか?
今回は、2022年夏以降、Microsoft 365版ExcelやExcel 2021で使えるようになったSTACK関数の活用法をご紹介します。
STACK関数は、タテ方向、ヨコ方向に情報を「つみかさねる」関数です
カタカタ語「スタックする」は、ややネガティブな使われ方をします(仕事がスタックする = とどこおる、など)。これは、雪道での車のスタック(雪やぬかるみにタイヤがはまり、身動きできなくなること)などから連想された意味合いです。
ところが、元の英単語stackにネガティブなニュアンスはなく、単に「つむ」「つみかさね」という意味でした(転じて「仕事が山づみになる」という使い方もします)。
2022年夏以降、Microsoft 365やExcel 2021などに搭載されたSTACK関数は、原語の意味どおり、垂直方向(V)、もしくは水平方向(H)に、複数の資料や情報を「つみかさねる」ための関数です。
構文は、
=VSTACK(array1, array2, …)
もしくは、
=HSTACK(array1, array2, …)
※(array1, array2, …は、結合する配列名を指定する引数
となります。
たとえば、
1枚のワークシートに表が2つあった場合、次のようなVSTACK関数を(ここではセルB7へ1つだけ)入力します。
同一関数が、必要な範囲へ自動的にスピルする(流れ込む)「ダイナミック配列」機能により、自動的に2表の結合された新表が生成されました。
STACK関数なら、複数ワークシートにまたがる資料つみかさねも楽勝です!
2表をつみかさねるだけなら、コピー&ペーストすればいいのですが、ここまでシンプルな業務は、あまり現実的ではありません。実際には、集計用データを、”月単位”や”営業所単位”などに分割していることが多いのではないでしょうか? そのような場合にこそ、STACK関数が威力を発揮するのです。
たとえば、月間売上レポートが、1ヶ月1ワークシートに分割されて記録されているとします。この月次ワークシートを結合して、四半期計や年計を算出する必要があるものとします。
VSTACK関数の引数に、’ワークシート名!を複数指定すれば、あっという間に、複数ワークシートの情報が結合(つみかさね)されます。
※この場合も、VSTACK関数の引数に指定する配列の”列数”を合わせる必要があります。
月間の売上報告件数は、今月が10件、別の月は20件と凸凹が生じるのが普通です。
それでも、各ワークシートの情報をモレなく結合させるためには、VSTACK関数の引数参照範囲を、少し多めに指定しておきましょう。
参照ワークシートにデータが存在しない行がある場合には、結合後のリストに0値が表示されます。
しかし、以前ご紹介したFILTER関数を併用すれば、ワークシートごとの売上報告件数が凸凹でも、0値を無視して、各月件数分のみを、忠実に結合させることができます。
最後に、このVSTACK関数の構文が劇的にわかりやすくなり、職場内の誰に引きついでも明確に作業を再現できる方法をお教えしましょう。それが、テーブルの使用です。
STACK関数の引数へは、テーブル名をそのまま入れられます
いくら月々の売上報告件数に凸凹があっても、月次で締めた時に、月次データをテーブル化しておいたらどうなるでしょうか?(テーブル名は、”January”、”Feburuary”、”March”…でも、略字”Jan”、”Feb”、”Mar”…でもかまいません)
前章で、FILTER関数と入れ子にして、
=FILTER(VSTACK(‘1月’!A2:E30,‘2月’!A2:E30,‘3月’!A2:E30),VSTACK(‘1月’!A2:E30,‘2月’!A2:E30,‘3月’!A2:E30)<>””)
と記述していた引数が、ワークシートを事前にテーブル化しておくことで、次のように替わります。
=VSTACK(Jan, Feb, Mar)
さて、どちらがシンプルで、説明しやすい関数式でしょうか?
一目瞭然ですね。
このように、12ヶ月分のシート結合でもよいですし、
=VSTACK(Tokyo, Osaka, Nagoya)
営業所別の実績積み上げでもよいですし、
=VSTACK(Satou, Tanaka, Suzuki)
営業マン別成績表マージ(結合)にも使えます。
必要な情報をうまくテーブル化して、直感的なテーブル名を付けられていれば、VSTACK関数の構文は非常にシンプルに、同僚などへも引きつぎやすい資料になるのです。
まとめ;Microsoft 365以降のExcelをお使いなら、VSTACK/HSTACK関数も使ってみましょう!!テーブル化と合わせ、パワークエリ要らずのラクラク資料結合!
せるワザ内では、ここ数年内に実用化された新関数(XLOOKUP/FILTER/UNIQUE etc.)の使い方についても、何度かご紹介してきました。旧関数を含めれば、使用できる関数の種類は500種を超えたという情報もあり、すべておぼえきれず、また、すべてを使いこなすこともむずかしいでしょう。
しかし、近年採用された新関数は、いずれも非常にシンプルかつ強力なものが多いので、作成する資料の目的に合致していれば、労力軽減の強い味方になってくれます。
今回は、日常業務でよく発生する「複数ワークシートの結合」が、パワークエリやVBAを使わなくても(もちろん手作業コピペでなくても!)、関数だけで処理できる可能性もあるのだ、ということを覚えておきましょう。
そして、テーブル化と組み合わせ、面倒くさい関数の入れ子をしなくてもパワークエリ並みの資料を作れることがわかれば、「こりゃ使ってみたいな」と思える場面も出てくるのではないでしょうか?
“習うよりなれろ”で、ぜひいろいろなワークシートをスタック(つみかさね)してみてください。なれてくるほど「仕事のスタック」は解消するかもしれませんよ!?
