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

今回はGoogleスプレッドシートのシート関数ネタ。「順列と組み合わせ」の全パターンを出力する数式を考えてみたいと思います。

GASを使わずシート関数の組み合わせのみで実現します。

ただし、今回は前段階となる話と LETやLAMBDA登場前の古いやり方の解説となるので、新関数を使ったイマドキ（最先端）の 順列、組み合わせの式は次回以降となります。

古いやり方でも ExcelやGoogleスプレッドシートでよく使う関数の活用パターンは参考になるかと思います。

是非最後までお読みください。

前回の noteはGoogleドキュメントに新しく実装された タブ機能について書きました。

順列と組み合わせ を理解する

「順列」とか「組み合せ」（組合せ）なんて聞くと、数学を思い出して苦手な人は ウゲーってなる人もいるかもしれませんｗ

でも「n種類の中から r個を取り出した時のパターン」が必要になるケースは、日常生活や仕事においても結構あります。

順列 と 組み合わせ の違い

まず 順列と組み合わせの違いですが、簡単に言うと

取り出したものの並び順を考慮するかどうか

の違いです。

このような違いがあります。

「A, B, C, D」の4つの要素から 2つを選ぶとき

順列の場合は

このように 4×3 = 12 で12パターンとなりますが、

組合せの場合は 

順列の中から 並び順を変えただけの

B,A
C,A
C,B
D,A
D,B
D,C

の6つは除外され

(4 × 3) / ( 2 × 1) = 6 で6通りとなります。

この順列、組み合わせ、それぞれのパターン数の計算方法を忘れちゃっている人は、解説しているサイトが色々あるのでそちらを確認ください。

Excel、Googleスプレッドシートの PERMUT関数、COMBIN関数

この順列の数や組み合わせの数を求める関数が、Googleスプレッドシートには（Excelにも）用意されています。

それが

順列のパターン数を求める PERMUT関数

そして 組み合わせのパターン数を求める COMBIN関数です。

今回のケースは 4つの中から 2つを取り出すので、 

このように 第1引数が4、第2引数が 2 とすれば、順列、組み合わせ、それぞれの パターン数（何通りか？）が計算できます。

ちなみに

PERMUT関数に対して PERMUTATIONA関数
COMBIN関数に対して COMBINA関数

という類似関数がありますが、これらは n個の要素の中から 重複して取り出せるという条件を付加した 順列と組み合わせのパターン数を返す関数です。

つまり 「A, B, C, D」の4つの要素から 「A, A」や「B,B」といった同じものを取り出すパターンもアリとするってことです。

この場合のパターン数は

こうなります。

4つの要素から 2個を取り出すだったら、「A,A」「B,B」「C,C」「D,D」の4パターン増えるだけなんでわかりやすいですが、4つの中から3個を取り出すだと「A,A,A」だけじゃなくて「A,A,B」や「A,C,A」なども出てくるので、重複を許可しない場合と比較して、一気にパターン数が増えます。

PERMULT( 4, 3 ) = 24 に対して
PEMUTATIONA( 4, 3 ) = 64 と 大きく差がありますね。

ちなみにこの重複ありの順列パターン数 64は 4^3 という計算 でも同じ結果が得られます。

これらの関数名は 英語の「順列」「組合せ」からきてるんで、英語が分かっている人だと、すんなりイメージできます。

このように パターン数が幾つあるかを出力する 関数は用意されているんですが、パターンの組み合わせを全て出力するような関数は 残念ながら用意されていません。

この 順列や組み合わせの全パターン出力を既存のシート関数を組み合わせた数式で実現しよう！ が、今回のテーマです。

【旧方式】順列の全パターンを出力する式を作ろう（まずは重複ありから）

冒頭でも書いた通り、今回はこの順列全パターンを出力する式を、LET、LAMBDA登場前の関数で作ります。

昔はこんな式でやってたんだなー。

くらいの感想でもいいですが、出来れば作成プロセスを通じて 知らない関数や挙動の理解を深めていただければと思います。

普通の重複なしの順列の前に、重複ありの順列を考えてみましょう。実はこっちの方が割と簡単です。

