クロス結合・交差結合により組み合わせリストを生成するExcelの計算式

Excelでリストのクロス結合（交差結合）を実現する方法について解説します。以下の計算式を使うことで、2つの配列・リストの要素を全ての組み合わせで結合することができます。

=LET(_ArrayX, 配列1, _ArrayY, 配列2, HSTACK(TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)), TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE)))

元となる2つの配列は縦に展開する形状としてください。

2つのリストの全組み合わせパターンを生成する計算式。コピペで使用可能。

式の構造と効果

この計算式は、2つの配列の要素を全ての組み合わせで結合するために設計されています。それぞれの関数の役割を理解することで、式の動作を把握することができます。式を立体化して研究してみましょう。

=LET(
  _ArrayX, 配列1,
  _ArrayY, 配列2,
  HSTACK(
    TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)),
    TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE)
  )
)

「配列1」に「A1:A4」、「配列2」に「B1:B6」を割り当てると以下のようになります。

=LET(
  _ArrayX, A1:A4,
  _ArrayY, B1:B6,
  HSTACK(
    TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)),
    TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE)
  )
)

LET関数

LET関数を使うことで、中間変数を定義し、計算式の読みやすさとパフォーマンスを向上させることができます。以下の変数が定義されています。

• _ArrayX: 配列1を格納します。
• _ArrayY: 配列2を格納します。

COUNTA関数

COUNTA(配列)は、配列の要素数をカウントします。

EXPAND関数

EXPAND関数は配列のサイズを変えるものです。拡張すると「埋める値」で指定した値か、エラー値「#N/A」を返します。

EXPAND(配列, 行数, [列数], [埋める値])は、配列を指定した行数と列数に拡張します。ここでは、配列1を配列2の要素数に、配列2を配列1の要素数に拡張しています。

IFERROR関数

IFERROR関数は、計算結果がエラーの場合に指定した値を返します。ここでは、EXPAND関数がエラー「#N/A」となった場合に元の配列を返すようにしています。

TOCOL関数

TOCOL関数は、配列を1列に変換します。これは結果をリスト形式にするためです。

HSTACK関数

HSTACK関数は、複数の配列を横に結合します。ここでは、配列1と配列2の繰り返しインデックスを横に結合しています。

クロス結合の完成実例と結果

具体的な完成例を使って、式の動作を確認してみましょう。例えば、次のようなデータがあるとします。

配列1　(A1:A4)

配列2　(B1:B6)

このデータに対して、以下の計算式を適用します：

=LET(_ArrayX, A1:A4, _ArrayY, B1:B6, HSTACK(TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)), TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE)))

結果は以下のようになります。

クロス結合式：使用手順の例

=LET(_ArrayX, 配列1, _ArrayY, 配列2, HSTACK(TOCOL(IFERROR(EXPAND(_ArrayX, COUNTA(_ArrayX), COUNTA(_ArrayY)), _ArrayX)), TOCOL(IFERROR(EXPAND(_ArrayY, COUNTA(_ArrayY), COUNTA(_ArrayX)), _ArrayY), , TRUE)))

上記の基本式を出力先にコピペします。続けて「関数の挿入」(fx)で「配列1」と「配列2」を登録します。

「名前値1：配列1」と「名前値2：配列2」に各配列を登録してOKします。

配列を登録すれば完成します。