「Googleスプレッドシートから見た！」Excel 14の新関数 -6 VSTACK / HSTACK

mir

2023年1月15日 16:20

Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事 6回目です。

14の新関数配列操作系の最後であり、配列操作系最強関数と言える
VSTACK、HSTACKを取り上げます。

関数の特徴
Excelでのメリット、デメリット、活用
Googleスプレッドシートの機能、関数との違い
Googleスプレッドシートでは無い機能をどう補うか

主にこの 4つの視点で検証していきます。

前回の記事

※この記事は Googleスプレッドシートに VSTACK / HSTACK が輸入される前に執筆したものです。現在は Googleスプレッドシートでも VSTACK / HSTACK が利用可能となっています。

EXCEL 14の新関数 VSTACK / HSTACK

Excel 14の新関数 6回目は 配列連結系の VSTACK / HSTACK を取り上げます。

今更ながらトップ画像で 「配列連結計系」ってめっちゃ誤字になってるのに気づく・・・。もう戻せない（面倒過ぎて）

STACKなんで 積み上げ系にしようかと思いましたが、HSTACKだと横方向っだし、そもそもVSTACKも下に追加していくイメージだし、「連結」（結合）って表現の方がしっくりきますね。

これらは、複数の配列（範囲）を合体（連結）する関数です。

VSTACKが縦方向の連結、HSTACKが横方向の連結という違いなんで、今回も2つ同時に検証していきましょう。

ちなみに LOOKUPも行方向が VLOOKUP、列方向が HLOOKUP ですよね？
VとH の意味合いですが

「V」は Vertical（垂直な）
「H」は Horizontal（水平な）

ってことだそうです。

VSTACK / HSTACK の特徴

Googleスプレッドシートでは当然のように活用している、セル範囲の結合・配列の連結ですが、Excelではこの VSTACK / HSTACK の登場でようやく対応できるようになりました。

■縦方向の連結
=VSTACK(array1,[array2],...)

■横方向の連結
=HSTACK(array1,[array2],...)

引数の array はセル範囲、配列どちらでも可

arrayは追加した順に VSTACKの場合は上から下へ、
HSTACKの場合は左から右へ連結されていく。

毎回思いますが、MS公式の 関数の使用例のページって見づらいというか 意味わからなくないですか？

引数はシンプルで対象とする範囲または配列を指定するだけ。

それぞれ EXCEL上での動きを見てみましょう。

■VSTACK関数 / HSTACK関数の基本の動き

普通に対象範囲を引数として カンマ区切りで指定していくだけなので、簡単に使えます。

引数の順番によって結果がかわります。VSTACKの場合は上から下へ 連結していくので、順番には注意が必要です。

HSTACKの場合は左から右へと順に連結 となります。
引数（対象範囲）が 3つ4つと増えても同様です。

**=HSTACK(E1:E4,G1:I4,SEQUENCE(4),A1:C4)**

セル範囲だけでなく、配列どうし、もしくは範囲と配列といった連結も可能です。

VSTACK / HSTACKの詳しい解説は、おなじみ オフィスタナカさんを参考に。

Excelでのメリット、デメリット、活用

VSTACK / HSTACK のメリットは、これまで Excelのシート上ではできなかった、範囲（配列）の連結が自由に出来るようになった点です。

とにかく Excel関数にとっては、これが大きい変革 メガシンカです。

そして Googleスプレッドシート使いとしては、

「連結面のサイズが違う範囲（配列）どうしを結合できる」

という部分が超メリット。悪魔的な便利さ に感じます。

=VSTACK(A1:D7,A11:B14)

たとえば上記のようなケース、範囲 A1:D7 と範囲 A11:B14 を縦に結合したい場合、結合面のサイズ（列数）は 4 と 2 で揃ってません。

でもTVの前の奥さん、VSTACK なら連結できちゃうんです。
（テレビショッピングみたい）

足りない部分だけがエラー#N/A となるだけです。

もちろん、IFERROR を使えばエラー箇所を空欄（空文字）にすることが出来ます。