他のアプローチもありますが、今回は BASE関数を使う方法で進めてみましょう！

Step1. BASE関数をスピらせよう

BASE関数は10 進数を別の底（〇進数）のテキスト表現に変換する関数です。

一番有名なのは2進数ですね。 0と1のコンピュータの世界です。

このように 10進数の2は 2進数だと 10（イチゼロ）となります。要は2毎に桁が増えるってことです。

第3引数で桁（文字数）を指定すれば、

このように0埋めで桁を揃えることが出来ます。

ちなみに  11や12といった 10以上の底 を指定した場合は 0～9の数字て賄えないので、アルファベットが登場します。

低を12とした場合は、12で一桁繰り上がるんですが、10、11を表す単体の数字がないため、10は A、11は Bとなります。

このBASE関数を使った 全パターン出力テクニックがあります。お題いってみましょう！

Q1. BASE関数を使った一つの式で 000～333までの64パターンを出力したい

このように0,1,2,3 の4つの数字を使う 3桁の4進数の全 64ターンを1つの式で出力させるには、どんな式をいれればよいでしょうか？

BASE関数を活用します。考えてみましょう！

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

A1. BASE関数を使った一つの式で 000～333までの64パターンを出力する

回答です。

BASE関数を使った 000から 333 までは 64パターン です。

これは 0,1,2,3 という4つの要素から「重複を許可して」 3つを取り出す際の組み合わせ全パターン

と同じと考えることが出来ますよね。

つまりパターン数は

となります。スタートは 0からとしたいので、0～63までの 64個、これを縦方向に生成するにはSEQUENCE関数を使って

こんな式を作ればよいです。

ARRAYFORMULAを使っているので　=SEQUENCE(4^3)-１　でも良いです。（こっちの方が２文字少ない）

SEQUENCE関数については 過去noteを参照。

これを BASE関数の第1引数とすればよいのですが、配列に対して処理をするので ARRAYFORMULA関数が必要です。

ARRAYFORMULA関数も 過去noteで特集しています。

まとめると

このような式になります。

Step2. 1文字ずつ分割しよう

0,1,2,3 の4つの要素から、重複アリで3つを取り出した順列 64パターンは生成できました。

しかし、このままだと扱いにくいので出来れば

右のように3列の数値データとしたいわけです。

過去に一度登場したお題ですが、これもやってみましょう。

Q2. BASE関数で出力された各セル3文字の1列データを1文字ずつ分割して 3列の数値データに変換したい

Q1 の回答の式

これをさらに別の関数と組み合わせます。考えてみましょう！

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

A2. BASE関数で出力された各セル3文字の1列データを1文字ずつ分割して 3列の数値データに変換する

回答です。2パターンあります。

1つはMID関数と横に展開するSEQUENCE関数を使って、1文字ずつとりだす方法。

MID関数の第2引数の開始位置に　SEQUENCE関数で生成した

1,2,3 という 横方向に展開される配列を入れ、第3引数を1とすることで、

第1引数の1列データから

このように1文字ずつ順番に取り出すことができます。

MID関数で取り出した結果は文字列である為、演算子を使って数値に変換します。

MIDのSEQUENCEによる1文字ずつ取得する配列処理にもARRAYFORMULAが必要になるので、ARRAYFORMULAは一番外側につけて全体に効かせればOK。

A2. BASE関数で出力された各セル3文字の1列データを1文字ずつ分割して 3列の数値データに変換する（別解）

別解です。区切り文字を入れてSPLIT関数で分割という方法もあります。

まず REGEXREPLACE関数で文字列の空白箇所（文字と文字の間、先頭と末尾も含む）を ","（カンマ）に置換します。

空白を置換することは　SUBSTITUTEでは出来ません。この空白置換が出来るのも 正規表現を扱える REGEXREPLACE関数の魅力です。

先頭と末尾にも カンマが入ってしまいましたが、SPLITで分割した際に空白無視できるので 問題ありません。

これを SPLIT関数で ","カンマを 区切り文字に指定して分割すればOK。

