「入力に応じてプルダウンリストの選択肢を変更できないかな？」
こんな疑問を解消します。
あらかじめ選択肢を絞ることができるプルダウンリストですが、項目が多すぎてしまうと少し不便です。
例えば社員の名前を表示をするプルダウンリストであれば、全社員が項目として表示されるよりも選んだ部署に応じた社員だけ出せた方がスッキリします。
今回はこのようにプルダウンリストの値を連動して変更する方法をご紹介します。
プルダウンリストの値を連動して変更する方法
プルダウンリストの値を連動して変動させる方法は２つあります。
①名前定義とINDIRECT関数を使用する方法
②OFFSET関数とMATCH関数を組み合わせる方法
どちらのやり方も一長一短あるので、用途に合わせて使えるようにしておくと便利です。
まずは実装が簡単な「名前定義とINDIRECT関数を使用する方法」からご紹介していきます。
名前定義とINDIRECT関数を使用してプルダウンを作成する方法
はじめに部署と社員の組み合わせのデータを用意します。
E1、F1、G1に部署名、そして部署名の下には各部署に所属する社員の名前を入力します。
続いてA1に"部署名"、B1に"社員名"と入力できたら準備完了です。
次に連動して変化するプルダウンリストを作成していきます。
まずは部署名のプルダウンから作成します。
A2セルを選択した状態で、画面上部にある「データ」→「入力規則」とすすみ、「許可」の下のボックスから「リスト」を押します。
すると「元の値：」というものが入力できるようになるので、ここでE1からG3までをドラッグしながら範囲選択してOKで閉じます。
これで部署名のブルダウンは完成です。
続いては、連動して変化させたいリストを作成していきます。
最初に各部署ごとの社員に対して名前をつけていきます。
今回の例では営業部社員に該当するE2からE4を選択した状態でA1セルの上にあるボックスに営業部と入力します。
ここでの入力値はプルダウンリストの値と一致させるようにしてください。
こうすることで今選択した範囲のセルに対して営業部という名前をつけることができました。
このセルの範囲に対して名前をつけることを名前定義と呼びます。
続けてシステム部の社員、法務部の社員にも名前定義を行います。
各部署の社員の名前づけが終わったら準備完了です。
定義した名前を確認するには「数式」→「名前の定義」をクリックします。
すると先ほど入力した「営業部」、「システム部」、「法務部」が登録されているはずです。
試しに「営業部」を押してみると、定義されたセルの範囲が確認できます。
後ほど使用していきますが、この名前定義を行うことでセルの範囲の指定を簡単にできるようになります。
この定義ができればあとは簡単に連動するプルダウンリストの作成が可能です。
続いてはB2のセルを選択した状態で「データ」→「入力規則」と進み、「許可」から「リスト」を選択します。
ここで「元の値:」の中に先ほど定義した名前を使用します。
試しにここに「=営業部」と入力してみると、営業部として定義されている社員名がプルダウンリストに表示されるようになっていることが確認できます。
ただし今回は部署に連動して社員名を表示させる必要があります。
連動させるためには元の値の中に「=INDIRECT（$A$2）」と書きOKを押してください。
すると部署に応じて連動して変化するプルダウンリストが作成できました。
INDIRECT関数の補足
中には「INDIRECT関数ってなんだろう？」と思った方もいるかと思いますので補足しておきます。
試しにINDIRECT関数を使わないでやってみるとどうなるのかみてみましょう。
この書き方で生成されたプルダウンリストをみてみると、営業部で定義されている社員名ではなくて営業部という文字列がそのまま表示されてしまいます。
このように文字列そのものではなく、その参照を使いたい場合に使用されるのがINDIRECT関数です。
選択肢が追加された場合の対処法(再度名前定義を行う)
この名前定義とINDIRECT関数を使用した連動プルダウンリストを作成するやり方は、とても簡単ではありますが１つ弱点として新たに項目が追加された場合には名前定義の更新作業が必要になります。
例えば営業部に新しい社員が追加された場合にI列に社員が追加されたとします。
この状態で社員名のプルダウンリストを確認してみても、新規で追加された社員名は表示することができていません。
このように新たに項目が追加された場合には「数式」→「名前の定義」と進み、変更したい名前を選択します。
続いて「セルの範囲を選んでください:」の下のボックスを選ぶと再度範囲指定ができるようになるので、今回新たに追加した項目も含めるようにします。
すると今回新たに追加した項目もプルダウンリストに表示されるようになります。
選択肢が変わることが少ない場合にはこの方法が一番簡単です。
ただし頻繁に項目が追加される場合は何度も名前定義を変更しないといけないので少し不便です。
そこで変更に強いもう1つの連動プルダウンの作成方法をご紹介します。
OFFSET関数とMATCH関数を使用した連動プルダウンの作成方法
２つめに紹介するのが「OFFSETとMATCH関数を使用した連動プルダウンリストの作成方法」です。
こちらは名前定義を使用した方法よりも少し難易度が高いですが、変更に応じた再定義が必要ないので一度作成してしまえば後々楽になるやり方です。
早速作ってみましょう。
まずは先ほどと同様に部署と連動させたい社員名を準備します（名前の定義は不要です）
準備ができたらまずは部署名のプルダウンリストを作成します(上の手順ですでに部署名のプルダウンリストを作成している場合にはこの操作は不要です！)
A1セルを選択した状態で、
①「データ」→「データの入力規則」をクリック
②入力値の種類で「リスト」を選択
③「元の値」に、$E$1:$G$1と入力する
と順番に行います。
①「データ」→「データの入力規則」をクリック
②入力値の種類で「リスト」を選択
③「元の値」に、$E$1:$G$1と入力する
これで部署名のプルダウンは完成です。
続いては社員名のプルダウンを作成していきます。
B2セルに合わせた状態で「データ」→「入力規則」から「許可」を「リスト」に設定します。
続いて元の値の箇所に、
=OFFSET($E$2,0,MATCH($A$2,$E$1:$G$1,0)-1,COUNTA(OFFSET($E$2,0,MATCH($A$2,$E$1:$G$1,0)-1,100,1)),1)
と入力してみてください。
この意味については後ほど解説していきます。
入力後OKを押すと連動して値が変わるプルダウンリストが完成します。
ここで営業部署に追加で何名か追加してみましょう。
今回の方法で作成したプルダウンリストであれば、新規で入力した社員が自動で追加されているのが確認できます。
OFFSET関数とMATCH関数を使用した連動プルダウンの解説
自分で応用できるようにするために、関数の解説をしていきます。
=OFFSET($E$2,0,MATCH($A$2,$E$1:$G$1,0)-1,COUNTA(OFFSET($E$2,0,MATCH($A$2,$E$1:$G$1,0)-1,100,1)),1)
難しく見えますが、１つずつみていくと理解できるようになります。
この中で使用している関数は、
OFFSET関数
MATCH関数
COUNTA関数
の３つです。
OFFSET関数
OFFSET関数は範囲の指定ができる関数です。
OFFSET関数は以下のとおり。
OFFSET（基準、下への移動、右への移動、縦の範囲、横の範囲）
まず第1引数には基準とするセルが入ります。
例えばOFFSET(E2)とすると、E2のセルが基準になります。
第2引数と第3引数はそれぞれ「下にいくつずらすか」、「右にいくつずらすか」を表します。
例えばOFFSET(E2,1,2)と書くとE2を基準として下に1、右に２を移動した範囲が選択されます。
第4引数と第5引数では「指定された位置からどのくらいの範囲を広げるのか」を指定します。
例えばOFFSET(E2,1,2,2,3)とした場合、指定された位置から下に２行分、右に３列分が指定範囲になります。
ではここまでの知識を使ってOFFSET関数によってB2に営業部の社員を入力してみましょう。
タブバーから「データ」→「入力規則」と進み、「元の値:」に
=OFFSET($E$2,0,0,6,1)
と入力します。
これは$E$2を基準として、その位置から移動せずに縦に6行、横に1列という指定です。
完了したら、B2のプルダウンリストをみてみましょう。
すると社員名に営業部の社員が入力されていることが確認できます。
では続いて部署に応じて連動して社員名を変更できるようにします。
これを行うために必要になるのがMATCH関数です。
MATCH関数は第1引数の値が第2引数の何番目にあるのかを返してくれる関数です。
なので、
MATCH(A2,E1:G1,0)
とすると営業部が選択されている場合に1、システム部が入力されてる場合は2、法務部が入力されている場合には3が取得できます。
さらに今回はE2を基準としているので、このMATCH関数で取得した値に対して-1をします。
これによって取得できた数値をOFFSET関数の第2引数に入れてあげるとその分だけ基準のセルがずれてくれるため、部署に応じた社員が取得できるようになります。
これでほぼ完成してきましたね。
ただしあと１つだけ修正が必要です。
試しに部署をシステム部に変更してから、社員名のプルダウンを開いてみてください。
すると空欄が表示されてしまっているかと思います。
これは取得する縦(列)の指定を5と固定で打っているため、その足りない分が空欄と表示されてしまっています。
ではどうすればいいのかというと、入力されている社員数の数だけこの第4引数に指定してあげることが必要です。
ここで必要になってくるのがCOUNTA関数です。
COUNTA関数ではセルの範囲を引数に渡してあげるとその中の入力されているセルの数が返ります。
セルの範囲の指定に関してはここまでに使用しているOFFSETをほぼそのまま使うことができます。
OFFSET関数の第4引数だけは変更してあげます。
今回は100としていますが、ここには選択肢で明らかに超えないであろう個数を入れればOKです。
OFFSETの第4引数だけ変更したらそれをCOUNTAの引数に指定してあげましょう。
COUNTA(OFFSET($E$2,0,MATCH($A$2,$E$1:$G$1,0)-1,100,1))
すると部署に応じた社員数が取得できます。
まとめ
今回は連動するプルダウンの使い方をご紹介しました。
ここまでをまとめておきます。
・連動するプルダウンを作る方法は、名前定義を使用する、またはOFFSET・MTACH関数を使う2つがある。
・選択肢の項目が頻繁に変わらないのであれば名前定義を使用する方法の方が簡単
以上、エクセルで連動するプルダウンを作成する方法のご紹介でした！
このページをみている人にオススメの商品
