決定版-この記事だけで習得できる！エクセルのプルダウンリストの連動方法

悩む男

エクセルでプルダウンリストを作ったんだけど、２つ３つを連動させることはできないのかな？

今回はこんな疑問に答えます。

この記事で紹介する『プルダウンリストを連動させる方法」通りに行えば誰でも簡単にプルダウンリストを連動できます。

なぜなら実際の解除手順をエクセルの画像と共にわかりやすく解説しているからです。

この記事では、プルダウンリストの基礎知識と連動手順を紹介して、その後に具体的な例を画像と共に解説します。

記事を読み終えれば、プルダウンリストを連動する方法は習得でき、実際の業務に利用できます。

プルダウンリストとは何？

プルダウンリストの連動方法を紹介する前に、基礎知識を簡単に説明します。

先ずは、そもそもプルダウンリスト（ドロップダウンリスト）とはなんでしょう？

つまり、入力作業の効率化ですね。

プルダウンリストの連動とはどういうこと？

次に、「連動」とはどういった意味でしょう？

例として、都道府県があります。中部地方を選択すると愛知県や三重県になり、次に愛知県を選択すると、名古屋市や豊田市とリストが自動で表示されるように絞り込む方法です。

なんで連動させる？

それでは、なんで連動させるのでしょうか？

１つのリストではダメなのでしょうか？

連動させることによってどのようなメリットがあるのかみてみます。

1つのリスト内に選択肢が多すぎると、リストを開いたときにスクロールが必要になり、かえって面倒になります。

ドロップダウンリストを複数に分けて、前のリストで選択した値によって次のリストの内容が絞り込まれると使いやすくなります。

プルダウンリストを連動させる方法は２つ。

ここからが今回の記事のメインパート、どのように連動させるかです。

方法は2つあります。

１つ目の「名前の定義」と「INDIRECT関数」を組み合わせる方法ですが、こちらはさらに項目が増えるか、増えないかで方法が異なります。

どちらの方法でも関数を使用することになります。事前に関数の基本的な使い方は身に付けてください。簡単な方法は１番の「名前の定義」と「INDIRECT」関数を使用する方法の方です。関数が苦手な方はこちらだけ読んでいただいても結構です。

先ずは、それぞれの重要用語の解説から。

名前の定義とは何か？

名前の定義を使えば簡単に範囲選択できたり、関数の引数に設定できたり、入力規則のリスト範囲に指定できたり、メリットがたくさんあります。

名前の定義の仕方は３つ

「名前Boxから」と「範囲選択して作成から」は効果がブック全体に及びます。

「名前の定義から」はブックとシート、どちらでも選べます。

テーブル化とは何か？

テーブル化するにもいくつかの決まり事があります。

INDIRECT関数とは何か？

つまり指定したセルの文字列を取り出して表示することです。

OFFSET関数とは何か？

検索・行列関数の１つです。INDIRECT関数と似ていますが、こちらは移動した位置にあるセル範囲を返します。

MATCH関数とは何か？

MATCH関数を使用することで探したいデータが表の何行目、何列目にあるのかを調べることができます。

プルダウンリストを連動させる手順

それでは実際の手順を紹介します。

名前の定義 　x　INDIRECT関数（項目が増えない場合）の手順

名前の定義 　x　INDIRECT関数（項目が増える場合）の手順

OFFSET関数　x　INDIRECT関数　の手順

具体例：画像と共に解説。

それでは、具体例を画像と共に詳しく解説していきます。今回はデパートやモールを例に説明します。大きなデパートでしたらそれぞれのフロア別に商品が分けられています。１階に食料品、2階に衣類、3階に電化製品などです。電化製品の階でも、パソコンや洗濯機などが各コーナーに分かれています。このように枝分かれするデータをプルダウンリストの連動で設定します。

基準となる最初のプルダウンリストを各フロアの「食料品、衣類、電化製品」で設定し、連動されるプルダウンリストを各フロアの商品（果物、パソコンなど）にします。

下記のエクセルデータを元に設定します。

名前の定義 　x　INDIRECT関数（項目が増えない場合）の方法

1,基準となるリストのデータに名前を定義する。

最初のプルダウンリストに設定するリストを選択する。A1セルからA3セルです。

＜数式＞タブの「名前の定義」を選択。

表示された「新しい名前」ダイアログボックスの名前の欄で「フロア」と名前をつけてOK ボタンを押す。

名前Boxから確認すると、A1セルからA3セルが「フロア」と名前が付けられています。

名前Boxに直接入力しても名前を付けられます。

2,選択範囲から名前を作成する。

２つ目のプルダウンリストに設定するデータを「フロア」を含んで選択する。つまり全てのデータを選択して名前を作成します。

＜数式＞タブの「選択範囲から作成」を選択。

表示されたダイアログボックスの「以下に含まれる値から名前を作成」の「左端列」のチェックBOXにチェックをいれてOKボタンを押す。

今回は１つ目のリストデータが左端に作ってあるため「左端列」にチェックを入れます。上端行にデータを作成すれば「上端行」にチェックを入れます。

「選択範囲から作成」で名前を作成すると上記設定の場合、「食料品」、「衣類」、「電化製品」の３つの名前が作成され、項目を含まない右方向の範囲が（果物、パソコン）が設定されます。

上記画像はB1-D1セルを選択したものです。名前Boxを見ると項目名の「電化製品」と表示されます。

3,１つ目のプルダウンリストを作成する。

