この記事ではExcel(エクセル)で連動したプルダウンを作る方法ついて、初心者でも分かるように詳しい手順を解説しています。
そもそもプルダウンって何？という方は、こちらの「エクセルのプルダウンの使い方・設定方法まとめ」をまずはご覧ください。
エクセルの便利な機能の1つに、「プルダウン」というセルに選択肢を設定できる機能があります。あらかじめ設定しておいた複数の選択肢を、縦に並ぶプルダウンメニューと呼ばれる形式で表示することができます。この記事ではエクセルのプルダウン[…]
エクセルはプルダウン同士を連動させて特定の選択肢のみを表示できる
エクセルで地方区分と都道府県区分を記載する、下記のような表をプルダウンを使って作りたいとします。
普通であればD列のプルダウンには「全地方の一覧」を、E列のプルダウンには「全都道府県の一覧」を設定するはずです。
ただし、これでは最初に地方区分を選んでいるのに、次のプルダウンでは47の全都道府県から選ばなくてはいけません。
地方区分で「関東」を選んだのであれば、都道府県で「東京・千葉・茨城・杤木・神奈川・埼玉・群馬」のみを表示してくれると楽ですよね。
エクセルではこのようなプルダウン同士を連動させることで、特定の選択肢のみを絞り込んで表示させることが可能です。
プルダウンと複数の関数を組み合わせる必要があるため手順は少し複雑ですが、初心者にも分かるように詳しく解説していきましょう。
エクセルで連動するプルダウンを作成する方法
エクセルでプルダウンを連動させて選択肢を絞り込むには、以下の2つの方法があります。
- プルダウンに「OFFSET関数」と「MATCH関数」を組み合わせる
- プルダウンに「名前の定義」と「INDIRECT関数」を組み合わせる
まずは上記2つの方法に共通する事前準備として、カテゴリ別に選択肢を整理した表組を別シートに作成します。
ここでは下記のような、地方区分ごとに整理した都道府県の一覧表を作成しておきます。
「OFFSET関数」と「MATCH関数」でプルダウンを連動させる方法
まずはOFFSET関数とMATCH関数を使って連動するプルダウンを作成する方法を解説します。
第一階層のプルダウンを設定する
まずは第一階層となるプルダウンを設定します。第一階層は通常の手順で設定すればOKです。
最初にプルダウンを設定したいセルを選択します。
セルを選択している状態で、データタブにあるデータの入力規則のボタンをクリックします。
すると設定画面が表示されるので、設定タブにある入力値の種類の項目からリストを選択します。
元の値と書かれた箇所をクリックし、先ほど別シートに作成した項目リストを一括選択します。
最後にOKをクリックすれば設定完了です。
Excelファイルをベースに答えるアンケート用紙では、「プルダウン」というメニュー形式の選択肢リストが使われることが多いです。アンケート用紙以外にも様々なシーンで使えるプルダウンメニューは、Excelを使う上では絶対に覚えて[…]
第二階層のプルダウンを設定する
続いて、連動した第二階層のプルダウンを設定していきます。
最初に第二階層となるプルダウンを設定したいセルを選択してください。
先ほどと同じ手順で、データタブにあるデータの入力規則のボタンをクリックします。
データの入力規則の設定画面が表示されるので、設定タブにある入力値の種類の項目からリストを選択します。
元の値と書かれた箇所をクリックし、=OFFSET(Sheet2!$B$3,MATCH($D3,Sheet2!$B$3:$B$10,0)-1,1,1,9)と入力します。
- OFFSET関数とは
- セルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を参照する関数
OFFSET(基準, 行数, 列数, [高さ], [幅])
- MATCH関数とは
- 指定した条件に当てはまる項目が、特定のセル範囲の中で何列目にあるかを示す関数
MATCH(検査値, 検査範囲, [照合の型])
OFFSET関数とMATCH関数の詳しい解説については、下記の記事を参考にしていただければと思います。
関連：世界一分かりやすいエクセルの『OFFSET関数』の解説
定時でカエルExcel(エクセル)のOFFSET関数が難しくて全然分からないです。。。オフィスうさぎ原理が分かればOFFSETもシンプルな関数だよ。この記事ではOFFSET関数について世界一分かり[…]
定時でカエルExcel(エクセル)のMATCH関数について教えてください！オフィスうさぎこの記事ではMATCH関数について世界一分かりやすく解説していくね！この記事ではエクセルのMATCH関数[…]
赤字部分を修正してコピペすればOKだよ！
修正する赤字部分がどのセルにあたるかを図式化したので、自身が作成したExcelファイルと照らし合わせて修正してみてください。
最後に右下のOKをクリックします。
以上の手順で、連動したプルダウンを作成することができました。
「名前の定義」と「INDIRECT関数」でプルダウンを連動させる方法
「名前の定義」と「INDIRECT関数」を使って連動するプルダウンを作る方法を解説します。
「名前の定義」を使って第一階層のプルダウンを設定
まずは「名前の定義」という機能を使いながら、第一階層のプルダウンを設定していきます。
別シートに作成したカテゴリ別に整理した表から、第一階層に表示したい項目を選択状態にします。
項目を選択している状態で、数式のタブにある名前の定義をクリックします。
すると設定画面が表示されるので、任意の名前を入力した後に右下のOKをクリックします。
続いて、別シートに作成した表の項目部分をすべて選択状態にします。
セルを選択している状態で、数式のタブにある選択範囲から作成をクリックします。
設定画面が表示されるので、左端列にチェックをいれてOKをクリックします。
続いて、プルダウンを設定したいセルを選択状態にします。
セルを選択している状態で、データタブにあるデータの入力規則のボタンをクリックします。
設定画面が表示されるので、設定タブにある入力値の種類の項目からリストを選択します。
元の値と書かれた箇所をクリックし、先ほど定義した名前を入力します。
最後に右下のOKをクリックします。
以上の手順で、第一階層のプルダウンを設定することができました。
INDIRECT関数を使って第二階層のプルダウンを設定
続いて、INDIRECT関数を使って連動した第二階層のプルダウンを設定していきます。
まずはプルダウンを設定したいセルを選択状態にします。
セルを選択している状態で、データタブにあるデータの入力規則のボタンをクリックします。
設定画面が表示されるので、設定タブにある入力値の種類の項目からリストを選択します。
元の値と書かれた箇所をクリックし、=INDIRECT($D3)を入力します。
- INDIRECT関数とは
- 指定する文字列を参照する関数
INDIRECT(参照文字列, [参照形式])
INDIRECT関数についてはの詳しい解説は、こちらの「執筆中です」を参考にしてください。
修正が必要な赤字部分に該当するセルがこちらです。自身が作成したExcelファイルと照らし合わせて修正してみてください。
最後に右下のOKをクリックすれば完了です。
以上の手順で、連動するプルダウンを設定することができました。
定時でカエルExcelでプルダウンから項目を選択したとき、自動で色分けまですることは可能ですか？オフィスうさぎプルダウンに「条件付き書式」を組み合わせればできるよ！この記事ではExcel(エク[…]
