[[20190731081838]]　『条件を超えた分だけ1%ずつ上がる関数について』（himawari）　ページの最後に飛ぶ

『条件を超えた分だけ1%ずつ上がる関数について』（himawari）

Excelの関数について教えてください。
10万までは10％、以降、10万を超えるごとにその分だけ1％ずつ利率が上がるという数式を教えていただけますでしょうか。

例えば、20万の場合、
100,000×10％＝10,000
（200,000‐10
0,000）×11％＝11,000
10,000＋11,000＝22,000
という解を出す数式が知りたいです。

よろしくお願いいたします。

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

 10,000＋11,000
 は
 21,000
 では？
 元の金額の最大はいくらになるだろうか？
（ねむねむ） 2019/07/31(水) 08:50

Excelというより高校数学の問題のような感じですね。
等差数列の和を求めると10万の位の桁切りでの利率が算出できるので
それに残りの利率を足すと良いのではないかと思います。
（夏休みの宿題） 2019/07/31(水) 08:54

A1に元金

=ROUNDDOWN(A1,-5)*((((INT((ROUNDDOWN(A1,-5))/100000))*10+(((INT((ROUNDDOWN(A1,-5))/100000))*((INT((ROUNDDOWN(A1,-5))/100000))-1))/2))/((INT((ROUNDDOWN(A1,-5))/100000))*10))/10)+(A1-(ROUNDDOWN(A1,-5)))*(0.1+((INT((ROUNDDOWN(A1,-5))/100000))/100))

（mm） 2019/07/31(水) 09:47

100000円未満の考慮漏れによる修正
=IF(A1>=100000,ROUNDDOWN(A1,-5)*((((INT((ROUNDDOWN(A1,-5))/100000))*10+(((INT((ROUNDDOWN(A1,-5))/100000))*((INT((ROUNDDOWN(A1,-5))/100000))-1))/2))/((INT((ROUNDDOWN(A1,-5))/100000))*10))/10)+(A1-(ROUNDDOWN(A1,-5)))*(0.1+((INT((ROUNDDOWN(A1,-5))/100000))/100)),A1*0.1)
（mm） 2019/07/31(水) 09:53

 元の金額が約100億まで対応。
 =A1*10%+SUMPRODUCT((A1-ROW(1:100000)*10^5)*((A1-ROW(1:100000)*10^5)>0))*1%
 式中の
 ROW(1:100000)
 の100000を大きくすると最大対応金額も大きくなっていく。
（ねむねむ） 2019/07/31(水) 10:25

 上記式を入力するブックが97-2003ブック（拡張子がXLS）の場合はROW(～)で指定できる最大値は65536になる。
 2007以降対応ブック（拡張子がXLSX、XLSM）だと1048576となる、
（ねむねむ） 2019/07/31(水) 10:36

こんなのも

=500*INT(A1/100000)^2+9500*INT(A1/100000)+MOD(A1,100000)*(0.1+INT(A1/100000)*0.01)

（渡辺ひかる） 2019/07/31(水) 10:57

コメントいただき、ありがとうございます。 10,000＋11,000＝21,000ですね。失礼しました。
初心者のわたしには到底導き出せない数式でした。本当にありがとうございます。

もう一度質問させていただきたいのですが、
A1が50万までは利率が35％、50万を超えるごとに超えた分だけ1％ずつ上がるという式はどうなりますでしょうか。教えていただいた数式に当てはめたのですが、どうもうまくいかず…

A1が200万の場合の利率↓

500,000＝35％→175,000
1,500,000＝38％→570,000
計：745,000

たびたび申し訳ありませんがよろしくお願いいたします。
（himawari） 2019/07/31(水) 11:54

最初の内容と計算方法が変わっていませんか？

	500,000		35%	175,000
	500,000		36%	180,000
	500,000		37%	185,000
	500,000		38%	190,000
計	2,000,000		730,000

となるのでは？

（渡辺ひかる） 2019/07/31(水) 12:21

 渡辺ひかるさんの確認のとおりでいいのであれば私の式の場合は
 =A1*35%+SUMPRODUCT((A1-ROW(1:100000)*5*10^5)*((A1-ROW(1:100000)*5*10^5)>0))*1%
 で。
（ねむねむ） 2019/07/31(水) 12:51

すみません！
計算方法が変わりまして、50万円をボーダーとして、超えた分に対して、UPした利率をかけるという式にしたいです。

200万-50万＝150万（150万の場合、3%利率UP）
50万：35％
150万：38％

何度も申し訳ありません。お返事いただけますと嬉しいです。よろしくお願いいたします。

（himawari） 2019/07/31(水) 13:07

 =A1*35%+MAX(0,A1-500000)*ROUNDUP(MAX(0,A1-500000)/500000,0)/100
 ではどうだろうか？
（ねむねむ） 2019/07/31(水) 13:20

 =A1*35%+MAX(0,A1-500000)*((ROUNDUP(A1/500000,0)-1)/100)
 これでもいいか。
（ねむねむ） 2019/07/31(水) 13:32

計算方法が変わったのなら、元の数式をいじっても、無理でしょうね

より簡単になったわけですから

=IF(A1<=500000,A1*0.35,175000+(A1-500000)*(INT((A1-1)/500000)*0.01+0.35))

でもいいと思います。

（渡辺ひかる） 2019/07/31(水) 13:34

200万の場合は解が一致するのですが、
例えば、2440332といった数字の場合、

