【Excel】連動するドロップダウンリストの作成方法-入力規則の応用
前回のエクセルにおける入力規則とは？ドロップダウンリストの作り方は？からの続きです。
前の記事：【Excel】入力を簡単に！ドロップダウンリストで入力を制限する-入力規則の使い方
ドロップダウンリストを使い慣れてくると、「大分類の選択⇒小分類の選択」の入力のときに、大分類を選んだ時点で小分類の項目が絞られていて欲しいという要望が出てきます。ほぼ間違いなく。
イメージとしては、部署を選んで課を選ぶときとか、帳簿の費目の大分類を選んで小分類を選ぶときとか・・・。
例を見てみたほうが早いですね。（以下、小さい画像はクリックすると大きくなります。）
- 部署で人事部を選ぶと、人事部内の課のリスト表示
- 部署で経理部を選ぶと、経理部内の課のリスト表示
- 部署で営業部を選ぶと、営業部内の課のリスト表示
こんな感じで、部署の値に連動して課のドロップダウンリストの項目が変わるというものです。
これは「名前の定義」と「INDIRECT関数」で作成することができますよ～っというお話です。（関数だからって難しくないです。）
目次
連動するドロップダウンリスト作成方法
まずは、準備をしなくてはいけません。
ステップ１：項目（元データ）の準備
前回のページで、ドロップダウンを作成するときの元データは、別シートで作成しておくのが良いよ！という話をしました。
ここでは、「リスト」という別シートに親である大分類を横にリストアップしていきましょう。（縦でも良いのですが、あとから見やすいのは横です。）
この例では大分類は６分類でA1から順に横に並べています。
次に小分類の項目です。
それぞれの大分類の下の２行目から小分類の項目を追加していきます。大分類はわかり易いように色塗りしています。
ステップ２：連動させるために名前を定義する（山場です！）
次に、名前を定義していきます。
親である大分類の項目に対応する小分類のまとまりを名前付けしていきます。大分類でAという項目が指定されたら、Aと定義された小分類だけを表示させるためです。
まずは、A列の「パンのなかま」です。
ここでは、A1～A8までを選択し、右クリックします。（選択範囲は各々で違うので、ご自分の環境で適応させてください。）
下から２番目に「名前の定義」というものがありますので、これをクリックしてください。
新しい名前というダイアログボックスが出てきます。名前のところにA1の値、参照範囲は選択範囲であることを確認してください。
ここで参照範囲を直接入力して修正する必要があります。「$A$1:$A$8」となっているところを「$A$2:$A$8」（小分類のみのリスト）と変えなくては、ドロップダウンリスト内に大分類の項目が出てきてしまいます。
※最初から、A2～A8を選択してもかまいません。その場合は、名前の欄を手動でA1の値に変更する必要があります。これは完全一致させておかないと連動してくれませんので、ここでは表記のブレがないように、参照範囲を修正する方法を取りました。
そうすると、シートのA1の上にある名前ボックスに定義した名前が出てきて、クリックすると定義した範囲を選択してくれます。
ここが正念場です！あとは大分類の数だけ、名前を定義していきます。
ステップ３：入力規則で大分類をドロップダウンリスト化する
「Sheet1」（入力シート）に戻り、大分類の列（ここではA列）を選択し、入力規則のダイアログボックスを表示させます。（メニュータブの「データ」→「データの入力規則」をクリック）
①入力値の種類を「リスト」にして、元の値のテキストボックスにカーソルを表示（クリックする）させ、②「リスト」シートの大分類の範囲（１行目）を選択します。
③無事に選択できたことが確認できたら、OKを押してください。
ステップ４：小分類をドロップダウンリスト化する
大分類の列を選択すると、前のステップで作成した大分類のリスト化ができていると思います。では、さっそく連動した小分類のリストを作っていきましょう。
ここではB2を選択し、入力規則のダイアログボックスを表示させます。（メニュータブの「データ」→「データの入力規則」をクリック）
入力値の種類を「リスト」にし、元の値に直接「=INDIRECT($A2)」と入力してください。 ※$は無くてもかまいません。気になる方は絶対参照・相対参照で調べてみてください。）
簡単に言うと、元の値の内容（＝リストの内容）は、横のA列の大分類の項目によって指定されるよ、という関数です。ここで先ほどの名前の定義が生きてきます。A列で指定された名前のグループをこのINDIRECT関数によって表示してくれるのです。
OKを押すと・・・
この例では、A2（参照する大分類）に何も入力がないので、エラーメッセージが出ますが無視して「はい」で構いません。
ステップ５：連動しているのか？確認してみましょう！
ではいよいよ確認です。
大分類で選択した値によって、小分類での項目内容が連動して変更されていることが確認できたでしょうか？
できてればOK！です。もし出来ていないようなら、関数のつづり間違いなどをチェックしてみましょう。
ステップ６：最後に下のセルもコピーして終了です
B2をコピーして、適切な位置まで貼り付けを行ってください。
このとき「形式を選択して貼り付け」⇒入力規則 にすると書式などはコピーされません。入力規則だけ設定したい場合はその方法でどうぞ。
おまけ：名前の定義のメンテナンス
小分類の項目に増減があった場合、シート上に追記するだけでは項目に反映されません。
名前の管理というところで、参照範囲のセル範囲を変更してあげなくてはいけません。
メニュータブの「数式」→「名前の管理」です。
対象となる参照範囲を変更しておきましょう。
まとめ／エクセルで連動したドロップダウンリストの作成方法
エクセルにはたくさんの機能があります。
マクロ（VBA）などでも効率化は進みますが、いかんせんハードルが高いのです。それを作った人が異動などでいなくなると、とたんにメンテナンスする人がおらず、騙しだまし使うか全く使われないものになってしまいます。
せっかく作ったものを誰もがメンテナンスできるようになれればそれに越したことはありませんが、仕組みを理解しやすい、こういった便利機能を組み合わせてみると、VBAでなくとも意外と使えるものになっていきますよ～。
この連動するドロップダウンリストでは、
- 入力規則－リスト
- 名前の定義
- INDIRECT関数
を使いました。
設定はちょっとだけ面倒ですが、設定してしまうとあとは簡単ですよ。ぜひ、やってみて下さい。
もういっこおまけ：ドロップダウンリストのキーボード操作は「Ctrl」＋「↓」
入力をバンバンしている最中に、ドロップダウンリストだからって、マウスまで手を持っていくのが面倒なときがあります。
その場合、「Ctrl」（コントロール）ボタンを押しながら、「↓」（下向きの矢印）を押下すると、リスト項目がピロッと現れます。お試しくださいな。
ではではー。