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

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

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

| 歌詞B       |  
|:-----------------|
| いいパンツ            | 
| やぶれない           | 

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

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

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

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

>`=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](https://webapps.stackexchange.com/questions/74023/join-two-columns-in-google-sheets)

>`=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関数](https://support.google.com/docs/answer/3093275)を一旦除外して考えると、この数式は二つの数式が結合されていることに気づきます。

>`{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関数](https://support.google.com/docs/answer/3094134)で、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関数](https://support.google.com/docs/answer/3094077)を使ってタブ(`char(9)`)で結合しています。

| イメージ|  
|:-----------------|
| 鬼のパンツ[TAB]**[TAB]**鬼のパンツ[TAB]| 

###その外側の数式(SPLIT)

>`split(join(char(9), rept(B2:B5&char(9),rows(B2:B5)))`

せっかくJOINで結合した内容を、[SPLIT関数](https://support.google.com/docs/answer/3094136)を使って、タブ`char(9)`で再度分割してしまいます。

| イメージ||
|:-----------------|:--|
| 鬼のパンツ|鬼のパンツ|

上記のJOIN→SPLITの行為は無駄に思えますが、`ArrayFormula`を利用するときに効果を発揮します。（後述）

###その外側の数式(TRANSPOSE)

>`transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))))`

[TRANSPOSE関数](https://support.google.com/docs/answer/3094262)を使って、行列を入れ替えます。

| イメージ|
|:-----------------|
| 鬼のパンツ|
| 鬼のパンツ|

###ArrayFormulaの帰還

後半の数式に行く前に、謎のJOIN→SPLITの意味を確認するために、[ArrayFormula関数](https://support.google.com/docs/answer/3093275)を当ててみます。

>`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](https://teratail.com/questions/88178)

- [Join two columns in Google Sheets - Web Applications Stack Exchange](https://webapps.stackexchange.com/questions/74023/join-two-columns-in-google-sheets)