Excel

すぐできる!重複データ(複数条件も)をチェック・抽出するExcelテクニック

 

この記事では、前半に顧客リストや在庫管理表などの重複が許されないExcel表から重複データを探す2つの方法を紹介し、後半では、2つの列・複数条件から重複データを探す方法を紹介します。

 

重複データをチェック・抽出する

条件付き書式で色をつける

重複データを探す1つ目の方法は、[条件付き書式]を使う方法です。
[条件付き書式]を使って、「重複データ(ここでは商品名)があるか」をチェック・抽出します。重複データがある場合にはセルに色が付きます

 

重複を調べる範囲を選択する

まずは重複しているかどうかを調べたい範囲を選択します。
今回は商品名に重複があるかを調べたいので、商品名が入力されているA列を選択します

 

A列全体を選択するには、A列のいずれかのセル(ここではA2セル)を選択し、[Ctrl + Space]と押します。

A列全体を選択

 

[条件付き書式]ボタンをクリックする

[ホーム]タブの[スタイル]グループにある[条件付き書式]ボタンをクリックします。

条件付き書式ボタン

 

[重複する値]を選択する

[条件付き書式]ボタンをクリックして表示されるコンテキストメニューから、[セルの強調表示ルール]を選び、[重複する値]を選択します。

重複する値

 

重複する商品名に色が付く

[重複する値]を選択すると、重複する商品名がある場合には、セルに色が付きます。ここでは、「油性ボールペン_黒」と「水性ボールペン_黒」がそれぞれ2つあり重複していることがわかります。

重複する値に色がつく

 

COUNTIF関数でチェック・抽出する

重複データを探す2つ目の方法は、COUNTIF(カウントイフ)関数を使う方法です。

 

COUNTIF関数を入力する

(日本語入力OFFで)C2セルに[=cou]と入力します。
すると、COUNTIFが表示されるので、COUNTIFを選択し[Tab]キーを押します

TabでCOUNTIF関数を選択

 

第1引数[検索範囲]を入力する

すると、C2セルに[=COUNTIF(]と入力されます。続けて、A2セルを選択します(もしくは、A2と入力)。

=COUNTIF(A2]と入力されている状態で、[ : ](コロン)を押します。
すると、[=COUNTIF(A2:A2]という式になります。

 

この式の左側の[A2]の部分を選択し、[F4](ファンクションキー)を1回押して、[=COUNTIF($A$2:A2]とします。

COUNTIF関数の範囲を入力

 

この「$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以上の数値になり、一目で重複している「商品名」を探すことができます。

重複箇所は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行目が「氏名」と「電話番号」ともに重複しているのが見つかります。

COUNTIF関数で複数条件の重複を探す

 

COUNTIFS関数を使って探す

COUNTIFS関数を使用すると、複数の列を条件として重複データを探すことができます。

D2セルには =COUNTIFS($A$2:A2,A2,$B$2:B2,B2) と入力します。COUNTIFS関数でも、7行目が「氏名」と「電話番号」ともに重複しているのが見つかります。余計な列を追加せずに探すことができる分、スムーズです。

COUNTIFS関数で複数条件の重複を探す

 

関連記事

参考文献

-Excel
-, ,

© 2022 まなびっと Powered by AFFINGER5