[[20160616133508]]　『この長い計算式をシンプルにする式はありますでし』（maya）　ページの最後に飛ぶ

『この長い計算式をシンプルにする式はありますでしょうか。』（maya）

1個入り1000円　2個入りは単価900円　3個入りは単価800円　4個入りは単価700円　の場合の　数量18個の場合の計算式ですが、こんなにまどろっこしくなってしまいます。
シンプルな計算式がありましたらお教えください。

A1 B1 C1 D1 に入り数
1入り 2入り 3入り 4入り　

A2 　　B2 　 C2 D2 に各単価
1000円　900円　800円　700円

A3に数量の18

=+(INT(A3/D1)*D1*D2)+(INT((A3-(INT(A3/D1))*D1)/C1)*C1*C2)+(INT((A3-(INT(A3/D1))*D1-(INT((A3-(INT(A3/D1))*D1)/C1))*C1)/B1)*B1*B2)+(INT((A3-(INT(A3/D1))*D1-(INT((A3-(INT(A3/D1))*D1)/C1))*C1-(INT((A3-(INT(A3/D1))*D1-(INT((A3-(INT(A3/D1))*D1)/C1))*C1)/B1))*B1)/A1)*A1*A2)
という感じです。

よろしくお願いいたします。

＜使用 Excel：Excel2010、使用 OS：Windows8 ＞

=INT(A3/D1)*D1*D2+(INDEX(A2:D2,MOD(A3,D1)))*(A3-INT(A3/D1)*D1)
（mm） 2016/06/16(木) 13:54

mayaさん、欲しい答えは4個入り4つ、2個入り1つの計3700円（18個）ってことでしょうか。

であれば、次の式で求められるかと。

=QUOTIENT(A3,D1)*D2+HLOOKUP(MOD(A3,D1),$A$1:$D$2,2,0)

意図の読み違いであればすみません。
（chiki） 2016/06/16(木) 14:00

 今回の質問は
 >1個入り1000円　2個入りは単価900円　3個入りは単価800円　4個入りは単価700円　の場合
 に特化した式か、個数と単価を変えても成り立つ式のどちらだろうか？
 (わざわざ個数と単価をセルに持っているためそこを変更する可能性があるのかを聞いている）

 特化した式で有れば4で割った余りは0,1,2,3のどれかなので
 =INT(A3/D1)*D1*D2+SUMPRODUCT((A1:C1=MOD(A3,D1))*A2:C2*A1:C1)
 で求められるが1行目の個数が変わると成り立たない。
（ねむねむ） 2016/06/16(木) 14:01

 chikiさん、
 mayaさんの式で
 (INT(A3/D1)*D1*D2)
 となっているということは4個入りの場合は1個700円の2,800円、3個入りの場合は1個800円の2,400円…なので今回の場合は

 4個入り4つで11,200円、2個入り一つの1,800円、合わせて13,000円ではないか。

（ねむねむ） 2016/06/16(木) 14:07

ねむねむさん、ありがとうございます。
そうですね。きちんと読んでなくてすみません。私の考えていた式でいくと
=QUOTIENT(A3,D1)*D1*D2+HLOOKUP(MOD(A3,D1),$A$1:$D$2,1,0)*HLOOKUP(MOD(A3,D1),$A$1:$D$2,2,0)
こんな感じでしょうか。

でもご指摘のとおり、1行目の個数が左から昇順になっていないと成り立たない式ではあります。
（chiki） 2016/06/16(木) 14:18

chikiです。
すみません。私の式ではMOD(A3,D1)が0の場合、NAになってしまいますので、ねむねむさんの式が美しいと思います。検証不足失礼しました。
（chiki） 2016/06/16(木) 15:35

たくさん回答を付けていただいてありがとうございます。
また、問合せの内容に不足がありました。すみません。

おっしゃる通り、合計数は変わります。また、入り数　や　金額も変更されます。

試しに長くまどろっこしい式で試算をしたのが下です。

単価：1個入り=@1000　2個入り=@900　3個入り=@800　4個入り=@700

　　合計数量→　　1個　　　2個　　　3個　　　4個　　　5個　　　6個　　　7個　　　8個　　　9個　　　10個
　　↓組合せ

 	1個入り	1	0	0	0	1	0	0	1	0	0
 	2個入り	0	1	0	0	0	1	0	0	1	1
 	3個入り	0	0	1	0	0	0	1	1	1	0
 	4個入り	0	0	0	1	1	1	1	1	1	2
合計金額→	1000	1800	2400	2800	3800	4600	5200	5600	6600	7400

です。
（maya） 2016/06/16(木) 19:50

 ３行目を作業行として使う。
 まず、合計数量を入れるのをE3セルに変更する。
 そしてD3セルに
 =INT(E3/D1)
 と入力。

 C3セルに
 =INT(MAX(0,$E3-SUMPRODUCT($D1:D1,$D3:D3))/C1)
 と入力して左へフィルコピー。

 最後に合計を求めたいセルに
 =SUMPRODUCT(A1:D1,A2:D2,A3:D3)
 でどうか。
（ねむねむ） 2016/06/16(木) 21:06

 おっとMAX関数はいらなかった。
 C3セルの式は
 =INT(($E3-SUMPRODUCT($D1:D1,$D3:D3))/C1)
 で。
（ねむねむ） 2016/06/16(木) 21:47

ねむねむさん、chikiさん、mmさんありがとうございます。
おかげで解決が出来きました。
理解が出来ていない関数もあるのでしっかり勉強します。
ご指導ありがとうございました。
（maya） 2016/06/18(土) 13:12

コメント返信：

[ 一覧(最新更新順) ]

[[20160616133508]] 『この長い計算式をシンプルにする式はありますでし』（maya） ページの最後に飛ぶ

[[20160616133508]]　『この長い計算式をシンプルにする式はありますでし』（maya）　ページの最後に飛ぶ