Google Spreadsheetでクロス結合（交差結合）をする数式とその中身の解読

やりたいこと

Googleスプレッドシートに下記のようなデータがあるとします。

歌詞A
鬼のパンツは
5年はいても
10年はいても

歌詞B
いいパンツ
やぶれない

下記のように全ての組み合わせを作成したいと思います。
（クロス結合とか交差結合と呼ばれるようです）

歌詞A	歌詞B
鬼のパンツは	いいパンツ
鬼のパンツは	やぶれない
5年はいても	いいパンツ
5年はいても	やぶれない
10年はいても	いいパンツ
10年はいても	やぶれない

結論

先人の知恵により、解決方法は検索すると見つけることができます。

• Google - スプレッドシートで複数シートにわたって組み合わせを作成し、値を計算したい(88178)｜teratail

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

• Join two columns in Google Sheets - Web Applications Stack Exchange

=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の行数回繰り返す
    • 繰り返したものを結合する
      • 結合したものを縦に並べる
• 後半
  • 歌詞Bを結合する
    • 結合したものを歌詞Aの行数回繰り返す
      • 繰り返したものを縦に並べる
• 前半と後半を合体させる
• ArrayFormulaで列に展開

数式の改善

データの内容が固定値の場合は良いのですが、歌詞Aまたは歌詞Bに追加があった場合に対応できません。
このため、データーシートにデータ数を取得する数式(例えば=counta(A2:A))を追加して、そこで行数を取得した方が応用が効くと思います。

参考ページ

• Google - スプレッドシートで複数シートにわたって組み合わせを作成し、値を計算したい(88178)｜teratail

• Join two columns in Google Sheets - Web Applications Stack Exchange