如何避免浮点运算带来的金额大写转换误差？

最近有一个问题真把我折腾了好久？
什么问题呢？
就是金额在网表的大写转换问题：

之前有热心的魔粉列出了这样一个公式：

SUBSTITUTE(SUBSTITUTE(MFCNUM(INT($MF(金额)$))&"元"&MFCNUM(INT(ROUND($MF(金额)$,2)*10)-INT(ROUND($MF(金额)$,2))*10)&"角"&MFCNUM(INT(ROUND($MF(金额)$,2)*100)-INT(ROUND($MF(金额)$,2)*10)*10)&"分","零角零分","整"),"零分","整")


但是我在套用的时候不巧发现了一个很有意思的问题：
首先看我用了的数据：
我的金额是：4999419.6
结果根据上述公式转换成：贰拾玖万玖仟玖佰肆拾壹元陆角-壹分
我连续找了帖子上的几个大写的公式，结果都是这个结果。我郁闷了。
我换了别的数字，确再难以出现尾巴上有一个“-壹分”的现象，试过了几百例数据，碰到几个出现这个问题。

最后我想到，这一定是浮点运算带来的问题，我用了ROUND/ROUNDUP/ROUNDDOWN/CEILING/FLOOR等函数来取舍都没有解决这个问题，真要打算提工单时，我想到了还有下面这个尝试。

请大家看我的公式，我即将贴在下面，如果您觉得有用，就转过去。

我的优化版的公式：


SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MFCNUM(INT($MF(金额)$))&"元"&MFCNUM(INT(EVEN(INT(($MF(金额)$-INT($MF(金额)$))*1000))/100))&"角"&MFCNUM(ROUND(INT(EVEN(INT(($MF(金额)$-INT($MF(金额)$))*1000))-(INT(EVEN(INT(($MF(金额)$-INT($MF(金额)$))*1000))/100)*100))/10,0))&"分","零角零分","整"),"零分","整"),"零角","零")


根据这个公式，最后，这个4999419.6的大写结果为：贰拾玖万玖仟玖佰肆拾壹元陆角整

终于正确了！

特意写一个心得分享给大家！

为了出现十万十亿等正常显示

进一步优化下：


SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(MFCNUM($MF(金额)$))="拾","壹","")&MFCNUM(INT($MF(

金额)$))&"元"&MFCNUM(INT(EVEN(INT(($MF(金额)$-INT($MF(金额)$))*1000))/100))&"角"&MFCNUM

(ROUND(INT(EVEN(INT(($MF(金额)$-INT($MF(金额)$))*1000))-(INT(EVEN(INT(($MF(金额)$-INT($MF(

金额)$))*1000))/100)*100))/10,0))&"分","零角零分","整"),"零分","整"),"零角","零")

楼主能否总结一下思路和原理？

不断优化后的公式。

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(MFCNUM(ROUND($MF(金额)$,2)))="拾","壹","")&MFCNUM(INT($MF(金额)$))&"元"&MFCNUM(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT(ROUND($MF(金额)$,2)))*1000))/100))&"角"&MFCNUM(ROUND(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT(ROUND($MF(金额)$,2)))*1000))-(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT($MF(金额)$))*1000))/100)*100))/10,0))&"分","零角零分","整"),"零分","整"),"零角","零")


思路，就是先将小数部分扩大1000倍后取整，暂定这个数为A，然后在缩小100倍取整得到角的整数，暂定这个数为B，用ROUND(A-B*100)/10,0)取整得到分的整数。同时兼顾了分后面的情况。

原理，就是绕过小数的再次直接加减带来的误差。不知道我说清楚没有。。。。

再上一个写法：

IF(LEFT(MFE(1,MFV(SUBSTITUTE(MFCNUM(ROUND($MF(金额)$,2),2,0),".",","))))="拾","壹","")&MFE(1,MFV(SUBSTITUTE(MFCNUM(ROUND($MF(金额)$,2),2,0),".",",")))&"元"&IF(MFSIZE(MFV(SUBSTITUTE(MFCNUM(ROUND($MF(金额)$,2),2,0),".",",")))=1,"整",""&SUBSTITUTE(SUBSTITUTE(MID(MFE(2,MFV(SUBSTITUTE(MFCNUM(ROUND($MF(金额)$,2),2,0),".",","))),1,1)&"角"&MID(MFE(2,MFV(SUBSTITUTE(MFCNUM(ROUND($MF(金额)$,2),2,0),".",","))),2,1)&"分","角分","角整"),"零角","角"))

showph 发表于 2019-8-15 19:36
再上一个写法：

IF(LEFT(MFE(1,MFV(SUBSTITUTE(MFCNUM($MF(金额)$,2,0),".",","))))="拾","壹","")&MFE( ...

支持MFVAR版本的可以进一步优化，用下面这个公式。
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(MFCNUM(ROUND($MF(金额)$,2)))="拾","壹","")&MFCNUM(INT($MF(

金额)$))&"元"&MFCNUM(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT(ROUND($MF(金额)$,2)))*1000))/100))&"角"&MFCNUM

(ROUND(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT(ROUND($MF(金额)$,2)))*1000))-(INT(EVEN(INT((ROUND($MF(金额)$,2)-INT($MF(

金额)$))*1000))/100)*100))/10,0))&"分","零角零分","整"),"零分","整"),"零角","零")

IF(LEFT(MFCNUM($MF(金额)$,2,0))="拾","壹","")&MFCNUM(INT($MF(金额)$),2,0)&"元"&CHOOSE(LEN(MFCNUM($MF(金额)$,2,0))-LEN(MFCNUM(INT($MF(金额)$),2,0))+1,"整","",RIGHT(MFCNUM($MF(金额)$,2,0))&"角整",LEFT(RIGHT(MFCNUM($MF(金额)$,2,0),2))&"角"&RIGHT(MFCNUM($MF(金额)$,2,0))&"分")

showph 发表于 2019-9-30 23:06
IF(LEFT(MFCNUM($MF(金额)$,2,0))="拾","壹","")&MFCNUM(INT($MF(金额)$),2,0)&"元"&CHOOSE(LEN(MFCNUM($MF ...

基本完美，不过还是发现一点小瑕疵，比如：壹拾万零贰佰壹拾伍元贰角叁分

IF(LEFT(MFVAR("RMB",SUBSTITUTE(SUBSTITUTE(MFCNUM(INT(TEXT($MF(金额)$,"0.00")))&"元"&
MFCNUM(INT(RIGHT(TEXT($MF(金额)$,"0.00"),2)/10))&"角"&MFCNUM(INT(RIGHT(TEXT($MF(金额)$,"0.00"))))&"分","零角零分","整"),"零分","整")))="拾","壹","")&MFVAR("RMB")


支持MFVAR的用这个公式

IF(LEFT(MFCNUM($MF(金额)$))="拾","壹","")&SUBSTITUTE(SUBSTITUTE(MFCNUM(INT(TEXT($MF(金额)$,"0.00")))&"元"&
MFCNUM(INT(RIGHT(TEXT($MF(金额)$,"0.00"),2)/10))&"角"&MFCNUM(INT(RIGHT(TEXT($MF(金额)$,"0.00"))))&"分","零角零分","整"),"零分","整")