前の選択で次の選択肢が変わる連動ドロップダウンリスト

解説記事内の画像はExcel 2016のものですが、操作方法は下記のバージョンで同じです。
  • Excel 2016, 2013, 2010
Excel 2016・2013・2010用 練習ファイル
(indirect-list.xlsx 11.2KB)
必要なデータ入力がすべて終わった状態になっています。

前の選択で次の選択肢が変わる連動ドロップダウンリスト01 1段階目の選択によって(この図ならB列)、2段階目の選択肢(この図ならC列)が変わる、

前の選択で次の選択肢が変わる連動ドロップダウンリスト02 連動するドロップダウンリスト。

実はこの連動するドロップダウンリスト、完成後に2段階目の選択肢のデータが増えたり減ったりする可能性がある場合は、ない場合と比べて、一工程分だけ操作が多くなります。

前の選択で次の選択肢が変わる連動ドロップダウンリスト03 例えば、1段階目で「東北地方」を選ぶと、2段階目の選択肢で東北6県の名前が選択できるようにする場合、急に新しい県が増えて東北7県になるということはありません。
ですので、こういった場合は、選択肢のデータが増えたり減ったりする可能性がない場合。

前の選択で次の選択肢が変わる連動ドロップダウンリスト02 ところが、1段階目で部署を選ぶと、2段階目の選択肢でその部署に所属する人の名前が選択できるようにする場合、退社でその部署の人がいなくなる場合もありますし、入社で人が増える場合もあります。
ですので、こういった場合は、選択肢のデータが増えたり減ったりする可能性がある場合。

2段階目の選択肢のデータが増えたり減ったりする可能性があるのか、ないのか分からない!という方は、一工程分の操作が加わるとはいえ、難しい操作ではないので、可能性がある場合として操作する方がおすすめです。

それでは操作に入っていきましょう!
2段階目の選択肢のデータが増えたり減ったりする可能性がある場合も、ない場合も、このまま読み進めてください。

選択肢となる元データを入力

まずはドロップダウンリストの選択肢として表示される、元データを入力します。
ドロップダウンリスト自体を設定する箇所の脇など、空いているところに入力してもOKですが、別のシートに入力した方がスッキリした印象になるので、今回は別のシートに入力することにします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト05 ドロップダウンリストの選択肢となる、元データを入力するところ専用として、新しいシートを追加し、

前の選択で次の選択肢が変わる連動ドロップダウンリスト06 そのシートの名前を、分かりやすいものに変更しておきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト04 【Office 2010】
Office 2010では、規定で新しいシートが3枚付いているので、「Sheet2」を使えばOKです。
もし新しいシートが無い場合には、シート見出しの右端にある[ワークシートの挿入]をクリックすると、新しいワークシートを追加できます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト07 追加した新しいシートの先頭行に、1段階目の選択肢の内容をズラッと入力します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト08 これが後々、1段階目のドロップダウンリストに表示されるデータとなります。

前の選択で次の選択肢が変わる連動ドロップダウンリスト09 そして、お好みでセルに色などを設定しておくと、パッと見で分かりやすくなるので、この後の操作がしやすいです。

前の選択で次の選択肢が変わる連動ドロップダウンリスト10 次に、先頭行に入力したものの内容となるデータを入力していきます。
この内容の部分が後々、

前の選択で次の選択肢が変わる連動ドロップダウンリスト68 2段階目の選択肢として表示されるデータとなります。

ここで、完成後に、2段階目の選択肢のデータが増えたり減ったりする可能性がある方と、ない方で、操作が分かれます。

選択肢をテーブル化

完成後に、2段階目の選択肢が増えたり減ったりする可能性がある場合、元データを追加するだけでドロップダウンリストの選択肢が自動で増えるよう、選択肢の元となる部分をテーブル化しておきます。

本来、一般的な表をテーブルに変換する際は、テーブルに変換したいセル範囲のどれか1つのセルを選択するだけで、テーブル化したい範囲を自動でExcelさんに認識してもらえるのですが、今回はパッと見 表に見える範囲の、それぞれの列を、別々のテーブルとして設定していきたいので、テーブルに変換したい範囲のセルをすべて選択していきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト11 まずは1つ目の列から。
1段階目の選択肢となる先頭行のセル、2段階目の選択肢となるセル範囲を合わせて範囲選択します。
テーブルには、その列が何を表すかを示す項目行が必ず必要なので、先頭行のセル(この図ならA1番地)の選択をお忘れなく。

