Excel难题：汇总多表中某人某些项目的收入
教育
教育
2024-07-11 08:26
上海
有1月-12月的工资明细名表，注意每个月的工资明细表中第2行的科目名称不一定都相同，每个科目对应的位置也不一定固定都在某一列，比如：
1月的I列是【其他补贴】，2月的I列却是【荣誉津贴】。在部门汇总表中汇总出每个人1到12月份各个科目对应的收入之和，这里不需要理会每个月的工资表中是否全部包含这些科目，有的就汇总，没有当月就按0计算。
问题本身很容易表述明白，看起来也不复杂，但是实现起来并不那么容易。
该同学写了两个复杂的公式，说结果不对，让我看看怎么回事。
看了公式我一头雾水，以上公式的逻辑比较乱，而且参数用法也不对，我就不分析了。
比如我们要求姓名为“赵”的这个人1到12月每个科目的收入，应该是到每个表中找到这个科目对应的数字相加即可，用VLOOKUP即可查找该人在某个表中对应的数字，但是不能同时查找该人在不同月份某个科目对应的数字。这时应该尽量用可以同时查找某人在不同表所对应的区域中的数字的函数，于是想到可以用SUMIF。正常情况下，SUMIF用法应该是，SUMIF(包含条件的列，条件，要求和的列)，平常我们的用法基本是下面这种情况：条件所在的列可以是多列，求和所在的列也可以是多列，但是这两个区域列数应该相等，否则就构不成一对一的关系了。知道了SUMIF有这个特点后，再分析下，这位同学的问题就是把这两个区域的数据一一对应分散到各个月份中去。假设每个表中各个科目所在的位置相同，这种情况下我们该怎么表达这两个区域呢？公式如下：=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:12)&"月!C3:C6"),B3,OFFSET(INDIRECT(ROW(1:12)&"月!A3:A6"),,3)))解析下：
相当于还是用SUMIF分别在12个表中进行条件求和，SUMIF第一个参数用INDIRECT(ROW(1:12)&"月!C3:C6")表示，即每个表中的C3:C6区域，也就是姓名所在的区域，求和区域为OFFSET(INDIRECT(ROW(1:12)&"月!A3:A6"),,3)，当然你也可以固定为某个列，因为我们前面已经说了假设某个科目所在的列位置是一样的，我这里用OFFSET主要是想着如果每个科目所在的列位置不一样，我可以修改OFFSET的第三参数从而返回不同的列。整个SUMIF返回的结果并不像下图中一样是一个总数：因为A2:D11是一个区域，而INDIRECT(ROW(1:12)&"月!C3:C6"）出来的区域是每个表中的C3:C6区域，并不是挨在一起的，他们的维度不一样的，所以出来的结果就是12个数字，相当于用SUMIF公式在每个分表中求和，要想得到汇总的结果，我们还需要对SUMIF的结果进行求和，所以外面套了个SUMPRODUCT。现在剩下的事情就是如何在SUMIF时能够在各个分表中查找某个科目所在的列，然后返回这个列呢？前面也提到了只要改变OFFSET的第三个参数即可，比如：=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:12)&"月!C3:C6"),B3,OFFSET(INDIRECT(ROW(1:12)&"月!A3:A6"),,{3;3;3;3;4;5;3;4;5;6;3;4})))相当于条件列区域为每个分表中的C3:C6，求和区域第一个表中是参照A3:A6偏移3列，所以为D3:D6，……第五个表中是偏移了4列，返回的是E3:E6，但是我们总不能查找每个科目时把该科目在12个表中位置都写一遍吧。查找某个科目在每个表中的列位置，我们肯定能想到MTACH，可惜的是MATCH并不支持这样的写法：
本案例我会放到近期开的VBA培训班中讲解，只有通过实际案例告诉大家这些代码到底是怎么来的，这样才能真正帮助到想要利用VBA解决实际问题的朋友。
1、我录制的所有视频教程都可以免费学习，每门教程都有对应的素材，视频学习永久有效，视频学习中遇到的疑问可以直接找我答疑。
2、除了视频中遇到的问题之外，工作中遇到的问题也可以找我答疑（主要答疑Excel方面的问题）。
3、会员群里不定期会用腾讯会议开直播，根据大家的需求讲解一些实际工作中遇到的问题的解决方案、一些数据处理流程的优化等。
4、我百度网盘中一些常用的软件及学习资料都可以分享给您，包括各版本的Office，政企版WPS，WPS中的VBA插件，以及一些常用的软件等，即我网盘里的所有资源都可以共享给你。
5、终身会员可以分销我的课程，有提成的！
终身会员群只招收100名学员，XF:999，需要的朋友请加V：527240310
