範囲が可変する名前定義

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


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


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



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


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



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

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


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



さて、ここからが本題です。
もし、このリストが可変する場合どうするか?
担当者が減ったり増えたりすることってよくあります。毎回、参照範囲を設定し直すのは面倒ですね。
こういう場合は、名前管理で参照範囲の代わりに数式を入力しておくと便利です。
「数式」タブから「名前の定義」をクリックします。

「新しい名前」ウィンドウの名前のテキストボックスに任意の名前を入力します。
参照範囲に以下の数式を入力します。
シート名は入力する必要はありません。「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関数については、ヘルプで検索してみてください。

「データ」タブの「データの入力規則」ボタンをクリックして、「データの入力規則」ダイアログボックスを表示します。「元の値」に名前を参照するように設定します。「元の値」のテキストボックスにカーソルを置いて「F3」を押すと、「名前の貼り付け」ダイアログボックスが表示されるので便利です。

これで、「リストシート」にあるA列のデータを更新するだけで、データの数が可変しても参照範囲を更新する必要はありません。

参照する範囲が行も列も可変する場合の方法は、縦横の範囲が可変する名前定義を参照してください。

【関連記事と広告】



人気記事TOP10

  • Excelのワークシートで、必要な範囲のみを表示して、他のシート部分は見えないようにしたいのですが・・・と、ご質問を受けることがあります。 ウィンドウ枠を固定して、不要な行列を非表示にすることでスクロールもできないワークシートを作成することができます。 Excelのバージョ...

  • グラフの種類の中に「補助円グラフ付き円グラフ」というのがあります。 ↓のようなグラフです。 一項目の内訳を別の円グラフに表示したい場合に便利です。 元データは↓のようになっています。 九州地区の内訳は別表に作成されています。 グラフの元データの選択 グラフの...

  • システムからCSVでデータをダウンロードできるようになっているものって多いですね。 でも、ダウンロードしたCSVを直接シングルクリックやダブルクリックで開くのは危険です。 アイコンをシングルクリックやダブルクリックすると、おそらくExcelが起動すると思いますが、E...

  • A4用紙に4枚印刷する方法です。 作成する文書は、はがきサイズで作成します。(※A4サイズのままでも出来ます) Officeボタンをクリックして、「印刷」をクリックします。 「印刷」ダイアログボックスが表示されます。 印刷範囲の「ページ指定」に半角で「1,1,1,1」と...

  • デジタルカメラで動画を撮ったけど、横向きに撮ってしまったということがあります。 写真なら、簡単に回転させることができるけど、動画はできないと思っていらっしゃる方も多いようです。 ムービーメーカーの起動 ムービーメーカーを使うと回転させることができます。 ムービーメーカー...

  • Excelブックを時々、読み取り専用として開いた後、編集する必要があると分かった場合   また閉じて開きなおすのは面倒ですね。 また、参照するだけでいいんだけど、今 開くと他の人が編集できなくなるしなぁと遠慮してしまうことがありませんか? こういう場合は、読み取り専用として...

  • A列とB列に下のように値が入力されていて、隣り合ったセルの値が同じならセルに色を付ける方法です。条件付き書式を使うと便利です。 範囲選択 まず、範囲選択します。 条件付き書式の新しいルール 範囲選択したまま「ホーム」タブの「条件付き書式」から「新しいルール」...

  • ファイルのパス名をクイックアクセスツールバーに表示する方法です。 Excel2007での解説ですが、WordやPowerPointでも同じです。 Officeボタンをクリックして、「Excelのオプション」をクリックします。 「Excelのオプション」から「ユ...

  • IE10を使ってます。 時々、いらいらしてしまうのですが、ウィンドウ左上にある「戻る」ボタンをクリックしても戻らないことがあります。 検索してみると、IEの設定をリセットすると良いらしいとのこと。 「ツール」から「インターネットオプション」をクリック。 「詳細設定...

  • Outlookの表示一覧は「ビュー変更」で切り替えることができます。3つの標準ビューがありますが、カスタマイズすることもできます。(画像はoutlook2013です) 「表示」タブをクリックして、左端にある「ビューの変更」をクリックします。標準では、「コンパクト」「シングル」...