送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们啊,年底快到了,今年的收益如何啊?年终提成到手了吗?
下面这张表是某著名公司年终的提成统计表。
现在要按照下面的格式来统计每个员工的提成额。
源数据表中列举了全年12个月每个供应商的采购金额,以及对应的ADMIN和QUALITY这两个职能部门的员工名单。
怎样可以快速、准确地统计出每个员工的提成呢?
第一眼看到这个例子,肯能大多数小伙伴都会想到一个函数--SUMIFS。的确,它在多条件统计时很方便。但是在本题目下却不适用。
第一季度的公式,在单元格D36中输入公式“=ROUND(SUMPRODUCT(($C$5:$E$31)*(P$5:P$31=$C36))*2.3/100,2)”并向下拖曳即可。
思路:
($C$5:$E$31)部分,给出第一季度求和的单元格区域;
(P$5:P$31=$C36)部分,给出求和的条件
上述部分相乘的结果就是符合条件的求和区域
利用SUMPRODUCT求和
ROUND函数圆整
其它几个季度的公式结构是一样的,唯一不同的地方就是数据区域不同,稍作修改就可以了。
有朋友会问了,这道题么到底能不能使用SUMIF函数呢?是可以的,但是公式写起来要复杂一些,还要运用的多维的概念。
第一季度的公式:
由于SUMIF(SUMIFS)函数的求和区域不能为多列的区域,因此在构建公式时需要借助OFFSET函数的帮助,来形成一个三列的数据区域。
在单元格D36中输入公式“=ROUND(SUM(SUMIF($P$5:$P$31,C36,OFFSET($C$5:$C$31,0,ROW($1:$3)-1,31,1)))*2.3/100,2)”,三键回车并向下拖曳即可。
思路:
OFFSET($C$5:$C$31,0,ROW($1:$3)-1,31,1),单元格区域$C$5:$C$31依次向右移动2列和3列,形成一个三维数组。它的结构是这样的:$C$5:$C$31,$C$5:$D$31,$C$5:$E$31
利用SUMIF函数对上述这三个维度的数据区域按条件求和,形成的结果是{728208.7288;137988.146;39977.596}
由于是三维的,SUMIF函数不能对上述求总和。需要利用SUM函数对其降维求和,结果是906174.47
这里使用的三维的概念,不是很好理解。朋友们记不住没关系,了解即可。
其它几个季度的公式大同小异。
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服