Excelで一意の値(ユニークな値)を抽出する8つの方法|重複データのフィルタリングを徹底解説

大量のデータから重複を取り除き、一意の値(ユニークな値)だけを抽出することは、Excelでのデータ整理において非常に重要な作業です。Excelには、重複データの削除や一意の値の抽出に役立つさまざまな機能が用意されています。この記事では、サンプルデータを使いながら、一意の値を抽出する8つの方法をわかりやすく解説します。

ここでは、「注文日」「カテゴリ」「商品」の3列からなるシンプルなデータセットを例にします。このデータセット全体から、注文された商品の一意のリストを取得することを目標としましょう。

8つの方法でExcelの一意の値を抽出する

方法1:「重複の削除」機能を使って一意の値を抽出する

大規模なデータセットを扱う際、重複エントリを削除したいケースはよくあります。Excelの「データ」タブには「重複の削除」機能があり、これを使えばデータセットから重複行を簡単に取り除けます。今回は「カテゴリ」と「商品」の2列から重複を削除してみましょう。

手順1: 対象の範囲(「カテゴリ」と「商品」の列)を選択し、「データ」タブ → 「データツール」グループ内の「重複の削除」をクリックします。

手順2: 「重複の削除」ダイアログボックスが表示されます。

• すべての列にチェックを入れます。
• 「先頭行をデータの見出しとして使用する」にチェックを入れます。
• 「OK」をクリックします。

手順3: 確認メッセージが表示され、「重複した値が8件見つかり、削除されました。一意の値が7件残っています」と通知されます。「OK」をクリックして完了です。

以上の手順を実行すると、下の画像のように重複が削除された結果が得られます。

方法2:条件付き書式を使って一意の値を抽出する

次に紹介するのは「条件付き書式」を活用する方法です。Excelの条件付き書式では、さまざまな条件に基づいてセルの書式を設定できます。ここでは数式を使って、範囲内(「商品」列)のセルを条件付きで書式設定します。アプローチは2つあります。一つは一意の値を色付けして目立たせる方法、もう一つは重複値を見えなくする方法です。

2-1. 条件付き書式で一意の値を色付けして抽出する

条件付き書式に数式を組み合わせることで、一意のエントリだけを強調表示できます。

手順1: 対象範囲(「商品1」列)を選択し、「ホーム」タブ → 「スタイル」グループの「条件付き書式」 → 「新しいルール」を選択します。

手順2: 「新しい書式ルール」ウィンドウが開きます。

• 「ルールの種類を選択してください」で「数式を使用して、書式設定するセルを決定」を選びます。
• 「ルールの内容を編集してください」に次の数式を入力します。

=COUNTIF($D$5:D5,D5)=1

この数式は、D列の各セルの出現回数が1回のみ(=一意である)場合にTRUEを返し、該当するセルに書式を適用します。

• 「書式」ボタンをクリックします。

手順3: 「セルの書式設定」ウィンドウが表示されます。

• 「フォント」タブで任意の色やスタイルを選択します。
• 「OK」をクリックします。

手順4: 「新しい書式ルール」ウィンドウに戻ると、プレビューで一意のエントリの見え方を確認できます。「OK」をクリックして確定しましょう。

最終的に、下の画像のように一意のエントリだけが希望どおりに色付けされます。

2-2. 条件付き書式で重複値を非表示にする

一意の値に手を加える代わりに、重複値だけを「隠す」ことも可能です。方法は2-1とほぼ同じですが、条件を「出現回数が1より大きい」に変更し、フォント色を白に設定することで、重複値を目立たなくします。

手順1: 方法2-1の手順1〜2を繰り返し、入力する数式を以下に変更します。

=COUNTIF($D$5:D5,D5)>1

この数式は、D列の各セルが出現回数2回以上(=重複している)場合にTRUEを返し、該当セルに書式を適用します。「書式」ボタンをクリックします。

手順2: 「セルの書式設定」ウィンドウで、フォント色を「白色」に選択し、「OK」をクリックします。

手順3: 「新しい書式ルール」ウィンドウに戻ります。フォント色を白にしたため、プレビューはほぼ真っ白に見えます。「OK」をクリックして確定します。

すべての手順を完了すると、下の画像のように重複値が見えなくなります。

※注意：フォント色は必ず白を選んでください。他の色では重複値を隠せません。

方法3:「フィルターオプション(詳細設定)」を使って一意の値を抽出する

前述の方法は元データからエントリを削除・変更するため、生データを変更できない状況では危険です。そんなときに便利なのが「フィルターオプション(詳細設定)」です。元データをそのまま残しながら、指定した場所に一意の値をコピーできます。

手順1: 対象範囲(「商品」列)を選択し、「データ」タブ → 「並べ替えとフィルター」グループの「詳細設定」をクリックします。

