Excelのプルダウンリストを連動させる方法

商品マスタ（分類・商品CD・商品名をまとめた一覧）をもとに分類別の候補表を作り、名前の定義とINDIRECT関数で2つのプルダウンを連動させます。
プルダウン(入力規則)の基本を既にマスターしている方向けの内容です。

基本操作を確認したい方は、Excelでプルダウンリストを作成する方法をご覧ください。

まずは、選択結果を入力する表と、候補の元になる商品マスタ・分類別一覧表を同じシートに用意しましょう。

連動に使う表とセルを準備する

作例では「入力」という名前のシートを使います。
同じシートの左側に入力表、右側に商品マスタと分類別の候補表を置きます。

入力表と商品マスタの位置をそろえる

次の配置で表を用意します。入力欄は2行目から使います。

• A列からD列：プルダウン選択後の結果を表示させる入力表
• F列からH列：商品マスタ
• J列からK列：分類別のコード候補

次に、商品マスタ(F列からH列)には次の7件を入力します。

商品名は対応を確認するための列で、C列のプルダウンには商品CDを表示します。

分類ごとに商品CDの候補を並べる

マスタの商品CDを、果物と野菜に分けてJ列・K列へ並べます。
見出しの「果物」「野菜」は、次の見出しで説明する名前範囲で活用していきます。

野菜の商品CDはK３までの2件ですが、今後の商品追加を考慮して、名前の定義ではK6までの空白セルも含めて設定します。
空白セルを含めることで、後から商品CDを追加しても、設定済みの範囲内であれば名前の定義を変更する必要がありません。
ただし、プルダウンに空白の候補が表示される場合があります。
不要な空白を表示したくない場合は、入力済みのセルだけを参照範囲に指定してください。

分類別の候補に名前を付け、入力規則を設定する

分類ごとに商品CDの候補を用意したら、Excelの「名前の定義」と「データの入力規則」を使って、2つのプルダウンを連動させます。

今回は、B2セルで「果物」または「野菜」を選択すると、C2セルに表示される商品CDの候補が切り替わるように設定します。

設定は次の順番で進めます。

1. 分類別の商品CDの範囲に名前を付ける
2. B2セルに分類を選択するプルダウンを設定する
3. C2セルにINDIRECT関数を使ったプルダウンを設定する

分類別の商品CDの範囲に名前を付ける

最初に、J列とK列へ入力した商品CDの候補に、それぞれ「果物」「野菜」という名前を付けます。

「数式」タブの「名前の管理」から新しい名前を作り、次のように設定してください。

名前の「参照範囲」には、商品CDの候補表を作成したシート名とセル範囲を指定します。

名前	参照範囲
果物	='入力'!$J$2:$J$6
野菜	='入力'!$K$2:$K$6

今回はシート名を「入力」としています。
別のシート名で表を作成した場合は、実際のシート名に置き換えてください。

同じ手順で「野菜」も登録します。

野菜の商品CDはK2:K3の2件ですが、今後の商品追加を考慮して、空白のK4:K6も含めて名前を定義します。

このように設定しておけば、確保した範囲内に商品CDを追加しても、名前の定義を変更する必要がありません。

ただし、プルダウンに空白の候補が表示される場合があります。

分類セルB2の「元の値」を設定する

次に、B2セルへ分類を選択するプルダウンを設定します。

今回はJ1・K1に入力した「果物」「野菜」を選択候補として使用します。

1. B2セルを選択します。
2. 「データ」タブから「データの入力規則」をクリックします。
3. 「設定」タブの「入力値の種類」で「リスト」を選択します。
4. 「元の値」に =$J$1:$K$1 と入力します。
5. 「OK」をクリックして設定を保存します。

設定後、B2セルのプルダウンを開き、「果物」と「野菜」が候補として表示されることを確認してください。

続けてC2セルの入力規則を設定するため、B2セルでは「果物」を選択しておきます。

商品CDセルC2をINDIRECTで連動させる

最後に、C2セルへ商品CDを選択するプルダウンを設定します。

B2セルで選んだ分類に応じて商品CDの候補を切り替えるため、INDIRECT関数を使用します。

B2セルで「果物」を選択した状態で、次の操作を行ってください。

1. C2セルを選択します。
2. 「データ」タブから「データの入力規則」を開きます。
3. 「入力値の種類」で「リスト」を選択します。
4. 「元の値」に =INDIRECT(B2) と入力します。
5. 「OK」をクリックして設定を保存します。

INDIRECT関数は、文字列をセルや名前の参照として扱うための関数です。

今回の設定では、B2セルの分類名を、あらかじめ定義した同じ名前の参照範囲として使用します。

