見出し画像

Googleスプレッドシート シート関数で作る「順列と組み合わせ」全パターン出力 3 （REDUCE登場）

mir

Googleスプレッドシートのシート関数ネタ。「順列と組み合わせ」の全パターンを出力する数式を考えるシリーズの第3弾、今回がシリーズ最後です。

前回までは LETやLAMBDA登場前の古いやり方で「順列と組み合わせ」の全パターン出力式を紹介してきました。

今回はいよいよ新関数を使ったイマドキ（最先端）の 順列、組み合わせの式 が登場します！

前回の noteはGoogleスプレッドシートのLET,LAMBDA 登場前の関数を組み合わせて「組み合わせ」を全パターン出力する式を紹介しました。


REDUCE関数を使った直積の式をベースに順列を考える

「順列」や「組み合わせ」の全パターン出力、第１回、第２回で紹介した BASE関数を使う方法は、

重複取得ケースを含めた全パターンを生成してから絞り込む

というものでした。

たとえば、0,1,2,3 という４つの要素から３つを取り出す際、順列にしろ組み合わせにしろ 通常は 2,2,2 や 1,3,3 といった同じ数字が2回登場するものは 対象外です。順列なら24パターンとなります。

ただ、一旦そこを無視して0,0,0 ～ 3,3,3 までの 4^3 = 64パターンを用意してから 数字の重複使用がないものだけに絞り込む。

これだとどうしても処理が重くなっていきます。


シート関数で繰り返しを実現する REDUCE関数と 再帰式

では、BASE関数を使わず処理する為にはどうすればよいか？ここでどうしても繰り返し処理が必要となります。

プログラミング（例えばGAS）だと、forループを使うケースですが、過去のシート関数ではこの繰り返し処理を実現する方法はありませんでした。

しかし LET関数 や LAMBDA関数の登場で、シート関数でも繰り返し処理を実現することが出来るようになりました。

新関数を使った繰り返し処理は

  1. LAMBDAヘルパー関数である REDUCEやSCAN を使う方法

  2. LETとLAMBDAを使って再帰式を作成する方法

大きくこの2つがあります。

この2つの使い分けは、

処理回数が決まっている
（GASだと for で書くケース）
→ REDUCE

処理回数が決まっていない、ある条件を満たしたら終了したい
（GASだと whileで書くケース）
→ LETとLAMBDAの再帰式

 ざっくり言うとこんなイメージです。

この繰り返し処理については過去の noteでも紹介しています。


順列（重複アリ）は、あの全パターン式が使える

順列や組み合わせは、n個の要素から k個を取り出すと考えた時に、この k（取り出す個数） が処理回数 となるので、REDUCEで書けそうですね。

で、実は既に過去のnoteに 順列全パターン出力に近いことをやっているREDUCE関数の式が登場しています。

このような架空の某カレー屋のメニューの組み合わせ全パターン出力にチャレンジした「Googleスプレッドシート LAMBDA REDUCE関数の魅力 2（直積 全パターン出力）」の回で使った式です。

ここで使った式は

=ARRAYFORMULA(
  LET(
    x,A2:D7,
    y,REDUCE(,SEQUENCE(COLUMNS(x)),
      LAMBDA(pv,cv,TOCOL(pv&","&TOROW(INDEX(x,,cv),3)))
    ),
    SPLIT(y,",")
  )
)

こんな式でした。解説は 過去noteを見ていただくとして、早速1つ目のお題いってみましょう！


Q1. 直積繰り返し式をベースに 順列（重複あり）の式を作りたい

↓ こちらの式をアレンジして

=ARRAYFORMULA(
  LET(
    x,A2:D7,
    y,REDUCE(,SEQUENCE(COLUMNS(x)),
      LAMBDA(pv,cv,TOCOL(pv&","&TOROW(INDEX(x,,cv),3)))
    ),
    SPLIT(y,",")
  )
)

A2:D2のセル範囲の4つの要素から、重複を許可して 2つ取得する順列（16パターン）を書き出す式（画像だと A9に入れる式）を考えてみましょう。

なおLET関数で、元になる要素のセル範囲を x、取り出す数（今回は2）を kと定義するものとします。

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,2,

↑このようにするってことですね。

考えてみましょう！ 








↓↓↓
ここから回答



A1. 直積繰り返し式をベースに 順列（重複あり）の式を作る

回答です。

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,2
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,TOCOL(pv&","&x))
    ),
    SPLIT(y,",")
  )
)

よりシンプルな式になりましたね。

