【Excel】プルダウンに追加しても出てこない理由｜テーブルにしても反映されない・別シートで自動で増やす方法

「一覧に1つ足したのに、プルダウンに出てこない」

Excelのプルダウン（ドロップダウンリスト）で、いちばんよく聞くつまずきです。

テーブルにすれば自動で増えると聞いてやってみたのに、それでも出てこない。そういう方も多いと思います。

この記事では、なぜ出てこないのかを先にお伝えしてから、足したら自動で増えるようにする方法を説明します。後半では、「貸出中のものはリストに出さない」のように、条件に合うものだけを出す方法も紹介します。

この記事の前提

Windows版のExcel（Microsoft 365）で説明します。後半で使うFILTER関数とSORT関数は、Microsoft 365のExcel、またはExcel 2021以降で使えます。前半（テーブルとINDIRECT関数）は、それより前のバージョンでも使えます。

この記事の前提

先に結論。リストに出るのは「元の値」に書いた範囲の中だけ
1. 一覧に足したのに、リストに出てこない
2. テーブルにしたのに、反映されない
1. 同じシートなら広がる。別のシートだと広がらない
3. 別のシートでも、足したら自動で増やす（INDIRECT関数）
4. 貸出中のものを、リストに出したくない
おまけ：備品名を選んだら、保管場所が自動で出る
まとめ

先に結論。リストに出るのは「元の値」に書いた範囲の中だけ

プルダウンは、［データ］タブの［データの入力規則］で作ります。入力値の種類を「リスト」にすると、「元の値」という欄が出てきます。

リストに出るのは、この元の値に書いた範囲の中だけです。

だから、一覧の下に1行足しても、その行が元の値の範囲の外にあれば出てきません。足したものを出したいなら、元の値に何を書くかを変えます。

元の値に書くもの	一覧に足すと	向いているもの
セル範囲（`=マスタ!$B$2:$B$12`）	増えない	曜日など、後から増えない一覧
テーブルの名前（`=INDIRECT("備品[備品名]")`）	自動で増える	備品・取引先など、増えていく一覧
並べた場所（`=$J$2#`）	条件に合うものだけ出る	貸出中を除くなど、状況で中身を変えたいとき

この記事では、会社の備品を貸し出すときの記録表を例にします。シート「マスタ」に備品の一覧（種類・備品名・保管場所）、シート「貸出表」に貸し出しの記録があり、貸出表の備品名の列にプルダウンを付けます。

1. 一覧に足したのに、リストに出てこない

マスタの一覧（B2〜B12）を元の値にしてプルダウンを作ったあと、13行目に「ノートPC-04」を足しました。でも、貸出表の▼を押しても出てきません。

データの入力規則を開いて、元の値を見てみてください。

=マスタ!$B$2:$B$12

12行目までになっています。13行目は範囲の外なので、出てこないんです。

いちばん手っ取り早い直し方は、$B$12 を $B$13 に書き換えることです。これで出てくるようになります。

ただ、これだと備品を足すたびに、入力規則を開いて直すことになります。たぶん、どこかで直し忘れます。

ここからは、元の値を手で直さなくていい方法を説明します。

2. テーブルにしたのに、反映されない

足した分を自動で広げるには、一覧をテーブルにします。テーブルは「ここからここまでが1つの表です」とExcelに教えておく機能で、すぐ下の行に入力すると表の範囲が自動で広がります。

一覧の中のどこかのセルをクリックして Ctrl＋T
「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して OK
［テーブルデザイン］タブの「テーブル名」を分かりやすい名前に変える（ここでは「備品」）

ところが、テーブルにしてから一覧にもう1台足しても、リストには出てきません。

元の値を見ると、=マスタ!$B$2:$B$13 のまま。テーブルは広がっているのに、元の値は広がっていません。

同じシートなら広がる。別のシートだと広がらない

実は、一覧とプルダウンが同じシートにあれば、セル範囲のままでもテーブルと一緒に元の値が広がります。同じシートでテーブルの下に1行足すと、元の値の $A$2:$A$4 が $A$2:$A$5 に自動で変わります。

広がらないのは、一覧を別のシートに分けているときです。

