やりたいこと
Googleスプレッドシートに下記のようなデータがあるとします。
| 歌詞A |
|---|
| 鬼のパンツは |
| 5年はいても |
| 10年はいても |
| 歌詞B |
|---|
| いいパンツ |
| やぶれない |
下記のように全ての組み合わせを作成したいと思います。
（クロス結合とか交差結合と呼ばれるようです）
| 歌詞A | 歌詞B |
|---|---|
| 鬼のパンツは | いいパンツ |
| 鬼のパンツは | やぶれない |
| 5年はいても | いいパンツ |
| 5年はいても | やぶれない |
| 10年はいても | いいパンツ |
| 10年はいても | やぶれない |
結論
先人の知恵により、解決方法は検索すると見つけることができます。
=arrayformula({
transpose(
split(
join(
char(9),
rept('シートβ'!A2:A4&char(9),rows('シートβ'!A2:A4))
), char(9)
)
),
transpose(
split(
rept(
join(
char(9),
'シートα'!A2:A4
)&char(9),
rows('シートβ'!A2:A4)
),char(9)
)
)
})
=ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))})
しかしながら、内容があまりに複雑に思えるので、以降で中身を解きほぐしてみます。
数式の分解
後者の式を例にとります。
=ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))})
ArrayFormulaを一旦除外
ARRAYFORMULA関数を一旦除外して考えると、この数式は二つの数式が結合されていることに気づきます。
{transpose(…), transpose(…)}
ですので、前半と後半に分けて中身を見てみましょう。
前半
transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9)))
内側にいる式から順に見ていきます。
一番内側の数式(REPT)
rept(B2:B5&char(9),rows(B2:B5))
B2セルにあるデータをタブを結合（&char(9)）して、REPT関数で、B2:B5の行数回（rows(B2:B5)）繰り返しています。
※正しくはA2:A5の行数回（rows(A2:A5)）繰り返すべきだと思います。元ページの例ではA列とB列の行数が同じなのでたまたま成立しています。
※B2:B5&char(9)の:B5はArrayFormulaの記述ですので、一旦無視してください。
| イメージ |
|---|
| 鬼のパンツ[TAB]鬼のパンツ[TAB] |
その外側の数式(JOIN)
join(char(9), rept(B2:B5&char(9),rows(B2:B5))
繰り返した内容を、JOIN関数を使ってタブ(char(9))で結合しています。
| イメージ |
|---|
| 鬼のパンツ[TAB][TAB]鬼のパンツ[TAB] |
その外側の数式(SPLIT)
split(join(char(9), rept(B2:B5&char(9),rows(B2:B5)))
せっかくJOINで結合した内容を、SPLIT関数を使って、タブchar(9)で再度分割してしまいます。
| イメージ | |
|---|---|
| 鬼のパンツ | 鬼のパンツ |
上記のJOIN→SPLITの行為は無駄に思えますが、ArrayFormulaを利用するときに効果を発揮します。（後述）
その外側の数式(TRANSPOSE)
transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))))
TRANSPOSE関数を使って、行列を入れ替えます。
| イメージ |
|---|
| 鬼のパンツ |
| 鬼のパンツ |
ArrayFormulaの帰還
後半の数式に行く前に、謎のJOIN→SPLITの意味を確認するために、ArrayFormula関数を当ててみます。
ArrayFormula(transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5)))))
| イメージ |
|---|
| 鬼のパンツ |
| 鬼のパンツ |
| 5年はいても |
| 5年はいても |
| 10年はいても |
| 10年はいても |
仮にここで、JOIN→SPLITを行わなかった(JOINせずにREPTから直接SPLITした)場合の結果を見てみましょう。
ArrayFormula(transpose(split(rept(B2:B5&char(9),rows(B2:B5))))
| イメージ | ||
|---|---|---|
| 鬼のパンツ | 5年はいても | 10年はいても |
| 鬼のパンツ | 5年はいても | 10年はいても |
一列ではなく、三列に分かれてしまいます。
ここの動きについては私も正確に理解できていませんが、ArrayFormulaによりtransposeが繰り返し処理されたようです。
いったんjoinを挟むことで、ArrayFormulaのスコープがtransposeまで及ぶことを防いでいるように見えます。
後半
transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))
後半も基本的な内容は同じですので、詳細は割愛します。
ただし、前半がreptしてからjoinしているのに対し、後半の式ではjoinしてからreptしています。
この理由は、後半の式でも前半同様の順番で数式を組んだ時の結果を見るとわかりやすいです。
結果は下記のようになります。
| 歌詞A | 歌詞B |
|---|---|
| 鬼のパンツ | いいパンツ |
| 鬼のパンツ | いいパンツ |
| 5年はいても | いいパンツ |
| 5年はいても | やぶれない |
| 10年はいても | やぶれない |
| 10年はいても | やぶれない |
このため、先にJOINで「いいパンツ」と「やぶれない」の組合せを作ってから、それを必要回数繰り返すの必要があります。
数式のまとめ
- 前半
- 歌詞Aの値を歌詞Bの行数回繰り返す
- 繰り返したものを結合する
- 結合したものを縦に並べる
- 繰り返したものを結合する
- 歌詞Aの値を歌詞Bの行数回繰り返す
- 後半
- 歌詞Bを結合する
- 結合したものを歌詞Aの行数回繰り返す
- 繰り返したものを縦に並べる
- 結合したものを歌詞Aの行数回繰り返す
- 歌詞Bを結合する
- 前半と後半を合体させる
-
ArrayFormulaで列に展開
数式の改善
データの内容が固定値の場合は良いのですが、歌詞Aまたは歌詞Bに追加があった場合に対応できません。
このため、データーシートにデータ数を取得する数式(例えば=counta(A2:A))を追加して、そこで行数を取得した方が応用が効くと思います。