No.009
《入力規則》 重複データが入力できないよう、メッセージを表示し規制する
2003/2007/2010/2013
データ量が多くなってくると、以前入力したデータがまた入力されてダブってしまった、などといったことがあります。
毎回データに重複がないかを調べつつ入力するのは面倒ですよね。
そこで、あらかじめ入力の際に重複データは入力できないように規制しようというものです。
今回は、セルB2以下のセルを対象に、重複データが入力できないように規則を設定します。
そしてなぜ入力ができないのかわかるよう、独自のメッセージを表示させようと思います。
- 重複データを規制するセルB2以下ををあらかじめ選択しておきます。
- ［データ］タブ－［データ ツール］グループ－［データの入力規則］をクリックします。
- ［設定］タブを開き、［入力値の種類］を［ユーザー設定］にします。
- 今回数式の答えを条件とします。［数式］ボックスに次の式を入力します。
=COUNTIF($B$2:B2,B2)<=1
COUNTIF関数の答えが1以下であるよう条件設定しました。詳しくは後述です。
- 特定のエラーメッセージを表示する場合は［エラー メッセージ］タブを開いてメッセージを指定します。
- ［OK］ボタンをクリックします。
上記設定で検証すると次のようになります。
上記手順5の［エラー メッセージ］タブで何も設定しなかった場合は、次のような既定のメッセージが表示されます。
では、［数式］ボックスに設定した式を解説します。
設定したCOUNTIF関数の式は、セルB2ばかり出てきます。
COUNTIF関数の最初の引数［範囲］の最初のセルB2のみ絶対番地の指定であるところがポイントです。
セルB2より下のセルでは、だんだん範囲が伸びていき、セルB2を起点とした範囲の中で、範囲の一番下のセルが何個あるのかを計算することになります。
つまり、B列のそのデータがセルB2からの範囲の中で何個あるかです。
COUNTIF関数の答えが1より大きい場合、重複データであることを意味します。
したがって入力規則では1以下と設定しました。
もちろん2未満、「=COUNTIF($B$2:B2,B2)<2」と設定してもOKです。
最後に注意点です。
もうすでに入力済みであるデータに今回の入力規則を設定しても、重複データは検出できません。今から入力するデータに有効な機能です。
また、入力規則が設定してあるセルにデータをコピーした場合、セルの内容が上書きされるため、設定した入力規則は消えてしまいます。その場合も、重複データは検出できません。