手順2: 「フィルターオプション」ダイアログボックスが表示されます。

• 「アクション」で「指定した範囲にコピー」を選択します。(「選択範囲内」でも構いませんが、元データを変更しないためにこちらを選びます)
• 「コピー先」に貼り付け先のセル(例：F4)を指定します。
• 「重複するレコードは無視する」にチェックを入れます。
• 「OK」をクリックします。

「OK」をクリックすると、指定した場所に一意の値が抽出されます。

方法4:UNIQUE関数を使って一意の値を抽出する

別の列に一意の値を表示したい場合は、UNIQUE関数が最も手軽です。UNIQUE関数は、範囲または配列から一意のエントリのリストを動的配列として返します。

=UNIQUE(配列, [比較の方向], [回数指定])

引数の説明：

• 配列： 一意の値を抽出する元となる範囲または配列(必須)
• [比較の方向]: 行方向で比較する場合はFALSE(既定)、列方向で比較する場合はTRUE(省略可)
• [回数指定]: 1回だけ出現する値を返す場合はTRUE、すべての一意の値を返す場合はFALSE(既定)(省略可)

手順1: 空白セル(例：E5)に次の数式を入力します。

=UNIQUE(D5:D19)

手順2: Enterキーを押すと、一瞬で一意のエントリがすべてスピル表示されます。

UNIQUE関数は一意の値をまとめて一度に出力してくれます。ただし、この関数はMicrosoft 365(Excel 365)以降でのみ使用可能なので注意してください。

方法5:UNIQUE関数とFILTER関数を組み合わせて条件付きで抽出する

方法4では単純に一意の値を抽出しましたが、特定の条件を満たす一意の値だけを取得したい場合はどうすればよいのでしょうか?例えば、特定のカテゴリに属する商品名だけを一意に抽出したいケースを考えてみます。

ここでは、「Bars(バー)」カテゴリ(セルE4)に含まれる一意の商品名を抽出します。

手順1: 任意のセル(例：E5)に次の数式を入力します。

=UNIQUE(FILTER(D5:D19,C5:C19=E4))

この数式は、C5:C19の範囲がセルE4と一致するという条件のもと、D5:D19の範囲をフィルタリングするよう指示しています。

手順2: Enterキーを押すと、「Bars」カテゴリの商品が下のスクリーンショットのように表示されます。

任意のカテゴリを指定して一意の商品を抽出できるため、大量の売上データを扱う際に非常に有効な手法です。なお、FILTER関数もExcel 365限定の関数です。

方法6:MATCH関数とINDEX関数を使った配列数式で抽出する

ここまでの説明をシンプルにするため、空白セルや大文字小文字の違いがないデータセットを想定してきました。しかし実際には、空白セルや大文字小文字が混在するデータも存在します。まず基本形として、空白のない範囲(「商品1」列)からMATCH関数とINDEX関数を組み合わせて一意の値を抽出する方法を見てみましょう。

6-1. 空白セルがない範囲から一意の値を抽出する

「商品1」の範囲には空白セルが存在しないものとします。

手順1: セルG5に次の数式を入力します。

=IFERROR(INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)),"")

数式の仕組み：

• COUNTIF($G$4:G4, $D$5:$D$19): 抽出済みの値($G$4:G4)が元の範囲($D$5:$D$19)内に何個あるかをカウントします。一致があれば1、なければ0を返します。
• MATCH(0, COUNTIF(...), 0): まだ抽出されていない値(カウントが0)の相対的な位置を返します。
• INDEX($D$5:$D$19, MATCH(...)): 条件を満たすセルの値を返します。
• IFERROR: エラー発生時に空白を表示し、#N/Aなどのエラー表示を防ぎます。

手順2: これは配列数式なので、Ctrl + Shift + Enterを同時に押します。「商品1」範囲の一意のエントリがすべて表示されます。

6-2. 空白セルを含む範囲から一意の値を抽出する

次に、「商品2」の範囲には複数の空白セルが存在します。空白セルを除外して一意の値を抽出するには、ISBLANK関数を組み込みます。

手順1: セルH5に次の数式を入力します。

=IFERROR(INDEX($E$5:$E$19, MATCH(0,IF(ISBLANK($E$5:$E$19),1,COUNTIF($H$4:H4, $E$5:$E$19)), 0)),"")

基本的な仕組みは6-1と同じですが、ISBLANK関数による論理判定を含むIF関数が追加されていることで、範囲内の空白セルを自動的に無視できるようになっています。

手順2: Ctrl + Shift + Enterを押すと、空白セルを無視して一意のエントリだけが抽出されます。

6-3. 大文字小文字を区別する範囲から一意の値を抽出する

