エクセルでの計算方法についてご回答お願いします。

質問

送料を出すための計算式についてご質問です。
Sheet1のA1に商品の重量があると仮定してB１に計算結果を表示させるにはどうすればいいでしょうか？？
重さの条件が0.05　0.1 0.15 0.2 0.25 0.3 0.35 0.4 .............と多いので調べてもできません((+_+))
どなたかよろしくお願いします！

Sheet2のA1とB1に重さと送料があるものとして。
↓↓↓↓↓↓↓↓↓

A列　　　　 B列
重さ(kg) 送料
0.05 ¥100
0.1 ¥200
0.15 ¥300
0.2 ¥400
0.25 ¥500
0.3 ¥600
0.35 ¥700
0.4 ¥800
0.45 ¥900
0.5 ¥1,000
0.55 ¥1,100
0.6 ¥1,200
0.65 ¥1,300
0.7 ¥1,400
0.75 ¥1,500
0.8 ¥1,600
0.85 ¥1,700
0.9 ¥1,800
0.95 ¥1,900
1 ¥2,000
1.1 ¥2,100
1.2 ¥2,200
1.25 ¥2,300
1.3 ¥2,400
1.4 ¥2,500
1.5 ¥2,600
1.6 ¥2,700
1.7 ¥2,800
1.75 ¥2,900
1.8 ¥3,000
1.9 ¥3,100
2 ¥3,200
2.5 ¥3,300
3 ¥3,400
3.5 ¥3,500
4 ¥3,600
4.5 ¥3,700
5 ¥3,800
5.5 ¥3,900
6 ¥4,000
6.5 ¥4,100
7 ¥4,200
7.5 ¥4,300
8 ¥4,400
8.5 ¥4,500
9 ¥4,600
9.5 ¥4,700
10 ¥4,800

回答No.9

kagakusuki · ベストアンサー

回答No.1様が指摘された

>そうすると１０ｋｇ超の値段がどうなってるのかが不明になってきます。

という問題に関して、質問者様からは未だに何の御説明もされていない様ですが、１０ｋｇ超の場合にはどの様な表示とすれば良いのでしょうか？
　取り敢えずの話として、A1セルに10kgを超える重量が入力された場合には、「重量オーバー」と表示する様にするものとして、回答させて頂きます。

　まずSheet2の表を次の様に重量が重い順番になる様に変更して下さい。

　　　　　　　 A列　　　B列　　　　C列
　1行目　　 重量　　　A　　　　　　B
　2行目　　　10　　　16200　　　12550
　3行目　　　9.5　　　16200　　　12550
　4行目　　　　9　　　14900　　　11500
　5行目　　　8.5　　　14900　　　11500
　6行目　　　　8　　　13600　　　10450
　7行目　　　7.5　　　13600　　　10450
　8行目　　　　7　　　12300　　　　9400
　9行目　　　6.5　　　12300　　　　9400
10行目　　　　6　　　11000　　　　8350
11行目　　　5.5　　　10200　　　　8350
12行目　　　　5　　　　9400　　　　7300
13行目　　　4.5　　　　8600　　　　7300
14行目　　　　4　　　　7800　　　　6150
15行目　　　3.5　　　　7000　　　　6150
16行目　　　　3　　　　6200　　　　5000
17行目　　　2.5　　　　5400　　　　5000
18行目　　　　2　　　　3065　　　　2080
19行目　　　1.9　　　　3065　　　　1980
20行目　　　1.8　　　　3065　　　　1880
21行目　　 1.75　　　　2795　　　　1880
22行目　　　1.7　　　　2795　　　　1780
23行目　　　1.6　　　　2795　　　　1680
24行目　　　1.5　　　　2525　　　　1580
25行目　　　1.4　　　　2525　　　　1480
26行目　　　1.3　　　　2525　　　　1380
27行目　　 1.25　　　　2255　　　　1380
28行目　　　1.2　　　　2255　　　　1280
29行目　　　1.1　　　　2255　　　　1180
30行目　　　　1　　　　1985　　　　1080
31行目　　 0.95　　　　1985　　　　1080
32行目　　　0.9　　　　1835　　　　　980
33行目　　 0.85　　　　1835　　　　　980
34行目　　　0.8　　　　1685　　　　　880
35行目　　 0.75　　　　1685　　　　　880
36行目　　　0.7　　　　1535　　　　　780
37行目　　 0.65　　　　1535　　　　　780
38行目　　　0.6　　　　1385　　　　　680
39行目　　 0.55　　　　1385　　　　　680
40行目　　　0.5　　　　1235　　　　　580
41行目　　 0.45　　　　1235　　　　　580
42行目　　　0.4　　　　1085　　　　　480
43行目　　 0.35　　　　1085　　　　　480
44行目　　　0.3　　　　　935　　　　　380
45行目　　 0.25　　　　　860　　　　　380
46行目　　　0.2　　　　　785　　　　　280
47行目　　 0.15　　　　　710　　　　　280
48行目　　　0.1　　　　　635　　　　　180
49行目　　 0.05　　　　　560　　　　　180