少し丁寧に動きを解説していきます。

まず REDUCEの冒頭ですが

REDUCE(,SEQUENCE(k),

第1引数 初期値・・・空白
第2引数 配列 ・・・ SEQUENCE(k)
今回の場合 kは2なので {1;2}という配列

このようになっています。

今回のケースで、第2引数の配列は REDUCE内の実処理をするコールバック関数内では出番はなく、処理回数分の配列であればなんでも良いんです。

kが2だったら、2回処理をしたいんで 要素が2つの配列をSEQUENCE(2) で生成しているだけです。だから

REDUCE(,{0,0},

こんなんでもいいですし

REDUCE(,{"1回目","2回目"},

こんなんでも良いです。でもSEQUENE関数を使うのが一番簡単ですね。

LAMBDA(pv,cv,TOCOL(pv&","&x))

で、一つ前の処理の結果を pv、配列が1つずつ順番に取り出したものを cv と置いて、 TOCOL(pv&","&x) という処理を k回 ループさせています。

k=2の場合 1回目は

pvは 1回目は 初期値なんで 空白ですね。これを カンマを挟んで x( A B C D) を連結するので 

,A ,B ,C ,D 

このようになります。ここが配列処理になるので、式全体の一番外側にARRAYFORMLAが必要となります。

最後にTOCOLで縦1列に変換して

,A
,B
,C
,D

これが 1回目の結果です。そしてこれが2回目の処理の pvとなります。

2回目は 1回目の結果と x を同じく連結させて TOCOLなので、

このようになります。あとはこの結果 y を SPLIT関数で

SPLIT(y,",")

とすれば 完成です。この配列を対象としたSPLITによる分割にも ARRAYFORMULA が必要になります。

以前の noteでも触れましたが、ARRAYFORMULAは一つあれば内部 全体に効果があるんで、とにかく一番外側に付けましょう。

理解出来ましたでしょうか？

手順としては、「1つ前の結果と 横並びの元配列を カンマ区切りで連結して、縦1列にする。」これを 取り出す回数だけ繰り返して、最後に SPLITで分割。これだけです。

取り出す回数を 3（k =3）とすれば、64パターンを出力となります。

Q2. REDUCEを使った順列（重複なし）の式を作りたい

それでは本番です。Q1の回答の式

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,2
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,TOCOL(pv&","&x))
    ),
    SPLIT(y,",")
  )
)

こちらをベースに 重複なしの順列全パターン出力式を作るにはどうすればよいでしょうか？

画像のような結果を出したいってことです。一旦ヒントなしで考えてみましょう！

わからない場合は 回答の中 に解説がありますので、そこを見ながら進めてみましょう。








↓↓↓
ここから回答



A2. REDUCEを使った順列（重複なし）の式を作る

いきなり回答ではなく、解説しながら進めてみましょう。

重複を排除するポイントは処理の中の

ここです。

ここで pv（縦の要素） の中に x（横の要素）と一致するものが含まれている場合は空白とする、一致する要素を含まない場合だけ カンマ区切りで連結する。と 処理を分岐させれば良さそうって考えつきます。

2回目の処理だとわかりにくいので、3回目の処理でイメージしてみましょう。

,A,D の行を見た時に、当然ですがこれは Aを含んでいるので Aの列との組み合わせ 本来であれば ,A,D,A が生成される箇所ですが空白とします。

同様にBやCは ,A,D には「含まれていない」ので連結した ,A,D,B や ,A,D,C は作成。Ｄは含まれるので ,A,D,D の箇所は空白。

こんな処理をする式を組みます。

含むを判定する式として FINDを使う方法もありますが、今回はREGEXMATCH関数を使ってみましょう。

REGEXMATCH関数は 正規表現のパターンにマッチするかどうかを判定する関数ですが、シンプルに含む時はTRUE、含まないときはFALSEを返す関数としても使えます。

※第2引数に正規表現で特別な意味合いをもつ メタ文字が使われていないことと、第1引数、第2引数ともに数値ではなくテキスト(文字列)である必要があります。

=ARRAYFORMULA(IF(REGEXMATCH(A3:A14&",",","&B2:E2&","),,A3:A14&","&B2:E2))

2回目の結果が A2:A14セルに展開されている想定での式

「含む」の判定で 元の配列に 「青りんご」と「りんご」が含まれている時に 「りんご」を含むで判定してしまうと 「青りんご」でヒットしてしまいます。

この誤判定を避ける為にカンマを前後につけることで厳密化しています。

