エクセルで他シートのデータを分類のダウンリストで検索しその分類内の商品をダウンリスト表示する

エクセルのシートを検索表示シート、商品分類商品名シートとし次のような形とする。

分類検索

図1

 

上にあるように、複数の分類の中から1つ選んだら、その分類内の商品名が表示でき、価格や在庫の様子を示したい。そこで

1_名前の定義

図2

 

商品名のシートが上のようにつくられている。簡単にするために分類ごとに商品名も並んでいる。このシートの中を後の関数式をわかりやすくするために名前を定義する。

2_名前の定義a

図3

 

枠で囲んだところをそれぞれ選んでは、右クリックしてメニューから「範囲に名前をつける」をクリックし、aは「分類」、bは「くだもの」,cは「野菜」,dは「魚」と定義する。

図1の検索シートに戻り、分類の欄(A2)に入力規則を設定する。

分類入力規則

図4

 

図4の「元の値」の「分類」はシート「商品」で定義した「分類」を示している。

続いてその分類に応じた商品を選択する欄には

品名の入力規則

図5

図5のA2の分類欄で選んだものに該当する商品だけを表示するために、入力規則をリストを選択し、元の値を「=INDIRECT($A$2) 」と設定する。これで分類欄で「くだもの」をダウンリストから選択すると「くだもの」に分類された商品だけがダウンリストで表示できる。

ここでは1つだけのものを表示することを考えているので、商品シートに作業のための行を設定した。

作業欄の設定

図6

商品シートの色を塗ったところが作業のための欄です。「分類」と「商品名」は検索シートから値をとるので、「=検索!A2」「=検索!B2」とします。さらに価格と在庫の欄についてはこの商品シートないですからVLOOKUPの関数が使用できます。「価格」は「=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)」、在庫は「=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)」と関数式を入力することで表示できます。

最後に検索シートに戻り

品名検索

図7

価格と在庫の欄は商品シートの作業欄のデータをとるということで、「=商品!C18」,「=商品!D18」とすれば表示させることができます。

図5と図7でデータが異なりますが、ダウンリストに応じて選択できるというのは、いろいろに使えそうです。今回やってみて、他シートにデータが数多くあり、別シートにそのデータを表示できるのです。