金額内での％の変更方法

初めて質問させて頂きます。

今回、エクセルで歩合給の計算をしたいと思っており
歩合の内容が下記になり、これをエクセル上で計算したいと思っております。
１００万円までの分５％
１０１～２００万円までの分１５％
２０１から３００万円までの分２０％
３００万円～　３０％

売上　４００万円
歩合　７０万円

売上部分に数字を入力して歩合も自動的に計算したいと思っております。

こちらの方法をお伺いしたいです。

宜しくお願い致します。

No.1 回答者： fujillin 回答日時： 2021/06/26 22:27

こんばんは

事前準備として、どこか空きセルに4行×3列の表を作っておきます。
また、これに「歩合表」と名前を付けておきます。
値の内容は以下の通り。（｜はセルの区切りを意味します）
　 0　｜　0.05　｜　0
100　｜　0.15　｜　5
200　｜　 0.2　｜　20
300　｜　 0.3　｜　40

上記を利用して、
歩合 =(売上-VLOOKUP(売上,歩合表,1,1))*VLOOKUP(売上,歩合表,2,1)+VLOOKUP(売上,歩合表,3,1)
ではいかがでしょうか？

No.2 回答者： goomania 回答日時： 2021/06/28 14:57

No.1さんが、歩合表とVLOOKUP関数を使用した数式をお示しになったので、CHOOSE関数を使用した数式を投稿します。

売上の数値がA2セルに万円単位で入力されているとした場合、
歩合を表示したいセルに

=IFERROR(CHOOSE(MIN(ROUNDUP(A2/100,0),4),A2*0.05,A2*0.15-10,A2*0.2-20,A2*0.3-50),"")

という数式を記述すれば、歩合金額が万円単位で表示されます。

※数式の解説
MIN(ROUNDUP(A2/100,0),4)で、０以上100未満、100以上200未満、200以上300未満、400以上のランク分けをしています。
A2*0.05は売上の5%を計算、A2*0.15-10は100*0.05+(売上-100)*0.15の括弧を外した、5+売上*0.15-15を整理した数式です。
A2*0.2-20も上記と同様に10０*0.05+100*0.15+(売上-200)*0.2のの括弧を外した、5+15+売上*0.15-40を整理した数式です。
A2*0.3-50も上記と同様です。

この回答へのお礼

ありがとうございます。
因みに
１００万円までの分５％
１０１～２００万円までの分１５％
２０１から４００万円までの分２０％
４０１万円～　３０％

とする場合はどこの数字を変えればいいでしょうか？

数式を理解せずご質問してしまい申し訳ございません。

宜しくお願い致します。

お礼日時：2021/07/02 12:36

No.3 回答者： goomania 回答日時： 2021/07/02 18:38

No.2です。

>１００万円までの分５％
>１０１～２００万円までの分１５％
>２０１から４００万円までの分２０％
>４０１万円～　３０％
に変更したいとのことなので、
これを
グループ1:0万円超100万円以下
グループ2:100万円超200万円以下
グループ3:200万円超300万円以下
グループ4:300万円超400万円以下
グループ5:400万円超
という仮想のグループに分けます。(ご質問者の説明のとおり、グループ３とグループ4は分ける必要はないのですが、あくまでも仮想のグループ分けです。)
夫々のグループの歩合を計算する数式を考えます。
グループ1の歩合は売上×0.05です。
グループ2の歩合は5+(売上-100)×0.15→売上×0.15-10です。
グループ3の歩合は5+15+(売上-200)×0.2→売上×0.2-20です。
グループ4の歩合は5+15+(売上-200)×0.2→売上×0.2-20(上と同じ)です。
グループ5の歩合は5+15+40+(売上-400)×0.3→売上×0.3-60です。
これらをまとめると、A2に売上金額(万円単位)があるとして、歩合を表示したいセルに

=IFERROR(CHOOSE(MIN(ROUNDUP(A2/100,0),5),A2*0.05,A2*0.15-10,A2*0.2-20,A2*0.2-20,A2*0.3-60),"")

という数式を記述すれば、歩合金額が万円単位で表示されます。


===余談===
通常、日本円で商取引をするときは単位は「円」です。
>１００万円までの分５％
>１０１～２００万円までの分１５％
>２０１から４００万円までの分２０％
>４０１万円～　３０％
日本語としてはOKですが、数学(EXCELの数式)的には困った条件です。
例えば、売上が1,000,001円から1,009,999円の時はどうするのでしょうか?
上記の表現はあくまでも、単位が「万円」のときに通じる表現です。
数学的に範囲を説明するときは
グループ1:A以上B未満
グループ2:B以上C未満
グループ3:C以上D未満
････････････････････
或いは
グループ1:A超B以下
グループ2:B超C以下
グループ3:C超D以下
････････････････････
というように、各グループ間に隙間がないこと、ランク分けするポイントが必ずどちらの範囲に含まれるのかを明確に示すこと、が必要です。
つまり、
グループ1:A以上B以下
グループ2:B以上C以下
のような表現はありがちですが、数学(EXCELの数式)的にはNGです。(Bがグルーブ1なのかグルーブ2なのか不明になります)
また、逆に、
グループ1:A以上B以下
グループ2:C以上D以下
のような表現だとCとDの間の処理が不明になります。
というわけで、ご質問者の表現は万円単位でないと、範囲に隙間がありEXCELの数式作成に支障があります。

No.4 回答者： googoo900 回答日時： 2021/07/04 00:49

一番初歩的と思われる、IFで金額に応じて場合分けする数式の回答例です。

歩合を出したいセルに下式を入力します。A1セルに売上が入力されているとしています。
「○○円まで」を「○○円以下」としています。「○○円未満」としたいのであれば、式の不等号の右隣の"＝"を削除すればよいです。

『=IF(A1<=100000,A1*0.05,IF(A1<=2000000,A1*0.15,IF(A1<=3000000,A1*0.2,A1*0.3)))』