Googleスプレッドシート LAMBDA REDUCE関数の魅力 2（直積 全パターン出力）
GoogleスプレッドシートのLAMBDAヘルパー関数において、最強だけど最も難解である REDUCE関数の魅力と、ぶっちゃけどんな時に活用できる関数なのか? を書いてみたいと思います。
シート関数のREDUCEだけではなく、GAS（Javascript）の配列メソッドである reduceについても 少し触れていきます。
前回のnoteでは 活用例の一つとして 変換表に沿って、複数セルを一括（繰り返し）変換するネタを紹介しました。
REDUCEを使って 組み合わせ全パターンを出力する
見たことのあるような色合いとメニュー構成ですがｗ
こんな感じで ベースのカレー、ライスの量、辛さ、オプション（１つ）が選べる架空のカレー屋さんがあったとします。
この４列の組み合わせの 全パターンを出力したい！ って時に、どんな式を作ればよいか？
というのが、今回のテーマです。
ちなみに 全部で何パターンあるか？は 各列の要素数を（乗算）かければよいので、
4 × 6 × 6 × 5 = 600
なんと、600パターンもあります。これは手作業では無理ですね。
Q1. まずは2列データから 組み合わせ（直積）を求めよう
いきなり 4列のパターン出力はハードルが高いので、まずは2列で ベースのカレーとライス量の 組み合わせパターン 4×6= 24件を 出力する式を考えてみましょう。
この組み合わせ出力処理は、直積とかクロス結合（CROSS JOIN)と呼ばれるもので 割とメジャーだったりします。
↓ A1セルにこちらのデータをコピペして利用ください。
ベースのカレー🍛 ライスの量🍚 辛さ🔥 オプション 🥄
ポーク 200g 甘口 手仕込とんかつ
甘口ポーク 250g 普通 チキンにこみ
ビーフ 300g 1辛 フィッシュフライ
ベジ 350g 2辛 ハーフやさい
400g 3辛 チーズ
500g 検索すれば解法は見つかりますが、まずは自力で考えてみましょう！（この式作ったことある！って人もいるかも）
↓↓↓
ここから回答
A1. 2列から 組み合わせ（直積）を求める式
回答です。
=ARRAYFORMULA(SPLIT(TOCOL(A2:A5&","&TRANSPOSE(B2:B7)),","))
他にも幾つか解法はあるんですが、上の式が一番簡単かと思います。
処理としては
A2:A5（ベースカレー）とTRANSPOSE(または TOROW）で縦横変換した B2:B7（ライス量）を間に区切り文字（今回は , を使用）を入れて連結させた青字のデータを生成。
この縦横で掛け合わせる処理が 「クロス結合」って感じですね。
TRANSPOSE関数は、データの縦横変換が出来る便利関数です。ただ、今回の場合は 1列のデータを1行にするだけなんで、TOROW関数を使った方が良いかも。
次に、これ（青字のデータ）をTOCOL関数で 縦1列にします。（FLATTEN関数を使ってもOK）
TOCOL関数の強みは、第2引数を使った空白除去やエラー除去ですが、シンプルな 縦1列変換でも活用できます。
最後にSPLIT関数で 連結の際に使った区切り文字（ , ）で 分割。これで完成。
GoogleスプレッドシートのSPLIT関数は、1列のデータを対象とした時に Arrayformulaと組み合わせれば 複数セルを一気に分割できるが魅力です。
この 複数セルの分割は、ExcelのSPLIT関数に該当する TEXTSPLIT関数では出来ません。
なので ここでは触れませんが、Excelで処理する場合は少しこの部分の式をアレンジする必要があります。（TEXTBEFORE、TEXTAFTERを使うとか）
まずは、2列の組み合わせパターン出力は出来ましたね。
ちなみにTOCOL等の新関数が登場する前の記事ですが、「いきなり答える備忘録」さんもこの直積の式について書かれています。
Q2. LETで範囲指定を1回にして、2列データで直積したい
一気に複数データとする前に、一括範囲指定で列を1つずつ取り出して処理する方法についても理解をしておきましょう。
やりたいことは Q1と同じ 2列データから 組み合わせ全パターン出力ですが、範囲指定を1回にして それを LETで xと置いた時の式を考えてみましょう。
LET関数は超絶便利なんで毎回登場しますね。スプレッドシートで複数の関数を組み合わせた式を作る際には、今や必須と言える関数かもしれません。
今回のケースは、LETを使って以下のような式を作ってみよう
=ARRAYFORMULA(LET(x,A2:B7, ここに入る式を考える
というお題です。考えてみましょう！
↓↓↓
ここから回答
A2. LETで範囲指定を1回にして、2列データで直積する
回答です。
=ARRAYFORMULA(LET(x,A2:B7, SPLIT(TOCOL(TOCOL(INDEX(x,,1),3)&","&TOROW(INDEX(x,,2),3)),",")))
こんな感じ。
LETで A2:B7 を xと置いた時に 1列目、2列目を取り出すのに INDEXを使っています。
ここは大丈夫ですね。
ここで 縦横変換で区切り文字で連結する前に 空白セルを除外しておきたい んですが、
じゃあどうするか？がポイントで、
その処理が
TOCOL(INDEX(x,,1),3)
TOROW(INDEX(x,,2),3))
この TOCOLとTOROWの式です。
先ほども書きましたが TOCOLやTOROWの魅力は、データ内の空白やエラーを削除して上詰め（左詰め）して 縦1列（横1行）として出力できる点です。
以前はこの空白詰めの為だけに QUERY関数を使ったりしていましたが、TOCOL、TOROWの登場で圧倒的に便利になりました。
ちょうど 1年前の 2022年12月には、まだ Googleスプレッドシートには TOCOLやTOROWはなくて、Excelの新関数うらやましーって note書いてたんですよね。時の流れは早いものです。
もし TOCOL、TOROWが無かったら
TOCOL(INDEX(x,,1),3)&","&TOROW(INDEX(x,,2),3)
↓
QUERY(INDEX(x,,1),"where Col1 is not null")&","&
TRANSPOSE(QUERY(INDEX(x,,2),"where Col1 is not null"))
こんな式になってたわけです。新関数はやはり強力ですね。
範囲指定を1回に集約した直積の式ができました。
Q3.REDUCE関数で 複数列のデータで 直積。組み合わせ全パターンを」出力したい！
それでは、いよいよ本題です。
可変する 複数列のデータに対して、列毎を掛け合わせた組み合わせの全パターンを出力する式に挑戦しましょう。
ポイントとしては
先ほどの A2の LETを使った 2列の結合式を 列数分ループ処理させる
その為に REDUCE関数を使う
この2つです。
そして
=ARRAYFORMULA(LET(x,A2:D7,
y,REDUCE( ここを考える ),SPLIT(y,",")))
指標として 式の構成はこのようにしておきましょう。
掛け合わせたい4列のデータ範囲 A2:D7 を xと置いて、
REDUCEで カンマ区切りで連結 のループ処理、結果を yと置く、
最後にSPLITで y を一気に カンマで分割
これを先ほどまでの2列の式をベースに作成するだけです。
やってみましょう！！
↓↓↓
ここから回答
A3.REDUCE関数で 複数列のデータで 直積。組み合わせ全パターンを」出力する
いきなり回答です。
=ARRAYFORMULA(LET(x,A2:D7,y,REDUCE(,SEQUENCE(COLUMNS(x)),LAMBDA(pv,cv,TOCOL(pv&","&TOROW(INDEX(x,,cv),3)))),SPLIT(y,",")))
少し長いのでインデント付きで わかりやすく。
=ARRAYFORMULA(
LET(
x,A2:D7,
y,REDUCE(,SEQUENCE(COLUMNS(x)),
LAMBDA(pv,cv,TOCOL(pv&","&TOROW(INDEX(x,,cv),3)))
),
SPLIT(y,",")
)
)長いとはいえ、思ったよりはコンパクトですね。
REDUCE式の部分を解説していきましょう。
まず冒頭部分の 初期値ですが、これはなし（空白）としておきます。
そして今回は 列単位で処理を回したいので、あとで INDEX(x,,cv) として 1つずつ順に列を取り出せるように、配列は 1～列数（ COLUMNS(x) ）までの連番配列をSEQUENCEで生成しておきます。
そして LAMBDA(pv,cv として
pv ・・・ 前回の結果 （初回は初期値）
cv ・・・ 配列の要素 （順番に 一つずつ取り出され処理される）
このようにして処理を回しています。
mirは pv （previousValue）と cv（currentValue） って付けるのが好きですが、ここは a,b でも なんでも良いです。
で、実際の処理は
TOCOL(pv&","&TOROW(INDEX(x,,cv),3))
この部分ですね。中身の処理を見てみましょう。
1巡目は pvは初期値 空欄となっています。そこに xから 1列目を取り出し、横1行にして空白除去したものと カンマ連結して 縦1列にするので
,ポーク
,甘口ポーク
,ビーフ
,ベジ
こんなデータが生成されます。
頭に カンマがあって大丈夫なの？と思うかもしれませんが、これは最後に SPLITで分割した際の空白除去で綺麗になります。
2巡目は1巡目の結果を pvとして、それと 2列目を取り出したデータを横1行空白除去したものを カンマ連結、最後に縦1列に。
これを3巡目、4巡目もくりかえし
この REDUCEで処理された カンマ区切りで全ての組み合わせが連結された600件のデータを yと置き、最後に SPLITで分割。
ここで 先頭の カンマの前の空白は自動で無視され完成です！
最後に完成した式の動作を確認してみましょう。
このように選択肢の変化に応じて、出力された組み合わせパターンが変化してますね。
最終的に 601行まで、タイトル行を除いて 600件の 全組み合わせが出力できているのが確認できました。
本来は配列のまま処理を回したいところですが、表計算は二次元配列以上の次元での処理は出来ず複雑になってしまう為、このように一旦 文字列として連結させて最後に分割という処理が簡単かと思います。
ただし、連結と分割を使っているので 00543 みたいな頭を0埋めしたコード番号等が SPLITで数値化されてしまったり、日付などを 表示形式で戻してあげる必要があったりします。
また当然ですが、元の表が 5列、6列と列が増えたり、行数が増えると一気に組み合わせの数も跳ね上がるので、データ量によってはパフォーマンスが落ちたり固まったりします。注意ください。
REDUCEを使った 複数列の組み合わせ全パターン出力、理解できましたでしょうか？
【余談】よく使う人なら 名前付き関数化
この処理よく使うのよ！って人なら 名前付き関数にしてしまっても良いでしょう。
データ ＞ 名前付き関数を作成
で 、たとえば CROSSJOIN といった関数名を付けて一度設定してしまえば、以降は上のように 範囲指定だけで簡単に全組み合わせを出力できます。
名前付き関数化する際は 範囲の引数を LAMBDAで外に出してあげるとよいでしょう。
名前付き関数の設定で、プレースホルダーとして range を定義してあげて
数式の定義に ↓ を
=LAMBDA(x,
ARRAYFORMULA(
LET(
y,REDUCE(,SEQUENCE(COLUMNS(x)),
LAMBDA(pv,cv,
TOCOL(pv&","&TOROW(INDEX(x,,cv),3))
)
),
SPLIT(y,",")
)
)
)(range)丁寧目に説明をいれておくと、他の人が使う時も便利です。
【めっちゃ余談】Googleスプレッドシート 色はスポイント使える
Googleスプレッドシートには スポイト機能があって、選択した色をセルの色や文字の色、枠線色として使うことができます。
Web上で使いたい色を見つけたら、その画像、または SnippingTool等で範囲を切り取ったものを 一旦 スプレッドシートに貼り付け（Ctrl + v）して、取得すればOK。
ちなみに今回の 架空のカレー屋さんの表のカラーも 某カレー屋さんのロゴからカラーをいただいております。（バレバレ）
実はこのスポイト機能、意外にもExcelには実装されてなくて、最近ようやくWeb版では利用できるようになったみたいです。
[新機能]
— 青りんご@Excel関数 (@Excel_xlsx) November 18, 2023
Web版のExcelでスポイトが使えるようになりました．
これは熱望していた方も少なくはないかと思います．
公式な発表はまだありませんが，Desktop版にも近いうちに追加されるのではないかと予想します#Excel pic.twitter.com/UNfec3ZBn8
ついつい、原色カラー使いがちの人や、自分のセンスに自信がない人は、センスのよい表のカラーリングをスポイトで真似しちゃうのが 良いですね。
ちなみに デザイン性を簡単にアップさせたオサレシートを作成する方法としては、テーマを変えるのもおススメです。
ちょっとした余談でした。
GAS 自作関数で 組み合わせ全パターンを出力する
前回のnoteで紹介した リスト表に基づく変換処理と同じく、こちらの組み合わせ出力も、REDUCE登場前は 複数列のケースだとシート関数での対応は難しく、GASでカスタム関数を作る方法で対応していました。
※シート関数でも頑張れば出来るんですが、結構面倒な式な上に今となってはREDUCE式で十分で紹介しても役に立たないので割愛。
GASで直積処理のコードを書いてみる
/**
* 列毎を掛け合わせた組み合わせをかえす 直積GAS関数
* @param {A1:D5} range 対象となる範囲または配列
* @customfunction
*/
function crossJoins(range) {
//ここのコードを考える
}さっき CROSSJOINを使っちゃったんで、crossJoinsという関数を スクリプトエディタでコードを書いて作成してみましょう。
ちなみに エディタ上の関数名は大文字・小文字は大事ですが、カスタム関数としてシート上で使う場合は 大文字・小文字が関係なくなります。
GAS初心者やまったくわからないって人には ハードル高すぎるんで、お題じゃなくいきなり回答いきましょう。
/**
* 列毎を掛け合わせた組み合わせをかえす 直積GAS関数
* @param {A1:A20} range 対象となる範囲または配列
* @customfunction
*/
function crossJoins(range) {
//行列入替 ＆ 空白削除
const array = range[0].map((_,i) => range.flatMap(r => r[i]? r[i]:[]));
//配列を直積する。いわゆるクロス結合
const result = array.reduce((a,b) => a.flatMap(x => b.map(y => x.concat(y))),[[]]);
return result;
}色々な書き方があるので、あくまでも一例です。
だいぶ削ぎ落した式なんで、かなりシンプル（でも、なに処理してるかよくわからない）って感じですね。
列毎の処理は GAS（JavaScript）の二次元配列としては 処理を回しづらいんで、最初に縦横変換 + 空白除去した 配列に変換してから、直積処理しています。
その 縦横変換 ＋ 空白除去が
//行列入替 ＆ 空白削除
const array = range[0].map((_,i) => range.flatMap(r => r[i]? r[i]:[]));ここです。
reduceと同じく、クールな配列メソッド の flatMap を使うことで、通常法は map + filterで 縦横の変換 と空白除去 するところを 一気に処理しています。
ちなみに 単純な 縦横変換（縦横入れ替え）だけなら
//transposeと一緒
function transpose2(range) {
//行列入替 ＆ 空白削除
const array = range[0].map((_,i) => range.map(r => r[i]));
return array;
}こんなコードになります。
GASでV8が使えなかった時代よりは、かなりシンプルに書けるようになりました。
昔はこれの為に Underscore ってライブラリを入れてた記憶があります。
で、直積処理が
//配列を直積する。いわゆるクロス結合
const result = array.reduce((a,b) => a.flatMap(x => b.map(y => x.concat(y))),[[]]);この部分です。
Pythonだと product って関数が用意されてるんですが、GAS（JavaScript）だとコネコネ作るしかありません。
しかし、ワンライナーコードとは言え reduce の中で flatMap して、さらに map して・・・ 。ぱっと見じゃ理解できないですよね。
コードをモダンにしてるだけで、やってることは以下のサイトの処理と同じです。
Bardにコード解説してもらう
上のcrossJoins関数は、結構前に 他のサイトを参考にしながら作ったコードではありますが、うまく説明できる自信がないので 生成AIにコードの解説を依頼してみましょうｗ
以下、Bardに投げかけて返ってきた回答（解説）です。
このコードは、JavaScriptでクロス結合を行う関数です。
function crossJoins(range) {
//行列入替 ＆ 空白削除
const array = range[0].map((_,i) => range.flatMap(r => r[i]? r[i]:[]));
//配列を直積する。いわゆるクロス結合
const result = array.reduce((a,b) => a.flatMap(x => b.map(y => x.concat(y))),[[]]);
return result;
}
まず、range引数で渡された配列を、行列の形式に変換します。行列の各列は、range配列の各要素のi番目の要素で構成されます。空白の要素は削除されます。
const array = range[0].map((_,i) => range.flatMap(r => r[i]? r[i]:[]));
次に、array配列を直積します。直積とは、2つの集合の各要素を組み合わせて得られる新しい集合です。
const result = array.reduce((a,b) => a.flatMap(x => b.map(y => x.concat(y))),[[]]);
reduce()関数を使用して、array配列を反復処理します。各要素を、直積の1行として出力します。
return result;
この関数の動作を、以下の例で確認します。
const range = [[1, 2, 3], [4, 5, 6]];
const result = crossJoins(range);
console.log(result);
このコードの出力は、以下のようになります。
[[1, 4], [1, 5], [1, 6], [2, 4], [2, 5], [2, 6], [3, 4], [3, 5], [3, 6]]
この出力は、range配列の各要素を組み合わせて得られた、2次元配列です。
Google Bard 回答
ちなみにChatGPTも ほぼ同じ内容の回答をしてくれました。
回答に追加質問すれば、より詳しく教えてくれます。
興味ある人は直接AIさんに 聞いてみてください。
なかなか腹落ちしないかもしれませんが、まずはコピペで使って慣れていくのが良いかと思います。
Googleスプレッドシート REDUCE活用 次回が最後
今回は直積（クロス結合）というお題で、REDUCE関数を活用する式を紹介しました。
シート関数のREDUCEの式は 結構丁寧に説明しましたが、理解できたでしょうか？
GASの方は難しいので、興味ある方は他のサイトなども見て理解を深めてみてください。
次回、最後にもう1回だけ REDUCE の例として、複数シートの串刺し処理における REDUCE活用を書きたいと思います。
この記事が気に入ったらサポートをしてみませんか？