「データの入力規則」のデータが他の内容に入れ替わる

質問

たとえば10列ほぼ、「データの入力規則」で作成したデータを入れたセルがあるとします。
当該セルに、リストから選択しようとするが、他のデータ内容になってしまったり、
行または列ごと移動を行うと、セルが隣接したデータに入れ替わってしまっているものもあります。
なのでいちいち、修正しなくてはなりません。
この不思議な現象についてご存知でしょうか？正常に戻す方法も教えてください。

Excel2019利用です。

回答No.2

asciiz · ベストアンサー

＞ただ「範囲選択」ではなく、文字列「りんご,なし,ぶどう」と入力していたのです。
＞これだと相対的になるということですね。

え、それはちょっと不思議ですね…?
ただ即値指定の場合は、入力規則を設定するセルごとに毎回書かなければいけませんから、うっかり書き間違えたセルがあったのか、別のところからコピーしてしまったのか…そういった他の原因のように思います…。

----

＞ここから先がわかりません…。

例えば、こんな手順になります。

(1) C1に「りんご」、C2に「ぶどう」、C3に「なし」を入力します。
(2) C1:C3を範囲指定し、右クリックから「果物」という名前を付けます。
(3) A1セルに入力規則、リスト: 元の値「=果物」としました。
(4) A1セルをA2・A3セルにコピーします。

この時点で、A1・A2・A3、どのドロップダウンリストでも、「りんご・ぶどう・なし」が選択肢に出てきますね。
「=果物」で指定されている範囲が、違う行・列にコピーするのであっても動かないからです。
さらに続けます。

(5) C4セルに「いちじく」を追加します。
(6) 数式リボン→名前の管理をクリック、「果物」の定義を選択し、[編集(E)]ボタンを押します。
(7) 参照範囲(R)として、「=Sheet1!$C$1:$C$3」となっているものを「=Sheet1!$C$1:$C$4」に修正し、[OK]します。

そうすると、A1・A2・A3、どのドロップダウンリストでも、「りんご・ぶどう・なし・いちじく」が選択肢に出るようになります。
セル自体に設定されている入力規則をいじらずに、「選択肢」を増やせたわけです。

こういうことが言いたかったわけなのですが、実験できますでしょうか…

----

＞あと、リスト候補は、絶対参照先として、別のシートでもよいのでしょうか？
＞メインの入力をシートAとしたら、参照先をシートBにしたいのです。

はい、「名前の管理」を開いたときにわかったかもしれませんが、名前の参照先として「シート名!範囲」と書かれていますので、別シートにリストを用意することも可能です。

お礼

無事に成功しました！
ちょっと知るだけで各段便利になりますね！
Excelって奥が深いです。
今回もありがとうございました！

回答No.1

asciiz

リストの範囲として、$のつかない相対セル指定で入ってしまっていないでしょうか。

「元の値」の入力欄右に、セル範囲指定ボタンがありますが、そこをクリックして範囲指定→Enter、とすると、「=$E$1:$E$10」のように、絶対指定セルで入力されます。

しかしここで、「=E1:E10」などと手入力してしまうと、そのセルを一つ下にコピーしたときにリスト範囲もずれ、「=E2:E11」に変化してしまうのが原因だと思います。

ということで修正方法は、行・列ともに$付きの絶対位置指定にすること。

あるいは、2ステップになりますが、
(1)リスト部分を範囲選択→右クリック→名前の定義、より、「○○リスト」といった名前を付けます。
(2)入力規則の元の値として、「=○○リスト」と名前で指定します。

こうしておくことにより、リスト位置は自動的に絶対位置指定のものとなり、リスト範囲が変わる場合にも、一度入力規則を設定したセルを修正して回るのではなく、定義した名前の範囲を修正するだけで、その名前を使っているセル全てに反映できます。
(一度定義した名前は、「数式」リボンの「名前の管理」ボタンより確認・編集できます。)

お礼

追加補足です。

＞「=E1:E10」などと手入力してしまうと

たしかにこれが原因のようです。

ただ「範囲選択」ではなく、文字列「りんご,なし,ぶどう」と入力していたのです。

これだと相対的になるということですね。

あと、リスト候補は、絶対参照先として、別のシートでもよいのでしょうか？
メインの入力をシートAとしたら、参照先をシートBにしたいのです。

Excel初心者ですので、もう少し具体的に教えていただけると助かります。

補足

ご回答者様、またお世話になります！

りんご、ぶどう、なし
というリスト候補を作り、このエリアを絶対値にしてリストを作成しました。
名前の定義で果物としました。

ここから先がわかりません…。

＞リスト範囲が変わる場合に

たとえば「いちじく」を追加するとします。

＞一度入力規則を設定したセルを修正して回るのではなく

これが修正しないという意味なら「いちじく」は自動で追加されると思いますがされてない。

＞定義した名前の範囲を修正するだけで、その名前を使っているセル全てに反映できます。

いちじくの範囲を追加しなおす、ということでしたら、
数式の名前の引用に、定義した名前がありますが、新しい範囲を選択しても「置換する参照はありません」というエラーがでてきます。