基于LAMBDA()函数的SpellNumber数字转英文金额函数优化及最佳实践问询

原自定义函数实现 #

以下是使用LAMBDA()实现数字转英文金额的原函数：

=LAMBDA(num,LET(x,ABS(num),wd,TEXT(INT(x),"000000000000000"),dec,ROUND(x-INT(x),2)*100,c,CHOOSECOLS,
digits,{"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
Tenths,{"Ten","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety","Hundred"},
tr,--MID(wd,1,3),bln,--MID(wd,4,3),mln,--MID(wd,7,3),Hazar,--MID(wd,10,3),Shotok,--MID(wd,13,1),Dosok,--MID(wd,14,2),
SPELL,LAMBDA(val,z,IFERROR(IF(val<20,c(digits,val)&" "&z&" ",c(Tenths,LEFT(val,1))&IFERROR(" "&c(digits,RIGHT(val,1)),"")&" "&z&" "),"")),
SPELL3,LAMBDA(vl,abb,IF(vl>99,SPELL(--LEFT(vl,1),"Hundred")&IF(--RIGHT(vl,2)=0,abb,SPELL(--RIGHT(vl,2), abb)),SPELL(vl,abb))),
dlr,SPELL3(tr,"Trillion")&SPELL3(bln,"Billion")&SPELL3(mln,"Million")&SPELL3(Hazar,"Thousand")&SPELL(Shotok,"Hundred")&SPELL(Dosok,""),
Cnt,IF(dec>1,SPELL(dec,"Cents Only."),SPELL(dec,"Cent Only.")),
TRIM(IF(AND(--wd>0,dec>0),dlr&" Dollar"&IFS(x>1,"s")&" And "&Cnt,IF(dlr="",Cnt,dlr&" Dollar"&IFS(x>1,"s")&" Only.")))))(B2)

原函数效果截图：

优化简化后的函数 #

针对原函数的命名不规范、拼写错误、冗余逻辑等问题，优化后的函数如下：

=LAMBDA(num,LET(
    originalNum, num,
    x, ABS(num),
    intPart, INT(x),
    decPart, ROUND((x - intPart)*100, 0),
    // 标准化数字分组（15位补零）
    groupedInt, TEXT(intPart, "000000000000000"),
    // 数字拼写映射表（修正Fourty为Forty）
    ones, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
    tens, {"Ten","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},
    // 拆分整数部分为万亿、十亿、百万、千、百、十位个位
    trillion, --MID(groupedInt,1,3),
    billion, --MID(groupedInt,4,3),
    million, --MID(groupedInt,7,3),
    thousand, --MID(groupedInt,10,3),
    hundred, --MID(groupedInt,13,1),
    tensOnes, --MID(groupedInt,14,2),
    // 单个/两位数拼写逻辑
    spellSmall, LAMBDA(val, suffix, IF(val=0,"",IF(val<20,INDEX(ones,val)&" "&suffix&" ",INDEX(tens,LEFT(val,1))&IF(RIGHT(val,1)<>"0"," "&INDEX(ones,RIGHT(val,1)),"")&" "&suffix&" "))),
    // 三位数拼写逻辑
    spellThree, LAMBDA(val, suffix, IF(val=0,"",IF(val>99,spellSmall(LEFT(val,1),"Hundred")&spellSmall(RIGHT(val,2),suffix),spellSmall(val,suffix)))),
    // 拼接整数部分的英文
    dollarText, TRIM(spellThree(trillion,"Trillion")&spellThree(billion,"Billion")&spellThree(million,"Million")&spellThree(thousand,"Thousand")&spellSmall(hundred,"Hundred")&spellSmall(tensOnes,"")),
    // 处理小数部分
    centText, IF(decPart=0,"",IF(decPart=1,spellSmall(decPart,"Cent Only."),spellSmall(decPart,"Cents Only."))),
    // 组合最终结果
    result, TRIM(
        IF(originalNum<0,"Negative ","")&
        IF(intPart=0,centText,
            IF(decPart=0,dollarText&" Dollar"&IF(intPart>1,"s","")&" Only.",
                dollarText&" Dollar"&IF(intPart>1,"s","")&" And "&centText
            )
        )
    ),
    // 处理零值特殊情况
    IF(originalNum=0,"Zero Dollars and Zero Cents Only.",result)
))

优化点说明 #

• 修正拼写错误：将Fourty改为标准拼写Forty
• 规范变量命名：用trillion/billion/thousand等通用命名替代Hazar/Shotok等非通用缩写
• 简化逻辑：移除冗余的IFERROR（通过判断val=0避免索引错误），用INDEX替代CHOOSECOLS更直观
• 增强边界处理：新增零值、负数的特殊场景处理
• 提升可读性：添加注释拆分逻辑块，分层清晰

最佳实践建议 #

• 规范命名与注释：使用英文通用术语命名变量，关键逻辑添加注释，方便后续维护和他人理解
• 覆盖全场景测试：测试零值、负数、纯小数、大额整数（如万亿级）、小数位为0或1等场景，确保函数鲁棒性
• 模块化拆分：如果需要扩展功能（如支持其他币种），可将数字拼写核心逻辑拆分为独立的LAMBDA函数，复用性更强
• 遵循金额拼写规范：严格按照英文金额的官方拼写规则（如"Forty"而非"Fourty"，大于1时加复数s）
• 性能优化：对于超大规模数据批量转换，可将函数定义为Excel自定义函数（通过名称管理器），减少重复计算开销

内容的提问来源于stack exchange，提问作者Harun24hr