みなさんこんにちは、truestarのtokuです。
前回 Excelを爆速で動かすおすすめショートカット3選 を紹介しました！
最近Excelの案件がちょうど固まったので、色々ショートカットや関数を調べていたのですが、
最新の関数が便利になりすぎていたので、例をもとにいくつか紹介したいなと思います。
Excelで2つのリストの全ての組み合わせを作成してみよう！
リストの全組み合わせ（配列×配列）を作成したいことってあると思います。
イメージは以下のような感じです。
今まではこういうリストを作成しようとすると、「マクロ」や「パワークエリ(Power Query)」など駆使しないと難しく、
ハードルが高い作業かつ、Excelの弱い点でもありました。
しかし、実は最新の関数を組み合わせることで、関数だけで上記のような作業を実現できるようになりました。
使う関数の紹介
今回使用する関数を列挙しておきます。細かな使い方は使うパートで説明します。
①LET関数
LET関数は関数に名前を付けることができる関数です。
LET(名前1,処理1,名前2,処理2,…,実行したい処理名称)のように式を書きます。
つけた名前を同じ関数内で使用できるのが大きな特徴です。
＜使用例＞
「xを1」,「yを2」として「足し算という名前でx+y」という処理に名前を付けて、最後足し算を出力する
②TRANSPOSE関数
行列を入れ替える関数です。入れ替えたい範囲を選択することで行列を入れ替えることが可能です。
③CONCAT関数
文字を結合する関数です。従来の「CONCATENATE関数」は範囲で値を選択することができませんでしたが、
CONCAT関数は範囲で選択が可能です。
④TEXTSPLIT関数
区切り文字を指定して、値を行や列に分割する関数です。
=TEXTSPLIT( Text,Col_delimiter,[Row_delimiter],[Ignore_empty],[Match_mode],[Pad_With]）
・Col_delimiter：
・Row_delimiter：縦
・Ignore_empty：空白処理のこと
・Match_mode：一致モードのこと 1・Pad_With：
ちょっと難しいですが、使用イメージは以下です。
「A,1;A,2;A,3;B,1;B,2;B,3;C,1;C,2;C,3;」という文字があった時、
「,」で列分割「;」で行分割を指定しています。
⑤スピルについて
スピルとは1つの数式を入力することで、そこを基点にして複数のセルに数式を自動入力してくれる機能です。
例えば④で紹介したTEXTSPLIT関数は左上に式を1つ入れると、全て分割されたものが返ってきます。
スピルを使用していると以下のように青枠で浮き出たような見え方になり、右上以外のセルの数式はグレーアウトしているのが特徴です。
細かい説明はまた別の機会にしたいと思いますが、今回はこのスピル機能も使っていきます。
いざ実践！
それではさっそく実践編に入っていきます。
が、その前に今回組み合わせたい2つのリストをテーブル化しておきます。
しなくてもいいのですが、その方が理解しやすい＆値が増えても対応できるので今回はテーブルを作成します。
テーブル名は自由でいいので以下のようなリストをテーブル化しておきます。
大まかな流れ
本来は直接LET関数1つの中に全ての処理を格納できるのですが、
一旦分割して説明し、最後にLET関数に格納する形で行こうと思います。
1.一旦すべての組み合わせをクロス集計表に起こす
2.それを全て1つの値に結合する
3.TEXTSPLIT関数で分割する
4.LET関数に格納して完成
1.一旦すべての組み合わせをクロス集計表に起こす
言葉では説明が難しいので一旦図で示したいと思います。
以下のようなクロス集計表を作成できると、全ての組み合わせを書き出すことが可能です。
G列は列Aそのまま「=テーブル名[列A]」ですが、H列～Q列は行列入れ替えが必要です。
そこで使用するのがTRANSPOSE関数です。「=TRANSPOSE(テーブル名[列B])」とすることで行列変換が可能です。
そしてA1が入力されているセルに、「=G2#&H1#」と入力すると全ての組み合わせを表示することが可能です。
これは先ほどのスピルという機能によって自動で他のセルにも数式が入力されます。
では「#」は何を表しているかというと、A～E、1～10どちらもスピルで表示されています。
Aや1が記載されているセルに上記で示した数式を入れるだけで、他のセルにも自動入力されるはずです。
このスピルで表示されている範囲全てを指定したいとき、「#」をつけることで範囲選択扱いになります。
ここでは
・G2#：A~Eまで
・H1#：1～10まで
を範囲選択しているのと同じことです。
さらにここで一工夫しておきます。
勘のいい人だと気づいているかもしれませんが、TEXTSPLIT関数の紹介時に示したように、
最終的には行列に分割をします。そのため、区切り文字を先に結合してあげるとこの後の処理が楽になります。
今回は「,」で列分割し、「;」で改行（行分割）するように区切り文字を指定します。
先ほどの「=G2#&H1#」を書き換えて「=G2#&","&H1#&";"」とします。
下記の表と一致すればOKです！
2.全て1つの値に結合する
さて続いてはTEXTSPLIT関数でまとめて処理するために、全ての値を一つの値に結合しましょう。
使う関数はCONCAT関数です。「=CONCAT(H2#)」と入力すると、以下のように全て結合されると思います。
5×10の範囲なのにこれも「H2#」で行けるの！？と思うかもしれませんが、
一つのスピルで記載されている場合は、そのスピルの範囲全てを選択することになります。
逆にこの場合、「Aの行だけ#を使って選びたい」とかになると工夫が必要です。
3.TEXTSPLIT関数で分割する
ここからはもう簡単です。
TEXTSPLIT関数で分割しますが、区切り文字を指定します。
「=TEXTSPLIT(結合したセル,",",";",TRUE)」と入力すると、長すぎて入りきりませんが全ての組み合わせが表示されたはずです。
最後のTRUEは空白のセルがもし存在した場合に省略すると0が表示されてしまうので、TRUEと入れておくことを勧めます。
4.LET関数に格納する
やっと最後ですね！ここまでお疲れ様でした。
今までのやり方だとクロス集計表作って、結合したものを作って…と関数を別々の個所にいくつも入力する必要があります。
これを1つの関数で済ませてしまうのがLET関数です。
①LET関数内に2つのリストを格納する
まずは2つのリストをLET関数の中に格納します。
列Aを「リストA」、列Bを「リストB」という名前で格納しましょう。
ただし、列BはTRANSPOSE関数で行列入れ替えていたことに注意してください。
記載方法は以下です。
=LET(
リストA,テーブル名[列A],
リストB,TRANSPOSE(テーブル名[列B])
ただし、まだ式の記載途中なのでかっこは閉じません。
②クロス集計表を作成した式を格納する
先ほどの式にクロス集計表を作成した式を追加します。
LET関数内では名称を付けたものはその名称で関数内で利用可能なので以下のように記載可能です。
今回は「クロス集計表」という名前を付けています。
=LET(
リストA,テーブル名[列A],
リストB,TRANSPOSE(テーブル名[列B]),
クロス集計表,リストA&","&リストB&";",
③1つの値に結合する処理を追加
先ほどCONCAT関数で行った処理を追加します。
結合処理なので「結合」と名前を付けておくことにしましょう。
=LET(
リストA,テーブル名[列A],
リストB,TRANSPOSE(テーブル名[列B]),
クロス集計表,リストA&","&リストB&";",
結合,CONCAT(クロス集計表),
④TEXTSPLIT関数で分割する処理を格納
ここまで来たらもうお分かりかと思いますが、TEXTSPLIT関数での処理も格納します。
「組み合わせリスト」という名前を付けておきましょう。
=LET(
リストA,テーブル名[列A],
リストB,TRANSPOSE(テーブル名[列B]),
クロス集計表,リストA&","&リストB&";",
結合,CONCAT(クロス集計表),
組み合わせリスト,TEXTSPLIT(結合,",",";",TRUE),
組み合わせリスト)
最後に組み合わせリストをもう一度記載しているのは、
最終的にどの処理をアウトプットをしたいかを示す必要があるので、
最終的に組み合わせリストを出力してくださいと指定しています。
そうすると、1つのセルに入力するだけで今までの処理をすべて行った結果を出力することが可能です。
【応用】リストに重複があったら？
今回のリストは重複なく非常に綺麗ですが、実務ではそもそもリストを顧客番号のユニークから持ってきたいなどの
ケースが多いのではないかと思います。
その場合は、UNIQUE関数を使用することで同様の処理が可能です。
テーブル化してリストAなどを作成する代わりに、
=Unique(ユニーク取りたい列)
とすることで対応可能です。またUNIQUE関数もスピル扱いとなり、Uniqueにしたもの全ての値を関数内に保持しているので、
そのままリストとして使うことができます。イメージだけ下記に記載しておきます。
2列に分割せず1列に羅列する場合
1列に全組み合わせを表示するだけならもっと簡単なやり方があります。
使うのはTOCOL関数です。
なんとこのTOCOL関数、配列をすべて1列に並べてくれます。
つまり何ができるかというと、上の例でいうクロス集計表まで作成します。
ただし、普通にリスト同士を＆でつなぎます。
すると、以下のような結果が返ってきます。
このスピルで表示された表をすべて1列に並べたらすべての組み合わせになりますね！
前回は、これをTextsplitで分割しましたがここでTOCOLを使用します。
そうすると…
載せきれませんがちゃんと表示されました。
区切り記号など気にしなくてもいいし、UNIQUEとの組み合わせも使えるので非常に便利ですね。
最後に
ちょっと長くなりすぎましたが、意外とExcelにも便利な関数が増えてちょっとした作業なら
マクロなどなくても簡単に作れるようになりました。
しっておくだけで地味に役立つ関数が結構あるのでぜひ色々勉強してみてください！
またtruestarではデータ活用に関する様々な業務を承っております。是非こちらからご相談ください。
