Excelで「分類を選んだら商品リストが切り替わる」2段階プルダウンを作ろうとして、設定がうまくいかなかったり、あとから壊れてしまった経験はありませんか？
本記事では、練習用Excelファイルを使って
項目を追加しても壊れにくい2段階プルダウンの作り方
を解説します。
「関数って難しそう…」と不安な方も大丈夫。使うのは「INDIRECT関数」ひとつだけ！
あとはマウス操作で進められます🐾
動画を見ながら同じ操作をすれば、Excel初心者でも10分程度で設定が完了します。
ぜひ練習用ファイルをダウンロードして、一緒に操作しながら進めてみてください。
壊れにくい2段階プルダウンの作り方
2段階プルダウンが壊れてしまう原因は、データの持たせ方にあることがほとんどです。
多くの場合、
- セル範囲を直接指定している
- あとから項目を増やす前提になっていない
といった作り方が原因で、内容に変更があるたびに手直しが必要になります。
この記事では、テーブル機能を使って
「項目が増えても、数式の書き直しは一切不要！」
「マスタを更新するだけで、プルダウンに即反映！」
な2段階プルダウンリストを作っていきます。
このように作っておくと、一度作った仕組みを何度も使い回せるようになります。
さっそく一緒に作ってみましょう！
今回は、練習用ファイルに「すぐに練習できるシート」と「答え合わせができる完成版」の2種類を用意しています。
- 📝【練習用】2段階プルダウン（ここを操作します）
- 📝【練習用】各種マスタ
- 🚩【完成版】2段階プルダウン （答え合わせ用）
- 🚩【完成版】各種マスタ
にゃんこのひと言🐾
2段階プルダウンのように、ちょっとした「関数のコツ」を知っているだけで、日々の入力作業は劇的に楽になります。こういった実務に直結するワザを基礎から網羅しておきたいなら、この本を辞書代わりに手元に置いておくのが一番の近道ですよ。
2段階プルダウンとは？
2段階プルダウンとは、1つ目の項目（分類）を選ぶと、2つ目の項目（商品）が自動的に絞り込まれる仕組みのことです。
まずは動画を見ながら完成版シートを触って、その便利さを体験してみましょう！
分類ご飯を選ぶと、商品名のプルダウンにはご飯分類の商品のみ表示されます。
分類パンを選ぶと、商品名のプルダウンにはパン分類の商品のみ表示されます。
このように2段階プルダウンを設定すると、次の選択肢には関連するもののみ表示されるため、「長いリストから探す」という手間が省けます！
まずは、2段階プルダウンがどんな風に動くのか、動画で実際のイメージを確認してみましょう！
最初にマスタを作成しよう！
では、いよいよ練習用ファイルを使って2段階プルダウンを作っていきます。
動画では解説のためにシートを分けていたけれど
練習しやすいようにマスタを1枚のシートにまとめています！
操作方法は同じだから安心してね！
まずは「【練習用】各種マスタ」シートに入っているデータをテーブル化していきましょう。
マスタをテーブルにしておくことで、あとで商品が増えても自動的にプルダウンに反映されるようになります。
※テーブル機能の基本編は【こちら】
※テーブル機能のの操作を忘れてしまった方は、この動画の[01:16]あたりから見てみてください
種類から順番にテーブル化していきます。テーブル化したら、そのテーブルに名前を付けましょう。
テーブルの名前は、表の中のどこかをクリックすると出てくる
画面上の『テーブルデザイン』タブから変更できるよ！
一番左端をチェックしてみてね！
この時
「テーブル化したマスタ項目のヘッダーと同じ名前にする」
というルールがあります。
2段階プルダウンリストを作成する上で必ず守らなくてはいけないルールなので覚えておきましょう！
完成版では『分類』や『飲み物類』という名前にしていたけれど
練習用シートでは画像と同じ『種類』や『飲み物』にしてくださいね！
これは、Excelの「1つのファイル内で同じテーブル名は使えない」
というルールを回避して、皆さんがスムーズに練習できるようにするためです。
やり方は全く同じなので、安心して自分のシートにある言葉（種類・ご飯など）に合わせて進めてくださいね！
他のマスタ項目も同様にテーブル化し、テーブルに名前を付けていきましょう。
もしヘッダーが『飲み物』なのにテーブル名を『飲み物類』にしてしまうと
あとでプルダウンが連動しなくなっちゃいます。
一字一句同じ名前にするのがコツです！
INDIRECT関数で連動するリストを設定しよう！
マスタの準備ができたので、いよいよ連動するリストを設定していきます。
ここで使うのが、選択された値をもとに参照先を切り替えられる INDIRECT関数 です。
この INDIRECT関数 と先ほど作成した「テーブル名」を組み合わせることで、項目を追加しても壊れにくい2段階プルダウンを実現できます。
※INDIRECT関数の基本編は【こちら】
※INDIRECT関数の操作を忘れてしまった方は、この動画の[01:33]あたりから見てみてください
1段目のプルダウン設定
まず1段階目のプルダウンとしてB列の「種類」を設定していきましょう。
ここでも「【練習用】2段階プルダウン」シートをテーブル化し、テーブルに名前を付けておきましょう。
動画では普通の表のまま解説しているけれど
入力するシートもテーブルにしておくのがおすすめです！
このテーブル名は連動に影響がないので任意の名前でOK！
テーブルにしておけば、新しい行を増やした時に
プルダウンの設定が自動でコピーされるんです！
これなら、あとから壊れる心配もなくて安心だね！
B2セルを選択した状態でリボンメニューから、データタブを選んで、データの入力規則のアイコンをクリックして「データの入力規則」のダイアログボックスを出します。
「データの入力規則」ダイアログボックスの最初の項目「入力値の種類」をリストに変更します。
続けて2つ目の項目「元の値」のテキストボックスにINDIRECT関数を入れます。
まずは以下の部分までを入れましょう。
=INDIRECT(
ここはセルと違って候補が上がってこないので
正確にスペルを入力する必要があります。
次に、最初の項目名が入っているB1セルをクリックします。
B1セル（種類）をクリックすることで、先ほど『種類』という名前を付けたテーブルを読みに行くように設定しています。ここは、常にB1セルを見てほしいので、必ず絶対参照にするのを忘れないようにしましょう！
この絶対参照を忘れると、リストが壊れるので注意してね！
直接B1セルをマウスでクリックすれば、自動で絶対参照になるけど
手入力でセル番地を入力するときは忘れがちなので要注意です！
ここまで出来たら「OK」ボタンでダイアログボックスを閉じます。
実際にB2セルのプルダウンをクリックして、ご飯・パン・飲み物が出ることを確認しましょう！
2段目のプルダウン設定
次に、2段階目のプルダウンとしてC列の「商品名」に設定していきましょう。
1段目のプルダウン設定と同様に、設定したいセルのC2セルを選択した状態で、リボンメニューからデータ → データの入力規則のアイコンをクリックし、「データの入力規則」のダイアログボックスを出します。
ダイアログボックス内の最初の項目「入力値の種類」をリストに変更し、2つ目の項目「元の値」のテキストボックスにINDIRECT関数を入れるところまで、先ほどと同じ手順でやってみましょう。
=INDIRECT(
1段目プルダウンリストの時は、常にB1セルを見てほしかったので「$B$1」と絶対参照にしましたが、2段目のプルダウンリストB列の「種類」の内容を見て表示する内容を切り替えたいので、行番号の前の$を外して「$B2」と複合参照にします。
※絶対参照・相対参照・複合参照の操作を忘れてしまった方は、この動画の[01:33]あたりから見てみてください
ここまで出来たら「OK」ボタンでダイアログボックスを閉じます。
この時、B列が空白だと下の画像の様に
「元の値はエラーと判断されます。続けますか？」
とポップアップが出ますが、エラーではないので「はい」で進んでOKです！
実際にB2セル → C2セルのプルダウンを続けてクリックして、「ご飯」を選んだときは「ご飯」の商品リスト、「パン」を選んだとき「パン」商品リストが表示されることを確認しましょう！
にゃんこのひと言🐾
プルダウンを設定して入力が楽になったら、次は『道具』にもこだわってみませんか？
このマウスはトラックボールが操作しやすく疲れにくいので、大量のデータ入力作業が驚くほどスムーズになりますよ🐾
まとめ
お疲れ様でした！
これで「項目が増えても壊れない2段階プルダウン」の完成です！
今回のように「テーブル」と「INDIRECT関数」を組み合わせて作っておけば、後からマスタに新しい商品を追加しても、数式をいじり直す必要は一切ありません。
マスタの表に新しい行を増やすだけで、自動的にプルダウンにも反映されます。
設定中に「エラーと判断されます」というメッセージが出ても、焦らず「はい」で進めば大丈夫、というコツも掴めましたね！
一度この仕組みを作っておけば、仕事の効率がぐんと上がるよ！
見積書や在庫管理など、いろんな場面で応用してみてね！
にゃんこのひと言🐾
お疲れ様でした！入力作業の効率化が進むと、ついつい画面に集中して猫背になりがちです。スタンドを使って目線を少し上げるだけで、夕方の肩や首の疲れが驚くほど軽くなります。
良い姿勢を保つことが、長く楽しくExcelを使い続けるコツですよ🐾