Excelで大量のリストを扱っていると、
「同じ名前が重複していないか確認したい」
「取引先コードが重複していないかチェックしたい」
といった場面があるかと思います。
Excelには、マクロを使わなくても重複を簡単に見つけられる方法が複数あります。
本記事では、初心者でも簡単に使える3つの方法をまとめました。
①UNIQUE関数とCOUNTIF関数を組み合わせる方法｜最もおすすめ
Excel 2021以降のバージョン、もしくはOffice 365をお使いの方は、動的配列関数が利用できるためここで紹介する方法がおすすめです。
例として以下のような氏名リストに重複がないかチェックします。
- 亜米利加 太郎
- 英吉利 次郎
- 仏蘭西 三郎
- 独逸 史郎
- 西班牙 四郎
- 伊太利亜 五郎
- 露西亜 六郎
- 独逸 史郎
- 印度 七郎
- 豪州 八郎
- 英吉利 次郎
- 加奈陀 九郎
手順
重複データを出力したいセル = 関数を入力するセルを選択します。
今回はわかりやすくするためにリストの横を選択しますが、別シートでも構いません。
以下の数式を入力してください。
それぞれの関数の意味は後で簡単に説明します。
=UNIQUE(FILTER(検索範囲,COUNTIF(検索範囲,検索範囲)>1))
上記の”検索範囲”に重複チェックしたいリストの範囲を入力します。
マウスドラッグで範囲を選択すると簡単に範囲入力できます。
=UNIQUE(FILTER(A1:A12,COUNTIF(A1:A12,A1:A12)>1))
関数の入力ができたら、Enterを押して出力結果を確認します。
重複データが複数ある場合は自動でスピルされ、関数を入力したセルの下に結果が展開されます。
反対に重複データがない場合は、『#CALC!』エラーが表示されます。
スピル機能とは、1つのセルに数式を入れると、結果が自動的に隣接セルに広がる（スピルする）仕組みです。
UNIQUE関数とFILTER関数は動的配列関数であり、このスピル機能に対応しております。
2019年デスクトップ版以前のExcelのバージョンでは、このスピル機能が使えない場合があります。
COUNTIF関数の動き｜各セルが何回出現しているかを数える
COUNTIF関数は、指定した範囲の中で、各セルの値が何回出現しているかを数える関数です。
今回の式では次のように使われています。
=COUNTIF(A1:A12, A1:A12)一般的なCOUNTIFの使い方は、例えば「〇」のあるセルだけ数えるのように
=COUNTIF(範囲,検索条件）
と第2引数は「単一の検索条件」ですが、
ここでは A1:A12 という範囲セルをそのまま渡しているため、配列計算になります。
A1〜A12 の各セルについて、
- その値が A1:A12 に何回出現しているか
- 12個分まとめて配列として返す
という動作になります。
FILTER関数の動き｜重複している行だけを抽出する
次に FILTER 関数が動きます。
FILTERは「条件に合うデータだけを取り出す関数」です
ここでは、COUNTIFが返した「出現回数の配列」に対して 「>1（1回より多い＝重複）」という条件を適用します。
=FILTER(A1:A12, COUNTIF(A1:A12,A1:A12)>1)FILTER関数を使うと、重複している行だけを取り出し、重複データの集合を作ります。
UNIQUE関数の動き｜重複を整理して、値を1つにまとめる
最後に UNIQUE が動きます。
UNIQUEは「重複を取り除いて1回だけの値に整理する関数」です。
=UNIQUE(FILTER(A1:A12, COUNTIF(A1:A12,A1:A12)>1))
FILTER が返した「重複している値の集合」から 重複を除いたユニークな値だけを抽出します。
＜UNIQUEの役割＞
- FILTER が返した重複データを整理してユニーク値のみ残す
- 結果は スピル機能によって、入力セルの下に自動展開される
UNIQUE関数はもともとユニークデータのみ残す関数です。
今回は「重複データはあるか？」「どのデータが重複しているか？」に着目した方法を解説していますが
UNIQUE関数をそのまま
=UNIQUE(検索範囲)とすると、重複データを除いたリストを表示してくれます。
こちらが好みの方はぜひ試してみてください。
全体の流れ（式の内部処理）
- COUNTIF関数：各値が何回出現しているかを配列で返す
- FILTER 関数：COUNTIF関数 の結果を使い、出現回数 >1 の行だけ抽出
- UNIQUE ：FILTER関数 の結果から重複を除き、ユニークな重複値だけを一覧化
- スピル ：UNIQUE関数 の結果が入力セルから下方向へ自動展開される
②条件付き書式を使う方法｜視覚的にわかりやすい
Excelには、重複セルを自動で色付けする機能があります。 初心者でも数クリックで設定でき、視覚的に重複を確認したいときに便利です。
● 手順
マウスドラッグ、もしくは『Shift+Ctrl+↓』キーで範囲を選択します。
文字の色や背景など好みのスタイルをリストから選択して『ＯＫ』ボタンで確定します。
『ユーザーの書式設定』から細かく設定することもできます。
これだけで、重複しているセルが自動で色付けされます。
● 条件付き書式のメリット
- 色で一目でわかる
- マクロや関数も不要で初心者向け
- Excel2010年版以降で使えるので互換性が高い
● 注意点
- 色付けだけなので「重複数」はわからない
- リストが多い時は全体を確認するのに手間がかかる
- 抽出や削除には向かない（フィルターと併用すれば可能）
③ピボットテーブルを使う方法
ピボットテーブルを使うと、 「どの値が何回出現しているか」を一覧で確認できます。
手順
マウスドラッグ、もしくは『Shift+Ctrl+↓』キーで範囲を選択します。
テーブルの範囲が最初に選択した範囲となっているか確認します。
またピボットテーブルを入れる場所も指定します。
ここは新規ワークシート、既存ワークシートのどちらでも大丈夫です。既存ワークシートに挿入する場合は、挿入する場所を指定してあげましょう。
ピボットテーブルの使い方は以下の記事も参考にしてみてください。
重複チェックしたいリストが数値で、値フィールドが『合計』などになっている場合は、
『値フィールドの設定』から『個数』に設定します。
これにより、COUNTIF関数と同じように「出現回数」がわかるので重複チェックに使えます。
その他：フィルターを使って重複行を非表示にする
ユニークデータだけ残したい場合に便利です。
手順
マウスドラッグ、もしくは『Shift+Ctrl+↓』キーで範囲を選択します。
『フィルターオプションの設定』のダイアログボックスで『重複するレコードは無視する』にチェックを入れて『ＯＫ』ボタンを押します。
重複している行は非表示になるため、必要に応じて、削除したり再表示させたりします。
非表示になった行を再表示させる方法はいくつかありますが、隠れている行番号部分をダブルクリックすると手早く再表示できます。
さいごに｜初心者でも重複チェックは簡単にできる
私も年度更新のリストに重複データがないかチェックすることがあります。
以前は先輩が作成してくれた、重複チェック用のマクロを使っていましたが、
チェックしたいデータを一度、マクロファイルに転記して使用する必要があり、少し面倒でした。
Office 365版のExcelになってからは、UNIQUE関数とCOUNTIF関数の組み合わせで簡単にチェックできるようになったため、非常に役に立っています。
現在はAIを使ってマクロのコードを簡単に作成できる時代になりましたが
Excel関数を使う方が大量のデータを素早く処理できるのでおすすめです。
今回の記事が皆さんのお仕事に少しでも役に立てれば幸いです。それではまた(^^)ﾉｼ
コメント