下面的Excel课程清单表格,当我们填写数量和单价之后,使用一个公式,实现灰色区域自动计算。 ![](http://image109.360doc.com/DownloadImg/2022/07/1022/248326875_2_20220710102416285.jpeg) =LEFT(RIGHT(" ¥"&ROUND($C6*$E6,2)*100,COLUMN($L$6)-COLUMN(E6)))=LEFT(RIGHT(" ¥"&ROUND(SUMPRODUCT($C$6:$C$12,$E$6:$E$12),2)*100,COLUMN($L$6)-COLUMN(E13)))="¥ "&SUBSTITUTE(SUBSTITUTE(IF(-RMB(SUMPRODUCT($C6:$C12,$E6:$E12)),TEXT(SUMPRODUCT($C6:$C12,$E6:$E12),";负")&TEXT(INT(ABS(SUMPRODUCT($C6:$C12,$E6:$E12))+0.5%),"[dbnum2]g/通用格式元;;")&TEXT(RIGHT(RMB(SUMPRODUCT($C6:$C12,$E6:$E12),2),2),"[dbnum2]0角0分;;整"),"-"),"零角",IF(SUMPRODUCT($C6:$C12,$E6:$E12)^2<1,,"零")),"零分","")上面的3个函数公式都比较复杂,伙伴们可以自动套用表格公式使用即可。
|