Excel 2013 関数技 37回

VLOOKUP関数で商品の種類ごとに異なるシートを検索する

見積書などを作る場合、商品の種類が多いと価格を調べるのも大変です。
今回は、商品コードや商品番号を判断して、異なるシートの商品を検索する場合のVLOOKUP関数の場合を紹介します。
(サンプルファイルは、こちらから 2013関数技37回サンプルデータ

商品の種類ごとに異なるシートを検索するには

衣料品などは、商品の種類が多く、コードから参照するにしても、複数のコード表がある場合は、どのように、効率よく入力していけばいいのでしょうか?

コードにあるルールを活用する

コードには、ルールがあるので、そのルールの文字をコードから取り出すようにすれば、複数の商品コード表を切り替えて参照できるのではないかという考え方です。
例では、Tがついているのは、Tシャツのコード表を参照し、Cが付いているコードからは、コートのコード表を参照するというわけです。

複数のコード表には、それぞれ名前を定義しておく

名前を定義しておくことで、数式が非常に簡潔でわかりやすくなります。
例えば、Tシャツのコード表を数式で範囲を指定する場合、
Tシャツ!$A$2:$C$15 となりますが、
名前定義で、Tシャツという名前をつけておけば、そのまま範囲が、
Tシャツでいいのです。

コードから、キーを取り出す。
A11セルから、T1308N-1001というコードの検索キーであるTを取り出します。式は、LEFT関数を使って、左から1文字を取り出せば良いです。
=LEFT(A11,1)
キーが先頭の2文字なら、=LEFT(A11,2)
もし、キーが左から2文字目にあるなら、
=MID(A11,2,1) というようになります。

取り出したキー文字で何をするかというと、コード表を参照したいので、VLOOKUP 関数を使います。
Tなら、Tシャツのコード表を返す式を考えます。
その前に、分類表を作り名前を定義しておきます。分類という名前です。

スポンサーリンク
スポンサーリンク

VLOOKUP関数の復習です。
どこに、LEFT(A11,1)を入れるかといえば、
検索値です。
そして、範囲は、分類表で、分類という名前を定義してあります。
列番号は、2列目
式は、このようになります。
=VLOOKUP(MID(A12,1,1),分類,2,0)
これで、どういう結果がでるでしょうか?

Tは、Tシャツ、Cはコートと返されました。これではまだ文字列なので、INDIRECT関数でセル範囲の指定に変えます。

今までのコードは、INDIRECT関数の引数である、参照文字列になります。
結果として、名前定義で指定しているセル範囲を指定することになるのです。
INDIRECT(VLOOKUP(MID(A11,1,1),分類,2,0))
それで、このINDIRECT関数で返されたセル範囲は、再びVLOOKUP関数の引数になります。
VLOOKUP(検索値, 範囲, 列番号, [検索の型])
検索の型は省略しています。

もう、これで、90%完成です。あとは、単価のセルに式をコピーするだけです。注意することは、参照セルの列だけ絶対参照にしておくことです。


すると、横にも下にもコピーすることができます。

今回は、VLOOKUP関数の上級技に挑戦してもらいました。
一見複雑に見えることも、名前定義を使って、INDIRECT関数で範囲指定をすることで、式をシンプルにすることができました。

ポイントは、キーを文字列関数で取り出して、名前定義されたコード表を指定するということです。
これで、コード表が何種類あっても、基本的には、VLOOKUP関数の式に収まります。
お疲れ様でした。
スポンサーリンク
スポンサーリンク