前の選択で次の選択肢が変わる連動ドロップダウンリスト12 [ホーム]タブ、

前の選択で次の選択肢が変わる連動ドロップダウンリスト13 [スタイル]グループの[テーブルとして書式設定]をクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト14 一覧から好みのものをクリックで選択します。
ちなみに、ここで表示される一覧は、操作中の文書に設定されているテーマによって変わってくるので、色が図と同じじゃなくても、気にする必要はありません。

前の選択で次の選択肢が変わる連動ドロップダウンリスト15 表示されたダイアログボックスで、テーブルに変換したい範囲が正しいかを確認し、更に[先頭行をテーブルの見出しとして使用する]にチェックが付いていることを確認し、[OK]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト16 1列目を1つ目のテーブルに変換することができました!


前の選択で次の選択肢が変わる連動ドロップダウンリスト17 お次は2つ目の列。
1段階目の選択肢となる先頭行のセル、2段階目の選択肢となるセル範囲を合わせて範囲選択します。
テーブルには、その列が何を表すかを示す項目行が必ず必要なので、先頭行のセル(この図ならB1番地)の選択をお忘れなく。

前の選択で次の選択肢が変わる連動ドロップダウンリスト12 [ホーム]タブ、

前の選択で次の選択肢が変わる連動ドロップダウンリスト13 [スタイル]グループの[テーブルとして書式設定]をクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト14 一覧から好みのものをクリックで選択します。
1列目をテーブル化するときに選んだものとは、同じでもOKですし、別のものでもOK。
どちらでもいいです。
今回の私の場合は、1列目とは別のテーブルだよ、というのを見た目でアピールするため、別の色合いのものを選択することにします。
また、ここで表示される一覧は、操作中の文書に設定されているテーマによって変わってくるので、色が図と同じじゃなくても、気にする必要はありません。

前の選択で次の選択肢が変わる連動ドロップダウンリスト18 表示されたダイアログボックスで、テーブルに変換したい範囲が正しいかを確認し、更に[先頭行をテーブルの見出しとして使用する]にチェックが付いていることを確認し、[OK]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト19 2列目もテーブルに変換することができました!


前の選択で次の選択肢が変わる連動ドロップダウンリスト20 ここまでの操作を繰り返して、すべての列をそれぞれのテーブルとして変換しておきます。

1段階目の選択肢となる元データに名前を定義する

ここからは、完成後に選択肢のデータが増えたり減ったりする可能性がある場合、ない場合ともに同じ操作となります。

前の選択で次の選択肢が変わる連動ドロップダウンリスト21 完成後に選択肢のデータが増えたり減ったりする可能性がない場合の方が、この章の図を見ると、急にセルに色が付いているのでビックリすると思いますが、これは完成後に選択肢のデータが増えたり減ったりする可能性がある場合の方用に、前の章で加えた操作によるものなので、

前の選択で次の選択肢が変わる連動ドロップダウンリスト22 完成後に選択肢のデータが増えたり減ったりする可能性がない場合の方は、このように先頭行にしか色が付いていない状態のはず。
このままの状態で操作していただいて、何の問題もありません。


それではここから、1段階目の選択肢となる元データに、名前を定義していきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト23 まずは1段階目の選択肢となる、先頭行のセルを範囲選択したら、

前の選択で次の選択肢が変わる連動ドロップダウンリスト24 名前ボックスでクリックして、好みの名前を入力します。
入力が終わったら、文字カーソルが表示されなくなるまで[Enter]キーを押して、名前の入力を確定します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト25 1段階目の選択肢となる、先頭行のセル範囲に、名前を定義することができました!

2段階目の選択肢となる元データに名前を定義する

次は、2段階目の選択肢となるセル範囲を、各ブロックごとに名前を定義していきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト26 まずは1列目から。
1列目の先頭行(この図ならA1番地)は、1段階目の選択肢となるデータです。
今は2段階目の選択肢となる部分に名前を定義したいわけですから、1列目の先頭行を除いた、内容の部分のみ範囲選択します。
この時、完成後に選択肢のデータが増えたり減ったりする可能性がある場合の操作をしている方は、前の章でテーブル化していますので、テーブルならではの選択方法を使えば、この範囲をワンクリックで選択できて便利です。

