Microsoft Excel（エクセル）で、表にうっかり重複データを作ってしまった経験は誰もがあると思います。
そんな時、表をフィルターで並び替えして目視で重複データを確認すると時間がかかってしまいます。
今回は、以下の１つの表の中に混在している重複データの確認と抽出方法を４つ紹介します。
- 「重複の削除」機能で、重複の削除まで行う
- 「重複する値」機能で、重複のチェック(確認)だけ行う
- 「COUNTIF関数」で、重複データを抽出する
- ピボットテーブルで、重複データを確認する
説明用の事例として、重複データが混ざった下の表を使って手順を説明します。品目列、産地列、数量列それぞれに重複データが混ざっています。
また、この記事の最後には、２つの表の中に重複データを確認する方法を紹介します。VLOOKUP関数を利用する方法で、簡単にそれぞれの項目の違いを確認することができます。
- VLOOKUP関数で、２つの表を比較し、列項目の重複を確認する
「重複の削除」機能で、重複データを確認し削除する方法
まずは表の重複データをチェックし削除する方法を紹介します。
「重複の削除」機能を使うと瞬時に重複データが削除されます。そのため、重複データの確認だけしたい場合は、残りの３つの方法を利用しましょう。
ちなみに、重複は「ちょうふく」と読みます。「じゅうふく」と読み間違いしなよう気をつけましょう。
手順１．「重複の削除」をクリック
表内の任意のセルを選択してから、「データ」タブの「重複の削除」をクリックします。
手順２．「重複」をチェックする列を選択
「重複の削除」画面が表示されます。
表の列項目名が表示されているので、削除したい重複列にチェック(✓）を入れて、「OK」ボタンをクリックします。
今回は品目列の重複を削除するために、「品名」に✓を入れます。
また、表の一番上の行を見出し行として取り扱わない場合は、「先頭行をデータの見出しとして使用する」のチェックを外してください。
手順３．重複行の削除結果を確認
表内に重複行が存在していた場合、「重複する〇〇個の値が見つかり、削除されました。一意の値が、○○個残っています。」と画面に表示され、表から重複行が削除されます。
この際、削除されるのは下側の行です。一番上の行は、デフォルトの正しい行と見なされて削除はされません。
今回の例では、「バナナ」と「リンゴ」が品名で重複しているので、下から３行目と最下行のの重複行が削除されました。
重複の削除誤りを防ぐために元データを残すこと！
機能を理解していないと、想定外の重複行が削除してしまうこともあります。
そんな場合に備えて、作業前に表をコピペして『元データ』のバックアップを残しておくため、ワークシートをコピーして、作業前のシートを残しておきましょう。
ワークシートをコピーする操作が分からない方は、以下の記事を参考にしてみてください。
元データを残しておかないのであれば、重複した削除を行った直後に、「Ctrl＋Z」のショートカットキーを使って元に戻してください。
重複の削除に問題がなければ、「Ctrl＋Y」のやり直しのショートカットキーを使って再度元に戻すことが出来ます。
２項目にチェックを入れる際の注意点
「重複の削除」画面で重複する項目列を２つ以上設定する場合は、取り扱いに注意が必要です。
例えば、各列に重複データが存在している表を使って、「品名」と「産地」にチェックを入れて重複の削除を行ってみます。
そうすると、各列毎に重複データが存在しますが、「重複する値は見つかりませんでした。」と表示されます。
これは、「品名」と「産地」がともに同じ値の行が２行以上存在しないため、重複とみなされないということです。
そのため、各列ごとに重複データを削除する場合は、１項目ずつチェックして削除を繰り返ししましょう。
使い方のポイント
「重複の削除」画面で２項目にチェックを入れた場合は、それぞれの項目が同じ値でないと重複と見なされない。
「重複する値」機能で、重複データのチェック(抽出)のみ行う方法
次に紹介するのは、重複データを確認して抽出する方法です。
一つ前の見出しで紹介した「重複の削除」は、ボタンを押すだけで重複行が削除されました。
一方、この方法は重複データをチェックしてセルに自動で色付けする方法です。そのため、まず重複があるかどうかを確認したい方にお勧めの機能です。
手順１．「条件付き書式」の「重複する値」を選択
まず重複データを抽出したい表を選択します。
「ホーム」タブの「条件付き書式」から「セルの強調表示ルール」を選び、「重複する値」をクリックします。
手順２．「重複する値」画面で「書式」を設定
「重複する値」画面が表示されるので、条件を設定します。
左側のプルダウン項目は「重複」を選択し、右側の「書式」はプルダウンリストから任意の項目を選択して「ＯＫ」をクリックします。
「書式」を変えることで、重複セルにどの色を付けるか、文字色を変えるかなどを選択することが出来ます。
手順３．「重複する値」の抽出結果を確認
列ごとに「重複する値」が抽出されますので、視覚的に簡単に確認することが出来ます。
上の表では、「品目」列は「バナナ」と「リンゴ」、「産地」列は「日本」、「数量」列は「35」が重複していることを確認することが出来ました。
手順４．「条件付き書式」設定を解除
重複のチェックを行ったことで、表には「条件付き書式」というセルの書式が設定されました。
そのため、このセル色は「セルの塗りつぶし」では変更(削除）出来ません。
重複を確認した後に自由に表の色を変更したい場合は、「条件付き書式」を解除する必要があります。
表の任意のセルをクリックしてから、「ホーム」タブの「条件付き書式」から「ルールのクリア」⇒「シート全体からルールをクリア」を選択します。
そうすると、条件付き書式を解除することが出来ます。解除される前の表を残したいのであれば、解除前に右クリックメニューの「図の貼り付け」を利用して表を残しておきましょう。
「図の貼り付け」方法が分からない方は、以下の記事で詳しく紹介していますので参考にしてください。
作業直後であれば、「Ctrl」キー＋「Z」キーで元に戻せば簡単に解除出来るよ！
フィルターで重複したデータだけを表示する方法
条件付き書式によって色付けされた重複データは、フィルター機能を使うことで重複データのみを表示することができます。
表にフィルターを設定し、色フィルターを使って薄い赤色だけを表示させます。データが膨大で重複データがどのセルか目視で確認しづらい場合は、フィルターを利用すると便利です。
色フィルターの使い方が分からない方は、以下の記事で詳しく紹介しているので参考にしてください。
COUNTIF関数で重複データを確認する方法
条件に合う数を数えるCOUNTIF関数(カウントイフ）を使えば、データ量が膨大でも簡単に重複データと重複数を把握することができます。
この方法は、表に重複チェック用の１列を追加して、重複している項目には”２”以上の数値が表示されて、重複していない項目には”１”が表示される方法です。
慣れれば２分程度で確認出来る方法ですのでぜひ習得してみてください。
１項目(１列）のみの重複を確認する方法
まずは、表の１項目(１列）のみの重複を確認する方法を解説します。
手順１．重複を確認するための列を追加
表に重複を確認するための列を追加します。追加する列の位置はどこでもOKです。
手順２．COUNTIF関数を挿入
追加した列の一番上のセルに、「＝COUNTIF(」を挿入します。
手順３．COUNTIF関数の引数を設定
COUNTIF関数は１つ目の引数「範囲」と２つ目の引数「検索条件」を設定する必要があります。
以下のように、COUNTIF関数を挿入してください。「範囲」は重複を確認したいセル範囲($C$3:$C$13)を、「検索条件」は隣のセル(C3)を指定します。「範囲」には絶対参照「$」を必ずつけてください。
=COUNTIF($C$3:$C$13,C3)
この設定により、「範囲」の中で重複しているセルの場合は、”２””３””４”などが表示され、重複している数が表示されます。
手順４．COUNTIF関数をコピペ
重複チェックするすべてのセルにCOUNTIF関数を挿入するため、COUNTIF関数を挿入したセルをコピーして、下のセルに貼り付けします。
手順５．重複している対象行を確認
COUNTIF関数の結果で”２”以上の数値が表示される場合は、重複しているデータが存在しているということです。
COUNTIF関数の詳しい使い方は以下の記事で詳しく紹介しています。
２項目(２列）の重複を確認する方法
続いて、２項目の値が同じ重複を確認する方法を紹介します。
下の表には「品名」が「イチゴ」で、かつ「産地」が「日本」の行は２行存在しますが、このようなデータを抽出する方法です。
手順１．重複チェック用のキー列を作成
重複チェック用に表に１列追加し、重複を確認する文字列を&演算子を使って結合します。
=C3&D3
手順２．重複チェック用のキー列を完成
&演算子を挿入したセルを下のセルにコピペして、重複キー列を完成させます。
手順３．重複チェック用にCOUNTIF関数を挿入
重複チェック用にさらに１列追加し、重複を確認するためCOUNTIF関数を挿入します。
=COUNTIF($C$3:$C$13,C3)
この手順以降は上の見出しで説明した手順とまったく同じです。COUNTIF関数の結果が”１”の行は重複しておらず、”１”以外の行は重複しています。
手順３．重複データを確認
COUNTIF関数を挿入したセルに”２”以上の数値が表示されたセルが重複した値ということです。以上で、重複チェックは完了です。
ピボットテーブルを利用した重複データの確認方法
ピボットテーブル機能は集計以外にも、データの個数や重複データを抽出するのに役立ちます。
例えば、地域別の販売高がまとめられた下の表に、同じ地域が混ざっていないか確認したい時は、「地域」列のみをデータ範囲として指定してピボットテーブルを挿入します。
「テーブルまたは範囲からのピボットテーブル」画面が表示されるので、ピボットテーブルのセル範囲を確認して「OK」を押します。
画面右側のピボットテーブルのフィールドの「行」エリアと「値」エリアに、「地域」項目をドラッグしてください。
そうすると、自動でデータの個数が集計されるようになり、下の図のように「大阪」が２行存在していることが分かりました。
このように簡易的に重複データをチェックする際にピボットテーブルは役立ちます。
ピボットテーブルの詳しい使い方は、【【基本～実践～応用編】ピボットテーブルの使い方！作成から分析まで丁寧に解説！】の記事で紹介していますので、参考にしてみて下さい。
２つの表を比較し、重複データを確認する方法
VLOOKUP関数を利用することで、２つの表を比較して、重複している項目と、重複してない項目をチェックをすることができます。
下のB列の表とE列の表に、重複があるかどうかを確認する事例を紹介します。
行数が多い方のE列の真横にVLOOKUP関数を挿入します。B列の範囲(B3;B12)の中で重複する場合は検索値を表示し、重複しない場合は「#N/A」を表示させます。
=VLOOKUP(E3,$B$3:$B$12,1,FALSE)
下の行もVLOOKUP関数をコピーします。
そうすると、B列の項目が重複している場合はVLOOKUP関数で該当する値が返されますが、重複していない項目は「#N/A」が表示されます。そのため、下の表の場合は、「柿」がB列に存在しておらず、それ以外は重複していることが分かります。
同様の方法で、２つの行項目を比較し、重複の有無を確認する際は、HLOOKUP関数を利用します。
VLOOKUP関数の使い方を確認したい方は、以下の記事を参考にしてください。
重複データの目視確認はやめよう！
今回は重複データを確認する４つの方法を紹介しました。重複データを目視で確認するとどうしても漏れが発生してしまいます。今回紹介した方法のうち、使いやすい方法を試してみて、実務で利用してみて下さい。