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

はじめに

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

リストの選択項目に連動して 2 つ目のリストを切り替えるには「INDIRECT 関数」や「OFFSET 関数」を使用します。

大分類、中分類、小分類のように 3 段階でリストを連動させるには「OFFSET 関数」を使用します。

• 連動して色付けするには「ドロップダウンリストと連動して色付けする」をご覧ください。

INDIRECT 関数で連動する

1 つ目のリストは好きなように作成します。ここではオフィス, バージョンの項目を表示します。

2 つ目のリスト用に連動させるデータを用意します。見出しは使いません。

1 つ目のリストの項目と同じ名前を定義します。「オフィス」を選択したときに連動してリストに表示する [セル] を範囲選択して、名前ボックスにオフィスと入力して Enter キーを入力します。同じようにして「バージョン」の名前も定義します。

名前が定義されました。

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

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

[リスト] を選択して [ドロップダウンリストから選択する] をチェックします。元の値に「INDIRECT 関数」を使用し=INDIRECT($B$3)と入力して [OK] をクリックします。$B$3は 1 つ目のリストのセルです。

[はい] をクリックします。

これで連動してリストを切り替えられます。

INDIRECT 関数とは

「INDIRECT 関数」は文字列を実際のセル参照に変換します。=INDIRECT("E3:E5")と入力すれば、セル「E3」～「E5」の範囲を参照します。

=INDIRECT($B$3)はセル「B3」に「オフィス」と入力されていれば、名前の定義の「オフィス」に変換されます。

このように動的に名前の定義を切り替えられるので、ドロップダウンリストを連動できます。

スポンサーリンク

OFFSET 関数で連動する

「OFFSET 関数」、「MATCH 関数」、「COUNTA 関数」を使用すると名前の定義を使用しないで連動できます。関数の使い方はそれぞれのページをご覧ください。

データは上記の「INDIRECT 関数で連動する」と同じものを使用します。

2 つ目のセルの [元の値] に次の数式を入力します。

数式の解説をします。「OFFSET 関数」の引数は次のようになっています。

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

このセル「D2」から連動用のデータを範囲選択できれば連動できます。

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

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

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

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

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

どの列から取得するかは先ほどと同じように「OFFSET 関数」と「MATCH 関数」を使用します。これを引数「高さ」にCOUNTA(OFFSET(D:D,0,MATCH(B3,E2:F2,0)))-1と入力します。

これで連動してリストを切り替えられます。

3 段階に連動する

大分類、中分類、小分類と順番に絞り込みをするように連動できます。

名前の定義を使用する方法でもできますが、名前の数が膨大になるため現実的ではありません。そこで「OFFSET 関数」を使用する方法を紹介します。

基本的な考え方は上記の「OFFSET 関数で連動する」と同じです。3 つ目の小分類のリストで違いがある部分について紹介します。

大分類、中分類、小分類の連動用のデータを作成します。

小分類の見出しは、大分類と中分類の値を結合したものにします。=F3&H3=F3&H4・・・

小分類のリストを表示するセルの [元の値] に次の数式を入力します。

引数「基準」にK2を入力します。これは小分類のデータの見出しの 1 つ左のセルになります。

「MATCH 関数」で検索する値を大分類と中分類を結合した値にします。MATCH(B3&C3,L2:T2,0)

その他は「OFFSET 関数で連動する」と同じです。

大分類、中分類の選択した項目によって小分類のリストが切り替わります。

スポンサーリンク