🐳

スプレットシートで3つ以上のテーブルをcross joinする

2021/06/22に公開

Google Sheets

tech

目的

決まった数の値を持つフィールドが複数あり、それらの組み合わせの全パターンを作りたい^[1]
手作業でまとめようにも数が多くて時間がかかりそう

という状況に遭遇し、それをどうにかするための関数を調べました。
(2つのテーブルの組み合わせに言及している記事は見つかるが、3つ以上はあまりヒットしない)

アウトプットのイメージがわかない方は先に https://zenn.dev/kudo/articles/8f36f3dbfe116f#結果を見てみると良いかもです

例

以下のようなユーザ属性の全パターンを考えてみます。

性別
- 男性
- 女性
- 未回答
権限
- 管理者
- 一般
所属組織
- 経営
- 管理部
- 営業部
- 開発部

スプレットシートにするとこんな感じです。

列番号 \ 行番号	A	B	C
1	男性	管理者	経営
2	女性	一般	管理部
3	未回答		営業部
4			開発部

関数

関数が長くなるので、まずは計算用のフィールドを作成します

列ごとの項目数

まずはベースとなる列ごとの項目数を計算します。COUNTAで範囲指定すればOKです。

例えば列Aであれば COUNTA(A1:A4) です。
この関数を、スプレットシートの5行目に記載します

列番号 \ 行番号	A	B	C
1	男性	管理者	経営
2	女性	一般	管理部
3	未回答		営業部
4			開発部
5	=COUNTA(A1:A4)	=COUNTA(B1:B4)	=COUNTA(C1:C4)

対象列以前のパターン数

自身の列以降でありえるパターン数を算出します。PRODUCTを利用します。

やりたいことは処理の繰り返しの回数の算出なんですが、文字では分かりづらいので列ごとに説明します。
例えば列Aであれば、一番最初の列なのでパターン数は1です。
列Bであれば、以前の列=列Aなので、パターン数は列Aの3パターンです。PRODUCT(A5) になります。
列Cであれば、列Aの3パターンと列Bの2パターンの積をとって6パターンです。ここは PRODUCT(A5:B5) になります

列番号 \ 行番号	A	B	C
1	男性	管理者	経営
2	女性	一般	管理部
3	未回答		営業部
4			開発部
5	=COUNTA(A1:A4)	=COUNTA(B1:B4)	=COUNTA(C1:C4)
6	1	=PRODUCT(A5)	=PRODUCT(A5:B5)

対象列以降のパターン数

前項とは逆に、対象列以降についても算出します。

列番号 \ 行番号	A	B	C
1	男性	管理者	経営
2	女性	一般	管理部
3	未回答		営業部
4			開発部
5	=COUNTA(A1:A4)	=COUNTA(B1:B4)	=COUNTA(C1:C4)
6	1	=PRODUCT(A5)	=PRODUCT(A5:B5)
7	=PRODUCT(B5:C5)	=PRODUCT(C5)	1

列の繰り返しパターンを演算して展開する

A8セルに以下の関数を記載します。

=ArrayFormula({
transpose(split(rept(join(char(9),rept(A1:A3&char(9),A7)),A6),char(9))),
transpose(split(rept(join(char(9),rept(B1:B2&char(9),B7)),B6),char(9))),
transpose(split(rept(join(char(9),rept(C1:C4&char(9),C7)),C6),char(9)))
})

各列の各項目の繰り返し出力回数を計算し、ArrayFormulaを利用して他のセルにも展開しています。
利用している関数の説明は、以下の記事がわかりやすいです。

結果

3 * 2 * 4 = 24パターンが無事出力されました。

今回は3テーブルを例に紹介しましたが、 ArrayFormula内の要素を増やすことで4つ以上のテーブルでも可能です。

サンプルはこちら

脚注

具体的にはテストケース作成時の話 ↩︎

目的

例

関数

列ごとの項目数

対象列以前のパターン数

対象列以降のパターン数

列の繰り返しパターンを演算して展開する

結果

Discussion