この記事では、前半に顧客リストや在庫管理表などの重複が許されないExcel表から重複データを探す、2つの方法を紹介し、後半では、2つの列・複数条件から重複データを探す方法を紹介します。
重複データをチェック・抽出する
条件付き書式で色をつける
重複データを探す1つ目の方法は、[条件付き書式]を使う方法です。
[条件付き書式]を使って、「重複データ(ここでは商品名)があるか」をチェック・抽出します。重複データがある場合にはセルに色が付きます。
重複を調べる範囲を選択する
まずは重複しているかどうかを調べたい範囲を選択します。
今回は商品名に重複があるかを調べたいので、商品名が入力されているA列を選択します。
A列全体を選択するには、A列のいずれかのセル(ここではA2セル)を選択し、[Ctrl + Space]と押します。
[条件付き書式]ボタンをクリックする
[ホーム]タブの[スタイル]グループにある[条件付き書式]ボタンをクリックします。
[重複する値]を選択する
[条件付き書式]ボタンをクリックして表示されるコンテキストメニューから、[セルの強調表示ルール]を選び、[重複する値]を選択します。
重複する商品名に色が付く
[重複する値]を選択すると、重複する商品名がある場合には、セルに色が付きます。ここでは、「油性ボールペン_黒」と「水性ボールペン_黒」がそれぞれ2つあり重複していることがわかります。
COUNTIF関数でチェック・抽出する
重複データを探す2つ目の方法は、COUNTIF(カウントイフ)関数を使う方法です。
COUNTIF関数を入力する
(日本語入力OFFで)C2セルに[=cou]と入力します。
すると、COUNTIFが表示されるので、COUNTIFを選択し[Tab]キーを押します。
第1引数[検索範囲]を入力する
すると、C2セルに[=COUNTIF(]と入力されます。続けて、A2セルを選択します(もしくは、A2と入力)。
[=COUNTIF(A2]と入力されている状態で、[ : ](コロン)を押します。
すると、[=COUNTIF(A2:A2]という式になります。
この式の左側の[A2]の部分を選択し、[F4](ファンクションキー)を1回押して、[=COUNTIF($A$2:A2]とします。
この「$A$2:A2」のように「絶対参照 : 相対参照」の形で指定すると、数式をコピーしたときにA2セルを固定したまま、終点だけを「A3、A4、A5」とずらすことができます。
テクニック
「絶対参照 : 相対参照」の形で指定するテクニックは、SUM関数で累計を求める場合などにも使えるので、ぜひ覚えてください。
第2引数[検索条件]を入力する
[=COUNTIF($A$2:A2]の後ろに、[ , ](半角カンマ)を入力し、第2引数[検索条件]を入力します。
今回の[検索条件]は、重複があるかを調べたい「商品名」ですので、A2セルをクリックします。
[=COUNTIF($A$2:A2,A2]と入力したら、[Ctrl + Enter]と押して確定します。
[Enter]と押して確定すると、アクティブセルがC3セルやD2セルに移動してしまいますが、[Ctrl + Enter]で確定すれば、アクティブセルはA2のままです。
テクニック
[Ctrl + Enter]と押すときは、右側の[Ctrl]キーと[Enter]キーを押すのがオススメ。右手だけで押せます。
重複箇所は2以上の数値になる
数式をC2セル~C9セルの範囲に入力すると、重複箇所は2以上の数値になり、一目で重複している「商品名」を探すことができます。
重複箇所だけに「重複」と表示するには
IF関数を使ってもっと見やすく
「重複箇所が2以上の数値になる」ことを利用して、C2セルの式を =IF(COUNTIF($A$2:A2,A2)>=2,"重複","") とし、C列が2以上の値のときに「重複」と表示されるようにすれば、さらに重複しているデータを探しやすくなります。
2つの列・複数条件から重複データを探す
同姓同名が存在するため、「氏名」の重複は許されるが、「氏名」と「電話番号」の複数条件の重複は許されないといったケースがあります。
このような「複数条件」の重複を探す方法を2パターン紹介します。
COUNTIF関数を使って探す
2つの列を結合演算子[&](アンパサンド)で結ぶ
COUNTIF関数を使って、複数条件の重複を探すには、「氏名の列」と「電話番号の列」の2つの列を結合演算子[ & ](アンパサンド)で結びます。
「氏名」と「電話番号」を結合した文字列をつくるため、C2セルには =A2&B2 と入力します。
COUNTIF関数を入力する
「氏名」と「電話番号」の両方が重複していないのかを調べるには、今作成した「氏名」と「電話番号」を結合した文字列の重複を調べればよいので、D2セルには =COUNTIF($C$2:C2,C2) と入力します。
ここでは、7行目が「氏名」と「電話番号」ともに重複しているのが見つかります。
COUNTIFS関数を使って探す
COUNTIFS関数を使用すると、複数の列を条件として重複データを探すことができます。
D2セルには =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) と入力します。COUNTIFS関数でも、7行目が「氏名」と「電話番号」ともに重複しているのが見つかります。余計な列を追加せずに探すことができる分、スムーズです。
関連記事
- IF関数を使って「空白」だったら「空白」にするテクニック
- 検索や置換、COUNTIFやSUMIFなどのExcel関数に使えるワイルドカードの使い方
- COUNTIF関数の使い方|以上・未満などの比較演算子とワイルドカード
- Excelカレンダー(日曜始まり)の作成方法~DATE/COUNTIF関数
- 2022年(令和4年)の祝日・休日に自動で色がつくようにする~COUNTIF関数