エクセル重複データを見つけてメッセージを出す

エクセル重複データでメッセージ エクセル数式
スポンサーリンク

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

波乗りアヒル
波乗りアヒル

作業効率化にはユーザーフォームを使いますが、様々な計算などはエクセル関数が必須です。

エクセル関数を使うことができないと、VBAどころじゃありませんし、成績表も作る事ができないのです。

当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、エクセル関数を組み合わせてみたら動いた!という実用例・体験談を書いています。

Excelの専門学校通学や・資格があるわけではありませんので、エクセル関数を組み合わせてみたらエラーにならずに動いてくれているだけなので、掲載の数式などが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。

入力データが重複していないかを見つけて知らせる

エクセルで入力データ量が増えてくると、知らない間に同じデータを入力してしまったことありませんか?

ボウリングのリーグ戦での成績表をエクセル(EXCEL)で作っていて、様々なエクセル関数やExcel-VBAを使って参加者のスコアー入力をしています。

また、レーン抽選を行ってレーン番号と投球順を入力すると、自動的に成績記入用のレコードシートが印刷されるようにエクセルを作っているのですが、誤ってレーン番号と投球順が重複してしまったことがあります。

そうすると修正ペンで直したり、再度印刷し直したりして無駄なことしてました。

波乗りアヒル
波乗りアヒル

重複したら警告発してくれないかな?

COUNTIF関数とVBA使ってメッセージ出せますよ!

ここでは、「ボウリング成績表レーン抽選方法~LTBリーグ編」で実際に使っているデータを参考に書いていきます。

波乗りアヒル
波乗りアヒル

ボウリングのレーン抽選なので、エクセル入力で誤記入して重複したら困りますからね。

COUNTIFで重複データチェック

図1 サンプルなので一部のみ表示

  1. レーン抽選フォーム(ユーザーフォーム)を使って、レーン抽選をした参加者のレーン番号と投球順を入力します。
  2. AW列にレーン番号、AX列に投球順が転記されます。
  3. AP列にレーン番号と投球順の値を結合させた数字が入ります。
  4. このAP列の数字が同じ場合に重複と判断して、メッセージを出します。
  5. 重複が無ければ、レコードシート印刷して完成です。

これが、Excel-VBAとCOUNTIF関数を組み合わせたデータ重複チェックです。

詳しくは、「ボウリング成績表レーン抽選方法~LTBリーグ編」を参考にしてください。

参考

ボウリング成績表レーン抽選方法~LTBリーグ編
ボウリングのリーグ戦でのレーン抽選を実施後、参加選手欄にレーン番号と投球順を入力すると自動的にレコードシートが出来上がると、とても便利なので、レーン抽選フォームをエクセルで作ってみました。

COUNTIF関数を使って、重複したデータを見つけよう!

では、重複データをCOUNTIF関数を使って見つける方法です。

1、レーン抽選フォームからデータを転記

ボウリングレーン抽選Ver1

レーン抽選フォーム(ユーザーフォーム)より、参加選手名に応じて抽選したレーン番号と投球順をそれぞれ入力します。

ここで入力されたデータが(レーン番号AW列・投球順AX列)に転記されます。

また、同時にレーン番号と投球順を結合させた数字が割り当て(AP列)に記入されます。

エクセルデータ重複チェック

2、COUNTIF関数でデータ重複チェック

レーン番号と投球順の重複がないかチェックします。

最初の画面(図1)の通り、セルAZ3にはCOUNTIF関数を使って下記のように数式を書いています。

=COUNTIF($AP$3:$AP$42,AP3)

割り当て範囲AP3~AP42の中で、AP3(この場合11)が何個同じ値があるかどうかを調べています。

上の図では、1個しかないのでAZ3の値は1となっていて、同じようにAZ4・AZ5・AZ6も1個しかないので1となっています。

この状態では重複した数字がないということにしてあります。

エクセルデータ重複チェック

選手5を重複させてみました