青りんご　・・・　りんご　を含むと判定してしまう
,青りんご,　・・・　,りんご,　を含むと判定しない

また、カンマを 連結することで 元の配列の要素が数値であった場合でも文字列化して REGEXMATCHで判定できるという利点もあります。


あとは、この結果を TOCOLで1列にする時に 第2引数を1として空白を除去すればOKです。

これを Q1 の回答の元の式に当てはめると

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,2,
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        TOCOL(IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x),1)
      )
    ),
    SPLIT(y,",")
  )
)

こんな式になります。これが回答です。

pv は後ろだけで前に カンマをつけなくてよいのは、Q1の解説で書きましたが 初期値が空白であるため自動的に1回目の処理で 

,A
,B
,C
,D 

このような先頭がカンマで始まる文字列の配列となるからです。

重複ありの式からの変更ポイントは

=ARRAYFORMULA(LET(
x,A2:D2,
k,2,
y,REDUCE(,SEQUENCE(k),LAMBDA(pv,cv,
TOCOL(IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x),1)
)),SPLIT(y,",")))

この太字部分ですね。

k をセル参照にして取り出す数を変えて動きをみてみましょう。

右側の数値は式には含んでおりません

いい感じですね。しっかり処理できています。

元の要素が数値だった場合や

要素のキーワードが部分的に重複していても問題ないです。

REDUCEを使った順列の全パターン出力式、とりあえず完成です。

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,F2,
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        TOCOL(IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x),1)
      )
    ),
    SPLIT(y,",")
  )
)


REDUCE関数を使った順列の式をベースに組み合わせを考える

続いて、この順列の式をベースに組み合わせの式を考えてみましょう。

ポイントとなるのは

IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x)

この部分です。

組み合わせ として成り立つの条件

順列は 同じ要素が使われている（含まれる）ケースのみ除外すれば良かったわけですが、組み合わせの場合はもう少し複雑です。

前回の序盤で 数値をベースに考えた際は

「自分の右側に自分以下の数値が登場する」を組み合わせとして採用する条件としました。

0,1,2
「必ず右側の数字は左側の数字より大きい」のでこれは組み合わせ
　　　
0,2,1
1,0,2
1,2,0
2,0,1
2,1,0　
これらは自分の右側に自分以下の数値が登場しているので 除外

今回は数値ではなく 元となる配列（セル範囲）で考えているので

「自分の右側には、元の配列（横ならび）において自分より右側にあるものだけが登場する」

このように考えることができます。

これは 4つの要素から3つを取り出した時も同様ですね。

この条件を数式にして 順列の式の

IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x)

この部分を置き換えれば、組み合わせの式と出来そうです。

お題いってみましょう！


Q3. 順列の式をベースにして 組み合わせの式を作りたい

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,F2,
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        TOCOL(IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x),1)
      )
    ),
    SPLIT(y,",")
  )
)

この式の

IF(REGEXMATCH(pv&",",","&x&","),,pv&","&x)

この部分だけを変更して

こんな感じで組み合わせの全パターン出力が出来る式を作ってみましょう！

さっぱりわからないという人も、まずはチャレンジしてみましょう。








↓↓↓
ここから回答です。




A3. 順列の式をベースにして 組み合わせの式を作る

回答です。

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,F2,
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        TOCOL(IF(IFERROR(XMATCH(CHOOSECOLS(SPLIT(pv,","),-1),x),0)>=XMATCH(x,x),,pv&","&x),1)
      )
    ),
    SPLIT(y,",")
  )
)


「自分の右側には、元の配列（横ならび）において自分より右側にあるものだけが登場する」

この条件は

「pv をカンマで分割した時の一番右側（最後）の要素 の元の配列（横ならび）の位置（何番目か） よりも 大きい一の要素だけが登場する」

と言い換えることができます。

  • 分割はSPLIT関数

  • 一番右側の要素を取得するのは CHOOSECOLS関数で引数を -1とする方法

  • そして位置（何番目か？）は XMATCH関数（MATCH関数でも可）

が使えますね。

CHOOSECOLS関数は第1引数で指定した 配列に対して、第2引数以降の数値の 列番号だけを取り出すことが出来る関数です。

これが元となる横並びの配列の中で何番目かをXMATCHで取得します。

XMATCH(x,x) の方は 単に {1,2,3,4} っていう横並びの要素数の連番ですね。

この数字を比較した結果を IF関数の条件として

このように

前回の結果(pv) を分割した一番最後（右）の要素の
元配列（ｘ）における位置（数値）
>=　
ｘの要素の位置 （数値）