要は 配列サイズを気にせず、ガンガン連結できちゃうってことですね。
これは便利。

一方、VSTACK / HSTACK のデメリット。これはExcel全般の仕様なんですが、やはり Googleスプレッドシート使いとしては許せない 空白が 0になる って点が超不満です。

今更変えようがないのかもしれませんが・・・。

実は VSTACK / HSTACK の神ってる機能（メリット）がもう一つあります。

活用例の中で紹介していきましょう。

(活用例）複数シートの表をまとめた上で検索・抽出

活用例としてまっさきに思いつくのが複数に分かれた表（テーブル）をまとめたデータからの検索・抽出です。

Excelだと複数シートといえば Power Query って感じですが、VSTACKの登場で、作業用シートや作業列を使わず数式一発で複数シートをまとめた表をバーチャルで生成して検索や抽出って処理が出来るようになりました。

=LET(table,VSTACK('1月'!A2:C50,'2月'!A2:C50,'3月'!A2:C50),
XLOOKUP(A4,INDEX(table,,2),table,"",,-1))

たとえば、こんな式で 1月、2月、3月と月で別れたシートに記載された注文日、商品、数量を VSTACKで縦連結し一つのテーブルを生成。XLOOKUPでテーブルの2列名を 下から商品名で検索することで 一番最近 のデータを抽出、なんて使い方ができます。

でも、シート3枚くらいならいいけど、1月~12月まで 12枚もシートがある場合も、ひとつひとつシート名書いていかなきゃいけないの？？

ってなりますねよ。

Yahoo知恵袋だったら、そもそもデータをシートで分けること自体が愚行。この不調法者めが！ と質問者がお叱りを受けるケースですｗ

でも、モーマンタイ（無問題）！

VSTACK('1月'!A2:C50,'2月'!A2:C50,'3月'!A2:C50)

この部分、実はExcelのあの機能で簡略化ができます！！

【神ってる】VSTACK / HSTACK は複数シートの串刺し集計に使える！！

あの機能というのは、エクセルで同じ構成の 複数シートを一気に計算する時に便利な 串刺し計算（3D集計）です。

これが VSTACK / HSTACK で使えるって・・・。
本当に悪魔的というか神ってますね。

つまり

=VSTACK('1月'!A2:C50,'2月'!A2:C50,'3月'!A2:C50)
↓
=VSTACK('1月:3月'!A2:C50)

こう出来ちゃうってことです。
もちろん 12月までのシート12枚でも

=VSTACK('1月:12月'!A2:C50)

驚きの手軽さ

これだけです。

これなら、開始シートと終了シート名を入れるだけなんで、どれだけシート増えても問題ないですね。お手入れも簡単！

ちなみに手入力でシート名を入れる場合、

'（シングルクォート）どこに付けるんだっけ？

ってなりがちですが、これは付けなくても大丈夫です。

1月:3月!A2:C50
↑【開始シート名】:【終了シート名】!【集計セル範囲】

と入力してエンターすれば、~~ジャパネットが負担~~ 自動でシングルクォートが付きます。

計算につかうのではなく、タイトル行つけて 統合テーブルを出力したいなら

=VSTACK('1月'!A1:C1,'1月:3月'!A2:C50)

こんな感じで VSTACKの引数の1つ目に 1月シートの A1:C1（タイトル行）を持ってきて連結させればいいですね。

いやー、Googleスプレッドシートに比べてホント簡単だわｗ

やっぱり 複数シートの扱いは Excel に一日の長があり、まだまだ Googleスプレッドシートの弱い部分であることを痛感しました。

ちなみに Googleスプレッドシートでの複数シートの処理は、以前の noteでやっています。

以下のように REDUCEを絡めたかなり複雑な式を作る必要があります。

■Excel
=LET(table,VSTACK('1月:3月'!A2:C50),
XLOOKUP(A4,INDEX(table,,2),table,"",,-1))

■Googleスプレッドシート
=LAMBDA(table,XLOOKUP(A4,INDEX(table,,2),table,"",,-1))
(REDUCE(,SEQUENCE(3),LAMBDA(pv,cv,IFERROR({pv;INDIRECT(cv&"月!A2:C50")},INDIRECT(cv&"月!A2:C50")))))

同じことをやろうとしたら、こんな風になるんですね。。

しかも Googleスプレッドシートの場合は、開始シート・終了シートといった指定は関数では無理、GASを使わないと出来ません。

今回のような 1月～3月のようにシート名に規則性があれば数式で生成できますが、ちょっとハードルが高いです。

EXCEL での VSTACK の便利な活用例でした。
もう1つ活用例を紹介しましょう。

(活用例）列ごと（行ごと）の空白削除

Googleスプレッドシートの例で登場した REDUCEですが、配列結合と組み合わせるケースは非常に多くて、Excelにおいても VSTACK、HSTACK は、LAMBDAヘルパー関数の REDUCEと相性がとても良いのです。

REDUCEの反復処理を利用して、範囲を繰り返し連結していく（forループでアレイをプッシュみたいな）イメージ。

しかし、LAMBDA ヘルパー関数最強の REDUCE と配列操作系新関数最強の VSTACK / HSTACK が手を組むって・・・。

闘強童夢！！ドリームマッチ って感じですねｗ

この VSTACK / HSTACKと REDUCEを組み合わせた事例を紹介しましょう。

=LET(array,A2:E12,DROP(IFERROR(
REDUCE("",SEQUENCE(COLUMNS(array)),
LAMBDA(pv,cv,HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),"")
))),""),,1))