例えば、B2セルで「果物」を選択した場合、INDIRECT関数は名前「果物」に対応するJ2:J6を参照します。

その結果、C2セルのプルダウンにはS01、S02、S05、S06、S07の5件の商品CDが表示されます。

B2セルを「野菜」に変更すると、参照先がK2:K6に切り替わり、S03、S04が商品CDの候補になります。

このように、B2セルで分類を変更するだけで、C2セルのプルダウンに表示される商品CDの候補が切り替わります。

ただし、今回の設定で切り替わるのはプルダウンの選択候補です。商品CDを自動入力したり、分類変更時に選択済みの商品CDを自動消去したりする機能ではありません。

また、今回使用した =INDIRECT(B2) の「B2」は相対参照です。

入力規則を下の行にコピーすると、参照先もB3、B4と変わるため、各行の分類に応じた商品CDを表示できます。

なお、$B2 のようにB列だけを固定する参照方法もありますが、今回のように下方向へコピーするだけなら B2 のままで問題ありません。

これで、分類に連動して商品CDの候補を切り替える設定は完了です。

次は、複数行への入力規則のコピーや、商品を追加した場合の設定方法を確認していきましょう。

複数行と商品追加で使う

ここまでの設定で、分類に応じて商品CDの候補を切り替える2段階プルダウンが完成しました。

実際に利用する場合は、複数行への入力規則のコピーや、商品・分類を追加したときの設定変更も必要になります。

ここでは、2段階プルダウンを継続して利用するための操作と注意点を紹介します。

B2・C2の入力規則を次の行にコピーする

設定した2段階プルダウンは、入力規則をコピーすることで、3行目以降でも利用できます。

今回はB2:C2に設定した２つの入力規則を、B3:C3へコピーします。

1. B2:C2を選択してコピーします
2. 貼り付け先のB3セルを選択します
3. 「形式を選択して貼り付け」を開き、「入力規則」を選択します
4. 「OK」をクリックして、B3:C3に入力規則を貼り付けます
5. B3で「野菜」を選択し、C3のプルダウンに野菜の商品CDが表示されることを確認します

入力規則をコピーするときは、セルの値まで複製しないように、「入力規則」のみを貼り付けることがポイントです。

また、C2セルに設定した =INDIRECT(B2) は相対参照のため、C3セルでは =INDIRECT(B3) に変わります。

これにより、それぞれの行の分類名に応じた商品CDを選択できます。

次の画像では、2行目で「果物」のS01、3行目で「野菜」のS03を選択しています。

分類を変更したら商品CDを選び直す

2段階プルダウンを利用する際は、分類を変更した後の商品CDに注意してください。

例えば、B2セルで「果物」、C2セルで商品CD「S01」を選択していたとします。

この状態からB2セルを「野菜」に変更すると、C2セルのプルダウン候補は野菜の商品CDであるS03、S04に切り替わります。

しかし、C2セルに入力済みの「S01」は自動的には消去されません。

このように、分類は「野菜」なのに、商品CDには果物の「S01」が残ることがあります。

Excelの入力規則は、基本的に入力時の値を制限する機能であり、参照先の候補が変更されても、入力済みの値を自動的に更新・消去するわけではありません。

そのため、分類を変更した場合は、次の操作を行ってください。

1. B列の分類名を変更します
2. C列に以前の商品CDが残っている場合は、Deleteキーなどでクリアします
3. C列のプルダウンを開き、新しい分類に対応した商品CDを選び直します

今回紹介した設定では、分類変更時に商品CDを自動消去する機能はありません。

入力済みのデータに不整合が生じないよう、分類と商品CDをセットで確認することが重要です。

商品を追加したら候補表と定義範囲を更新する

新しい商品を追加する場合は、商品マスタと分類別の商品CD候補を更新します。

ただし、名前の定義で指定した範囲内に空白セルがあるかどうかによって、必要な操作が変わります。

果物に商品CDを追加する場合

今回は例として、果物に新しい商品CD「S09」を追加します。

1. F～H列の商品マスタに、分類「果物」、商品CD「S09」、対応する商品名を追加します
2. J列の商品コード候補の末尾、J7セルに「S09」を入力します
3. 「数式」タブの「名前の管理」を開き、名前「果物」を選択します
4. 参照範囲を ='入力'!$J$2:$J$6 から ='入力'!$J$2:$J$7 に変更します
5. B列で「果物」を選び、C列のプルダウンに「S09」が追加されたことを確認します

画像のように、J列へ追加したS09が商品CDの候補にも表示されれば、設定は完了です。

