こんにちは。
質問させていただきます。
エクセルを使い、手数料の算出をしたいです。
条件は以下の通りです。
セルAが
(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ベストアンサー
- 回答日時:
>(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
- 回答日時:
こんばんは。
=IF(A1="","",CHOOSE(MATCH(A1,{0,50,1000},1),A1*7.5%,A1*4%+1.75,A1*2%+21.75))
でいいです。
No.5
- 回答日時:
>(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
- 回答日時:
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
- 回答日時:
確認したいのですが、「(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
- 回答日時:
とりあえずご相談の直接の回答としては
=A1*7.5%+MAX(0,A1-50)*-3.5%+MAX(0,A1-1050)*2%
で計算できます。
#ところでちなみに。
1000に対して(2)と(3)で計算結果の食い違いがあります。
>さらに1,000引いた数に対し2%
の扱いの間違いが原因なので、もう一回やりたいことをよく検討してみてください。
No.1
- 回答日時:
=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))お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- その他(Microsoft Office) ある表(10桝程度)の中に数字が入っています。ダブっている数字を除く数字の合計数の計算方法 5 2023/02/15 11:33
- 労働相談 有給休暇使用時の賃金の計算方法について 5 2022/04/04 00:02
- Excel(エクセル) エクセルの関数いれれますか? 1 2022/03/25 20:39
- Excel(エクセル) エクセルで関数の数式を入力できません。 3 2022/08/25 17:49
- 消費税 消費税の納税額の計算 1 2023/02/19 18:12
- 減税・節税 源泉給料 乙の支払 3 2023/03/12 17:27
- 数学 賃料と専有面積のデータが60部屋分ほどがあり、 賃料÷専有面積(=1㎡あたりの賃料)の数式で計算する 2 2023/02/18 20:33
- Excel(エクセル) エクセルの関数に関しての質問です。 5 2022/10/07 11:17
- Excel(エクセル) 図書カードの分配 7 2023/05/09 15:57
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのセルにFALSEが...
-
Accessのクエリの条件付けの際...
-
ベストアンサーを選ばす質問を...
-
あなたが結婚したいと思う相手...
-
SUMIFS関数とDSUM関数の違いに...
-
ノイマン条件について
-
FileMakerのcase関数の作り方
-
EXCEL・複数条件がある場合のCO...
-
同じ式の中にIF関数を2回使う
-
FAXの送り方
-
刑務官は駐車場無料?光熱費タダ?
-
1年後の春が待ち遠しいのですが...
-
外泊を許してくれない夫
-
努力効率が良いこと悪いこと
-
付き合っている日ってどうやっ...
-
努力万能主義が理解できません
-
灯油配送用ミニローリー
-
海上自衛隊や刑務官こそが、日...
-
警察学校について
-
職場などで時間が経つほど関係...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルのセルにFALSEが...
-
Accessのクエリの条件付けの際...
-
FileMakerのcase関数の作り方
-
IF関数を使って3つ以上のある...
-
建築を勉強しているものです。 ...
-
答えは?
-
関数の違いを教えてください
-
ベストアンサーを選ばす質問を...
-
同じ式の中にIF関数を2回使う
-
エクセルの関数DSUMについて
-
EXCEL・複数条件がある場合のCO...
-
リフロー炉の急激な温度上昇に...
-
エクセルの数式である文字を含...
-
教えてgooでは、ベストアンサー...
-
女子高生に貢ぎたいひといます?
-
エクセル 計算式 手数料計算
-
エクセルにて
-
x,yが有理数であることは、x+y...
-
ウイニングポスト7 マキシマ...
-
エクセル 関数の複数条件設定...
おすすめ情報