列ごとの空白上詰め処理です。実はこれ Excel だと結構大変で上のような複雑な式になります。（TEXTJOINとTEXTSPLITを組み合わせる方法もあります）

一方、この列毎の FILTER処理は Googleスプレッドシートでは、メガシンカした BYCOLで簡単に出来るようになりました。

残念ながら本家であるはずの ExcelのBYCOLは 配列の入れ子がサポート外となっている為、REDUCEとHSTACKで横連結を繰り返す 複雑な式での対応になります。

■Excel
=LET(array,A2:E12,DROP(IFERROR(
REDUCE("",SEQUENCE(COLUMNS(array)),
LAMBDA(pv,cv,HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),"")
))),""),,1))

■Googleスプレッドシート
=BYCOL(A2:E12,LAMBDA(c,IFERROR(FILTER(c,c<>""),)))

こっちは先ほどの複数シートの処理と逆で、Googleスプレッドシートの方が圧倒的に簡単で短いですね。

それぞれ得手不得手があるってことでしょうか。

Excelの式を少し解説しておきます。

まず REDUCE の処理ですが、第２引数に SEQUENCE(COLUMNS(array)) を設定することで、1から対象となるarray（A2:E12）の列数 5までの連番に対して順に処理をしていきます。この 1～5 から一つずつ取り出したものを cv としています。

LAMBDA ヘルパー関数の REDUCEは、プログラミングに触れたことがない人には結構難しい関数です。以前の noteでも取り上げています。

※ Googleスプレッドシートの REDUCE関数なので一部 Excelと挙動が違う部分がありますが、大きくは同じものと考えてOK

INDEX(array,,cv)

この cvを使って、INDEX関数で対象配列から 1列ずつ取得しています。

TOCOL(INDEX(array,,cv),3)

通常は FILTERを使う空白除外ですが、対象範囲が1列の場合は上のように TOCOL を使って簡略化できます。

IFERROR(TOCOL(INDEX(array,,cv),3),"")

ただし、対象の列が 完全に空 （何かしら入力されたセルが一つもない）、つまり上の C列のような場合は TOCOLが #CALC!エラーを返し全滅してしまうので、TOCOLをIFERRORで括って、この部分でエラー回避をしています。

HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),""))

この空白除去で上に詰まった配列を pv（一つ前までの処理結果の配列）に HSTACK で横連結していきます。

これを繰り返し行えるのが REDUCE関数 です。

