EXCELのリスト入力色々(可変リスト他）

講習会でリスト入力を色々と体験してもらいました。

けっこう楽しい講習会になりました。^^

 

EXCELではセルにリストから選択して入力できる仕組み（データの入力規則）のリスト入力があります。

※データリボンの中に「データの入力規則」があり、この中のデータの入力規則を選択して

　開いたウインドウの中の条件の設定、入力値の種類から「リスト」を選択してデータ範囲を指定するだけで

　リスト入力ができます。

通常は以下のように設定してリスト入力を活用します。

 これだけではちょっと面白くない。

それぞれの商品リスト群をさらにリスト入力できないだろうか

以下のような表があったとする。

商品リストはA・B・Cそれぞれを選択可能として

それぞれの品番・品名・単価が簡単に入力出来たらおもしろい。

とりあえず、各商品リストの品番がリスト入力出来れば、後はVLOOKUP関数で簡単に抽出できる。

 

その仕組みを作ってみる。

下図のように範囲選択、名前ボックスをクリック、「商品A」と入力確定させ、さらにEnterキー押下で名前の確定を行う。

これを商品リストBとCでも行って下準備をしておく。

 B3セルを選択して、データの入力規則で以下のように設定する。

INDIRECT関数を使うことによりA3セルに入力した値と結合させて

先ほど名前定義した範囲が利用出来るようになります。

 

これで、B3セルには、A3セルで選ばれた商品リストの品番リストに可変表示させることができます。

商品リストAが選ばれればAの品番リストが、Bが選ばれればBの品番リストが表示されます。

データの入力規則はオートフィルでコピーできますので

以下、必要数フィルコピーをします。

 

VLOOKUP関数で、選択された品番から自動的に品名や単価を抽出するには

もう少し準備が必要です。

 

下図の様に選択、名前ボックスをクリック、商品リストAと入力確定、もう一度Enterキー押下で名前の確定を行います。

商品リストBと商品リストCも同様に名前の定義を行う。

 

C3セルを選択して数式バーに以下のように関数式を作る（エラー処理は必須です。）

=IF($B3="","",VLOOKUP($B3,INDIRECT("商品リスト"&$A3),COLUMN(B1),0))

式確定後、D3セルにフィルコピー、以下必要数フィルコピーします。

 

これで、商品リストをリストから選択して、選ばれた商品リストの品番をリスト入力すると

自動的にそれぞれの品名・単価が自動的に入力されます。

 

以下はオマケです。^^

リスト入力の値は、直接入力しても使うことができます。

ポイントは、直接入力で入力して、カンマ（半角）で値を区切ることです。

 

消費税の値を8％と10％のリスト入力ができるように設定してみました。

表示された0.08に、%スタイルを設定すると8％と表示されます。

実際は0.08なので計算式にもそのままこのセルを使うことができます。

8と10でも計算式をそれなりにすれば計算できますが．．．。