メインコンテンツへスキップ
見出し画像

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

    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(A1:D4,F1:I3)

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


    画像
    =VSTACK(F1:I3,A1:D4)

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


    画像
    =HSTACK(E1:E4,G1:I4,A1:C4)

    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になる って点が 超不満です。

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

    画像
    空白セルは数式を通すと 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知恵袋だったら、そもそもデータを シートで分けること自体が愚行。この不調法者 めが! と質問者がお叱りを受けるケースですw

    でも、モーマンタイ(無問題)!

    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スプレッドシートに比べてホント簡単だわw

    やっぱり 複数シートの扱いは 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 が手を組むって・・・。

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


    この 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 の処理ですが、第2引数に 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 と これまで検証した 配列操作新関数 大集合の 胸アツ展開ですねw (プリキュア大集合的な)


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

    配列操作系最強の 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 を再現できるのか?? つづく



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


     
     

    mir

     
     
    元Excel職人・VBA使いから、Googleスプレッドシート職人・GAS使いにジョブチェンジ。謎解き感覚で お題(課題)を解決していくような記事を書こうかなと。その他、AIやらGeminiやらGoogleWorkspaceネタ全般

    あなたへのおすすめ