エクセル 計算式 手数料計算

質問

こんにちは。
質問させていただきます。

エクセルを使い、手数料の算出をしたいです。
条件は以下の通りです。

セルＡが
(1)50以下の場合
その数に対し7.5％

(2)50以上1,000以下の場合
50までに対し7.5％
50を引いた残りの数に対し4％
両方の合計

(3)1,000以上の場合
50までに対し7.5％
50を引いた残りの数に対し4％
さらに1,000引いた数に対し2％
これらの合計

【例】
(1)45
45×0.075=3.375

(2)150
50×0.075+(150-50)×0.04=7.75

(3)1200
50×0.075+(1200-50)×0.04+(1200-50-1,000)×0.02=52.75

これを一本の式にまとめたいです。

よろしくお願います。

回答No.7

kagakusuki · ベストアンサー

>(3)1,000以上の場合
>50に対し7.5％
>元の数から1000を引いた残りの数に対し2％
>1000から50を引いた950に対し4％
>これらの合計

　それでしたら、

=IF(ISNUMBER($A1),MIN($A1,50)*0.075+(MIN($A1,1000)-50)*0.04*($A1>=50)+($A1-1000)*0.02*($A1>=1000),"")

　或いは

=IF(ISNUMBER($A1),$A1*LOOKUP($A1,{0,50,1000;7.5,4,2})/100+LOOKUP($A1,{0,50,1000;0,1.75,21.75}),"")

になります。
　尤も、条件を当てはめる範囲を「(1)50以下、(2)50を超えて1000以下、(3)1000を超えて」とした場合の関数である、

=IF(ISNUMBER($A1),MIN($A1,50)*0.075+(MIN($A1,1000)-50)*0.04*($A1>50)+($A1-1000)*0.02*($A1>1000),"")

でも同じ結果となりますが・・・
　それから、A列内でセルの切り取り、挿入、削除、等のセルの位置関係がずれる様な編集作業を行っても、正しい結果が出る様にする場合には、「$A1」の部分を「INDEX($A:$A,ROW())」に置き換えて、次の様にします。

=IF(ISNUMBER(INDEX($A:$A,ROW())),MIN(INDEX($A:$A,ROW()),50)*0.075+(MIN(INDEX($A:$A,ROW()),1000)-50)*0.04*(INDEX($A:$A,ROW())>50)+(INDEX($A:$A,ROW())-1000)*0.02*(INDEX($A:$A,ROW())>1000),"")

　或いは

=IF(ISNUMBER(INDEX($A:$A,ROW())),INDEX($A:$A,ROW())*LOOKUP(INDEX($A:$A,ROW()),{0,50,1000;7.5,4,2})/100+LOOKUP(INDEX($A:$A,ROW()),{0,50,1000;0,1.75,21.75}),"")

お礼

納得いく計算式を入れることが出来ました。
何度もご丁寧な説明ありがとうございました。

回答No.6

keithin

こんばんは。

=IF(A1="","",CHOOSE(MATCH(A1,{0,50,1000},1),A1*7.5%,A1*4%+1.75,A1*2%+21.75))
でいいです。

回答No.5

kagakusuki

>(1)は50未満の数、(2)は50以上1000未満、(3)は1,000以上と条件を変更したいと思います。
>この条件で式は組み立てられるでしょうか？

　式を組み立てる事"だけ"でしたら可能ですが、質問者様が仰った変更点だけでは、

>1050未満の場合はマイナスになってしまいますね

という欠陥を放置しておられますから、ANo.3と同様の結果となってしまいます。
　2％を掛ける対象となる数値をどの様にされるのか、という事も御指示願います。

補足

なるほど。
それではご提案いただいた
(3)1,000以上の場合
50に対し7.5％
元の数から1000を引いた残りの数に対し2％
1000から50を引いた950に対し4％
これらの合計

という条件で式を作ってください。

よろしくお願いします。

回答No.4

kagakusuki

ANo.3です。

>マイナスにならないようにするにはどのような式を入れればいいのでしょうか？

　マイナスになったのは、質問者様の提示された条件に従って式を立てた結果なのですから、式の問題ではなく、条件そのものを変えて頂かなくてはどうしようも御座いません。
　条件は質問者様の方で提示されたものであり、回答者には、どのような経緯でその様な条件に決まったのかは、判る筈も御座いませんから、どの様な条件とすべきかは回答者の方で「この方法にすべきだ」と勝手に決めて良い事ではなく、質問者様に新たな条件を提示して頂くより他には術は御座いません。
　尚、質問者様が提示された条件には幾つかの矛盾や不明確な点が御座います。
　(1)は「50以下の場合」としながら、(2)において「50以上1,000以下の場合」とされていますが、50丁度は「50以下の数」であると同時に「50以上の数」でもありますから、50丁度の場合は(1)と(2)のどちらの条件を当てはめれば良いのかが不明という事になります。
　尤も、(2)の条件の内容が、「50までに対し7.5％、50を引いた残りの数に対し4％」という、「50丁度の場合に得られる結果」が偶々同じ値となるため、本質的な矛盾は生じておりませんが、一般的には、境界付近の数値はどちらの条件を当てはめる範囲に属するのかを、明確にしておかねばなりません。
　ですから、50丁度の場合において、(1)と(2)のどちらの条件を当てはめれば良いのかという事と、1000丁度の場合において、(2)と(3)のどちらの条件を当てはめれば良いのかという事を明確にして頂く様、御願いします。
　例えば、
「50丁度の場合には、(1)の条件のみを当てはめて、(2)の条件は当てはめない」という場合には、(1)は「50以下」、(2)は「50を超えて」とすべきですし、
「50丁度の場合には、(2)の条件のみを当てはめて、(1)の条件は当てはめない」という場合には、(1)は「50未満」、(2)は「50以上」とすべきです。