前の選択で次の選択肢が変わる連動ドロップダウンリスト27 範囲選択が終わったら、名前ボックスでクリックして、名前を入力します。
この時、名前ボックスに入力する名前は、先頭行のセルのデータ、つまり1段階目のデータと必ず同じ名前にします
これ、めちゃくちゃ大切なポイントです!

1段階目のデータと同じ名前にするからこそ、連動するドロップダウンリストになります!

入力が終わったら、文字カーソルが表示されなくなるまで[Enter]キーを押して、名前の入力を確定します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト28 本来なら、名前を定義した直後は、その名前がこのボックスに表示されます。
完成後に選択肢のデータが増えたり減ったりする可能性がない場合の操作をしている方は、このようになります。

前の選択で次の選択肢が変わる連動ドロップダウンリスト29 ところが、完成後に選択肢のデータが増えたり減ったりする可能性がある場合の操作をしている方は、テーブル化しているところにかぶせて名前を定義したので、その名前が消えて、その範囲のテーブル名が表示されてしまいます。
でも、ちゃんと名前は設定されていますので、心配する必要はありません。


前の選択で次の選択肢が変わる連動ドロップダウンリスト30 2列目も同様に、2段階目の選択肢となる部分に名前を定義していきます。
2列目の先頭行(この図ならB1番地)は、1段階目の選択肢となるデータです。
今は2段階目の選択肢となる部分に名前を定義したいわけですから、先頭行を除いた、内容の部分のみ範囲選択します。
この時、完成後に選択肢のデータが増えたり減ったりする可能性がある場合の操作をしている方は、前の章でテーブル化していますので、テーブルならではの選択方法を使えば、この範囲をワンクリックで選択できます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト31 範囲選択が終わったら、名前ボックスでクリックして、名前を入力します。
この時、名前ボックスに入力する名前は、先頭行のセルのデータ、つまり1段階目のデータと必ず同じ名前にします
これ、めちゃくちゃ大切なポイントです!

1段階目のデータと同じ名前にするからこそ、連動するドロップダウンリストになります!

入力が終わったら、文字カーソルが表示されなくなるまで[Enter]キーを押して、名前の入力を確定します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト32 本来なら、名前を定義した直後は、その名前がこのボックスに表示されます。
完成後に選択肢のデータが増えたり減ったりする可能性がない場合の操作をしている方は、このようになります。

前の選択で次の選択肢が変わる連動ドロップダウンリスト33 ところが、完成後に選択肢のデータが増えたり減ったりする可能性がある場合の操作をしている方は、テーブル化しているところにかぶせて名前を定義したので、その名前が消えて、その範囲のテーブル名が表示されてしまいます。
でも、ちゃんと名前は設定されていますので、心配する必要はありません。


前の選択で次の選択肢が変わる連動ドロップダウンリスト34 ここまでの操作を繰り返して、残りの列にも、2段階目の選択肢となる各ブロックごとに名前を定義していきます。
もちろん、その名前は、各列の先頭行のセルのデータ、つまり1段階目のデータと必ず同じ名前にします

1段階目のデータと同じ名前にするからこそ、連動するドロップダウンリストになります!

これで、下準備は完了です!

連動するドロップダウンリストを設定!まずは1段階目

各選択肢の元となるデータについては設定が終わったので、今度は連動するドロップダウンリストそのものの設定に入っていきましょう!

前の選択で次の選択肢が変わる連動ドロップダウンリスト35 先ほどまでは選択肢の元となるデータを入力したシートで操作していましたが、今度は、ドロップダウンリスト自体を設定するシートに切り替えておきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト36 まずは1段階目のドロップダウンリストを設定したいセルを範囲選択します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト37 [データ]タブ、[データツール]グループの[データの入力規則]ボタン(▼がある方ではなく、絵柄の方のボタン)をクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト38 [設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]欄でクリックして、この欄に文字カーソルを入れます。
この[元の値]欄には、ドロップダウンリストの元となるデータが何なのかを設定します。
元となるデータは、先ほど定義した名前の範囲なので、キーボードの[F3]キーをポンッと押すと、

