顧客マスタなど、重複データしたデータが入力されていてはいけないリストがあります。そして、この重複データが入力されているかどうかを、目で追って確認するのは大変な作業になります。そこで、今回の記事では、ワークシート関数を組み合わせて、重複データのチェックを効率的に行う便利なテクニックを紹介します。

重複データをチェックするには

重複データと重複していないデータ。これらの違いは「出現回数」です。重複していないデータの出現回数は「1」ですが、重複データの出現回数は「1」以外の数になります。

そこで、重複データをチェックするときに活用したい関数がCOUNTIF関数です。

COUNTIF関数は「特定のセル範囲内で、あるデータが入力されているセルの個数を数える」関数。まさに、データの出現回数を数えたい重複データチェックで利用できる関数ですね。



しかし、あるデータが出現した回数を表示しても、今度は「1」以外の数値を探す必要があります。この作業も、目で追って確認していては間違いを起こす可能性があります。

そこで、IF関数を組み合わせて、『出現回数が「1」でないときは「重複」という文字列を表示し、そうでないときは何も表示しない』といった数式を作成します。これなら、ひと目で重複しているデータを見つけることができますね。



COUNTIF関数の基本

COUNTIF関数の構文は次の通りです。

※今回の組み合わせ術とは直接関係ありませんが、COUNTIF関数の引数「検索条件」の指定方法を工夫すると、様々な活用法が考えられます。


COUNTIF関数の使い方

例えば、セルB2~B7に、次のように「Yes」と「No」が入力されています。ここで、セルB9に「=COUNTIF(B2:B7,"Yes")」と入力します。検索範囲に「セルB2~B7」を指定し、検索条件に「"Yes"」、つまり「『Yes』と入力されているセル」を指定します。



すると、COUNTIF関数を入力したセルに「Yes」と入力されているセルの個数「4」が表示されます。



しかし、検索の条件となっている「Yes」が関数の中に記述されているので、何のデータが入力されているセルの個数なのかがわかりづらいですね。また、「No」が入力されているセルの個数を求めたいとき、また同じように関数を入力する必要があるので、少々面倒です。

そこで、今度は、セルA9に「Yes」と入力して、検索条件にセルA9を指定します。これなら、何のデータの個数を求めているかわかりやすくなります。



また、範囲に指定したセル範囲を絶対参照に変換すれば、セルA10に「No」と入力し、オートフィルを使ってセルB10に関数を入力すれば、簡単に「No」が入力されているセルの個数「2」を求めることができます。



アンケート集計などに便利なCOUNTIF関数

COUNTIF関数は、アンケート集計などで利用できます。例えば、次のサンプルでは、セル範囲B3~D6にアンケート結果が入力されていて、セル範囲B10~D12にはアンケートの集計結果を表示するCOUNTIF関数が入力されています。



今回の組み合わせ術とは直接関係がありませんが、複合参照の復習にもなるので、簡単に入力している数式について紹介しておきましょう。

セルB10には、セル範囲B3~B6を検索範囲とし、「セルA10の入力データの左から1文字目」の文字列が入力されているセルの個数を求める数式「=COUNTIF(B$3:B$6,LEFT($A10,1))」が入力されています。

LEFT関数については、ガイド記事「文字列の取り出し自由自在」をご覧下さい。


ここでは、複合参照を利用してセル範囲を記述しているので、集計結果を表示したいその他のセルにオートフィルで数式を入力できます。COUNTIF関数の「範囲」に指定した「B$3:B$6」は「3行目~6行目」の指定だけが固定されているので、C11のセルであれば、オートフィルにより「C$3:C$6」となり、Q2のアンケート結果範囲が参照されます。

複合参照については、ガイド記事「オートフィルのための数式作成(後編)」をご覧下さい。


また、COUNTIF関数の「検索条件」に指定した「LEFT($A10,1)」は「A列」の指定だけが固定されているので、C11のセルであれば、オートフィルにより「LEFT($A11,1)」と入力され、「2:普通」の集計条件を参照できます。



重複データをCOUNTIF関数でカウントしよう

ここでは、次のような顧客マスタデータをサンプルとして、重複データをチェックする方法を解説します。



ひとまず、顧客名について重複をチェックしてみましょう。

重複をチェックする範囲はセルB4~B10です。しかし、今後、マスタデータが増えていく可能性もあるので、ここでは、B列全体をチェック範囲とします。そして、B列の各行のセルを検索条件に指定して、「各行のセルに入力されているデータ」が入力されているセル、つまり重複データをB列内で検索して、そのセルの個数を求めます。個数は関数を入力したセルに表示されます。



この数式を各行にコピーすれば、各行のデータについて、検索範囲内に入力されている個数を求めることができます。この個数が「1」より大きい行に、重複データが入力されている、ということになるわけですね。



COUNTIF関数とIF関数を組み合わせた式を作成しよう

さて。これでCOUNTIF関数によってデータの個数が求められたわけですが、個数が「1」より大きい行を判別するのも面倒ですね。そこで、IF関数を組み合わせて、重複データが際立つように工夫します。

次のように、COUNTIF関数の結果が「1」より大きい場合は「重複」と表示し、それ以外は空白にします。IF関数の条件式の中にCOUNTIF関数を埋め込んでいる点がポイントです。



この数式を各行にコピーすれば、重複データが入力されている行だけに「重複」と表示されるので、どこに重複データがあるのかが一目瞭然です。



複数項目について重複データをチェックする工夫

これで「顧客名」が重複しているデータをチェックすることができましたが、注意深く確認してみると、顧客名が重複している「ABC株式会社」の所在地が「東京都港区」と「静岡県富士市」で違っています。同じ顧客名ですが、別な会社のようです。



このように、顧客名だけでなく、所在地やその他のデータも含めて重複をチェックする場合があります。ここで、所在地やその他のデータについてもCOUNTIF関数とIF関数を使って重複データをチェックし、それぞれの項目が全て重複しているかどうかを確認するのは大変面倒です。



そこで、各項目のデータを連結して、ひとつの文字列にまとめておきます。こうすれば、各行について一意のデータとなり、各項目のデータについて個別にチェックする必要もありません。



この連結した文字列のセル範囲をCOUNTIF関数の範囲に指定し、連結した文字列を検索条件としてCOUNTIF関数を作成すれば、全項目についての重複チェックがまとめて処理できます。

入力されている数式

入力した結果

【関連記事】
Excelの表で重複しているデータを削除する方法




※記事内容は執筆時点のものです。最新の内容をご確認ください。
※OSやアプリ、ソフトのバージョンによっては画面表示、操作方法が異なる可能性があります。