IFERROR(REDUCE("",SEQUENCE(COLUMNS(array)),
LAMBDA(pv,cv,HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),"")))),"")

REDUCEの後に再度 IFERRORをしていますが、こちらは HSTACKの連結の際のサイズ違い（行数不足）のエラーを空白にする処理用です。

また、REDUCEの初期値は省略できない都合上、REDUCE("",　としている為、今回の場合は一番左の列に不要な空の列が生成されてしまいます。

DROP(IFERROR(REDUCE("",SEQUENCE(COLUMNS(array)),
LAMBDA(pv,cv,HSTACK(pv,IFERROR(TOCOL(INDEX(array,,cv),3),"")))),""),,1)

これを最後に DROPで1列目を除外しています。（行方向は手を加えないので第2引数は空欄の為 ,,1 となる）

このREDUCE 初期値 "" で仮置きからの～最後にDROP って処理の流れは 「いきなり答える備忘録」さんがよく使ってます。

ちなみに式内の INDEX(array,,cv) は、 CHOOSECOLS(array,cv) と書くことも出来ます。（CHOOSECOLSっていう関数名が長いからつい避けちゃう）

というわけで、さすが配列操作新関数の最後の回。DROP、TOCOL、CHOOSECOLS、そしてHSTACK とこれまで検証した 配列操作新関数大集合の胸アツ展開ですねｗ　（プリキュア大集合的な）

その他の活用例は、今回もおなじみ 「いきなり答える備忘録」さんが参考になります。難しい例が多いですが。

配列操作系最強の VSTACK、HSTACKだけあって活用シーンが幅広いです。

Googleスプレッドシートの機能、関数との違い

Googleスプレッドシートの場合は、VSTACK / HSTACK に該当する「関数」はありません。

セル範囲・配列の縦横の連結に関しては、中カッコとカンマ、セミコロンを使った配列記述を使います。

■縦方向の連結
={array1; [array2]; …}

■横方向の連結
={array1, [array2], …}

引数の array はセル範囲、配列どちらでも可

それ用に関数があるわけではなく、機能としてカバーしてるって感じですね。

実はこの Googleスプレッドシートの配列結合に関しては、Excel 14の新関数シリーズの 1回目で触れてます。

Excelでは中カッコを使った結合は、単体の文字列や数値には使えるものの、範囲や配列に対しては使えなかったわけです。

この部分を補う関数が、追加された VSTACK / HSTACK なんですね。

じゃあGoogleスプレッドにおいて VSTACK / HSTACK の代わりは

中カッコを使った配列連結で十分か？

残念ながらまったく同じことが出来るとは言えません。

VSTACK / HSATACK のメリットとして紹介した、サイズの違う配列の連結ができないという問題があります。

連結面のサイズが違う範囲・配列を連結しよとすると、上のように全体がエラーとなり結果が得られません。

VSTACK / HSTACKの連結みたいに 不足部分だけエラーを返してくれれば良かったのですが・・・。

いずれにせよ部分的にエラーは出るけど、あとで IFERRORでやっつければOKってことでガンガン連結できた VSTACK / HSTACK と違って、Googleスプレッドシートの中カッコによる連結は 連結面のサイズを気にする必要があります。

サイズが揃ってない場合は加工する必要があるってことです。

ここが、ちょっと面倒というか残念です。

さすがに後から追加してきただけあって、Excelの VSTACK / HSTACK の方が 痒いところに手が届くというか、ポイントを抑えてるってことでしょう。

【次回】Googleスプレッドシートでは無い機能をどう補うか

では、Googleスプレッドシート上で 連結面のサイズが違っても連結できる ようにするには、どうすればよいか？

幾つかのアプローチが思いつきますが、いずれも複数の関数を組み合わせた式を作ることになります。

今回は VSTACK / HSTACK がすごすぎて、前半の Excelの話がかなり長くなってしまったので、次回（次週）この続きを書きたいと思います。

はたして Googleスプレッドシートで VSTACK / HSTACK を再現できるのか？？　つづく

■このシリーズの次の記事

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