エクセル ドロップダウンリストと連動して切り替える

はじめに

エクセルでドロップダウンリスト (プルダウン) を連動して切り替える方法を紹介します。

「INDIRECT 関数」から、名前の定義を使用して選択したリストと連動できます。

「SWITCH 関数」から、選択した項目に対応する範囲をリストに表示できます。

「OFFSET 関数」から、選択した項目に対応する範囲を検索してリストに表示できます。

連動の準備をする

次のように 1 つ目のドロップダウンリストを選択すると、2 つ目のドロップダウンリストの内容を連動して切り替えられます。

1 つ目のリストは好きなように作成します。

ここでは オフィス,バージョン の項目を直接入力しています。

2 つ目のリストに表示する連動用データを作成します。見出しを使用する方法と使用しない方法があります。使用するときは 1 つ目の項目が見出しの名前になります。

2 つ目の [セル] を範囲選択します。

[データ] タブをクリックし、データツールグループにある [データの入力規則] をクリックします。

元の値に入力する関数によって連動の仕方が変わります。

• INDIRECT 関数：名前の定義を使用して選択したリストと連動できます。
• SWITCH 関数：選択した項目に対応する範囲をリストに表示できます。
• OFFSET 関数：選択した項目に対応する範囲を検索してリストに表示できます。見出しを使用します。

INDIRECT 関数で連動する

1 つ目のドロップダウンリストで選択した項目から定義された名前に変換して連動できます。

1 つ目のリストの項目と同じ名前を定義します。オフィスのデータを範囲選択し、名前ボックスに オフィス と入力して Enter キーを入力します。

選択した範囲に オフィス という名前が付けられます。

同じようにして バージョン という名前を付けます。

2 つ目のセルの元の値に「INDIRECT 関数」を使用して、選択した項目を名前に変換してそれをリストに表示できます。

$B3 は、1 つ目の一番上のリストのセルです。列だけ絶対参照にします。

これで連動してドロップダウンリストを切り替えられます。

SWITCH 関数で連動する

1 つ目のドロップダウンリストで選択した項目に対応する範囲を連動して表示できます。

2 つ目のセルの元の値に「SWITCH 関数」を使用して、選択した項目に対応する範囲をリストに表示できます。

2016 以前のバージョンなら「IFS 関数」を使用します。

$B3 は、1 つ目の一番上のリストのセルです。列だけ絶対参照にします。

これで連動してドロップダウンリストを切り替えられます。

OFFSET 関数で連動する

1 つ目のドロップダウンリストで選択した項目に対応する範囲を自動で取得して連動できます。

2 つ目のセルの元の値に「OFFSET 関数」を使用して、選択した項目に対応する範囲をリストに表示できます。「XMATCH 関数」を使用して見出しを検索できます。

2019 以前のバージョンなら「MATCH 関数」を使用して、見出しを検索します。

$B3 は、1 つ目の一番上のリストのセルです。列だけ絶対参照にします。

数式の解説をします。見やすさのために絶対参照にしていません。実際には絶対参照で入力します。

「OFFSET 関数」の引数は次のようになっています。

引数「基準」に D2 を入力します。これは連動用データの見出しの 1 つ左のセルになります。

基準のセル「D2」から「OFFSET 関数」を使用して、連動用データの範囲を取得します。

連動用データは基準のセル「D2」の 1 つ下の行から始まるので、引数「行数」に 1 を入力します。

基準のセル「D2」からオフィスなら 1 つ右の列、バージョンなら 2 つ右の列にあります。「XMATCH 関数」を使用して見出しが何列目にあるのか取得できます。引数「列数」に XMATCH(B3,E2:F2) を入力します。 B3 は 1 つ目のリストのセルです。

ここまでで オフィス を選択したときはセル「E3」を、バージョン を選択したときはセル「F3」を範囲選択できます。範囲選択する列数は 1 列なので引数「幅」に 1 を入力します。

最後に引数「高さ」を求めます。オフィスのデータの数は 3 です。バージョンのデータの数は 4 です。この数値を取得できれば完成です。

「COUNTA 関数」を使用して =COUNTA(E:E)-1 のように入力すると E 列に入力されているデータの数を取得できます。見出しは不要なので -1 しています。

どの列から取得するかは、基準のセル「D2」の列を基準にして「OFFSET 関数」と「XMATCH 関数」を使用します。これを引数「高さ」に COUNTA(OFFSET(D:D,0,XMATCH(B3,E2:F2)))-1 を入力します。

これで連動してドロップダウンリストを切り替えられます。