Excel別シートと連動するドロップダウンリストの作り方！

Excelをクラウド上などで共有して複数人で使用する場合は、入力する人によって半角・全角・略語などの「表記ゆれ」や「入力ミス」が発生することがあります。

このような時は連動するドロップダウンリストを使用すると解決することができます。

ドロップダウンリストは、特定の項目を入力すると関連する項目を絞り込むことができるため、設定しておくと「表記ゆれ」や「入力ミス」の防止につながります。また、連動するドロップダウンリストと、別シートの参照を組み合わせることで作業効率がアップします。

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

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

この機能は、複数の人と同じエクセルファイルを共有・編集する際に「表記揺れ」や「入力ミス」を防ぐことができます。

また、連動するドロップダウンリストを作成すると、参照元のデータを自動入力できるので、Excelの表管理の効率化につながります。

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

(シート1）

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

(シート2)

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

連動するプルダウンリストのメリット

Excelの別シートを参照して連動するプルダウンリストを作成すると、次のようなメリットがあります。

プルダウンリストの作り方

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

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

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

この記事で紹介する連動式ドロップダウンリストは、各支店の社員名を選択すると別シートの社員別の営業成績表から、データを自動的に反映する方法を採用しています。

サンプル表を作成して準備する

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

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

🥉手順①：シート１には下図のように表を2個、作成して項目を入力し、シートに名前を付けます。

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

今回は別シートに例として社員10人の名前と売り上げを表にまとめています。

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

ドロップダウンリストを、新規に作成する方法を解説します。

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

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

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

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

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

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

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

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

■この記事を見た方にお勧めの商品

別シートと連動するドロップダウンリストの作り方

別シートと連動するドロップダウンリストの作成手順は、次の5ステップで作成していきます。

(自動連動式ドロップダウンリストの一例）

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

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

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

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

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

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

🥉手順④：『データの入力規則』のダイアログボックスが開きましたら『設定』タブを選択し『入力値の種類』のボックスにある下向き矢印【v】をクリックします。

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

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

🥉手順⑦：最初にセル【E1】を選択し、Shiftキーを押しながら、セル【G1】をクリックして選択し【OK】をクリックします。

最初のドロップダウンリストの【元の値】に、表の支店名【東京・大阪・札幌】のセル【E1:G1】を入力します。

支店名のセル【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】と入力してから、セル【A2】を選択し【)】を入力し【OK】をクリックします。

元の値に【=INDIRECT($A$2)】と表示されていることを確認してから【OK】をクリックします。

元の値には『支店名』のセル【A2】を指定します。

✨参考

元の値に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】を指定してから【F4】キーを押して絶対参照に指定し【,】を入力します。

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

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

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

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

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

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

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

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

■この記事を見た方にお勧めの商品

■VLOOKUP関数の詳しい使い方は別の記事で詳しく紹介しています。