設定したいセルを選択。B5セルにします。

＜データ＞タブの「データの入力規則」を選択。

表示されたダイアログボックスで「入力値の種類」で「リスト」、

「元の値」に「=フロア」と入力。OKボタンを押す。

１つ目のプルダウンリストができました。

4,連動させるリストを作成する。

設定したいセルを選択。B6セルにします。＜データ＞タブの「データの入力規則」を選択。表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に『=INDIRECT(手順3のセル番号)』と入力してOKを押す。

5,アラートを消す。

B5セルが空白の場合「元の値はエラーと判断されます。続けますか？」とアラートが表示されますが「はい」を押す。

6,完了。動作確認。

動作の確認をします。１つ目のプルダウン リストで「食料品」を選択すると、「果物、野菜、冷凍食品」が選択できるようになりました。

名前の定義 　x　INDIRECT関数（項目が増える場合）の方法

1,データをテーブル化します。

実は先ほどのデパートの具体例の画像ですとテーブル化できません。

テーブル化の規則を思い出してください。先頭行は項目名でなければいけません。テーブル化するときによく陥るミスです。ですので、テーブル化できるように表の行と列を入れ替えたものが下記の画像です。このデータをテーブル化していきます。

1-1,テーブルにしたいデータのどこかを選択して、＜ホーム＞タブの「テーブルとして書式設定」を選択。

表をテーブルに変換するには、テーブルに変換したいセル範囲のどこかのセルを選択状態にしておけばテーブルかしたい範囲を自動でエクセルが認識します。

1-2,任意のスタイルを選択。

表示されたスタイルから好きな色を選びます。自分の見やすい色で結構です。

今回は「中間」の青色にします。

1-3,「テーブルとして書式設定」ダイアログボックスで選択範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れてOKを押す。

テーブルに変換できました。

ここからは「名前の定義 　x　INDIRECT関数（項目が増えない場合）の手順」と同じですので省略します。

OFFSET関数 　x　MATCH関数の方法

1,１つ目のプルダウンリストを作成する。

1-1,設定したいセルを選択。

1-2,＜データ＞タブの「データの入力規則」を選択。

1-3,表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」右端の「↑」を選択。

1-4,プルダウンリストに設定したいデータを選択して「Enter」を押す。

「電化製品、衣類、食料品」のA1,2,3セルを選択します。

1-5,OKボタンを押す。１つ目のプルダウンリストが設定できました。

2,連動させるプルダウンリストを作成する。

2-1,設定したいセルを選択。B6セルに設定します。

2-2,＜データ＞タブの「データの入力規則」を選択。

2-3,表示されたダイアログボックスで「入力値の種類」で「リスト」、「元の値」に「=OFFSET($A$1,MATCH(B5,A1:A3,0)-1,1,1,3)」を入力。OKを押す。

複数のプルダウンリストを連動させるためにOFFSET関数とMATCH関数を組み合わせて使用しています。長い数式ですので分解して説明します。MATCH関数は探したい情報がどこにあるかを見つけるための関数です。

見つけたい１つ目のプルダウン リストの情報「B5セル」が何行目にあるかを探します。（今回の表では行ごとに項目が分かれているため「行」です。列毎に分かれていれば「列」を探します）

書式は：MATCH(検査値,検査範囲,[照合の種類])　でしたね。「検査値」が見つけたい情報、「検査範囲」が探す範囲、「照合の種類」が判定です。

B5セルを見つけるのに、「A1セルからA3セル」までの範囲で探します。判定は、照合の種類：1=以下、0=完全一致、-1=以上　でしたので、完全一致する「0」になります。

次にOFFSET関数です。先ず簡単に関数の説明です。OFFSET関数は、「指定したセル、セル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。」でしたね。

上の画像ですとOFFSET関数が返す値は「ここ」になります。範囲の基準から１行下がり、２列右に移動した１の高さ、幅のセルです。これを今回の数式で見ると書式は下記ですので、

書式：=OFFSET(基準,行数,列数,[高さ],[幅])

基準＝「セル、セル範囲から」です。今回の表ですと「A1セル」です。絶対参照で括って「$A$1」にしてあります。

高さ=今回の表では「電化製品」を例にすると１行だけですので「1」です。

幅=今回の表では「電化製品」を例にすると3列ありますので「3」です。

行数＝基準から上方向、又は下方向へ移動する距離を数値で指定します。

「0」を指定すると基準と同じ行、「1」だと基準の１つ下の行、「-1」だと基準より上の行を指定できます。

この行数を今回はMATCH関数で取り出します。今回の表では「電化製品」を例にすると「1」を返しますが、OFFSET関数の行数の基準となる数字は「0」ですので「-1」にしなければいけません。

3,アラートを消す。

3-1,「元の値はエラーと判断されます。続けますか？」と表示されたアラートで「はい」を押す。

4,完了。動作確認。

１つ目のプルダウンリストで選択した「電化製品」の連動で２つ目のプルダウン リストの「品目」に「TV、パソコン、洗濯機」が選択できるようになりました。

まとめ。

以上でプルダウンリストの連動方法でした。少し長くなりましたが、この記事を復習していただければもうプルダウンリストの連動方法で悩むこともありません。簡単にまとめます。

プルダウンリストの連動には、先ず下記を理解したしてください。

理解できたら実際の連動方法がこちら、

そして最後に下記を理解できれば、あとは手順を確認するだけです。

すこし難しい関数もでてきますが、一つ一つ身につけてください。