【復活】Googleスプレッドシート 11新輸入関数最新動向-2 【TOCOL / CHOOSEROWS】

2023年3月12日 21:10

新関数の追加から早々に不具合が発生し、一旦ロールバックされて使えない状態が続いていた新配列操作系関数ですが、先日ようやく復活しました。

VSTCK, HSTACK, CHOOSEROWS, CHOOSECOLS, WRAPROWS, WRAPCOLS やっと復活ー pic.twitter.com/r6OQit1HrR
— mir (@mir_for_note) March 9, 2023

というわけで Googleスプレッドシート11の新関数の続きで、今回からは Excelから Googleスプレッドシートに輸入された 配列操作系の 8つの新関数 をざっくり紹介していきましょう。

シリーズ前回の記事

Excelから輸入された配列操作系関数

全部で8つですが、基本的には対になる2つの関数セットが4つとなります。

TOROW / TOCOL
CHOOSEROWS / CHOOSECOLS
WRAPROWS / WRAPCOLS
VSTACK / HSTACK

Googleスプレッドシートならではの活用もあるかと思いますが、これらすべて最近 Excel14の新関数シリーズで触れたばかりです。

個々の関数の掘り下げは別の機会とし、今回は一気に半分の4つを見ていきましょう。

Googleスプレッドシート 11の新関数-3,4 TOROW / TOCOL

TOROW(array_or_range, [ignore], [scan_by_column])
TOCOL(array_or_range, [ignore], [scan_by_column])

array_or_range ・・・　配列または範囲,
[ignore]・・・　[無視]　（省略時は 0）
　0: すべての値を保持する
　1: 空白を無視する
　2: エラーを無視する
　3: 空白とエラーを無視する
[scan_by_column]・・・[列でスキャン（するか？）] 省略時は false
　True: 配列を列方向（上から下）にスキャンする
　False: 配列を行方向（左から右）にスキャンする

TOROW / TOCOL は、セル範囲もしくは配列を 1本の行（TOCOLの場合は列）に変換するついでに、空白やエラー除去もできちゃう + 読み取りの方向も指定できるという関数です。

FLATTENとTOROW / TOCOL の違い

もともとGoogleスプレッドシートには、FLATTENというこれに似たような関数がありました。

FLATTENはセル範囲・配列を左から右方向にスキャンして、1本の列（縦1列）に変換する関数です。

でも、列ではなく横1行としたい時や、空白・エラー除去をしたい、読み取りを左から右ではなく上から下としたい場合は、FLATTEN単体では無理なので、他の関数を組み合わせる必要があります。

これらが引数で制御できる TOROW / TOCOL は、 FLATTENの上位互換と言える関数ですね。もう FLATTEN出番ないかも？

=FLATTEN(A1:D7)
=TOCOL(A1:D7)

単に範囲を左から右に読み取って縦1列に変換するだけなら、FLATTENとTOCOLどっちを使っても一緒です。

一方、上の画像のような「範囲を上から下にスキャンし、空白とエラーを除去したものを 1行で出力したい。」といったケースでは

=TRANSPOSE(FILTER(FLATTEN(TRANSPOSE(A1:D7)),NOT(ISBLANK(IFERROR(FLATTEN(TRANSPOSE(A1:D7)),)))))

=TOROW(A1:D7,3,true)

こんなに差がありますｗ　便利になったもんだ。

Excel のTOROW / TOCOL とGoogleスプレッドシート版の違い

基本的な内容は、 EXCELの TOROW / TOCOLと同じです。

ただし1点だけ、Googleスプレッドシートは式で空白が返せます。

Excelの式に慣れている人は空白を返す際に "" と空文字を使いがちです。

しかし、この空文字は完全なる空白とは別モノで、ISBLANK では FALSE扱いになります。

今回Googleスプレッドシートに追加された TOROW / TOCOL は、この空文字は空白とみなさず第2引数で 1や3を指定しても 空文字は削除対象となりません。

一方、式で空白を返した場合は TOROW / TOCOL で除外し詰めることができます。

この数式で 空白を空白として返せるのは、Excelには出来ない Googleスプレッドシートの利点の一つです。

Excelに慣れている人は 空文字を使いがちなんで注意しましょう。

Excelでは式で出力した空白（空文字）入りデータは TOCOL、TOROWでは空白詰めが出来ないのは悩ましいですね。

それ以外は Excelでの使い方と変わりありません。

「Googleスプレッドシートから見た！」Excel 14の新関数 -3 TOROW / TOCOL で詳しく検証しているので、そちらをご覧ください。

また「いきなり答える備忘録」さんも、追加して早々に Googleスプレッド TOROW / TOCOLの解説と活用例を書かれています。こちらも参考になります。

Googleスプレッドシートならではの TOROW / TOCOL活用例

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

Googleスプレッドシートならではの使い方としては、上のような列毎の空白除去上詰めの際、FILTER関数をTOCOLに置き換えといったケースが思いつきます。

=BYCOL(A2:D12,LAMBDA(c,TOCOL(c,3)))

LAMBDAヘルパー関数の BYCOLとTOCOLを組み合わせる活用例です。
※ BYCOLは MAPでも代用可能
※行単位での空白詰め左寄せの場合は BYROWを使用