前の選択で次の選択肢が変わる連動ドロップダウンリスト39 この文書に定義された名前が一覧表示されるので、1段階目の選択肢用につけた名前をクリックで選択します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト23 1段階目の選択肢用につけた名前とは、選択肢となる元データ用のシートの、先頭行のセル範囲につけた名前のことです。

前の選択で次の選択肢が変わる連動ドロップダウンリスト39 1段階目の選択肢用の名前を選択したら、[OK]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト40 [元の値]欄に、選択した名前を設定することができました。
これで、「この名前の範囲のデータを、リストの選択肢として表示してね」という設定ができたわけです。
最後にダイアログボックス右下の[OK]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト41 1段階目のドロップダウンリストを設定することができました!

連動するドロップダウンリストの2段階目を設定!

前の選択で次の選択肢が変わる連動ドロップダウンリスト42 次は、連動するドロップダウンリストの2段階目の設定をします。
2段階目のドロップダウンリストを設定したいセルを範囲選択し、

前の選択で次の選択肢が変わる連動ドロップダウンリスト37 [データ]タブ、[データツール]グループの[データの入力規則]ボタン(▼がある方ではなく、絵柄の方のボタン)をクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト38 [設定]タブの[入力値の種類]で[リスト]を選択し、[元の値]欄でクリックして、この欄に文字カーソルを入れます。
この[元の値]欄には、ドロップダウンリストの元となるデータが何なのかを設定します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト01 2段階目の選択肢は、1段階目で選択したデータと同じ名前がついた範囲をリスト表示したいので、INDIRECT関数を使って、「左隣のセルに入力されたデータと同じ名前がついた範囲」が、ドロップダウンリストの選択肢として表示されるように設定します。
ここで、「どうしてINDIRECT関数を使うの?」と思った方も多いはず。
これについては、「INDIRECT関数の基本」で詳しくご説明しています。

