【原】学会这个函数套路，轻松解决50%的Excel问题！

与 30万 粉丝一起学Excel

VIP群内有很多问题，看似完全不相关，但只需学会一个函数套路，基本都能解决。

下面，卢子通过实际案例来说明这个套路。

1.余额

按账户、收入、支出，计算余额。也就是累计每个账户的收入，减去累计每个账户的支出。

累计账户收入，只要将区域采用混合引用，下拉的时候，区域就逐渐变大，从而起到累计的作用。

=SUMPRODUCT((E2=E$2:E2)*F$2:F2)

同理，累计账户支出。

=SUMPRODUCT((E2=E$2:E2)*G$2:G2)

余额就是两个公式相减，当然也可以合并同类项，让公式更加简洁。

=SUMPRODUCT((E2=E$2:E2)*(F$2:F2-G$2:G2))

语法：

=SUMPRODUCT((条件=条件区域)*求和区域)

而SUMPRODUCT函数可以换成一大堆函数，这才是最强大的地方。比如换成SUM函数，这样需要按Ctrl+Shift+Enter三键结束。

=SUM((E2=E$2:E2)*(F$2:F2-G$2:G2))

这个套路还有另外一种写法，SUM+IF，跟SUMIF作用差不多。

=SUM(IF(E2=E$2:E2,F$2:F2-G$2:G2))

下面再换成其他案例，拓展下这个套路结合其他函数的用法。

2.平均工资

员工并不是同一个月份入职，因此有的工资是空白的。比如卢子只入职3个月，平均工资就是(5100+5200+5200)/3。

这个套路除了可以单条件，也可以多条件。平均值就换成AVERAGE就可以，记得按Ctrl+Shift+Enter三键结束。

=AVERAGE(IF(($B$2:$I$2="工资")*(B3:I3>0),B3:I3))

当然，高版本有AVERAGEIFS函数，作用一样。

=AVERAGEIFS(B3:I3,$B$2:$I$2,"工资",B3:I3,">0")

同理，可以知道最大工资、最小工资、总工资。

=MAX(IF(($B$2:$I$2="工资")*(B3:I3>0),B3:I3))

=MIN(IF(($B$2:$I$2="工资")*(B3:I3>0),B3:I3))

=SUM(IF(($B$2:$I$2="工资")*(B3:I3>0),B3:I3))

3.入职月份

入职月份，也就是第一次工资大于0对应的月份。

最后一次的用LOOKUP。1,0这2个数字可以换成其他数字，只要前面的数字比后面大就可以，以大查找小，才能保证查找到最后的对应值。比如用2,1也可以。

=LOOKUP(1,0/(($B$2:$I$2="工资")*(B3:I3>0)),$B$1:$I$1)

查找第一次可以用VLOOKUP、XLOOKUP、MATCH等函数，这里用XLOOKUP会更简洁。用0查找0，就返回第一次对应的值。

=XLOOKUP(0,0/(($B$2:$I$2="工资")*(B3:I3>0)),$B$1:$I$1)

套路并不难，多练习几遍就会了，再结合每个函数的语法，解决50%的函数问题没问题。

推荐：90%的人都不知道XLOOKUP函数的这个新用法，真好用！

上篇：IF函数一个超经典的用法，无意间想到的

请把「Excel不加班」推荐给你的朋友