[[20190125141523]]　『販売手数料算出のための最適関数』（おっくん）　ページの最後に飛ぶ

『販売手数料算出のための最適関数』（おっくん）

下記のような販売手数料の取り決めにおいて、販売する機械に付与される販売手数料の算出を簡単に行えるエクセルシートを作りたいと思います。

(1)売上額が50万円以下の場合、販売手数料は売上額の10％
(2)売上額が75万円以下の場合、販売手数料は売上額50万円までの部分には10％、50万1円から75万円までの部分には8％
(3)売上額が100万円以下の場合、販売手数料は売上額50万円まではの部分には10％、50万1円以上75万円以下までの部分には8%、75万1円から100万円までの部分には6％
(4)売上額が100万円1円以上の場合、販売手数料は売上額50万円までの部分には10％、50万1円以上75万円以下までの部分には8%、75万1円から100万円までの部分には6％、100万１円以上の部分には５％

売上額が30万円であれば、販売手数料は3万円ですが、販売金額の欄に30万円と入力すれば販売手数料の欄に3万円、売上額が80万円であれば、販売手数料の欄に7万4千円(5万円+2万4千円)と表示されるようにしたいです。

いかなる販売金額の時にも販売手数料が算出でき、且つ表中に変な表示が出ないようにしたいと思いますが、どなたか最適なエクセル式をご教示ください。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 100万円の場合、5万+2万+3千で7万3千ではないのか？
 =IF(A1="","",MIN(A1,500000)*10%+MAX(0,MIN(A1,750000)-500000)*8%+MAX(0,MIN(A1,1000000)-750000)*6%+MAX(0,A1-1000000)*5%)
 ではどうか？
（ねむねむ） 2019/01/25(金) 15:09

 すまない。
 >100万円の場合、5万+2万+3千で7万3千ではないのか？ 
     ↓
  80万円の場合、5万+2万+3千で7万3千ではないのか？
（ねむねむ） 2019/01/25(金) 15:21

 =IF(A1="","",A1*5%+MIN(A1,1000000)*1%+MIN(A1,750000)*2%+MIN(A1,500000)*2%)
 これでもいいか。
（ねむねむ） 2019/01/25(金) 15:41

ありがとうございます。ご指摘の部分はその通りです。ご連絡頂いた数式を試してみます！
（おっくん） 2019/01/25(金) 15:49

ねむねむ様

先日は有り難うございました。ただ、当方はエクセル初級者なので、ねむねむ様にお教え頂いた式が理解できません。。。

お教え頂いた、=IF(A1="","",A1*5%+MIN(A1,1000000)*1%+MIN(A1,750000)*2%+MIN(A1,500000)*2%)の内容は、一番初めは、A1が空欄ならば、空欄にしておくことは分かりますが、それ以降のMIN関数の部分が理解を超越しています。MINのカッコ内と*1%、*2%はどういう意味なのでしょうか？ご多忙のところ恐縮ですが、お教え頂ければ幸いです。

（おっくん） 2019/01/29(火) 10:40

%を全部足すと10%、というだけではないでしょうか？あとはMIN関数で、金額に応じて小さい方を使っているかと。

MIN関数が、指定した中の最も小さい値を返すものだ、という事だけ調べれば、あとは初心者かどうかは関係なく、ひらめきだけの問題です。初級者だから判らない、という思い込みは捨てた方が良いでしょう。貴方も同じ発想をひらめくまで、A1に境界条件の金額とかいろいろ入力し、検算してみると良いです。
（???） 2019/01/29(火) 11:01

  500000以下────┐                 2%────┐10%
  750000以下────┴──┐           2%──┐8%┘
 1000000以下───────┴──┐     1%┐6%┘
 1000000超  ──────────┴─┐ 5%┘
            ────────────┘
（ねむねむ） 2019/01/29(火) 11:02

 一つ目の数式は範囲を縦に区切って求めるもの、２番目は上記の図のように横に区切って求めている。
 でわかるだろうか？
（ねむねむ） 2019/01/29(火) 11:04

縦と横とは、うまい表現ですね。全金額を棒にして、50万分切ったものに10%かけて…、というのが縦で、全金額に5%かけるのは確定、100万以下には1%…とするのが横ですか。あ、この例えだと、切った後の棒をイメージすると、縦横逆の方が判りやすいでしょうか。ネギの輪切りと千切り、みたいな。
（???） 2019/01/29(火) 11:50

ねむねむ様

11時1分にコメントをくれた方が言うように、ヒラメキがないです！
ねむねむ様がお書きになっていることが、すみません、さっぱり分かりません。。。

50万以下が2%で10%、75万以下が2%で8%、100万以下が1%で6%、100万以上が5%って
どういうことなんでしょうか？

また、50万までの販売手数料、75万までの販売手数料、100万までの手数料、100万
以上の手数料とエクセルの列を分けたいのですが、それぞれ、式はどうやって分割
したらよいでしょうか？

すみません、分からないのでご教示ください。

（おっくん） 2019/01/29(火) 12:25

 前回の図全体が手数料の全額となる。
 そして全体で5%を求め、100万以下の部分で1%を求めると既に全体に5%を求めているので100万以下は6%となっている。
 同じように75万以下の部分で2%を求めると75万以下は5%+1%+2%で8%、50万以下で2％を求めると50万以下は5%＋1%＋2%＋2%で10%となる。
（ねむねむ） 2019/01/29(火) 12:37

 総合すると
 100万を超える部分は5%、75万を超えて100万以下は6%、50万を超えて75万以下は8%、50万以下は10%となる。

（ねむねむ） 2019/01/29(火) 12:41

本筋と関係ないですが。

 >11時1分にコメントをくれた方が言うように、

???さんといいます。

もしかしたら???を見てネームがない、あるいは不表示にしてると勘違いしてるのかなと思ったので。

失礼します。
（おえう） 2019/01/29(火) 12:44

 手数料を分ける場合は、1番目の式が各価格帯で分けて計算して足しているものなので
 MIN(A1,500000)*10%+MAX(0,MIN(A1,750000)-500000)*8%+MAX(0,MIN(A1,1000000)-750000)*6%+MAX(0,A1-1000000)*5%)
 の式を+で区切ってみてくれ。
（ねむねむ） 2019/01/29(火) 12:45

私の名前は文字化けしているのではなく、記号にしているのですよ。他所と同じ名前だと目立つかな、と思ったので、ワイルドカードで伏せました。
ひらめきの補助として、例えば160万の場合を例に、表にしてみます。

 縦案	100上	75上	50上	50未	計
 	600000	250000	250000	500000	1600000
 	30000	15000	20000	50000	 115000

これは、元の条件の通り計算しているので、判るかと思います。
次に、短い方の数式の場合。

 横案	5%	1%	2%	2%	計
 	1600000	1000000	750000	500000
	80000	10000	15000	10000	 115000

こんな感じになるのですが、ネギを思い浮かべて、ひらめきませんかね？
（???） 2019/01/29(火) 12:59

お教え頂いた方々
色々と有り難うございました。

（おっくん） 2019/01/31(木) 10:32

コメント返信：

[ 一覧(最新更新順) ]

[[20190125141523]] 『販売手数料算出のための最適関数』（おっくん） ページの最後に飛ぶ

[[20190125141523]]　『販売手数料算出のための最適関数』（おっくん）　ページの最後に飛ぶ