Excelで連動する複数のドロップダウンリストを作成する方法

Excelのドロップダウンリストは、データ入力を効率化できる強力な機能です。セルにドロップダウン矢印が表示され、クリックすると選択肢の一覧が現れるため、ユーザーが直接入力する必要がありません。

入力ミスや表記ゆれを防げるうえに、Excelではリストの項目を別のセル範囲から参照することも可能です。

さらに、データの入力規則を工夫して設定すれば、複数のドロップダウンリストを「連動」させることもできます。1つ目のリストで選んだ内容に応じて、2つ目のリストに表示される選択肢が自動的に切り替わる仕組みです。

連動型ドロップダウンリストはどんな場面で役立つ？

オンラインフォームではおなじみの挙動ですね。例えば、住所入力フォームで都道府県を選ぶと、市区町村の候補が絞り込まれるようなイメージです。この仕組みをExcelでも再現できれば、オンラインフォームと同等の高度な入力シートを作成でき、ユーザーの回答に合わせてシート自体が変化するようになります。

具体例として、パソコン修理の受付用に、ユーザーから故障箇所の情報を収集するスプレッドシートを考えてみましょう。入力項目は次のような構成になります。

• パーツ名：モニター、マウス、キーボード、本体システム
• パーツ種類：
  • モニター：ガラス、筐体、電源コード、内部基板
  • マウス：ホイール、LEDライト、コード、ボタン、筐体
  • キーボード：キー、筐体、メンブレン、コード、内部基板
  • 本体システム：筐体、ボタン、ポート、電源、内部基板、OS

このツリー構造からも分かるように、「パーツ種類」で選択できる項目は、1つ目のドロップダウンリストでどのパーツ名が選ばれたかに依存します。

例えば、シートの初期状態は以下のようになっているとします。

ここで連動型ドロップダウンリストを作成すれば、B1のリストで選択した項目をもとに、B2のリストの内容を自動的に制御できます。

それでは、実際の設定手順を見ていきましょう。

ステップ1：ドロップダウンリストの元データシートを作成する

設定をきれいに管理するために、まずExcelで新しいシート（タブ）を作成し、そこにすべてのリスト項目をまとめて入力しておくのがおすすめです。

連動型ドロップダウンリスト用の元データは、次のように表形式で作成します。1行目の見出しには、1つ目のドロップダウンリストに表示したい項目（パーツ名）を横に並べ、その下に対応する2つ目のリスト項目（パーツ種類）を縦に列挙していきます。

次に、各列の範囲に名前を付けておきます。後ほどデータの入力規則を設定する際に、この名前を使って正しい範囲を参照できるようにするためです。

名前の付け方は簡単です。各列の項目部分を選択し、列見出し「A」の上にある名前ボックスに、その列の見出しと同じ名前を入力するだけです。

例えば、セルA2〜A5を選択し、その範囲に「モニター」という名前を付けます。

これをすべての列に対して繰り返し、必要な範囲にそれぞれ適切な名前を付けていきます。

「選択範囲から作成」機能で一括命名する方法

手作業での命名が面倒な場合は、Excelの「選択範囲から作成」機能を使うと便利です。上記の手作業と同じ結果を、ワンクリックで実現できます。

手順は次のとおりです。まず、元データシートの表全体を選択します。次にメニューの数式タブを開き、リボンの選択範囲から作成をクリックします。

ポップアップウィンドウが表示されるので、最上行のみにチェックが入っていることを確認してOKをクリックします。

これで、最上行の見出しの値が、その下の各範囲の名前として自動的に登録されます。

ステップ2：1つ目のドロップダウンリストを設定する

元データの準備ができたら、いよいよ連動型ドロップダウンリストの本体を設定していきます。手順は以下のとおりです。

1. 元のシートに戻り、最初のラベルの右側にある空白セルを選択します。次にメニューのデータタブを開き、リボンのデータの入力規則をクリックします。

2. データの入力規則ウィンドウが開いたら、入力値の種類で「リスト」を選択します。続いて元の値欄の右側にある上向き矢印アイコンをクリックします。これで、ドロップダウンリストのソースとなるセル範囲を選択できるようになります。

3. 元データを作成した2枚目のシートに移動し、見出し行のセルのみを選択します。これらの見出しが、選択中のセルに表示される1つ目のドロップダウンリストの項目になります。

4. 選択ウィンドウの下向き矢印をクリックしてデータの入力規則ウィンドウを展開すると、元の値欄に選択した範囲が表示されていることを確認できます。OKをクリックして完了です。

5. 元のシートに戻ると、1つ目のドロップダウンリストに、2枚目のシートの見出し項目がすべて表示されているはずです。

これで1つ目のドロップダウンリストは完成です。次は、これと連動する2つ目のリストを作成しましょう。

ステップ3：連動する2つ目のドロップダウンリストを設定する

1つ目のセルで選択された内容に応じてリスト項目を読み込みたい、2つ目のセルを選択します。

先ほどと同じ手順でデータの入力規則ウィンドウを開き、入力値の種類で「リスト」を選択します。ポイントは元の値欄です。ここにINDIRECT関数を使った数式を入力することで、1つ目のドロップダウンリストでの選択内容に応じたリスト項目を引き込めるようになります。

入力する数式は次のとおりです。

=INDIRECT($B$1)

INDIRECT関数はどのように働くのでしょうか？

この関数は、文字列で指定された有効なExcel参照（この場合はセル範囲）を返します。ここでの文字列とは、1つ目のセル（$B$1）から渡される範囲名に相当します。つまりINDIRECT関数は、範囲名を受け取り、その名前に紐づく正しいセル範囲をドロップダウンの入力規則へ渡す役割を果たします。

注意：1つ目のドロップダウンリストで値を選択していない状態で2つ目の入力規則を設定すると、エラーメッセージが表示されることがあります。その場合は「はい」を選択してエラーを無視し、先に進めて問題ありません。

設定が完了したら、新しい連動型ドロップダウンリストをテストしてみましょう。1つ目のリストでパーツ名を選択し、続いて2つ目のリストを開きます。選択したパーツに対応する項目だけが表示されれば成功です。これらは、元データシートの該当列に入力しておいたパーツ種類です。

Excelで連動型ドロップダウンリストを活用しよう

このように、INDIRECT関数とデータの入力規則を組み合わせることで、スプレッドシートをぐっと動的なものにできます。他のセルでの選択内容に応じて後続のドロップダウンリストの内容を変化させれば、ユーザーにとって格段に使いやすくなり、集まるデータの質も向上します。

ぜひ今回紹介したテクニックをいろいろ試して、自分のスプレッドシートに合った便利な連動型ドロップダウンリストを作ってみてください。おすすめのコツや活用事例があれば、ぜひコメント欄で共有してください。