在大多数用户眼中,SUMIFS是汇总,SUM是求和,IF是判断的,VLOOKUP是查找匹配的……还是太单纯,这些函数的内心其实复杂的hin哩,往往会做一些超出我们三观的事。
01
SUMPRODUCT函数完成交叉查找
交叉查找有很多种方法,唯独用SUMPRODUCT最让人意外,也最简单。
如下图:根据产品和客户查找对应的销量。J7单元格的公式:
=SUMPRODUCT((A2:A14=J4)*(B1:F1=J5)*B2:F14)
Excel数据处理高手职场速成班(Excel2019) | 知识兔
拓展一下:其实这个问题也可以用VLOOKUP函数解决,只是要配合MATCH函数
02
VLOOKUP函数隔行求和
Vlookup本是最最常用的查找函数,这次却出现在隔行求和的公式里。
Excel数据处理高手职场速成班(Excel2019) | 知识兔
【例】在H2单元格设置公式,隔1列求和
数组公式:公式输入完成后按CTRL+SHIFT+回车键结束
=SUM(VLOOKUP(A2,A2:K2,ROW(1:6)*2-1,0))
指定列求和
指定对3,4,6列求和
公式:
=SUM(VLOOKUP(A2,A2:G2,{3,4,6},0))
注:{3,4,6}把要求和的列数放在大括号内,用逗号分隔。
03
MIN函数判断取值
Min函数在判断数值大小时是可以替代IF函数的,还比IF函数更简单。
例如下图:由于公司给不起加班费,规定加班超过50分钟的一律视为50分钟,少于50分钟按实际加班时长。可以直接用公式=MIN(50,B2)来处理。
Excel数据处理高手职场速成班(Excel2019) | 知识兔
04
VLOOKUP函数多条件判断
作为大众情人VLOOKUP函数,它独得万千宠爱,一般不抢别人饭碗,除非忍不住。有一天VLOOKUP看到IF老兄正忙的焦头烂额,走进一看:好家伙,原来在计算提成,里三层外三层的套了很长的公式就为了计算H列的提成。
VLOOKUP函数说:你先歇着,让我来。只见VLOOUP优雅的在单元格划拉了几下写下下面这串公式=VLOOKUP(G2,$B$2:$C$6,2,1)问题就迎刃而解了。IF函数不得不服,真不愧是大众情人VLOOKUP。