MID関数の結果と違って、SPLIT関数で分割した場合は 自動で 数字のみのセルは数値に変換されます。

SPLIT関数も過去noteで取り上げています。

先ほどと同じくARRYAOFMRULA関数を一番外側につけることで、中の式は全て 配列処理効果が付与され、BASE関数による底の変換、REGEXREPLACEの置換、そしてSPLITの分割、これら全てが配列処理となります。

MID＋SEQUENCE、REGEXREPLACE＋SPLIT、どちらを使ってもよいですが、列数が多いと MID＋SEQUENCEの方が処理回数が多くて重いかもしれません。（未計測）

とりあえず 0,1,2,3 の4種類の要素から「重複を許可して」3つを取り出し並べた時の全64パータン出力ができました。

Step3. 数値を実データに置き換える

「できましたー。」と言われても・・・。

いやいや数値じゃなくて実データの順列パターンが欲しいんだけど。。って思いますよね。

田中、山田、佐藤、鈴木 の 4人の中から 重複アリで3つ取り出した順列の全パターンを出力、

つまり、こう置き換えたいってニーズが高いかと思います。これに挑戦してみましょう。

Q3. 数値の重複アリ順列全パターン配列を 実データに置き換えたい

実データは A1:D1に 横ならびに4つ入っているとします。

先ほどのStep2で作成した 0,1,2,3の 4つの要素を実データにどのような式で置き換えれば良いでしょうか？

ただし 新関数登場前の時代の式なので、MAP関数など LAMBDAヘルパー関数を使わずにという縛りがあります。

考えてみましょう。

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

A3. 数値の重複アリ順列全パターン配列を 実データに置き換える

回答です。

なんで？ってなるかもしれませんが、Googleスプレッドシートの場合はこんな時はVLOOKUPを（かつては）使ってました。

解説していきます。

実データ に1から番号をふり、先ほどのStep3の結果を +1 することで
0,0,0～3,3,3の64 パターンを 1,1,1～4,4,4 とすれば 実データの引き当てができそうですよね。

+1をすることで数値化も出来るんで、数値化の為だけに付けていた先頭の --は不要となります。

この数値を使って値を引き当てる方法で、まず思いつくのが INDEX関数を使う方法です。しかし・・・

Googleスプレッドシートは、INDEX関数の第2引数、第3引数に配列が取れません。

INDEX関数を組み合わせてもスピらず（配列が返らず）、実データの引き当てには使えないってことです。

これは、Excel（スピル対応バージョン）だといけるんです。

GoogleスプレッドシートはINDEX関数の仕様が違うみたいんで、同じようにスピらないんですよね。ギギギ（はだしのゲンで使われる擬音）

もちろん今だったら Googleスプレッドシートの場合はMAP関数を使ってINDEXをスピらせる方法があります。

しかしMAPなどLAMBDAヘルパー関数がなかった当時はどうしていたか？

この手の処理の場合、かつては VLOOKUP関数に頼っていました。

VLOOKUPのキーを A1（田中）、検索範囲を A1:D1として、第4引数 FALSEで完全一致で検索。当然 A:D1 の行が検索結果としてヒットするわけですが、

ここでARRAYFORMULAと組み合わせて 第3引数の 列番号を 配列指定することで、結果を第3引数の配列と同じサイズにスピルさせることが出来ます。

第3引数に {2,3,4} のように横方向1行の配列を指定する方法なら知ってるよって人もいるでしょうが、実は縦横に広がる複数行複数列の配列も指定できるんですね。（※第1引数が単体の場合のみ利用可）

ExcelのVLOOKUPでは出来ないテクニックですし、GoogleスプレッドシートでもXLOOKUPでは出来ない、VLOOKUPだけが出来る縦横スピルの応用技です。

ちなみに選択肢の中から〇番目をピックアップするので、CHOOSE関数 が思い浮かぶ人もいるかもしれませんが、残念ながら CHOOSE関数は選択肢を1つ1つ第2引数、第3引数… と記述する書き方しかできません。

つまり

