Excelで商品区分を選ぶと、その区分に合った商品名だけをプルダウンリストに表示したい場面があります。
このような連動型の入力規則は、部署と担当者、都道府県と市区町村、分類と品目など、複数条件を持つ表で役立つ機能です。
入力規則に複数条件を設定するには、元データの整理、名前の定義、INDIRECT関数やFILTER関数の活用を順番に行うことが大切です。
基本の考え方は、1つ目のプルダウンで条件を選び、その選択結果を2つ目のリストの参照先にすることです。
Microsoft 365やExcel 2021以降ではFILTER関数を使う方法が便利で、古いExcelでも名前の定義とINDIRECT関数で対応できます。
この記事では、複数条件に応じてプルダウンリストを切り替える設定を、サンプル表と具体例を使って詳しく解説していきます。
エクセルで入力規則に複数条件を設定する方法【連動プルダウンの作成】
| A列 | B列 | C列 |
|---|---|---|
| 1 | 分類 | 商品名 |
| 2 | 飲み物 | コーヒー |
| 3 | 飲み物 | 紅茶 |
| 4 | 食べ物 | パン |
それではまず、最初の選択内容に合わせて次のプルダウンを変える基本設定について解説していきます。
連動プルダウンでは、入力用シートとは別に、候補一覧を管理するマスター表を準備すると運用しやすくなります。
候補データの1行目は必ず見出しにし、実際のデータを2行目から入力する形にしておくと、数式や範囲指定のミスを減らせます。
入力用シートと候補一覧シートの準備
まず、入力する表を用意します。
ここでは入力用シートのA列を分類、B列を商品名、C列をサイズとし、1行目にはヘッダーが入っているものとします。
別シートの名前をマスターとして、A列に分類、B列に商品名、C列にサイズを入力します。
分類には飲み物、食べ物、文房具のような大きな区分を登録し、同じ分類に属する候補を複数行に並べましょう。
たとえばマスターシートでは、A2とA3に飲み物、B2にコーヒー、B3に紅茶を入力します。
分類と商品名を同じ行に対応させておくと、後でFILTER関数を使って条件に一致する商品だけを抽出できます。
データが途中で空白になると候補が途切れて見えることがあるため、一覧の途中には空行を入れないことがポイントです。
【操作のポイント】候補表は入力表と分け、1行目をヘッダー、2行目以降をデータとして統一します。
1つ目のプルダウンリストの登録
続いては、分類を選ぶための1つ目のプルダウンリストを確認していきます。
入力用シートでA2からA100など、分類を選択させたい範囲を選びます。
リボンのデータタブからデータの入力規則をクリックし、設定タブの入力値の種類でリストを選択します。
元の値には、分類候補を入力したセル範囲を指定します。
分類がマスターシートのE2からE4に重複なしで用意されている場合は、元の値に「=マスター!$E$2:$E$4」と入力します。
シート名に空白が含まれる場合は、数式バーで参照範囲を選択してExcelに自動入力させると安全です。
入力規則のドロップダウンを表示するチェックが有効になっているかも確認しましょう。
【操作のポイント】分類候補は重複のない一覧にしておくと、利用者が迷わず選択できます。
2つ目のプルダウンを条件に連動させる準備
続いては、分類を選んだ後に商品名を絞り込む準備を確認していきます。
Microsoft 365またはExcel 2021以降では、空いているセルにFILTER関数で候補を表示させる方法が分かりやすい手順です。
入力用シートの例として、G2セルに次の数式を入力します。
=FILTER(マスター!$B$2:$B$100,マスター!$A$2:$A$100=$A2,”該当なし”)
この数式は、マスターシートのA列が入力用シートのA2と一致する行だけを探し、対応するB列の商品名を一覧として返します。
FILTER関数の最初の引数は返したい範囲、2つ目の引数は抽出条件、3つ目の引数は該当データがない場合の表示です。
G2から下へコピーする場合は、条件セルの列だけを固定して「$A2」と指定します。
これにより、行ごとに異なる分類を選択しても、その行に合った候補一覧を作れるようになります。
【操作のポイント】FILTER関数の条件範囲と抽出範囲は、開始行と終了行を必ずそろえます。
FILTER関数による複数条件のプルダウンリスト
| 分類 | 商品名 | サイズ |
|---|---|---|
| 飲み物 | コーヒー | S |
| 飲み物 | コーヒー | M |
| 飲み物 | 紅茶 | M |
続いては、2つ以上の条件を同時に使って候補を切り替えるFILTER関数の設定を解説していきます。
複数条件を掛け算で結合する数式
分類と商品名の両方に応じてサイズを選ばせたい場合は、FILTER関数の条件を増やします。
入力用シートでA2に分類、B2に商品名が選ばれているとして、H2セルに候補を出す数式を入力します。
=FILTER(マスター!$C$2:$C$100,(マスター!$A$2:$A$100=$A2)*(マスター!$B$2:$B$100=$B2),”該当なし”)
条件式を丸かっこで囲み、その間に掛け算記号を入れると、両方の条件が成立する行だけを抽出できます。
ExcelではTRUEが1、FALSEが0として扱われるため、飲み物であり、かつコーヒーである行は1×1となり、候補として残る仕組みです。
掛け算はAND条件、足し算はOR条件として使えるため、条件が増えたときにも応用できます。
【操作のポイント】複数条件のFILTER関数では、それぞれの条件式を丸かっこで囲んでから掛け合わせます。
スピル範囲を入力規則の元の値に指定する方法
続いては、FILTER関数で表示された一覧を入力規則へ渡す方法を確認していきます。
H2セルにFILTER関数を入力すると、結果は必要な行数まで自動で広がります。
この自動展開された範囲をスピル範囲と呼び、参照時には先頭セルの後ろへシャープ記号を付けます。
サイズ用の入力規則を設定するとき、元の値には「=H2#」と入力します。
H2#は、H2から自動展開されている候補すべてを意味する参照です。
入力規則を設定する対象はC2セルです。
データタブからデータの入力規則を開き、入力値の種類をリストにして、元の値へ「=H2#」を指定しましょう。
この設定により、A2とB2の選択結果が変わるたびに、C2の候補も自動的に更新されます。
FILTER関数の結果がほかのデータでふさがれるとスピルエラーになるため、候補表示用の列には十分な空白を確保します。
【操作のポイント】動的配列を入力規則へ渡すときは、先頭セル番地の末尾に#を付けます。
行ごとに異なる連動リストを設定する方法
続いては、複数行の入力表へ連動プルダウンを広げる方法を確認していきます。
入力規則をC2だけに設定した後、C2をコピーしてC3からC100へ貼り付けても、数式参照が期待どおり変わらない場合があります。
行ごとに独立したFILTER関数の出力場所を用意することが必要です。
たとえばH2にサイズ候補を出す場合、H3にはA3とB3を参照する数式を配置します。
ただし、スピルした候補同士が重なる可能性があるため、実務では補助列を十分に空けるか、名前の定義を組み合わせる設計が向いています。
入力行が多い帳票では、候補一覧専用の補助シートを作ると表が見やすくなります。
テーブル機能を使う場合も、入力規則の参照式が行番号に応じて変わるかを数行で試してから全行へ適用しましょう。
【操作のポイント】複数行に展開する前に、2行目と3行目で別々の条件が正しく反映されるかを確認します。
名前の定義とINDIRECT関数による連動設定
| 分類名 | 名前の定義 | 候補 |
|---|---|---|
| 飲み物 | 飲み物 | コーヒー、紅茶 |
| 食べ物 | 食べ物 | パン、ケーキ |
続いては、FILTER関数を利用できないExcelでも使える、名前の定義とINDIRECT関数による設定を解説していきます。
分類ごとの候補範囲と名前の定義
まず、マスターシートに分類ごとの商品候補を列単位で並べます。
たとえばA1に飲み物、A2からA4にコーヒー、紅茶、ジュースを入力し、B1に食べ物、B2からB4にパン、ケーキ、サラダを入力します。
次に、飲み物の候補であるA2からA4を選択し、数式タブの名前の管理から新規作成を選びます。
Σ オートSUM fx 関数の挿入 定義された名前
| A | B | C | |
|---|---|---|---|
| 1 | 飲み物 | 食べ物 | |
| 2 | コーヒー | パン | |
| 3 | 紅茶 | ケーキ |
名前には見出しと同じ飲み物を入力し、参照範囲が「=マスター!$A$2:$A$4」になっていることを確認します。
同じように、食べ物の候補範囲には食べ物という名前を定義します。
1つ目のプルダウンの表示文字と、名前の定義に使う名前を一致させることが、この方法の重要な条件です。
【操作のポイント】名前には空白や記号を使いにくいため、分類名は短く分かりやすい表記にそろえます。
INDIRECT関数を使った入力規則の元の値
続いては、選択した分類名から名前の定義を呼び出す設定を確認していきます。
入力用シートのA2に分類を選ぶプルダウンが設定されている場合、B2の商品名用の入力規則を開きます。
入力値の種類をリストにして、元の値には次の数式を入力します。
=INDIRECT($A2)
INDIRECT関数は、セルA2に入っている文字列をセル番地や定義名として解釈する関数です。
A2で飲み物を選ぶと、INDIRECT($A2)は飲み物という名前の定義を参照し、コーヒーや紅茶を候補として表示します。
A2で食べ物を選べば、食べ物という名前の定義が参照され、パンやケーキに切り替わります。
分類をまだ選んでいない状態では候補を開けないことがありますが、先に親となるプルダウンを選ぶ運用で問題ありません。
【操作のポイント】INDIRECT関数内の$A2は、分類が入力される列だけを固定する指定です。
3段階のプルダウンへ拡張する方法
続いては、分類、商品名、サイズの3段階へ拡張する考え方を確認していきます。
2段階目の商品名にも対応する名前の定義を作成し、その商品名に合うサイズ候補を登録します。
たとえばコーヒーという定義名にはS、M、Lを設定し、紅茶という定義名にはM、Lを設定します。
3段階目のサイズを入力するC2セルの入力規則では、元の値を「=INDIRECT($B2)」とします。
これでA2の分類からB2の商品名が決まり、B2の商品名からC2のサイズ候補が決まる流れになります。
連動する段階が増えるほど、定義名の重複や表記ゆれがエラーの原因になりやすいため、候補マスターは定期的に見直しましょう。
【操作のポイント】3段階連動では、各段階の選択肢すべてに対応する名前の定義を作成します。
入力規則のエラー対策と複数条件の注意点
| 状況 | 主な原因 | 確認箇所 |
|---|---|---|
| 候補が表示されない | 参照範囲の誤り | 元の値 |
| 該当なしになる | 文字列の不一致 | 空白と表記 |
続いては、連動プルダウンで起こりやすいエラーと、複数条件設定時の注意点を解説していきます。
空白文字と表記ゆれの確認
候補が表示されないときは、条件セルとマスター表の文字が完全に一致しているかを確認します。
見た目が同じ飲み物でも、末尾に半角スペースや全角スペースが入っていると、FILTER関数では別の文字列として扱われます。
コピーしたデータには気付きにくい空白が含まれることがあるため、LEN関数で文字数を確認すると原因を見つけやすくなります。
=LEN(A2)
この数式はA2の文字数を返します。
想定より1文字多い場合は、TRIM関数や置換機能で余分な空白を取り除きましょう。
分類名、名前の定義、入力規則の参照式では、全角半角を含めた表記の統一が必要です。
【操作のポイント】候補が出ない場合は、最初に余分な空白と表記ゆれを疑います。
入力規則のコピー時に起きる参照ずれ
続いては、入力規則を下方向へコピーしたときの参照ずれを確認していきます。
たとえばB2の元の値を「=INDIRECT($A2)」と設定して下へコピーすると、B3では自動的に「=INDIRECT($A3)」となります。
この動きが連動表では必要ですが、「=INDIRECT($A$2)」としてしまうと、すべての行がA2だけを参照します。
反対に「=INDIRECT(A2)」では横方向へコピーした際に参照列まで動くため、レイアウト変更に弱くなります。
入力表を縦方向に増やす通常の運用では、列のみを固定する$A2が扱いやすい指定です。
データの入力規則ダイアログで設定を変更した後は、適用範囲が意図したセルまで広がっているかも確認しましょう。
【操作のポイント】縦方向にコピーする連動リストでは、親セルの列だけを$で固定します。
既存の選択値を消去する運用
続いては、親の選択肢を変更したときに残る古い値への対処を確認していきます。
分類を飲み物から食べ物へ変更しても、B2に以前選んだコーヒーが残ることがあります。
入力規則は通常、既に入力されている値を自動で消去する機能ではないためです。
手入力で運用する表では、親の項目を変更したら子の項目をいったん削除してから選び直すルールを共有します。
より厳密な運用が必要な場合は、条件付き書式で不整合な値を目立たせる方法や、VBAで親セルの変更時に子セルをクリアする方法もあります。
ただし、VBAを使うとマクロ有効ブックとして管理する必要があるため、まずは入力ルールと確認欄で対応できるか検討しましょう。
【操作のポイント】親項目を変更した後は、下位項目が新しい候補に合っているかを必ず確認します。
複数条件プルダウンを使いやすくする表の設計
| 入力順 | 選択項目 | 役割 |
|---|---|---|
| 1 | 分類 | 候補を大きく絞る |
| 2 | 商品名 | 対象を特定する |
| 3 | サイズ | 詳細条件を選ぶ |
続いては、複数条件の入力規則を長く使いやすくするための表設計を解説していきます。
候補マスターをテーブル化する考え方
候補一覧が追加される可能性がある場合は、マスター表をテーブルとして書式設定しておくと便利です。
マスター表の範囲を選択し、挿入タブからテーブルを選ぶと、データ追加時に表の範囲が自動拡張されます。
FILTER関数でテーブルの列を参照すれば、新しい商品やサイズを追加したときにも候補へ反映されやすくなります。
テーブル名は商品マスターのように内容が分かる名前へ変更しておくと、数式の意味を後から確認しやすくなります。
一方、INDIRECT関数と名前の定義を使う方法では、追加した候補が定義範囲に含まれているかを確認する必要があります。
【操作のポイント】候補が増える表はテーブル化し、追加後のプルダウン表示も確認します。
入力順序を分かりやすく示す方法
続いては、利用者が親項目から順に選べるようにする見せ方を確認していきます。
入力列は、分類、商品名、サイズのように左から右へ選択順に並べると自然です。
ヘッダーに「1 分類」「2 商品名」「3 サイズ」といった順番を示す文字を入れる方法もあります。
連動プルダウンは設定が正しくても、利用者が子項目から触ると候補が空に見えることがあります。
親項目を先に選ぶ必要があることを、入力欄の近くに短く案内すると問い合わせを減らせます。
入力規則の入力時メッセージを使い、セルを選んだときに「先に分類を選択してください」と表示する方法も有効です。
【操作のポイント】親から子への入力順が視線の流れと一致するよう、列の並びと案内文を整えます。
メンテナンス時の確認項目
続いては、候補の追加や修正を行った後に確認したい項目を解説していきます。
新しい分類を追加した場合は、1つ目のプルダウン候補、対応する商品候補、必要ならサイズ候補まで一連で登録します。
FILTER関数方式ではマスター表の条件列と抽出列が同じ行まで拡張されているかを確認します。
INDIRECT関数方式では、新しい分類と同じ名前の定義が作られているかを確認しましょう。
削除した候補を選んでいる既存データがないかをフィルターや検索で確認することも重要です。
候補マスターを変更したら、実際の入力セルで親項目から最後の項目まで選び直すテストを行うと安心です。
【操作のポイント】候補追加後は、元データだけでなく入力規則の表示まで実機で確認します。
まとめ エクセルでプルダウンリストを切り替える入力規則の複数条件設定
Excelの入力規則で複数条件を設定し、プルダウンリストを切り替える方法をまとめます。
Microsoft 365やExcel 2021以降なら、FILTER関数で条件に一致する候補を動的に抽出し、スピル範囲を入力規則の元の値へ指定する方法が便利です。
FILTER関数で2つの条件を指定する場合は、条件式を丸かっこで囲み、掛け算で結合します。
=FILTER(抽出範囲,(条件範囲1=条件1)*(条件範囲2=条件2),”該当なし”)
古いExcelを含めて使うなら、分類ごとに名前の定義を作り、入力規則の元の値へ「=INDIRECT($A2)」を指定する方法が実用的です。
親項目の文字列と名前の定義を一致させること、候補表の空白や表記ゆれをなくすこと、親項目を変更した後に子項目を確認することが成功の鍵になります。
分類、商品名、サイズのように入力順を整えた連動プルダウンを作れば、入力ミスを抑えながら、誰でも迷いにくいExcel表へ改善できるでしょう。
