垂直方向・水平方向へ配列を追加するVSTACK関数・HSTACK関数の使い方:Excel関数

スポンサードリンク

作成:2022/9/6,更新:2023/3/12

VSTACK関数とHSTACK関数

VSTACK関数で串刺し計算(集計)にトライ

垂直方向へ配列を追加する
　ブイ　スタック
=VSTACK(array1,array2,...)

水平方向へ配列を追加する
　エイチ　スタック
=HSTACK(array1,array2,...)

VSTACK関数・HSTACK関数がExcel for Microsoft 365 で使用することができるようになりました。(2022/9/2に確認しました)

VSTACK関数・HSTACK関数の引数

関数の引数は現時点では英語表記になっています。

VSTACK関数・HSTACK関数

引数		意味
array1	必須	セル範囲や配列
array2	省略可	追加するセル範囲や配列

【使用例】

B2:E6セルをテーブルに変換しています。テーブル名は　鹿児島　です。
G2:J6セルをテーブルに変換しています。テーブル名は　指宿　です。
この2つのテーブルを垂直方向へ結合します。
B9セルには　=STACK(鹿児島,指宿)　と入力しています。
テーブルでは先頭行のタイトル行は無視され、データのみが結合されます。

適切なデータとは思えませんが、同じデータを使って説明すると、
水平方向へ結合する場合は、=HSTACK(鹿児島,指宿)　と入力します。
下図のように、水平方向へデータが結合されます。

結合した配列からデータを検索する例

2つのテーブルの商品名が「バナナ」のデータを取り出してみます。
データを抽出するのに FILTER関数を利用します。
2つのテーブルの結合は VSTACK関数を使います。
FILTER関数の引数の抽出条件に「3列目がバナナと等しい」とするために　CHOOSECOLS関数を使います。
B9セルの数式は　=FILTER(VSTACK(鹿児島,指宿),CHOOSECOLS(VSTACK(鹿児島,指宿),3)="バナナ")　としました。
CHOOSECOLS関数の詳細な説明は　指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方　をご覧ください。

列数が異なる配列を結合すると?

2R×3C　の配列と　3R×2C　の配列を垂直方向へ結合すると、5R×3C の配列になります。
空欄となるセルには #N/A が入力されます。
B6セルには　=VSTACK(B2:D3,F2:G4)　と入力しています。

2R×3C　の配列と　3R×2C　の配列を水平方向へ結合すると、3R×5C の配列になります。
空欄となるセルには #N/A が入力されます。
B6セルには　=HSTACK(B2:D3,F2:G4)　と入力しています。

これらのエラーを表示したくない場合は、IFERROR関数を使います。
=IFERROR(VSTACK(B2:D3,F2:G4),"")　としてエラーを非表示にすることが可能です。

VSTACK関数で串刺し計算(集計)にトライ

串刺し計算は列見出しと行見出しが同じ順番に並んでいる場合に使用できます。
同じセル位置に4月～6月のデータがあります。

これを串刺し計算で行うと、C3:E5セルを選択して、数式バーに =SUM('4月:6月'!C3) と入力して、[Ctrl]+[Enter]で数式の入力を確定します。

スピルが利用できる場合は　='4月'!C3:E5+'5月'!C3:E5+'6月'!C3:E5　とすることもできます。

VSTACK関数を使うと、下図のような感じになります。
=SUM(FILTER(FILTER(VSTACK('4月:6月'!$B$3:$E$5),
CHOOSECOLS(VSTACK('4月:6月'!$B$3:$E$5),1)=$B13),
COUNTIF(C$12,$B$12:$E$12)))
かなり面倒ですが、セルの位置が各シートで異なっていたり、行見出しが同じでなかったりする場合には、上記の串刺し計算では計算できません。
そのような場合はこちらの出番となります。(実は、ピボットテーブルの方が楽ですが・・・)

VSTACK関数で疑似串刺し計算(集計)を行う

ただ、串刺し計算はデータの位置関係がすべて同じ位置にある必要がありますが、ここでは列見出しの順番が同じである必要があります。
行見出しが異なっているのが前と異なっています。7月のB5セルはマンゴーとなっています。8月,9月はバナナです。
また、行データの増減にも対応できます。

ここで使用したデータ
3つのシートのデータはすべてテーブルにしています。データの増減と数式の見易さのためです。

シート「7月」に「七月」というテーブルを作成しています。

同じように、シート「8月」に「八月」というテーブルを作成しています。

同じように、シート「9月」に「九月」というテーブルを作成しています。

操作手順

集計するシートのC2:E2セルに列見出しを入力します。
B3セルに3つのテーブルの重複しない行見出しを書き出します。
B3セルの数式は　=UNIQUE(CHOOSECOLS(VSTACK(七月,八月,九月),1))　としています。
3つのデータの結合は　VSTACK(七月,八月,九月)
この結合したデータから行見出しの列を　CHOOSECOLS(VSTACK(七月,八月,九月),1)　で取り出しています。
ユニークな行見出しは　UNIQUE関数で取り出しています。
これらの関数の詳細な使い方は　CHOOSECOLS関数　 UNIQUE関数　をご覧ください。
C3セルの数式は
=SUM(FILTER(FILTER(VSTACK(七月,八月,九月),
CHOOSECOLS(VSTACK(七月,八月,九月),1)=$B3),
COUNTIF(C$2,$B$2:$E$2)))
としました。

数式の説明
結合したデータ　VSTACK(七月,八月,九月)　から「りんご(B3セル)」のデータを取り出します。
数式は　FILTER(VSTACK(七月,八月,九月),CHOOSECOLS(VSTACK(七月,八月,九月),1)=$B3)　としました。
Filter関数で　データ　VSTACK(七月,八月,九月),から　1列目が「りんご($B3)」であるデータを取り出しています。

取り出したデータでりんごの合計をするために、2列目の　128～131の列を取り出します。
FILTER(FILTER(VSTACK(七月,八月,九月),
CHOOSECOLS(VSTACK(七月,八月,九月),1)=$B3),
COUNTIF(C$2,$B$2:$E$2))
,
COUNTIF(C$2,$B$2:$E$2)は{0,1,0,0}　といった配列を返しますので、2列目の　128～131の列を取り出すことを指定しています。
これで　128～131が取り出せたので、データを合計すればよいので、SUM関数でくくればよいことになります。
C3セルに入力した数式を右方向、下方向へコピーすれば完成です。
8月のデータに　パイナップルを追加してみました。
数式を 7行目までコピーすると、計算結果が表示されます。
データの追加にも対応しているのも確認できました。