「Googleスプレッドシートから見た！」Excel 14の新関数 -5 CHOOSEROWS / CHOOSECOLS
2023年一発目の noteになります。
Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事 5回目です。
14もあるんでシリーズが長期化してしまい、ネタに使ってた鎌倉殿も終わって、さらに年をまたいでしましましたｗ
関数の特徴
Excelでの メリット、デメリット、活用
Googleスプレッドシートの機能、関数との違い
Googleスプレッドシートでは無い機能を どう補うか
主にこの 4つの視点で検証していきます。
前回の記事
※この記事は Googleスプレッドシートに CHOOSEROWS / CHOOSECOLS が輸入される前に執筆したものです。現在は Googleスプレッドシートでも CHOOSEROWS / CHOOSECOLS が利用可能となっています。
EXCEL 14の新関数 CHOOSEROWS / CHOOSECOLS
Excel 14の新関数 5回目は Ctrl選択系の CHOOSEROWS、CHOOSECOLS を取り上げます。
これ読み方は、チューズロウズ、チューズコルズでいいんすかね？
なんか頭の悪い中学校の挨拶みたいｗ
これらは、配列から指定した 行・列を取り出せる 配列操作系の関数です。行を選択するか？列を選択するか？の違いなんで、これも双子関数ですね。
2つ同時に検証していきましょう。
CHOOSEROWS / CHOOSECOLS の特徴
前回の TAKE、DROP に似てますが、あちらが 対象の配列（範囲）から まとまった範囲を取り出す関数だったのに対して、こちらは 飛び飛びで指定できる関数。
なんとなかく Ctrl 押しながら 行（列）を選択する感覚に近いんで、 Ctrl選択系と mirは 呼んでます。
CHOOSEROWS / CHOOSECOLS の引数、動きをまとめると以下になります。
=CHOOSEROWS(array,row_num1,[row_num2],…)
=CHOOSECOLS(array,col_num1,[col_num2],…)
array ・・・ 対象となるセル範囲、または配列データ
row_num1,2… ・・・ 取得する行番号
col_num1,2… ・・・ 取得する列番号
TAKE,DROPは 行方向、列方向の両方を 指定出来ましたが、CHOOSE系は 名前の通り CHOOSEROWSは行指定のみ、CHOOSECOLSは列指定のみとなっています。
でもその分、単に飛び飛びで選択できるってだけじゃありません。
選択指定の自由度が格段に上がってます。
それぞれ EXCEL上での動きを見てみましょう。
■CHOOSEROWS関数 /CHOOSECLOS関数
※以下 CHOOSECOLSの場合は、行を列に読み替える
1.基本的には ,(カンマ)区切りで 引数を増やして複数行を指定
=CHOOSEROWS(A1:D7,1,3,5)
2.複数指定の方法は 配列でもOK
=CHOOSEROWS(A1:D7,{2,4})
3.配列指定は 特に行方向、列方向は気にしなくてよい
=CHOOSEROWS(A1:D7,{2;4})
※これは 2と一緒。つまり 配列は ,区切 ;区切り どっちでもOK
4.TAKE,DROPと同じくマイナス指定で下からの指定が可能
=CHOOSEROWS(A1:D7,-1)
※CHOOSECOLSの場合はマイナスで右からの指定
5.マイナスの場合も配列指定、複数指定が可能
=CHOOSEROWS(A1:D7,{-1,-3})
※下から1行目と3行目を指定
6.指定は正負の混在が可能
=CHOOSEROWS(A1:D7,-1,1)
※最終行と先頭行を指定
CHOOSEROWS、CHOOSECOLSの詳しい解説は おなじみ オフィスタナカさんを参考に
Excelでの メリット、デメリット、活用
CHOOSEROWS、CHOOSECOLS のメリットは 対象範囲（配列）から
欲しい行（列）を 欲しい順番で 欲しいだけ取得できる
といった 自由な配列加工 が簡単に出来るという点です。
欲しいだけ ってなんだ？
と思うかもしれませんが、これは後で触れます。
欲しい順番 に関しては、特徴の紹介で気づいた方もいるかもしれませんが、マイナス指定による 下（右）からの指定に加え、自由に順番の入れ替えが可能です。
さらに配列による {2;4} といった複数行指定が可能ということは、SEQUENCEがここで使えるってことですね！
つまり、
=CHOOSEROWS(A1:D7,SEQUENCE(7,1,7,-1))
こんな式で範囲を上下反転（逆順）にできるってことです。
ちなみに 7 は 7行という意味ですが、ここは ROWS(A1:D7) とすることが出来ます。
CHOOSEROWSがマイナス指定で下からの行番号を指定できる、という特性を活かすと
=CHOOSEROWS(A1:D7,-1*SEQUENCE(7))
こう書いても同じ結果が得られます。こっちの方が短いですね。
SEQUENCEで生成した {1;2;3;4;5;6;7} という配列の全要素に -1を かけて
{-1;-2;-3;-4;-5;-6;-7} という配列を生成、これで行番号を指定しているので下から順に取り出した形になっています。
もちろんこれは SORTBYで処理できますし、こっちの方がシンプルです。
=SORTBY(A1:D7,ROW(A1:A7),-1)
または
=SORTBY(A1:D7,SEQUENCE(7),-1)
SORTの挙動が ExcelとGoogleスプレッドシートで違うので Excelには SORTBYという関数が別で用意されています。
今回のような 対象と並び替えキー配列が違う場合はSORTBYを使うんですが、比較検証記事をそのうち書きたいと思います。
欲しい行を欲しい順番で取得、ここまでは FILTERや SORT（SORTBY）で処理できます。
しかし「欲しいだけ」 、つまり 同じ行（列）を複数回 何度も取り出す配列操作は、FILTERやSORTBYでは 出来ません。
これが出来るのが CHOOSEROWS、CHOOSECOLS の特徴（メリット）です。
たとえば、以下のような処理が可能ってことです。
画像上：1～3行目を2回ずつ繰り返し取得
=CHOOSEROWS(A1:D7,1,1,2,2,3,3)
画像下：1～3行目を2回繰り返し取得
=CHOOSEROWS(A1:D7,1,2,3,1,2,3)
上と下の違いわかりますよね？
上が 1～3行目を行毎に2回ずつ抽出、下が1～3行という行範囲を2回抽出となっています。
これらは当然ですが、配列の並び替えや単なる配列からの絞り込み処理とは別モノなんで、SORT関数、FILTER関数では対応できません。
そして、この 1,1,2,2,3,3 や 1,2,3,1,2,3 という繰り返し処理は、関数を組み合わせて生成可能です。
関数で生成できるなら、大きい数（行数が多いケース）でも対応できますね。
（余談）MOD、QUOTIENTを使った繰り返しテクニック
使うべきは、SEQUECE、そして 割り算系の MOD（余りを算出する関数）とQUOTIENT（商を算出する関数） です。
MOD ・・・ 1,2,3,1,2,3 といった範囲 繰り返し
MOD( SEQUENCE( 範囲の行数 * 繰り返し数 ) -1 , 範囲の行数 ) + 開始の数
QUOTIENT ・・・ 1,1,1,2,2,2,3,3,3 といった単体 繰り返し
QUOTIENT( SEQUENCE( 範囲の行数 * 繰り返し数 ) -1 , 繰り返し数 ) + 開始の数
こんな風に覚えておくとよいでしょう。
たとえば 11～15の範囲を 4回繰り返したい時は
=MOD(SEQUENCE(5*4)-1,5)+11
22～25の範囲を1つ3回ずつ繰り返したい場合は
=QUOTIENT(SEQUENCE(4*3)-1,3)+22
こんな感じになるわけです。
これは EXCELでもGoogleスプレッドシートでも同じなので 覚えておくと便利かも。
ただし、Googleスプレッドシートの場合は 配列処理をするので、全体を Arrayformulaで括る必要があります。
(活用例）CHOOSE系で 範囲（配列）を繰り返し拡大
活用例として、上記の繰り返しテクを活かした 特定範囲（配列）を上下に指定した数分 繰り返し拡張させる、なんて使い方もできます。
=LET(array,A1:C3,r,ROWS(array),c,COLUMNS(array),CHOOSECOLS(CHOOSEROWS(array,MOD(SEQUENCE(r*F1)-1,r)+1),QUOTIENT(SEQUENCE(c*F2)-1,F2)+1))
※F1 ・・・ 下方向への繰り返し
※F2 ・・・ 右方向への繰り返し
面白いと思いますが、具体的にどういった時に活用できるかは・・・わかりません！
その他の活用例は、今回もおなじみ 「いきなり答える備忘録」さんから。
CHOOSEROWSではなく、CHOOSECOLSの活用例が多いです。
レーベンシュタイン距離 や 素因数分解 といった 難しいケースの計算過程の一部で使用されてます。
あと、TEXTSPLITで分割した配列の最後の1つの要素を取得するのに使ってるのが面白いなと。INDEXを応用しても最後の要素取得は出来ますが、 -1で指定できる CHOOSECOLSの方がシンプルですね。
CHOOSEROWS、CHOOSECOLSのデメリットですが、これは特にないかなと。
INDEXやTAKE、DROP、FILTERなど色々ある中で、ケースによってどれを使うのが適切かを判断するのに 少し慣れが必要ってくらいでしょうか。
Googleスプレッドシートの機能、関数との違い
Googleスプレッドシートには残念ながら、CHOOSEROWS、CHOOSECOLS に該当する関数はありません。
実は Excelなら INDEXで代替することが出来ます。
=CHOOSEROWS(A1:D7,5,5,3)
の代わりに
=INDEX(A1:D7,{5;5;3},SEQUENCE(1,COLUMNS(A1:D7)))
でもOK。
※どちらも 範囲の 5行目、5行目、3行目の順に取得
INDEXの場合は、行は 縦配列、列は横配列で指定する必要があります。
また、行列どちらかを省略したり、このままではマイナスによる指定は出来ません。
ただし、指定する行、または列が 1行（1列）のみで、配列指定を使っていない場合は、もう一方を省略可能です。
以下のような単体行（列）ならOK
=INDEX(A1:D7,5,) ・・・5行目のみ取得
=INDEX(A1:D7,,2) ・・・2列目のみ取得
以下のような複数行（列）の配列指定の場合はダメ
=INDEX(A1:D7,{4;5},) ・・・4,5行目を取得（エラーとなる）
=INDEX(A1:D7,,{2,3}) ・・・2,3列目を取得（エラーとなる）
よって、上のように 5行目、3行目、3行目 の順で取得したい場合は、INDEXの第3引数、列の箇所に 全列指定の配列を入れる必要があり、
SEQUENCE(1,COLUMNS(A1:D7))
という式で {1,2,3,4,5,6,7} という横配列を生成しています。
同じようなことができますが、INDEXだとちょっと面倒ってことです。
でも、CHOOSEROWS、CHOOSECOLSと同様に、同じ行を繰り返し取得したり並び順を変えたりが可能ですし、さらに
=INDEX(A1:D7,{5;5;3},{4,3,2})
※5,5,3行目を4,3,2列の順に取得
上記のような式で、CHOOSEROWS,CHOOSECOLSの両方を合わせた処理をINDEXのみで実現可能だったりします。
こんな処理が必要なケースがあるのかは不明ですが、若い者にはまだまだ負けんよって感じのレジェンド関数の意地 を感じますｗ
るろうに剣心における師匠、比古清十郎 みたいなもんでしょうか。
Excelの INDEXは最強関数の一つと言えるでしょう。
しかし、残念ながら Googleスプレッドシートの INDEXは挙動が違います。
残念ながら 行や列の配列指定（複数指定）が出来ません。
Arrayformulaが効かないって・・・。なぜなんだー！！
というわけで、残念ながらGoogleスプレッドシートには CHOOSEROWS,CHOOSECOLS に類似する（近い）関数も無いってことになります。
複数の関数を組み合わせた式を自作 するしかないですね。
Googleスプレッドシートでは無い機能を どう補うか
では、Googleスプレッドシート上でどのようにして、CHOOSEROWS,CHOOSECOLS の代替を実現するか？
今回はこれまで配列操作で大活躍だった FILTER関数は使えません。
メリットの箇所で説明した通り、CHOOSEROWS、CHOOSECOLSは 同じ行を繰り返し取得したり、順序を自由に変えることが出来るからです。
これはFILTER関数には出来ない領域ですし、SORTBY関数を組み合わせても同じ行の重複取得が無理です。
じゃあ、どうするか？
幾つか方法がありますが、ここはぶっちゃけ LAMBDA + ヘルパー関数に頼っちゃいましょう。
そして、Googleスプレッドシートでは 行、列の 配列指定が 出来ない ＆ Arrayformulaが効かない INDEXを無理やりスピらせちゃいましょう！！
このヒントを手掛かりに、Googleスプレッドシートで CHOOSEROWS の代替式、作れそうでしょうか？
QAいってみましょう！
Q. Googleスプレッドシートで、 CHOOSEROWS と同じ結果を返す式を作れるか？
自力で挑戦してみる方は、以下のサンプルデータ（範囲 A1:D7）を対象としてみてください。7行４列のアルファベット配列 + 数字です。
A B C D
E F G H
I J K L
M N O P
Q R S T
U V W X
Y Z 1 2条件は以下の通り。
CHOOSEROWSと同じく 選択した 行を取得できる
選択は 配列指定することで 複数選択可能
マイナスの際は 一番下から 〇行目という指定が可能
並び順を変えたり、同じ行を複数回 取得も可能
例として、5行目、5行目、3行目、-1行目（最終行）の順に取得とする
さすがに、CHOOSEROWSと同じような 複数の引数指定は無理なので、行の指定は配列となります。作成のヒントは以下の通り。
・INDEX関数を使う
・LAMBDA ヘルパー関数を活用する
どうでしょうか？ いけそうな方は自力で作ってみましょう！
↓↓
ここから回答です。
↓↓
A. Googleスプレッドシートの 既存関数で CHOOSEROWS を再現する
今回はいきなり答えでいいかなと思いましたが、マイナスの際の処理だけ先に説明しておきましょう。
-1を指定した際、範囲の一番下の行（7行目）、-2なら 範囲の下から2行目（6行目）の指定 ・・・ということは、
マイナスでの行指定の場合は
= 指定行 + 範囲の行数 +1
という変換をすれば、マイナス指定ではない 正の数の行指定に変換できるということです。
範囲の行数は、 ROWS関数で取得できますね。
セル範囲だけでなくバーチャルな配列を対象とした際も使えます。
これをIFで分岐させてもいいんですが、合えてIFを使わない記述だと
r+(ROWS(A2:D8)+1)*(r<0)
※ r ・・・ 指定行 数値
このように書けます。 r<0 を満たす際は TRUEで *1（かける1） となりますが、満たさない場合は *0で そのまま r が返るという処理です。
これを元に 今回は CHOOSEROWS、つまり 行方向の処理なんで LAMBDAヘルパー関数は BYROWを使いましょう。（ここは MAPでもOK）
BYROWやMAPが 個々の結果で配列を返せて 最終的に配列のネストが出来るのは、EXCELから輸入したLAMBDAヘルパー関数を Googleスプレッドシートが2022年の12月初旬に メガ進化させたもので、Googleスプレッドシートオンリーの機能です。
ということで、回答としては以下の式。
=BYROW({5;5;3;-1},LAMBDA(r,INDEX(A1:D7,r+(ROWS(A1:D7)+1)*(r<0),)))
指定行を ; で連結して 縦配列 {5;5;3;-1} を生成、これを BYROW で一つずつ取り出し r とする。rがマイナスの時は +(ROWS(A1:D7)+1) が加算され、上からの行番号に変換、これを順に INDEXしていくって流れです。
BYROW内の INDEX処理は単体行に対する処理なので、第3引数（列指定）は省略が可能です。
意外と簡単でしたかね？
別解：ヘルパー関数無しでも出来る
一応別解として、ヘルパー関数無しの式も作ってみたんですが・・・。
=LAMBDA(array,r,ARRAYFORMULA(VLOOKUP(r+(ROWS(A1:D7)+1)*(r<0),{SEQUENCE(ROWS(array)),array},SEQUENCE(1,COLUMNS(array),2),0)))(A1:D7,{5;5;3;-1})
こっちはちょっと複雑だし長いしで、LAMBDA + ヘルパー関数（さらに配列ネスト） が使える 今の時代からすると 前時代的な古い式 かなってことで 解説は割愛します。
XLOOKUPが配列ネスト対応（縦横スピル可能）だったら、もう少し上の式もシンプルに出来るんですが・・・。縦横両方にスピる VLOOKUPに無理やり落とし込んでいます。
BYROW（またはMAP）とINDEXの方がおススメです。
CHOOSEROWS / CHOOSECOLS Googleスプレッドシート代替式 まとめ
まとめです。
CHOOSECOLSの方は BYROWを BYCOLを使った式に変換すれば良いですね。
最後にもう1回 LAMBDAって（ラムって）、範囲と指定行（列）部分を外にだしておきましょう。
CHOOSEROWS の代替式
=LAMBDA(array,choose_r,BYROW(choose_r,LAMBDA(r,INDEX(array,r+(ROWS(array)+1)*(r<0),))))(A1:D7,{5;5;3;-1})
※範囲の 5行目、5行目、3行目、-1（一番下の行）を取得
CHOOSECOLS の代替式
=LAMBDA(array,choose_c,BYCOL(choose_c,LAMBDA(c,INDEX(array,,c+(COLUMNS(array)+1)*(c<0)))))(A1:D7,{3,3,-1})
※範囲の 3列目、3列目、-1（一番右の列）を取得
CHOOSEに関しては 実用的なケースといっても具体例が思いつかないので、今回は
INDEXは、まだまだ若い者には負けないレジェンド関数！
LAMBDA ヘルパー関数で Googleスプレッドシートでも INDEXがスピる！
これだけ覚えておけばよいかなと。
一応、BYROWした後で BYCOLといった形で 2つを組み合わせて 行・列両方を自由選択する配列処理する式（ExcelのINDEX の代替式）も作成可能ですが、これも複雑な割に実用性はなさそうなんで割愛します。
今回のExcel 14の新関数検証は、以上となります。
14の新関数の配列操作系の検証は次回が最後。
mir的には 今回の配列操作系では 最強認定の VSTACK 関数 / HSTACK 関数 です！
■このシリーズの次の記事
この記事が気に入ったらサポートをしてみませんか？