トップページ > Excel エクセル使いこなし技 > 異なるワークシートにある範囲を「入力規則」の「リスト」として利用する方法

異なるワークシートにある範囲を「入力規則」の「リスト」として利用する方法

異なるワークシートにある範囲を「入力規則」の「リスト」として利用する方法


「入力規則」を使うと、セルに入力されるべきデータの指定を簡単に出来ます。ただ、残念なことに「入力規則」で「リスト」を使う際に用いるデータの「リスト」は同じワークシートにあることが求められます。幸いなことに、このエクセルが設ける制限を逃れる方法がいくつか有るので紹介していきます。

方法1:範囲に「名前」を付ける

エクセルの「入力規則」の壁を克服するおそらく最も簡単でかつ速い方法は、「リスト」を含む範囲に「名前」を付けることです。範囲に「名前」を付けるには、「リスト」を含む範囲を選択し、数式バーの左端にある「名前ボックス」に「名前」を入力することです。(※「名前ボックス」:通常、選択しているセルの番号、例えば、「A1」とか「C6」が表示されています)では、例えば、「リスト」範囲に「照合用データリスト」という名前をつけたとしましょう。

「入力規則」のドロップダウン・リストを表示したいセルを選択し、「データ」>「入力規則」を選択します。そして、「入力値の種類」から「リスト」を選び、「元の値」に「=照合用データリスト」と入力し、「OK」をクリックします。これで完成です。

名前をつけた範囲を使うことにより、他のワークシートに「リスト」があったとしても、「入力規則」に利用できるようになりました。

方法2:INDIRECT関数を使う

INDIRECT関数を使うと、関数の引数にあるテキストが示すセル範囲を参照することが出来ます。これにより、例え、参照するセル範囲が他のワークシートであっても、同じワークシート上のセルへの参照と同じ様に使えます。この機能を使って、「入力規則」用のリストを参照することが出来ます。

例えば、「Sheet1」の「A1:A5」に「入力規則」用のデータリストがあるとしましょう。「Sheet1」と異なるワークシートを選択し、「データ」>「入力規則」を選択します。そして、「入力値の種類」から「リスト」を選び、「元の値」に「=INDIRECT(“Sheet1!A1:A5″)」と入力します。そして、「ドロップダウン リストから選択する」にチェックがされていることを確認し、「OK」をクリックします。これで、シート「Sheet1」にあるリストがドロップダウンリストに出てくるはずです。

もし、例えば、「Sheet 1」のようにリストを持つワークシートの名前に空白スペースが含まれている場合、INDIRECT関数は「=INDIRECT(“‘Sheet 1′!A1:A5″)」のように入力する必要があります。ここの違いは、最初のダブルクオテーションマーク「”」の後ろとエクスクラメーションマーク「!」の前に、「’」シングルクオテーションマークを入力して、シート名が「’」シングルクオテーションマークで囲まれているかどうかです。

2つの方法の長所と短所

範囲に「名前」を付ける方法とINDIRECT関数を使う方法はそれぞれ長所と短所があります。

「名前」を付ける方法の長所はシート名を変更しても「入力規則」のリストに影響がない、ということです。これは一方でINDIRECT関数の短所となります。つまり、ワークシート名に変更があると、INDIRECT関数内に設定された参照範囲は自動的に変更されないため、手で新しいワークシート名に変更を施す必要があります。

INDIRECT関数の長所は指定した範囲が常に同じであることです。もし、「名前」を付けた範囲の場合に行や列を削除してしまうと、「名前」を付けた範囲も削除に応じて変更されてしまいます。

コメントを残す

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

*

次の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/data-valid-list/trackback/