500000*0.35=175000
1940332*0.38=737326
→912326
かと思うのですが、

いただいた数式で計算すると
931729になります。

どうしてでしょうか？？
（himawari） 2019/07/31(水) 23:30

200万の場合は解が一致するのですが、
例えば、2440332といった数字の場合、

500000*0.35=175000
1940332*0.38=737326
→912326
かと思うのですが、

いただいた数式で計算すると
931729になります。

どうしてでしょうか？？
（himawari） 2019/07/31(水) 23:30

2,440,332 円なら 39％ではないんですか？

また、条件変更？

（渡辺ひかる） 2019/08/01(木) 07:48

 以下もとになる金額
         ～  500000　35％
  500001 ～ 1000000  36％
 1000001 ～ 1500000  37％
 1500001 ～ 2000000　38％
 2000001 ～ 2500000  39％
 ではないのか？
 違うのであれば上記のような表にまとめてみてくれ。
（ねむねむ） 2019/08/01(木) 08:55

何度も本当にスミマセン！！
条件が変わってしまいました。。

100万ボーダーで、↓のようにしたいです。

～ 1000000　35％
1000001 ～ 1500000 36％
1500001 ～ 2000000　37％
2000001 ～ 2500000 38％
2500001 ～ 3000000 39％

その場合↓かなぁと思ったのですが、
=IF(A1<=1000000,A1*0.35,350000+(A1-1000000)*(INT((A1-1)/500000)*0.01+0.35))
=A1*35%+MAX(0,A1-1000000)*((ROUNDUP(A1/500000,0)-1)/100)
2440332の場合、911729になります。
1,000,000を引いた1440332の部分が39％になってしまうんですよね。

1000000*0.35=350000
1440332*0.38=547326
897326
になるようにしたいのです。

自分で考えられず、お手数をおかけして申し訳ありません。
（himawari） 2019/08/01(木) 21:06

 ＞1000000*0.35=350000 
 ＞1440332*0.38=547326 
 ＞897326 
 ＞になるようにしたいのです。

 急に簡単なロジックになったのですが、にわかに信じられないです。

 以下の例だと、投資1円増で、利息15,000円増になりますけど、本当にいいんですか？

  行  ____A____  _B_  ___C___  
   1  2,440,332       利息          
   2  1,000,000  35%  350,000       
   3  1,440,332  38%  547,326       
   4                  897,326       
   5                                
   6  2,500,000       利息          
   7  1,000,000  35%  350,000       
   8  1,500,000  38%  570,000       
   9                  920,000  ← ┐
  10                              │
  11  2,500,001       利息        │
  12  1,000,000  35%  350,000     │
  13  1,500,001  39%  585,000     │
  14                  935,000  ← ┘

 増加した1円にだけ、39%が適用されるんじゃないですか？

 質問のタイトルも「条件を超えた分だけ1%ずつ上がる」
　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　　　　　　「ずつ」ですからねぇ。

（半平太） 2019/08/01(木) 23:22

お返事いただきありがとうございます。
まとめていただいた内容で相違ありません。

質問のタイトルが分かりづらかったですよね。。

～ 1000000　35％
1000001 ～ 1500000 36％
1500001 ～ 2000000　37％
2000001 ～ 2500000 38％
2500001 ～ 3000000 39％

というグレードがあって、グレードがあがるごとに1％あがるということになります。
この場合の数式はどうなりますでしょうか。

教えていただけますと幸いです。
（himawari） 2019/08/03(土) 00:06

 ＞=A1*35%+MAX(0,A1-1000000)*((ROUNDUP(A1/500000,0)-1)/100) 
    　　　　　　　　　　　　　　　　　 ↓
   =A1*35%+MAX(0,A1-1000000)*(ROUNDUP(MAX(0,A1-1000000)/500000,0)/100)
 　　　　　　　　　　　　　　　　　　~~~~~↑~~~~~~~~~~~
 　　　　　　　　　　　　　　　　　　基準額超で計算する

（半平太） 2019/08/03(土) 10:07

私のほうを修正するなら

=IF(A1<=1000000,A1*0.35,350000+(A1-1000000)*(INT((A1-1)/500000)*0.01+0.35))

ではなくて

=IF(A1<=1000000,A1*0.35,350000+(A1-1000000)*(INT((A1-500001)/500000)*0.01+0.35))

となりますね。

=IF(A1<=1000000,A1*0.35,350000+(A1-1000000)*(INT((A1-1)/500000)*0.01+0.36))

でも同じですが・・・・

結局、百万円を1円でも超えると1％上がるわけですから
超過分だけ考えると

1 ～ 500000 1％
500001 ～ 1000000　2％
1000001 ～ 1500000 3％
1500001 ～ 2000000 4％

が得られるような関数を考えればいいわけです。

（渡辺ひかる） 2019/08/03(土) 12:46

みなさま、ありがとうございます涙
一件落着しました。

条件が変更になったり、わたしのお伝えの仕方が悪かったりでご迷惑をおかけしました。

本当にありがとうございましたｍｍ

（himawari） 2019/08/04(日) 16:14

コメント返信：

[ 一覧(最新更新順) ]

[[20190731081838]] 『条件を超えた分だけ1%ずつ上がる関数について』（himawari） ページの最後に飛ぶ

[[20190731081838]]　『条件を超えた分だけ1%ずつ上がる関数について』（himawari）　ページの最後に飛ぶ