　この事は、(2)と(3)の「1,000以上」、「1,000以下」に関しても同様ですから、忘れずに明確にして下さい。
　そうしませんと、(2)の条件において、「50までに対し7.5％、50を引いた残りの数に対し4％」とされていますから、元の数が1000の場合、
「50まで」×0.075=50×0.075=3.75
「50を引いた残りの数」×0.04=(1000-50)」×0.04=950×0.04=38
「合計」=3.75+38=41.75
が、手数料となるのに対し、
(3)の条件において、「50までに対し7.5％、50を引いた残りの数に対し4％、さらに1,000引いた数に対し2％」とされていますが、元の数が1000の場合、
「50まで」×0.075=50×0.075=3.75
「50を引いた残りの数」×0.04=(1000-50)」×0.04=950×0.04=38
「『50を引いた残りの数』から更に1000引いた数」=(1000-50-1000)×0.02=(-50)×0.02=-1
「合計」=3.75+38+(-1)=40.75
が手数料という事になりますから、
1000丁度に対して当てはめる条件をどちらにするのかを明確にしませんと、手数料をどちらの金額とすべきなのかを決める事が出来ません。

　又、1050未満の数に対して、「50を引いた残りの数」から更に1000引きますと、
1000-50-1000=-50
1001-50-1000=-49
1002-50-1000=-48
　　　　　・
　　　　　・
　　　　　・
1049-50-1000=-1
という様に、2％を掛ける数値がマイナスとなってしまいます。
　これは50を引いた事で既に1000よりも小さくなってしまっている残りの数から、更に1000を引いた事が原因です。

>(2)を50～1050まで、(3)を1050以上にすればいいのでしょうか？

　それも一つの手ですが、その場合、1050丁度の場合は(2)と(3)のどちらの条件を当てはめれば良いのでしょうか？
　他にも、

(2)50を超えて、1000以下の場合
50に対し7.5％
50を引いた残りの数に対し4％
両方の合計

(3)1,000以上の場合
50に対し7.5％
元の数から1000を引いた残りの数に対し2％
1000から50を引いた950に対し4％
これらの合計

とする手も御座います。

　言うまでも御座いませんが、「(2)を50～1050まで、(3)を1050以上にする」という方法と、私が提案した方法、それと又別の方法、の何れにすれば良いのかは、質問者様（質問者様のお勤め先）の方で決めて頂かなければなりません。

>どのような式を入れればいいのでしょうか？

に関しましては、その事を決めて頂いた結果を、ここの補足欄等に提示して頂いてからの話となります。

補足

丁寧なご回答ありがとうございます。
確かにそうですね。
(1)は50未満の数、(2)は50以上1000未満、(3)は1,000以上と条件を変更したいと思います。
この条件で式は組み立てられるでしょうか？

回答No.3

kagakusuki

確認したいのですが、「(3)1200」の場合の数式からしますと、A列のセルに入力されている値が1000を超えて、1050未満の場合には、2％を掛ける値がマイナスになるのですが、それでも宜しいのでしょうか？

(1001-50-1000)×0.02=(-49)×0.02=-0.98
(1049-50-1000)×0.02=(-1)×0.02=-0.02

　それでもよろしいのだとしますと、以下の様になります。
　A列のセルに数値が入力されていない場合には、何も表示しないようにするものとして、

=IF(ISNUMBER($A1),IF($A1>50,50,$A1)*0.075+($A1-50)*0.04*($A1>50)+($A1-50-1000)*0.02*($A1>1000),"")

或いは

=IF(ISNUMBER(INDEX($A:$A,ROW())),IF(INDEX($A:$A,ROW())>50,50,INDEX($A:$A,ROW()))*0.075+(INDEX($A:$A,ROW())-50)*0.04*(INDEX($A:$A,ROW())>50)+(INDEX($A:$A,ROW())-50-1000)*0.02*(INDEX($A:$A,ROW())>1000),"")

になります。

補足

丁寧なご説明ありがとうございます。

確かに、1050未満の場合はマイナスになってしまいますね。
マイナスにならないようにするにはどのような式を入れればいいのでしょうか？
(2)を50～1050まで、(3)を1050以上にすればいいのでしょうか？

質問ばかりで申し訳ありません。
ご回答いただければ幸いです。

回答No.2

keithin

とりあえずご相談の直接の回答としては
=A1*7.5%+MAX(0,A1-50)*-3.5%+MAX(0,A1-1050)*2%
で計算できます。

＃ところでちなみに。
１０００に対して（２）と（３）で計算結果の食い違いがあります。

＞さらに1,000引いた数に対し2％

の扱いの間違いが原因なので、もう一回やりたいことをよく検討してみてください。

回答No.1

K Kazz（@JazzCorp）

=IF($A1<=50,$A1*7.5/100,IF(A1<=1000,(50*7.5+($A1-50)*4)/100,(50*7.5+($A1-50)*4+(A1-1050)*2)/100))