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

クロス結合・交差結合により組み合わせリストを生成する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つのリストの全組み合わせパターンを生成する計算式。コピペで使用可能。

式の構造と効果

この計算式は、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)

1月
2月
3月
4月
5月
6月

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

=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)))

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

東京1月
東京2月
東京3月
東京4月
東京5月
東京6月
千葉1月
千葉2月
千葉3月
千葉4月
千葉5月
千葉6月
神奈川1月
神奈川2月
神奈川3月
神奈川4月
神奈川5月
神奈川6月
埼玉1月
埼玉2月
埼玉3月
埼玉4月
埼玉5月
埼玉6月

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

    =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します。

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

    コメント