前の選択で次の選択肢が変わる連動ドロップダウンリスト44 そしてここからは現在範囲選択している、2段階目のドロップダウンリストを設定する範囲の先頭、C2番地を基準に考えていきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト43 [元の値]欄に、図のように「 =INDIRECT( 」と入力したら、

前の選択で次の選択肢が変わる連動ドロップダウンリスト01 C2番地のドロップダウンリストの選択肢は、1段階目のB2番地で選択したものによって変わってくるので、

前の選択で次の選択肢が変わる連動ドロップダウンリスト45 [元の値]欄に入力した「 =INDIRECT( 」の後ろに文字カーソルが点滅している状態で、

前の選択で次の選択肢が変わる連動ドロップダウンリスト46 B2番地をクリックすると、図のようにダイアログボックスのINDIRECT関数を設定した部分にB2番地が絶対参照の状態で入ってきます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト47 C列にある、2段階目のドロップダウンリストを設定したいセルは、どのセルも絶対にB2番地の内容によって選択肢を変えたいわけではなく、C3番地のセルならB3番地、C4番地のセルならB4番地と、左隣のセルの内容によって選択肢を変えたいので、

前の選択で次の選択肢が変わる連動ドロップダウンリスト48 キーボードの[F4]キーをポンッポンッと何回か押し、「B」の左側にも「2」の左側にも「$」が付いていない状態にします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト49 「=INDIRECT(B2」まで入力が終わったので、最後に関数の終わりを表す、括弧終わりを入力します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト59 この数式は、「B2番地に入力されている文字を、セル範囲として認識してね」という意味になります。
関数名や括弧などの記号は、半角で入力します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト58 これで設定完了!
最後に、ダイアログボックス右下の[OK]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト50 このようなメッセージが表示されますが、これは1段階目のデータがまだ入っていないので、2段階目の選択肢を設定しようがないよ、という意味。
特に問題ありませんので、[はい]ボタンをクリックします。

前の選択で次の選択肢が変わる連動ドロップダウンリスト51 これで、2段階の連動するドロップダウンリストが完成です!

前の選択で次の選択肢が変わる連動ドロップダウンリスト52 1段階目のドロップダウンリストが設定してあるセルを選択すると、ドロップダウンリストの[▼]が表示されるので、クリックして、

前の選択で次の選択肢が変わる連動ドロップダウンリスト53 リストから好みのものをクリックで選択します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト54 その隣にある2段階目のドロップダウンリストが設定してあるセルを選択すると、ドロップダウンリストの[▼]が表示されます。
クリックすると、

前の選択で次の選択肢が変わる連動ドロップダウンリスト01 1段階目に選択したものに沿った選択肢が表示されます。

前の選択で次の選択肢が変わる連動ドロップダウンリスト55 これだけでは、2段階目が1段階目の内容によって変わるかはまだ分からないので、他のセルでも確認してみましょう。
1段階目を設定したセルを選択して[▼]をクリックし、

前の選択で次の選択肢が変わる連動ドロップダウンリスト56 一覧から好みのものを選択します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト57 その隣にある、2段階目のセルを選択して[▼]をクリックすると、

前の選択で次の選択肢が変わる連動ドロップダウンリスト02 1段階目の内容に合わせて、2段階目の選択肢が変わっていることが分かります!

完成後に選択肢のデータが増えたり減ったりしたら

前の選択で次の選択肢が変わる連動ドロップダウンリスト60 2段階目の選択肢のデータを増やしたり減らしたくなったら、操作は簡単。
これは、完成後に、2段階目の選択肢のデータが増えたり減ったりする可能性がある場合の操作をしてきた方のためのもの。

前の選択で次の選択肢が変わる連動ドロップダウンリスト06 2段階目の選択肢のデータを増やしたい場合も、減らしたい場合も、選択肢の元となるデータを入力したシートで操作するだけ。

前の選択で次の選択肢が変わる連動ドロップダウンリスト61 データが増えた場合には、単純にデータを入力するだけ。
各ブロックをテーブル化した上で、名前を定義しているので、データを追加するだけでテーブル範囲が拡張し、それと共に名前が定義された範囲も自動的に拡張されます

前の選択で次の選択肢が変わる連動ドロップダウンリスト62 よって、2段階目のドロップダウンリストにもそれが反映されて、選択肢が自動的に増えました


前の選択で次の選択肢が変わる連動ドロップダウンリスト63 ドロップダウンリストの選択肢を減らしたい場合も簡単!

前の選択で次の選択肢が変わる連動ドロップダウンリスト06 選択肢の元となるデータを入力したシートで、

前の選択で次の選択肢が変わる連動ドロップダウンリスト64 減らしたいセルを選択し、選択したセルのところで右クリック、

前の選択で次の選択肢が変わる連動ドロップダウンリスト65 [削除]から[テーブルの行]をクリックします。
この時、「行を削除するということは、同じ行にある他のデータも消えるのでは?」と心配になるかもしれませんが、今回は、それぞれが1列しかないテーブルなので、テーブルの行を削除するということは、結局、選択したそのセルだけを削除することと同じ、というわけです。

前の選択で次の選択肢が変わる連動ドロップダウンリスト66 データを削除することができました!
そして、各ブロックはテーブル化した上で、名前を定義しているので、削除に伴い、テーブル範囲も縮小し、それと共に名前が定義された範囲も自動的に縮小します。

前の選択で次の選択肢が変わる連動ドロップダウンリスト67 よって、2段階目のドロップダウンリストにもそれが反映されて、選択肢が自動的に減りました!


ここでは、2段階目のデータが増減することについて触れてきましたが、1段階目のデータを増減したい場合にはどうするの?と思った方もいらっしゃるかもしれません。
1段階目の元データ範囲、つまり先頭行の部分だけをまとめて1つのテーブルとしていない(正確に言うと、この範囲だけをテーブルにはできない)ので、2段階目のリストのように、元データの追加や削除だけで1段階目のリスト表示が変わることはありません。
1段階目のデータ範囲に設定した名前の範囲を変更する操作が必要になります。
この操作の詳細については、また別の機会にご紹介したいと思います。

今回の操作は、ドロップダウンリストテーブル名前の定義INDIRECT関数と、様々な機能を複合的に使った応用編。
「どうしてその機能を使ったんだろう?」と不思議に思った方は、是非、下記のページをご覧ください!

スポンサーリンク