CHOOSE(2,A1:E1）　や　CHOOSE(3,{”田中","山田","佐藤","鈴木"})  といった記述には対応していない為、今回のケースでは利用できないのです。残念！

これで実データを使った 重複アリ順列全パターン出力式が出来ました。
最後にこれを汎用性のある形にしましょう。

Step4. 式を汎用性ある形にする

Step3の式

これは、 4つの要素から3つを取り出すという条件のもとで作成したのです。

元になる要素の範囲と 取り出す数 、この2つを指定するだけで処理される汎用性のある式にしましょう。

Q4. セル参照で汎用性のある式にしたい

対象の要素が入った範囲（空白セルや重複はない）を A1:E1、
取り出す数を入れるセルを B3 として、

これらを参照するだけで他は変更する必要のない式を作りましょうというお題です。

ベースの式は出来上がってるので、難しくはないですね。考えてみましょう！

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

A4. セル参照で汎用性のある式にする

回答です。

長くなってきたのでインデントをつけた式で。

=ARRAYFORMULA(
  VLOOKUP(
    A1,
    A1:E1,
    MID(BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3),SEQUENCE(1,B3),1)+1,
    FALSE
  )
)

まず、もとになる要素数が必要になるので、COUNTA(A1:E1) で 5という数を取得して、これを元にSEQUENCE関数で連番を生成、BASE関数で底を変換とします。

VLOOKUPの 第1引数 A1は INDEX(A1:E1,,1)でもいいですね。あとは 取り出す数の部分を B3 に置き換えればOK。

式内の 1 や 0 の箇所は変動しないのでそのままで良いです。

確認してみましょう。

データ量が多いので全て確認してませんが、この式の結果の行数とPERMUTATIONA関数の返す数が一致しているので、全パターン出力できてることがわかりますね。

順列全パターン（重複取得アリ）出力の汎用的な式が完成しました～。

【旧方式】順列の全パターンを出力する式を作ろう（重複なしに挑戦）

ここまでの流れで完成した式は、あくまでも 重複アリで取り出せる式です。

順列は普通は重複なしで考えるケースが多いですよね。それでは、先ほどの式を 重複なしの一般的な順列の全パターン出力式に改良していきましょう！

Step5. 同じ数値が入るケースを除外する

「重複なしで取り出す」をどのように考えればよいでしょうか？

先ほどの重複アリの章の Step1まで戻ります。

0,1,2,3 の4つから 2つを取り出して並べるパターンを考えた場合、Step1の式は

このようになります。

順列として必要なのは、この16パターンのうち 00や 11、22、33という 同じ数が出てくる要素を除外した 12パターンです。

4つのうち3つを取り出すケースだと、重複アリは 64ありますが、そこから同じ数字が2回以上使われる要素が 40個あるので、順列のパターンは 24となります。

このように同じ数字が使われている要素を除外したいわけです。

逆にここだけクリアできれば、残りのステップは既に出来ているので、順列の全パターン出力式は完成間近です。

ちょっと難しいですが、これをお題として考えてみましょう。

Q5. BASE関数で生成した 64パターン（64行）の配列を 同じ数字が2回登場するものを除外した配列にしたい

とりあえずわかりやすくする為に、この式を A3に入れ、生成される 0,1,2,3 から 3つを重複アリで取り出した順列 000～333 の 64パターンをA3以下に出力して A3:Aを対象として001や 121、333など 同じ数が2回以上使われている要素を除外したいってお題でいきましょう。

考えてみましょう！

少し難しいので 考え方のヒントを。
↓↓

条件にマッチしない要素（行）を除外して絞り込むので A3:Aを 第1引数とした FILTER関数の出番です。

また個々のセルだけをみて同じ数が登場するか否かを判定するのではなく、0,1,2,3 という4つの要素から 011 は 0と1の2つの要素しか使われていない、222だと 1つの要素しか使われていない、つまり 213のように 3つの要素が使われているかどうかを判定する式を考えてみると良いかもしれません。

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

A5. BASE関数で生成した 64パターン（64行）の配列を 同じ数字が2回登場するものを除外した配列にする

回答です。

REGEXREPLACE関数で置換した後の文字列の長さをLEN関数で取得して、それが1と一致したものだけを FILTER関数で残しています。

中身をくわしくみていきましょう。

まず一番内側の REGEXREPLACE関数ですが、これはStep2の別解でも登場した 正規表現を使って文字列を検索・置換することが出来る関数です。

REGEXREPLACE("0123","["&A3:A&"]",)

第1引数の対象となる文字列は "0123" という 要素の数字を全て繋げた文字列

第2引数の検索する正規表現は  "["&A3:A&"]" と配列になっています。

これによって A3:Aは

000　→　[000]
001　→　[001]
002　→　[002]
003　→　[003]
010　→　[010]

こんな感じで A列の3桁の数字で構成される文字列が [ ] で囲われます。

正規表現では [abc] のようにした場合、カッコ内の個々の一文字のいずれかに一致するか？（または検索）として機能します。

その為、第3引数の置換後を 空 とした場合

=ARRAYFORMULA(REGEXREPLACE("0123","["&A3:A&"]",))

の結果は、"0123"という文字列から [ ] 内の単体数字のいずれかに一致したものが削除（空白に置換）されたテキストの配列となります。

ここで 000 や 001、020 のような 同じ数字が2回以上登場する場合は、置換後の文字の長さが 2や3、重複のない 012、013、021 の場合は 残った文字は1文字のみとなっているのがわかります。

つまり、この結果をLEN関数で取得して 1（元の要素の数 - 取り出す数）と一致するか？を条件としてFILTER関数で絞り込む

こんな式を作れば、数字が重複しない要素、つまり順列だけに絞り込めるわけです。

Step6. FILTER式を作業列無しの汎用的な式にしよう

それでは Step5の式を A列を作業列として使わずに書いてみましょう。

Q6. Step5の数値の順列を出力する式を汎用性のある式にしたい

作業列を使わないことに加え、要素の数（以下の場合は4）を A1セル、取り出す数を B1セルを参照する形として式を作ってみてください。

煩雑ですが、当時のようにLETを使わない式とします。

考えてみましょう！

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

A6. Step5の数値の順列を出力する式を汎用性のある式にする

回答です。

=FILTER(
  BASE(SEQUENCE(A1^B1,1,0),A1,B1),
  LEN(REGEXREPLACE(
    CONCATENATE(SEQUENCE(A1,1,0)),
    "["&BASE(SEQUENCE(A1^B1,1,0),A1,B1)&"]",
  ))=A1-B1
)

 FILTERの中で どうしても BASE関数の式が2回登場するので、長くなってしまいます。

数字の重複使用確認で使った "0123" の文字列は

CONCATENATE(SEQUENCE(A1,1,0))

で生成しています。範囲を区切り文字なしで結合するなら Googleスプレッドシートの場合は CONCATENATE関数の出番です。

でもExcelと仕様が違うんでわかりにくいのと、関数名が長いんですよね。。JOIN関数の 区切り文字を空として =JOIN(,SEQUENCE(A1,1,0))　でもよいです。

動かしてみると正しく機能しているのがわかりますね。

数値の順列を出力できました。 

Step7. Step4の式と組み合わせて順列全パターン出力式を完成させよう

最後に Step6で作成した 式

=FILTER(
  BASE(SEQUENCE(A1^B1,1,0),A1,B1),
  LEN(REGEXREPLACE(
    CONCATENATE(SEQUENCE(A1,1,0)),
    "["&BASE(SEQUENCE(A1^B1,1,0),A1,B1)&"]",
  ))=A1-B1
)

 を Step4で作成した

=ARRAYFORMULA(
  VLOOKUP(
    A1,
    A1:E1,
    MID(BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3),SEQUENCE(1,B3),1)+1,
    FALSE
  )
)

この式と合体させて 順列パターン出力式を完成させましょう。

Q7. セル参照の汎用性のある順列式を作りたい

A1:E1 セルの要素 から B3セルに入れた数 （画像だと4）を 重複なく取り出して並べた時のパターンを全て書き出す式を作るにはどうすればよいでしょうか？

煩雑な式になるんで、ここで LET関数使っちゃってもOKとします。

先ほど書いた通り Step4の式とStep6の式の組み合わせです。

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

A7. セル参照の汎用性のある順列式を作りたい

回答です。

=ARRAYFORMULA(
  VLOOKUP(
    A1,
    A1:E1,
    MID(
      FILTER(
        BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3),
        LEN(REGEXREPLACE(
          CONCATENATE(SEQUENCE(COUNTA(A1:E1),1,0)),
         "["&BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3)&"]",
        ))=COUNTA(A1:E1)-B3
      ),
      SEQUENCE(1,B3),1
    )+1,
    FALSE
  )
)

