|
本文转载自公众号：老墨说Excel，作者：老墨。 https://mp.weixin.qq.com/s/WUhrm6t9qqPRwDAoKdWCaQ 春暖花开  今 天好几个朋友问我怎么快速统计每个员工全年1-12月工资，这个其实在老墨推荐的那篇《HR必备技能：如何做一套系统的工资系列表？》里已经有介绍，但我相信可能远水解不了近渴，所以，我还是为大家整理一个简化版的也便于大家理解的案例。 开始讲解之前，还得重申一下几个原则或者说前提： 1）每月工资表样式务必一致，标题等等。如果不一致就无法用公式统一解决，这个必须清楚，那只能先复制粘贴到统一的表格再来做汇总的事。 2）每月工资表尽可能清清爽爽，什么部门小计、项目小计之类......但凡统计项都到时候拎到另外一张表，不要为了在月度表里看方便就放在上面，这样不利于汇总的时候公式统一应用。  明确前提之后，我们再来说汇总的事，上表就是简化后的工资表，A列是姓名，B列是实发工资，届时根据姓名去作为条件去汇总统计。然后1-12月都是一样的格式。  汇总表的样式如上，姓名、实发工资以月为单位按列排布。这里需要注意的是，我们在命名每个月的工资表的时候名称尽量统一，比如1月工资表叫“1月”，那后面都一样，这个是届时汇总时候识别表格的关键。如果你叫了“1月工资表”，那么在汇总表上的第二行这里也是“1月工资表”这两者要一一对应，才能一个公式解决汇总12个月的工资。  现在开始写公式，我们先把“1月”实发工资引用过来，这里我们就用SUMPRODUCT()来汇总，公式为：=SUMPRODUCT(('1月'!$A$2:$A$13=汇总表!$A3)*('1月'!$B$2:$B$13))，这个公式其实就一个条件，就是A列的姓名，当1月姓名区域等于A3单元格名字时候，再与B列实发工资区域相乘，便得出A3单元格“毛**”的1月实发工资，这里姓名位置顺序无关函数公式计算结果的正确性。  下面接下来要解决的是，如何用一个公式适用全部12个月工资汇总的问题。 当然你觉得不嫌麻烦的话，就上面那个公式也是可以了的，比如你将公式中的“1月”改成“2月”、“3月”......“12月”就行了。 但对于老墨来说，在公式中再加一个函数的事，绝不会去改12次。在公式中要加的函数是INDIRECT()，间接引用函数，之前老墨也有介绍过。它是用来解决工作表切换问题的，这就是老墨之前强调为什么工作表名称要和汇总表中第二行名称一样的原因了，因为我已经在第二行将1-12月份工作表名称都列好了，通过INDIRECT()把SUMPRODUCT()中的区域进行间接引用，实现一个公式适用所有月份，完整公式为：=SUMPRODUCT((INDIRECT('''&B$2&''!$A$2:$A$13')=汇总表!$A3)*(INDIRECT('''&B$2&''!$B$2:$B$13')))，公式INDIRECT('''&B$2&''!$A$2:$A$13')，就是'1月'!$A$2:$A$13。  完成之后，你只要将B3单元格公式复制到整张表就是OK了，这样汇总是不是省心多了。 （END） 老墨说Excel |
|
|
