UNIQUE関数で複数列の組み合わせからアイテムリストをスピル形式で出力する方法

※最新のExcelではより簡単な手法が使用できるようになりました。以下のページへお進みください。

複数列の組み合わせからアイテムリストをUNIQUE関数で出力する(最新・完全スピル対応)

Excelで複数列からなる固有の組み合わせを抽出する計算式(UNIQUE関数、HSTACK関数) 固有の組み合わせを抽出するための概要 Excelでは、データセット内の固有の項目やその組み合わせを抽出することができます。例えば「重複の削除」...

www.helpaso.net

2024.01.13

はじめに

Excelの最新機能を活用すると、複数列のデータから一意なアイテムリストを作成し、それをスピル形式で出力することが可能です(ただし2023年時点では完全なスピルではない)。この記事では、UNIQUE関数を主軸として商品分類とカラーの組み合わせを例に、その方法を詳しく解説します。以下のA1:E32にあるサンプルテーブルを用いて解説をします。

必要な関数とその役割

この処理には、主にUNIQUE関数、LET関数、およびTEXTSPLIT関数を使用します。

• UNIQUE関数: 重複しない一意な値のリストをスピル形式で作成します。
• SORT関数：配列を並べ替えてスピル形式で出力します。
• TEXTSPLIT関数: 文字列を指定した区切り文字で分割し、スピル形式で複数の列に展開します。

計算式の作成

以下の計算式は、商品分類とカラーの組み合わせから一意なアイテムリストを作成し、それを2列に分けて出力します。

以下の計算式は、「UNIQUE」関数を使用して、商品分類（B列）とカラー（E列）の組み合わせの一意なリストを作成します。組み合わせる際に区切り文字として “|” を使用して、後で簡単に分割できるようにします。

=UNIQUE(B2:B32&"|"&E2:E32)

好みによっては上記の式をSORT関数で囲んで並べて表示しても問題ありません。こうすると同じものが固まるため見やすくなります。

=SORT(UNIQUE(B2:B32&"|"&E2:E32))

次に、上記で出力した先頭部分を「TEXTSPLIT」関数を使用して、”|” で分割し、結果を2列に展開します。これにより、1列目に商品分類、2列目にカラーが表示されます。残念ながら縦の拡張には対応していないので、まず1つだけ出します。

「TEXTSPLIT」関数を使用して、”|” で分割し、結果を2列に展開します。これにより、1列目に商品分類、2列目にカラーが表示されます。

=TEXTSPLIT(G2,"|")

作成した式をオートフィルで拡張します。これらの操作でH列とI列にて商品分類とカラーの組み合わせアイテムリストが生成されました。

まとめ

この方法を使用することで、複数列のデータから複雑な組み合わせを効率的に処理し、スピル形式で出力することができます。Excelの最新機能を活用することで、データ分析の幅が大きく広がります。