【Excel】プルダウンの連動がINDIRECTでできない理由｜ハイフン・追加しても出ないを、FILTER関数の2段階で解決

Excelのプルダウン 種類を選ぶと備品名が絞れる連動 Excel

「種類を選んだら、備品名のリストがその種類だけになる」

Excelのプルダウン（ドロップダウンリスト）を2段階で連動させる方法として、よく紹介されているのがINDIRECT関数を使うやり方です。

ところが、やってみると「ある種類だけリストが出ない」「一覧に足したのに出てこない」ということが起きます。

この記事では、INDIRECT関数の連動で出てこない理由を先にお伝えしてから、INDIRECT関数以外の作り方として、FILTER関数で連動させる方法を説明します。種類ごとの一覧も、名前の定義もいりません。

この記事の前提

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

先に結論。連動とは、1つ目のセルで「元の値」の範囲を切り替えること

プルダウンに出るのは、［データの入力規則］の「元の値」に書いた範囲の中だけです。

連動というのは、1つ目のセル（種類）で選んだものに合わせて、2つ目のセル（備品名）の元の値の範囲を切り替えることです。INDIRECT関数で作っても、FILTER関数で作っても、やっていることはこれだけです。違うのは、切り替える先の範囲をどう用意するかです。

INDIRECT関数で作る FILTER関数で作る
種類ごとの一覧 別に作る いらない（マスタ1つのまま）
名前の定義 種類ごとに付ける いらない
マスタに足したとき 一覧にも足して、名前の範囲を直す すぐリストに出る
ハイフン入りの種類 そのままでは出ない そのまま出る
使えるExcel どのバージョンでも Microsoft 365・Excel 2021以降

この記事では、会社の備品の貸出表を例にします。シート「マスタ」に備品の一覧（テーブル名「備品」・A列が種類、B列が備品名）、シート「貸出表」のC列に種類、D列に備品名のプルダウンを付けます。

1. INDIRECT関数の連動で、ある種類だけ出てこない

まず、INDIRECT関数で作る方法をおさらいします。

  1. 種類ごとに備品名を並べた一覧を作る（1行目に種類、その下に備品名）
  2. 一覧を選んで、［数式］タブの［選択範囲から作成］で「上端行」にチェックを入れてOK。種類の名前が、その下の範囲の名前になる
  3. 備品名のセルの元の値に、次の式を入れる
=INDIRECT($C2)

C2に「カメラ」と入っていれば、「カメラ」という名前の範囲がリストに出る、という仕組みです。

種類にハイフンが入っていると出ない

この作り方で、「ポケットWi-Fi」を選んだときだけ、備品名のリストが出てきません。

原因は名前です。名前にはハイフン（-）が使えないので、［選択範囲から作成］で作ると「ポケットWi_Fi」に変わります。 INDIRECT関数が「ポケットWi-Fi」という名前を探しても見つからないので、リストが出ないんです。

元の値の式で、ハイフンをアンダーバーに置き換えてから探すと出るようになります。

=INDIRECT(SUBSTITUTE($C2,"-","_"))

SUBSTITUTE関数は、文字を置き換える関数です。「C2の中の – を _ に置き換えたもの」を名前として探します。

名前は、列ごとに選んで作る

種類ごとの一覧は、種類によって備品の数が違います。一覧をまとめて選んで［選択範囲から作成］をすると、備品の少ない列では、下の空白まで名前の範囲に入ります。リストの下に空白が並ぶのはこれが原因です。名前は、1列ずつ選んで作ってください。

2. マスタに足したのに、連動先に出てこない

INDIRECT関数の作り方では、マスタに「ノートPC-05」を足しても、ノートPCを選んだときのリストに出てきません。

リストに出ているのは、種類ごとの一覧に付けた名前の範囲です。マスタではありません。足すたびに、種類ごとの一覧にも足して、［数式］タブの［名前の管理］で範囲を広げ直すことになります。

ハイフン、名前の付け方、足したときの手間。この3つが、INDIRECT関数で連動を作ったときにつまずきやすい所です。どのバージョンのExcelでも使えるのはINDIRECT関数の強みなので、古いExcelを使っている方は、上の直し方で対応してください。

Microsoft 365かExcel 2021以降なら、次の方法で、種類ごとの一覧も名前も作らずに連動させられます。

3. INDIRECT関数以外の作り方：FILTER関数で連動させる

