重複するデータの洗い出し方法｜条件付き書式とCOUNTIF関数
使用関数：COUNTIF、IF
重複データをデータベースから抽出するテクニックです。条件付き書式で重複するレコードのセルやフォントに色を付けて強調表示させる、COUNTIF関数を使って重複データの個数を表示させる、それを応用して重複のチェックマークを付ける操作を図解します。
条件付き書式で重複データのセルやフォントに色を付ける
重複データだけにセルの背景色を付けたり、フォントの色を変えるのは「条件付き書式」で行います。フィルターで抽出しやすい状況にしておくわけですね。
ここでは、取引先名簿の「氏名」フィールドに適用することにします。フィールド名の入ったセル内にカーソルをポイントして、下向きの黒矢印の形になったらクリック。「氏名」フィールド配下のセル範囲を全て選択します。（※アルファベットのところでクリックしちゃうと列全体が選択されてしまうので注意）
「ホーム」タブの「条件付き書式🔻」→「セルの強調表示ルール」→「重複する値」をクリックします。
「重複する値」ダイアログが開きます。重複データに書式を適用するか、重複がないデータに適用するかを選択し、書式の種類も幾つかの選択肢から選べます。
デフォルトの書式で「OK」すると、重複するデータの「氏名」セルに書式が適用されました。セルとフォントの両方に色が付いていますね。
関数で重複のチェック
どちらが分かりやすいかは人それぞれですが、重複のチェックフィールドを設けるやり方もあります。こちらは関数を使用します。
「COUNTIF」は「条件に一致するデータの数を返す」関数です。同じデータがフィールド内に幾つあるかを数えてくれます。重複したデータがあれば「２」以上の数が返り、一意のデータであれば「１」と返るということですね。
即ち、「COUNTIFで返された数字が２以上なら・・・」または、「COUNTIFで返された数字が１より大きければ・・・」という比較演算子を使ったIF文でチェックリストを実装すればいいわけで、IF関数にCOUNTIFをネストした数式を組み立てます。
比較演算子の詳細はIF関数のページへ
COUNTIF関数で重複データの個数を表示
最初に、チェックが入るフィールドを増設します。フィールドを増設する位置の右隣の列にあるセルを選択して右クリック。メニューから「挿入」→「テーブルの列」をクリックします。
フィールド名を「重複」にして、最初のセルを選択。「数式」タブの「関数ライブラリ」の「その他の関数」→「統計」→「COUNTIF」をクリックします。
「COUNTIF」の関数の引数ダイアログボックスが開きます。
最初の引数「範囲」は条件を検索する範囲の指定です。[氏名]フィールドだと同姓同名が含まれている可能性があるので、[住所]を指定します。（※この表はテーブル化していますので、参照形式が構造化参照になっています）
構造化参照の基本
次の引数「検索条件」には検索値である住所を入れればいいので、「住所」フィールドの最初のセルをクリックして指定します。
「重複」フィールドにデータの個数が入りました。
数式は「=COUNTIF([住所],[@住所])」です。次章でIFに入れ子するので、この数式（=を除いた部分）をコピーしておきます。
【付記】：表によっては一つの範囲指定だけでは重複かどうかの判定が難しい場合があります。その時は「COUNTIF」ではなく、複数の条件を指定できる「COUNTIFS」を使います。
数式は「=IF(COUNTIFS([住所],[@住所],[氏名],[@氏名])>1,"✔","")」のようになります。範囲と条件をカンマで区切って交互に並べる構成ですね。
IF関数にネストして重複チェックマークを付ける
COUNTIFで表示させたデータ個数を「降順」で並べ替えれば重複データの多い順になります。重複データを洗い出すというミッションは果たしているので、このままでも問題ないのですが、やはりチェックマークで一目で判るようにしておきましょう。
「関数ライブラリ」→「論理」→「IF」をクリックしてダイアログを開き、最初の引数「論理式」にコピーしておいた数式をペーストし、そこに続けて「>1」（または>=2）と入力します。前述したように、「COUNTIFで返された数字が１より大きければ」（または「COUNTIFで返された数字が２以上なら」）というIF文になりました。
このIF文に答えて、「真の場合」に「"✔"」を入力し、「偽の場合」に空欄を意味する「""」を入力しました。重複データがあればチェック欄にレ点が表示されるということです。（※IMEやGoogle日本語入力が設定された環境なら「✔」は「ちぇっく」と入力して変換できます）
チェック欄を確認すると、数式は正常に機能しています。
最終的な数式は「=IF(COUNTIF([住所],[@住所])>1,"✔","")」となります。
「住所の列に同一の住所が複数あれば、チェック欄に✔が付き、なければ何も表示されない」
抽出した重複レコードをフィルター機能でまとめ、別シートに移す操作の解説記事です。