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

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

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

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

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

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

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

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

(シート1）

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

(シート2)

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

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

Excelには、作成したリストのデータを選択したセルに入力できる『プルダウン機能』があります。

プルダウン機能を使用することで、データをリストから選ぶことができるので、入力作業が簡単で、入力ミスを軽減することができます。

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

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

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

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

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

連動式ドロップダウンリスト作成手順

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

サンプル表を作成

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

『自動連動式ドロップダウンリスト』の作り方はこの先の『ドロップダウンリストの作り方：応用編』の手順に従って作業をしてください。

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

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

🥉手順①：ドロップダウンリストを作成するセルを選択します。

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

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

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

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

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

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

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

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

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

名前定義の作成

★名前定義の設定

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

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

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

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

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

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

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

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

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

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

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

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

🥉手順⑦：『新しい名前』のダイアログボックスの『名前』の欄に、任意の名前(成績表)と入力し『範囲』を『ブック』に指定し、参照範囲にカーソルを置き別シート(成績表)のセル【A1:B10】を範囲選択し【OK】をクリックします。

🥉手順⑧：『名前の管理』が表示されましたら、左下の【閉じる】ボタンをクリックします。

『参照範囲』には、シート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関数を使用したドロップダウンリストの作り方

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

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

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

INDIRECT関数の設定方法

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

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

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

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

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

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

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

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

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

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

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

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

今回は、VLOOKUP関数を使用して別シートのデータを自動参照する方法を詳しく解説します。

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

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

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

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

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

✨VLOOKUP関数の書式

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

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

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

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

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

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

🥉手順③：検索値【B2】を指定し絶対参照に指定し【,】を入力します。

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

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

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

🥉手順⑤：列番号【2】を入力ます

🥉手順⑥：最後にカンマ【,】入力します。

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

※検索方法を指定する場合は、キーボードの矢印キー【▼】を押して【FALSE-完全一致】が選択された状態で【ダブルクリック】します。

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