410 410 範囲が可変する名前定義 | 初心者のためのOffice講座-SupportingBlog2 Yoshiko Hamamoto

範囲が可変する名前定義

Excelの入力規則でリストを使って、コンボボックスから選択できるように設定する機能は便利ですね。

ドロップダウンリスト(プルダウンメニュー)は[入力規則]で作成 | Excel2003 | 初心者のためのOffice講座(Excel2003)


Excel2007も同じように設定できます。ボタンの位置が変わっただけで、ダイアログボックスでの設定方法は同じです。

データの入力規則

入力規則を設定したいセルを選択して、「データ」タブの「データツール」グループにある「データの入力規則」をクリックします。


「データの入力規則」ダイアログボックスが表示されたら、「設定」タブにある「入力値の種類」から「リスト」を選択します。
「元の値」のテキストボックス内をクリックします。

そして、リストに表示したい値を範囲選択します。テキストボックスに範囲が自動的に表示されます。「OK」ボタンをクリックします。


名前を付けた範囲を指定

また、ここに名前を付けた範囲を設定することもできます。
「リストシート」などとリストのみを別シートにしたい場合などは、名前定義で範囲に名前をつけて、その名前を設定するといいです。

セル範囲に名前を付ける方法については、以下のページで詳しく解説しています。

名前定義の活用 | Excel2003 | 初心者のためのOffice講座



Excel2007までは、別シートの範囲を指定する場合は、名前定義を活用するしかなかったのですが、Excel2010からは別シートの参照範囲であっても設定できます。

データの入力規則(リストの参照範囲の設定) | Excel2010 | 初心者のためのOffice講座

リストが可変する場合の設定

さて、ここからが本題です。
もし、このリストが可変する場合どうするか?

担当者が減ったり増えたりすることってよくあります。毎回、参照範囲を設定し直すのは面倒ですね。

こういう場合は、名前管理で参照範囲の代わりに数式を入力しておくと便利です。
「数式」タブから「名前の定義」をクリックします。


「新しい名前」ウィンドウの名前のテキストボックスに任意の名前を入力します。
参照範囲に以下の数式を入力します。

シート名は入力する必要はありません。「OK」ボタンを押すと自動的に入力されます。

=$A$1:INDEX($A:$A,COUNTA($A:$A))

あるいは、

=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講座