【エクセル】重複データを確認する4つの方法。COUNTIF関数やピボットテーブルでも可能

エクセルで重複データを最速で抽出と削除する方法

Excel(エクセル)で表にうっかり重複データを作ってしまった経験は誰もがあると思います。

そんな時、表をフィルターで並び替えして目視で重複データを確認すると時間がかかってしまいます。

今回は以下の4つの重複データの確認&抽出方法を紹介します。

  • 「重複の削除」機能で、重複の削除まで行う
  • 「重複する値」機能で、重複のチェック(確認)だけ行う
  • 「COUNTIF関数」で、重複データを抽出する
  • ピボットテーブルで、重複データを確認する

今回は、重複データが混ざった下の表を使って手順を説明します。品目列、産地列、数量列それぞれに重複データが混ざっています。

重複データが混ざっているExcelの表
重複データが混ざっているExcelの表

重複データを確認し削除する方法

まずは表の重複データをチェックし削除する方法を紹介します。

この方法を使うと瞬時に重複データが削除されます。そのため、重複データの確認だけ行いたい場合は、残りの3つの方法を利用しましょう。

手順1.「重複の削除」をクリック

表内の任意のセルを選択してから、「データ」タブの「重複の削除」をクリックします。

「データ」タブの「重複の削除」を選択
「データ」タブの「重複の削除」を選択

手順2.「重複」をチェックする列を選択

「重複の削除」画面が表示されます。

表の列項目名が表示されているので、削除したい重複列にチェック(✓)を入れて、「OK」ボタンをクリックします。

重複を確認する列を選択
重複を確認する列を選択

今回は品目列の重複を削除するために、「品名」に✓を入れます。

また、表の一番上の行を見出し行として取り扱わない場合は、「先頭行をデータの見出しとして使用する」のチェックを外してください。

手順3.重複行の削除結果を確認

表内に重複行が存在していた場合、「重複する〇〇個の値が見つかり、削除されました。一意の値が、○○個残っています。」と画面に表示され、表から重複行が削除されます。

重複行が削除された結果が表示
重複行が削除された結果が表示

この際、削除されるのは下側の行です。一番上の行は、デフォルトの正しい行と見なされて削除はされません。

図解.削除される重複行の説明
図解.削除される重複行の説明

今回の例では、「バナナ」と「リンゴ」が品名で重複しているので、下から3行目と最下行のの重複行が削除されました。

重複の削除の誤りを防ぐために。

重複の削除を実行した後に、想定外の重複行が削除されることもあります。

そんな場合に備えて、作業前に表をコピペして『元データ』を残しておきましょう。

もしくは、ワークシートをコピーして、作業前のシートを残しておきましょう。

操作方法が分からない方は、以下の記事を参考にしてみてください。

元データを残しておかないのであれば、重複した削除を行った直後に、「Ctrl+Z」のショートカットキーを使って元に戻しましょう。

重複の削除に問題がなければ、「Ctrl+Y」のやり直しのショートカットキーを使って再度元に戻すことが出来ます。

重複削除前と削除後のデータを切り替える
重複削除前と削除後のデータを切り替える

2項目にチェックを入れる際の注意点

「重複の削除」画面で重複する項目列を2つ以上設定する場合は、取り扱いに注意が必要です。

例えば、各列に重複データが存在している表を使って、「品名」と「産地」にチェックを入れて重複の削除を行ってみます。

2項目にチェックを入れて重複の削除を実施
2項目にチェックを入れて重複の削除を実施

そうすると、各列毎に重複データが存在しますが、「重複する値は見つかりませんでした。」と表示されます。

重複が見つからないパターン
重複が見つからないパターン

これは、「品名」と「産地」がともに同じ値の行が2行以上存在しないため、重複とみなされないということです。

そのため、各列ごとに重複データを削除する場合は、1項目ずつチェックして削除を繰り返ししましょう。

「重複の削除」画面で2項目に✓を入れた場合は、それぞれの項目が同じ値でないと重複と見なされません。

重複データのチェック(抽出)のみ行う方法

次に紹介するのは、重複データを確認して抽出する方法です。

一つ前の見出しで紹介した「重複の削除」は、ボタンを押すだけで重複行が削除されてしまいますので、重複のチェックを行ってから削除することをお薦めします。

手順1.「条件付き書式」の「重複する値」を選択

まず重複データを抽出したい表を選択します。

「ホーム」タブの「条件付き書式」から「セルの強調表示ルール」を選び、「重複する値」をクリックします。

「ホーム」タブの「重複する値」をクリック
「ホーム」タブの「重複する値」をクリック

手順2.「重複する値」画面で「書式」を設定

「重複する値」画面が表示されるので、条件を設定します。

左側のプルダウン項目は「重複」を選択し、右側の「書式」はプルダウンリストから任意の項目を選択して「OK」をクリックします。

「書式」を変えることで、重複セルにどの色を付けるか、文字色を変えるかなどを選択することが出来ます。

「重複する値」画面で条件を設定
「重複する値」画面で条件を設定

手順3.「重複する値」の抽出結果を確認

列ごとに「重複する値」が抽出されますので、視覚的に簡単に確認することが出来ます。

列ごとの重複項目が確認出来る
列ごとの重複項目が確認出来る

上の表では、「品目」列は「バナナ」と「リンゴ」、「産地」列は「日本」、「数量」列は「35」が重複していることが確認することが出来ました。

手順4.「条件付き書式」設定を解除

重複のチェックを行ったことで、表には「条件付き書式」というセルの書式が設定されました。

そのため、このセル色は「セルの塗りつぶし」では変更(削除)出来ません

重複を確認した後に自由に表の色を変更したい場合は、「条件付き書式」を解除する必要があります。

