エクセルの重複データの抽出方法|別シートと一致の確認方法も紹介

エクセルの重複データの抽出方法

今回は、エクセルで重複データを抽出する方法についてご紹介します。

エクセルでは、作業中に同じデータを重複して入力してしまうことがあります。膨大なデータを扱う中で、目視にて重複データの有無を確認することは難しいでしょう。エクセルの機能を使い、重複データを自動的に見つける方が効率的です。

この記事では、エクセルの重複データ抽出方法から重複データの削除方法まで解説していきます。

エクセルの重複データを確認する方法【1列の場合】

まず、エクセル1列の範囲内に重複データがないかどうか確認する方法をご紹介します。

条件付き書式を使って重複データに色付けをする方法COUNTIF関数を使って重複データを発見する方法の2パターンを解説します。

  • 重複データに色を付けて確認する方法
  • COUNTIF関数で重複データを確認する方法

重複データに色を付けて確認する方法

条件付き書式を使うと、重複データが入力されたセルに色を付けて重複データを確認することができます。

STEP.1
重複データを確認したい列を選択します。画像では生徒名が入力されているA列を選択しています。

重複データに色を付けて確認する方法

STEP.2
「ホーム」タブ→「条件付き書式」→「セルの強調表示ルール」→「重複する値」の順にクリックしていきます。

重複データに色を付けて確認する方法

STEP.3
左のメニューが「重複」になっているのを確認します。画像青枠部分をクリックすると、重複セルの色について様々なオプションから選択することができます。画像では「濃い赤の文字、明るい赤の背景」を選択しています。最後に、「OK」を押します。

重複データに色を付けて確認する方法

STEP.4
重複しているセル(画像例では佐藤)の背景に色が付き、文字の色も赤に変わりました。

重複データに色を付けて確認する方法

COUNTIF関数で重複データを確認する方法

COUNTIF関数を使って、重複データがあるかどうかを確認することもできます。COUNTIF関数は、条件に合ったセルの個数を数える関数です。書式は以下のようになります。

=COUNTIF(範囲,検索条件)

STEP.1
表に重複判定の列を作り、ここにCOUNTIF関数を入力していきます。COUNTIFの「範囲」に重複判定したい範囲を、「検索条件」に重複判定したいセルを入力します。

例えば画像では、C2に以下のように入力しています。これで名前の入力されているA2~A8セルの中にA2セルと同じ内容のものが何個あるかを判定することができます。

=COUNTIF(A2:A8,A2)

COUNTIF関数で重複データを確認する方法

STEP.2
A2~A8まですべてのセルの重複判定をオートフィル機能で行いたいので、式を次のように書き換えます。

=COUNTIF($A$2:$A$8,A2)

$が先頭についたセル番地は固定され、オートフィル機能を使ってもずれません。セル右下のフィルハンドルにポインターを移動させ、黒い十字アイコンになったらA8までドラッグします。

COUNTIF関数で重複データを確認する方法

STEP.3
重複している「佐藤」の行に対応するセルのみ「2」と表示され、後は「1」と表示されました。

COUNTIF関数で重複データを確認する方法

なお、IF関数と組み合わせて以下のような数式にすれば、重複のある列のみに「!」印を付けることができます。

=IF(COUNTIF($A$2:$A$8,A2)>1,”!”,””)

COUNTIF関数で重複データを確認する方法

エクセルの重複データを確認する方法【2列以上の場合】

エクセルのデータが2列以上にまたがっており、その複数列すべてのデータが重複しているかどうか確認したいときにはデータの結合を使います。

画像の表は、社員名とテスト結果の一覧です。同じ社員名でも、テスト結果が違えば別のデータと考えます。この中から社員名、1回目、2回目のテスト結果すべてが一致する重複データがあるかどうかを探します。

エクセルの重複データを確認する方法【2列以上の場合】

STEP.1
データ結合をする列と重複判定をする列を作ります。画像ではデータ結合をする列をD列に、重複判定をする列をE列にしています。D2セルに「=A2&B2&C2」と入力し、Enterを押します。

エクセルの重複データを確認する方法【2列以上の場合】

STEP.2
D2セルに「山田4756」と表示されました。これはA2、B2、C2セルのデータを結合したものです。他の行も同じように結合したいので、D2セルの右下(フィルハンドル)にポインターを移動させ、黒の十字アイコンに変化したら9行目までドラッグします。

エクセルの重複データを確認する方法【2列以上の場合】

STEP.3
D行に結合データが入力されたのを確認し、E2セルに「=COUNTIF($D$2:$D$9,D2)」と入力→Enterを押します。

エクセルの重複データを確認する方法【2列以上の場合】