マスタは「備品」という名前のテーブルにしておきます（テーブルにする方法は前回の記事で説明しています）。

① 種類のプルダウンを作る

貸出表のC2〜C11を選んで、［データ］タブの［データの入力規則］で「リスト」を選び、元の値に次の式を入れます。

=INDIRECT("備品[種類]")

テーブル「備品」の種類の列を指定しています。元の値にはテーブルの名前（構造化参照）をそのまま書けないので、ダブルクォーテーションで囲んでINDIRECT関数で包んでいます。ここで使っているINDIRECT関数は、種類ごとの名前を探すためのものではありません。

② 種類で絞った備品名を、どこかに並べる

FILTER関数は元の値に直接書けないので、一度どこかのセルに並べます。ここでは貸出表の空いているK列を使います。K2に次の式を入れます。

=FILTER(備品[備品名],備品[種類]=C2,"")
  • 1つ目の引数（備品[備品名]）：取り出したいもの。マスタの備品名の列です
  • 2つ目の引数（備品[種類]=C2）：条件。種類がC2と同じもの
  • 3つ目の引数（""）：条件に合うものが1つもないときに返すもの。種類がまだ空の行でエラーにならないように、空っぽにしています

C2が「ノートPC」なら、K列にノートPCだけが並びます。

③ 元の値に「K2#」を書く

貸出表のD2〜D11を、D2から選んで、データの入力規則の元の値に次のように入れます。

=K2#

K2の後ろの # は、「K2の式が並べた範囲ぜんぶ」という意味です。これでD2の▼を押すと、ノートPCだけが出てきます。

4. 2行目は動くのに、3行目が空白になる

ところが、3行目の種類に「プロジェクター」を選んでD3の▼を押すと、何も出てきません。

D3の入力規則を開いてみると、元の値は =K3# になっています。D2〜D11をまとめて設定したので、1行下のD3では、1行下のK3を見ているんです。ところが、式が入っているのはK2だけで、K3には式が入っていません。 だから何も出てきません。

行ごとに式を持たせる。縦ではなく、横に並べる

それなら、K3にも3行目の種類で絞った式を入れればいい、となります。行ごとに式を1つずつ持たせれば、行ごとに違うリストになります。

ただ、K2の式をそのまま下にコピーすることはできません。K2の結果は下に並んでいる（スピルしている）ので、その下のセルに式を入れると、並ぶ場所がふさがって #SPILL! エラーになります。

そこで、結果を横に並べます。使うのはTRANSPOSE関数で、縦に並んだものを横に並べ替えます。K2の式を、TRANSPOSEで囲むだけです。

=TRANSPOSE(FILTER(備品[備品名],備品[種類]=C2,""))

これで、K2の結果がK2、L2、M2…と横に並びます。このK2をK11までコピーすると、行ごとに、その行の種類の備品が並びます。

D3の▼を押すと、プロジェクターが出てくるようになりました。INDIRECT関数では出なかったポケットWi-Fiも、そのまま出ます。

元の値に $ を付けないのは、行ごとにずらしたいから

元の値の =K2# には、$ を付けていません。

元の値をセルのクリックで指定すると =$K$2# のように $ が付きますが、そうするとD3もD4も、全部K2を見ることになります。全部の行に、2行目の種類のリストが出てしまいます。

$ を付けなければ、D3はK3、D4はK4と、自分の行の式を見ます。 元の値は =K2# のままで、行ごとに連動します。

マスタに足すと、すぐリストに出る

マスタの一番下に「ノートPC-05」を足してみてください。ノートPCを選んだ行の▼に、すぐ出てきます。

元の値は一度も触っていません。種類ごとの一覧も、名前もありません。マスタ1つのままです。

5. 種類で絞ったうえで、貸出中のものも外したい

前回の記事で、マスタに「状態」の列（貸出中かどうか）を作って、貸出中のものをリストから外しました。これを、今回の連動と組み合わせます。

条件は2つです。種類がC2と同じ、かつ、状態が「貸出中」ではない。FILTER関数で条件を2つにするときは、それぞれを丸括弧で囲んで、掛け算でつなぎます。

=TRANSPOSE(FILTER(備品[備品名],(備品[種類]=C2)*(備品[状態]<>"貸出中"),""))

直すのは、2つ目の引数（条件）のところだけです。K2を差し替えたら、K11までコピーし直します。

