概要
データ分析やシステム開発の現場において、「直積（デカルト積、クロスジョイン）」は非常に重要な概念です。これは、複数の異なる集合（リストや列）の要素を組み合わせて、考えられるすべての組み合わせパターンを生成する操作を指します。例えば、商品カテゴリ、色、サイズという3つの要素があった場合、それぞれの可能な値のすべての組み合わせ（例：Tシャツ-赤-S、Tシャツ-赤-M、Tシャツ-青-S、…）を網羅的に生成する際に用いられます。
通常、このような直積の生成はデータベースのSQLにおける`CROSS JOIN`や、プログラミング言語でのネストしたループ処理、あるいはExcel VBAマクロを用いて行われることが多いでしょう。しかし、本記事では、最新のExcel 365に搭載された強力な動的配列関数を活用し、VBAを一切使わずに、ネイティブなExcel関数だけで複数列の直積を生成する「極意」をご紹介します。これにより、マクロの知識がないユーザーでも、手軽に複雑な組み合わせデータを生成し、データ分析やマスターデータ作成、テストケース生成などの業務を効率化することが可能になります。
詳細解説
複数列の直積をExcel関数で実現する核心は、各リスト（列）の要素を適切に繰り返すインデックスを生成することにあります。この繰り返しパターンを数学的に表現し、Excel関数に落とし込むことで、動的な直積生成が可能になります。
基本的な考え方は、以下のようになります。
例えば、リストA (n1個の要素) とリストB (n2個の要素) の直積を考えます。生成される直積の行数は `n1 * n2` となります。
* リストAの要素は、リストBの要素数 `n2` の回数だけ繰り返される必要があります。
* リストBの要素は、`1, 2, …, n2, 1, 2, …, n2, …` のように繰り返される必要があります。
これを一般化して、`m`個のリスト `L1, L2, …, Lm` があり、それぞれの要素数が `n1, n2, …, nm` であるとします。
生成される直積の総行数は `N = n1 * n2 * … * nm` となります。
各リストの要素に対応するインデックス `idx_k` (k番目のリストのインデックス) を生成するには、`SEQUENCE`関数と算術演算子（`INT`、`MOD`）を組み合わせます。
`SEQUENCE(N)` は、1からNまでの連番の動的配列を生成します。これをベースに、各リストのインデックスを計算します。
例として、3つのリスト `L1(n1), L2(n2), L3(n3)` の直積を考えます。
1. **リストL1のインデックス `idx1` の生成:**
`idx1` は、`n2 * n3` 回ごとに次の要素に移る必要があります。
`INT((SEQUENCE(N)-1) / (n2 * n3)) + 1`
`(SEQUENCE(N)-1)` で0からN-1の連番にし、`n2 * n3` で割って整数部を取ることで、`0,0,…,0, 1,1,…,1, …` のパターンを生成し、最後に`+1`で1ベースのインデックスに戻します。
2. **リストL2のインデックス `idx2` の生成:**
`idx2` は、`n3` 回ごとに次の要素に移り、かつ `n2` でサイクルを繰り返す必要があります。
`MOD(INT((SEQUENCE(N)-1) / n3), n2) + 1`
まず、`L1`と同じ要領で `INT((SEQUENCE(N)-1) / n3)` で `0,0,…,0, 1,1,…,1, …` のパターン（ただし、`n3`回で切り替わる）を生成します。
次に、これを `n2` で割った余り `MOD(…, n2)` を取ることで、`0,1,…,n2-1, 0,1,…,n2-1, …` のサイクルパターンを生成し、最後に`+1`で1ベースのインデックスに戻します。
3. **リストL3のインデックス `idx3` の生成:**
`idx3` は、`1` 回ごとに次の要素に移り、かつ `n3` でサイクルを繰り返す必要があります。
`MOD(SEQUENCE(N)-1, n3) + 1`
`(SEQUENCE(N)-1)` を直接 `n3` で割った余りを取ることで、`0,1,…,n3-1, 0,1,…,n3-1, …` のサイクルパターンを生成し、最後に`+1`で1ベースのインデックスに戻します。
これらのインデックスが生成できたら、あとは `INDEX` 関数を使って元のリストから対応する値を取り出し、`HSTACK` 関数（水平にスタック）でそれらの列を結合すれば、直積が完成します。`LET` 関数を用いることで、中間変数を定義して数式を読みやすく、管理しやすくすることが可能です。
**動的配列関数の前提:**
この手法は、Excel 365で利用可能な以下の動的配列関数に大きく依存しています。
* **`SEQUENCE`:** 指定された行数、列数、開始値、ステップで数値のシーケンスを生成します。
* **`LET`:** 数式内で名前付きの計算結果を定義し、その名前を使用して数式の一部を簡略化できます。
* **`HSTACK`:** 複数の配列を水平方向に結合します。
* **`ROWS`:** 指定した範囲の行数を返します。
* **`INDEX`:** 指定した範囲または配列から、指定した行と列の交点にある値を返します。
これらの関数が利用できないExcelバージョン（Excel 2019以前など）では、この直接的な関数アプローチは利用できません。その場合はVBAやPower Queryなどの代替手段を検討する必要があります。
サンプルコード
ここでは、具体的なExcel関数を用いた複数列の直積生成のサンプルを示します。
以下のリストがシート上に存在すると仮定します。
**リスト1 (A1:A3): カテゴリ**
| カテゴリ |
| :——- |
| 果物 |
| 野菜 |
| 飲料 |
**リスト2 (B1:B2): 色**
| 色 |
| :— |
| 赤 |
| 青 |
**リスト3 (C1:C2): サイズ**
| サイズ |
| :——— |
| S |
| M |
2列の直積（カテゴリと色）を生成する例
以下の数式を、例えばE1セルに入力してください。
=LET(
list1_range, A1:A3, ' 1つ目のリスト範囲を定義
list2_range, B1:B2, ' 2つ目のリスト範囲を定義
n1, ROWS(list1_range), ' 1つ目のリストの要素数を取得
n2, ROWS(list2_range), ' 2つ目のリストの要素数を取得
total_rows, n1 * n2, ' 生成される直積の総行数を計算
seq, SEQUENCE(total_rows), ' 1から総行数までの連番を生成
' リスト1のインデックスを計算 (例: 1,1,2,2,3,3)
idx1, INT((seq - 1) / n2) + 1,
' リスト2のインデックスを計算 (例: 1,2,1,2,1,2)
idx2, MOD(seq - 1, n2) + 1,
' 生成されたインデックスを使って元のリストから値を取り出し、水平に結合
HSTACK(
INDEX(list1_range, idx1),
INDEX(list2_range, idx2)
)
)
**出力結果の例 (E1セルからスピル):**
| カテゴリ | 色 |
| :——- | :— |
| 果物 | 赤 |
| 果物 | 青 |
| 野菜 | 赤 |
| 野菜 | 青 |
| 飲料 | 赤 |
| 飲料 | 青 |
3列の直積（カテゴリ、色、サイズ）を生成する例
以下の数式を、例えばG1セルに入力してください。
=LET(
list1_range, A1:A3, ' 1つ目のリスト範囲 (カテゴリ)
list2_range, B1:B2, ' 2つ目のリスト範囲 (色)
list3_range, C1:C2, ' 3つ目のリスト範囲 (サイズ)
n1, ROWS(list1_range), ' リスト1の要素数 (3)
n2, ROWS(list2_range), ' リスト2の要素数 (2)
n3, ROWS(list3_range), ' リスト3の要素数 (2)
total_rows, n1 * n2 * n3, ' 生成される直積の総行数 (3*2*2 = 12)
seq, SEQUENCE(total_rows), ' 1から総行数までの連番を生成
' リスト1 (カテゴリ) のインデックスを計算 (例: 1,1,1,1,2,2,2,2,3,3,3,3)
idx1, INT((seq - 1) / (n2 * n3)) +
