ドロップダウンリストの作り方【Excel】連動して別シートを参照する方法を紹介!

正しい【ドロップダウンリスト】の作り方を紹介します。Excelの表のデータを後から追加した場合に、レイアウトの調整や表を編集しなくても自動的にデータが反映されます。別シートのデータを自動的に参照する連動式ドロップダウンリストの作り方を紹介します。

この記事は以下のような方におすすめ!

★Excelの表を簡単に管理したい方!

★Excelで連動式ドロップダウンリストを作成する方法について知りたい!

★ドロップダウンリストを別シートから参照する方法について知りたい!

★ドロップダウンリストの値に対して自動的にデータを参照する方法を知りたい!

スポンサーリンク

ドロップダウンリストとは?

ドロップダウンリストは、別名プルダウンメニューとも呼ばれ、Excelの表のデータをリスト化して選択する機能です。

データ量が多くなるとデータの確認に時間が掛かるようになります。

このような場合には、連動式ドロップダウンリストを作成することにより、データの確認と入力を簡素化できExcelの表の管理を素早く行うことができます。

この記事を最後まで読んで下図のような連動式ドロップダウンリストを作って見ましょう。

(下図の例では支店名を選択すると社員名のリストが反映されます)

(シート1)

(下図の例では社員名を選択すると売上金額が自動的に反映されます)

(別シートの社員別の営業成績表)

(シート2)

ドロップダウンリストのメリット

ドロップダウンリストのメリット

①入力ミスを防止でき簡単に入力できる

②リストのデータを一括で参照できる

②データの追加を自動反映することができる(設定が必要)

自動連動式【ドロップダウンリスト】の作り方

Excelのドロップダウンリストの作成方法について紹介します。

今回は実務でも使用例が多い各支店の社員の営業成績の管理表を例に、ドロップダウンリストを作成する方法を紹介します。

ドロップダウンリストの作成は『データの入力規則』を使用して作成します。

『自動連動式ドロップダウンリスト』は、以下の、5ステップで作成していきます。

ドロップダウンリスト1の作成(データの入力規則)

名前定義の設定:(セル範囲に名前を付ける)

テーブル機能を設定(追加データを自動的に反映)

ドロップダウンリスト2の作成(INDIRECT関数)

別シートを参照(VLOOKUP関数)

各支店の社員名を選択すると別シートの社員別の営業成績表からデータを自動的に反映する方法を採用しています。

サンプル表を作成

Excelでドロップダウンリストを作成する前に、表にデータを入力して準備をしておきます。

今回の例では、各シートには【店舗(社員名)】と【成績表】という名前を付けています。

①シート1には下図のように『支店名』と『社員名』を記入して、表を2個、作成します。

②下図のように別シートに『成績表』というシート名を設定し表を作成します。

ドロップダウンリストの作り方

課題の『自動連動式ドロップダウンリスト』の作り方は、この先の章【ドロップダウンリストの作り方:応用編】を参考にしてください。

ドロップダウンリストの作り方:基本編

ドロップダウンリストの基本的な作成方法について解説します。

①プルダウンメニューを作成するセルを選択します。

今回は例として、セル【A2】を選択します。

■支店別の社員名の表の【支店名】東京・大阪・札幌をドロップダウンリストに追加します。

②Excelのリボンから【データ】タブを選択します。

③『データツール』から【データの入力規則】を選択します。

④『データの入力規則』の『入力値の種類』を【リスト】に選択し『元の値』にドロップダウンリストに使用する項目【東京,大阪,札幌】と入力し【OK】をクリックします。

※【元の値】の入力方法は、値と値の間に【,】カンマを入力します。

ドロップダウンリストの作り方:応用編

課題の『自動連動式ドロップダウンリスト』の作り方の手順はこちらから!

ドロップダウンリスト1の作成

最初にセル【A2】にドロップダウンリストを作成していきます。

①セル【A2】を選択します。

②Excelのリボンから【データ】タブを選択します。

③『データツール』から【データの入力規則】を選択します。

④『データの入力規則』の『入力値の種類』のボックス(すべての値)をクリックします。

