![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
こんにちは。
質問させていただきます。
エクセルを使い、手数料の算出をしたいです。
条件は以下の通りです。
セル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),"")
になります。
![「エクセル 計算式 手数料計算」の回答画像3](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/1235215_5497e5b4f21a3/M.jpg)
この回答への補足
丁寧なご説明ありがとうございます。
確かに、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で質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのセルにFALSEが...
-
条件付きの優しさの人って条件...
-
あなたが結婚したいと思う相手...
-
FileMakerのcase関数の作り方
-
エクセルのor関数について
-
レーザビームなどの光線
-
建築を勉強しているものです。 ...
-
一般曹候補生に落ちたら受験生...
-
FAXの送り方
-
警察学校について
-
主婦の外泊(長文です)
-
夕方飛んでるものはコウモリ?
-
刑務官宿舎
-
身体検査のとき興奮したら
-
そこそこの地銀に入るって凄い...
-
海上保安官の半分は、泳げない...
-
私は一般曹候補生と自衛官候補...
-
職場などで時間が経つほど関係...
-
努力するメリットとは?
-
海上自衛隊や刑務官こそが、日...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルのセルにFALSEが...
-
急に新着が表示されなくなった ...
-
教えてgoo 、アプリとブラウザ...
-
FileMakerのcase関数の作り方
-
EXCEL・複数条件がある場合のCO...
-
IF関数を使って3つ以上のある...
-
リフロー炉の急激な温度上昇に...
-
エクセル合計欄0(ゼロ)を表...
-
答えは?
-
女子高生に貢ぎたいひといます?
-
ベストアンサーを選ばす質問を...
-
建築を勉強しているものです。 ...
-
ノイマン条件について
-
エクセル 計算式 手数料計算
-
教えて!goo の回答は、「人生...
-
倒れないことをカッコよく言うと?
-
急ぎです!中3受験生
-
ウイニングポスト7 マキシマ...
-
条件分岐によるページ移動
-
x,yが有理数であることは、x+y...
おすすめ情報