上次个税算法闲聊（2）提到了个税累计预扣会用到两个函数：indirect和sumproduct，indirect是引用数据，而sumproduct则是将引用的数据汇总求和，我们来讲工作中会遇到的2种求和情况
1、多表位置相同求和-sum
2、多表位置不定多条件求和-sumproduct+sumif
01 多表位置相同求和
每到季度、半年度、年度时，总会需要写总结报告，这个时候往往需要一张汇总表将过往数据求和汇总，这些数据的特征就是在不同表的同一个位置
举个例子：我们公司每天都要把前一天的销售日报表发到微信群给领导过目
销售日报表
月底了，老板说要看看这个月的销售月报表
销售月报表
然后我们来观察一下：日报表和月报表根本没有什么区别，每个项目的位置一样，那这个时候我们就可以用sum函数来实现相同位置跨多表求和
使用公式：SUM('2019.01.23:2019.01.30'!e3), 即对23到30日的所有销售日报表中的e3单元格求和
Excel中多表，多单元格引用用“:”,如a:e意为选取A列到E列，跨表引用的形式为“表名!单元格”，如2！a1，意为因为工作表名为2的a1单元格
小结：
跨表引用-Sum函数优缺点
优点：位置相同时，函数简单易动
缺点：位置变化时，跨表引用相同数据原理上错误，若坚持用sum函数实现汇总，只能手工点击表格获取位置，麻烦
02 多表位置不定多条件求和-sumproduct+sumif
期待老板每天只看日报表，月底只看月报表，简直是奢望，老板真正想看的表格长这样，有日销售额，累计销售额、完成率
标红处其实就是求累计数据，这个时候用sum函数根本搞不定
我在实际完成处把公式套好了，又用纯手工求和的方法验证了一遍，正确无误，可以放出来了
累计求和验证
公式：=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("28:"&$D$1))&"日!c3:c4"),$C$3,INDIRECT(ROW(INDIRECT("28:"&$D$1))&"日!e3")))
括号太多，直接复制就行了，要不然容易出错
整个公式意为：在多张表中通过条件比对的方式找到数据，利用sumproduct函数对找到的数据进行求和；
Sumproduct(sumif(条件区域c3到c4,求和条件，求和区域：每一张的e3))
公式说明
假设有N张表（为了偷懒，我做了3张表意思意思一下，28日、29日、30日）
当d1处输入30时
ROW(INDIRECT("28:"&$D$1))返回row(28:30)，即{28；29；30}
INDIRECT(ROW(INDIRECT("28:"&$D$1))&"日!e3")))等价于indirect({28；29；30})&"日!e3")，即分别引用28!e3、29!e3、30!e3这3个单元格的值
公式利用sumif函数筛选出符合条件的数组，然后利用sumproduct函数进行求和
工资累计求和：
上面的公式解决了累计求和的问题，只需要把相应的区域替换即可完成工资累计求和
累计工资
公式如下SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:"&$G$1))&"月!A:A"),$A3,INDIRECT(ROW(INDIRECT("1:"&$G$1))&"月!c:c")))
直接复制就行，要不然照着抄都容易抄错，考验眼神的时候到了
累计工资的算法都会了，专项附加累计扣除额、个税累计缴纳数直接更改区域就行了，换汤不换药
个税公式
ROUND(MAX((I3-J3)*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;2520;16920;31920;52920;85920;181920},0)-K3,2)
万事具备，只欠工资了
写在后面
我很努力想要把累计求和的公式给说透，无能水平不过，实在是描述不清楚，就这样将就看吧，有空我再理一遍思绪
网友评论