Excel合计金额自动转中文大写

如下表格即可实现：

 

 

 

A16...	J16
A17...	J17

 

 

J16单元格：=RMB(SUM(A16:A17))

J17单元格：=IF(NOT(ISNUMBER(FIND(".", TEXT(J16, "[DBNUM2]")))), TEXT(J16, "[DBNUM2]")  & "元整", IF(LEN(TEXT(J16, "[DBNUM2]")) - FIND(".", TEXT(J16, "[DBNUM2]")) = 1, SUBSTITUTE(TEXT(J16, "[DBNUM2]"), ".", "元")  & "角", IF(EXACT("零", LEFT(RIGHT(TEXT(J16, "[DBNUM2]"), LEN(TEXT(J16, "[DBNUM2]")) - FIND(".", TEXT(J16, "[DBNUM2]"))), 1)), SUBSTITUTE(TEXT(J16, "[DBNUM2]"), ".", "元") & "分", SUBSTITUTE(LEFT(TEXT(J16, "[DBNUM2]"), FIND(".", TEXT(J16, "[DBNUM2]"))), ".", "元") & LEFT(RIGHT(TEXT(J16, "[DBNUM2]"), LEN(TEXT(J16, "[DBNUM2]")) - FIND(".", TEXT(J16, "[DBNUM2]"))), 1) & "角" & RIGHT(RIGHT(TEXT(J16, "[DBNUM2]"), LEN(TEXT(J16, "[DBNUM2]")) - FIND(".", TEXT(J16, "[DBNUM2]"))), 1) & "分")))

 

注：J16单元格格式为“货币（两位小数）”，J16数字不超过15位

　　判断逻辑：!不含小数点 ? "元整" : (含一位小数 ? "几角" : (角为零 ? "零几分" : "几角几分"))