跳到主要内容

通过公式拆分金额

1. 使用场景

很多时候,财务部门要将填报的金额按照…千、百、十、元、角、分的格式进行拆分,以便打印模板内按下图格式打印。

2. 实现功能

2.1 配置表单页面

2.2 配置字段公式

注意根据实际需求修改字段。

(1)百万

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":49,"sticky":null},"to":{"line":0,"ch":53,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":70,"sticky":null},"to":{"line":0,"ch":74,"sticky":null},"value":"numberField_lc04ogys","invalid":false}],"isCmData":true}

(2)十万

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":48,"sticky":null},"to":{"line":0,"ch":52,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":72,"sticky":null},"to":{"line":0,"ch":76,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":88,"sticky":null},"to":{"line":0,"ch":92,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":94,"sticky":null},"to":{"line":0,"ch":98,"sticky":null},"value":"numberField_ksidlu5w","invalid":false}],"isCmData":true}

(3)万

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":47,"sticky":null},"to":{"line":0,"ch":51,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":70,"sticky":null},"to":{"line":0,"ch":74,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":86,"sticky":null},"to":{"line":0,"ch":90,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":92,"sticky":null},"to":{"line":0,"ch":96,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":107,"sticky":null},"to":{"line":0,"ch":111,"sticky":null},"value":"numberField_ksidlu5y","invalid":false}],"isCmData":true}

(4)千

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":46,"sticky":null},"to":{"line":0,"ch":50,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":68,"sticky":null},"to":{"line":0,"ch":72,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":84,"sticky":null},"to":{"line":0,"ch":88,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":90,"sticky":null},"to":{"line":0,"ch":94,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":105,"sticky":null},"to":{"line":0,"ch":109,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":119,"sticky":null},"to":{"line":0,"ch":122,"sticky":null},"value":"numberField_ksidlu5z","invalid":false}],"isCmData":true}

(5)百

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":45,"sticky":null},"to":{"line":0,"ch":49,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":66,"sticky":null},"to":{"line":0,"ch":70,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":82,"sticky":null},"to":{"line":0,"ch":86,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":88,"sticky":null},"to":{"line":0,"ch":92,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":103,"sticky":null},"to":{"line":0,"ch":107,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":117,"sticky":null},"to":{"line":0,"ch":120,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":129,"sticky":null},"to":{"line":0,"ch":133,"sticky":null},"value":"numberField_ksidlu5x","invalid":false}],"isCmData":true}

(6)十

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":32,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":44,"sticky":null},"to":{"line":0,"ch":48,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":64,"sticky":null},"to":{"line":0,"ch":68,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":80,"sticky":null},"to":{"line":0,"ch":84,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":86,"sticky":null},"to":{"line":0,"ch":90,"sticky":null},"value":"numberField_ksidlu5w","invalid":false},{"from":{"line":0,"ch":101,"sticky":null},"to":{"line":0,"ch":105,"sticky":null},"value":"numberField_ksidlu5y","invalid":false},{"from":{"line":0,"ch":115,"sticky":null},"to":{"line":0,"ch":118,"sticky":null},"value":"numberField_ksidlu5z","invalid":false},{"from":{"line":0,"ch":127,"sticky":null},"to":{"line":0,"ch":131,"sticky":null},"value":"numberField_ksidlu5x","invalid":false},{"from":{"line":0,"ch":139,"sticky":null},"to":{"line":0,"ch":142,"sticky":null},"value":"numberField_ksidlu60","invalid":false}],"isCmData":true}

(7)元

{"text":"IF(GT(​金额​,9999999),9,VALUE(LEFT(RIGHT(TEXT(INT(​金额​)),2),1)))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":48,"sticky":null},"to":{"line":0,"ch":52,"sticky":null},"value":"numberField_lc04ogys","invalid":false}],"isCmData":true}

(8)角

{"text":"IF(GT(​金额​,9999999),9,LEFT(ARRAYGET(SPLIT(TEXT(​金额​),\".\"),2),1))","marks":[{"from":{"line":0,"ch":6,"sticky":null},"to":{"line":0,"ch":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":47,"sticky":null},"to":{"line":0,"ch":51,"sticky":null},"value":"numberField_lc04ogys","invalid":false}],"isCmData":true}

(9)分

{"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":10,"sticky":null},"value":"numberField_lc04ogys","invalid":false},{"from":{"line":0,"ch":53,"sticky":null},"to":{"line":0,"ch":57,"sticky":null},"value":"numberField_lc04ogys","invalid":false}],"isCmData":true}

3. 实现效果

4. 在线试玩

Copyright © 2024钉钉(中国)信息技术有限公司和/或其关联公司浙ICP备18037475号-4