元の値に書いてあるのは、あくまで「マスタシートのB2からB13」という場所です。同じシートならテーブルに合わせて広がる仕組みがあるのですが、別のシートだとそれが働きません。

一覧を「マスタ」のような別のシートに分けるのは、仕事ではよくある作り方です。「テーブルにしたのに反映されない」という方は、たいてい一覧が別のシートにあります。

3. 別のシートでも、足したら自動で増やす（INDIRECT関数）

別のシートでも広がるようにするには、場所ではなくテーブルそのものを指定します。

テーブルの名前で書く（構造化参照）

テーブルの列は、こう書けます。

備品[備品名]

「テーブル『備品』の、備品名の列ぜんぶ」という意味です。この書き方を構造化参照といいます。場所ではなく名前で指定しているので、テーブルが広がれば、これも一緒に広がります。

試しにどこかのセルに =備品[備品名] と入れると、備品名が全部並びます。

元の値には、そのまま書けない

ところが、元の値に =備品[備品名] と入れてOKを押すと、エラーになります。セルには書けるのに、元の値には構造化参照をそのまま書けない決まりになっています。

そこで、INDIRECT関数で包みます。

=INDIRECT("備品[備品名]")

備品[備品名] をダブルクォーテーションで囲むと、ただの文字になります。INDIRECT関数は、その文字を範囲として使う関数です。文字としてなら、元の値にも入れられます。

ダブルクォーテーションは忘れないでください。 付け忘れると、さっきと同じエラーになります。

これで、マスタに備品を足すと、元の値を触らなくてもリストに出てくるようになります。入力規則を開いても、元の値は =INDIRECT("備品[備品名]") のまま変わっていません。それでいいんです。

足しても増えないときは、1行空いていないか

テーブルは、すぐ下の行に入力したときだけ広がります。1行空けて入力すると、テーブルの外になるので広がりません。リストにも出てきません。

補足：OFFSET関数とCOUNTA関数を使うやり方

範囲を自動で広げる方法として、OFFSET関数とCOUNTA関数を組み合わせるやり方もよく紹介されています。

=OFFSET(マスタ!$B$2,0,0,COUNTA(マスタ!$B:$B)-1,1)

B2を起点に、B列に入っている数（見出しの1つを引く）だけの高さを範囲にする、という式です。これでも足した分は出てきます。

ただ、引数が5つあって入れ子になるぶん読みにくく、一覧の途中に空白があると最後がずれます。テーブルとINDIRECT関数なら1つで済むので、僕はこちらをおすすめしています。

4. 貸出中のものを、リストに出したくない

ここまでで、足したものはリストに出るようになりました。

でも、貸出表で「カメラ」を貸し出そうとして▼を押すと、まだ返ってきていない「カメラ-02」も出てきます。うっかり選ぶと、手元にないものを貸し出したことになります。

テーブルにしただけでは、一覧に載っているものは全部リストに出ます。 条件に合うものだけを出したいときは、数式でリストを作ります。流れは3つです。

マスタに「状態」の列を作って、貸出中かどうかを出す
貸出中でないものだけを、別の場所に並べる
元の値に、並べた場所を書く

① マスタに「状態」の列を作る

マスタのD1に「状態」と入れると、テーブルがD列まで広がります。D2に次の式を入れます。

=IF(COUNTIFS(貸出表!$C$2:$C$11,B2,貸出表!$E$2:$E$11,"")>0,"貸出中","")

テーブルなので、1つ入れるだけで同じ列の全部の行に入ります。

式を分けて見ます。

COUNTIFS：条件に合う行を数えます。条件は2つで、「貸出表の備品名が、この行の備品名と同じ」かつ「返却日が空っぽ」。つまり、借りていてまだ返していない記録が何件あるかです
IF：その数が0より大きければ「貸出中」、そうでなければ空っぽにします
$：下の行にコピーされても、貸出表の範囲がずれないように固定しています

B2 の部分をクリックで選ぶと [@備品名] と表示されますが、意味は同じです。また、貸出表の範囲（ここでは11行目まで）は、実際に使うときは記録する行数に合わせて広めに取ってください。

② 貸出中でないものだけを並べる（FILTER関数）

