Excel(エクセル）でプルダウン(ドロップダウン)を使うと、アンケートをとる時などに”〇”や”△”、”×”など特定の決まった項目を入力してもらうことが出来るので便利ですよね。
しかし、従業員リストや市区町村、郵便番号など、選択する件数が多いものに対してプルダウンを設定すると利用者側が使用しづらいことがあります。
そんな時にはプルダウンを２つや３つ連動させて設定することで選択する件数を絞ることが出来ます。
下の図のように、１つ目のプルダウンで選択した項目(主催部署)に連動させて、２つ目のプルダウンリストの選択数を減らすことで、利用者も簡単に、かつ誤りなくプルダウンが利用出来ます。
今回は、プルダウン（ドロップダウン）設定の応用編として、連動する２段階プルダウンの設定方法をご紹介します。
プルダウン(ドロップダウン)の設定方法(おさらい）
まずプルダウンの基本的な設定方法をまだ理解できていない方は、設定方法をおさらいしましょう。
Excelのプルダウンとは、ドロップダウンとも言います。
下図のようにセルの右側にある▼を押すと入力候補の項目が一覧表示され、その中から選択して入力できる入力規則のことを言います。プルダウンの対象セルに直接文字等を入力しようとしてもエラーが発生します。
手順１．プルダウンに使うリストを準備
空いているセルにプルダウンに使うリストを事前に作成しておきます。利用者が見える範囲で作成すると不恰好なので、利用しないシートに作成ましょう。
手順２．プルダウンに使うリストを準備
プルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
手順３．リストを選択
「データの入力規則」画面の「設定」タブを開き、入力の種類は「リスト」を選択します。次にリストの対象範囲を選択するために、元の値の「↑」ボタンをクリックします。
手順４．「プルダウンリスト」をセル範囲を選択
プルダウンに使うリストが入力されているワークシートをクリックして、対象リストのセル範囲を選択します。選択し終えたら、「データの入力規則」画面の「↓」をクリックします。
手順５．「データの入力規則」画面を閉じる
「元の値」にリストにするセル範囲が入力されていることを確認し、「OK」で画面を閉じます。
手順６．最終確認
プルダウンを設定したセルに▼が表示され、プルダウンリストが表示されていれば完了です。下のセルにもプルダウンを設定したい時は、セルの通常のコピペで入力規則もコピーされます。
プルダウンの基本的な操作方法は以下の記事でも紹介していますので、参考にしてみてください。
プルダウンを連動する作業の流れ
今回は下の表のような連動する２段階プルダウンの作成方法を紹介します。
表の「主催部署」列(２列目)のプルダウンで「調達部」「総務部」「経理部」を選択し、「担当者」列(３列目)のプルダウンで主催部署にあった従業員だけを表示するように設定します。
全体の作業の流れは以下のステップ１～ステップ４の４ステップとなります。
まず、ステップ1で１つ目と２つ目のプルダウン用のリストを準備します。
ステップ２は、１つ目のプルダウンを設定するシンプルな作業です。
１つ目のプルダウンに連動する２つ目のプルダウンを設定する作業を、ステップ３とステップ４で行います。
- ステップ１．プルダウンリストを準備
- ステップ２．１段階目のプルダウンを設定
- ステップ３．２段階目のリストに【名前】を付ける。
- ステップ４．２段階目のプルダウンをINDIRECT関数を使って設定
連動に利用する機能と関数
今回、新たに連動するプルダウンを設定するために、「名前」機能とINDIRECT関数を用います。
２つとも聞きなれない方が多いと思うので、手順を紹介する前に、機能の概要を紹介します。
「名前」機能
『名前』機能を使うと、セルやセル範囲や数式や固定値に、機能名のとおり「名前」を登録することが出来ます。
登録した「名前」は、数式の中で使うことでセルの引数の設定が簡単になります。
セル範囲に「名前」を付ける方法は、数式バーの左横、A列の列番号の上側に設置されている「名前ボックス」を利用します。
まず「名前」を付けたいセル範囲を選択します。
セルを選択した状態のままで「名前ボックス」に「名前」を入力して「Enter」キーを押します。「Enter」キーで確定しないと登録が完了しないので忘れないように注意が必要です。この作業で「名前」の登録は完了です。
「名前」が登録されたか確認します。セル範囲を再度選択すると、「名前ボックス」に登録した「名前」が表示されていれば無事登録が完了しています。
連動する２段階プルダウンでは、この「名前」リストを部署別従業員リストに使用します。
INDIRECT関数
INDIRECT関数は、参照するセルの文字列を利用して参照を求める関数です。
構文と引数は以下のようになっています。
=INDIRECT(参照文字列,[参照形式])
1番目の引数は「参照文字列」で入力は必須です。セル参照を表す文字列またはセル番号を指定します。
2番目の引数は「参照形式」で、この引数は省略が可能です。参照文字列で指定されたセル参照の種類を、論理値で指定します。今回はこの引数は利用しません。
これだけでは、どのような関数かイメージできないと思いますので、どのように使うか簡単に実例で紹介します。
上の表ではC3セルに「=INDIRECT(B3)」が入力されています。B3セルに入力されている文字列「E5」のセル番号をINDIRECT関数が参照して「社会」が表示されています。
このように、INDIRECT関数を使用すると数式を変更しないで、参照するセルを間接的に変更することができます。
２段階プルダウン(連動するプルダウン）を設定する方法
それでは、連動する２段階プルダウンの設定方法を紹介します。
各ステップごとに手順を載せていますので、順を追いながら作業してください。
ステップ１．プルダウンリストを準備
それでは、まず１つ目のプルダウンで選択する「部署名」と２つ目のプルダウンで選択する「担当者名」を１つの表に纏めます。
最終的に作成するプルダウンを適用する表と同じワークシートで作成すると見栄えが良くないと思われる方は、使用していない新規のワークシート(例：部門人員表)で表を作成してください。
また、このリストの並びがプルダウンの並び順を決定するので、プルダウンの表示に優先順位を付けたい場合は、上側もしくは左側にリストを配置してください。
ステップ２．１つ目のプルダウンを設定
続いて、ステップ２では「主催部署」列にプルダウンを設定します。
手順１．「データの入力規則」をクリック
プルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
手順２．リストを選択
「データの入力規則」画面の「設定」タブを開き、入力の種類は「リスト」を選択します。次にリストの対象範囲を選択するために、元の値の「↑」ボタンをクリックします。
手順３．「プルダウンリスト」をセル範囲を選択
プルダウンに使うリストが入力されているワークシートをクリックして、対象リストのセル範囲を選択します。選択し終えたら、「データの入力規則」画面の「⇩」をクリックします。
手順４．１つ目のプルダウンの完成
「データの入力規則」画面を「OK」で画面を閉じれば、下のように１つ目(１段階目）のプルダウンの完成です。
ステップ３．２段階目のリストに『名前』を付ける。
ここからが本題です。
ステップ３では、ステップ１で作成したプルダウンリストのセル範囲に「名前」を付けます。
上の見出しで紹介したとおりエクセルの「名前」機能を使うと、特定のセル範囲に「名前」を付けて関数などに使用できるようにすることが出来ます。
手順１．「総務部」列に「名前」を付ける
「総務部」列のB3～B6のセル範囲を選択して、左上の「名前ボックス」に、”総務部”と入力して「Enter」キーを押します。
手順２．「調達部」列に「名前」を付ける
同様に、「調達部」列のC3～C6のセル範囲を選択して、左上の「名前ボックス」に、”調達部”と入力して「Enter」キーを押します。
手順３．「経理部」列に「名前」を付ける
同様に、「経理部」列のD3～D6のセル範囲を選択して、左上の「名前ボックス」に、”経理部”と入力して「Enter」キーを押します。
手順４．「名前」を確認
「名前」の登録が無事完了したか不安な場合は、名前を登録したセル範囲を選択してみてください。「名前ボックス」に登録した「名前」が表示されていれば大丈夫です。
もし、「名前」の登録を失敗してしまった場合は、「数式」タブ→「名前の管理」から、「削除」をクリックすれば再度名前の登録をやり直すことが可能です。
ステップ４．２段階目のプルダウンをINDIRECT関数を使って設定
２つ目のプルダウン用に「名前」を設定し終えたら、最後に２段階目のプルダウンを設定します。
手順１．「データの入力規則」をクリック
２つ目のプルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
手順２．INDIRECT関数を『元の値』欄に挿入
「データの入力規則」画面が開きますので、入力の値の種類は「リスト」を選びます。
次に、元の値の入力欄に「=INDIRECT(C3)」と数式を入力します。「$C3」や「$C$3」など絶対参照を使わないように気を付けて下さい。
このセルC3は、すぐ左隣の１つ目のプルダウンの対象セルです。
上の見出しでも紹介しましたが、INDIRECT関数は参照するセルの文字列を利用して参照を求める関数です。
今回参照するセルとは、C3セルです。
C３セルは１つ目のプルダウンで「名前」登録したものと同じ名称の「調達部」「設計部」「経理部」しか選べないので、間接的に「名前」の登録したセル範囲を参照することが出来ます。
このINDIRECT関数と「名前」の利用により、2つ目のプルダウンは１つ目のプルダウンと連動する、という仕組みです。
手順３．２つ目のプルダウンを確認
２つ目のプルダウンを選択すると、下のように１つ目のプルダウンの「部署名」に合った「担当者」のみが表示されます。
手順４．２行目以降にプルダウンのセルをコピ―
２行目以降に同じ作業を行う必要はありません。
プルダウンを設定したセルをコピーして、下のセル範囲に貼り付けることで、プルダウンの入力規則も貼り付きます。以上で、２段階プルダウンの設定は完了です。
２つ目のプルダウンリストを追加する方法
「名前」を登録した２つ目のプルダウンにリストを追加する方法を紹介します。
下の表のように、「調達部」に「藤川」「大崎」が追加になった場合の操作方法を紹介します。
登録した「名前」のセル範囲を変更するために、「数式」タブの「名前の管理」をクリックします。
「名前の管理」画面から変更したい「調達部」を選択します。
「参照範囲」に入力されているセル範囲を変更して、「閉じる」を押します。
「=部門別人員表!$C$3:$C$6」⇒「=部門別人員表!$C$3:$C$8」
「名前の参照への変更を保存しますか？」と問われるので、「はい」を選択します。
２つ目のプルダウンを開いて、追加した担当者が追加されていれば完了です。
２段階プルダウン作成のポイントは、INDIRECT関数と「名前」
連動した２段階のプルダウン設定手順はご理解いただけたでしょうか。INDIRECT関数と「名前」登録は普段使い慣れていない方が多いと思いますが、慣れると短い時間で設定出来るようになります。
また、今回は２段階設定の対応をご紹介しましたが、３段階、４段階も同様の手順で設定できますので、ぜひ状況に応じてご活用ください。