Excel約4分で読めます

Excelで重複を数える|COUNTIFで2件目以降に印

Excelで重複を数えるを表したイメージ
記事の内容を表したイメージ(AI生成)

名簿や申込一覧に同じ名前や番号が複数行あるとき、「1件目は残して2件目以降だけに印を付けたい」という場面があります。Excelの重複削除機能はデータそのものを消してしまうため、印を付けて目視確認してから対処したい場合には向きません。

この記事では、COUNTIF関数を使って「その行より上に同じ値が何件あるか」を数え、2件目以降の行にだけ印を立てる方法を説明します。数式の組み方・判断の手順・迷いやすい例外の順に進めます。作業前にファイルをコピーして退避しておくことを先にお勧めします。


なぜ「その行より上だけ」を数えるのか

COUNTIF全体の範囲で同じ値を数えると、1件目も2件目も同じ結果になり、どちらが最初かを区別できません。範囲の開始行を固定して終了行をその行自身にする「可変範囲」にすることで、「今の行まで何件あるか」を行ごとに計算できます。

たとえばA列に値があるとき、N行目のセルで使う数式は次の形になります。

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

A2行目ではA2:A2(1件)、A3行目ではA2:A3(2件の中から)と自動的に範囲が広がります。その結果が1なら「ここまでで初めて登場した行(1件目)」、2以上なら「すでに上に同じ値がある行(2件目以降)」と判断できます。


具体例で確認する(架空の例です)

以下は架空の申込一覧です。

表は横にスクロールできます

行A列(申込者名)B列(COUNTIF結果)C列(判定)
2山田太郎1(空白)
3鈴木花子1(空白)
4山田太郎2重複
5田中一郎1(空白)
6鈴木花子2重複
7山田太郎3重複

B2に入力する数式:

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

これをB列の最終行までコピーします。B3以降は$A$2:A3、$A$2:A4と末尾だけ変わり、先頭は常にA2で固定されます。

C列は判定の表示列です。C2に次の数式を入れてコピーします。

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

「2以上なら重複と表示、それ以外は空白」という意味です。印の文字は任意に変えられます。◯や×でも構いません。


判断基準と条件の調整

結果が1のとき・2以上のとき

  • 1:その行が同じ値の中で最初の登場です。通常は残す対象です。
  • 2以上:上に同じ値がすでに存在します。確認・削除・統合の候補です。

大文字・小文字、全角・半角の扱い

COUNTIFは大文字と小文字を区別しません。yamadaとYAMADAは同じ値として数えられます。一方、全角と半角(アとア)は別の値として扱われます。表記が揺れている名簿では、COUNTIF適用前に表記を統一するか、EXACT関数を使う方法を検討してください。

空白セルがある場合

A列に空白セルがあると、空白どうしも同一とみなされてカウントが増えます。空白行を含むデータに使う場合は、B列の結果を目視で確認するか、数式にIF(A2="","",COUNTIF(...))のように空白除外の条件を加えます。

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

条件付き書式と組み合わせる

印を付けた列を目視しやすくするには、条件付き書式で行全体に色を付ける方法が便利です。手順の概要は次のとおりです(画面の表示は版や設定により異なります)。

  1. 対象のデータ範囲全体を選択します。
  2. 「条件付き書式」→「新しいルール」→「数式を使用して書式設定するセルを決定」を選びます。
  3. 数式欄に次を入力します(データが2行目から始まり、A列が検索列の場合)。
=COUNTIF($A$2:$A2, $A2)>=2
  1. 書式で背景色や文字色を設定します。

このとき、数式のA列参照は$A2のように列だけを固定します。行番号を固定すると、選択範囲のすべての行が同じ行と比較されてしまい、正しく動きません。

条件付き書式はあとから削除・変更できます。書式のみを変えてもデータは残るため、確認が終わったら書式をクリアして元の状態に戻せます。


複数列を組み合わせて重複を判定する

「名前が同じ」だけでなく「名前と日付が両方同じなら重複」と判断したい場合は、COUNTIFS(複数条件版)を使います。

A列が名前、B列が申込日の場合:

=COUNTIFS($A$2:A2, A2, $B$2:B2, B2)

この数式をC列に入れると、名前と日付の両方が一致した件数を行ごとに数えられます。どちらか一方が違えばカウントは増えません。

架空の例で確認します。

表は横にスクロールできます

行A列(名前)B列(申込日)C列(COUNTIFS結果)
2山田太郎10/11
3山田太郎10/51
4山田太郎10/12
5鈴木花子10/11

3行目は名前が同じでも日付が違うため1のまま、4行目は名前も日付も2行目と同じなので2になります。


次の行動

作業前にファイルを別名でコピーし、変更前の状態を確保してください。まずB列またはC列に数式を1行入力し、意図したとおりに結果が出るかを3〜5行で確かめてから、全行へコピーします。印が付いた行を削除・統合する前に、1件目として残す行が正しく特定できているかを目視で確認してください。個人情報を含む名簿を外部サービスや生成AIに貼り付けて確認する場合は、所属先のルールを先に確認してください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