トップページ > Excel エクセル使いこなし技 > 「入力規則」の照合用データリストを可変にする方法

「入力規則」の照合用データリストを可変にする方法

「入力規則」の照合用データリストを可変にする方法


エクセルのデータ「入力規則」は非常に素晴らしい機能なのですが、(ちょっとしたトリックを使わないと)照合に使うリストに追加でデータを足しても、自動的に照合用データに追加反映されず、元のままです。
Parajumpers Truman
Parajumpers UGO
「入力規則」を使ったことがある人にとっては、「入力規則」がなかなか良い機能であることをご存知でしょう。おそらく最も素晴らしい点は、ユーザーが選択したどのセルからもデータ照合用のリストを追加できることでしょう。でも、エクセルにデータを追加すれば、自動的に照合用データリストに追加されるようになれば、もっと素敵ではありませんか?これはこれから紹介する方法で実現できます。

従来の方法:まずは普通に「入力規則」の照合用データリストを設定

セルA1:A10に照合用データリストが入力されているとしましょう。これを照合用データリストに指定するには、「データ」>「入力規則」をクリックします。そして、「設定」タブで「入力値の種類」を「リスト」に変更し、「元の値」に「 =$A$1:$A$10 」と指定して、「OK」をクリックします。これで、セル範囲 A1:A10 の値から入力値を選択でき、それ以外の値を受け付けなくなります。

ステップ1:次に「名前」を使って、「入力規則」の照合用データリストを設定

次に、「名前」を使って、同じ設定を行ってみます。

まずは、「名前」の定義からです。「挿入」>「名前」>「定義」をクリックします。「名前の定義」ボックスで、「名前」に「照合用データ」、「参照範囲」に「 =$A$1:$A$10 」と入力し、「追加」「OK」をクリックします。

そして、次は「入力規則」のリストで先ほど設定した「名前」を付けた範囲を参照するように設定します。

「データ」>「入力規則」をクリックします。そして、「設定」タブで「入力値の種類」を「リスト」に変更し、「元の値」に「 =照合用データ 」と指定して、「OK」をクリックします。これで、「照合用データ」という名前が付けられたセル範囲 A1:A10 の値から入力値を選択でき、それ以外の値を受け付けなくなりました。

「入力規則」の照合用データリストを可変にする方法

ステップ2:最後に「名前」で参照する範囲を可変にする

ステップ1では、「名前」を付けた範囲「照合用データ」の「参照範囲」を「 =$A$1:$A$10 」としていましたが、ここを下の数式に変更すると、参照範囲をデータが追加や削除されても対応できるようになります。

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

これで、「入力規則」の「設定」タブで「元の値」が「 =照合用データ 」を指定していれば、A列に入力されたデータがリストに表示されます。そして、データが追加されても、削除されても、自動的にリストが対応してくれます。

「入力規則」の照合用データリストを可変にする方法

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

トラックバックURL

http://excel.jiten.org/tips/variable-validation-list/trackback/