この結果が
TRUEなら 空白
FALSEなら カンマを挟んで連結

という式となっています。

でも、XMATCH(x,x) はSEQUENCE(1,COUNTA(x)) で置き換えれますし、 REDUCE内で毎回計算するの無駄ですよね。

これは外に出しちゃいましょう。

=ARRAYFORMULA(
  LET(
    x,A2:D2,
    k,F2,
    array,SEQUENCE(1,COUNTA(x)),
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        TOCOL(IF(IFERROR(XMATCH(CHOOSECOLS(SPLIT(pv,","),-1),x),0)>=array,,pv&","&x),1)
      )
    ),
    SPLIT(y,",")
  )
)

REDUCEを使った組み合わせの全パターン出力式も完成です！


引数で順列、組み合わせ の切り替えができる 全パターン出力式を 名前付き関数にしよう 

最後に REDUCEを使った 順列、組み合わせの全パターン出力式を 一つの式にまとめて 名前付き関数化できる形にしてみましょう。

最終イメージは

こんな感じでモードの切り替えで、1（TRUE）の時は組み合わせ、0（FALSE）の時は 順列の全パターンを出力する式です！

最後のお題、いってみましょう！！


Q4. 順列、組み合わせの切り替えができる全パターン出力式をつくりたい

作りたい式は

元となる横一行の配列をx （A2:D2）、抽出数を k（F2）、モードをCOMBIN_MODE（H2）として

=LAMBDA(x,k,COMBIN_MODE,【ここの式を作る】)(A2:D2,F2,H2)

こんな式を考えてみましょう。これが出来れば名前付き関数化できますね。

式が長くて複雑に感じるかもしれませんが、順列の式と組み合わせの式を一つにまとめるだけです。

やってみましょう！








↓↓↓
ここから回答です。




A4. 順列、組み合わせの切り替えができる全パターン出力式をつくる

回答です。

=LAMBDA(x,k,COMBIN_MODE,
  ARRAYFORMULA(LET(
    array,SEQUENCE(1,COUNTA(x)),
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        LET(
          _cf,IF(
            COMBIN_MODE,
            IFERROR(XMATCH(CHOOSECOLS(SPLIT(pv,","),-1),x),0)>=array,
            REGEXMATCH(pv&",",","&x&",")
          ),
          TOCOL(IF(_cf,,pv&","&x),1)
        )
      )
    ),
    SPLIT(y,",")
  ))
)(A2:D2,F2,H2)


こんな式になります。

IFをネストしてもいいんですが、REDUCE内でLETを使って条件式 conditional function を _cfと置いた方がわかりやすく書けます。

あとはこれを 名前付き関数として

関数名のセンスがいまいち

こんな感じで登録するだけ。

式を途中まで打てば候補に登場するので

選択して 3つの引数を指定すれば

こんな感じで 順列、組み合わせを切り替えて全パターン出力できます。

ちなみに 第1回の BASE関数を使った 順列式は 7つの要素から7つ取り出すケースだと 25秒以上かかっていましたが、今回の式であれば

この通り瞬殺です。

BASE関数を使った式に比べるとだいぶ処理が軽くなってますね。


文字列化しない方法も考える

今回紹介した REDUCEを使った順列、組み合わせの全パターン式は、縦横でクロスさせて条件判別をした上で、カンマ区切りで連結、最後に SPLITで分割という手法を用いました。

この方式は処理が割とわかりやすいのと、REDUCEは1つだけで繰り返し処理は 取り出す個数 （ｋ）のみなんで処理が軽いという利点があります。


文字列化する　REDUCE 順列・組み合わせ式の弱点

しかし、この式には1点だけ弱点があります。

それは、対象とする 要素が 頭に0がついた 数値のみで構成された文字列だった場合です。

たとえば 0001や0002 のような 配列から 順列や組み合わせを取得しようとすると、

このように0が消えて数値化してしまいます。

これは最後に分割する際に、分割後の値が数値と見なせるものは自動で数値化してしまう SPLIT関数の仕様によるものです。

これを回避する方法は SPLIT関数の超応用例の回のnoteで触れていますが、ちょっと面倒なんですよね・・・。

というわけで、例外的なケースにも対応できる 文字列として結合しない REDUCEによる 順列・組み合わせ式を最後に紹介しておきましょう。

こちらは複雑なんで お題ではなく紹介のみとします。


REDUCE関数を使った途中で文字列化しない 順列・組み合わせの全パターン出力式

途中でカンマ区切りで結合した文字列にしない式がこちらです。

