他シートにある商品名一覧を選択リストに表示したい

商品名などは、一覧でセルに入力しておいて、入力規則で選択しるとに表示したい場合も多い。この際、他シートに入力されている一覧を利用する際は、「名前」を活用しよう。

 

 

商品検索.xls
Microsoft Excel 27.0 KB

①Sheet2にマスタリストを作成します。

 

②商品名の入力されている範囲を選択し、名前ボックスに「商品一覧」と入力し、Enterします。

 

このサンプルデータでは、A1~A4の範囲に「商品一覧」という名前がつけられました。

 

 

データの入力規則
データの入力規則

③ドロップダウンリストにしたいSheet1の範囲を選択します。

(例ではSheet1のA1~A5)

 

■2007では、【データ】タブー【データツール】グループー【データの入力規則】

 

 

【設定】タブ
【設定】タブ

④【設定】タブを選択。

⑤入力値の種類は、▼「リスト」を選択します。

⑥元の値の枠内にカーソルを入れ、F3キーを押下すると、[名前の貼り付け]ダイアログが表示され、②で定義した範囲が表示されます。「商品一覧」を選択しOKします。

⑦「ドロップダウン リストから選択する」に✓が入っていることを確認してOKします。

 

→これで、ドロップダウンリストを使って、他シートのリストを選択できるようになります。

 

 

 

 

B列・C列のデータを自動表示する

①Sheet1、セルB1をA1に入力されたデータに応じで、自動表示させるには、

 

B1に

=IF($A1="","",VLOOKUP($A1,Sheet2!$A$1:$C$4,2,0))

と入力します。

 

Vlookup関数は、検索の関数で、4つの引数を必要とします。

=VLOOKUP(①検索値,②リスト範囲,③求めたい列番号,④完全一致か近似値か)

マスターリストは、最左列を昇順に並べ替えておく必要があります。

 

①検索値は、セルA1を指定しますが、数式を横へコピーした際に、参照セルが右へずれないようにF4キーを3回押下し、「$A1」とします。

 

②リスト範囲は、Sheet2のA1~C4をドラッグで選択し、数式をコピーしても参照先がずれないように、F4キーを1回押下し、「Sheet2!$A$1:$C$4」とします。

 

③求めたい列番号は、マスタリストの2列目にあるデータを求めたいので「2」を手入力します。

 

④検索値に対応するデータは、1つだけしかないので、完全一致の「0」を指定します。

 

 

セルB1に入力した数式は、オートフィル機能を使って、C1にコピーします。

同じ式が入力できますので、2列目のデータが返されます。

C1の式を、

=IF($A1="","",VLOOKUP($A1,Sheet2!$A$1:$C$4,3,0))

に直します。           2を3に変更↑

 

B1・C1の式は、オートフィルを使って、下方向にコピーして使えます。