次に選手5を誤って入力してしまった場合、AP列に同じ数字が2個発生しています。

すると、セルAZ4とAZ7の値が「2」になりました。

これは割り当て範囲(AP3:AP42)の中にAP4と同じ値が2個あります!という事です。

また、AZ7にも同じ数式を書いているので、同じく「2」になりました。

単純に同じ数字を数えて表示しているだけなので、3個あったら「3」になります。

3、重複があったらメッセージを発する

ここまでは、COUNTIF関数を使って同じ数字を数えただけですが、重複したら「2」以上の数字があったら、メッセージを発するようにExcel-VBAのメッセージ機能を使えるようにしましょう。

エクセル重複でメッセージ

メッセージを出すには、セルの値に応じてメッセージを出す!出さない!を設定しなくてはいけません。

その為にセルAZ1にCOUNTIF関数を使って、重複ある場合と無い場合を入れておきます。

=IF(COUNTIF(AZ3:AZ42,”>1″)>1,1,0)

この数式は、もしAZ3~AZ42の範囲で1よりも大きい数字が1よりも多い(2以上)だったら、「1」を表示、そうでなかったら「0」を表示としてあります。

重複データがなければ、すべて1となっているのでセーフ。重複があると「2」が出てくるのでアウト!って感じです。

そしてこのAZ1の値が「0」か「1」という数字を認識してExcel-VBAのメッセージ機能を使っています。

重複していなければメッセージは表示されず、重複した場合のみ警告としてメッセージが発せられるようにしています。

重複チェックでメッセージ

このメッセージ機能に関しては。「VBAメッセージボックスをセルの値で表示する方法」で詳しく書いています。

このExcel-VBAを使用しています

VBAメッセージボックスをセルの値で表示する方法
メッセージが文字だけじゃなくて、指定したセルの値と連動して変化してくれるとものすごく便利です。何週目の成績?など何がセルの値だと1週目、2週目と変動してメッセージを出してれると便利ですね。

スポンサーリンク


エクセルの条件付き書式で重複データを判定

この方法は、Excel-VBAもCOUNTIF関数も使わない、エクセルの標準機能「条件付き書式」を使うと、表に同じデータが重複して入力されているかどうかを確認する方法です。

この方法はとても簡単なのですが、実際の作業で毎回自分自身の目で確認が必要になり、見逃してしまう事や、重複チェックを忘れて印刷してしまう事が考えられるので、ボウリングリーグ戦成績表では使っていません。

1、データ重複チェックをしたいセルの範囲(AB3:AB12)を選択します。

エクセルデータ重複チェック

エクセルデータ重複チェック

2、選択したセルの範囲に条件付き書式を設定します。

条件付き書式で重複チェック

エクセル2016の画面です

この画面は「エクセル2016」の画面です。

3、【条件付き書式】をクリックすると、このような画面が表示されますので、【セルの協調表示ルール】➡【重複する値】へとマウスを合わせれば、表示されます。

条件付き書式重複チェック

マウスを合わせると表示されます

4、【重複する値】をクリックすると、重複する値の設定画面が表示されます。

エクセル重複チェック

書式でお好みの色に変更できます。

これで条件付き書式の設定が完了しました。

もし設定した範囲内に重複データがあったら、こんな感じで表示されますよ。

エクセルデータ重複チェック

条件付き書式設定後

繰り返しになりますが、条件付き書式は見るだけなので、見つけてもメッセージで重複データということを知らせてくれることもできないので、ご自身の目で確認が必要になりますよ!

条件付き書式をクリアする

条件付き書式は、あちこちのセルに設定できますのでどこに設定したか分からなくなってしまいます。

セルをクリックしても、ここに条件付き書式が設定されてますなんて知らせてくれません。

分かっていれば、その部分のみ条件クリアすればいいのですが、分からなくなったら、全部まとめてクリアすることができます。

条件付き書式クリア

条件付き書式のクリアは部分的か全体か選べます

一緒に使うと便利なエクセル関数

コメント