VLOOKUPの第3引数のMID関数の第1引数にFILTER関数の式を突っ込んで、セル参照箇所を書き換える感じでいいですね。

FILTER関数の中に入れちゃう書き方でもよいですが、結局最後のVLOOKUPでARRAYFORMULAが必要になるんで式は短くなりません。

=ARRAYFORMULA(
  VLOOKUP(
    A1,
    A1:E1,
    FILTER(
      MID(BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3),SEQUENCE(1,B3),1)+1,
      LEN(REGEXREPLACE(
        CONCATENATE(SEQUENCE(COUNTA(A1:E1),1,0)),
        "["&BASE(SEQUENCE(COUNTA(A1:E1)^B3,1,0),COUNTA(A1:E1),B3)&"]",
      ))=COUNTA(A1:E1)-B3
    ),
    FALSE
  )
)

どちらも今どきはあまり見かけない煩雑な式ですね。

どうしてもLET関数の登場前だと同じ記述が登場するのでわかりづらいし長くなります。

これをLET関数で整理すると

=ARRAYFORMULA(LET(
  array,A1:E1,
  k,B3,
  n,COUNTA(array),
  x,BASE(SEQUENCE(n^k,1,0),n,k),
  VLOOKUP(
    INDEX(array,,1),
    array,
    FILTER(
      MID(x,SEQUENCE(1,k),1)+1,
      LEN(REGEXREPLACE(
        CONCATENATE(SEQUENCE(n,1,0)),
        "["&x&"]",
      ))=n-k
    ),
    FALSE
  )
))

