Excel関数で複数列の全組み合わせを作る数式

はじめに

複数列に並んだ候補値から全組み合わせ（直積）を作る例を示します。
たとえば、次のように列ごとに候補が並んでいるとします。

A	B	C
a	1	A
b	2	B
	3

これらの値を左の列から1つずつ選択して、取り出せる組み合わせは、各列の個数の総乗なのでこの場合は
$2 \times 3 \times 2 = 12$ 通り
全て書き出すと下記の通りとなります。

a 1 A
a 1 B
a 2 A
a 2 B
a 3 A
a 3 B
b 1 A
b 1 B
b 2 A
b 2 B
b 3 A
b 3 B

既出の方法だと エクセルの真髄 さんが公開されている以下の記事で数式が紹介されています。
https://excel-ubara.com/excel3/EXCEL046.html

本記事では QUOTIENT と MOD を使わない少しトリッキーな方法で下記数式を作成したので、仕組みを紹介します。

=LET(
    _table, A:C,
    _table_choose, LAMBDA(col, TOCOL(CHOOSECOLS(_table, col), 3)),
    REDUCE(
        _table_choose(1),
        SEQUENCE(COLUMNS(_table) - 1, , 2),
        LAMBDA(acm, col,
            LET(
                _next, _table_choose(col),
                _makeindex, LAMBDA(f_ij,
                    TOCOL(MAKEARRAY(ROWS(acm), ROWS(_next), f_ij))
                ),
                HSTACK(
                    CHOOSEROWS(acm, _makeindex(LAMBDA(i, _, i))),
                    CHOOSEROWS(_next, _makeindex(LAMBDA(_, j, j)))
                )
            )
        )
    )
)

列を取り出す関数

LET関数の先頭から見ていきます。まず1行目は入力に取る列の範囲に _table と名前を付けています。

_table, A:C,

次の行は _table から特定の列を取り出す CHOOSECOLS 関数と、「空白とエラーを無視する」範囲を取り出す TOCOL 関数を組み合わせています。これにより列全体の中から値が存在するセルのみを取り出すことが出来ます。

_table_choose, LAMBDA(col, TOCOL(CHOOSECOLS(_table, col), 3)),

REDUCE の中身

REDUCE 関数での処理を日本語で説明すると下記になります。

REDUCE(
    最初の列を取り出す,
    2番目以降の列の番号リストを作る,
    LAMBDA(前回値, 列番号,
        次の列を取り出す,
        行番号を作る関数,
        左と右の列をつなげる
    )
)

REDUCE 最初の2行は、初期値となる最初の列と2列目以降の列番号リストを作成します。

_table_choose(1),
SEQUENCE(COLUMNS(_table) - 1, , 2),

_table_choose は上記で説明した通り、1列目から「空白とエラーを無視する」範囲を取り出します。
SEQUENCE 関数は 2 から始まって _table 全体の列数までの連番を作成します。最初の入力例に当てはめると {2, 3} が格納されます。

次は繰り返し処理を行う関数の引数名と、列番号から次の列を取り出す処理です。

LAMBDA(acm, col,
    LET(
        _next, _table_choose(col),

acm, col, _next については最初のループでは以下の値が格納されます。

acm = {'a', 'b'}
col = 2
_next = {1, 2, 3}

行を選択するための数列を作る

以下の部分では列番号の数列を作成するためのユーティリティ関数を作成しています。

_makeindex, LAMBDA(f_ij,
    TOCOL(MAKEARRAY(ROWS(acm), ROWS(_next), f_ij))
),

初回ループのパラメータを当てはめると MAKEARRAY の内部は以下のようになります。

ROWS(acm) -> ROWS({'a', 'b'}) -> 2
ROWS(_next) -> ROWS({1, 2, 3}) -> 3

MAKEARRAY(ROWS(acm), ROWS(_next), f_ij)
  -> MAKEARRAY(2, 3, LAMBDA(i, _, i))  // i行を取り出す
    -> {1, 1, 1;
        2, 2, 2}
  -> MAKEARRAY(2, 3, LAMBDA(_, j, j))  // j列を取り出す
    -> {1, 2, 3;
        1, 2, 3}

それぞれを TOCOL で1列にすると、下記の様に二つの列が直積となる様な並びでインデックス列が作られます。¹

TOCOL({1, 1, 1;
       2, 2, 2})
  -> {1; 1; 1; 2; 2; 2}  // 左列の並び

TOCOL({1, 2, 3;
       1, 2, 3})
  -> {1; 2; 3; 1; 2; 3}  // 右列の並び

左右列の結合

作成した行番号の数列を使って、左列 acm と右列 _next を並べて、それらを HSTACK で横に連結します。

HSTACK(
    CHOOSEROWS(acm, _makeindex(LAMBDA(i, _, i))),
    CHOOSEROWS(_next, _makeindex(LAMBDA(_, j, j)))
)

初回ループでは以下のようになります。

CHOOSEROWS(acm, _makeindex(LAMBDA(i, _, i))),
  -> CHOOSEROWS({'a', 'b'}, {1; 1; 1; 2; 2; 2})
  -> {'a'; 'a'; 'a'; 'b'; 'b'; 'b'}
  
CHOOSEROWS(_next, _makeindex(LAMBDA(_, j, j)))
  -> CHOOSEROWS({1, 2, 3}, {1; 2; 3; 1; 2; 3})
  -> {1; 2; 3; 1; 2; 3}

上記リストが HSTACK によって結合されると以下の行列が作成されます。

a 1
a 2
a 3
b 1
b 2
b 3

次のループでは、上記の行列が acm に代入され、次の列番号 col 代入された状態処理が繰り返されて、_table の最後の列までを連結していきます。

まとめ

今回の数式は、複数列の候補一覧から全組み合わせを作るためのものです。
考え方をまとめると、次のようになります。

• TOCOL と CHOOSECOLS で各列の有効値だけを取り出す
• REDUCE でテーブルの1列目と、2列目以降の番号リストを使って1列ずつ繋げていく
• MAKEARRAY で直積用の行番号リストを作る
• CHOOSEROWS と HSTACK で実際の組み合わせ表を作成する

1. リストの要素を区切る記号について、カンマ(,)は行方向、セミコロン(;)は列方向を表す。 ↩