エクセルのドロップダウンリストが見にくいと思ったことがあるかもしれません。項目が多いと探すのが大変ですから、Excelで絞り込みができるとプルダウンのリストも減って見やすくなりますよね。エクセルでの絞り込みはプルダウンを複数作ることで可能です。今回は、エクセルのドロップダウンリストを連動させることで絞り込みをする方法をご紹介します。
まずは、 エクセルのプルダウンで連動して絞り込みができるイメージをみておきましょう。簡単に説明すると、グループ分けのようなものであり、グループ名を最初のプルダウンで選ぶとそのグループに含まれている項目が2つ目のプルダウンに表示されます。事前にエクセルのリストで連動の設定をしておくことで、「グループ名◎◎ということは、次はそのグループのどれかだろう」とExcelが自動的に判断してドロップダウンリストの絞り込みが行われるのです。次の項目で、具体的な流れをご紹介しましょう。
今回は、エクセルで都道府県を選択するプルダウンを例にします。47ありますので、1回のプルダウンにするとエクセルのかなり下まで選択肢が並びますよね。そこで、先に地方を選ぶことで、エクセルのプルダウンの選択肢で出てくる都道府県が絞り込みできるようにしてみましょう。
まずは、エクセルの任意のセルに、グループ分けをしていきます。別シートでも問題ありませんが、後々に絞り込みの設定がしやすいように、同じグループの項目は同じ列または同じ行に並べておくのがおすすめです。プルダウンの項目は、抜けと重複がないように注意しましょう。例では地方で絞り込みたいので、『北海道,東北,関東,北陸甲信越,東海,近畿,中国,四国,九州』と47都道府県のグループ分けを行いました。
次に、グループ名(例では地方名)を選択できるプルダウンを新たに作成します。グループ名が書かれたセル範囲を選択し、エクセル上部の『データ』⇒「データツール」にある『データテーブル』を押しましょう。「データの入力規則」と書かれたダイアログボックスが出てきますので、「入力値の種類(A)」で『リスト』を選択し、「元の値(S)」に選択肢を直接入力します(セル参照するとうまく反応しません)。間に半角でカンマ『,』を忘れないように注意です。
もし後々に追加あるいは修正を加えたい場合には、同じように『データの入力規則』から「設定」に進むと変更できます。プルダウンの作成方法に関してはこちらの記事で詳しく触れていますので、あわせて参考にしていただけますと幸いです。
さて、ここからは一方のプルダウンを選ぶともう一方のプルダウンの絞り込みができるように連動させていきましょう。
グループ分けをしたところで、エクセルはそのことを知りません。そこで、結びつきがあることを伝えるのが目的です。Excelの名前の定義を使う場合と範囲選択から作成を使う場合の2つのやり方があります。
片方のプルダウンで設定したある選択肢を選んだときに、もう一方に出てきてほしい項目を、最初の作業で記入したエクセルのシートから範囲選択し、上部の『数式』⇒「定義された名前」から『名前の定義』を選びます。たとえば、例のように『近畿』を選ぶとき、次のプルダウンが『滋賀県・京都府・大阪府・兵庫県・奈良県・和歌山県』になるようにしたいので、範囲選択するのはG8:L8です。
「新しい名前」と書かれたダイアログボックスが出てきました。「名前(N)」の部分には『近畿』のように最初のプルダウンで設定した選択肢(グループ名)を1つ入力します。「範囲(S)」は同じような内容が複数のシートにある場合にはシート名を選択しますが、特に指定する必要がなければそのまま『ブック』で構いません。「参照範囲(R)」には先ほど範囲選択したセル番地が自動的に表示されています。『OK』を押してダイアログボックスを閉じ、最初のプルダウンに出てくる選択肢の数だけ繰り返しましょう。
親要素のプルダウンの項目が多い場合には、名前を定義する回数が増えて大変かもしれません。その際には、全ての項目を範囲選択し、『数式』⇒「定義された名前」の『範囲選択から作成』を選んで作るのがおすすめです。 『範囲選択から作成』 はショートカットでCtrl+Shift+F3でも行えます。
このような画面が出てきます。範囲選択した際に親要素が並んでいる場所を選びチェックを付けましょう。今回の例では左側に親要素がならんでいるので、✓を付けるのは『左端列(L)』です。こうすることで地域名が自動的に都道府県のグループ名として入力できます。先ほどの配置を縦横逆にしている場合には、上側に親要素が並んでいるので『上端行(T)』を選びましょう。最後に『OK』をクリックして画面を閉じます。
いよいよ二つ目のプルダウン(例では都道府県を選ぶ段階)を作成していきます。 プルダウンを作成したいセルを選択し、エクセル上部から『データテーブル』を選択するのは先ほどと同じです。
「設定」の「入力値の種類(A)」を『リスト』に変更しましょう。元の値に入力する内容が、最初のプルダウンとは異なる部分です。インダイレクト関数と呼ばれる、文字列と指定された参照文字列の内容を返す機能を使います。条件式は『=INDIRECT(最初のプルダウンを作成したセル番地)』です。今回の例では先ほどC2に作ったので、条件式は『=INDIRECT(C2)』としました。警告メッセージが出てきた場合には無視して「はい」をクリックしましょう。
名前の定義によって親要素(例では地方)・子要素(例では都道府県)が結びつき、インダイレクト関数によって親要素が入力されるプルダウンが返されます。この二つを合わせることで、エクセルの最初のプルダウンに連動して次の選択肢の絞り込みができるのです。例では、地方が変わると都道府県名も絞り込まれるようになりました。なお、インダイレクト関数に関しては以下の記事でも触れています。
「エクセルのプルダウンの絞り込みができないような気がする」「うまくプルダウンのグループ分けができているかを知りたい」と設定を確かめたい場合には、「定義された名前」の『名前の管理』を押してみましょう。「名前」のところにグループ名である親要素、「値」のところにその右側あるいは下側に並べた子要素が過不足なく含まれていれば問題ありません。
今回は、エクセルのプルダウンを連動させて選択肢の絞り込みができるように設定するやり方をご紹介しました。Excelのドロップダウンリストが長い・見にくいと思ったときには、グループ分けをして見やすくするのがおすすめです。名前の定義によってグループ内をリンクさせ、インダイレクト関数で連動させる点を押さえておきましょう。なお、3段階・4段階以上と、何回も連動させたい場合もあるかもしれません。応用形と注意したいことを次回以降の記事で触れていきますので、あわせて参考にしていただけますと幸いです。最後までお読みいただきありがとうございました。