仕事効率化

【エクセル】連動型プルダウンリストで選択肢を絞り込む方法とは?

エクセルのプルダウンリストで選択肢を絞り込みたいと思ったことはありませんか?

プルダウンリストは答えを選択するだけでセルにデータを入力できるため、エクセル初心者やパソコンが苦手な方でも簡単に使えて便利ですよね。
一般的なプルダウンリスト
しかし、画面に膨大な選択肢が表示されてしまうと、目的のものを探すだけでも一苦労。

かえって作業効率を低下させてしまうかもしれません。

もしプルダウンリストに表示される選択肢が膨大な量だったら…
選択肢が多すぎるプルダウンリスト

ナル
ナル
目的のデータが全然見つからない!

そこで本記事では、プルダウンリストの選択によって、次のプルダウンリストに表示される内容が変更する連動型プルダウンリスト」の作り方をお伝えします。

連動型プルダウンリストは名前の定義とINDIRECT関数で作成できる

エクセルの機能である「名前の定義」と、関数の「INDIRECT」を組みわせることで、プルダウンリストの選択肢を絞り込むことができます。

たとえば、大分類のプルダウンリストで第3工場を選択したら、中分類のプルダウンリストではDラインとEラインに絞り込まれます。
連動するプルダウンリスト
そして、もし中分類でEラインを選んだら、小分類の選択肢は「防犯用品・配線器具・ペット用品」に限定されるというものです。
中分類の選択で小分類の選択肢が変わる
お伝えする方法を応用すれば、4段階、5段階のプルダウンリストにも利用することができます。

スポンサーリンク


名前の定義とINDIRECT関数がプルダウンリスト作成に必要な理由

エクセルの連動型プルダウンリストの作成の前に、「名前の定義」と「INDIRECT関数」について簡単にご説明します。

連動型プルダウンリストの作成に必要な知識①名前の定義

エクセルは、特定のセルやセル範囲に対して名前を付けることができます。

練習としてやってみます。

A1セルからA5セルを範囲選択して、数式タブから「名前の定義」をクリック。
範囲選択して名前の定義
表示された新しい名前ダイアログボックスの「名前」の欄に「名前の練習」と入力します。
参照範囲はそのままでOK。
名前を付ける
シート左上のセル名に、「名前の練習」が追加されます。

この欄を「名前の練習」に切り替えると、A1セルからA5セルが自動的に範囲選択されるようになりました。
セル範囲に名前が付いた

ナル
ナル
セル範囲に名前を付けるメリットはあるの?

たとえば、C1セルにA1セルからA5セルの合計をSUM関数で求めるとします。

通常なら式は「=SUM(A1:A5)」となりますよね。
通常のSUM関数の式
セル範囲に名前を付けると、「=SUM(名前の練習)」というように、関数の引数を文字列で入力ができるようになります。
名前を引数にしたSUM関数
セル範囲に名前を付けることで、数式の意味や選択範囲が分かりやすくなるというメリットがあります。

スポンサーリンク



連動型プルダウンリストの作成に必要な知識②INDIRECT関数

INDIRECT関数は文字列をセル参照やセル範囲として数式に利用できます。
INDIRECT関数の引数
INDIRECTは「間接的に」という意味で、その名のとおり若干まわりくどい関数です。

下図でA1セルに入力されている「INDIRECT関数の練習」という文字列をD1セルに取り出すとしたら、「=A1」と入力しますよね。
A1セルを参照する
しかし、D1セルに「=INDIRECT(A1)」とINDIRECT関数を入力しても、「#REF!」エラーが表示されます。
INDIRECT関数のエラー
INDIRECT関数を利用するときには、引数の参照文字列は「文字列」でなければいけません。

そのため、引数を「A1」ではなく、「”A1″」と文字列として入力すれば、正しくA1セルの値を返すことができるようになります。

エクセルでは「””」で囲んだ数値やセル参照は文字列になります。

=INDIRECT(“A1”)

引数を文字列で入力
または、B1セルに「A」、C1セルに「1」を入力して、そこをINDIRECT関数で参照することでも、A1セルの「INDIRECT関数の練習」を抽出することができます。
文字列を結合して引数にする

ナル
ナル
なんだか面倒な関数ですね…

ちょっとクセのある関数ではありますが、INDIRECTは他のシートのセルを参照するときにも利用できます。

