Excelでユニークな値を抽出する5つの簡単な方法【重複削除・関数活用】

Excelでユニークな値(重複しない一意の値)を取得したいとお考えなら、この記事が参考になります。Excelでは、1つの列だけでなく、複数の列や行にまたがって重複した値が発生することがあります。正確なデータ分析やデータ統合を行うためには、重複を取り除くことが必要になるケースが多くあります。一方で、重複を削除するのではなく、繰り返し現れる値を確認したいだけの場合もあります。本記事では、Excelでユニークな値を検出したり、重複を除去したりするための代表的な5つの方法を詳しく解説します。

Excelでユニークな値を取得する5つの方法

高速液体クロマトグラフィー(HPLC)は、分析化学で広く利用されている有名な手法で、混合物中の化合物の分離・同定・定量が可能です。このチュートリアルでは、複数のリサーチアシスタントがそれぞれ異なるHPLCカラム(さまざまな化合物を検出できる)を使用して実験を行うワークシートを題材にします。各列の重複と一意性を、いくつかの異なるプロセスで分析していきましょう。

下図が元データです。200行×5列のデータセットですが、スペースの都合上、一部の行のみ表示しています。

それでは、シンプルな手順でユニークな値を取得できる5つの方法をご紹介します。

方法1:フィルター オプションの詳細設定を使う

「フィルター オプションの詳細設定」(Advanced Filter)を使うと、条件に基づいてデータを抽出でき、ユニークな値も簡単に取得できます。以下の手順に従ってください。

手順:

• まず、「Using Advanced Filter」という名前の空白のワークシートを開きます。
• 次に、データ > 並べ替えとフィルター > 詳細設定 を選択します。

• すると、「フィルター オプションの詳細設定」ダイアログボックスが表示されます。
• 「指定した範囲」を選択します。
• 「コピー先」には、同じシート上の $B$4 を指定し、「重複するレコードは無視する」にチェックを入れます。
• 「リスト範囲」には、Datasetシートの $B$4:$B$204(大学・研究機関名の列)を選択します。
• 最後に「OK」をクリックします。

• これで、大学・研究機関名の一意の値が「Using Advanced Filter」シートに抽出されます。

• 同様の手順を、元データセットの他のすべての列に対しても実行できます。
• 下図のように「フィルター オプションの詳細設定」ウィンドウを編集すれば、HPLCカラムの一意の値も取得できます。

最終的な出力結果は次のようになります。

方法2:「重複の削除」機能を使う

データタブの「重複の削除」機能を使って、重複した値を削除することもできます。ただし注意点として、この機能はデータを完全に削除してしまうため、必ず元データのコピーを作成してから作業してください。

手順:

• まず、元データのシートをコピーします。ソースデータは常にそのまま残しておくのがベストプラクティスです。コピーしたシートの名前を「Removing Duplicates to Get Unique Values」などに変更しましょう。
• 次に、対象の範囲を選択し、データ > データ ツール > 重複の削除 をクリックします。

• すると、「重複の削除」ダイアログボックスが表示されます。
• 重要なポイントとして、「先頭行をデータの見出しとして使用する」にチェックが入っていることを確認してください。その後、重複を削除したい列を選択するか、すべての列にチェックを入れたままにします。
• 特定の1つの列だけ重複を削除するよう選択した場合、結果は上記の「フィルター オプションの詳細設定」による一意の値の抽出例と同じになります。
• たとえば、下図のように「大学・研究機関名」列を選択します。

• すると、ダイアログボックスがポップアップ表示され、「大学・研究機関名」列に基づいて171件の重複値が削除され、6件の一意の値が残ったことが示されます。

• これにより「フィルター オプションの詳細設定」の結果が裏付けられ、さらにこの特定の列内の重複値の数も把握できます。
• 今度は、下図のようにすべての列にチェックを入れて重複を削除してみましょう。

• その結果、Excelから「重複値3件、一意の値197件」という回答が返されます。これは、データセット内の3行において、「大学・研究機関名」「HPLCカラム」「HPLCカラムが検出可能な化合物(サプライヤー)」「HPLCカラムが実際に検出に使用されている化合物(特定ラボ)」「リサーチアシスタント」の各列の値がすべて一致していたことを意味します。
• さらに、データを文脈の中で見て、「リサーチアシスタント」列を除いた場合にどれだけの値が重複しているのかを確認してみます。

• その結果、下図のように22件の重複値が検出・削除され、178件の一意の値が残ったことがわかります。

• 最後に「OK」をクリックすれば、ユニークな値が取得できます。

方法3:Excel関数を使う

さまざまな数式を使ってユニークな値を見つけることもできます。EXACT関数やUNIQUE関数は、一意の値を探すのに非常に便利です。

3-1. EXACT関数と条件付き書式を組み合わせる

EXACT関数を条件付き書式と組み合わせて使うことで、列内のどの位置に該当する値が出現しているのかを視覚的に確認できます。

手順:

• まず、下図のように「HPLCカラムが実際に検出に使用されている化合物(特定ラボ)」列を選択します。
• 次に、ホーム > スタイル > 条件付き書式 を選択し、ドロップダウンメニューから「新しいルール」を選びます。