データセットに大文字小文字が異なる類似エントリ(例：「Apple」と「apple」)が含まれる場合、FREQUENCY関数をTRANSPOSE関数やROW関数と組み合わせて使う必要があります。

手順1: セルI5に次の数式を入力します。

=INDEX($F$5:$F$19, MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), ""), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0))

数式の各パートの説明：

• TRANSPOSE($I$4:I4): 既に抽出済みの値を縦横変換して配列として扱えるようにします。
• EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)): 文字列同士が大文字小文字も含めて完全に一致するかを判定します。
• IF(EXACT(...), MATCH(ROW(...), ROW(...))): TRUEの場合、その文字列の相対位置を返します。
• FREQUENCY(...): 各文字列が配列内に出現する回数を計算します。
• MATCH(0, FREQUENCY(...), 0): 配列内で最初の未抽出値(0に一致する位置)を見つけます。
• INDEX($F$5:$F$19, ...): 見つかった位置に対応する一意の値を返します。

手順2: Ctrl + Shift + Enterを同時に押すと、大文字小文字を区別した一意の値がセルに表示されます。

すべての種類のエントリをそれぞれの列に抽出すると、データセット全体は下の画像のようになります。

商品データの型はご自身のニーズに合わせて自由に変更し、それに応じて数式を調整して活用してください。

方法7:VBAマクロを使って一意の値を抽出する

商品列から一意の値を取得したい場合、VBAマクロを使うこともできます。選択範囲の値を取得し、ループ処理によって重複を排除するコードを書けば、自動化された抽出が可能になります。

VBAマクロを適用する前に、対象のデータセットを用意し、一意の値を抽出したい範囲を選択しておきましょう。

手順1: マクロコードを書くために、Alt + F11を押して「Microsoft Visual Basic」ウィンドウを開きます。ツールバーの「挿入」タブ → 「標準モジュール」を選択します。

手順2: モジュールウィンドウが開いたら、以下のコードを貼り付けます。

Sub Unique_Values()
Dim Range As Variant, prdct As Variant
Dim mrf As Object
Dim i As Long
Set mrf = CreateObject("scripting.dictionary")
Range = Selection
For i = 1 To UBound(Range)
mrf(Range(i, 1) & "") = ""
Next
prdct = mrf.keys
Selection.ClearContents
Selection(1, 1).Resize(mrf.Count, 1) = Application.Transpose(prdct)
End Sub

コードの解説：

• 変数宣言後、CreateObject("scripting.dictionary")でDictionaryオブジェクトを作成し、mrfに代入します。Dictionaryは重複キーを自動的に排除する性質を持っています。
• 選択範囲をRangeに格納し、Forループで各セルの値をDictionaryに登録していきます。この過程で重複が自動的に除去されます。
• 最後に選択範囲をクリアし、一意の値を転記して表示します。

手順3: F5キーを押してマクロを実行し、ワークシートに戻ると、選択範囲から一意の値だけが表示されています。

方法8:ピボットテーブルを使って一意の値を抽出する

ピボットテーブルは、選択したセルから一意の項目リストを作成できる強力なツールです。Excelではピボットテーブルを簡単に挿入でき、目的の結果をすぐに得られます。

手順1: 対象範囲(「商品」列)を選択し、「挿入」タブ → 「テーブル」グループの「ピボットテーブル」をクリックします。

手順2: 「ピボットテーブル」ダイアログボックスが表示されます。

• 範囲(例：D4:D19)は自動的に選択されます。
• 「ピボットテーブルを配置する場所」で「既存のワークシート」を選択します。
• 「OK」をクリックします。

手順3: 「ピボットテーブルのフィールド」ペインが表示されます。ここには「商品」というフィールドが1つだけあります。

「商品」フィールドにチェックを入れると、下の画像のように一意の商品リストが表示されます。

まとめ

一意の値の抽出は、Excelでよく行われる操作の一つです。この記事では、重複の削除機能、条件付き書式、フィルターオプション、UNIQUE関数、FILTER関数、MATCH関数・INDEX関数、VBAマクロ、ピボットテーブルといった多彩なアプローチを紹介しました。

ポイントとして、関数を使う方法は元データを一切変更せず、別の場所に結果を表示できるのが大きなメリットです。一方、機能(重複の削除など)を使う方法は元データからエントリを永久に削除するため、実行前にデータのバックアップを取っておくことをおすすめします。用途に応じて最適な方法を選び、効率的にデータを整理しましょう。ご不明な点や追加情報があれば、ぜひコメントでお知らせください。

関連記事

• Excelでカスタムフィルターを使う方法(5つの方法)
• Excelで色でフィルタリングする方法(2つの例)
• Excelで数式が入ったセルをフィルタリングする方法(2つの方法)
• Excelのフィルターで複数項目を検索する方法(2つの方法)