如何将发票金额拆分
很多时候，财务部门要将填报的金额按照…千、百、十、元、角、分的格式进行拆分，以便打印模板内按下图格式打印。
那么我们如何将一个数字字段填报的数值逐个拆开呢？
1. 设计思路
1.表单内添加一个用来录入原始数据的数值组件。
2.在这个数值后面按照顺序添加所需要位数的数值组件。
3.分别对每一个数值组件编写不同的函数。
4.大于百万数值将拆分成 9999999
2. 函数编写
- 百万 ：
下述代码可直接复制到公式编辑器内，注意替换录入金额字段。
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,1000000),LT(录入金额,10000000)),LEFT(录入金额,1),0))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":53,"sticky":null},"to":{"line":0,"ch":59,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":76,"sticky":null},"to":{"line":0,"ch":82,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}
- 十万 ：
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,100000),LE(录入金额,10000)),RIGHT(LEFT(录入金额,2),1),INT((录入金额-(百万*1000000))/100000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":52,"sticky":null},"to":{"line":0,"ch":58,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":78,"sticky":null},"to":{"line":0,"ch":84,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":96,"sticky":null},"to":{"line":0,"ch":102,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":104,"sticky":null},"to":{"line":0,"ch":108,"sticky":null},"value":"numberField_ksidlu5w","invalid":false}],"isCmData":true}
- 万 ：
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,10000),LE(录入金额,1000)),RIGHT(LEFT(录入金额,3),1),INT((录入金额-(百万*1000000)-(十万*100000))/10000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":51,"sticky":null},"to":{"line":0,"ch":57,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":76,"sticky":null},"to":{"line":0,"ch":82,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":94,"sticky":null},"to":{"line":0,"ch":100,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":102,"sticky":null},"to":{"line":0,"ch":106,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":117,"sticky":null},"to":{"line":0,"ch":121,"sticky":null},"value":"numberField_ksidlu5y","invalid":false}],"isCmData":true}
- 千：
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,1000),LE(录入金额,100)),RIGHT(LEFT(录入金额,4),1),INT((录入金额-(百万*1000000)-(十万*100000)-(万*10000))/1000)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":50,"sticky":null},"to":{"line":0,"ch":56,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":74,"sticky":null},"to":{"line":0,"ch":80,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":92,"sticky":null},"to":{"line":0,"ch":98,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":100,"sticky":null},"to":{"line":0,"ch":104,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":115,"sticky":null},"to":{"line":0,"ch":119,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":129,"sticky":null},"to":{"line":0,"ch":132,"sticky":null},"value":"numberField_ksidlu5z","invalid":false}],"isCmData":true}
- 百 ：
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,100),LE(录入金额,10)),RIGHT(LEFT(录入金额,5),1),INT((录入金额-(百万*1000000)-(十万*100000)-(万*10000)-( 千*1000))/100)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":49,"sticky":null},"to":{"line":0,"ch":55,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":72,"sticky":null},"to":{"line":0,"ch":78,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":90,"sticky":null},"to":{"line":0,"ch":96,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":98,"sticky":null},"to":{"line":0,"ch":102,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":113,"sticky":null},"to":{"line":0,"ch":117,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":127,"sticky":null},"to":{"line":0,"ch":130,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":139,"sticky":null},"to":{"line":0,"ch":143,"sticky":null},"value":"numberField_ksidlu5x","invalid":false}],"isCmData":true}
- 十 ：
{"text":"IF(GT(录入金额,9999999),9,IF(AND(GE(录入金额,10),LE(录入金额,1)),RIGHT(LEFT(录入金额,6),1),INT((录入金额-(百万*1000000)-(十万*100000)-(万*10000)-( 千*1000)-(百*100))/10)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":48,"sticky":null},"to":{"line":0,"ch":54,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":70,"sticky":null},"to":{"line":0,"ch":76,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":88,"sticky":null},"to":{"line":0,"ch":94,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":96,"sticky":null},"to":{"line":0,"ch":100,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":111,"sticky":null},"to":{"line":0,"ch":115,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":125,"sticky":null},"to":{"line":0,"ch":128,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":137,"sticky":null},"to":{"line":0,"ch":141,"sticky":null},"value":"numberField_ksidlu5x","invalid":false},{"from":{"line":0,"ch":149,"sticky":null},"to":{"line":0,"ch":152,"sticky":null},"value":"numberField_ksidlu60","invalid":false}],"isCmData":true}
- 元 ：
{"text":"IF(GT(录入金额,9999999),9,RIGHT(INT(录入金额),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":34,"sticky":null},"to":{"line":0,"ch":40,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}
- 角 ：
{"text":"IF(GT(录入金额,9999999),9,LEFT(ARRAYGET(SPLIT(TEXT(录入金额),\".\"),2),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":49,"sticky":null},"to":{"line":0,"ch":55,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}
- 分 ：
{"text":"IF(GT(录入金额,9999999),9,RIGHT(LEFT(ARRAYGET(SPLIT(TEXT(录入金额),\".\"),2),2),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":12,"sticky":null},"value":"numberField_ksidlu5v","invalid":false},{"from":{"line":0,"ch":55,"sticky":null},"to":{"line":0,"ch":61,"sticky":null},"value":"numberField_ksidlu5v","invalid":false}],"isCmData":true}
3. 效果演示
点击表单，直接查看拆分效果：点击查看
宜搭为了更好的优化宜搭使用手册内容和质量，占用您3-5分钟时间，辛苦填写一下文档反馈问卷。文档反馈问卷是匿名提交，同时问卷信息仅用于宜搭文档体验反馈收集，感谢您对宜搭的支持！
--------------------获取宜搭最新信息，欢迎关注我们--------------------
本文档对您是否有帮助？