ここで使うのはFILTER関数です。FILTER関数は元の値に直接書けないので、一度どこかのセルに並べます。ここでは貸出表の空いているJ列を使います。J1に「借りられる備品」と見出しを入れて、J2に次の式を入れます。

=SORT(FILTER(備品[備品名],備品[状態]<>"貸出中"))

1つ目の引数（備品[備品名]）：取り出したいもの。テーブル「備品」の備品名の列です。元の値には書けなかった構造化参照も、数式の中ならそのまま書けます
2つ目の引数（備品[状態]<>"貸出中"）：条件。<> は「等しくない」という意味なので、状態が「貸出中」でないもの
SORT：並べた結果を、あいうえお順に並べ替えます。後から足したものがバラバラに並ばないようにするためです

これで、貸出中の「カメラ-02」が抜けた一覧がJ列に並びます。FILTER関数の条件の書き方は、こちらの記事でも詳しく解説しています。

【Excel】FILTER関数の複数条件｜ANDは掛け算、ORは足し算

式を入れたのはJ2だけなのに、下まで並ぶ（スピル）

式を入れたのはJ2の1つだけなのに、結果が下のセルまで並んでいます。これをスピルといいます。

並ぶ先のセルに何か入っていると、#SPILL! というエラーになります。スピルで並ぶ先には、何も入力しないでおいてください。

③ 元の値に、並べた場所を書く

最後に、貸出表の備品名の列の入力規則を開いて、元の値を次のように書き直します。

=$J$2#

J2の後ろの # は、「J2の式が並べた範囲ぜんぶ」という意味です（スピル範囲演算子といいます）。

ここで $J$2:$J$12 のように範囲で書くと、並ぶ件数が増えたときに、範囲の外に出た分がリストから外れます。最初に「一覧に足したのに出てこない」で起きたことと同じです。# を付けておけば、何件並んでもその全部になります。

借りると消えて、返すと戻る

これで▼を押すと、カメラ-02はリストに出てきません。

貸出表に「ノートPC-02」を記録すると、J列からもリストからもノートPC-02が消えます。順番に追うと、こうなっています。

貸出表にノートPC-02を記録する
マスタの状態が、それを数えて「貸出中」に変わる
FILTER関数の条件に合わなくなって、J列から外れる
元の値はJ列の並びを指しているので、リストからも外れる

返ってきて返却日を入れると、COUNTIFSの2つ目の条件（返却日が空っぽ）に合わなくなり、状態が空っぽに戻ります。ノートPC-02はまたリストに出てきます。

この間、元の値は一度も触っていません。

おまけ：備品名を選んだら、保管場所が自動で出る

備品名が決まれば、保管場所も決まります。貸出表のD2に次の式を入れて、下の行にコピーします。

=XLOOKUP(C2,備品[備品名],備品[保管場所],"")

1つ目：何を探すか（C2の備品名）
2つ目：どこから探すか（備品テーブルの備品名の列）
3つ目：見つかったら何を返すか（保管場所の列）
4つ目：見つからなかったときにどうするか（"" で空っぽ）

これで、プルダウンから備品名を選ぶと、保管場所も自動で入ります。

まとめ

リストに何が出るかは、元の値に何を書くかで決まります。

一覧に足したのに出てこない → 元の値の範囲の外に足している
テーブルにしたのに反映されない → 一覧が別のシートにある。=INDIRECT("備品[備品名]") のようにテーブルの名前で書く
条件に合うものだけ出したい → FILTER関数でどこかに並べて、元の値に =$J$2# のように書く

一覧が増えるだけならテーブル、条件で絞りたいならFILTER関数。この基準で選んでください。

実際の画面で確認したい方は、動画でも解説しています。

【Excel】ドロップダウンリストは3段階｜足しても出ない／自動で増える／条件で絞る
https://youtu.be/f1xTuCPsS5A

あわせて読みたい記事です。

プルダウンは、作り方を覚えるより、元の値に何が書いてあるかを見るほうが早く直せると思っています。ほかのつまずきも、同じように1本ずつ動画にしています。

▼チャンネル登録はこちら
https://www.youtube.com/@pc7663