Excelでデータの入力規則を設定していると、
「100以下しか入力できないはずなのに、200をコピペしたら入ってしまった…」
「入力規則を設定したのに、貼り付けるとチェックされない…」
「コピペしたあと、入力規則そのものが消えてしまった💦」
ということはありませんか。
結論からいうと、Excelのデータの入力規則は、コピーやオートフィルによる入力では無効な値を完全には防げません。
Microsoftも、入力規則はセルへ直接入力した場合に無効な値を防ぐ仕組みであり、コピーやオートフィルなどでは検証メッセージが表示されず、無効なデータが入る場合があると案内しています。
ただし、Excelには入力規則に違反したセルを赤い円で見つける機能があります。
この記事では、入力規則がコピー・貼り付けですり抜ける原因と、すでに入ってしまった無効なデータを見つける方法、入力ミスを減らすための対策を解説します。
Excelの入力規則がコピー貼り付けで無視される理由
まず、実際にどのような現象が起きるのか確認してみましょう。
たとえば、A9:A13へ次の入力規則を設定したとします。
・入力値の種類：整数
・条件：次の値以下
・最大値：100
・エラーメッセージ：停止
この状態でセルへ直接、「200」と入力すると、入力規則に違反しているためエラーメッセージが表示されます。
ところが、別のセルに入力されている200をコピーして貼り付けると、エラーが表示されず、そのまま入力される場合があります。
Microsoft公式でも、データの入力規則は直接入力したときに無効な値を防止する仕組みであり、コピーやオートフィルでは検証メッセージが表示されない場合があると説明されています。
そのため、「入力規則を設定している＝どんな方法でも無効な値を入力できない」というわけではありません。
Excelの不具合とは限らない
入力規則を設定したのに無効な値が入ると、
「入力規則が壊れたのでは？」
と思うかもしれません。
しかし、コピーやオートフィルによって値が入力された場合にチェックをすり抜けるのは、Excelの入力規則の仕組みによるものです。
数式の結果やマクロなどによって、入力規則に違反する値が入る場合もあります。
コピーすると入力規則そのものが消える場合もある
ここで注意したいのが、「入力規則は残っているが、無効な値が入った」ケースと、「入力規則そのものが貼り付けによって変わった」ケースは別だという点です。
Excelでは通常のコピー・貼り付けをすると、値だけでなく、書式や数式、入力規則などのセル属性も貼り付けられます。
そのため、入力規則が設定されているセルへ別のセルを通常貼り付けすると、コピー元の状態によっては貼り付け先の入力規則が置き換わる場合があります。
つまり、次の2つを分けて考える必要があります。
| 症状 | 確認すること |
|---|---|
| 入力規則は残っているのに200などが入る | コピーによる入力規則のすり抜け |
| 貼り付け後に入力規則自体が変わった | 通常貼り付けによるセル属性の上書き |
入力規則を残したい場合は、通常の貼り付けではなく「値のみ貼り付け」を使う方法があります。
👉入力規則が設定されているセルをまとめて確認したい場合は、Ctrl＋Gの［ジャンプ］機能を使う方法もあります。詳しい手順はこちらの記事で解説しています。
入力規則を残すなら「値のみ貼り付け」を使う
コピー元の入力規則や書式を持ち込まず、値だけを貼り付けたい場合は、［値］として貼り付けます。
Excelでは［形式を選択して貼り付け］から、値だけを貼り付けられます。
値だけ貼り付ける場合は、Ctrl＋Alt＋Vで［形式を選択して貼り付け］を開き、［値］を選択する方法があります。
値のみ貼り付けでも無効な値は入る場合がある
ここには重要な注意点があります。
値のみ貼り付けを使えば、入力規則を完全に守れるという意味ではありません。
値のみ貼り付けでは、コピー元の入力規則を貼り付けないため、貼り付け先に設定されている入力規則を残せます。
一方で、コピーによって値を入力していることには変わらないため、入力規則に違反する値が入る場合があります。
つまり、「入力規則を残すこと」と、「無効な値を防ぐこと」は別問題です。
ここを混同しないことが重要です。
👉値だけの貼り付けなど、コピー・貼り付けのショートカットをまとめて確認したい方はこちらも参考にしてください。
無効なデータを円で囲んで見つける方法
すでにコピーやオートフィルで無効なデータが入ってしまった場合は、Excelの［無効なデータを円で囲む］を使うと見つけやすくなります。
Microsoft公式でも、入力規則を満たしていないセルを赤い円で表示して確認する方法が案内されています。
操作手順は次のとおりです。
■無効なデータを円で囲む手順
1. 確認したいセル範囲を選択する
↓
2. ［データ］タブを開く
↓
3. ［データの入力規則］の横にある矢印をクリックする
↓
4. ［無効データのマーク］をクリックする
入力規則に違反しているセルが赤い円で囲まれます。
たとえば、「100以下」という入力規則を設定した範囲に200が入っていれば、そのセルを簡単に見つけられます。
入力規則を設定した表を複数人で使っている場合は、定期的にこの機能でチェックしておくと入力ミスを発見しやすくなります。
赤い円を消す方法
赤い円で囲まれたセルへ入力規則を満たす値を入力すると、そのセルの円は消えます。
円の表示だけをまとめて消したい場合は、以下の手順で選択します。
■円の表示だけをまとめて消す方法
1. ［データ］タブを開く
↓
2. ［データの入力規則］の横にある矢印
↓
3. ［入力規則マークのクリア］
を選択します。
ただし、円を消しただけでは無効なデータそのものは修正されません。
入力規則に違反した値が残っていないか確認してから円を消しましょう。
コピー貼り付けによる入力ミスを減らす方法
入力規則だけでコピー・貼り付けによる無効値を完全に防ぐのは難しいため、実務では「入力を制限する」だけでなく「あとから確認できるようにする」ことが重要です。
対策としては、次の方法があります。
・入力規則が設定されたセルへは値のみ貼り付けを使う
・［無効データのマーク］で定期的にチェックする
・重要なシートでは保護機能も併用する
・必要に応じてフィルハンドルやドラッグによるコピーを制限する
・重要なデータでは入力規則だけに依存せず、確認用の仕組みも用意する
Microsoftでは、ドラッグ＆ドロップによるコピーや入力を抑えたい場合、以下の設定も案内されています。
■ドラッグ&ドロップによるコピーや入力を抑えたい場合の方法
1. ［ファイル］タブから［オプション］を選択
↓
2. ［詳細設定］から［フィル ハンドルとセルのドラッグ アンド ドロップを有効にする］をオフにする
これでセルをドラッグして移動したり、オートフィルをかけることができなくなります。
ただし、この設定はCtrl＋Vによる貼り付けを完全に禁止する設定ではありません。
「入力規則を設定したから絶対に間違ったデータは入らない」と考えるのではなく、入力後のチェックも組み合わせる方が安全です。
👉入力規則だけでなく、数式や重要なセルそのものを誤操作から守りたい場合は、シート保護を使う方法もあります。
［無効データのマーク］が表示されない場合
［無効データのマーク］が見つからない場合は、使っているExcelの環境を確認してください。
この機能はデスクトップ版Excelで利用します。
Excel for the webなど、利用環境によっては同じ項目が表示されない場合があります。
ブラウザ版Excelを使っていて項目が見つからない場合は、デスクトップ版Excelでファイルを開いて確認してみてください。
まとめ｜入力規則だけではコピー貼り付けを完全には防げない
Excelのデータの入力規則は、セルへ直接入力するときのミス防止には便利ですが、コピーやオートフィルによる無効なデータを完全に防ぐ機能ではありません。
通常の貼り付けでは、コピー元の入力規則まで持ち込まれ、貼り付け先の設定が変わる場合もあります。
入力規則を残したい場合は値のみ貼り付けを使い、すでに無効な値が入っていないか確認したい場合は、
［データ］→［データの入力規則］→［無効データのマーク］
を利用してみてください。
入力規則を設定して終わりにするのではなく、
「入力を制限する」＋「無効なデータをあとから確認する」
という2段構えにしておくと、実務での入力ミスを減らしやすくなります。
👉入力規則を使ったプルダウンの矢印が出ない・選択肢が空になる場合は、こちらの記事で確認ポイントを解説しています。
コメント