Excelで複数の列のデータの全組み合わせパターンを生成する方法（スピル）

下図のようなことがやりたかったのですが、ググってもぱっとやり方が出てこなかったのでシェアします。

結論 =TOCOL(<一列目の値の範囲>&"-"&TRANSPOSE(<二列目の値の範囲>))

結論としてはこれです。2019年から導入された「スピル」という機能を使うと簡単にできました。

=TOCOL(<一列目の値の範囲>&"-"&TRANSPOSE(<二列目の値の範囲>))
↓ 具体例
=TOCOL(B2:B4&"-"&TRANSPOSE(C2:C4))

解説

スピルとは

まず、スピルについて説明しておきます。Excelの数式は、基本的に数式を指定したセルの値を計算するためのものでした。しかし、スピルを使うことで、数式を指定したセルだけでなく、その周囲のセルの値もまとめて計算することができます。

スピルの使い方としては、通常の数式で1つのセルを参照する部分で、セルの範囲を参照するようにします。すると、一つのセルしか埋めていなくても、範囲指定した各セルに対して数式を計算してくれます。

上述の例では、縦方向の範囲指定をしましたが、横方向の範囲指定をするとも可能です。さらに、縦方向と横方向のスピルを組み合わせることで、表を一発で作ることができます。

結果の表を一列（一行）に集約する

結果の表を一列（一行）に集約したい場合は、TOCOL（TOROW）関数をかませます。

縦方向の範囲×縦方向の範囲計算する

上述の例では、縦方向の範囲×横方向の範囲で計算しましたが、どちらも縦方向にしたいことがあると思います。そのような場合、次のように単純に書くと、範囲の方向が同じなので、組み合わせではなく、対応する行同士が乗算されてしまいます。

この場合は、一方の縦方向の範囲にTRANSPOSE関数をかませることで、縦方向の範囲を横方向の範囲に変換することができます。

これらを組み合わせると、複数の列のデータの全組み合わせパターンを生成する次の数式が得られます。

=TOCOL(<一列目の値の範囲>&TRANSPOSE(<二列目の値の範囲>))
↓ 具体例
=TOCOL(A2:A5*TRANSPOSE(B2:B4))

3列の場合

3列の場合は、まずはいずれかの2列の組み合わせを生成した上で、上述のTOCOLを使って1列にします。後は、残った2列に対してもう一度同じことをします。

=TOCOL(TOCOL(<一列目の値の範囲>&"-"&TRANSPOSE(<二列目の値の範囲>))&"-"&TRANSPOSE(<三列目の値の範囲>))
↓ 具体例
=TOCOL(TOCOL(A2:A4&"-"&TRANSPOSE(B2:B4))&"-"&TRANSPOSE(C2:C4))

個人的スピル数式作成のコツ

色々遊んでみて、作成のコツをつかんだのでシェアします。

題材

次のように、問題文のテンプレを使って、渡した数字の組み合わせの数だけ問題文を生成する数式を作ってみます。

1. 1つの組み合わせについて作ってみる

まずは、スピルを使わず、1つの組み合わせについて作ってみます。今回は、テンプレにある<A>をC3セルで、<B>をD3セルで置き換える数式を作りました。

=SUBSTITUTE(SUBSTITUTE(B9,"<A>",C3),"<B>",D3)

2. 一つの列だけを拡張する

次に、スピルを使って一つの列だけを拡張します。C3の部分をC3:C6に置き換えました。すると、縦方向に拡張されます。

=SUBSTITUTE(SUBSTITUTE(B9,"<A>",C3:C6),"<B>",D3)

3. 二つの列を拡張する

拡張する列を増やします。D3の部分をTRANSPOSE(D3:D6)に置き換えました。このとき、TRANSPOSEをかまさないと、AもBも下方向に拡張しようとしてうまく動きませんので、BはTRANSPOSEを使って右方向になるようにします。

後は、縦に並べたければTOCOL()をかませるだけです。

以上です。