EXCEL中数字自动变成大写金额的4种方法

骚

的确，英文版下不行

牧源膳品

客气了(≧∇≦)

闲鱼

试过以下方法在英文版中可以：=IF(B2=0,"零元",IF(B2<0,TEXT(INT(ABS(B2)),"负[DBNum2][$-zh-CN]General")&"元"&IF((INT(B2*10)-INT(B2)*10)=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[DBNum2][$-zh-CN]General")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",TEXT(INT(B2*10)-INT(B2*10),"[DBNum2][$-zh-CN]General")&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2][$-zh-CN]General")&"分"),TEXT(INT(B2),"[DBNum2][$-zh-CN]General")&"元"&IF(INT(B2*10)-INT(B2)*10=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[DBNum2][$-zh-CN]General")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",TEXT(INT(B2*10)-INT(B2*10),"[DBNum2][$-zh-CN]General")&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2][$-zh-CN]General")&"分")))

落花本无意

谢谢，但我输入150.55结果是【150元5角05分】这样的，不是壹佰伍拾元伍角伍分。可能还是缺少一些中文语言包的特性。

浮生一日

试试在 Special 画面中的选项中先选择：Chinese ，然后就会出现相关转换中文大写的选项，之后可能才可以使用对应的函数。

笑珺尧然

我使用的excel2013英文版版，选择格式为special的对应中文大小写选项后，公式中的[DBNum2][$-zh-CN]General要改成[DBNum2][$-804]General就可以了，最后显示的是【壹佰伍拾元伍角零伍分】，基本正确了，除了最后多了个零，非常感谢！

士超

差不了，我的是 Excel 2016 ，不同版本可能稍有不同

空虚

最后我把公式改成这样，角之后分之前不会多一个零了。=IF(B2=0,"零元",IF(B2<0,TEXT(INT(ABS(B2)),"负[DBNum2][$-804]General")&"元"&IF((INT(B2*10)-INT(B2)*10)=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[DBNum2][$-804]General")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",TEXT(INT(B2*10)-INT(B2*10),"[DBNum2][$-804]General")&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2][$-804]General")&"分"),TEXT(INT(B2),"[DBNum2][$-804]General")&"元"&IF(INT(B2*10)-INT(B2)*10=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[DBNum2][$-804]General")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",IF(INT(B2*10)-INT(B2)*10=0,"零"&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2][$-804]General")&"分",TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2][$-804]General")&"分"))))

广东果果

又发现负数会有问题，我把判断正负的if公式的结构改了下，公式也变得短了一点，最后如下，希望没啥其他问题了。=IF(B2=0,"零元",IF(B2<0,"负","")&TEXT(INT(ABS(B2)),"[DBNum2][$-804]General")&"元"&IF(INT(ABS(B2)*10)-INT(ABS(B2))*10=0,"",TEXT(INT(ABS(B2)*10)-INT(ABS(B2))*10,"[DBNum2][$-804]General")&"角")&IF((INT(ABS(B2)*100)-INT(ABS(B2)*10)*10)=0,"整",IF(INT(ABS(B2)*10)-INT(ABS(B2))*10=0,"零"&TEXT(INT(ABS(B2)*100)-INT(ABS(B2)*10)*10,"[DBNum2][$-804]General")&"分",TEXT(INT(ABS(B2)*100)-INT(ABS(B2)*10)*10,"[DBNum2][$-804]General")&"分")))

尘世浮华

好象是作者在角和分之间漏写了一个判断，我改成如下这样就不会在角分之间多一个0了，=IF(B2=0,"零元",IF(B2<0,"负","")&TEXT(INT(ABS(B2)),"[DBNum2][$-804]General")&"元"&IF(INT(ABS(B2)*10)-INT(ABS(B2))*10=0,"",TEXT(INT(ABS(B2)*10)-INT(ABS(B2))*10,"[DBNum2][$-804]General")&"角")&IF((INT(ABS(B2)*100)-INT(ABS(B2)*10)*10)=0,"整",IF(INT(ABS(B2)*10)-INT(ABS(B2))*10=0,"零"&TEXT(INT(ABS(B2)*100)-INT(ABS(B2)*10)*10,"[DBNum2][$-804]General")&"分",TEXT(INT(ABS(B2)*100)-INT(ABS(B2)*10)*10,"[DBNum2][$-804]General")&"分")))