下図で、練習シート2のA1セルを参照したいとしたら、式は次のようになります。

=INDIRECT(“練習シート2”&”!A1″)

別のシートのデータを抽出
別々のシートに入力してあるデータを1つのシートに集めたいときには、文字列で参照できるINDIRECT関数は重宝しますよ。

【エクセル】最終行のデータを関数で取得する方法(別シート対応)エクセルで最終行のデータを取得したいと思ったことはありませんか?2つの関数を組み合わせるだけで、可変する表にも対応する式をつくることができます。応用編として別シートの最終行データを取得する方法もご紹介!...

 

スポンサーリンク


連動型プルダウンリストの作成に必要な知識③名前の定義&INDIRECT関数

下図の表はB2セルの商品コードと一致した商品名と単価を、VLOOKUP関数で商品リストから抽出しています。
VLOOKUP関数
VLOOKUP関数については下記の記事が参考になります。

【エクセル】VLOOKUP関数の使い方とすぐにできる6つの応用技VLOOKUP関数はビジネスで大活躍の関数。しかし、初心者にはとっくきにくいと苦手意識を持っている方も多いです。そこでエクセル歴20年の筆者が分かりやすく解説。さらに新エクセル関数XLOOKUPについても紹介。...
【エクセル】セルの参照を固定(列のみ・行のみ)する方法とは?エクセルで数式をコピーしたら計算がおかしい…そんな経験はありませんか? 下図のC2セルには、静岡店の売上構成比を求めるために「=B...

VLOOKUP関数の引数である「範囲」には、商品リストAと商品リストBがあります。

厄介なことに、同じ商品コードがそれぞれのリストに含まれています。

ナル
ナル
商品コードが重複してしまうから、求めたい商品名と単価を抽出できないです。

商品リストに名前を付けてみます。

G3セルからI5セルに商品リストAと名前を付け、G8セルからI10セルに商品リストBと名前を付けます。
商品リストに名前をつける
そして、B3セルに入力されているVLOOKUP関数の引数(範囲)に、INDIRECT関数を利用します。
VLOOKUP関数の範囲
INDIRECT関数を組み合わせてVLOOKUP関数の式が完成しました。

=VLOOKUP($B$3,INDIRECT($A$3),2,FALSE)

A3セルに商品リストAが入力されていればG3セルからI5セル、商品リストBが入力されていればG8セルからI10セルをVLOOKUP関数の範囲として自動的に判別してくれます。
商品リストをINDIRECT関数で判別
このように、セル範囲に名前をつけて文字列で管理する機能と、文字列で参照するINDIRECT関数の愛称は抜群

2つを組み合わせることで、1つ目のプルダウンリストの選択(文字列)に連動して2つ目のプルダウンリストに表示する内容(範囲)を絞り込むわけです。

スポンサーリンク



連動型プルダウンリストで選択肢を絞り込む方法

家電製造工場の一覧表を利用して連動型プルダウンリストを作成します。
プルダウンリストと家電製造工場のリスト
プルダウンリストは3つ作成します。

1つ目は大分類「第1工場・第2工場・第3工場」。

2つ目の中分類は各工場に2つずつ。

3つ目の小分類は、各工場のラインごとに異なります。

プルダウンリストとデータ元になる表は、別のシートに準備しても問題ありません。

連動型プルダウンリストで選択肢を絞り込む方法①1つ目のプルダウンの作成

B2セルを選択した状態で、データタブのデータの入力規則をクリック。
データの入力規則
入力値の種類を「リスト」に変更し、元の値の欄に「第1工場,第2工場,第3工場」と手入力します。

文字列と文字列の間は「,」(半角カンマ)で区切ります。
元の値を入力

数字やアルファベットを含む文字列の場合、半角・全角にも気をつけてください。

B2セルを選択するととなりに▼マークが表示。

プルダウンリストでデータ入力ができるようになります。
1つ目のプルダウンリスト
プルダウンリストの内容を修正したい場合には、B2セルを選択してデータの入力規則。

元の値を修正すれば、プルダウンリストの内容を変更できます。
プルダウンリストの内容を変更
プルダウンリストを削除したいときは、入力の種類をすべての値に戻せばOKです。

スポンサーリンク


連動型プルダウンリストで選択肢を絞り込む方法②2つ目のプルダウンの作成

