Excelで番号、メールアドレス、管理IDなどを入力していると、同じ値を二重に登録してしまうことがあります。後から重複を探して消す方法もありますが、入力時点で止めたい表では、データの入力規則にCOUNTIFを組み合わせると扱いやすくなります。
この記事では、Microsoft 365のWindowsデスクトップ版Excelを前提に、1列の範囲で重複入力を防ぐ設定方法を説明します。Web版やMac版でもデータの入力規則は使えますが、画面の表示やメニュー名が異なる場合があります。
重複削除ではなく入力前に止める
Excelには重複する値を削除する機能や、重複を目立たせる条件付き書式があります。すでに入っている一覧を整理するなら便利ですが、入力済みの行を消す操作は元データに影響します。削除前には必ずファイルを保存し、必要ならシートを複製してから確認します。
一方、これから入力する値を重複させたくない場合は、データの入力規則を使います。入力規則は、セルに入力できる値や種類を制限し、条件に合わない値を入力したときにエラーメッセージを表示できます。候補から選ばせる設定は入力規則のプルダウンで入力ミスを減らす方法と相性がよく、自由入力の列では今回のような数式による判定が役立ちます。
COUNTIFで同じ値の数を調べる
重複判定にはCOUNTIF関数を使います。COUNTIFは、指定した範囲の中で条件に合うセルの数を数える関数です。たとえばA2からA100までの管理番号欄で、A2と同じ値が範囲内にいくつあるかを調べるなら、考え方は次のようになります。
=COUNTIF($A$2:$A$100,A2)
同じ値が1個なら、その値は範囲内で一度だけ使われています。2個以上なら、同じ値がすでに存在します。入力規則では「入力してよい条件」を数式で指定するため、重複を防ぐには1以下であることを条件にします。
=COUNTIF($A$2:$A$100,A2)<=1
範囲の先頭と末尾にはドル記号を付け、判定対象のセルだけをA2のように相対参照にします。これにより、A3ではA3、A4ではA4を見ながら、同じ固定範囲の中で重複数を数えられます。参照のずれが分かりにくい場合は、絶対参照と複合参照の使い分けも合わせて確認すると整理しやすくなります。
データの入力規則に数式を設定する
まず、重複を防ぎたい範囲を選択します。ここではA2からA100を対象にします。既存データがある場合は、先に重複がないか確認してから設定します。すでに同じ値が入っている範囲へ入力規則を設定しても、過去の値が自動で修正されるわけではありません。
- A2:A100を選択する
- 「データ」タブを開く
- 「データの入力規則」を選ぶ
- 「設定」タブの「入力値の種類」で「ユーザー設定」を選ぶ
- 「数式」に「=COUNTIF($A$2:$A$100,A2)<=1」と入力する
- 必要に応じて「エラー メッセージ」タブで案内文を設定する
- 「OK」を選ぶ
数式は、選択範囲の左上セルを基準にして書きます。A2:A100を選んでいるならA2、C2:C100を選んでいるならC2を使います。途中のセルだけを基準にすると、他の行で判定がずれることがあります。
エラーメッセージで直し方を伝える
重複を止めるだけでは、入力者が何を直せばよいか分かりにくい場合があります。エラーメッセージには、原因と次の行動を短く書きます。たとえばタイトルを「重複した値です」、本文を「この管理番号はすでに使われています。別の番号を入力してください」のようにします。
Excelの入力規則では、エラーメッセージの種類を選べます。重複を必ず止めたい列では「停止」を選びます。「注意」や「情報」は、条件外の入力を続行できる場面があるため、重複禁止の列には向きません。メッセージの考え方を整えたい場合は、データ検証のエラーメッセージを整える方法も参考になります。
設定後に必ずテストする
入力規則を設定したら、対象範囲で動作を確認します。まず空いているセルに新しい値を入力し、問題なく受け付けられるか見ます。次に、すでに入力されている値と同じ値を別のセルに入れ、エラーメッセージが出るか確認します。
- 新しい値が入力できるか
- 同じ値を入れたときに止まるか
- 空白を許可するかどうかが目的に合っているか
- コピー貼り付けで入力規則が崩れていないか
- 行を追加したときに対象範囲へ含まれるか
データの入力規則は、セルに直接入力したときの制限として使う機能です。コピーやオートフィルなどの操作では、想定どおりにメッセージが出ない場合があります。配布用の表では、入力範囲をテーブルにする、保護するセルと入力するセルを分けるなど、運用上の確認も必要です。行が増える表では、テーブルと参照で表を扱いやすくする方法の考え方を使うと範囲管理がしやすくなります。
既存データの重複は別に確認する
入力規則は、これから入れる値を制限するための設定です。すでに重複しているデータを見つけるには、条件付き書式、フィルター、COUNTIFの補助列などを使って確認します。重複する行を削除する場合は、残す行の判断が必要になるため、元ファイルを保存してから作業します。
重複を見つけた後は、どの値を正とするかを決めます。番号の打ち間違いなのか、同じ人や同じ案件を二重登録したのかで対応が変わります。入力規則は再発防止には向いていますが、既存データの意味までは判断できません。
まとめ
Excelで重複入力を防ぐには、データの入力規則で「ユーザー設定」を選び、COUNTIFを使った数式を設定します。A2:A100なら「=COUNTIF($A$2:$A$100,A2)<=1」のように、固定した範囲の中で同じ値が1個以下かを判定します。
設定後は、新しい値、重複する値、空白、コピー貼り付け、行追加の動きを確認します。既存データを削除する作業とは目的が違うため、入力前に止める仕組みと、過去データを整理する作業を分けて考えることが大切です。