エクセルのドロップダウンリストでは別シートを反映したい場合もあるかもしれません。ドロップダウンリストを別シートのデータへ自動反映することができると、直接入力によるミスや元のデータが変わった時の修正の手間を減らせますよね。エクセルではプルダウンで選んだ値に連動して自動入力する内容や次の選択肢の内容を（別シートも含めて）変えることも可能です。今回は、Excelのドロップダウンリストをテーブル化も含めて連動する方法などをご紹介します。
エクセルのプルダウンをつくるときには別シートから反映して元データとして参照したい場合もあるかもしれません。プルダウンは直接入力のみならずエクセルにあるデータを参照できれば便利ですが、別シートの値や一覧も参照できるなら一気に使い道が広がりますよね。ここでは、Excelのプルダウンをつくるにあたり別シート参照をどのようにするかを見ていきましょう。例として、［プルダウン］という名前のシートにドロップダウンリストを作る際に［データ］という別の名前のシートにある値を使うことを考えていきます。
エクセルのドロップダウンリストに別シートのものを反映する手順は、途中までは基本のプルダウンの作り方と同じです。①作成したいセルまたは範囲を選択、②『データ』⇒「データツール」の『データの入力規則』、③「データの入力規則」の「設定」で「入力値の種類(A)」は『リスト』を選択、を順に進めましょう。
異なるのは、「元の値(S)」と書かれた部分の空欄に入力する内容です。まず、空欄の右側の□を押すと(1⃣)、別の細長いダイアログボックスが出てきます。ダイアログボックスの下側にある空欄をクリックして(2⃣)、反映させたいデータが書かれているシート名をエクセルシートの左下部分から選択しましょう(3⃣)。
そして、エクセルのプルダウンに選択肢として表示させたい、別シートのセル範囲をドラッグします(4⃣)。条件式は『=シート名!セル番地:セル番地』です。『Enter』を押すと元の画面に戻ります(5⃣)ので、『OK』を押して反映されているかを確かめましょう(6⃣)。
直接入力することもでき、「元の値(S)」と書かれた部分の空欄をダブルクリックして入力していきます。条件式は同様に『=シート名!セル番地:セル番地』です。例であれば『=データ!$F$3:$F$11』(絶対参照の$が付いているのに注意)と入力すると、画像のようにデータの指定範囲に書いてある地方名が表示できています。なお、シート名にスペースがある場合は正しく参照されるために『’シート名’!セル番地:セル番地』のようにシート名をシングルクォーテーションで囲む必要があるので、忘れないようにしましょう。
Excelのプルダウンは別シート参照もできることがわかりました。エクセルのプルダウンを別シートと連動させるには、名前の定義とINDIRECT関数の組み合わせが簡潔です。INDIRECT関数によって、あるプルダウンで選んだ値によってもう一つのプルダウンで出てくる値の候補が変わる「連動プルダウン」にすることができます。ここでは、地方名が書かれたドロップダウンリストで『近畿』(親要素と呼びます)を選ぶとき、都道府県名が書かれた別のプルダウンで『滋賀県・京都府・大阪府・兵庫県・奈良県・和歌山県』(子要素と呼びます)が表示されるように設定することを考えてみましょう。
まずは、エクセルのシート上から、子要素に該当する項目を範囲選択し、上側にある『数式』⇒「定義された名前」内の『名前の定義』を順番に選びます。例では、『滋賀県・京都府・大阪府・兵庫県・奈良県・和歌山県』になるようにしたいので、範囲選択するのはG8:L8です。なお、例では横方向に並んだデータ（G8:L8）を使用していますが、縦方向の一覧でも同様に設定できます。
次に、「新しい名前」と書かれたダイアログボックスの「名前(N)」の部分に親要素として指定したい選択肢(例では『近畿』)を1つ入力しましょう。初めてすると「あれ、逆ではない？」と思うかもしれませんが、ここでは『近畿』という親要素の名前を、子要素である都道府県一覧の範囲に付ける形になります。Excelの「名前の定義」はその範囲に別名を付ける設定であり、子要素の一覧に親要素名を割り当てることで、INDIRECTで参照しやすくするためです。「参照範囲(R)」には先ほど範囲選択したセル番地およびシート名も自動的に表示されていますので、先ほどのように別シートの名前を入力する必要はありません。『OK』を押してダイアログボックスを閉じましょう。
プルダウンを作成する流れは先ほどと同様です(反映させる方法を参照)。親要素のプルダウンを先に作ってから、子要素と連動させていきます。子要素のプルダウンを作成したいセルを選択(例ではシート名[プルダウン]のD9)して『データの入力規則』で出てくる編集画面の「入力値の種類(A)」を『リスト』に変更しましょう。「元の値」は『=INDIRECT(最初のプルダウンを作成したセル番地)』とします。今回の例では『=INDIRECT(C9)』です。
『OK』を押して画面を閉じようとすると、「元の値はエラーと判断されます、続けますか？」という警告文が出てきて驚くかもしれません。この表示が出る原因は、2つ考えられます。1つ目は、プルダウンが参照するセルに候補にない値が入力されているか、そもそも空欄であるかです。データの入力規則は指定した値だけを許可する設定であり、プルダウンが参照するセルまたは範囲に空欄があったり名前の定義で該当部分が見つからなかったりすると、Excelは「現在入力されている値がリストに存在しない」と判断して警告を出します。特にINDIRECTの場合、参照元のセルが空欄だと名前の定義が見つからずエラーになるので注意しましょう。
また、2つ目として、参照を変えたことでプルダウンを設定しているセルの値が候補に含まれていないものになった場合もエラーが発生するのです。つまり、元の値とはプルダウンの設定元となる参照範囲を指しますが、実は既にプルダウンが設定されているセルに入力されている値もエラーの原因となることがあります。ただし、いずれもプルダウンの連動そのものの設定はできていますので、『はい』を選びましょう。
「元の値はエラーと判断されます、続けますか？」の対処法としては、参照している範囲に空白のセルが混じっているかをまずは確かめます。さらに、「名前の定義」で正しく範囲を参照しているかもチェックしましょう。シート名にスペースがある場合は引用符が必要です。それでも警告が出るのを避けられない場合は、データの入力規則の「エラーメッセージ」設定を調整して運用上の注意を記載しておくと混乱しないかもしれません。実務的には、元データをテーブル化しておけば範囲ずれや空白問題は起きにくいです。どのようにするかを、次の項目で見ていきましょう。
エクセルのプルダウンを実務で長期間使う場合、元データをテーブル化しておくことで、一覧を更新したときにドロップダウンリストへ自動反映できるメリットがあります。通常のセル範囲のままだと、行を追加した際にプルダウンの参照範囲がずれてしまうのですが、テーブルを使えば自動的に範囲が拡張されるのが特長です。Excelのドロップダウンリストをテーブルとして連動する方法は、元データが書かれた別シートを開き、範囲を選択したうえで『挿入』⇒『テーブル』を押します。テーブル名は内容に関連する名前（例：T_地域一覧）にすると管理がしやすいでしょう。プルダウンの「元の値」には、『=テーブル名[列名]』の形式で指定しておけば、行を追加しても自動反映が可能です。これによって、ドロップダウンリストを別シートのデータへ自動反映する仕組みが整い、一覧のメンテナンスが非常に簡単になります。
エクセルのプルダウンで選んだものに連動して自動入力したい（別シートも含めて）場合もあるかもしれません。たとえば、商品名をプルダウンから選ぶと自動で価格や在庫数が表示される、といった使い方です。エクセルのプルダウンを使ったシート連動は、VLOOKUP関数やXLOOKUP関数と組み合わせることで実現できます。プルダウンを設定したセル（例：シート[選択]のB3）を元に、『=XLOOKUP(B3,元データ!A:A,元データ!C:C)』のように入力すれば、選んだ商品に対応した値を自動入力が可能です。別シートの場合はこれまでと同様にシート名で参照しましょう。INDIRECT関数と組み合わせれば、「最初のプルダウンの選択肢によって、次に参照する表自体を切り替える」という高度な連動も可能です。
エクセルのプルダウンを活用すると、項目別の集計を別シートに自動的に作ることもできます。たとえば、売上管理表で「地域名」のプルダウンを作り、選んだ地域だけの売上件数や平均値を別シートに集計して表示する、といった使い方です。よく使われる方法は2つあります。1つ目は、SUMIF・COUNTIF などの条件付き集計関数とプルダウンの値を組み合わせる方法です。別シートの集計セルに『=SUMIF(元データ!A:A,選択シート!B2,元データ!C:C)』のように設定すると、選んだ項目に応じた結果が出ます。2つ目は、FILTER関数による「条件に一致する行だけの抽出」で、一覧をそのまま別シートに表示が可能です。例えば、『=FILTER(元データ!A:D,元データ!A:A=選択シート!B2)』とすれば、選択した項目に応じて別シートに一覧を自動生成できます。
今回は、エクセルのプルダウンにおけるシート連動として、プルダウンで選択肢を他のシートの値を使って入力する方法と、プルダウンで選んだメニューに応じて他のシートの値を変更する方法をご紹介しました。ドロップダウンリストを別シートのデータへ自動反映することができると、直接入力によるミスや元のデータが変わった時の修正の手間を減らせるのが特長です。また、エクセルのプルダウンを活用すると、項目別の集計を別シートに自動的に作ることもできます。目的に応じて関数を組み合わせながら、より便利にシートを連動させていきましょう。最後までお読みいただきありがとうございました。