エクセル関数で次の様な事はできますでしょうか？

質問

例えば、単価4500円の物を売った時、累計手数料が10500円に達するまでは、
タイプ60の場合、必ず1000円以下にならない、
タイプ30の場合500円以下にならない額を利益として残した差額を手数料とする。
（単価は変動します。）
その手数料の累計が10500円になる回は、10500円ー累計額を手数料。
以降はすべて手数料を500円で固定。
※手数料累計は、人ごとに累計
※人の並びは、バラバラに入っていきます。
（下記表は見やすく人ごとにソートした状態になってます。）

下記D列（手数料）を差出できませんでしょうか？
E列も自動計算したいが、なくてもいい項目です。
（手数料を算出する為に、累計を自動計算させたいのですが、
　循環参照になってしてしまう為）

※手入力する項目は、A、B、C、G列のみです。

A列　 　B列　　C列　　　D列　　　　E列　　　　F列　　　G列
名前　タイプ　　単価　　手数料　　手数料累計　　利益　　日付
山田　　60　　5000円　4000円　　4000円　　1000円　1/1
山田　　60　　4500円　3500円　　7500円　　1000円　1/3
山田　　60　　4500円　3000円　　10500円　 1500円　1/5
山田　　60　　6000円　　500円　　11000円　5500円　1/7
佐藤　　30　　2500円　2000円　　2000円　　　500円　1/1
佐藤　　30　　2000円　1500円　　3500円　　　500円　1/2
佐藤　　30　　2500円　2000円　　5500円　　　500円　1/3
佐藤　　30　　2500円　2000円　　7500円　　　500円　1/4
佐藤　　30　　2500円　2000円　　9500円　　　500円　1/5
佐藤　　30　　3000円　1000円　　10500円　　2000円　1/7
佐藤　　30　　3000円　　500円　　11000円　　2500円　1/9

回答No.2

mt2008 · ベストアンサー

無理やるやるならこんな感じかな。
H列を作業列として使います。

H2は空、H3には↓を入れて下にコピー。これが前回までの手数料累計値になります。
=SUMPRODUCT(MAX((E$2:E2)*(A$2:A2=A3)))

D2に↓
=IF(H2>=10500,500,IF((H2+C2-IF(B2=60,1000,500))>=10500,10500-H2,C2-IF(B2=60,1000,500)))

E2に↓
=H2+D2

D2:E2を下にコピー

お礼

わ～☆☆☆
感動致しました！！
神々しいです！！
SUMPRODUCTは、思いつきませんでした！！
循環参照がこれで、回避できるとは！！
画像と、数式の使い方、説明も、とても的確で頭が上がりません。
とても、助かりました！
しばらく、この感動が冷めなさそうです！！

本当にありがとうございました！
（＾＾）ノ☆

回答No.3

mt2008

先ほどの添付画像ですが、F列の「利益」は、テスト時の適当な値がそのまま残っているので、=C2-D2と入れてやってください。

お礼

最後まで、丁寧にご解説本当にありがとうございます～！！
泣きそうです☆

回答No.1

molly1978

>例えば、単価4500円の物を売った時、累計手数料が10500円に達するまでは、
>タイプ60の場合、必ず1000円以下にならない、
>タイプ30の場合500円以下にならない額を利益として残した差額を手数料とする。

手数料累計を出すには、手数料が必要です。手数料を出すには、手数料累計が必要です。
条件が循環しています。手計算できないものは、エクセルでも計算できません。

明確にするには、今までの累計手数料を使って新しい手数料を決めることです。

たとえば、今までの累計手数料が10500円未満なら利益は1000円、10500円以上ならタイプ60の手数料は1000円、タイプ30の手数料は500円で固定。

お礼

こんなに早く、考えてくださってありがとうございました！！
そうなんです。
タイプの手数料を固定にすれば、簡単なのですが、単価によっては利益が少なすぎてしまったり、
また単価も変動しているので、普通にやると循環参照になってしまうので、簡単にはできなくて困っていました。
早くにご意見頂いて、心強いかったです。ありがとうございました！！