Office Hack

エクセルのプルダウンメニュー(ドロップダウンリスト)を連動する

  • by kenji
  • Release
  • Update

Excel(エクセル)で決まった形式の値を入力したいとき、プルダウンリスト(またはドロップダウンメニュー)が便利です。入力の間違いを防ぐことができるので作業の効率化につながります。

今回は、「プルダウンリスト」の応用編として複数のプルダウンリストを連動させる方法をご紹介します。

例えば地域名、都道府県のような連動性のある値を入力したい場合、東北地方を選択すると、青森県や岩手県からといった東北地方のみのリストが自動で表示されるように絞り込む方法です。

名前の定義とINDIRECT関数を組み合わせる方法と、OFFSET関数とMATCH関数を組み合わせる方法の2つを説明しています。

元データを用意する

リストの元になるデータを用意しておきます。今回は、Sheet2に上図のような地方に対応した都道府県のデータを用意しました。

プルダウンメニュー(ドロップダウンリスト)を連動する方法(名前の定義×INDIRECT関数)

名前の定義とINDIRECT関数を組み合わせてプルダウンメニュー(ドロップダウンリスト)を連動する方法を説明します。今回は2段階を紹介していますが、3段階、4段階のプルダウンメニューを連動させることも可能です。

作業時間:10分

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

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

    今回は、基準となる地方名のプルダウンリストを選択することでその地方に属する都道府県を連動するプルダウンリストを設定していきます。①地方名の範囲(B2~B8)を選択します。②【数式】タブ、③【名前の定義】の順に選択します。

  2. 新しい名前を定義する

    新しい名前

    「新しい名前」ダイアログボックスが表示されるので、①名前に『地方』と入力し、②【OK】ボタンを押します。

  3. 選択範囲から名前を作成する

    選択範囲から作成

    ①B2からL8までのすべてのデータを選択し、②【数式】タブから③【選択範囲から作成】を選択します。

  4. 名前を作成する選択範囲を設定する

    選択範囲から名前を作成

    「選択範囲から名前を作成」ダイアログボックスが表示されたら、①【左端列】のみにチェックを入れて②【OK】ボタンを押します。

  5. 基準となるプルダウンリストを作成する

    データの入力規則

    ①【Sheet1】へ戻り、地方名を選択するプルダウンリストを作成していきます。②C2セルを選択し、③【データ】タブの、④【データの入力規則】を選択します。

  6. 基準となるプルダウンリストのデータの入力規則を設定する

    名前の指定

    ①「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」には『=地方』と入力し、④【OK】ボタンを押します。

  7. 連動させるプルダウンリストを作成する

    データの入力規則

    地方名に連動した都道府県を選択するプルダウンリストを作成していきます。①C3を選択した状態で、②【データ】タブの、③【データの入力規則】を選択します。

  8. 連動させるプルダウンリストのデータの入力規則を設定する

    INDIRECT関数の入力

    「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」には『=INDIRECT(C2)』と入力し、④【OKボタン】を押します。INDIRECT関数は「INDIRECT(参照文字列, [参照形式])」という書式で構成され、指定される文字列への参照を返します。

  9. アラートを消す

    元の値はエラートと判断されます

    C2セルが空白の場合、「元の値はエラーと判断されます。続けますか?」というアラートが表示されますが【はい】を押します。

  10. 連動の動作を確認する

    実際にプルダウンリストが連動しているか確認してみましょう。C2セルのプルダウンリストから【北海道・東北】を選択します。 C3セルのプルダウンリストを表示すると北海道・東北に属する都道府県がリストに表示されました。

プルダウンメニュー(ドロップダウンリスト)を連動する方法(OFFSET関数×MATCH関数)

OFFSET関数とMATCH関数を組み合わせてプルダウンメニュー(ドロップダウンリスト)を連動する方法を説明します。

データの入力規則

基準となるプルダウンリストを作成します。①C2セルを選択し、②【データ】タブ、③【データの入力規則】の順に選択します。

データの範囲を選択する

①「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」右端の【↑】ボタンを押します。

セル範囲を選択

①連動したいデータがある【Sheet2】を開きます。②地方名の範囲(B2~B8)を【ドラッグ】で選択し、Enterを押します。

OKボタンを押す

【OK】ボタンを押します。

データの入力規則

連動させるプルダウンリストを作成します。①C3セルを選択し、②【データ】タブ、③【データの入力規則】の順に選択します。

OFFSET関数とMATCH関数の入力

「データの入力規則」ダイアログボックスが表示されたら、①【設定】タブを選択し、②「入力値の種類」を【リスト】に変更、③「元の値」に『=OFFSET(Sheet2!$B$2,MATCH($C$2,Sheet2!B2:B8,0)-1,1,1,10)』と入力し、④【OK】ボタンを押します。

複数のプルダウンリストを連動させるためにOFFSET関数とMATCH関数を組み合わせて指定しています。

  • OFFSET関数は、基準セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲を返します。書式としては「OFFSET(基準, 行数, 列数, [高さ], [幅])」のように使用します。
  • MATCH関数は、指定したセルの範囲を検索し、その範囲内の項目の相対的な位置を返します。書式は「MATCH(検査値, 検査範囲, [照合の型])」のように使用します。

長い数式となっていますので分解して理解しましょう。

MATCH関数

まずはOFFSET関数の引数として使われているMATCH関数が返すものを見てみましょう。

上図のようにSheet1のC2セルの値をSheet2のB2からB8セルから検索します。Sheet1のC2セルが「関東」の場合、上から2番目なので「2」を返します。

OFFSET関数

OFFSET関数は「OFFSET(基準, 行数, 列数, [高さ], [幅])」のように記述します。「基準」をSheet2のB2セルにし、「行数」はSheet1のC2セルの値によって変動させたいのでMATCH関数で一致した値を元に上から何番目の行なのかを指定します。

Sheet1のC2セルが「関東」の場合、上から2番目ですが、OFFSET関数で「行数」を指定するときには1を引きます。

元の値はエラートと判断されます

C2セルが空白の場合、「元の値はエラーと判断されます。続けますか?」というアラートが表示されますが【はい】を押します。

連動の動作を確認する

実際にプルダウンリストが連動しているか確認してみましょう。C2セルのプルダウンリストから【関東】を選択します。 C3セルのプルダウンリストを表示すると関東に属する都道府県がリストに表示されました。

その他、様々なプルダウンメニュー(ドロップダウンリスト)の編集方法



よろしければ参考にならなかった点をお聞かせください

Page Top