データの入力規則の「リスト」に、指定したセル範囲の値を表示できます。
たとえば下図のセル B3 には、F4 から F8 の範囲を参照するリストが設定されています。
J01.png

[データの入力規則] ダイアログ ボックスを確認すると、[元の値] にセル範囲が指定されていることがわかります。
J02.png

 

標準のセル範囲を参照している場合

リストが参照しているのが標準のセル範囲 (テーブルではない。なにもしてなければ普通はこっち。) の場合、今回のケースでは参照しているのはセル F8 までなので、セル F9 に項目を追加したとしても、ドロップダウン リストには追加した項目 (F) は表示されません。
J03.png
 

追加した項目 (セル F9 の F) もドロップダウン リストに表示するには、リストが設定されているセルを選択して [データの入力規則] ダイアログ ボックスを表示して、[元の値] の範囲をセル F9 も含むように修正します。
(ドラッグして範囲指定しなおしてもよいし、手入力で修正しても OK)

コレが面倒だからなんとかなりませんか?というのが昔からある課題なのです。解決策はいくつもありますが、その中の 1 つであるテーブルを利用する方法について次のセクションから記載します。
J04.png

 

テーブルの一部を参照する場合

リストが参照しているのがテーブル内の列の場合は、項目を追加したときに、ダイアログ ボックスでリストの参照範囲を編集しなくても、追加した項目がドロップダウン リストに表示されます。

簡単にいうと、データの追加対応が楽になりますよ、っていう話です。(ザツだな)

テーブルとの組み合わせの使い方 (指定の方法) はいくつかあって、目的やテーブルの形式によって使い分けるのですが、ここではシンプルに、同じワークシートにあるテーブルの一部を参照することを例にご紹介します。
J05.png

下図では、F3 から H8 のセル範囲をテーブルに変換し、「コースリスト」というテーブル名を設定してあります。
(今回のやり方ではドロップダウン リストの設定時にテーブル名は使用しないので既定のままでもよいです。テーブルに関しては過去の記事をどうぞ。)
 J06.png

 

  1. ドロップダウン リストを設定するセルを選択し、リボンの [データ] タブの [データ ツール] グループの [データの入力規則] をクリックします。
    J07.png
     
  2. [データの入力規則] ダイアログ ボックスが表示されます。

    [設定] タブの [入力値の種類] で「リスト」を選択し、[元の値] ボックスの中をクリックするなどしてカーソルを表示したら、ドロップダウン リストに表示したい値が含まれるテーブルのセル範囲をドラッグして選択します。

    このとき、選択したセル範囲は、テーブルだからといって特別な表記にはならず、標準のセル範囲を参照するときと同じように絶対参照 ($ が列と行につく) で指定されます。
    J08.png
     
  3. テーブルは、一番下の行のすぐ下に値を入力すると、自動的に 1 行分拡張されます。

    ここでは、テーブルの「E」のセルの下に「F」を追加して 1 行分範囲を拡張しています。
    J09.png
     
  4. ドロップダウン リストが参照しているセル範囲がテーブルの場合は、(リストの範囲を編集しなくても) 自動的に追加した項目がリストに表示されます。
    J10.png
     
  5. テーブルでデータを追加したあと、[データの入力規則] ダイアログ ボックスの [元の値] を確認すると、(勝手に) 参照する範囲が F9 まで拡張されている (追加した項目も含まれている) ことがわかります。
    J11.png
     

おまけ

テーブルには名前があって、テーブルや列を指定するときにその構造を使えるのだから、リストの設定でも使いたいという場合は、INDIRECT 関数と組み合わせます。

たとえば、”「コースリスト」という名前のテーブルの「コース」という列” というのを Excel にわかるように記述するには、「”コースリスト[コース]”」とします。このときに、この文字列がただの文字列ではなくて場所の名前ですよ、というのを Excel に伝えるために INDIRECT 関数を使用します。

下図の [元の値] にあるように指定でき、この方法なら、参照するテーブルがどのワークシートにあってもデータの追加に対応できます。この場合はテーブル名とかはわかりやすい名前に変えておいたほうがいいでしょうね。
J12.png


 


リストの参照範囲が自動的に広がるようにしたいというリクエストは多く、テーブル機能がなかったころは、名前の定義や OFFSET 関数と COUNTA 関数で対応していました。
入力規則のリストに関してはテーブルを利用することで対応でき、バリエーションも多いので知っておくとよいと思います。


石田 かのこ