=LAMBDA(x,k,COMBIN_MODE,
  LET(
    y,TOCOL(x,1),
    n,SEQUENCE(ROWS(y)),
    REDUCE(
      ,SEQUENCE(k),
      LAMBDA(pv_1,cv_1,
        REDUCE(,SEQUENCE(ROWS(pv_1)),
          LAMBDA(pv_2,cv_2,
            LET(
              r,INDEX(pv_1,cv_2),
              fx,FILTER(
                  IF(INDEX(r,,1)="",y,IFERROR(HSTACK(r,y),r)),
                  IF(
                    COMBIN_MODE,
                    XMATCH(CHOOSECOLS(r,-1),y)<n,
                    COUNTIF(r,y)=0
                  )
              ),
              IF(ISNA(fx),pv_2,IF(pv_2="",fx,IFERROR({pv_2;fx},fx)))
            )
          )
        )
      )
    )
  )
)(A2:D2,F2,H2)

REDUCEが2回登場する、いわゆる 2重ループを使った式ですね。処理内容としては本当は3重ループで、愚直に書くとREDUCEを3回使う式になるんですが、さすがにこれは重すぎでした・・・。

一番内側の REDUCE1回を FILTER関数内の配列処理に変えた式となっています。

もう少し精査できるのかもしれませんが、いぜれにせよ胃もたれしそうな式ですw

とりあえずこちらの式であれば、頭が0で数字のみの文字列を対象とした場合でも、文字列のまま処理ができます。

あと利点ではないですが、文字列化せず 二重ループで処理って点が クールって感じる人がいるかもｗ

ただし処理的には REDUCE2回でFILTERも使っているので結構重いです。

やはりmir的におススメは

=LAMBDA(x,k,COMBIN_MODE,
  ARRAYFORMULA(LET(
    array,SEQUENCE(1,COUNTA(x)),
    y,REDUCE(,SEQUENCE(k),
      LAMBDA(pv,cv,
        LET(
          _cf,IF(
            COMBIN_MODE,
            IFERROR(XMATCH(CHOOSECOLS(SPLIT(pv,","),-1),x),0)>=array,
            REGEXMATCH(pv&",",","&x&",")
          ),
          TOCOL(IF(_cf,,pv&","&x),1)
        )
      )
    ),
    SPLIT(y,",")
  ))
)(A2:D2,F2,H2)

こちらかなと。


REDUCE関数の3重ループのいい感じの式 2024/11/05追記

=LAMBDA(array,k,COMBIN_MODE,
  IF(k=1,TOCOL(array),
    LET(x,SEQUENCE(COUNTA(array)),
      y,REDUCE(x,SEQUENCE(k-1),LAMBDA(pv_1,cv_1,
          REDUCE(,SEQUENCE(ROWS(pv_1)),LAMBDA(pv_2,cv_2,VSTACK(pv_2,
            REDUCE(,x,LAMBDA(pv_3,cv_3,
              LET(r,INDEX(pv_1,cv_2),
                IF(COUNTIF(r,IF(COMBIN_MODE,">=","=")&cv_3),
                  pv_3,VSTACK(pv_3,{r,cv_3})
                )
            )))
          )))
      )),
      FILTER(VLOOKUP(INDEX(array,,1),array,y,FALSE),INDEX(y,,1)<>"")
    )
  )
)(A2:D2,F2,H2)

REDUCE内で全て処理するのを諦めて、一旦もとになる要素を数値化、最後にFILTER関数内で空白やエラーを含む不要な行を削除しつつ、VLOOKUPで 元の要素に戻すという REDUCE関数 3重ループの 順列・組み合わせ 全パターン出力式を作ってみました。

順列と組み合わせの切換えも

COUNTIF(r,IF(COMBIN_MODE,">=","=")&cv_3)

数値で処理してるんでシンプルな記述で対処できます。

式がだいぶ精査できたのと、処理も少し軽いかなと思います。

本当はじっくり解説が必要だと思いますが、1万文字を超えてしまったんで今回は紹介のみとします。


すごいぞREDUCE関数

今回は LAMBDAヘルパー関数の REDUCE関数を使った 順列・組み合わせの全パターン出力式に挑戦しました。

やはり REDUCE関数はつおいです。でも難しいですよね。

今回の式は REDUCEを使わず LET、LAMBDAの再帰式で書くこともできます。

もし興味があれば挑戦してみてください。

重たい関数シリーズが続いたので、次回は少し軽めのネタにしようかなと思いますｗ


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

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