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

mir

2023年1月22日 20:57

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

14の新関数配列操作系の最後VSTACK、HSTACK が1回で終わらなかったので、その続きになります。

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

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

前回は違いまでを検証したので、今回は Googleスプレッドシートで VSTACK、HSTACKの代替式が作れるか？の検証です。

前回の記事

※こちらのnoteは Googleスプレッドシートに VSTACK / HSTACKが追加される前に書いたものです。

現在は Googleスプレッドシートでも VSTACK / HSTACKが利用可能です。

EXCEL 14の新関数 VSTACK / HSTACK の続き

セル範囲、配列を連結できる VSTACK / HSTACK は、Googleスプレッドシートの { , ; }（中カッコとカンマ、セミコロン）を使う連結よりも便利な部分がありました。

特に 連結面のサイズが違っても結合出来る メリットは大きいです。

これを Googleスプレッドシートで代替できるのか？
検証していきましょう。

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

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

3つのアプローチを検証してみましょう。

連結面が小さいほうを大きい方にサイズを合わせて連結
あらかじめ両方を連結したサイズの箱を用意して、そこに入れていく
ミルフィーユ方式、１行（１列）ずつ重ねていく

なお、Excelの VSTACK、HSTACK の 引数をいくつも取れる部分への対応は無理です。もちろん串刺し計算に使えるというチートにも対応できません。

あくまでも サイズの違う 2つ範囲（配列）の連結 に対応する代替式となります。

上記以外のアプローチもあるので「お題」にはしませんが、自信のある人は、2つのサイズの違う（範囲・配列）を結合できる式の作成に挑戦してみてください。

↓↓ ３つのアプローチはここから

1. 連結面が小さいほうを大きい方にサイズを合わせて連結

まずは普通に考えた正攻法。連結面が小さ方を拡張させて、大きい方のサイズに合わせる手順を考えてみましょう。

小さい方の範囲（配列）を拡張する方法は、Excel 14の新関数シリーズの1回目に取り上げた Expand の Googleスプレッドシートの代替式

=LAMBDA(arr,r,c,pad,MAKEARRAY(r,c,LAMBDA(r,c,IFERROR(INDEX(arr,r,c),pad))))(範囲, 行数 , 列数 ,"埋め文字" )

※行数、列数はそれぞれ拡張後の高さ、幅

こちらを使ってみましょう！

例えば上のように array1 (A1:C4) の下に array2 (A10:D12)を連結させたい場合は、結合面となるそれぞれの幅（列数）を COLUMNS関数で取得して比較すればよいですね。

とりあえず IFで分岐させて、サイズの大きい方に連結面を合わせる式を作ってみましょう。

=LAMBDA(array1,array2,padd,IF(COLUMNS(array1)<COLUMNS(array2),{MAKEARRAY(ROWS(array1),COLUMNS(array2),LAMBDA(r,c,IFERROR(INDEX(array1,r,c),padd)));array2},{array1,MAKEARRAY(ROWS(array2),COLUMNS(array1),LAMBDA(r,c,IFERROR(INDEX(array2,r,c),padd)))}))(A1:C4,A10:D12,"×")

処理としては以下の流れ。

条件 COLUMNS(array1)<COLUMNS(array2) が
true ・・・ array1 の方が接続面が小さい
array1 を EXPAND代替式で拡張して array2 と連結

false・・・array2の方が接続面が小さい、もしくは同じサイズ
array2 を EXPAND代替式で拡張して array1 の下に連結

EXPAND 代替式は元の範囲（配列）と同じ縦横サイズを指定した場合は、そのままの配列となるので、接続面が同じ というケースは気にしなくてよいです。

うーん、出来たけどちょっと式が煩雑ですよね。少し改良してみましょう。

=LAMBDA(array1,array2,padd,{MAKEARRAY(ROWS(array1),MAX(COLUMNS(array1),COLUMNS(array2)),
LAMBDA(r,c,IFERROR(INDEX(array1,r,c),padd)));
MAKEARRAY(ROWS(array2),MAX(COLUMNS(array1),COLUMNS(array2)),
LAMBDA(r,c,IFERROR(INDEX(array2,r,c),padd)))})(A1:C4,A10:D12,"×")

IFを使わず array1,array2 の両方とも EXPAND代替式を適用させてから結合させています。結合面のサイズを大きい方に合わせる式は

MAX(COLUMNS(array1),COLUMNS(array2))

これによって小さい方は大きい方に合わせて列が拡張され、不足部分が padd で指定した × で埋められています。大きい方はEXPAND代替式を通しても変更なしで出力されます。

あまりスリムになったように見えませんが、一応 IFを使った式が 268文字に対して下は 258文字と若干削減はされてますｗ

2. あらかじめ両方を連結したサイズの箱を用意して、そこに入れていく

1番目の方法では、 array1,array2 それぞれをMAKEARRAYで加工していますが、どうしても MAKEARRAYを2回使うので長い式になってしまいます。

じゃあ最初からarray1,array2が両方入るサイズの箱を MAKEARRAYで用意してしまおうってのが、この2番目の方法です。

そもそも MAKEARRAYってどう使うんだっけ？って人は、以下を読み返してみてください。

上のように縦に連結する場合、必要となる箱のサイズは

高さ　・・・　ROWS(array1) + ROWS(array2)　
幅　　・・・　MAX(COLUMNS(array1),COLUMNS(array2))

となります。これを使って式を作ると

=LAMBDA(array1,array2,padd,MAKEARRAY(ROWS(array1)+ROWS(array2),
MAX(COLUMNS(array1),COLUMNS(array2)),
LAMBDA(r,c,IFERROR(IF(r<=ROWS(array1),INDEX(array1,r,c),
INDEX(array2,r-ROWS(array1),c)),padd))))
(A1:C4,A10:D12,"×")