⑤「入力値の種類』の一覧から【リスト】を選択します。

⑥『元の値』にカーソルを置きます。

⑦セル【E1:G1】を選択し【OK】をクリックします。

最初のドロップダウンリストの【元の値】に表の支店名【東京・大阪・札幌】を選択します。

支店名のセル【A2】にドロップダウンリストが作成されています。

名前定義の作成

名前定義の設定

Excelの名前定義とは、セル・数値・文字列・数式に『名前』を付けてデータを管理することができ、以降は名前を指定するだけで必要なデータに簡単にアクセスすることができる便利な機能です。

セル【A2】の支店名から社員名を呼び出せるようにするために、各支店の社員名に対して『名前定義』をしておきます。

ドロップダウンリストや関数に応用することで簡単に表のデータを呼び出すことができます。

名前定義は【シート1】と【シート2】の表に設定

①社員名の一覧表のセル【E1:G4】を範囲選択します。

②Excelのリボンから【数式】タブを選択し『定義された名前』のグループから【選択範囲から作成】をクリックします。

③『選択範囲から名前を作成』が表示されましたら『上端行』のみに☑チェックを入れて【OK】をクリックします。

※名前定義の設定に『名前ボックス』を使用しても構いませんが、表の項目すべてに名前定義を設定する場合は『選択範囲か作成』を使用することにより一括で名前を付けることができます。

④次に『シート2』のシート名の『成績表』をクリックします。

■シート2の社員別の成績表が表示されます。

⑤Excelのリボンから【数式】タブを選択し『定義された名前』の一覧から【名前の管理】をクリックします。

⑥『名前の管理』が表示されましたら【新規作成】をクリックします。

⑦『新しい名前』のダイアログボックスの『名前』の欄に、任意の名前(成績表)と入力し『範囲』を『ブック』にしていします。

⑧『参照範囲』に、シート2『成績表』のセル範囲【$A$1:$B$10】を絶対参照で範囲選択して【OK】をクリックします。

※参照範囲をクリックして、シート2の表を選択すると自動的に絶対参照になります。

名前ボックスの使い方

名前定義を設定する場合に『名前ボックス』を使用しても構いません。

名前定義の設定に『名前ボックス』を使用する方法を解説します。

※『名前ボックス』を使用して名前定義を設定した場合は、ブック全体に適用されます。

①名前定義の参照先(セル範囲)を範囲選択し『名前ボックス』に名前を付け【Enter】キーを押します。

今回は、東京支店の社員名のセル【E2:E4】を選択し名前を【東京】に設定しています。

②東京支店の社員名、セル【E2:E4】に名前に【東京】が設定されました。

名前定義の確認方法

名前定義は『名前の管理』から確認と編集を行うことができます。

①Excelのリボンから【数式】タブを選択し『定義された名前』の一覧から【名前の管理】をクリックします。

作成された『名前の定義』の一覧を確認することができます。

②『名前定義』の確認後は画面の右下の【閉じる】をクリックします。

(今回は全部で4個の『名前定義』が作成されています)

テーブル機能を設定

テーブル機能を設定

ドロップダウンリストにテーブルを挿入することにより、新たにデータが追加されても自動的に反映されます。

※テーブル機能を設定していない場合は、データを追加すると、名前定義を編集する必要があります。

テーブルを設定していない場合は、下図のように『東京支店』に新入社員【上村】のデータを追記してもドロップダウンリストには反映されません。

テーブルを挿入する方法

今回は、シート1の各支店の社員の一覧表とシート2の社員別の成績表にテーブルを設定していきます。

①セル【E1:G4】の各支店の社員の一覧表を範囲選択します。

②Excelのリボンから『挿入』タブを選択しテーブルのグループから【テーブル】をクリックします。

※ショートカットキー【Ctrl】+【T】を使用すると【テーブル】機能を呼び出ることができます。

③『テーブルの作成』が表示されましたら【先頭行をテーブルの見出しとして使用する】に☑チェックが入っていることを確認して【OK】をクリックします。

■セル【E1:G4】の各支店の社員の一覧表にテーブルが適用されました。

