因为助理的一个低级错误,我悄悄在 Excel 后台做了一个“黑盒插件”

一、事情的起因

前几天在审一份出口合同时，我抓出了一个很低级的错误：总价小写的数字，跟下面的英文大写（Say Total...）对不上。

这种粗心在外贸行业太常见了。但只要做过信用证（L/C）交单的人都知道，这后面是个多大的坑——错一个字母、多一个空格，银行就能算你一个不符点，直接扣掉几十美金。

我把合同退给外贸助理，跟她说：“去研究一下，能不能让 Excel 根据小写自动转换大写，把这个漏洞彻底堵死。”

作为管理人员，我习惯让员工自己去折腾，把事情研究明白。但我也清楚，在 Excel 纯函数里要完美搞定十几和几十的切换、美分四舍五入、1美金的单复数，难度基本是地狱级的。

我挺担心她们研究几天搞不出来最后放弃了。所以，我自己先在后台琢磨出了一套稳妥的方法放在这，静静等她们来找我。

今天把这套不用写代码、不用开 VBA 宏、直接用纯函数封装好的“大写插件”发出来，外贸圈和财务圈的朋友如果用得着，拿去直接用。

二、核心方法：把复杂留给后台（辅助页）

为了不破坏你们现有的合同打印排版，我们要用一种“前后台分离”的思维。

在你的合同 Excel 里，新建一个工作表，命名为 【辅助页】。在 B1 到 B6 单元格中，依次复制进去下面这 6 行公式。这几行公式负责在后台把数字强行切块：

B1（锁定十万位）：

=IF($B$7<100000,"",CHOOSE(INT($B$7/100000),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE")&" HUNDRED")

B2（万位与千位自适应）：

=IF(INT(MOD($B$7,100000)/1000)=0,IF($B$7>=100000," THOUSAND",""),IF(MOD(INT(MOD($B$7,100000)/1000),100)<10,CHOOSE(MOD(INT(MOD($B$7,100000)/1000),10),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE"),IF(MOD(INT(MOD($B$7,100000)/1000),100)<20,CHOOSE(MOD(INT(MOD($B$7,100000)/1000),100)-9,"TEN","ELEVEN","TWELVE","THIRTEEN","FOURTEEN","FIFTEEN","SIXTEEN","SEVENTEEN","EIGHTEEN","NINETEEN"),CHOOSE(INT(MOD(INT(MOD($B$7,100000)/1000),100)/10)+1,"","","TWENTY","THIRTY","FORTY","FIFTY","SIXTY","SEVENTY","EIGHTY","NINETY")&IF(MOD(INT(MOD($B$7,100000)/1000),10)>0,"-"&CHOOSE(MOD(INT(MOD($B$7,100000)/1000),10),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE"),"")))&" THOUSAND")

B3（锁定百位）：

=IF(INT(MOD($B$7,1000)/100)=0,"",CHOOSE(INT(MOD($B$7,1000)/100),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE")&" HUNDRED")

B4（连接词逻辑判断）：

=IF(AND($B$7>=100,MOD(INT($B$7),100)>0),"AND","")

B5（锁定个位与十位）：

=IF(MOD(INT($B$7),100)=0,"",IF(MOD(INT($B$7),100)<10,CHOOSE(MOD(INT($B$7),100),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE"),IF(MOD(INT($B$7),100)<20,CHOOSE(MOD(INT($B$7),100)-9,"TEN","ELEVEN","TWELVE","THIRTEEN","FOURTEEN","FIFTEEN","SIXTEEN","SEVENTEEN","EIGHTEEN","NINETEEN"),CHOOSE(INT(MOD(INT($B$7),100)/10)+1,"","","TWENTY","THIRTY","FORTY","FIFTY","SIXTY","SEVENTY","EIGHTY","NINETY")&IF(MOD(INT($B$7),10)>0,"-"&CHOOSE(MOD(INT($B$7),10),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE"),""))))

B6（美分小数，自带四舍五入防误差）：

=IF(ROUND(MOD($B$7,1),2)=0,"",IF(ROUND(MOD($B$7,1),2)<0.1,CHOOSE(ROUND(MOD($B$7,1),2)*100,"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE")&" CENT",IF(ROUND(MOD($B$7,1),2)<0.2,CHOOSE(ROUND(MOD($B$7,1),2)*100-9,"TEN","ELEVEN","TWELVE","THIRTEEN","FOURTEEN","FIFTEEN","SIXTEEN","SEVENTEEN","EIGHTEEN","NINETEEN")&" CENTS",CHOOSE(INT(ROUND(MOD($B$7,1),2)*10)+1,"","","TWENTY","THIRTY","FORTY","FIFTY","SIXTY","SEVENTY","EIGHTY","NINETY")&IF(MOD(ROUND(MOD($B$7,1),2)*100,10)>0,"-"&CHOOSE(MOD(ROUND(MOD($B$7,1),2)*100,10),"ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE"),"")&" CENTS")))

📌 关键一步：B7 单元格什么都不写，留空。它就是咱们的“输入接线口”。

B8（最终总装配输出）：复制下面这行长公式，它自带 TRIM 函数，会自动吸附掉所有因为跳过零头产生的尴尬双空格。

=TRIM("SAY TOTAL US " & IF(INT($B$7)=0,"",IF(INT($B$7)=1,"ONE DOLLAR ","DOLLARS ")) & IF(INT($B$7)=1,"",TEXT(B1,"") & " " & TEXT(B2,"") & " " & TEXT(B3,"") & " " & IF(ROUND(MOD($B$7,1),2)>0,IF(AND(B1="",B2="",B3=""),""," "),TEXT(B4,"")) & " " & TEXT(B5,"")) & IF(AND(INT($B$7)>0,ROUND(MOD($B$7,1),2)>0)," AND ","") & TEXT(B6,"") & " ONLY.")