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