|
VIP群內(nèi)有很多問題，看似完全不相關(guān)，但只需學(xué)會一個函數(shù)套路，基本都能解決。按賬戶、收入、支出，計算余額。也就是累計每個賬戶的收入，減去累計每個賬戶的支出。
累計賬戶收入，只要將區(qū)域采用混合引用，下拉的時候，區(qū)域就逐漸變大，從而起到累計的作用。=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((條件=條件區(qū)域)*求和區(qū)域) 而SUMPRODUCT函數(shù)可以換成一大堆函數(shù)，這才是最強大的地方。比如換成SUM函數(shù)，這樣需要按Ctrl+Shift+Enter三鍵結(jié)束。=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)) 下面再換成其他案例，拓展下這個套路結(jié)合其他函數(shù)的用法。
員工并不是同一個月份入職，因此有的工資是空白的。比如盧子只入職3個月，平均工資就是(5100+5200+5200)/3。
這個套路除了可以單條件，也可以多條件。平均值就換成AVERAGE就可以，記得按Ctrl+Shift+Enter三鍵結(jié)束。=AVERAGE(IF(($B$2:$I$2="工資")*(B3:I3>0),B3:I3)) 當然，高版本有AVERAGEIFS函數(shù)，作用一樣。=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)) 入職月份，也就是第一次工資大于0對應(yīng)的月份。最后一次的用LOOKUP。1,0這2個數(shù)字可以換成其他數(shù)字，只要前面的數(shù)字比后面大就可以，以大查找小，才能保證查找到最后的對應(yīng)值。比如用2,1也可以。
=LOOKUP(1,0/(($B$2:$I$2="工資")*(B3:I3>0)),$B$1:$I$1) 查找第一次可以用VLOOKUP、XLOOKUP、MATCH等函數(shù)，這里用XLOOKUP會更簡潔。用0查找0，就返回第一次對應(yīng)的值。
=XLOOKUP(0,0/(($B$2:$I$2="工資")*(B3:I3>0)),$B$1:$I$1) 套路并不難，多練習(xí)幾遍就會了，再結(jié)合每個函數(shù)的語法，解決50%的函數(shù)問題沒問題。
推薦：90%的人都不知道XLOOKUP函數(shù)的這個新用法，真好用！ 上篇：IF函數(shù)一個超經(jīng)典的用法，無意間想到的 
請把「Excel不加班」推薦給你的朋友
|
