【Excel】集計に邪魔な入力ルールを無視したデータを洗い出したい！エクセルで無効なデータを一気に探すテク

ケース1：独自のエラーメッセージでスタイルを「注意」や「情報」に設定している

　Excelでは、セルに入力ルールを設定して、ルールに反するデータが入力された時に独自のエラーメッセージを表示するように設定できましたね。この時に、エラーのスタイルを「注意」や「情報」にしていると、無効なデータの入力を許可してしまいます。どういうことなのか、実際の例を使って見てみましょう。

　次の登山同好会の新規登録フォームのセルB10には、全角20文字（半角40文字）以内で入力するというルールが設定されているものとします（詳しい手順は、以前の記事の「テクニック2」を参考にしてください）。設定されているルールを確認してみましょう。［データ］タブ（①）→［データの入力規則］（②）をクリックすると［データの入力規則］ダイアログボックスが表示され、入力ルール（③）を確認できます。ここでは、「全角20文字（半角40文字）以内で入力する」と設定されています。

　［エラーメッセージ］タブ（④）では、表示されるエラーメッセージのスタイル（⑤）や内容（⑥）を設定できます。この例では、メッセージのスタイルは［停止］（⑦）が設定されているものとします。

　シートに戻り、実際にセルB10に20文字を超える文字列を入力してみましょう（⑧）。［Enter］キーを押して内容を確定すると、エラーメッセージが表示されますね（⑨）。

　ここで、［文字数制限］ダイアログボックスの［再試行］（⑩）をクリックすると、文字列を再入力するように促され、［キャンセル］（⑪）をクリックすると、セルの内容は削除されます。

　このように、エラーメッセージのスタイルを「停止」にしていると、ルールを無視したデータは入力し直さない限り、内容を確定することはできません。

　一方、エラーメッセージのスタイルに「注意」や「情報」を指定していると、ルールに反したデータでも入力を許してしまいます。実際にやってみましょう。

　再び［データ］タブ（⑫）→［データの入力規則］（⑬）をクリックして［データの入力規則］ダイアログボックスを表示し、［スタイル］を［注意］（⑭）に変更します。

　［OK］（⑮）をクリックしてシートに戻ります。

　セルB10に20文字を超える文字列を入力すると（⑯）、エラーメッセージが表示されますが、「続けますか？」という問いが表示されます。［はい］（⑰）をクリックすると、そのまま入力内容は許可されてしまいます。

　20文字を超える文字列が入力できてしまいましたね（⑱）。

　「情報」スタイルの場合も同様に、ルールに反するデータの入力が可能になってしまいます。

　このように独自のエラーメッセージでエラーのスタイルに「注意」や「情報」を指定していると、せっかく入力ルールを設定していても、ルールを無視したデータが入力されてしまうことがあるのです。

ケース2：コピペした場合は入力ルールが適用されない

　もう1つのケースとして、データをコピペした場合（貼り付けオプションで［値］を指定してペーストした場合）は、入力ルールは適用されません。先ほどの例と同じ登山同好会の新規登録フォームを使ってやってみましょう。

　事前にセルA14に20文字を超える文字列が入力されているとして（①）、このデータを入力ルールが設定されているセルB10に貼り付けてみます。

　セルA14（②）を選択した状態で［Ctrl］＋［C］キーを押してコピーしたあと、セルB10（③）をクリックし、［Ctrl］＋［V］キーを押してデータを貼り付けます。データを貼り付けた時にセルの右側に表示される［貼り付けオプション］メニューの［値］（④）を選択します。

　すると、エラーメッセージが表示されることはなく、20文字を超える文字列が入力できてしまいますね（⑤）。

「無効データのマーク」でルールを無視したデータをチェックする

　ここまでの項では、入力ルールを無視したデータが存在してしまう理由について解説しました。このような無効なデータを1つ1つチェックするのは、手間がかかります。したがって、この項ではこのようなデータをすばやくチェックする方法を解説します。

　次の登山同好会の新規登録フォームには、入力ルールを無視したデータがいくつか入力されていますが、見た目ではわかりづらいので「無効データのマーク」機能を使って、チェックしてみましょう。

　［データ］タブ（①）→［データの入力規則］の右側にある矢印（②）をクリックして表示されるメニューから［無効データのマーク］（③）を選択します。

　無効なデータが入力されているセル（セルB7とセルB10）の上に赤色の楕円形のマーク（④）が表示されました。

　ちなみにセルB7に楕円形のマークが付いている理由は何でしょうか。このセルには血液型を入力するためのドロップダウンリスト（⑤）が設定されているのですが、どういうわけか「A型」という文字列が入力されており、ルールに反しています。したがって、マークされたというわけです。

　マークを消したい時は、［データ］タブ→［データの入力規則］の右側にある矢印をクリックして表示されるメニューから［入力規則マークのクリア］を選択すると、消えます。

入力ルールを無視したデータを一括でチェックできる

　今回は、入力ルールを設定したのに無効なデータが発生してしまう理由と、それらの無効なデータをチェックする方法を解説しました。

　物事には例外が発生するのが常です。入力ルールを設定した場合も、いろいろな理由により無効なデータが発生してしまうことがあります。機械的にチェックできるところはなるべくExcelに任せて、無駄な時間を費やさないようにしたいですね。ぜひ覚えておいてください。