これで、カメラを選んだ行の▼から、まだ返ってきていない「カメラ-01」が消えます。返却日を入れれば、また出てきます。

なぜ掛け算で「かつ」になるのかは、こちらの記事で詳しく解説しています。

6. 種類を変えたのに、備品名が古いまま残る

連動を使っていると、必ず一度は出会う落とし穴です。これはINDIRECT関数で作っても、FILTER関数で作っても起きます。

7行目で、種類に「ノートPC」、備品名に「ノートPC-03」を選んだとします。そのあとで、種類を「プロジェクター」に変えます。

種類はプロジェクターなのに、備品名はノートPC-03のままです。▼を押すと、リストはちゃんとプロジェクター-01・02に変わっています。変わったのはリストだけで、入っている値はそのままです。

入力規則が確かめるのは、入力したときだけ

なぜこうなるのか。入力規則が確かめるのは、そのセルに入力したときだけだからです。

D7にノートPC-03を選んだとき、種類はノートPCだったので、合っていました。そのあとC7を変えても、D7には何も入力していないので、確かめ直しは起きません。連動の作り方の問題ではなく、入力規則そのものの決まりです。

合わない組み合わせに、色を付ける（条件付き書式）

そこで、種類と備品名の組み合わせが合わないときに、色が付くようにしておきます。

  1. 貸出表のD2〜D11を、D2から選ぶ
  2. ［ホーム］タブの［条件付き書式］→［新しいルール］
  3. 「数式を使用して、書式設定するセルを決定」を選び、次の式を入れる
  4. ［書式］で塗りつぶしの色（薄い赤など）を選んでOK
=AND($D2<>"",COUNTIFS(マスタ!$A:$A,$C2,マスタ!$B:$B,$D2)=0)

式を分けて見ます。

  • COUNTIFS：条件に合う行を数えます。「マスタの種類の列に、この行の種類（C2）がある」かつ「同じ行の備品名が、この行の備品名（D2）」。つまり「この種類の、この備品」という行が、マスタに何行あるかです
  • =0：0行なら、その組み合わせはマスタに無い＝種類と備品名が合っていない、ということです
  • $D2<>””：備品名がまだ空の行も、数えると0になります。そのままだと空の行が全部赤くなるので、「備品名が空ではない」という条件を足しています
  • AND：2つを両方満たすときだけ、色を付けます

範囲を マスタ!$A:$A のように列ごとにしているのは、マスタに備品を足しても、数える範囲の外にならないようにするためです。条件付き書式の数式には、備品[種類] のようなテーブルの名前を直接書けないので、こうしています。

これで、種類を変えた瞬間にD7が赤くなります。種類を戻すと、色は消えます。

「無効データのマーク」との違い

［データの入力規則］の▼にある［無効データのマーク］でも、リストに無い値に丸を付けられます。ただ、確かめるのは「今のリストに入っているか」だけです。

上の5で貸出中を外していると、正しく貸し出している行（借りたカメラ-01の行）にも丸が付きます。リストから外れているからです。種類と備品名の組み合わせだけを見たいときは、条件付き書式のほうを使ってください。

補助の列は、隠しておいても動く

K列から右（FILTER関数で横に並べた補助の列）は、ふだん見る必要がないので、列を選んで右クリック→［非表示］にしておくとすっきりします。

非表示にしても、プルダウンは今までどおり動きます。並ぶ件数がいちばん多い種類のぶんだけ右に広がるので、補助の列の右側には何も入力しないでおいてください。

まとめ

  • 連動とは、1つ目のセルで、元の値の範囲を切り替えること
  • INDIRECT関数の連動で出ない → ハイフン入りの種類は SUBSTITUTE で置き換える／名前は列ごとに作る／マスタに足したら一覧と名前の範囲も直す
  • Microsoft 365・Excel 2021以降なら、FILTER関数で行ごとに絞って（TRANSPOSEで横に並べる）、元の値に =K2#（$なし）
  • 入力規則が確かめるのは、入力したときだけ。種類を変えても備品名は残るので、条件付き書式で色を付けておく

連動を作ったら、色で確かめる仕組みもセットにしておくと安心です。

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

【Excel】連動するドロップダウンリストはFILTERで｜INDIRECTの名前も一覧も、もういらない
https://youtu.be/ZV_QDYyKpdQ

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

ほかのつまずきも、同じように1本ずつ動画にしています。

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