延滞金計算のユーザー定義関数で迷ってます。納期限が11/30~12/30の時端数処理でズレでます。

はじめまして、yasshi42と申します。ここを利用するのは初めてです。よろしくお願いします。
最初はVLOOKUP関数を使っていたのですが、各年毎に計算して端数切捨てしていたものを、利率が同じ間は日数を加算して利率が変わったと時に計算するように改修したいのですが未熟なのでうまくいきません。納期限が11/30~12/30はうまくいっているのですが…
↓を試したのですがだめでした。
if t3.Offset(0, 5)=t3.Offset(1, 5) Then 　'もし一つしたの利率がおなじなら
Days= Days+ t3.Offset(1, 3)　'日数を加算する。
Else
（略）
End　if
↓は2年目以降のLoop処理です。
Do Until iNen = eNen
　Set t3 = Range("利率設定").Find(What:=iNen)
　kaisibi = t3.Offset(0, 1)
　endbi = t3.Offset(0, 2)
　Days = t3.Offset(0, 3)
　rituY = t3.Offset(0, 5)
（ここに上記のIf文入れましたがだめでした。）
　　If endbi < keisanbi Then
　　Yentai2nd = Entai * rituY * Days / 365
　　YEntai = YEntai + Yentai2nd
　　End If
　　iNen = iNen + 1
　　Set t3 = Nothing
Loop

文字数制限があるみたいなのでコードの全部はのせてません。↓に載せてます。
http://arakurikaesiki.blog.fc2.com/blog-entry-69 …

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2016/11/27 16:25

こんにちは。

全部見切れてはいないのですが、こういう金利計算の基本的な扱いを間違えていると思います。もちろん、VBAを使う使わないに関わらず、気をつけなくてはならない問題があります。

一言「パソコンは、２進で動いています。したがって、小数点以下の数字には、丸め誤差が出ます。」ということです。

しかし、逆にVBAで始めてきちんとした修正ができる問題です。

コードをみると、Double型の変数があちこちにありますが、それはありないことです。2進で計算させたら、割り切れる数以外は、四則演算でほぼ狂います。もちろん、そのすべてが小数点以下を取り扱うこととは言えませんが。

Dim Yentai1st, rituY As Double　'←X
Dim endbi, kaisibi As Date

それと、VBAは、VB6のDNAを継ぐものですから、VB.Net とは違い、そのような書き方をすると、Yentai1st は、Variant になっています。

Double型の代わりに、Currency 型（または、Decimal型/CDec で変換する)を使うというのが、決まりになっています。つまり、10進で計算させることです。電卓などでは、そうした完全な補正措置が取られていますが、Excelの表面上は、ある程度の補正はしているそうですが、VBAにおいては、補正も保護もなく、数字が丸裸にされてしまいます。

だから、Excelの表計算自体も、信用できません。例えば、小数点をドラッグコピーした場合に、万が一にも、表に隠れた小さな数値が混じっていれば、それだけで、実際にズレが生じてしまいますので、小数点の扱いは、ドラッグコピーなどは極めて気をつけなくてはなりませし、ワークシートの関数の二次計算も、そのままだし概ね不可だと思って間違いありません。一旦、Fixed/VBAでは、Fix 関数を使うなりしないと、正確には出てきません。マイクロソフト側では、Round関数を勧めています。なお、今回は使われていませんが、INT関数は、間違いの元ですから、気をつけたほうがよいです。

それ以外の問題で、問題箇所を探すというと、全部解体工事になってしまうと思います。

この回答へのお礼

WindFallerさんこんばんわ
丁寧な解説ありがとうございます。早速Double型の代わりに、Currency 型を使ってみました。ですが、結果は変わりませんでした。
Fix関数は作成過程で使ってみましたが、いろいろ検索しているとバグがあると書いてあったのでやめました。RoundDown関数を使っていす。（法律でも1円未満の端数は切捨てとなっています。）
延滞金の計算はX:納期限の翌日から１ヶ月を経過するまでとY：納期限の翌日から１ヶ月を経過した日から納付の日までの金額で、Yの計算をしています。11月30日から12月30日が納期限だと1月1日から１ヶ月を経過した日までの金額を差し引きしなければなりません。そこがうまくいっていないみたいです。その端数処理ですが、各年ごとに切捨てしてる自治体と、利率が同じ間は日数を通算しそこで端数処理している自治体があって（デバック用に計算例を掲載している自治体の計算例ハードコピーとりまくりました）、今まで各年ごとに切捨てでやっていたのですが、後者に変更しようとしている最中です。色々やってみます。

お礼日時：2016/11/27 19:57

No.1 回答者： neKo_deux 回答日時： 2016/11/27 11:49

> 納期限が11/30~12/30の時端数処理でズレでます。

手計算ではきちんと計算できるけど、ユーザー定義関数、VBAだと上手くいかないとかなんでしょうか？

計算過程をPrintとかでしっかり表示して、手計算と突き合わせとか。


> 文字数制限があるみたいなのでコードの全部はのせてません。↓に載せてます。

見ないですが、コードよりは計算手順なんかを日本語でしっかり書いて明確化しては。

> 　kaisibi = t3.Offset(0, 1)
> 　endbi = t3.Offset(0, 2)
> 　Days = t3.Offset(0, 3)

これもExcelのシートが無いので分かりませんが、開始日から終了日までの日数がExcelのシートに入ってたとして、VBAで経過日数しか使わないなら、開始日と終了日は取り込まずに、経過日数だけ持ってくるべきとか。

この回答へのお礼

neKo_deuxさん回答ありがとうございます。
＞見ないですが、コードよりは計算手順なんかを日本語でしっかり書いて明確化しては。
今やりたい計算の手順は「もし一つ下のセルの利率が同じ間、日数を加算し続ける」です。

＞これもExcelのシートが無いので分かりませんが、開始日から終了日までの日数が
＞Excelのシートに入ってたとして、VBAで経過日数しか使わないなら、開始日と終了＞日は取り込まずに、経過日数だけ持ってくるべきとか。

シートに↓のような表をつくってそれを参照してます。
１列目,2列目 ,３列目,4列目,5列目,6列目
YYYY ,kaisibi,endbi ,Days ,rituX ,rituY
日本語というか文章が苦手でわかりにくくてすみません。

お礼日時：2016/11/27 13:41