個数によって値段の変わる品物の合計金額を算出したい

画像左上のようなA～D列にわたる表があるとします。
これは、例えば田中さんが４月１日にある品物を10個購入した、という意味です。
この品物は一度に購入する個数によって値段が変わり、F～I 列にあるように１～10個目は１個30円、11～20個目は１個20円、21～40個目は１個10円で購入できます。

このような条件のもとで、画像左下のように、各人の日付ごとの購入金額を一気に算出したいです。
できましたら、F～I 列の個数や金額の条件を変更した時に、その結果を反映してくれるような方法であるとありがたいです。

どなたか、このような方法をご教授ください。
使用しているのはExcel2010です。

また、以下に画像のようなExcelファイルをアップロードしましたので、
もしご回答を作られる場合はお使いください。
http://www1.axfc.net/uploader/Sc/so/289617.xls

No.4 ベストアンサー 回答者： keithin 回答日時： 2011/11/05 13:18

では少しメンドクサイ式ですが，こんな具合で。

Ｂ９：
=IF(B2=0,0,SUMPRODUCT(($H$2:$H$4<=B2)*($H$2:$H$4-$F$2:$F$4+1)*$I$2:$I$4)+(B2-IF(B2<$H$2,0,VLOOKUP(B2,$H$2:$H$4,1)))*VLOOKUP(B2,$F$2:$I$4,4))

この回答へのお礼

無事に目的を達成することができました。
回答をくださった皆さん、ありがとうございます。

お礼日時：2011/11/05 16:39

No.5 回答者： CC_T 回答日時： 2011/11/05 13:50

Ｊ列に値引き金額を入れて、個数に応じた「値引き」で計算してみました。

Ｂ２セルに
=B2*$I$2+$J$4*IF(B2>20,B2-20,0)+$J$3*IF(B2>10,B2-10-IF(B2>20,B2-20,0))
と入力して、あとはドラッグです。

蛇足ながら価格変動の個数しきい値が変わる時は、上の式の中の２０、１０の部分を変える必要があります。

No.3 回答者： QchanOK 回答日時： 2011/11/05 10:43

通常は、VLOOKUP とかを使用して行いますね。

でも、あえて一番ベタな方法を書いてみました。
if文 を使う方法ですね。


Ｂ９ に入れる式

(1)　=if(AND(b2>=f2,b2<=h2),b2*i2,
(2) if(AND(b2>=f3,b2<=h3),b2*i3,
(3) if(AND(b2>=f4,b2<=h4),b2*i4,0)))

分かりやすくするため行を分けてますが、１行で書いて下さい。

説明は、
(1)：b2 の内容が f2 以上で h2 以下の場合、b2 と i2 を掛ける。それ以外は(2)の処理
(2)：b2 の内容が f3 以上で h3 以下の場合、b2 と i3 を掛ける。それ以外は(3)の処理
(3)：b2 の内容が f4 以上で h4 以下の場合、b2 と i4 を掛ける。それ以外は 0


if文のＡＮＤ条件の書式を使います。

　=if( AND(条件1,条件2) , 条件1と条件2が満たされた場合の式 , 条件1,2でもない場合の式)

今回は、[条件1,2でもない場合の式] の部分が「入れ子=ネスト」となります。
　ちなみに、この「ネスト」は何個まで指定できるといった制限があります。
　７個（たぶん）くらいだったと思います。


※あとは、この B9 をコピーしてみるのですが
　コピーして変わってしまうと困るところは絶対値指定にしておくことは
　言うまでもありませんね（一応）。

ベタで、すみません。

この回答への補足

私の説明が足りませんでした。申し訳ありません。
例えば「20個購入した場合、20個全てを１つ20円で計算する」のではなく、
「10個目までは１つ30円、11～20個目までの10個は１つ20円で計算する」という作業をしたかったのです。
ですからこの場合、合計は400円ではなく500円となります。
これは無理な注文なのでしょうか・・・

補足日時：2011/11/05 11:54

No.2 回答者： keithin 回答日時： 2011/11/05 10:39

ご質問で掲示されている金額の計算結果はかなりあちこち計算間違いがあるようですが，

Ｂ９：
=IF(B2=0,0,B2*VLOOKUP(B2,$F$2:$I$4,4))
として右に下にコピーします。


参考：
対照表の作り方を添付図のようにすると
=IF(B2="","",B2*VLOOKUP(B2,$F$2:$I$5,4))
という計算に出来ます。
前述の式は，個数リストにまだ数字が入っていないセルと，ゼロ個と記入されているセルを区別できないことを確認してください。

この回答への補足

私の説明が足りませんでした。申し訳ありません。
例えば「20個購入した場合、20個全てを１つ20円で計算する」のではなく、
「10個目までは１つ30円、11～20個目までの10個は１つ20円で計算する」という作業をしたかったのです。
ですからこの場合、合計は400円ではなく500円となります。
これは無理な注文なのでしょうか・・・

補足日時：2011/11/05 11:53

No.1 回答者： -9L9- 回答日時： 2011/11/05 10:31

VLOOKUP関数のTRUEオプションの典型的な使用例ですね。

通常、実務で質問のような表を作るようなことはまず考えられないことから、課題の解答のための質問と思われるので、ヒントのみ示します。
下記のサイトの中盤以降に「■リストの検索　（2）縦方向・「～以上」」というところがあり、TRUEオプションの使用例があります。これで範囲ごとの単価の抽出ができますので、これを式に組み込めば目的の計算結果が得られます。
http://www.geocities.jp/oyakamassan_m/kansu/fxki …