表の任意のセルをクリックしてから、「ホーム」タブの「条件付き書式」から「ルールのクリア」⇒「シート全体からルールをクリア」を選択します。

条件付き書式を解除する方法
条件付き書式を解除する方法

そうすると、条件付き書式を解除することが出来ます。備忘のために重複データを確認した表を残したいのであれば、「図」で貼り付けておきましょう。

条件付き書式を解除すると色も解除
条件付き書式を解除すると色も解除

作業直後であれば、「Ctrl」キー+「Z」キーで元に戻せば簡単に解除出来るよ!

COUNTIF関数で重複データの確認

条件に合う数を数えるCOUNTIF関数(カウントイフ)を使えば、データ量が膨大でも簡単に重複データを見つけ出すことができます。

この方法は、表に重複チェック用の1列を追加して、重複している項目に”2”以上の数値が表示され、重複していない項目には”1”が表示される方法です。

慣れれば2分程度で確認出来る方法ですのでぜひ習得してみてください!

1項目(1列)の重複を確認

まずは、表の1項目(1列)の重複を確認する方法を解説します。

手順1.重複を確認するための列を追加

表に重複を確認するための列を追加します。追加する列の位置はどこでもOKです。

重複チェック用の列を追加
重複チェック用の列を追加

手順2.COUNTIF関数を挿入

追加した列の一番上のセルに、「=COUNTIF(」を挿入します。

重複チェックのためにCOUNTIF関数を挿入
重複チェックのためにCOUNTIF関数を挿入

手順3.COUNTIF関数の引数を設定

COUNTIF関数は1つ目の引数「範囲」と2つ目の引数「検索条件」を設定する必要があります。

以下のように、COUNTIF関数を挿入してください。

=COUNTIF($C$3:$C$13,C3)

COUNTIF関数を完成させる
COUNTIF関数を完成させる

1つ目の引数「範囲」は、重複チェックする列全体($C$3:$C$13)を選択します。下のセルにもCOUNTIF関数を利用するので、絶対参照の$を付けてください。

2つ目の引数「検索条件」は重複チェックする同じ行のセル(C3)を選択します。

この設定により、「範囲」の中で重複しているセルは、”2””3””4”など”1”以外の数値が表示されます。

手順4.COUNTIF関数をコピペ

重複チェックするすべてのセルにCOUNTIF関数を挿入するため、COUNTIF関数を挿入したセルをコピーして、下のセルに貼り付けします。

COUNTIF関数をコピペ
COUNTIF関数をコピペ

手順5.重複した値を確認

COUNTIF関数を挿入したセルに”2”以上の数値が表示されたセルが重複した値ということです。

COUNTIF関数を使った重複データの確認
COUNTIF関数を使った重複データの確認

COUNTIF関数の詳しい使い方は以下の記事で詳しく紹介しています。

2項目(2列)の重複を確認

2項目の値が同じ重複を確認する方法を紹介します。

下の表には「品名」が「イチゴ」で、かつ「産地」が「日本」の行は2行存在しますが、このようなデータを抽出する方法です。

2項目の重複を確認する
2項目の重複を確認する

手順1.重複チェック用のキー列を作成
重複チェック用に表に1列追加し、重複を確認する文字列を&演算子を使って結合します。

=C3&D3

重複チェック用のキー列を設定
重複チェック用のキー列を設定

手順2.重複チェック用のキー列を完成
&演算子を挿入したセルを下のセルにコピペして、重複キー列を完成させます。

重複キー列をコピペして完成させる
重複キー列をコピペして完成させる

手順3.重複チェック用にCOUNTIF関数を挿入
重複チェック用にさらに1列追加し、重複を確認するためCOUNTIF関数を挿入します。

=COUNTIF($C$3:$C$13,C3)

COUNTIF関数を挿入
COUNTIF関数を挿入

この手順以降は上の見出しで説明した手順とまったく同じです。COUNTIF関数の結果が”1”の行は重複しておらず、”1”以外の行は重複しています。

手順3.重複データを確認
COUNTIF関数を挿入したセルに”2”以上の数値が表示されたセルが重複した値ということです。以上で、重複チェックは完了です。

2項目の重複データの抽出
2項目の重複データの抽出

ピボットテーブルを利用した重複データの確認方法

ピボットテーブルは集計以外にも、データの個数や重複データを抽出するのに役立ちます。

例えば、下の地域別の販売高がまとめられた表に、同じ地域が混ざっていないか確認したい時は、「地域」列のみをデータ範囲として指定してピボットテーブルを挿入します。

ピボットテーブルで重複を確認する方法
ピボットテーブルで重複を確認する方法

「テーブルまたは範囲からのピボットテーブル」画面が表示されるので、ピボットテーブルのセル範囲を確認して「OK」を押します。

ピボットテーブルの範囲を確認
ピボットテーブルの範囲を確認

画面右側のピボットテーブルのフィールドの「行」エリアと「値」エリアに、「地域」項目をドラッグしてください。

ピボットテーブルで重複データを確認
ピボットテーブルで重複データを確認

そうすると、自動でデータの個数が集計されるようになり、下の図のように「大阪」が2行存在していることが分かりました。

このように簡易的に重複データをチェックする際にピボットテーブルは役立ちます。

ピボットテーブルの詳しい使い方は、【【基本~実践~応用編】ピボットテーブルの使い方!作成から分析まで丁寧に解説!】の記事で紹介していますので、参考にしてみて下さい。

まとめ

今回は重複データを確認する4つの方法を紹介しました。重複データを目視で確認するとどうしても漏れが発生してしまいます。今回紹介した方法のうち、使いやすい方法を試してみて、実務で利用してみて下さい。