Excelで複数列からなる固有の組み合わせを抽出する計算式(UNIQUE関数、HSTACK関数)

固有の組み合わせを抽出するための概要

Excelでは、データセット内の固有の項目やその組み合わせを抽出することができます。例えば「重複の削除」や「UNIQUE関数」です。これらは、特に大きなデータセットから特定の情報をフィルタリングする際に役立ちます。

ただし「重複の削除」は動的機能を持っていないので、データの追加や削除にリアルタイム対応できません。また標準の「UNIQUE関数」はつながっている列にのみ対応しています。離れた列を組み合わせることはできません。

UNIQUE関数とHSTACK関数の組み合わせ

今回は、離れた複数の特定の列から固有の組み合わせをリアルタイム反映可能な状態で抽出するための計算式を詳しく解説します。「UNIQUE関数」と新関数「HSTACK関数」を組み合わせて使用することで対応します。※HSTACK関数は2023年現在、Excel 365でのみ使用できます。

UNIQUE関数は、指定された範囲または配列から重複する値を排除して固有の値を返します。一方、HSTACK関数は複数の範囲や配列を水平方向に結合します。これらの関数を組み合わせることで、異なる列の固有の組み合わせを抽出できます。

解説用のサンプルテーブルとして以下のもの(A1:E32)を使用します。存在する「商品分類」と「カラー」の組み合わせを出してみます。

完成計算式の論理

標準の計算式(拡張非対応)

計算式

=UNIQUE(HSTACK(B2:B32,E2:E32))

は、以下の手順で動作します：

1. HSTACK関数による結合：まず、HSTACK関数はB列（商品分類）とE列（カラー）の各行を横に結合します。これにより、商品分類とカラーがペアになった新しい配列が作成されます。

   

2. UNIQUE関数による固有の抽出：次に、UNIQUE関数は結合された配列を受け取り、重複する組み合わせを排除して、固有の組み合わせのみを返します。

   

この計算式の結果は、商品分類とカラーのすべての固有の組み合わせのリストとなります。必要に応じてSORT関数などで囲んで並べ替え表示させることもできます。

テーブルを使った計算式(拡張に対応)

分析元を「テーブル」として登録しておけばレコードの追加にも対応します。例えば前述の表に「テーブル甲」という名前を付けた場合の計算式は下記のようになります。HSTACK内部を変更しています。

=UNIQUE(HSTACK(テーブル甲[商品分類],テーブル甲[カラー]))

テーブルでの列指定ならリアルタイム拡張に対応しています。新しいレコードを追加すれば、集計表に自動反映されます。

まとめ

ExcelのUNIQUE関数とHSTACK関数を組み合わせることで、データセット内の異なる列から固有の組み合わせを効率的に抽出することができます。この方法は、データ分析やレポート作成において非常に役立つツールとなります。