EXCEL 複数分岐 参照

質問

初心者ながらエクセルで、単価確認計算の一覧表を作成してます。

商品が１０種類ほどあり、重量もそれぞれが異なっている時の単価を出したいのです。
今までは、各商品ごとのシートを作成し、その商品ごとに入力してＶＬＯＯＫＵＰを使っていました。
複数のシートにそれぞれ入力してしまうと、チェックがかけにくくなるので
理想としては一枚のシートで確認したいのです。

シート１を入力用のものにして、シート２に商品とその商品ごとの各重量単価を入力しています。
シート１の商品と重量を入力したときに、シート２を参照してシート１の単価のセルに返したいのですが上手くできません。
宜しくお願い致します。

　シート1　　　商品　　重量　　単価　　　　　
　　　　　　1　　A　　　　10　　 　　　
　　　　　　2　　B　　　　18　　　 　　　　
　　　　　　3　　B　　　　29　　 　

　　　　　　　　　↑　　　↑　　　　　　　　↑

　シート2　　　　　　　　　　　　重量
　　　　　　　　商品　　2～8kｇ　　8～15kｇ　　15～20kｇ　　20～25kｇ　　　　　
　　　　　　1　　A　　　　1400　　 1200 　1100　　　　　980　　　　　　
　　　　　　2　　B　　　　1380　　　 1300　　　　 1200　　　　　1150
　　　　　　3　　Ｃ　　　　1000　　　 1000　　　　 950 　　　　　900　　
　　　　　　4　　Ｄ　　　　1240　　　 1210　　　　 1100　　　　 1040

回答No.3

keithin · ベストアンサー

今まで「商品ごとに違うシートの表を『VLOOKUP』していた」のでしたら，今までの表は「縦に重量」が並んでいたハズですね。
それをワザワザ変更する必要もない（しない方が便利）なので，次のように作成します。

シート２：
Ａ列　Ｂ列　　Ｃ列　　…
重量　商品Ａ　商品Ｂ　…
　２　1400　　1380
　８　1200　　1300
１５　1100　　1200
２０　 981　　1150

Ａ列はいままで通り重量区分の「以上の値」です。

するとあとは，VLOOKUP関数で商品ごとに「何列目」を取ってくればよいかだけになるので，基本形は
=VLOOKUP(重量,Sheet2!$A:$E,MATCH(商品名,Sheet2!$A$1:$E$1,0))
のようになります。

ただし。
一連のお話は，各商品共に「重量区分けが同じ」であることが大前提になっていますね。
商品ごとに重量区分が「違う」場合は，わざわざ同じ表に組み込むのは合理的ではない場合も考えられます。
そういう時は，商品ごとに「どのセル範囲をVLOOKUPすればいいのか」のように計算を仕分けてしまうのも一つの手になります。

=VLOOKUP(重量,IF(商品ＡならSheet2!A:B，IF(商品ＢならSheet2!C:D，それ以外の商品はSheet2!E:F)),2)
といった具合です。

お礼

本当にありがとうございます。

ご指摘の通り商品ごとに重量区分は違っています。
ここはまた分岐にするか、区分を細かく分けて手入力で
乗り切るか考えてました。
細かく気が付いてくださり、重量区分けが違う場合のことまで
アドバイスをいただけたのが嬉しかったです。

回答No.4

tom04

こんばんは！
極力お示しの配置に近い形で・・・

↓の画像のようにSheet2に表を作成しておきます。
（実際は薄い緑色の部分だけでOKなのですが、判りやすくするために敢えて「未満」の行を入れています。

Sheet1のC2セルに
=IF(COUNTBLANK(A2:B2),"",INDEX(Sheet2!$B$3:$F$6,MATCH(A2,Sheet2!$A$3:$A$6,0),MATCH(B2,Sheet2!$B$1:$F$1,1)))

という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

※　～2kgの部分は適宜数値を入力すれば、0kg以上にも対応できると思います。m(_ _)m

お礼

図入りで初心者にわかりやすい丁寧な説明でした。
本当に私が質問時に作っていた配置に近い形だったので
とても参考になり、助かりました。
ありがとうございます。
また宜しくお願いします。

回答No.2

mu2011

一例です。
シート２の重量項目は、左から２、８、１５、２０と入力して下さい。
内訳は、2≦重量<8、8≦重量<15、15≦重量<20、20≦重量となります。
シート１のC2に=OFFSET(Sheet2!$A$1,MATCH(A2,Sheet2!$A:$A,0)-1,MATCH(B2,Sheet2!$1:$1,1)-1)、下方向にコピー
尚、商品欄が空白、重量が2Kg未満の場合はIF関数で対応して下さい。

お礼

ご丁寧な説明をありがとうございました。
追いかけで勉強しながら使用しているので、
OFFSET関数を調べましたがかなりややこしいのですね…。
使いこなせるように勉強致します。
また宜しくお願いします。

回答No.1

KURUMITO

シート1ではA2セルから下方に商品名、B2セルから下方に重量が入力されるとします。
シート2ではA2セルから下方に商品名が、B1セルには2、C1セルには8、D1セルには15、E1セルには20と入力します。
B列およびE列の2行目以降にはそれぞれの重量単価が入力されているとします。
そこでシート1のC2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(B2="","",INDEX(Sheet2!A:E,MATCH(A2,Sheet2!A:A,0),MATCH(B2+0.1,Sheet2!$1:$1,1)))

お礼

ご丁寧な説明をありがとうございました。
あまりにも初心者すぎて怒られるかと思っていました。
考え方・記述などとても参考になりました。
ありがとうございます。
また宜しくお願いします。