スポンサードリンク | |
作成:2022/9/6,更新:2023/3/12
VSTACK関数とHSTACK関数 |
VSTACK関数で串刺し計算(集計)にトライ |
VSTACK関数・HSTACK関数がExcel for Microsoft 365 で使用することができるようになりました。(2022/9/2に確認しました)
関数の引数は現時点では英語表記になっています。
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),"") としてエラーを非表示にすることが可能です。
串刺し計算は列見出しと行見出しが同じ順番に並んでいる場合に使用できます。
同じセル位置に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)))
かなり面倒ですが、セルの位置が各シートで異なっていたり、行見出しが同じでなかったりする場合には、上記の串刺し計算では計算できません。
そのような場合はこちらの出番となります。(実は、ピボットテーブルの方が楽ですが・・・)
ただ、串刺し計算はデータの位置関係がすべて同じ位置にある必要がありますが、ここでは列見出しの順番が同じである必要があります。
行見出しが異なっているのが前と異なっています。7月のB5セルはマンゴーとなっています。8月,9月はバナナです。
また、行データの増減にも対応できます。
ここで使用したデータ
3つのシートのデータはすべてテーブルにしています。データの増減と数式の見易さのためです。
シート「7月」に「七月」というテーブルを作成しています。
同じように、シート「8月」に「八月」というテーブルを作成しています。
同じように、シート「9月」に「九月」というテーブルを作成しています。
スポンサードリンク
PageViewCounter
Since2006/2/27