なお、商品マスタに新しい商品を登録するだけでは、別に作成した分類別候補表や、名前の定義で固定した参照範囲は自動的に更新されません。

野菜に商品CDを追加する場合

一方、野菜は名前の参照範囲をK2:K6に設定しており、K4:K6には追加用の空白セルがあります。

そのため、新しい野菜の商品CDをK4セルに追加する場合は、名前の参照範囲を変更する必要はありません。

商品マスタとK列の候補表に商品CDを追加すれば、プルダウンの選択候補にも反映されます。

このように、あらかじめ空白セルを含めて範囲を設定しておくと、商品追加時の手間を減らせます。

ただし、K6を超えて商品を追加する場合は、名前の参照範囲を広げる必要があります。

また、空白セルを含む範囲を指定している場合は、プルダウン内にも空白の候補が表示されることがあります。

新しい分類を追加する場合

果物や野菜とは別に、新しい分類を追加する場合は、親のプルダウンに表示する分類名も増やす必要があります。

例えば、L列へ新しい分類を追加する場合は、次の手順で設定します。

1. 商品マスタに、新しい分類の商品を登録します
2. L1セルに分類名を入力し、L2以降に対応する商品CDを並べます
3. L列の商品CD候補に、L1の分類名と同じ名前を定義します
4. B2セルの入力規則を開き、「元の値」を =$J$1:$K$1 から =$J$1:$L$1 に変更します
5. 3行目以降にも、更新した分類選択用の入力規則を反映します

これで、新しく追加した分類もB列のプルダウンから選択できるようになります。

なお、新しい分類を追加しても、C列の =INDIRECT(B2) は基本的に変更する必要はありません。

分類名と同じ名前を正しく定義しておけば、INDIRECT関数が対応する商品コードの候補範囲を参照します。

このように、名前の定義と入力規則を適切に管理することで、商品や分類が増えた場合にも2段階プルダウンを継続して利用できます。

連動しない・候補が増えない場合の確認点

2段階プルダウンが正しく動作しない場合は、分類名・名前の定義・入力規則の設定を順番に確認しましょう。

特にINDIRECT関数を使用する場合は、プルダウンで選択する分類名と、定義した名前が一致していることが重要です。

よくある症状と確認するポイントを、次の表にまとめました。

症状	確認するポイント
1つ目のプルダウンに分類名が表示されない	入力規則が「リスト」になっているか、「元の値」に分類名の一覧を正しく指定しているか確認します。
2つ目のプルダウンに候補が表示されない	分類名が選択されているか、選択した分類名と名前の定義が一致しているか確認します。
別の分類の商品コードが表示される	INDIRECT関数が正しい分類セルを参照しているか、名前の参照範囲が正しいか確認します。
複数行にコピーすると正しく連動しない	各行のINDIRECT関数が、同じ行の分類セルを参照しているか確認します。
プルダウンに空白が表示される	名前の参照範囲に空白セルが含まれていないか確認します。
商品を追加しても候補が増えない	商品マスタと分類別候補表を更新し、必要に応じて名前の参照範囲を広げます。
新しい分類が選択できない	親のプルダウンの参照範囲と、新分類の商品コードに対応する名前の定義を確認します。
分類を変更しても古い商品コードが残る	選択済みの値は自動消去されません。古い商品コードをクリアして選び直します。
プルダウンの矢印が表示されない	入力規則の「リスト」設定と「ドロップダウン リストから選択する」のチェックを確認します。

設定したプルダウンが連動しない場合は、まず1つ目の分類が正しく選択されているかを確認し、続いて2つ目の入力規則と名前の参照範囲を確認してください。

なお、名前の定義は、入力規則を設定するシートから参照できる有効範囲にしておく必要があります。

入力規則そのものの設定方法については、「Excelでプルダウンリストを作成する方法」も参考にしてください。

まとめ

今回は、Excelの名前の定義とINDIRECT関数を使って、分類に応じて商品コードの候補が切り替わる2段階プルダウンの作成方法を紹介しました。

設定のポイントは、分類別の商品コード候補に名前を付け、2つ目のプルダウンからINDIRECT関数で参照することです。

B列で「果物」「野菜」などの分類を選択すると、C列には対応する商品コードの候補が表示されます。

また、入力規則をコピーすれば複数行でも利用でき、名前の参照範囲を適切に管理することで、商品や分類の追加にも対応できます。

ただし、分類を変更しても選択済みの商品CDは自動的に消去されません。入力内容に不整合が生じないよう、分類変更時は商品コードも確認してください。

名前の定義とINDIRECT関数を組み合わせることで、入力ミスを減らし、商品や分類に応じたデータ入力を効率化できます。