エクセルのプルダウンメニュー(ドロップダウンリスト)を連動する
- Release
- Update
Excel(エクセル)で決まった形式の値を入力したいとき、プルダウンリスト(またはドロップダウンメニュー)が便利です。入力の間違いを防ぐことができるので作業の効率化につながります。
今回は、「プルダウンリスト」の応用編として複数のプルダウンリストを連動させる方法をご紹介します。
例えば地域名、都道府県のような連動性のある値を入力したい場合、東北地方を選択すると、青森県や岩手県からといった東北地方のみのリストが自動で表示されるように絞り込む方法です。
名前の定義とINDIRECT関数を組み合わせる方法と、OFFSET関数とMATCH関数を組み合わせる方法の2つを説明しています。
リストの元になるデータを用意しておきます。今回は、Sheet2に上図のような地方に対応した都道府県のデータを用意しました。
プルダウンメニュー(ドロップダウンリスト)を連動する方法(名前の定義×INDIRECT関数)
名前の定義とINDIRECT関数を組み合わせてプルダウンメニュー(ドロップダウンリスト)を連動する方法を説明します。今回は2段階を紹介していますが、3段階、4段階のプルダウンメニューを連動させることも可能です。
作業時間:10分
基準となるリストのデータに名前を定義する
今回は、基準となる地方名のプルダウンリストを選択することでその地方に属する都道府県を連動するプルダウンリストを設定していきます。①地方名の範囲(B2~B8)を選択します。②【数式】タブ、③【名前の定義】の順に選択します。
新しい名前を定義する
「新しい名前」ダイアログボックスが表示されるので、①名前に『地方』と入力し、②【OK】ボタンを押します。
選択範囲から名前を作成する
①B2からL8までのすべてのデータを選択し、②【数式】タブから③【選択範囲から作成】を選択します。
名前を作成する選択範囲を設定する
「選択範囲から名前を作成」ダイアログボックスが表示されたら、①【左端列】のみにチェックを入れて②【OK】ボタンを押します。
基準となるプルダウンリストを作成する
①【Sheet1】へ戻り、地方名を選択するプルダウンリストを作成していきます。②C2セルを選択し、③【データ】タブの、④【データの入力規則】を選択します。
基準となるプルダウンリストのデータの入力規則を設定する
①「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」には『=地方』と入力し、④【OK】ボタンを押します。
連動させるプルダウンリストを作成する
地方名に連動した都道府県を選択するプルダウンリストを作成していきます。①C3を選択した状態で、②【データ】タブの、③【データの入力規則】を選択します。
連動させるプルダウンリストのデータの入力規則を設定する
「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」には『=INDIRECT(C2)』と入力し、④【OKボタン】を押します。INDIRECT関数は「INDIRECT(参照文字列, [参照形式])」という書式で構成され、指定される文字列への参照を返します。
アラートを消す
C2セルが空白の場合、「元の値はエラーと判断されます。続けますか?」というアラートが表示されますが【はい】を押します。
連動の動作を確認する
実際にプルダウンリストが連動しているか確認してみましょう。C2セルのプルダウンリストから【北海道・東北】を選択します。 C3セルのプルダウンリストを表示すると北海道・東北に属する都道府県がリストに表示されました。
プルダウンメニュー(ドロップダウンリスト)を連動する方法(OFFSET関数×MATCH関数)
OFFSET関数とMATCH関数を組み合わせてプルダウンメニュー(ドロップダウンリスト)を連動する方法を説明します。
基準となるプルダウンリストを作成します。①C2セルを選択し、②【データ】タブ、③【データの入力規則】の順に選択します。
①「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」右端の【↑】ボタンを押します。
①連動したいデータがある【Sheet2】を開きます。②地方名の範囲(B2~B8)を【ドラッグ】で選択し、Enterを押します。
【OK】ボタンを押します。
連動させるプルダウンリストを作成します。①C3セルを選択し、②【データ】タブ、③【データの入力規則】の順に選択します。
「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」に『=OFFSET(Sheet2!$B$2,MATCH($C$2,Sheet2!B2:B8,0)-1,1,1,10)』と入力し、④【OK】ボタンを押します。
複数のプルダウンリストを連動させるためにOFFSET関数とMATCH関数を組み合わせて指定しています。
- OFFSET関数は、基準セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲を返します。書式としては「OFFSET(基準, 行数, 列数, [高さ], [幅])」のように使用します。
- MATCH関数は、指定したセルの範囲を検索し、その範囲内の項目の相対的な位置を返します。書式は「MATCH(検査値, 検査範囲, [照合の型])」のように使用します。
長い数式となっていますので分解して理解しましょう。
まずはOFFSET関数の引数として使われているMATCH関数が返すものを見てみましょう。
上図のようにSheet1のC2セルの値をSheet2のB2からB8セルから検索します。Sheet1のC2セルが「関東」の場合、上から2番目なので「2」を返します。
OFFSET関数は「OFFSET(基準, 行数, 列数, [高さ], [幅])」のように記述します。「基準」をSheet2のB2セルにし、「行数」はSheet1のC2セルの値によって変動させたいのでMATCH関数で一致した値を元に上から何番目の行なのかを指定します。
Sheet1のC2セルが「関東」の場合、上から2番目ですが、OFFSET関数で「行数」を指定するときには1を引きます。
C2セルが空白の場合、「元の値はエラーと判断されます。続けますか?」というアラートが表示されますが【はい】を押します。
実際にプルダウンリストが連動しているか確認してみましょう。C2セルのプルダウンリストから【関東】を選択します。 C3セルのプルダウンリストを表示すると関東に属する都道府県がリストに表示されました。
このページを見ている人におすすめの商品
その他、様々なプルダウンメニュー(ドロップダウンリスト)の編集方法
この記事はお役に立ちましたか?
- はい
- いいえ