ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.009

《入力規則》 重複データが入力できないよう、メッセージを表示し規制する

2003/2007/2010/2013

データ量が多くなってくると、以前入力したデータがまた入力されてダブってしまった、などといったことがあります。
毎回データに重複がないかを調べつつ入力するのは面倒ですよね。
そこで、あらかじめ入力の際に重複データは入力できないように規制しようというものです。

今回は、セルB2以下のセルを対象に、重複データが入力できないように規則を設定します。
そしてなぜ入力ができないのかわかるよう、独自のメッセージを表示させようと思います。

  1. 重複データを規制するセルB2以下ををあらかじめ選択しておきます。
  2. [データ]タブ-[データ ツール]グループ-[データの入力規則]をクリックします。
    データの入力規則
  3. [設定]タブを開き、[入力値の種類]を[ユーザー設定]にします。
  4. 今回数式の答えを条件とします。[数式]ボックスに次の式を入力します。
      =COUNTIF($B$2:B2,B2)<=1
    COUNTIF関数の答えが1以下であるよう条件設定しました。詳しくは後述です。
    入力規則
  5. 特定のエラーメッセージを表示する場合は[エラー メッセージ]タブを開いてメッセージを指定します。
    入力規則エラーメッセージ
  6. [OK]ボタンをクリックします。

上記設定で検証すると次のようになります。

入力規則エラーメッセージ

上記手順5の[エラー メッセージ]タブで何も設定しなかった場合は、次のような既定のメッセージが表示されます。

入力規則規定のエラーメッセージ

では、[数式]ボックスに設定した式を解説します。
設定したCOUNTIF関数の式は、セルB2ばかり出てきます。
COUNTIF関数の最初の引数[範囲]の最初のセルB2のみ絶対番地の指定であるところがポイントです。
セルB2より下のセルでは、だんだん範囲が伸びていき、セルB2を起点とした範囲の中で、範囲の一番下のセルが何個あるのかを計算することになります。
つまり、B列のそのデータがセルB2からの範囲の中で何個あるかです。

COUNTIF関数の答えが1より大きい場合、重複データであることを意味します。
したがって入力規則では1以下と設定しました。
もちろん2未満、「=COUNTIF($B$2:B2,B2)<2」と設定してもOKです。

最後に注意点です。
もうすでに入力済みであるデータに今回の入力規則を設定しても、重複データは検出できません。今から入力するデータに有効な機能です。
また、入力規則が設定してあるセルにデータをコピーした場合、セルの内容が上書きされるため、設定した入力規則は消えてしまいます。その場合も、重複データは検出できません。

twitter hatena line pocket

関連ヘルプ

《保護》 一部のセルだけ変更可能にし、他のセルは変更できないように保護したい(変更不可セルが大部分)
《保護》 一部のセルを変更できないように保護したい(大部分のセルが変更可能)
《入力規則》 セルに日本語入力のオン/オフや全角/半角などを設定する
《保護》 数式バーにセルの数式や内容が表示されないようにしたい
《入力規則》 セルに▼が表示され、ドロップダウン リストから入力できるようにするには
《入力規則》 入力規則のリスト入力で、別シートのリスト項目を表示するには
《名前》 特定のセル範囲に名前を定義する
《名前》 定義された名前を編集、削除するには
《条件付き書式》 土日の列または行に色をつける
《入力規則》 入力規則でリストの設定を行なったが、リストが表示されない
《保護》 非表示にしているシートを再表示できないようにする
《条件付き書式》 1904年オプション使用せずに、負の時刻を計算に使用するには