これはBYCOLでの列単位の処理で列（一次元配列）を返すことができる Googleスプレッドシートだから可能な式で、Excelではエラーとなります。

他にも クロス表 → リスト表変換で使えそうですが、それは別noteで書きたいと思います。

さらに、mirの過去記事で FLATTEN使ってるものは、TOCOLやTOROWに置き換えすることで簡略化可能です。

Googleスプレッドシート 11の新関数-5,6 CHOOSEROWS / CHOOSECOLS

CHOOSEROWS(配列, 行番号 1, [行番号 2])
CHOOSECOLS(配列, 列番号 1, [列番号 2])
※行番号、列番号はマイナスをつけると後ろからの指定

CHOOSEROWS、CHOOSECOLSはセル範囲・配列から番号指定（〇番目）で、指定した行（CHOOSECOLSの場合は列）を抽出する関数です。

この CHOOSEROWS / CHOOSECOLS は、Googleスプレッドシートで マイナスで範囲の後ろから指定出来る初の関数じゃないでしょうか？

また、行番号（列番号）は配列で指定ができます。ということは、SEQUENCE関数との相性もメッチャいいってことですね！

こちらも基本動作は EXCELと一緒なんで、詳細や活用は過去のnoteを参照ください。

INDEXや他の関数と CHOOSEROWS / CHOOSECOLS の違い

Excel新関数シリーズの記事でも書きましたが、CHOOSEROWS / CHOOSECOLS の特徴は対象とするセル範囲・配列から

欲しい行（列）を　欲しい順番で　欲しいだけ取得できる

という点です。

欲しい順番で は、 3,1,2 のように指定することで、順番を気にせず行（列）を取り出すことができるという意味です。

=Arrayformula(CHOOSEROWS(A1:D3,QUOTIENT(SEQUENCE(F1*ROWS(A1:D3),1,0),F1)+1))

欲しいだけというのは、同じ行（列）を何度も繰り返し取得することが可能ってことです。

SEQUENCEと QUOTIENT（商）や MOD（余り）を組み合わせることで、数字の繰り返し配列を生成し、それをCHOOSEROWSの引数として使って、上のように範囲の一つ一つの行を指定した回数（F1セルの 4）繰り返すといった処理が可能です。

解説は過去のnoteを参照ください。

同じ処理は BYROW（もしくはMAP）とINDEXを組み合わせる方法、もしくはVLOOKUPを応用した方法でもできますが、CHOOSERROWSを使ったこちらの式が一番シンプルじゃないかと思います。

難点は関数名が長いってことですかね・・・。

Googleスプレッドシートならではの CHOOSEROWS / CHOOSECOLS 活用例

=Arrayformula(CHOOSECOLS(SPLIT(A2:A5,":,"),{2,4}))

たとえば、上のようなケースで、氏名と年齢のデータだけ取り出したいといった場合。SPLITとCHOOSECOLSを組み合わせることで、シンプルな式で処理が出来ます。

このテクニックで CHOOSECOLS で -1を指定することで分割した後の最後の要素も取得できますが、分割後のセル数がバラバラの時は注意が必要です。

その辺りの対応策は「いきなり答える備忘録」さんに掲載されています。

CHOOSEROWSとXMATCHとの組み合わせもアリ

=LET(array,A1:D7,key,F2:F4,keycol,1,ARRAYFORMULA(CHOOSEROWS(array, XMATCH(key,CHOOSECOLS(array,keycol)))))

XLOOKUP関数が縦横スピルできない代わりに、今回のCHOOSEROWSとCHOOSECOLS を XMATCH と組み合わせる方法もあります。

よくあるキーを特定列から検索してヒットした行を丸々抽出したいといった処理は、残念ながら XLOOKUP + Arrayformula が使えません。

これを XLOOKUPと同じタイミングで追加された新関数 XMATCHと、今回追加の CHOOSEROWS、CHOOSECOLSを Arrayformulaと組み合わせて処理する式となります。

せっかくなんで LET関数を使って、式の冒頭部分で

array （対象の範囲）・・・A1:D7
key（検索キーの列）・・・F2:F4
keycol（検索範囲の何列目で検索するか？）・・・　1

このように名前付け（変数化）して、ここだけ変えればいいように簡略化しています。

LET便利すぎて検証記事を書く前にガンガン使っちゃってますｗ

XMATCH はちょうど平日更新で超応用例シリーズで取り上げています。
地味だけど意外と便利な関数かも。

Googleスプレッドシートは新配列操作系関数でより簡単に、よりシンプルに！

今回は Googleスプレッドシートに Excelから輸入された新配列操作系関数の

TOROW
TOCOL
CHOOSEROWS
CHOOSECOLS

の4つを検証しました。

いずれも、これが無いと無理ってわけじゃないですが、一部の複雑な式が新関数でぐっと簡略できるようになった印象。

難しい関数ではないので、色々な活用例を参考にして是非使いこなせるようになりましょう！

次回は残りの4つの配列操作系新関数を検証していきます。

【復活】Googleスプレッドシート 11新輸入関数 最新動向-2 【TOCOL / CHOOSEROWS】

Excelから 輸入された 配列操作系関数