當前位置:首頁 > 教育 > 正文

九類Excel多條件公式大全,工作再忙也要看,強烈建議你收藏備用!

哈喽,大家好呀~~

今天為大家整理了九類常用的多條件公式,實用性非常強,強烈建議推薦收藏備用。

第一類 多條件判斷

按照不同的績效等級發放獎金,規則為: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))

好啦,以上就是今天的所有内容了,内容較多,建議大家收藏起來,用得上的時候拿出來套用即可。

你可能想看:

有話要說...

取消
掃碼支持 支付碼