1つ目(大分類)の選択内容で2つ目(中分類)の選択肢が絞り込まれるプルダウンリストを作成します。
連動するプルダウン
第1工場の中分類であるAラインとBライン(E3セルとF3セル)を範囲選択して、数式タブから名前の定義をクリック。
中分類を選択して名前の定義
新しい名前ダイアログボックスが表示されます。

参照範囲は自動的に入力されるため、名前の箇所のみ変更します。

中分類「AラインとBライン」の1つ上の階層である大分類「第1工場」と入力。
第1工場と入力
続いて、G3セルとH3セルを名前の定義で「第2工場」。

I3セルとJ3セルも同様にして「第3工場」と名前を付けていきます。

名前の定義で「第1工場」、「第2工場」、「第3工場」ができました。
中分類に名前が付いた
次に、B3セルを選択してデータタブからデータの入力規則。
データの入力規則
入力値の種類をリストに変更。

ここまでは、1つ目(大分類)のプルダウンリストを作成したときと同じですが、元の値の入力にINDIRECT関数を利用します。

=INDIRECT($B$2)

元の値にINDIRECT関数
INDIRECT関数がB2セルに入力された文字列の範囲を参照します。

たとえば、B2セルが「第1工場」となっていたら、「第1工場」と名前を付けた範囲であるAラインとBライン(E3セルとF3セル)が参照されるという仕組みです。
大分類により中分類が連動
大分類のプルダウンリストを第3工場に変更すると、中分類のプルダウンリストはDラインとEラインを表示しました。
選択肢を変更すると連動する
1つ目(大分類)のプルダウンの選択により、2つ目(中分類)のプルダウンの内容が絞り込まれることが確認できました。

2段階の連動型プルダウンリストを作成する場合は、以上の手順で完了です。

スポンサーリンク



連動型プルダウンリストで選択肢を絞り込む方法③3つ目のプルダウンの作成

2つ目(中分類)のプルダウンリストに連動して、3つ目(小分類)のプルダウンリストが絞り込まれる設定をします。

基本的には2つ目(中分類)のプルダウンリストを作成したときと同じです。

第1工場のAラインの商品であるE3セルからE5セルを選択した状態で名前の定義。
小分類の名前の定義
気を付けたいのが名前の付け方。

単純に小分類の1つ上の階層である中分類の名前を付けてはいけません。

ナル
ナル
中分類にAラインが2つあるからだね!

第1工場にも第2工場にも「Aライン」が存在するため、Aラインを名前にしてもINDIRECT関数がセル参照範囲を正しく判断ができません。
中分類に同じ名前が存在
そこで、大分類と中分類を結合した名前をセル範囲に付けることにします。

「第1工場」の「Aライン」なら「第1工場Aライン」。

「第2工場」の「Aライン」なら「第2工場Aライン」。
重複しない名前を付ける
これなら名前が重複することはないですね。

同様に、残りの小分類にも名前を付けます。
他の小分類にも名前を付ける
名前の付け方を間違えたり、修正したい場合には、数式タブの「名前の管理」で一覧を確認することができます。
数式タブの名前の管理
下図のようになれば完璧です。
小分類の名前
それでは、仕上げとしてB4セルにプルダウンリストを設定しましょう。

B4セルを選択してデータの入力規則。
小分類にデータの入力規則
入力値の種類をリストに変更してから元の値を入力します。

=INDERECT($B$2&$B$3)

元の値にINDIRECT関数
B2セルが「第2工場」でB3セルが「Aライン」なら「第2工場Aライン」という文字列の範囲をINDIRECT関数が参照してくれます。

3つ目のプルダウンリストであるB4セルには、第2工場のAラインで製造している家電のみが選択肢として表示されていますね。
選択肢が絞り込まれている
大分類と中分類を変更すると、連動して小分類のリストも変更されます。
連動型プルダウンリストの完成
以上で、連動型プルダウンリスト(3段階)の完成となります。

スポンサーリンク


まとめ

今回ご紹介した方法で、さらに深い階層のプルダウンリストを作成することも可能です。

データ入力の効率化にもなりますし、選択肢が絞られることで誤入力も軽減されますね。

社員名簿、住所録、商品リスト…使い道はたくさんあります。

エクセルでプルダウンリストを利用している方は、ぜひこの機会に連動型にしてみてください。
スポンサーリンク