STEP.4
D2の内容が全体で何回表示されたか、回数が表示されます。E2は1なのでD2の内容に重複はありません。続いてE2セルの右下(フィルハンドル)にポインターを移動させ、黒の十字アイコンに変化したら、9行目までドラッグします。

エクセルの重複データを確認する方法【2列以上の場合】

STEP.5
E4、E9セルに2と表示されます。4行目と9行目に重複があることが分かりました。

エクセルの重複データを確認する方法【2列以上の場合】

エクセルの重複データを抽出して削除する方法

エクセルの重複データを発見できたら、抽出して削除しなければなりません。重複データの抽出には、フィルター機能を使います。COUNTIF関数の重複判定にフィルターをかけ、重複している行のみを表示してから削除を行います。

STEP.1
まず、前述の手順でCOUNTIF関数を使い、2回入力されている重複データを発見します。次に項目名部分を選択し、「ホーム」タブの「並べ替えとフィルター」→「フィルター」クリックします。

エクセルの重複データを抽出して削除する方法

STEP.2
表の見出し欄に「▼」アイコンが表示されます。「重複判定」の列の「▼」をクリックします。

エクセルの重複データを抽出して削除する方法

STEP.3
フィルターを掛ける項目が表示されます。「2」にのみチェックを入れて、「OK」をクリックします(「1」にチェックがついている場合はクリックして外します)。

エクセルの重複データを抽出して削除する方法

STEP.4
重複判定の列に2と表示されているデータのみが残りました。

エクセルの重複データを抽出して削除する方法

STEP.5
2行目以降のデータは重複データなので必要ありません。行をクリック→右クリックで「行の削除」を選びます。

エクセルの重複データを抽出して削除する方法

STEP.6
重複データが削除されると、重複判定のセルが2から1に変化し、重複がなくなったことが分かります。最後に、「ホーム」タブ→「並べ替えとフィルター」→「クリア」を選択し、フィルター機能を解除します。

エクセルの重複データを抽出して削除する方法

STEP.7
重複データが消えた状態で、元の表が表示されます。

エクセルの重複データを抽出して削除する方法

エクセルで別シートと一致する重複データを抽出・削除する方法

エクセルの場合、複数のシートで作業を行うこともあります。最後にシート間で一致する重複データを抽出し、削除する方法についてご紹介します。

複数シートの重複判定をするには、COUNTIF関数も複数使う必要があります。COUNTIF関数は足し算をすることで、「AかつB」という複数条件を設定できます。

STEP.1
重複したセルを判定する列を作成します。画像例では、データ1とデータ2という複数シート内で重複データがないか判定していきます。B列に「重複判定」の列を作っておき、データ2のB2に以下の数式を入力します。

=COUNTIF($A$2:$A$8,A2)+COUNTIF(データ1!$A$2:$A$8,A2)

エクセルで別シートと一致する重複データを抽出・削除する方法

最初のCOUNTIF関数は、同一シートのA2~A8とA2間で重複データがないか探してカウントしています。次のCOUNTIF関数はデータ1というシート内のA2~A8内でデータ2シートのA2セルと重複するデータ個数をカウントしています。

※「シート名!セル番地」で別シートのセルを指定することができます。

STEP.2
他の行も同じように判定したいので、B2セルの右下(フィルハンドル)にポインターを移動させ、黒の十字アイコンに変化したら9行目までドラッグします。

エクセルで別シートと一致する重複データを抽出・削除する方法

STEP.3
データ1とデータ2のシート間で重複しているデータ行にのみ2が、重複していない行には1が表示されました。

エクセルで別シートと一致する重複データを抽出・削除する方法

STEP.4
重複データを削除します。項目名を選択し、「ホーム」タブ→「並べ替えとフィルター」→「フィルター」をクリックします。

エクセルで別シートと一致する重複データを抽出・削除する方法

STEP.5
「重複判定」右下の「▼」をクリックし、「2」にチェックを入れ、「OK」をクリックします。

エクセルで別シートと一致する重複データを抽出・削除する方法

STEP.6
重複データのみが表示されます。いらない行を選択し、右クリック→「行の削除」でデータを削除します。

エクセルで別シートと一致する重複データを抽出・削除する方法

エクセルの重複データの抽出方法を覚えてデータ整理を効率化

以上、エクセルの重複データを抽出する方法をご説明しました。重複データがないかを1つ1つ確認していくのは大変です。

しかしエクセルの条件付き書式やCOUNTIF関数を使えば、重複データを簡単に発見できます。重複データを抽出したら、フィルター機能を使って必要のないデータを削除することも可能です。

エクセルでの重複データ抽出方法を覚えれば、日々のデータ整理業務も効率的に進みます。