Excelの入力規則でリストを使って、コンボボックスから選択できるように設定する機能は便利ですね。
ドロップダウンリスト(プルダウンメニュー)は[入力規則]で作成 | Excel2003 | 初心者のためのOffice講座(Excel2003)
Excel2007も同じように設定できます。ボタンの位置が変わっただけで、ダイアログボックスでの設定方法は同じです。
「データの入力規則」ダイアログボックスが表示されたら、「設定」タブにある「入力値の種類」から「リスト」を選択します。
「元の値」のテキストボックス内をクリックします。
そして、リストに表示したい値を範囲選択します。テキストボックスに範囲が自動的に表示されます。「OK」ボタンをクリックします。
「リストシート」などとリストのみを別シートにしたい場合などは、名前定義で範囲に名前をつけて、その名前を設定するといいです。
セル範囲に名前を付ける方法については、以下のページで詳しく解説しています。
もし、このリストが可変する場合どうするか?
あるいは、
=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)
リストの一行目(セルA1)にタイトルがある場合は、数式を以下のように修正してください。
=$A$2:INDEX($A:$A,COUNTA($A:$A))
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)
INDEX関数やOFFSET関数については、ヘルプを参照してください。
INDEX 関数 - Office サポート
OFFSET 関数 - Office サポート
「データ」タブの「データの入力規則」ボタンをクリックして、「データの入力規則」ダイアログボックスを表示します。
「元の値」に名前を参照するように設定します。
「元の値」のテキストボックスにカーソルを置いて「F3」キーを押すと、「名前の貼り付け」ダイアログボックスが表示されるので便利です。
これで、「リストシート」にあるA列のデータを更新するだけで、データの数が可変しても参照範囲を更新する必要はありません。
参照する範囲が行も列も可変する場合の方法については、以下の記事を参照してください。
ドロップダウンリスト(プルダウンメニュー)は[入力規則]で作成 | Excel2003 | 初心者のためのOffice講座(Excel2003)
Excel2007も同じように設定できます。ボタンの位置が変わっただけで、ダイアログボックスでの設定方法は同じです。
データの入力規則
入力規則を設定したいセルを選択して、「データ」タブの「データツール」グループにある「データの入力規則」をクリックします。「元の値」のテキストボックス内をクリックします。
名前を付けた範囲を指定
また、ここに名前を付けた範囲を設定することもできます。「リストシート」などとリストのみを別シートにしたい場合などは、名前定義で範囲に名前をつけて、その名前を設定するといいです。
セル範囲に名前を付ける方法については、以下のページで詳しく解説しています。
名前定義の活用 | Excel2003 | 初心者のためのOffice講座
Excel2007までは、別シートの範囲を指定する場合は、名前定義を活用するしかなかったのですが、Excel2010からは別シートの参照範囲であっても設定できます。
データの入力規則(リストの参照範囲の設定) | Excel2010 | 初心者のためのOffice講座
Excel2007までは、別シートの範囲を指定する場合は、名前定義を活用するしかなかったのですが、Excel2010からは別シートの参照範囲であっても設定できます。
データの入力規則(リストの参照範囲の設定) | Excel2010 | 初心者のためのOffice講座
リストが可変する場合の設定
さて、ここからが本題です。もし、このリストが可変する場合どうするか?
あるいは、
=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)
リストの一行目(セルA1)にタイトルがある場合は、数式を以下のように修正してください。
=$A$2:INDEX($A:$A,COUNTA($A:$A))
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 1)
INDEX関数やOFFSET関数については、ヘルプを参照してください。
INDEX 関数 - Office サポート
OFFSET 関数 - Office サポート
「データ」タブの「データの入力規則」ボタンをクリックして、「データの入力規則」ダイアログボックスを表示します。
「元の値」に名前を参照するように設定します。
「元の値」のテキストボックスにカーソルを置いて「F3」キーを押すと、「名前の貼り付け」ダイアログボックスが表示されるので便利です。
これで、「リストシート」にあるA列のデータを更新するだけで、データの数が可変しても参照範囲を更新する必要はありません。
参照リストをテーブルに変換することでも自動で増減できます。
グラフのデータ範囲にOFFSET関数を使用すると、データが追加されてもグラフの修正は不要になります。
参照範囲はテーブルにするか、OFFSET関数を使用すると便利です。
グラフのデータ範囲を可変にして自動でグラフを拡張する2つの方法 | Excel 2019 | 初心者のためのOffice講座