2024年4月2日发(作者：)

excel金额小写转大写最简单的公式

在日常工作和生活中，我们经常会用到Excel来进行各种数据操

作。而在Excel中，金额是一个非常重要的数据类型，尤其是在财务、

会计等领域，金额的正确性至关重要。

对于金额的表达方法，我们通常使用数字（即小写金额）来表示，

如1000元、90000元等。但在有些特殊场合，我们需要使用中文表示

方法（即大写金额），如壹仟元整、玖万元整等。

那么如何将Excel中的小写金额转换为大写金额呢？其实，这个

问题非常简单，只需要使用一个公式即可。

下面是将Excel中的小写金额转化为大写金额的最简单公式：

=TEXT(INDEX({"零","壹","贰","叁","肆","伍","陆","柒","捌

","玖

"},MATCH(LEFT(A1,1),{"0","1","2","3","4","5","6","7","8","9"}

,0)+1),)&IF(MID(A1,2,1)="0","",TEXT(INDEX({"拾","佰","仟

"},LEN(A1)-1-1+1-

MATCH(MID(A1,2,1),{"0","1","2","3","4","5","6","7","8","9"},0

)),))&IF(MID(A1,3,1)="0",IF(RIGHT(A1,1)="0","","零

"),TEXT(INDEX({"零","壹","贰","叁","肆","伍","陆","柒","捌","

玖

"},MATCH(MID(A1,3,1),{"0","1","2","3","4","5","6","7","8","9"

},0)+1),"零"))&IF(MID(A1,4,1)="0",IF(RIGHT(A1,2)="00","","零

"),TEXT(INDEX({"零","壹","贰","叁","肆","伍","陆","柒","捌","

玖

"},MATCH(MID(A1,4,1),{"0","1","2","3","4","5","6","7","8","9"

},0)+1),))&IF(RIGHT(A1,3)="000","","零")&IF((LEN(A1)-

LEN(SUBSTITUTE(A1,".","")))=1,"元",TEXT(INDEX({"拾","佰","仟

","万"},(LEN(A1)-1)/4+1),"元"))&IF(RIGHT(A1,2)="00","整

",IF(RIGHT(A1,1)="0",TEXT(INDEX({"零","壹","贰","叁","肆","伍

","陆","柒","捌","玖

"},MATCH(RIGHT(A1,1),{"0","1","2","3","4","5","6","7","8","9"

},0)+1))&"角",TEXT(INDEX({"零","壹","贰","叁","肆","伍","陆

","柒","捌","玖"},MATCH(MID(A1,LEN(A1)-

1,1),{"0","1","2","3","4","5","6","7","8","9"},0)+1))&"角

"))&IF(RIGHT(A1,2)="0","",TEXT(INDEX({"零","壹","贰","叁","肆

","伍","陆","柒","捌","玖

"},MATCH(RIGHT(A1,2),{"01","02","03","04","05","06","07","08"

,"09","10","11","12"},0)),)&"分"))

使用这个公式之前，需要将Excel的格式改为文本格式（在单元

格中右击→设置单元格格式→选择“文本”，并勾选“使用千位分隔

符”）。

其中，A1表示包含小写金额的单元格，将公式输入到其他单元格

中即可。

这个公式可能看起来有些复杂，但它其实是根据中文大写金额的

表达规则来编写的，实现了将小写金额转换为大写金额的功能。

总的来说，使用Excel金额小写转大写最简单的公式其实是非常

方便的，能够大大提高工作效率，让我们的工作更加便捷。