エクセル 2016 ドロップダウンリストと連動して切り替える
はじめに
エクセル 2016 で複数のドロップダウンリストを連動して切り替える方法を紹介します。
リストの選択した項目によって 2 つ目のリストを切り替えられます。大分類、中分類、小分類のように 3 つのリストを連動して絞り込みもできます。
名前の定義と「INDIRECT 関数」を使用する方法と「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 関数を使用する」と同じです。
大分類、中分類の選択した項目によって小分類のリストが切り替わります。
