VSTACK 関数 / HSTACK 関数

  1. Home
  2. Excel
  3. 機能と数式
  4. ワークシート関数

ここで解説する VSTACK関数 と HSTACK関数 は、ProPlusに追加された関数です。Excel 2016/2019/2021では使用できませんのでご注意ください。また、本稿執筆時点(2022/3/20)では、まだInSider Programに実装されただけですので、製品版で使えるようになるには、まだ少し時間がかかると思います。なお、この関数については、YouTubeの動画でも解説しています。ぜひ、ご覧ください。また、この関数では「スピル」という機能が使われています。スピルに関しては「Excel 2016レビュー[Excelの使い方が激変する「スピル」]」をご覧ください。

VSTACK関数 / HSTACK関数

STACKとは、"積み重ねる"などを意味する英単語です。先頭のVは、垂直を意味するVerticalの略で、Hは水平のHorizontalです。つまり、複数のセル範囲や配列を、VSTACKは「垂直方向に積み重ね」HSTACKは「水平方向に積み重ね」る関数です。

【VSTACK関数】

【HSTACK関数】

動作自体は、それほど難しいイメージではありません。引数も単純です。

スタックしたいセル範囲や配列を、ひたすらカンマで区切って指定するだけです。いくつのセル範囲をスタックできるのか、ヘルプには書かれていないので試してみました。50個のセル範囲を指定して、何も問題はなかったので、ワークシート関数の一般的制限である「引数は255まで」なのかなぁ～って感じてます。まぁ、いずれにしても「これ以上はスタックできません」みたいなエラーには遭遇しないのではないかと。

今回(2022年3月)、3つのテキスト操作系関数と、11個のセル範囲(配列)操作系関数が追加されたのですが、個人的には、このVSTACK関数が鬼のように便利だと感じます。いくつか、思いつくままに利用例をご紹介します。

上図で説明します。セル範囲A1:B6とセル範囲D1:E6はテーブルにしています。名前はそれぞれ「Data1」と「Data2」にしました。ここでは分かりやすく、2つのテーブルを同じシート内に作成していますが、もちろんこれは、別のシートにあってもいいです。言うまでもありませんけど、テーブルではなく通常のセル範囲でも問題はありません。さて、セルH2にVSTACK関数を入力しました。2つのテーブル「Data1」と「Data2」を縦にスタックしています。このように、複数のテーブルや表であっても、VSTACK関数によって1つのリストにできれば、そのリストに対してVLOOKUP関数などを実行できます。

テーブルにしておけば、それぞれの表にデータの増減があっても問題ありません。

下図は、2つのテーブルをスタックして、2列目(数値)を昇順で並べ替えました。もちろん、データの増減や数値の変更にも対応します。

下図では「Data1」と「Data2」に同じ名前が入力されています。ただし、並び順は異なります。ここから、特定のデータだけを取り出します。もし、二つのリストが"縦に繋がっていたら"、FILTER関数で一発ですよね。だったら、VSTACK関数でスタックしちゃいましょう。

ちょっと数式が長いですかね。LET関数などと組み合わせると、少しは短くなるかも。

CHOOSECOLS関数については、別のコンテンツで詳しく解説します。

ここまで、簡単なケースをご紹介しました。実際には、もっと大きいリスとなどをスタックさせて、そこに対して何かするのでしょうね。もちろん、スタックさせるテーブルや表は、別のシート上に存在してもかまいません。他ブックのデータもスタックできます。

取得と変換(Power Query)で読み込んだ複数のテーブルを1つにスタックして、そこからピボットテーブルを作成するなど、いろいろと考えられますね。

いろいろ試してみた

応用範囲の広いVSTACK関数は、さまざまな用途で活躍することでしょう。なので、いろいろなケースを試してみました。まずは「スタックさせるリストの列数が異なっていたら」です。

データがないところは「#N/A」エラーになります。このエラーは、IFERROR関数で対応可能です。

「非表示の列が含まれていたら」どうでしょう。

非表示の列であっても、同じようにスタックされますね。では「セルが結合されていたら」を試してみます。素晴らしいことに、テーブル内のセルは結合できませんので、ここは一般的なワークシートでやります。

セルの結合は解除されます。結合されていたセルには値が入っていません。空欄セルです。すると、そこには"0"が表示されます。これは、FILTER関数と同じ仕様ですね。どうしても、空欄セルに"0"を表示したくないのでしたら、次のように文字列化してください。

FILTER関数と同じ仕様ということは、日付の表示形式は引き継がれないのでしょうか。

表示形式は引き継がれません。VSTCK関数の結果に、表示形式などの書式を設定してください。

こんな感じですね。VSTACK関数は、結果が分かりやすいですから「じゃ、こんなケースでは？」ってときは、実際に試してみてください。最後に、上記の検証をしていて、ふと思いました。スタックした結果が「ワークシートの大きさ(1,048,576行)を超えたら」どうなるのでしょう。やってみます。

A列とB列に、1,000,000行のデータを入力しました。この2列を縦にスタックしてみます。

まずは、行数の制限である1,048,576行のデータをスタックしました。COUNTA関数は、正常な結果を返しています。では、行数制限を超えるデータをスタックしてみましょう。

変な結果になりました。念のため、SUM関数も使ってみますか。すべての値を"1"にして、その合計を求めます。

うまくいきません。COUNTA関数とSUM関数が正常に動作しないのですから、他の関数なども同じでしょう。

今日は、このくらいでかんべんしてやりましょう。いずれにしても、今まで「複数のリスト(表)が1つになっていたらなぁ～楽なのになぁ～」って思う場面は少なくありませんでした。これからはもう、そんな悩みや苦労とは無縁のExcelライフを送れそうです。なお、今回は詳しく解説しませんでしたが、横方向にスタックするHSTACK関数も、基本的には同じです。冒頭でご紹介したYouTubeの動画では、HSTACK関数を使う例もお見せしていますが、正直言って、HSTACK関数を使う機会は、VSTACK関数に比べて少ないのではないかと感じています。