サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
入力規則（プルダウン）が効かない・増殖するときの直し方
記事IT・テクノロジー
同じ列のはずなのに、一部のセルだけプルダウンが出ない。コピペしたら
規則ごと消えて自由入力になってしまった。選択肢の元データに項目を
増やしたのに、プルダウンには反映されない――入力規則（データの
入力規則／プルダウンリスト）は、見た目には気づきにくい形で
壊れることが多い機能です。原因別に整理します。
■ 症状1：範囲を指定したのに、一部のセルだけ効かない
入力規則は「セル1つずつに設定が紐づく」仕組みです。範囲を選択して
一括設定したつもりでも、あとからその範囲の一部だけを別の作業
（行の挿入、他のシートからのコピペ、セルの削除→再入力）で触ると、
そのセルだけ設定が外れる、または別の設定に上書きされることがあります。
疑わしいセルを1つ選び、Excelなら「データ」タブの「データの入力規則」、
スプレッドシートなら「データ」→「データの入力規則」を開いて、
他の正常なセルと条件が同じか見比べてください。範囲がずれている、
リストの参照先が違う、といった差分が見つかれば、そこが原因です。
■ 症状2：コピペで規則ごと上書きされる
セルのコピー＆ペーストは、値だけでなく書式・入力規則も一緒に
コピーします。規則が設定されていないセルを、規則が設定されている
セルの上にコピペすると、貼り付けた側の「規則なし」がそのまま
上書きされ、プルダウンが消えます。逆に、別の選択肢を持つ入力規則が
設定されたセルをコピペすると、意図しない選択肢のプルダウンに
置き換わります。
これを避けるには、値だけを動かしたい場面では「値のみ貼り付け」
（Excelなら貼り付けオプションの「値」、スプレッドシートなら
「特殊貼り付け」→「値のみ貼り付け」）を使う習慣が有効です。
すでに規則が壊れてしまった範囲は、正常なセルを1つコピーし、
「特殊貼り付け」→「入力規則のみ」（Excelは「入力規則」を選んで
貼り付け）で規則だけを直したい範囲に再適用すると、値を壊さずに
規則だけ直せます。
■ 症状3：選択肢の元データを増やしても、プルダウンに反映されない
=データの入力規則 → リストの範囲を「マスタ!A2:A20」のように指定
このように固定範囲でリストの参照先を指定していると、
マスタのA21以降に項目を追加しても、プルダウンの選択肢には
反映されません。範囲の外に追加しているため、規則そのものが
その存在を知らない状態になっています。
対処は2つあります。1つは、あらかじめ余裕を持った範囲
（A2:A100など）を指定しておくこと。もう1つは、マスタの範囲を
テーブルとして定義する、または名前付き範囲にして「常に最終行まで
自動的に含む」設定にしておくことです。スプレッドシートでは
`=マスタ!A2:A` のように列全体（2行目以降）を参照先に指定すれば、
追加した項目も自動的に選択肢へ反映されます。Excelでテーブル化
（Ctrl+T）した範囲を参照元にした場合も同様に、行の追加が
自動的に反映されます。
■ 症状4：INDIRECTを使った連動プルダウンが、片方だけ効かなくなる
1つ目のプルダウンで選んだ値によって、2つ目のプルダウンの選択肢が
変わる「連動プルダウン」は、多くの場合INDIRECT関数と名前付き範囲を
組み合わせて作られています。
=データの入力規則 → リストの範囲を =INDIRECT(A2) と指定
この仕組みは、A2セルの文字列（例："野菜"）と、名前付き範囲の
名前（例："野菜"という名前で定義された範囲）が完全に一致している
ことが前提です。ずれると、2つ目のプルダウンは何も選択肢を
出さない、または前の選択肢のまま反応しなくなります。
ずれやすい原因は主に3つです。
・名前付き範囲を作成するとき、名前にスペースや記号（"/" や "-" など）
が含まれていると、Excelやスプレッドシートは自動的にアンダースコアなど
別の文字に置き換えて登録することがあります。その結果、
プルダウンの選択肢の文字列（"野菜/果物"）と、実際に登録された
名前（"野菜_果物"）が一致しなくなります。
・1つ目のプルダウンの選択肢を後から変更・追加したのに、対応する
名前付き範囲を新しく作り忘れている。選択肢は増えても、
その名前に対応する参照先が存在しないため、2つ目のプルダウンが
空になります。
・選択肢の先頭・末尾に見えない半角スペースが入っている。
見た目は同じ文字列でも、INDIRECTは完全一致で名前を探すため、
これだけで連動が切れます。
疑わしい場合は、名前付き範囲の一覧（Excelなら「数式」タブの
「名前の管理」、スプレッドシートなら「データ」→「名前付き範囲」）
を開き、1つ目のプルダウンの選択肢の文字列と、登録されている
名前とを1つずつ突き合わせて確認してください。
■ 症状5：行を挿入したら、途中から規則が効かなくなる
入力規則を範囲指定（例：A2:A50）で設定したあと、その範囲の外側
（51行目より下）に新しい行を挿入した場合、新しく増えた行には
元の規則が及びません。範囲の内側に行を挿入した場合は多くのケースで
規則も一緒についてきますが、挿入の仕方（行ごと挿入か、セルの
コピペで行を増やしたか）によって挙動が変わることがあり、
確実ではありません。
行が増減する前提の表であれば、症状3と同じく、規則を設定する範囲を
最初から広めに取っておくか、テーブル化した範囲に対して規則を
設定しておくと、行の増減に対して規則が追従しやすくなります。
■ 症状6：エラーメッセージが出ずに、規則を無視した値が入る
入力規則には「無効なデータの場合」の設定があり、Excelでは
「停止」「注意」「情報」の3段階、スプレッドシートでは
「拒否する」「警告を表示」の2段階から選べます。既定値以外
（「注意」「情報」「警告を表示」）が選ばれていると、規則から外れた
値でも警告が出るだけで入力自体は通ってしまいます。一見プルダウン
が機能しているように見えて、実際には自由入力を許してしまっている
状態です。厳密に選択肢だけに絞りたい場合は、Excelなら「停止」、
スプレッドシートなら「拒否する」に設定を変更してください。
すでに規則外の値が紛れ込んでいる列は、規則を設定し直しても
既存の値までは自動的にチェックされないため、別途フィルタや
COUNTIF等で規則外の値が残っていないか確認しておくと安全です。
■ やってはいけないこと
プルダウンが効かないセルを見つけるたびに、その場で規則を
作り直すこと。その場しのぎで直したセルは、他の正常なセルと
参照範囲や条件が微妙に異なる「もう1つの規則」になりがちで、
これが症状1の「一部のセルだけ違う挙動をする」の原因を
新たに増やしてしまいます。直すときは、正常なセルの規則を
コピーして適用する形に統一してください。
■ それでも解決しないとき
・規則を作り直しても、特定のセルだけ元に戻ってしまう
・連動プルダウンのどこで名前がずれているか、自分では追えない
・複数人で編集していて、いつからおかしくなったか分からない
このあたりまで来たら、規則と名前付き範囲を1つずつ突き合わせて
確認した方が早いです。
私はスプレッドシートの入力規則・数式まわりの調査と修正を承っています。
どこで規則がずれていたかを明らかにしてお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
