ここから本文です

【Excel】セルに設定した入力ルールを無視したデータが入力されている!エクセルで無効なデータを一気に探すテク

6/15(金) 6:55配信

Impress Watch

◆ 入力ルールをセルに設定したのにルールを無視したデータが!

 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に任せて、無駄な時間を費やさないようにしたいですね。ぜひ覚えておいてください。

窓の杜,トップスタジオ

最終更新:6/15(金) 6:55
Impress Watch