こんな感じになりました。

IF(r<=ROWS(array1),
INDEX(array1,r,c),
INDEX(array2,r-ROWS(array1),c))

この部分で r（行番号）が array1の行数以下なら array1を INDEXで参照、それを超えたら array1の行数を超えた分の数値 r-ROWS(array1) を使って array2 から INDEXで参照、としています。

列方向の不足部分を IFERRORで対応しているのは、EXPAND代替式と一緒ですね。

217文字なんで結構減りましたかね。でも、もう少し短くできないものか？

最後にちょと視点を変えた方法を試してみましょう。

3. ミルフィーユ方式、１行（１列）ずつ重ねていく

そもそも、本当に Googleスプレッドシートにおいて、サイズの違う範囲・配列は連結できないのか？

実は例外的にサイズが違っていても連結（っぽく）できる方法があります。

**BYCOL(A2:D12,LAMBDA(c,FILTER(c,c<>"")))**

こちらの BYCOLを使った式、結果は長さ（行数）がバラバラの列が横に連結できてますよね？

Googleスプレッドシートの メガ進化した BYROW、BYCOL、MAP、SCANなら、 1行、または1列ずつであれば、サイズの違う範囲（配列）を 連結していくことが出来るんです！

わかりやすい例だと

**=BYROW(SEQUENCE(5),LAMBDA(r,SEQUENCE(1,r)))**

このようなことが可能です。

ちなみに 2行目は B2:C2 までがデータ範囲ですが、BYROW全体としてはB2:F6の四角形の範囲を返している形になります。

F2はなにもないように見えますが、ここに何か入力すると式全体がエラーになります。

この1行（1列）を順に重ねていく ミルフィーユ方式で、サイズ違いの2つの配列を結合する式を作ってみましょう。

今回の場合は縦連結なので行を重ねていけばいいわけですから BYROWの出番なんですが、たまには MAPを使ってみましょう。

MAPであれば与える配列が縦1列なら BYROWと同じ動き、横1行なら BYCOLと同じ動きと、縦横両方に対応できます。

=LAMBDA(array1,array2,
MAP(SEQUENCE(ROWS(array1)+ROWS(array2)),
LAMBDA(r,IFERROR(INDEX(array1,r,),INDEX(array2,r-ROWS(array1),)))))
(A1:C4,A10:D12)

埋める文字の指定は出来ず、不足部分は自動で空欄となるものの、サイズの違う2つの範囲が縦連結できてますね。

MAPに与える配列は

SEQUENCE(ROWS(array1)+ROWS(array2)

これは　{1;2;3;4;5;6;7}　つまり、

1から両方の範囲を足した行数（7）までの連番配列です。

これを最初は INDEX(array1,r,) で、array1 から 1行ずつ取得していくわけですが、 r が ROWS(array1) の4 を超えると範囲外となりエラーになります。これをIFERRORを使ってエラーになったら（array1が終わったら）、

INDEX(array2,r-ROWS(array1),)

この式で、array2 の r-ROWS(array) つまり array2 に切り替えて 1行目から順に取得していきます。

処理としてはそれぞれの範囲から 1行ずつ取得したデータを 7つ重ねていますが、結果としては 2つの列サイズ（横幅）の違う配列が縦に連結したように見えるわけです。

だいぶ式が短くなりました。
なんと、改行なしで 144文字です。

1番目の式が 258文字なので、100文字以上スリムになったわけです。

というわけで、3番目の ミルフィーユ方式の MAPを使った式が VSTACK / HSTACKの代替式（結合面サイズの違う2つの配列・範囲を連結させる式）としてお勧めと言えます！

最後にまとめです。

Googleスプレッドシートにおける VSTACK / HSTACK 代替式まとめ

Googleスプレッドシートにおいて、中カッコとセミコロン、カンマによる範囲・配列の連結では対応できない、連結面のサイズ違いの2つの範囲・配列を結合する EXCEL の VSTACK / HSTACK の代替式はこちら！

VSTACK 代替式 (縦連結）

=LAMBDA(array1,array2,
MAP(SEQUENCE(ROWS(array1)+ROWS(array2)),
LAMBDA(r,IFERROR(INDEX(array1,r,),INDEX(array2,r-ROWS(array1),)))))
(範囲1,範囲2)

HSTACK 代替式 (横連結）

=LAMBDA(array1,array2,
MAP(SEQUENCE(1,COLUMNS(array1)+COLUMNS(array2)),
LAMBDA(c,IFERROR(INDEX(array1,,c),INDEX(array2,,c-COLUMNS(array1))))))
(範囲1,範囲2)

もし、もっとシンプルな式が出来たら教えてください。

作ってみたけど活用する場面は・・・

とりあえずこんな形になりましたが、別に無理に使う必要があるものでもないです。

こんなことも出来るってことだけ、頭の片隅に入れておいていただければ良いかと。

再掲しますが、 { , ; }（中カッコとカンマ、セミコロン） を使う連結は Googleスプレッドシートでは 必須（マスト）です。

こっちはしっかり使えるようなりましょう！

その上で Googleスプレッドシートの場合は、連結面のサイズが違う結合は全体がエラーになる、って部分だけ注意すればよいです。

今回で、Excel 14の新関数のうち 11の配列操作系の検証が完了しました。

残りは 文字列操作系の 3つ。単体で使っても超便利な強力な関数ぞろいですが、その中でも基本となる TEXTSPLIT を次回は取り上げます！

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

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