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

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

商品検索.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キーを３回押下し、「$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の式は、オートフィルを使って、下方向にコピーして使えます。