いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)クロス結合(直積)を作る

 Excelでクロス結合(CROSS JOIN、直積)を作成する方法です。
 TOCOL関数(行列を縦1列に並べ替える関数)とTEXTBEFORE/TEXTAFTER関数を使って実現しています。TEXTSPLITが使えれば便利なのですがそれだとうまくいかないためちょっと面倒な式になっています。

  • TOCOL関数等を利用してクロス結合(直積)を作ることができます。

手順

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


 式は次のようになっています。

 E3セル

=LET(
x,TOCOL(B3:B5&"_"&TRANSPOSE(C3:C6)),
HSTACK(TEXTBEFORE(x,"_"),TEXTAFTER(x,"_"))
)

 まず「B3:B5&"_"&TRANSPOSE(C3:C6)」の部分により、県名と品目を「_」でつないだ文字列を全通り(12個)生成しています(下記参照)。
 そしてこれをTOCOL関数で縦1列に並べたものをLET関数によりxと名付けています。
 さらにTEXTBEFORE関数とTEXTAFTER関数を使い、xの各項目を「_」の前後に分割した列をそれぞれ生成し、HSTACK関数で横並びに表示させています。
 TOCOL関数の結果に対しTEXTSPLIT関数が直接使えれば話は早いのですが、TEXTSPLIT関数の第1引数に配列を与えた場合に最初の項目の列(この例では県名の列)しか返ってこないという仕様のためうまくいきません。


 なお、「B3:B5&"_"&TRANSPOSE(C3:C6)」の部分だけ実行すると次のようになります。
 これをTOCOL関数で縦1列に並べてさらに「_」の前後に分割し、2列に表示している、というわけです。