哈喽,大家好呀~~
今天為大家整理了九類常用的多條件公式,實用性非常強,強烈建議推薦收藏備用。
第一類 多條件判斷
按照不同的績效等級發放獎金,規則為:A-1000元、B-500元、C-300元、D-100元。
公式1 =IF(C2="A",1000,IF(C2="B",500,IF(C2="C",300,100)))
公式2 =VLOOKUP(C2,{"A",1000;"B",500;"C",300;"D",100},2,)
點評:這類問題可以使用多個IF嵌套得到最終結果,但如果情況比較多的話,使用IF嵌套容易出錯,因此可以考慮使用VLOOKUP函數,通過對應關系來完成多條件判斷,增加條件時隻需要在常量數組裡添加相應的内容即可。
微信掃碼入群:領取本篇教程配套的Excel課件
第二類 多條件求和
統計各部門不同績效等級的獎金合計。
公式1 =SUMIFS($D:$D,$B:$B,$F2,$C:$C,G$1)
公式2 =SUMPRODUCT($D$2:$D$20*($B$2:$B$20=$F2)*($C$2:$C$20=G$1))
點評:對于多條件求和問題首選SUMIFS函數,當數據量不大的時候用SUMPRODUCT函數也方便,SUMPRODUCT函數的優勢在于條件區域可以使用數組或其他函數,SUMIFS函數的條件區域隻能使用單元格引用。
第三類 多條件計數
統計各部門不同績效等級的人數。
公式1 =COUNTIFS($B$2:$B$20,$F2,$C$2:$C$20,G$1)
公式2 =SUMPRODUCT(($B$2:$B$20=$F2)*($C$2:$C$20=G$1))
點評:對于多條件計數問題首選COUNTIFS函數,當數據量不大的時候用SUMPRODUCT函數也方便。兩個函數的優劣與多條件求和雷同。
第四類 多條件求平均值
統計各部門不同績效等級的平均工資。
公式 :
=ROUND(IFERROR(AVERAGEIFS($C:$C,$B:$B,$G2,$D:$D,H$1),),2)
點評:AVERAGEIFS函數可以實現多條件求平均值的功能,IFERROR函數可以将無法平均時返回的錯誤值替換為0,ROUND函數可以将平均後的結果按照指定的位數四舍五入,避免出現過多小數。
第五類 多條件排名次
按照部門和績效等級相同的人員對分數進行排名。
公式 :
=SUMPRODUCT(($B$2:$B$20=B2)*($C$2:$C$20=C2)*($D$2:$D$20>=D2))
點評:使用SUMPRODUCT函數可以非常容易地計算出分組排名的結果,這其實是利用了條件計數的原理,相當于統計在小組名稱相同的數據中,大于或等于當前值的個數。
第六類 多條件最大值
公式1 =MAXIFS(D:D,B:B,G2,C:C,H2)
公式2 =MAX(($B$2:$B$20=G2)*($C$2:$C$20=H2)*$D$2:$D$20)
點評:MAXIFS函數可以處理多條件統計最大值的問題,用法與AVERAGEIFS相同,但是2016及以下版本沒有這個函數,隻能使用公式2利用數組計算來得到多條件最大值,公式2需要按Ctrl、shift和Enter鍵完成輸入。
第七類 多條件最小值
公式1 =MINIFS(D:D,B:B,G2,C:C,H2)
公式2 =MIN(IF(($B$2:$B$20=G2)*($C$2:$C$20=H2),$D$2:$D$20,10))
點評:MINIFS也是2016以上版本新增的一個函數,用法與MAXIFS一樣。低版本隻能使用公式2來得到多條件最小值。
第八類 多條件匹配數據
公式1 =FILTER(A2:A20,(B2:B20=G2)*(C2:C20=H2))
公式2=IFERROR(INDEX($A$1:$A$20,SMALL(IF(($B$1:$B$20=$G$2)*($C$1:$C$20=$H$2),ROW($1:$20),99),ROW(A1))),"")
點評:FILTER是Excel365新增的一個函數,功能就是用來篩選數據的,具體用法可以在評論區留言【求FILTER用法】獲取。
第九類 多條件提取唯一值
公式1 =UNIQUE(B2:C20)
=INDEX($B$2:$C$20,SMALL(IF(MATCH($B$2:$B$20&$C$2:$C$20,$B$2:$B$20&$C$2:$C$20,)=ROW($1:$19),ROW($1:$19),99),ROW(A1)),COLUMN(A1))
好啦,以上就是今天的所有内容了,内容較多,建議大家收藏起來,用得上的時候拿出來套用即可。
有話要說...