いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Gスプレッドシート)クロス結合(直積)を作る

 Googleスプレッドシートでクロス結合(CROSS JOIN、直積)を作る方法についてです。
 関数で実現するのはなかなか面倒でしたが、2021年1月ごろに追加されたFLATTEN関数(行列を縦1列に並べ替える関数)を使って比較的容易に実現できるようになっています。

  • FLATTEN関数を使ってクロス結合(直積)を作ることができます。

手順

f:id:accs2014:20210203030513p:plain:right:w600

 B列には3つの県名、C列には4つの果物の名前が入力されています。
 そこでE3セルに次のような式を入力して、これらのクロス結合、つまりすべての組み合わせを表す12個の行を出力しています。

 E3セル

=ARRAYFORMULA(SPLIT(FLATTEN(B3:B5&"_"&TRANSPOSE(C3:C6)),"_"))

 まずは「B3:B5&"_"&TRANSPOSE(C3:C6)」の部分により、県名と果物の名前を「_」の記号でつないだ文字列を12個(3行×4列)生成します(下記画像を参照)。
 それをFLATTEN関数(行列を縦1列に並べ替える)を使って縦1列に並べ替え、さらにSPLIT関数を使って県名と果物名の2つの列に分割することで、結果的にクロス結合となります。
 

 なお、最初の段階である「B3:B5&"_"&TRANSPOSE(C3:C6)」だけ実行した結果は次のようになります。
 これをFLATTENで縦に並べてSPLITで横2列に分けている、と考えればわかりやすいと思います。

f:id:accs2014:20210203030517p:plain:w780