【Excel】COUNTIFで重複件数を数える|2回目以降・先頭0・記号の注意点

Excelで同じ値が何回出てくるかを調べるには、COUNTIF関数を使います。基本形は=COUNTIF(調べる範囲,条件)です。ただし、「全体に何件あるか」と「上から何回目か」は式が違います。さらに、先頭に0があるIDや、記号を含む文字列は、単純な式だけで厳密な一致判定をしないよう注意が必要です。

重複は、すぐ削除してよいという意味ではありません。同じ顧客が別の日に購入した記録など、残すべき行もあります。まず補助列に件数を出し、何を一件とみなすか確認してから整理します。

1. 小さな一覧でCOUNTIFの結果を確かめる

A1を「商品」、A2からA7を順に「りんご、みかん、りんご、ぶどう、みかん、りんご」とします。B1を「全体の件数」とし、B2に次の式を入力してB7までコピーします。調べる範囲には見出しのA1を含めません。

=COUNTIF($A$2:$A$7,A2)

B2からの結果は3、2、3、1、2、3です。りんごの行にはどの行でも3、みかんには2が入ります。範囲の$A$2:$A$7を固定しているため、下へコピーしても調べる六つのセルは変わりません。条件のA2は行に応じてA3、A4へ移動します。

MicrosoftのCOUNTIFの説明は、範囲内で条件に合うセルを数える関数として案内しています。行全体が同じかを比較しているのではありません。商品名だけが同じでも、数量や日付まで一致しているとは限らないことを覚えておきます。

2. 2件以上の行を見つけ、空欄は数えない

空欄の行がある一覧では、補助列も空欄にしておくと確認しやすくなります。B2には次の式を入れます。ここでA2の空欄判定は、未入力や空文字を表示対象から外すためのものです。スペースだけ入ったセルまで自動的に空欄扱いする式ではありません。

=IF(A2="","",COUNTIF($A$2:$A$7,A2))

B列の数値が2以上なら、その値は範囲内に複数回あります。フィルターで2以上を表示して、元の行を確認できます。件数を色で強調する場合も、判定列を残しておくと、なぜその行を重複候補としたのか分かります。

この例で「重複する値の種類」は、りんごとみかんの2種類です。「重複に関係する行」は5行、「各種類の先頭一件を残した余分な行」は3行です。同じ表でも何を数えるかで数値が変わるので、報告するときは単に「重複5件」と書かず、対象を明示します。B列の件数を合計しても、この三つのどの数にもなりません。

3. 2回目以降だけを調べるなら範囲を広げながら数える

上から見て何回目かを知りたい場合は、開始セルだけを固定し、終了セルを現在行までにします。C2に次の式を入力してC7までコピーしてください。

=IF(A2="","",COUNTIF($A$2:A2,A2))

結果は1、1、2、1、2、3になります。全体の件数とは違い、最初のりんごは1、次は2、最後は3です。この補助列で2以上の行を抽出すると、各値の2回目以降だけを候補にできます。

ただし、並べ替えると「先頭」の行が変わります。最新の記録を残すのか、最初の登録を残すのかなどのルールを先に決め、必要なら日付や管理番号も確認します。同じ名前の人を同一人物と決めつけたり、同じ商品を含む別取引を不要行と判断したりしないでください。

削除せず確認だけしたいなら、補助列の抽出を解除すれば元の全行に戻せます。並べ替えるときは商品列だけでなく表全体を対象にし、他列との対応を保ちます。

4. 「*」「?」「~」を含む値は文字として数える

COUNTIFの条件では、*は任意の文字列、?は任意の一文字を表すワイルドカードです。A2にA*が入っているとき、そのまま条件にすると「A*という文字」だけでなく、Aで始まる別の値も一致する可能性があります。

名前などの文字列に記号が含まれる場合は、チルダを使って記号を通常の文字として扱います。次の式は、~を先に~~へ変え、その後で*と?の前にチルダを付けます。

=IF(A2="","",COUNTIF($A$2:$A$7,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?")))

たとえば条件のA*は=A~*になり、末尾の星印自体を探します。置換順を逆にすると、後から付けたチルダまで二重になるため注意してください。SUBSTITUTE関数は、このような特定文字の置換に使えます。数値のようなIDを厳密に区別する目的は、次の方法で別に考えます。

5. 先頭0や大文字・小文字を区別するIDはEXACTを使う

会員IDの「001」と「1」を別物として扱うなら、入力・取り込み時から文字列として保持します。表示形式を000にして数値1を001と見せただけでは、中身は数値1です。既に失われた先頭の0を、後から[文字列]へ変えるだけで取り戻すことはできません。原本と照合して取り込み直します。

また、COUNTIFは英字の大文字・小文字を区別しません。文字列として取り込んだという理由だけで、COUNTIFを厳密なID比較とみなすのも避けます。文字列そのものを区別する必要がある場合は、EXACT関数で比較し、TRUEの件数をSUMPRODUCTで合計します。

=IF(A2="","",SUMPRODUCT(--EXACT($A$2:$A$7,A2)))

すべて文字列として保持した「001」「1」「001」「A*」「AB」「A*」なら、結果は順に2、1、2、2、1、2です。EXACTは大文字・小文字も区別し、セルの書式の違いは比較しません。IDの正式な表記規則が分からないまま、全角半角変換や空白除去で別のIDを一つにまとめないようにします。

6. 想定した件数と違うときの点検順

まず参照範囲の先頭と末尾を確認します。新しい行がA8以降に増えていても、式がA7までなら対象外です。反対に列全体を指定すると、欄外の集計や別の一覧まで含めるおそれがあります。現在の表の範囲を明示する方が、結果を点検しやすくなります。

次に、コピー後も範囲が固定されているか、条件が同じ行のセルを見ているかを確認します。余分な空白や改行が疑わしい場合は、数式バーで内容を見て、LENで文字数を比べます。元データを一括置換する前に、どの文字が余分なのかを確かめてください。

数値・文字列・日付が混在している一覧では、見た目が同じでも扱いが異なります。「左寄せだから文字列」と外観だけで判断せず、ISTEXTやISNUMBERを補助に使います。長い識別子や255文字を超える文字列などは、COUNTIFの制約も公式説明で確認します。SUMPRODUCTを使う場合も、負荷を抑えるため列全体の参照は避けます。

7. 条件付き書式と削除は別の作業にする

例のような通常の商品名なら、A2:A7を選び、[条件付き書式]で数式によるルールを作成し、次の判定式を使えます。適用先の先頭がA2なので、数式もA2を基準にします。

=AND(A2<>"",COUNTIF($A$2:$A$7,A2)>1)

これは全体に複数ある値の行を強調する式で、先頭の一件も対象です。記号や厳密なID判定を要する一覧では、前の節で作った適切な件数の補助列を参照して色分けします。色が付いたというだけで行を削除してはいけません。

重複候補を確認してから、どの列の組み合わせで同一記録とみなすかを決めます。元表のコピーを残し、削除を行うなら件数と残す行を確認してから実施します。

8. 確認後は補助列と作業記録を整理する

検算には、元の一覧でりんごを一件ずつ数え、B列の3、C列の1・2・3と比較する方法が使えます。IDでは、先頭0、記号、大文字・小文字を含む小さな例を先に試し、業務上の区別と式の区別が一致することを確認してください。

確認用の式が不要になったら補助列の内容や対象の条件付き書式だけを削除します。元データはそのまま残せます。元表の値を修正した場合は、修正内容を記録し、誤りに気づいたら[元に戻す]または保存したコピーから復元します。件数を出すことと、データを統合・削除する判断を分けると、安全に一覧を整理できます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】