Excelでプルダウンを設定したはずなのに、いつの間にか選択肢が表示されなくなっていた。あるいは、プルダウンは残っているのに、選択肢にない値が入っていた。このような状態は、ほかのセルから値をコピー＆ペーストしたときに起こることがあります。
ややこしいのは、「入力規則そのものが上書きされた状態」と「入力規則は残っているが、不正な値が入った状態」は別の問題だということです。見分け方と直し方も異なります。
この記事では、ステータス列にプルダウンを設定した一覧表を使い、2つの状態を切り分けます。入力規則の復旧、「無効データのマーク」による確認、再発を減らす貼り付け方法まで順番に整理します。
通常の貼り付けでは、コピー元セルの内容や設定によって、貼り付け先の入力規則が上書きされることがあります。一方、「値のみ貼り付け」なら入力規則を残しやすいものの、選択肢にない値の混入までは防げません。
実務では「値のみ貼り付け」を基本にし、入力規則を対象範囲へ再適用したあと、「無効データのマーク」で不正な値を確認する流れが分かりやすいです。
入力規則が「消える」と「効かない」は別の状態
入力規則そのものが消えた状態
入力規則を設定していないセルを通常どおり貼り付けると、貼り付け先の値だけでなく、セルに付いている設定もコピー元の状態で上書きされることがあります。プルダウンの矢印が表示されなくなり、「データの入力規則」を開いてもリストの設定を確認できません。
この状態では、セルに判定基準となる入力規則がありません。そのため、後述する「無効データのマーク」を実行しても、入力規則が消えたセルの値はチェックできません。
入力規則は残っているが、不正な値が入った状態
入力規則のあるセルへ値だけを貼り付けた場合、プルダウンの設定が残っていても、入力時のエラーメッセージが表示されず、選択肢にない値が入ることがあります。
Microsoftのサポートでは、手入力だけでなく、コピー、フィル、数式、マクロなどで入った値も「無効データのマーク」で確認できると案内されています。こちらは入力規則が残っているため、ルールと現在値を照合できます。
今回使用する一覧表と確認環境
今回は、氏名、所属、ステータスを入力する簡単な確認表を使います。ステータス列には「未対応」「対応中」「完了」の3つを選べる入力規則を設定します。別の場所には、入力規則に含まれない「保留」という文字を用意します。
| OS | Windows 11 |
|---|---|
| Excel | Microsoft Office Home and Business 2021 |
1．ステータス列へ入力規則を設定する
まず、動作を確認するための入力規則を作ります。ステータスを入力するセル範囲を選択し、「データ」タブの「データの入力規則」を開きます。
「設定」タブの「入力値の種類」で「リスト」を選び、「元の値」へ次の3項目を入力します。
未対応,対応中,完了
「ドロップダウン リストから選択する」にチェックが入っていることも確認します。「エラーメッセージ」タブでは、スタイルを「停止」にしておくと、キーボードから選択肢以外の値を入力したときに処理を止められます。
設定後、セルの右側に表示される矢印から「対応中」などを選び、プルダウンが使えることを確認します。
2．通常のコピー＆ペーストで入力規則の変化を確認する
入力規則を設定していないセルへ「保留」と入力し、そのセルをコピーします。続いて、入力規則を設定したステータス列のセルを選び、Ctrl＋Vで通常どおり貼り付けます。
貼り付け後は、次の2点を確認します。
1つ目は、セルを選択したときにプルダウンの矢印が表示されるかどうかですが、結果は表示されませんでした。
2つ目は、「データの入力規則」を開き、設定したリストが残っているかどうかですが、結果は残っていませんでした。
3．値のみ貼り付けでも不正な値が入るか確認する
次は、ステータス列へ入力規則を設定し直してから、「保留」を値だけで貼り付けます。コピー元セルをコピーし、貼り付け先を右クリックして「形式を選択して貼り付け」から「値」を選びます。
値のみ貼り付けでは、貼り付け先の入力規則や書式を残したまま、値だけを移せます。ただし、コピー＆ペーストは手入力と同じようには検証されないため、選択肢にない値が入っていないか、貼り付け後の確認が必要です。
「値のみ貼り付け」は入力規則を残すための対策であり、不正な値の貼り付けを完全に防ぐ方法ではありません。貼り付け後の確認と組み合わせて使います。
4．消えた入力規則を復旧する
対象範囲へ入力規則を設定し直す
入力規則を設定する範囲が決まっている場合は、ステータス列の対象範囲をまとめて選択し、入力規則を再設定する方法が確実です。消えたセルを1つずつ探すより、想定している範囲へ同じルールを適用し直せます。
この記事の例では、ステータス列の入力欄をまとめて選び、「未対応」「対応中」「完了」のリストを再設定します。
正しいセルから入力規則だけをコピーする
同じ入力規則が残っているセルが近くにある場合は、そのセルをコピーし、「形式を選択して貼り付け」から「入力規則」を選ぶ方法もあります。値や書式を変えず、入力規則だけを貼り付けられます。
この操作で直るのはルールです。すでに「保留」などの不正な値が入っている場合、その値までは自動で修正されません。入力規則を戻したあと、次の手順で値も確認します。
5．「無効データのマーク」で不正な値を見つける
入力規則を設定し直したら、対象範囲を選択します。「データ」タブで「データの入力規則」の横にある矢印を開き、「無効データのマーク」を選択します。
入力規則に合わない値が入っているセルは、赤い円で囲まれます。今回の例では、リストに含まれない「保留」が確認対象です。
正しい値へ修正すると、そのセルの円は消えます。まとめて円を非表示にしたい場合は、同じメニューから「入力規則マークのクリア」を選びます。
「無効データのマーク」が確認できるのは、入力規則が残っているセル、または再設定したセルです。通常貼り付けによって入力規則そのものが消えたセルは判定できません。
先に対象範囲へ入力規則を再適用し、そのあとで不正な値を確認すると、ルールの消失と値の混入をまとめて直せます。
6．入力規則が消える・効かない状態を減らす方法
貼り付けは「値のみ」を基本にする
他システムや別のExcelファイルからデータを移す場合は、通常の貼り付けではなく「値のみ貼り付け」を基本にします。貼り付け先の入力規則、罫線、表示形式などを上書きしにくくなります。
ただし、値のみ貼り付けでも入力規則に合わない値は入り得ます。作業後に「無効データのマーク」を実行するところまでを、確認手順としてセットにします。
入力範囲全体へ同じルールを適用する
行を追加するたびに入力規則を個別設定すると、設定漏れが起こりやすくなります。入力予定の範囲をあらかじめ決めて、同じ入力規則をまとめて設定しておくと管理しやすくなります。
運用途中で入力規則が消れた可能性がある場合も、正常なセルだけを探すより、対象範囲へ同じルールを再適用する方が迷いません。
シート保護だけで完全に防げるとは考えない
シート保護は、見出しや数式など、編集させたくないセルを守る用途に向いています。ただし、入力を許可するセルはロックを解除する必要があります。Microsoftのサポートでも、入力規則を設定したセルへ入力させる場合は、そのセルをロック解除してからシートを保護するよう案内されています。
入力を許可したセルに対するすべての貼り付け操作を、入力規則とシート保護だけで完全に制御できるわけではありません。シート保護は数式やレイアウトの保護に使い、入力値の確認は別に行います。
厳密な制御が必要なら入力方法を見直す
貼り付け自体を制御したい場合は、VBAで変更を監視する方法もあります。ただし、マクロ有効ブックとして配布する必要があり、セキュリティ設定や保守も増えます。
まずはVBAなしで「値のみ貼り付け」「対象範囲への再設定」「無効データの確認」を運用し、それでも誤入力の影響が大きい場合に、入力フォームや別の仕組みを検討するのが現実的です。
入力規則を設定・変更できないときの確認点
「データの入力規則」がグレーアウトして選べない場合は、シートが保護されていないか確認します。保護中のシートでは、入力規則の設定を変更できません。
また、プルダウンの矢印だけが表示されない場合は、「データの入力規則」の「設定」タブで「セル内ドロップダウン」のチェックを確認します。ルールが残っているかどうかは、矢印だけで判断せず、設定画面を開いて確認します。
共同編集やほかの人へ配布するブックでは、入力範囲、貼り付け方法、確認担当を先に決めておくことも大切です。入力規則は入力ミスを減らす機能ですが、データ品質を自動で保証する機能ではありません。
まとめ
Excelの入力規則でコピー＆ペースト後の問題を調べるときは、「入力規則が消えた」のか、「入力規則は残っているが不正な値が入った」のかを最初に分けます。
入力規則が消えている場合は、対象範囲へルールを再適用します。ルールが残っている、または再適用したあとは、「無効データのマーク」で選択肢にない値を確認できます。
再発防止は「値のみ貼り付け」だけで終わらせず、貼り付け後の確認までセットにするのがポイントです。マクロを使わない運用でも、ルールの復旧と不正値の確認を分けると対応しやすくなります。
参考リンク
Microsoft サポート：セルにデータの入力規則を適用する
コメント