エクセルの計算式で

質問

利息の計算をしたいのですが
関数の使い方を教えてください。

債務の残高がAの列に入ります。
　　　　　　Ａ(残高)　　Ｂ(利率)
1(日付)　　100000
　　　　　　10000
　　　　　　　…
というような形です。

Ｂ列に入る利率を計算する式を教えてください。

条件
1.直前の行の残高がマイナスだった場合、5%になる
2.直前の行の残高が10万から100万の間だったら18%になる
3.直前の行の残高が100万以上なら15%になる
4.一度条件3で15%になったら、以降はずっと15%となるが、マイナスになったとき(条件1)の場合は条件1が優先される

わかりにくいかも知れませんが、よろしくお願いします。

回答No.4

dsuekichi · ベストアンサー

> ただ、日付を入れる欄があって、そこを別に参照しているセルがあるらしく
> 循環参照と出てしまいます。

ひょっとして、A列は日付？

> 利率の入っている実際のセルはH列なので
> 教えていただいたB→Hに変更して記述はしました。

あえて書かなかったんですが・・・

残高が入っているセルはA列と仮定しています。
つまり、残高がA列でなければ、修正が必要です・・・

補足

もちろん、そこの部分も修正しました。

というか…
できました!!

教えていただいたとおりにしたら
できました!すみません。

ただ、もうひとつ教えてくださいませんか?
最初にご指摘のとおり、1円以上10万円未満の場合、20%とすることにしました。
先ほどは、残高が100万円以上で15%に一度なったら
以降ずっと15%で…と言いました。
今度は
「一度でも10万円以上になった場合、残高が10万円未満となっても
利率は18%のまま」という条件が加わりました。

もちろん、その上の100万円になったら以降はずっと15%になります。

ああ～!もう書いててもややこしくなってきました。

さきほど教えていただいた
=IF(A1<0,0.05,IF(COUNTIF(B$1:B1,0.15)>0,0.15,IF(A1>=1000000,0.15,0.18)))
という式にプラスしてうまく計算できる方法はありますか？

まとめると
1～99,999　20%
100,000～999,999　18%
1,000,000以上　　15%
ただし、一度下がった利息はもう上がらない。
直前の残高が0以下になった場合は5%となる。

最後の質問です！
よろしくお願いします！

回答No.5

dsuekichi

> まとめると
> 1～99,999　20%
> 100,000～999,999　18%
> 1,000,000以上　　15%
> ただし、一度下がった利息はもう上がらない。
> 直前の残高が0以下になった場合は5%となる。

えー、つまり

条件は、
A.直前の行の残高がマイナスだった場合、5%になる
B.一度でも15%になっていたら、15%になる
C.直前の行の残高が1,000,000円以上なら15%になる
D.一度でも18%になっていたら、18%になる
E.直前の行の残高が100,000円から999,999円の間だったら18%になる
F.直前の行の残高が1円から99,999円の間だったら20%になる

って事でいいのかな・・・

この順番でIF文を作って行けばよいのですよね？
こんな感じ？

IF(A1<0, <===== 条件Aチェック
0.05, <===== 条件A成立
IF(COUNTIF(B$1:B1,0.15)>0, <==条件A不成立なので、条件Bチェック
0.15, <====== 条件B成立
IF(A1>=1000000, <====== 条件B不成立なので、条件Cチェック
0.15, <====== 条件C成立
IF(COUNTIF(B$1:B1,0.18)>0, <==== 条件C不成立なので、条件Dチェック
0.18, <====== 条件D成立
IF(A1>100000, <====== 条件D不成立なので、条件Eチェック
0.18, <====== 条件E成立
0.2 <====== 条件E不成立なので、自動的に条件Fに
)
)
)
)
)

＃説明と、余分な改行・空白をとれば、動作すると思いますが・・・

お礼

できました！！
ありがとうございました。
あれこれと注文をつけたのに、丁寧に教えていただいて
ホントに感謝してます。
助かりました。ありがとうございました！

回答No.3

dsuekichi

> やってみたのですが、0%になってしまいます。
> 実際にはセルがBではないので、その書き方がおかしいのでしょうか…

> 　　　　　Ａ(残高)　　Ｂ(利率)
の記述があったので、てっきり利率はB列かと思ったんですが・・・

セルの指定は実際に合わせて修正してください。
計算式自体は、前記の

> 1.直前の行の残高がマイナスだった場合、5%になる
> 4''.一度でも15%になっていたら、15%になる
> 2.直前の行の残高が10万から100万の間だったら18%になる
> 3.直前の行の残高が100万以上なら15%になる
を順番にIFで記述してるだけです。

> 実際のソフトを見てもらうわけにはいかないでしょうか？
「実際のソフト」って「計算式」のことですか？

例えば、「セルXXにこういう値、セルYYにこういう値があって、
セルYYに「=～」の様な計算式を入力した。」ぐらいなら
見れるかもしれませんけど・・・

補足

何度もすみません。
利率の入っている実際のセルはH列なので
教えていただいたB→Hに変更して記述はしました。
ただ、日付を入れる欄があって、そこを別に参照しているセルがあるらしく
循環参照と出てしまいます。
なので複雑すぎてもうわからなくなりました(汗)

計算式の入ったエクセルをそのまま見てもらえたらわかるのかな
と思って、見てくださいとお願いしてしまったのですが
すべての計算式を書いたのではとても見られないと思います。

無理なことを言ってすみませんでした。
大変勉強になりました。
もう少し頑張ってみます。
本当にありがとうございました。

回答No.2

dsuekichi

> 一度15%になったら、それ以降は5%か15%のどちらかにしたいのです。
・・・「一度でも15%だったら、２度と18%にはならない」って事ですか・・・

つまり条件は、

1.直前の行の残高がマイナスだった場合、5%になる
4''.一度でも15%になっていたら、15%になる
2.直前の行の残高が10万から100万の間だったら18%になる
3.直前の行の残高が100万以上なら15%になる
ですか・・・

> どこかで「15%になったこと」を検索させるしかないでしょうか…
そうですね・・・・

「エクセルの計算式」って、ExcelVBAではなく、数式バーで入力する各セルの計算式のことですよね？

でしたら、「COUNTIF」とかを使って、例えばB2セルなら
---------------------------------------------------
=IF(A1<0,0.05,IF(COUNTIF(B$1:B1,0.15)>0,0.15,IF(A1>=1000000,0.15,0.18)))
---------------------------------------------------
とか・・・

補足

ありがとうございます。
やってみたのですが、0%になってしまいます。
実際にはセルがBではないので、その書き方がおかしいのでしょうか…

あの、実際のソフトを見てもらうわけにはいかないでしょうか？

回答No.1

dsuekichi

条件を

1.直前の行の残高がマイナスだった場合、5%になる
4'.直前の行の利率が15%の場合、15%になる
2.直前の行の残高が10万から100万の間だったら18%になる
3.直前の行の残高が100万以上なら15%になる

と置き換えれば、IFを階層化して表せるのでは・・・

＃ところで、残高が０円から１０万だったら利率はどうなるのでしょう？
＃１０万単位にしか成らないって事は無いでしょうし・・・

補足

早速のご回答ありがとうございます。

「直前の」とは限らないのです。
一度15%になったら、それ以降は5%か15%のどちらかにしたいのです。

どこかで「15%になったこと」を検索させるしかないでしょうか…

それからすみません。
0から10万の間でも18%です。
ただし、一度15%になったら15%としたいです。
よろしくお願いします。