④先の手順と同様に『シート2』の社員別の成績表にもテーブルを適用します。

■セル【A1:B10】の社員別の成績表にテーブルが適用されました。

INDIRECT関数を使用したドロップダウンリストの作り方

ドロップダウンリスト2を作成

ドロップダウンリストにINDIRECT関数を使用することにより、名前定義した名前から参照先のデータを表示してくれます。

今回の例では『各支店』の支店名を選択すると、指定した支店の『社員名』が自動的に表示されます。

INDIRECT関数の設定方法

INDIRECT関数とは、参照したいセルを文字列で指定し、セルの値を呼び出すことができる関数です。

①社員名のセル【B2】を選択します。

②Excelのリボンから【データ】タブを選択します。

③『データツール』から【データの入力規則】を選択します。

④『データの入力規則』のダイアログボックスが表示されましたら『入力値の種類』に【リスト】を選択し『元の値』に【=INDIRECT($A$2)】と入力し【OK】をクリックします。

元の値にINDIRECT関数を使用しセルの選択範囲は支店名のセル【A2】を絶対参照に指定します。

※元の値に【=INDIRECT(】と入力後に、セル【A2】を選択し【)】を入力して【OK】をクリックすると自動的に絶対参照に指定されます。

⑤『元の値はエラーと判断されます。続けますか?』と表示されましたら【OK】をクリックします。

■社員名のセル【B2】にドロップダウンリスト2が作成されました。

別シートのデータを参照する方法

別シートのデータを自動的に参照

ドロップダウンリストで選んだ値に応じて、自動的にデータを別シートから参照する方法を紹介します。

今回の場合は、ドロップダウンリストから支店別の社員名を選ぶと社員別の【売上】が自動的に表示される方法を解説します。

VLOOKUP関数を使用して別シートのデータを参照する方法

VLOOKUP関数とは、検索対象の【指定した列】から、データを取り出す関数です。

VLOOKUP関数の設定方法【名前定義を使用】

別シート(シート2)に作成した表の名前定義を、VLOOKUP関数の範囲に設定しデータを呼び出す方法を解説します。

指定したデータを表示するセルを選択します。

①売上の欄のセル【C2】を選択します。

VLOOKUP関数の書式

VLOOKUP(検索値,範囲,列番号,[検索方法])

今回の入力方法【=VLOOKUP($B$2,成績表,2,FALSE-完全一致】

※VLOOKUP関数の入力後の最後の閉じカッコは入力せずに【Enter】を押します。

②セル【C2】に【=VL】と入力し【Tab】キーを押します。

検索値を指定し絶対参照に指定し【,】を入力します。

今回の検索値はセル【B2】を選択し絶対参照に指定し【,】を入力します。

④別シート(シート2)の表に名前定義した名前を入力し【,】を入力します。

今回は【成績表】を入力し【,】を入力します。

列番号【2】を入力し【,】を入力ます。

検索方法【FALSE-完全一致】を選択して【Enter】キーを押します。

※検索方法を選択する場合は【ダブルクリック】で選択します。

■連動式ドロップダウンリストが完成しました。

VLOOKUP関数の設定方法【通常の範囲選択】

ドロップダウンリストに名前定義を使用しない場合のVLOOKUP関数の入力方法を解説します。

① 売上の欄のセル【C2】を選択します。

②セル【C2】に【=VL】と入力し【Tab】キーを押します。

検索値を指定し絶対参照に指定し【,】を入力します。

今回の検索値はセル【B2】を選択して絶対参照に指定し【,】を入力します。

④別シート(シート2)の【成績表】を選択します。

⑤参照先の表を選択し【,】を入力します。

今回は社員別成績表のセル【A2:B10】を選択し【,】入力します。

⑥列番号【2】を入力し【,】を入力します。

検索方法【FALSE-完全一致】を選択し【Enter】キーを押します。

※検索方法を選択する場合は【ダブルクリック】で選択します。

■連動式ドロップダウンリストが完成しました。

MITSUHIKO
最後まで読んで頂きありがとうございます!

おすすめの記事