• 続いて、「次の数式を満たす場合に値を書式設定:」のテキストボックスに、次の数式を入力します。

=EXACT("C8 compounds",$E5)

重要なのは、「OK」をクリックする前に書式を設定しておくことです。

• そこで、次に書式を選択します。
• オプションから、濃い青の塗りつぶしと白の太字テキストを選択します。

• 完了したら「OK」をクリックし、書式のプレビューを確認します。

最後に「OK」をクリックすると、次のような出力が得られます。

3-2. UNIQUE関数を活用する

UNIQUE関数を使っても、ユニークな値を取得できます。UNIQUE関数は、引数で指定された範囲の中から一意の値だけを抽出してくれる関数です。

ここでは、「リサーチアシスタント」列の一意の値を取得する必要があると仮定します。

• まず、セル H5 に次の数式を入力します。

=UNIQUE(F5:F204)

ここで、F5:F204 は「リサーチアシスタント」列のセル範囲です。

• 次に、Enterキーを押せば、絞り込まれたリサーチアシスタントの一覧が表示されます。

方法4:テーブル機能を活用する

Excelのテーブル機能を使って、重複の検出や除去を行うこともできます。

手順:

• まず、元データを含むシートのコピーを作成します。
• 次に、データセット内のセルを選択した状態で、キーボードの Ctrl + T を押すか、挿入 > テーブル > テーブル を選択します。

• テーブルのフィルター機能を使うと、特定の値だけを分離できます。「大学・研究機関名」列の横にあるドロップダウン矢印をクリックすれば、特定の大学を選択できます。「University ABC」だけにチェックを入れれば、データセット内のUniversity ABCに関するすべてのレコードが分離されます。

• すると、大学名の一意の値を含む次のような出力が得られます。

• さらに、複数の列を組み合わせてテーブルをフィルターすることも可能です。たとえば、「University ABC」のレコードのうち、リサーチアシスタントのJennifer Smithがまとめたものだけを表示したい場合は、まず上記の例と同様に「University ABC」にチェックを入れ、次に「リサーチアシスタント」列のドロップダウン矢印をクリックして「Jennifer Smith」を選択します。

• これにより、一意のリサーチアシスタントの情報を確認できます。

方法5:テーブルとスライサーを組み合わせる

スライサーを使うと、テーブルを視覚的に素早くフィルターできます。以下の手順で、スライサーを使ってユニークな値を取得しましょう。

手順:

• スライサーを挿入するには、まずテーブル内のセルを選択し、テーブル デザインタブ > ツール > スライサーの挿入 をクリックします。

• 次に、「スライサーの挿入」ダイアログボックスで、下図のように「HPLCカラムが検出可能な化合物(サプライヤー)」列と「HPLCカラムが実際に検出に使用されている化合物(特定ラボ)」列にチェックを入れます。

• これで、下図のようなスライサーが挿入されました。

• 続いて、「HPLCカラムが検出可能な化合物(サプライヤー)」のスライサーで「C8 compounds」を選択すると、テーブルが即座にフィルターされ、この列でC8化合物が記載されている行だけが表示されます。
• さらに、下図のように「HPLCカラムが実際に検出に使用されている化合物(特定ラボ)」のスライサーでも「C8 compounds」を選択します。値がどこで繰り返されているかを確認することで、サプライヤーが指定した仕様に実際に従っているラボがどれなのかを把握できます。

Excelで複数列からユニークな値を取得する方法

UNIQUE関数を使えば、Excelでユニークな値を非常に簡単に取得できます。UNIQUE関数は、複数の列にまたがる一意の値も検出可能です。

• たとえば、B列とC列から一意の値を取得したい場合、まずセル E5 に次の数式を入力します。

=UNIQUE(B5:C204,FALSE,TRUE)

ここで、B5:C204 は「大学・研究機関名」と「HPLCカラム」のセル範囲を指しています。

• 次に、Enterキーを押します。

すると、次のような出力が得られます。

Excelでユニークな値の個数を数える方法

COUNTIF関数を使えば、列や行からユニークな値の個数を数えることができます。COUNTIF関数は一意の値そのものを表示するのではなく、その個数をカウントして表示します。

• たとえば、C列の一意の値の数を知りたい場合、まずセル G5 に次の数式を入力します。

=COUNTIF(C5:C204)

• 同様に、Enterキーを押すと、出力結果として 14 が表示されます。

まとめ

Excelには、繰り返し現れる値を検出したり、重複した値を削除したりするための多彩なツールが用意されています。将来のデータベース統合に向けてデータセットをクリーンアップする際(冗長性や重複の除去が必要な場合)には、複数のツールを組み合わせて使うことも珍しくありません。また、常に重複を削除したいわけではなく、単にカウントしたり記録したりしたいだけという場面もあります。本記事が、Excelでユニークな値を見つけるためのお役に立てば幸いです。添付ファイルへのコメントや、皆さんの重複削除・繰り返し値検出のコツ、さらにお使いのラボでのHPLC分析の活用事例なども、ぜひお気軽にお聞かせください。

関連する便利な記事

• Excelで2つの列に基づいて重複行を削除する方法【4つの方法】
• VLOOKUPを使ってExcelで重複値を見つける方法