　次に、Sheet1のB1セルに次の関数を入力して下さい。

=IF($A1>0,IF($A1>MAX(Sheet2!$A:$A),"重量オーバー",INDEX(Sheet2!$A:$C,MATCH($A1,Sheet2!$A:$A,-1),COLUMNS($A:B))),"")

　次に、Sheet1のB1セルをコピーして、Sheet1のC1セルに貼り付けて下さい。
　次に、Sheet1のD1セルに次の関数を入力して下さい。

=IF(COUNT($B1,$C1),SUM($B1,$C1),"")

　以上です。

お礼

できました(*^_^*)
ありがとうございます。

回答No.8

tom04

No.3・4・5です。

D列が「0」になってしまう！というコトですが・・・
こちらでアップした配置で検証して投稿していますので
間違いないと思いますが、

もしかして「相対参照」になっていないのでは？
（$マークを付けていない）

それではコツコツC2・D2セルに数式をいれてみてください。

C2セルに
=IF(COUNTBLANK(A2:B2),"",VLOOKUP(B2,Sheet2!A:C,3,1))

D2セルに
=IF(COUNTBLANK(A2:B2),"",VLOOKUP(B2,Sheet2!A:D,4,1))

という数式です。m(_ _)m

回答No.7

keithin

＞実際の送料は0.05kgなら560円、1kgなら1985円、2kgなら3065円みたいに一定ではありません。

それは当然承知しています

＞Sheet1のH1に重量、H2から重量(kg)の数字でSheet3にかえしたい重さと料金があります。

Ｈ１に重量と書いてあって、Ｈ２から下に重量の数字が列記されている
Ｉ１に金額と書いてあって、Ｉ２から下に金額の数字が列記されている
という事ですね。でしたら回答１の数式と何も変わりませんが、単純に
=INDEX(Sheet1!I:I,COUNTIF(Sheet1!H:H,"<"&A1)+2)
です。

回答No.6

bunjii

>Sheet1のA1に商品の重量があると仮定してB１に計算結果を表示させるにはどうすればいいでしょうか？？
>Sheet2のA1とB1に重さと送料があるものとして。
>補足ですが、C列にパターンA、D列にパターンB、E列に金額をだせるようにしたいのですが、どういった計算式になりますでしょうか？？
質問内容と補足の要望に一貫性がありませんね。

勝手解釈ですが添付画像のようなテーブルを作成してみました。
数式は各列共通にできませんがA列の2行目以降に実貫重量を入力してパターンA、パターンBの料金を算出してD列に安い方の料金を表示しています。
併せて、料金表の重量もE列、F列に以上、未満の表示をしてみました。

数式については必要に応じて開示できます。

回答No.5

tom04

No.3・4です。

＞Sheet1のC列にパターンＡ（２行目以降は金額）を
＞Sheet1のD列にパターンＢ（２行目以降は金額）を表示させたいというこです

結局、C・D列に各パターンの金額だけを表示すれば良い！という解釈で・・・
Sheet2の配置はNo.3と一緒だとすると
Sheet1のC2セルに
=IF(COUNTBLANK($A2:$B2),"",VLOOKUP($B2,Sheet2!$A:$D,COLUMN(C1),1))
という数式を入れ、隣りのD列までコピー　→　そのまま（C・D列が範囲指定されている状態で）
下へコピーしてみてください。m(_ _)m

補足

ご丁寧にありがとうございます。
今いろいろ試していましたがＣ列にはパターンＢの料金が表示され、Ｄ列にはすべて0になってしまいます((+_+))
商品名が英数字なのですがそこは問題ないですよね？？

回答No.4

tom04

No.3です。

＞補足ですが、C列にパターンA、D列にパターンB、E列に金額をだせるようにしたいのですが・・・

の意味がいまいち理解できないのですが、
前回アップした画像の配置での数式で。

前回の数式はSheet1のB列によって、Sheet2に作成した表の行を判断。
Sheet1のC列によって、Sheet2のC列またはD列を判断。
その交差するセルをSheet1のD列に表示するようにしていますので、
「重量」と「料金パターン」の2条件がないと、「金額」が表示されません。

※　Sheet1のC列に「パターンA」、D列に「パターンB」と入力した場合、E列に「金額」を表示させたい！
という意味でしょうか？

ん～～～～～～その辺が理解できません。、
憶測で回答しても新たな混乱を招くかもしれませんね。m(_ _)m

補足

Sheet1のC列にパターンＡ（２行目以降は金額）を
Sheet1のD列にパターンＢ（２行目以降は金額）を表示させたいというこです。

できますでしょうか？？

回答No.3

tom04

こんにちは！
他の方の補足を拝見すると、料金体系が2種類あるようですので、
一例です。
やり方だけ！VLOOKUP関数の「TRUE」型で対応できると思います。

