图一 自定义数字格式
5.在C2单元格录入以下公式:=IF(COLUMN()=3,INT($B2/C$1),INT(($B2-SUM(OFFSET($B$1,ROWS(A$1:A1),1,1,COLUMNS($B:B)-1)*OFFSET($B$1,,1,1,COLUMNS($B:B)-1)))/C$1))
录入公式后需要同时按下Ctrl+Shift+Enter三键结束,表示按照数组公式计算,否则无法产生正确结果。
6.选择单元格C1,将公式向右填充至I2,再双击填充柄,将C2:I2的公式向下填充至末尾。公式的计算结果见图二所示:
图二利用数组公式计算钞票张数
7.为了验证计算是否准确,再在J列建立一个辅助区,用于汇总所有面额与数量的乘积。在J1输入汇总,在J2输入以下公式:=SUM($C$1:$I$1*C2:I2)
仍然以Ctrl+Shift+Enter三键结束,否则无法产生正确结果。
8.双击J2单元格的填充柄,将公式向下填充到最末单元格。
9.根据J列的汇总值与B列的工资进行比较,可以清晰分辨公式的正确性。见图三所示:
图三验证公式的准确性
公式思路解释:
计算100元面额的钞票数量时最简单,将工资除以100,然后利用INT函数取整即可,即公式中INT($B2/C$1)部分;
而计算其它面值的钞票张数时,只需要对剩下的部分工资进行计算。而如何确定已经计算过的钞票面额的值是重点。本例中利用OFFSET($B$1,,1,1,COLUMNS($B:B)-1))获取已经计算过的钞票面额,再用OFFSET($B$1,ROWS(A$1:A1),1,1,COLUMNS($B:B)-1)获取已计算过的钞票对应的数量,两者乘积并汇总,再总薪资求差即为剩下的待计算金额。
(本文来源于图老师网站,更多请访问https://m.tulaoshi.com/diannaorumen/)而两个区域乘积并汇总在ET中有一个专用函数MMULT,所以本例公式可以改为=IF(COLUMN()=3,INT($B2/C$1),INT(($B2-MMULT(OFFSET($B$1,ROWS(A$1:A1),1,1,COLUMNS($B:B)-1)
,TRANSPOSE(OFFSET($B$1,,1,1,COLUMNS($B:B)-1))))/C$1))
根据前面的分析,计算100元钞票的数量和其它面值的数量使用了不同的公式。为了让两者统一,即仅使用一个公式完成,通过IF函数将两段公式结合即可,使公式在第3列是按前一种方式计算,列号大于3时则按另一种方式计算。
最后补充一点,如果用同类软件EXCEL解决此问题,可以改用以下普通公式完成:
(本文来源于图老师网站,更多请访问https://m.tulaoshi.com/diannaorumen/)=IF(COLUMN()=3,INT($B2/C$1),INT(($B2-SUMPRODUCT(B2:$C2*B$1:$C$1))/C$1))