EXCEL生産性向上委員会ちょっと便利テクニック１７

02/10/2002 -
１７．入力データの重複をリアルタイムに検知する
・	シート上の表にデータを連続して入力していくときに、同じデータを複数入力してしまったことを把握したいということがあると思います。いろいろなやり方があるかと思いますが、入力中に「リアルタイム」でデータの重複を検知する１つの方法を紹介します。いろいろ応用できるテクニックですので、ぜひ活用してみてください。
	8-3、8-5、16のテクニック紹介で使用した「条件付書式」をここでも用います。

・	例として、右のような表にデータを入力していくケースで、商品番号・区分・数量（A・B・C列）の３項目について重複を検知する設定する場合で説明します。表は、100行まであるものと仮定します。

・	F列を重複検出用のワークエリアとして使用します。（見た目を気にする場合は、F列を非表示にすればよいでしょう。特にF列である必要は無いので、別の列を使う場合は条件付き書式を設定する時に$Fの部分を別の列にしてください）下図の様に、F2に =A2&B2&C2 という式を入れます。これで、商品番号・区分・数量が１つに連結された内容がF2に入ります。 F2をコピーしてF3～F100までに貼り付けておきます。


	次に、A2からE100までを選択した状態で、「書式(O)」「条件付き書式(D)」から条件付書式を設定します。条件には「数式が」を選択し、　=COUNTIF($F$2:$F$100,"="&$F2)>1　という式を入力して、「書式(F)」で検知した場合の書式を設定します。下の図の例では、赤字の太字に設定しています。
	式の意味としては、　『 F2からF100の範囲で、F2と同じ値のセルの数が１より大きい（２つ以上、つまり重複している）』　という条件になります。 A2からE100まで全てのセルについて、F2からF100の範囲での判定を行うので、$F$2:$F$100 というように行も列も絶対参照にします。 "="&$F2 の部分は、=F2 と書きたいところですが、比較演算子は"="とダブルクォーテーションで囲み、セルの名前F2とは & でつなぎます。ただし、行ごとに判定条件が変化するように $F2 と列だけ絶対参照にします。（この$を付けないと、B列の条件付き書式はG列で、C列はH列で判定されてしまいます）



	以上で設定は終りです。例えば、下のように７行目のC列に50と入力して確定（Enterキーを押下）した瞬間に、F7の値とF3の値が等しくなるので、3行目と７行目の条件付き書式の条件が成立して、それぞれの行が赤字の太字になります。重複に気づいてデータを修正（例えばセルB7の”D9”を”D8”に）すると、修正した瞬間に3行目と7行目は普通の黒字に戻ります。


	【2003.4.20追加】重複データの入力を制限する上では入力データが重複したことをリアルタイムで検知する方法を紹介しましたが、検知するだけではなく重複データは入力させない（入力を制限する）ことも可能です。
・	上と同じように、商品番号・区分・数量（A・B・C列）の３項目について重複データの入力を制限する設定の場合で説明します。表は、100行まであるものと仮定します。やはりF列を重複検出用のワークエリアとして使用します。「データの入力規則」機能を使用します。（こちらも参考にして下さい） A2からC100までの範囲を選択して、「データの入力規則」を開きます（右図）。条件の設定は、「入力値の種類(A)」を「ユーザー設定」にして、「数式(F)」に =COUNTIF($F$2:$F$100,"="&$F2)=1 と入力します。 A列・B列・C列を連結した値が、F列の中で1つだけである（重複していると2になる）ことを入力の条件と設定するわけです。
	ついでに、「エラーメッセージ」タブでエラーの場合（入力データが重複した場合）のメッセージも設定しましょう。
	このように設定すると、重複したデータを入力した場合（右図では、7行目のC列に50と入力してEnterキーで確定した瞬間）に、エラーメッセージが表示されます。条件付き書式を設定しておけば、どこが重複しているかも一目瞭然です。なお、もし商品番号だけで重複を制限したい場合（そちらのケースの方が多いかもしれませんが）は、入力規則の数式(F)の設定を =COUNTIF(A:A,"="&A2)=1 とします。
	１つ注意していただきたいのが、データの入力規則を設定した場合でも、コピー・ペーストやCtrl+D、Ctrl+Rなどで値を貼り付けた場合は重複したデータを入力できてしまうということです。例えば、上の図でセルC3の（50という値）をコピーしてセルC7にペーストすると、条件付き書式の設定どおり赤字の太字にはなりますが、上のようにエラーメッセージは表示されません。結果的に3行目と7行目は重複してしまいます。また、全く別の場所（例えば別のシートとか別のファイルのセル）をコピーして条件付き書式を設定したA列B列C列のいずれかの場所に貼り付けると、せっかく設定した条件付きもデータの入力規則も（貼り付けた場所だけは）消えてしまいます。つまり、完璧にはデータの重複を排除できないということです（厳密にやりたい場合はVBAを使うことになるでしょう）。


	Web excel7.com