↓の画像で右側がSheet2でSheet2に表を作成しておきます。
B列（未満の列）は必要ないのですが判りやすいように敢えて表示しています。
A列は重量区分・C列がパターン「A」の場合の料金、D列がパターン「B」の場合の料金とします。

画像ではSheet1のD2セルに
=IF(COUNTBLANK(B2:C2),"",VLOOKUP(B2,Sheet2!$A:$D,MATCH(C2,Sheet2!$1:$1,0),1))
という数式を入れオートフィルで下へコピーしています。

※　質問では重量だけの料金体系になっているようですが、
距離は無視しています。m(_ _)m

補足

ありがとうございます。
補足ですが、C列にパターンA、D列にパターンB、E列に金額をだせるようにしたいのですが、どういった計算式になりますでしょうか？？

回答No.2

dduster

送料の基準を教えてください。
それがなくては回答できませんよ

重量と送料は正比例していないようです。
重量のどこかで（●Kgからは）割引などがありますよね？
それを明記しないとダメです

補足

そうなんですか...
重量によってとなりの料金がでてくるようにできると思ってましたので((+_+))
2パターンありまして、
こちらと
0.05 ¥560
0.1 ¥635
0.15 ¥710
0.2 ¥785
0.25 ¥860
0.3 ¥935
0.35 ¥1,085
0.4 ¥1,085
0.45 ¥1,235
0.5 ¥1,235
0.55 ¥1,385
0.6 ¥1,385
0.65 ¥1,535
0.7 ¥1,535
0.75 ¥1,685
0.8 ¥1,685
0.85 ¥1,835
0.9 ¥1,835
0.95 ¥1,985
1 ¥1,985
1.1 ¥2,255
1.2 ¥2,255
1.25 ¥2,255
1.3 ¥2,525
1.4 ¥2,525
1.5 ¥2,525
1.6 ¥2,795
1.7 ¥2,795
1.75 ¥2,795
1.8 ¥3,065
1.9 ¥3,065
2 ¥3,065
2.5 ¥5,400
3 ¥6,200
3.5 ¥7,000
4 ¥7,800
4.5 ¥8,600
5 ¥9,400
5.5 ¥10,200
6 ¥11,000
6.5 ¥12,300
7 ¥12,300
7.5 ¥13,600
8 ¥13,600
8.5 ¥14,900
9 ¥14,900
9.5 ¥16,200
10 ¥16,200

こちらになります。
0.05 ¥180
0.1 ¥180
0.15 ¥280
0.2 ¥280
0.25 ¥380
0.3 ¥380
0.35 ¥480
0.4 ¥480
0.45 ¥580
0.5 ¥580
0.55 ¥680
0.6 ¥680
0.65 ¥780
0.7 ¥780
0.75 ¥880
0.8 ¥880
0.85 ¥980
0.9 ¥980
0.95 ¥1,080
1 ¥1,080
1.1 ¥1,180
1.2 ¥1,280
1.25 ¥1,380
1.3 ¥1,380
1.4 ¥1,480
1.5 ¥1,580
1.6 ¥1,680
1.7 ¥1,780
1.75 ¥1,880
1.8 ¥1,880
1.9 ¥1,980
2 ¥2,080
2.5 ¥5,000
3 ¥5,000
3.5 ¥6,150
4 ¥6,150
4.5 ¥7,300
5 ¥7,300
5.5 ¥8,350
6 ¥8,350
6.5 ¥9,400
7 ¥9,400
7.5 ¥10,450
8 ¥10,450
8.5 ¥11,500
9 ¥11,500
9.5 ¥12,550
10 ¥12,550

お手数ですがよろしくお願いします。

回答No.1

keithin

相変わらず肝心の事がご相談に書かれていませんね。
０．０５ｋｇ「まで」（０．０５ｋｇを含む）が１００円なのか
０．０５ｋｇ「から」（０．０５ｋｇを含む）が１００円なのか
とか、表の読み方は一体全体どうしたいのですか。

ふつーに考えると「まで（含む）」の方かなと思いますが、そうすると１０ｋｇ超の値段がどうなってるのかが不明になってきます。

とりあえずシート２の
１行目は「重さ(kg)」「金額」としっかりタイトル行にして
２行目から数字を記入してあることにして、
=IF(A1=0,"",INDEX(Sheet2!B:B,COUNTIF(Sheet2!A:A,"<"&A1)+2))
とでもしてみましょう。

補足

素早い回答ありがとうございます！！
はい(*_*)
までを含んだつもりでご質問致しました。
またもや困惑させてしまい失礼しました。

教えてもらった数式でやってもできませんでした(+_+)
実際の送料は0.05kgなら560円、1kgなら1985円、2kgなら3065円みたいに一定ではありません。
それとSheet1のH1に重量、H2から重量(kg)の数字でSheet3にかえしたい重さと料金があります。

自分で訂正してやってみたけどできませんでした(・。・;
もう一度教えていただければ助かります。