こんな式になります。

LET関数を使えてる人なら だいぶ読みやすくなりましたね。

動かしてみましょう。

下まで入りきらないので全要素は見えませんが、PERMUT関数の結果と この数式で出力した行数 COUNTA(A5:A)の結果の数値が一致してるのがわかりますね。

順列の全パターンを書き出す式が完成しました。

ちなみに 今回の式の基本部分は、ほぼ「いきなり答える備忘録」さんで紹介している式と一緒ですｗ （たぶん当時参考にさせていただいたんだろうと思います）

BASE関数を使った 順列式の弱点

今回の順列式、LETやLAMBDAがない時代に作れた数式ではありますが、一旦重複ありの全パターンを生成してから絞り込むという処理である為、かなり遠回りな（無駄な計算が多い）式となっています。

BASE関数で 底を 11以上とすると アルファベットが登場するので、元になる要素数は 10までとする必要がありますし、それ以前に 計算負荷が高いので

このように 7つの要素から 6つを取り出すというケース （5040パターン）あたりから計算が重くなり

7つの要素から7つ取り出す順列の場合は 同じ5040パターンなのに、今回作成した式だと 一度 重複アリで 823543パターン（約 82万行）を生成してから絞り込むため、

このように結果出力まで 25秒程度かかります。

処理の重さを感じますし、これ以上の数だと固まっちゃいそうです。

というわけで、もう少し数の多い順列や今回は触れなかった 「組み合わせ」を出力する為には、BASE関数で 重複ありの全パターンを生成して絞り込む式とは別のアプローチが必要になるってことです。

ここで活躍するのが REDUCE関数などLAMBDAヘルパー関数 や LET関数 といった新関数。

次回は、過去関数の続きで「組み合わせ」全パターン出力式と、新関数を使った 順列・組み合わせの全パターン出力式に挑戦しましょう。