【新関数】エクセル VSTACK/HSTACK関数の使い方

【新関数】エクセル VSTACK/HSTACK関数の使い方

Microsoft365環境のエクセルでは、2022年8月下旬頃からVSTACK関数が使えるようになりました。

VSTACK関数を使うと、複数の配列やセル範囲を縦に結合できます。

この例では、A～C列に入力されている東京営業所のデータと、E～G列に入力されている大阪営業所のデータを縦につなげました。

同じようにHSTACK関数を使うと、複数の配列やセル範囲を横に結合できます。

この例では、元々のA列～B列、C列の順番を入れ替えて、左からC列、A列、B列の順番になるようにHSTACK関数を使いました。

どちらの関数も、数式を入力したセルだけでなく、その右または下のセルにも計算結果が表示されることに注意してください（スピルといいます）。

VSTACK関数の書式

VSTACK関数の引数の意味

VSTACK関数を使うときには、結合したい配列やセル範囲を好きな数だけ指定します。

=VSTACK(A2:C4 , E2:G3)
①セル範囲、配列1A2～C4セル
と

②セル範囲、配列2E2～G3セル
を縦に結合する

引数名 意味 例
①セル範囲または配列
(array1)		 結合したいセル範囲・配列を入力します A2:C4
②セル範囲または配列
(array2)		 結合したいセル範囲・配列を入力します E2:G3
・・・ （以下、結合したいセル範囲・配列の数だけ繰り返す） ・・・

HSTACK関数も、引数の指定方法はまったく同じです。

VSTACK関数、HSTACK関数の用途

複数シートのデータを1つにまとめる

VSTACK関数を使うと、複数シートに分散したデータを1つのデータにまとめられます。今までは、VBA、Power Queryを使うか手作業で行うしかなかった作業が、数式で簡単に処理できるようになります。

複数シートの串刺し集計にも対応しているので、うまく使うと少ない記述量で、一気にデータをまとめることもできます。

配列操作関数の計算結果を1つの配列にまとめる

また、FILTER関数・UNIQUE関数やMAP関数など、配列操作関数を使って複雑な処理を書くときにVSTACK関数やHSTACK関数を使うと、できあがった配列を自由に結合できるようになります。

こちらは、配列操作関数を使いこなすのが非常に難しいため（ほとんど曲芸の域？）、実務的には、使う機会はあまりないでしょう。

VSTACK関数、HSTACK関数を使うときの注意点

VSTACK関数を使うときは列数、HSTACK関数を使うときは行数を合わせる

VSTACK関数を使うときには、指定するセル範囲または配列の列数を、できるだけ一致させるようにしましょう。

列数が違う範囲・配列を結合すると、列数が足りない部分には「#N/A」エラーが表示されます。

=VSTACK(A2:C4 , E2:F3)

エラーを消したいときには、IFERROR関数と併用しましょう。

=IFERROR(VSTACK(A2:C4 , E2:F3) , "-")

VSTACK関数のいろいろな使用例

VSTACK関数、HSTACK関数の代表的な使用例をいくつか紹介します。

VSTACK関数で十分下の行まで指定する方法

先ほど紹介した方法では、VSTACKで結合する範囲を各シートのデータとまったく同じになるように指定をしました。ただ、このままでは、実際の入力行数が変わるごとにVSTACKの数式を変更する必要があるため、実用的ではありません。

そこで、実際に入力されている行数よりも十分大きい行数を入れて、各シートの行数が変わってもVSTACKの数式を変更しないで済むようにしましょう。

とはいえ、単に行数を増やしただけでは、次のように「0」のデータが混じってしまいます。

=VSTACK(A2:C5 , E2:G5)

このような場合には、FILTER関数で余計な行を取り除きましょう。

=FILTER(I2# , CHOOSECOLS(I2# , 1)<>0)
①配列I2～K9セル「I2#」
から

②検索条件（含む）I2～I9セル「CHOOSECOLS(I2#,1)」が「0」以外の行
を探す

該当ないときは
③空の場合「#CALC!」エラー
を表示する。

このFILTER関数を使った数式は、少し複雑なので解説をします。

「I2#」の「#」の意味

1つ目の引数の「I2#」の「#」は、スピルした全体を表す文字です。

つまり、「I2#」は、I2セルからスピルをした範囲全体（今回の場合は、I2～K9セル）を表します。

CHOOSECOLS関数では何をやっているのか？

次に、2つ目の引数の「CHOOSECOLS(I2#,1)<>0」を考えてみましょう。

CHOOSECOLS関数は、「指定した範囲や配列」の「指定した列」を取得する関数です。今回は、「I2#」（＝I2セルからスピルしたI2～K9セル）のうちの1列目（＝I2～A9セルの部分）を取り出す指定をしていることになります。

結局、2つ目の引数の「CHOOSECOLS(I2#,1)<>0」は、「I2～I9セルが0以外の行」という条件を表すことになります。

これで、目的のデータを取り出すことができました。

VSTACK関数でシートの串刺し指定

VSTACK関数やHSTACK関数では、シートの串刺し指定ができます。

たとえば、次のように東京シート、大阪シート、福岡シートに、今回と同じようなデータが入っている場合を考えてみます。

このとき、集約シートに次の数式を入力すると、東京、大阪、福岡シートのA2セル～C5セルのデータを結合できます。

=VSTACK(東京:福岡!A2:C5)

数式の入力の仕方自体は、SUM関数で串刺し集計をするときとまったく同じです。下記のようにして数式を入力できます。

  • 「=VSTACK(」と入力
  • 東京シートのA2～C5セルを選択
  • Shiftキーを押しながら福岡シートを選択
  • 「)」と入力
  • 「Enter」キーを押す

この方法を使えば、元データがたくさんのシートに分けて入力されている場合でも、最初と最後のシート名を指定すれば、すべてのシートのデータを取ってくることができます。

あとは、先ほどと同じようにFILTER関数を使えば、目的のデータを取り出すことができます。

=FILTER(A2# , CHOOSECOLS(A2# , 1)<>0)

Microsoft公式ページ

Microsoft公式ページへのリンクを掲載しておきます。公式の情報を確認したいときには、下記リンクからご覧ください。

Microsoftの公式ページ（日本語）

まとめ

複数のセル範囲や配列の値を縦につなぎたいときにはVSTACK関数、横につなぎたいときにはHSTACK関数を使いましょう。

同じようなデータが大量のシートに分散しているときには、串刺し集計を上手に使うと、簡単な数式でデータを結合できます。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座（初級編）」（動画マニュアル　総収録時間162分）を無料プレゼント中です！

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます！

無料動画講座　登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。

集計vstack関数, hstack関数