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 の意味合いですが

ってことだそうです。

VSTACK / HSTACK の特徴

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

たとえば、こんな式で 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 で使えるって・・・。
本当に悪魔的というか神ってますね。

つまり

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

これだけです。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

こっちは 先ほどの複数シートの処理と逆で、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 に該当する「関数」はありません。

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

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

実は この 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 を再現できるのか？？　つづく

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