ガッツの日記～活用しやすいドロップダウンリストを作成する裏技～

データを正しく使いこなす第一歩は、ドロップダウンリストの活用です。

Excelでは、一度入力したはずの会社名や項目を、また手入力しているケースがよくあります。

「株式会社A」「㈱A」「A社」など、同じ意味なのに表記ゆれが起きると、後で修正が必要になり、データ活用の妨げになります。

ドロップダウンリストを使えば、一度入力した内容をそのまま再利用でき、クリックだけで正確なデータが選択できます。

この記事では、基本的な設定方法を知っている前提で、「どうすればもっとドロップダウンリストを使ってもらえるか」という視点で裏技を紹介します。

テーブルと連動した、範囲が可変なドロップダウンリスト

ドロップダウンリストを使用していて、一つの悩みは参照範囲が可変なことだと思います。

上記の画像で説明すると、一度設定した黄色の箇所（$M$2:$M$8）を必要に応じて変えなくてはいけないケースがあるはずです。

この場合はドロップダウンリストの参照範囲（元の値）をテーブルにすることです。

テーブルは参照範囲をセル位置ではなく、名前にすることができます。

その特徴を利用します。

但し、特殊な設定の仕方が必要です。

参照範囲が下の画像の「日付マスタT」の「日付列」だとします。

この場合は下の画像のようにINDIRECT関数を使用します。

これで、テーブル範囲が変わってもドロップダウンリストに反映されます。

二段階で絞り込むドロップダウンリスト

XLOOKUP関数使用

ドロップダウンリストを使用していて「使いにくい」と感じるケースの一つが「一体どれを選んでいいのか？」がよく分からないケースだと思います

事前に絞り込まれていれば「これを選択すれば大丈夫」という確信は持ちやすいです

具体的な例でお話したいと思います

社内に関東、関西で支店がいくつかあったとします

関東にある支店ってどことどこだっけ？と調べるよりも以下のように「関東、関西」をまず選択し、その後に関東に絞り込まれて支店が選択できた方がいいですよね？

関東⇒東京支店、茨城支店

関西⇒大阪支店、兵庫支店

実はこれ、XLOOKUP関数を組み合わせれば意外と簡単です

まずは以下のように元の値を作成します

上の表内を検索範囲：地区、出力範囲：県１と県２としてXLOOKUP関数を書くと次のようになります

ちなみに参照値はB2セル（関東、関西）になります

こちらのXLOOKUP関数をドロップダウンリストに埋め込みます

そうすると、二段階のドロップダウンリストが作成できます

検索可能なドロップダウンリスト

こちらは以前にも紹介した内容になります

ドロップダウンリストに検索ワードを入力したら、検索ワードを含むものが表示されます

こうすることで、入力候補が無数にある場合にはかなり効率が上がります

今回は詳細に解説しませんが、ポイントだけは解説したいと思います⇒過去記事

ポイントは３つありまして、1つ目は元の値をリストにするのでなく候補を元データにすることです

2つ目は一番重要なのですが、候補を関数だけで出力します

使用する関数は以下です

・FILTER関数 ⇒条件に合う内容をテーブルにする

・FIND関数 ⇒検索文字列を含む位置を数字で出力する

・ISNUMBER関数 ⇒指定範囲が数字であればTRUE、そうでなければFALSEを出力する

つまり、FIND関数で数字で出力された内容をTRUEで出力し、更にTRUEの内容だけをFILTER関数で出力します

最後に、何気にこれも重要だと思うのですが、ドロップダウンリストのエラー出力を回避するようにしておくことです

これはどういうことかというと、ドロップダウンリストは事前に決められたものしか出力できないようにするためにあります

ここを敢えて、事前に指定された元の値以外のものを出力できるようにするのです

そうしないと、検索文字列をドロップダウンリスト内に入力できません

上記のエラーメッセージタブで「エラー表示」のチェックを外せばエラーメッセージが出なくなります

＜まとめ＞

今回はドロップダウンリストをもっとうまく活用できるようにする方法を解説しました

全般的に新関数、つまりスピル関数を使用しています

さすがに検索型は難しいですが、念のため、スピル関数が使えないバージョンでも2段階のドロップダウンリストを使用できるようにする方法を解説します

名前機能とINDIRECT関数を使用すれば可能です

関東、関西、それぞれに名前を付けます

ここで「数式」リボンをうまく使うのも大きなポイントです

ここからINDIRECT関数を使います

この関数は「名前」を通じて、名前の内容を参照できます

ですので、以下の画像のような感じでドロップダウンリストの元の値を設定します

これで2段階でのドロップダウンリストを作成できます

にほんブログ村