エクセルでプルダウン(ドロップダウン)を使うと、セルから決まった文字列を選択することが出来ます。
ただ、プルダウンリストが多すぎると、文字列を探すのが大変になるため、逆に不便に感じてしまいます。
そんな時にはプルダウンを連動させて設定することで、選択する件数を絞ることが出来ます。
以下の記事では、２つのプルダウンを連動させて、選択する項目数を減らす方法を紹介しました。
今回は、３つのプルダウンを連動させて、さらに項目数を限定する方法を紹介します。
部門、課、氏名をプルダウンで選択する時などに便利なテクニックだよ。
２段階プルダウンを設定する方法(おさらい)
２段階プルダウンを設定するときは、通常のプルダウンの設定に加えて、INDIRECT関数と「名前」機能を利用しました。
今回紹介する３段階プルダウンも、基本的には同様のテクニックを利用します。
プルダウンの基本操作をあらためて確認したい方は、下の２段階プルダウンの設定に関する記事を参考にしてください。
プルダウンの連動に利用する機能と関数
３つの連動するプルダウンを設定するためには、「名前」機能とINDIRECT関数を用います。
２つの連動するプルダウンを設定する時にも使う機能です。
２つとも聞きなれない方が多いと思うので、手順を紹介する前に、機能の概要を紹介します。
「名前」機能で、セル範囲に「名前」を登録
『名前』機能を使うと、セルやセル範囲や数式や固定値に、機能名のとおり「名前」を登録することが出来ます。
登録した「名前」は、数式の中で使うことでセルの引数の設定が簡単になります。
セル範囲に「名前」を付ける方法は、数式バーの左横、A列の列番号の上側に設置されている「名前ボックス」を利用します。
まず「名前」を付けたいセル範囲を選択します。
セルを選択した状態のままで「名前ボックス」に「名前」を入力して「Enter」キーを押します。「Enter」キーで確定しないと登録が完了しないので忘れないに注意が必要です。この作業で「名前」の登録は完了です。
「名前」が正しく登録されたか確認します。
セル範囲を再度選択すると、「名前ボックス」に登録した「名前」が表示されていれば無事登録が完了しています。
３つを連動させるプルダウンでは、この「名前」機能を使用します。
「名前」機能を詳しくもっと知りたい方は、以下の記事を参考にしてみてください。
INDIRECT関数は、プルダウンの設定時に活躍
INDIRECT関数は、参照するセルの文字列を利用して参照を求める関数です。
構文と引数は以下のようになっています。
=INDIRECT(参照文字列,[参照形式])
1番目の引数は「参照文字列」で入力は必須です。セル参照を表す文字列またはセル番号を指定します。
2番目の引数は「参照形式」で、この引数は省略が可能です。参照文字列で指定されたセル参照の種類を、論理値で指定します。今回はこの引数は利用しません。
これだけでは、どのような関数かイメージできないと思いますので、どのように使うか簡単に実例で紹介します。
下の表ではC3セルに「=INDIRECT(B3)」が入力されています。
B3セルに入力されている文字列「E5」のセル番号をINDIRECT関数が参照して「社会」という文字が表示されています。
このように、INDIRECT関数を使用すると数式を変更しないで、参照するセルを間接的に変更することができます。
普段は活躍しない関数ですが、２段階プルダウン、３段階プルダウンの設定の時に役立ちます。
INDIRECT関数を使った実例を詳しくもっと知りたい方は、以下の記事を参考にしてみてください。
３段階プルダウンを設定する方法
それでは、連動する３段階プルダウンの設定方法を紹介します。
今回は、「部門」・「課」・「担当者名」の３つの項目を使ったプルダウンの設定を紹介します。
各ステップごとに手順を載せていますので、手順１～手順５に沿って作業してください。
手順１．プルダウンリストを準備
まずは、１つ目のプルダウンで選択する「部門」と２つ目のプルダウンで選択する「課」を１つの表に纏めます。
このリストの並びがプルダウンリストの表示順に影響します。プルダウンの上側に表示したい場合は、部門は左側に、課は上側に入力してください。
次に、隣の空いたスペースに、２つ目のプルダウンで選択する「課」と課に所属する「担当者名」を１つの表に纏めます。
２つの表をそれぞれ異なるシートに作成しても問題ありません。
以上で、手順１の作業は完了です。
手順２．１つ目のプルダウンを設定
続いて、「部門」列にプルダウンを設定します。
プルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
「データの入力規則」画面の「設定」タブを開き、入力の種類は「リスト」を選択します。次にリストの対象範囲を選択するために、元の値の「↑」ボタンをクリックします。
手順１で作成した部門と課を纏めた表から、部門が入力されたセル範囲を選択します。選択し終えたら、「データの入力規則」画面の「↓」をクリックします。
「データの入力規則」画面の元の値にセル範囲が選択されたことを確認し、「OK」を押して画面を閉じてください。
下のように、１つ目(１段階目）のプルダウンが完成します。
２行目以降に同じ作業を行う必要はありません。
プルダウンを設定したセルをコピーして、下のセル範囲に貼り付けることで、プルダウンの入力規則も貼り付きます。以上で、手順２の設定は完了です。
手順３．リストに『名前』を付ける
ここからが本題です。
今度は、手順１で作成した「部門」「課」「担当者」をまとめた表の各列に「名前」を登録します。
上の見出しで紹介したとおりエクセルの「名前」機能を使うと、特定のセル範囲に「名前」を付けて、関数に使用できるようにすることが出来ます。
まず、「総務部」列のB3～B4のセル範囲を選択して、左上の「名前ボックス」に、「総務部」と入力して「Enter」キーを押します。
登録する「名前」は、必ず表の見出しと同じ名前にしてください。
例えば、「総務」と名前を登録して、表の見出しが「総務部」では、うまくプルダウンが連動しません。
同様に、右側の「経理部」列のC3～C4のセル範囲を選択して、左上の「名前ボックス」に、「経理部」と入力して「Enter」キーを押します。
この作業を繰り返し、「経理部」列の右側の列に対しても、すべて名前を登録します。
「名前」の登録が完了したか不安な場合は、名前を登録したセル範囲を選択してみてください。「名前ボックス」に登録した「名前」が表示されていれば正しく処理できています。
もし、「名前」の登録を失敗してしまった場合は、「数式」タブ→「名前の管理」から、「削除」をクリックすれば、もう一度、名前の登録をやり直すことができます。
手順４．２段階目のプルダウンをINDIRECT関数を使って設定
「名前」を設定し終えたら、２段階目のプルダウンを設定します。
手順４－１．「データの入力規則」を選択
まず、２つ目のプルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
手順４－２．INDIRECT関数を『元の値』欄に挿入
「データの入力規則」画面が開きますので、入力の値の種類は「リスト」を選びます。
次に、元の値の入力欄に「=INDIRECT(B3)」と数式を入力します。「$B3」や「$B$3」など絶対参照を使わないように気を付けて下さい。
このセルB3は、すぐ左隣の１つ目のプルダウンの対象セルです。
上の見出しでも紹介しましたが、INDIRECT関数は参照するセルの文字列を利用して参照を求める関数です。
今回参照するセルとは、B3セルです。
B3セルは１つ目のプルダウンで「名前」登録したものと同じ名称の「総務部」「経理部」「営業部」しか選べないので、間接的に「名前」に登録したセル範囲を参照することが出来ます。
このINDIRECT関数と「名前」の利用により、2つ目のプルダウンは１つ目のプルダウンと連動する、という仕組みです。
手順４－３．２つ目のプルダウンを確認
２つ目のプルダウンを選択すると、下のように１つ目のプルダウンの「部門名」に合った「課」のみが表示されます。
手順４－４．プルダウンをコピペ
プルダウンを設定したセルをコピーして、下のセル範囲に貼り付けることで、プルダウンの入力規則も貼り付きます。以上で、２段階プルダウンの設定は完了です。
手順５．３段階目のプルダウンをINDIRECT関数を使って設定
最後に、３段階目のプルダウンを設定します。
手順５－１．「データの入力規則」を選択
まず、３つ目のプルダウンを設定するセルをクリックしてから、「データ」タブの「データの入力規則」をクリックします。
手順５－２．INDIRECT関数を『元の値』欄に挿入
「データの入力規則」画面が開きますので、入力の値の種類は「リスト」を選びます。
次に、元の値の入力欄に「=INDIRECT(C3)」と数式を入力します。「$C3」や「$C$3」など絶対参照を使わないように気を付けて下さい。
このセルC3は、すぐ左隣の2つ目のプルダウンの対象セルです。
手順５－３．３つ目のプルダウンを確認
３つ目のプルダウンを選択すると、下のように２つ目のプルダウンの「課」に合った「担当者」のみが表示されます。
手順５－４．プルダウンをコピペ
プルダウンを設定したセルをコピーして、下のセル範囲に貼り付けることで、プルダウンの入力規則も貼り付きます。以上で、３段階プルダウンの設定は完了です。
以上の作業により、３つの連動するプルダウンの設定が完了しました。
下のように、左側で選択した項目によって、右側に表示させるプルダウンリストが変化します。
プルダウンリストを後から追加する方法
設定したプルダウンリストを後から追加する方法を紹介します。
下の表のように、３つ目のプルダウンリストの「勤労課」に「三田」「折川」の２名を追加します。
登録した「名前」のセル範囲を変更するために、「数式」タブの「名前の管理」をクリックします。
「名前の管理」画面から、セル範囲を変更したい「勤労課」を選択します。
「参照範囲」に入力されているセル範囲を変更して、「閉じる」を押します。
「=部門別人員表!$G$3:$G$5」⇒「=部門別人員表!$G$3:$G$7」
「名前の参照への変更を保存しますか？」と問われるので、「はい」を選択します。
２つ目のプルダウンを開いて、追加した担当者が追加されていれば完了です。
プルダウンが上手くいかない場合
連動するプルダウンが上手くいかない場合は、以下の２点を確認してみてください。
プルダウンが「＝INDIRECT」になる
２つ目と３つ目のプルダウンリストに、「＝INDIRECT」と表示されてしまう場合があります。
この場合、「データの入力規則」画面で設定したINDIRECT関数が正しく入力されていない可能性があります。
特に、「=」や「(」「)」が半角で入力されているか確認してください。
全角で入力すると、このようなエラーが発生してしまいます。
下の画面ショットの場合、「＝INDIRECT(B3）」となっており、「）」が全角のためエラーの原因となっています。
プルダウンリストが全て同じになる
２つ目や３つ目のプルダウンリストについて、すべて同じ項目が表示されてしまう場合があります。
この場合、「データの入力規則」画面で設定したINDIRECT関数に、絶対参照の「$」が入力されていないか確認してください。
