九類按條件統(tǒng)計(jì)的公式大合集!建議收藏備用
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2022-10-17 10:02:15點(diǎn)擊:1683
編按:
哈嘍,大家好,今天來(lái)給大家整理了九類按條件統(tǒng)計(jì)的公式,涉及到多條件判斷、多條件匹配、多條件求和等,實(shí)用性非常的強(qiáng),強(qiáng)烈推薦大家收藏備用!
今天為大家整理了九類常用的公式,實(shí)用性非常強(qiáng),推薦收藏備用。
第一類 多條件判斷
按照不同的績(jī)效等級(jí)發(fā)放獎(jiǎng)金,規(guī)則為: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,)
點(diǎn)評(píng):這類問題可以使用多個(gè)IF嵌套得到最終結(jié)果,但如果情況比較多的話,使用IF嵌套容易出錯(cuò),因此可以考慮使用VLOOKUP函數(shù),通過對(duì)應(yīng)關(guān)系來(lái)完成多條件判斷,增加條件時(shí)只需要在常量數(shù)組里添加相應(yīng)的內(nèi)容即可。
第二類 多條件求和
統(tǒng)計(jì)各部門不同績(jī)效等級(jí)的獎(jiǎng)金合計(jì)。
公式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))
點(diǎn)評(píng):對(duì)于多條件求和問題首選SUMIFS函數(shù),當(dāng)數(shù)據(jù)量不大的時(shí)候用SUMPRODUCT函數(shù)也方便,SUMPRODUCT函數(shù)的優(yōu)勢(shì)在于條件區(qū)域可以使用數(shù)組或其他函數(shù),SUMIFS函數(shù)的條件區(qū)域只能使用單元格引用。
第三類 多條件計(jì)數(shù)
統(tǒng)計(jì)各部門不同績(jī)效等級(jí)的人數(shù)。
公式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))
點(diǎn)評(píng):對(duì)于多條件計(jì)數(shù)問題首選COUNTIFS函數(shù),當(dāng)數(shù)據(jù)量不大的時(shí)候用SUMPRODUCT函數(shù)也方便。兩個(gè)函數(shù)的優(yōu)劣與多條件求和雷同。
第四類 多條件求平均值
統(tǒng)計(jì)各部門不同績(jī)效等級(jí)的平均工資。
公式 =ROUND(IFERROR(AVERAGEIFS($C:$C,$B:$B,$G2,$D:$D,H$1),),2)
點(diǎn)評(píng):AVERAGEIFS函數(shù)可以實(shí)現(xiàn)多條件求平均值的功能,IFERROR函數(shù)可以將無(wú)法平均時(shí)返回的錯(cuò)誤值替換為0,ROUND函數(shù)可以將平均后的結(jié)果按照指定的位數(shù)四舍五入,避免出現(xiàn)過多小數(shù)。
第五類 多條件排名次
按照部門和績(jī)效等級(jí)相同的人員對(duì)分?jǐn)?shù)進(jìn)行排名。
公式 =SUMPRODUCT(($B$2:$B$20=B2)*($C$2:$C$20=C2)*($D$2:$D$20>=D2))
點(diǎn)評(píng):使用SUMPRODUCT函數(shù)可以非常容易地計(jì)算出分組排名的結(jié)果,這其實(shí)是利用了條件計(jì)數(shù)的原理,相當(dāng)于統(tǒng)計(jì)在小組名稱相同的數(shù)據(jù)中,大于或等于當(dāng)前值的個(gè)數(shù)。
第六類 多條件最大值
公式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)
點(diǎn)評(píng):MAXIFS函數(shù)可以處理多條件統(tǒng)計(jì)最大值的問題,用法與AVERAGEIFS相同,但是2016及以下版本沒有這個(gè)函數(shù),只能使用公式2利用數(shù)組計(jì)算來(lái)得到多條件最大值,公式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))
點(diǎn)評(píng):MINIFS也是2016以上版本新增的一個(gè)函數(shù),用法與MAXIFS一樣。低版本只能使用公式2來(lái)得到多條件最小值。
第八類 多條件匹配數(shù)據(jù)
公式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))),"")
點(diǎn)評(píng):FILTER是Excel365新增的一個(gè)函數(shù),功能就是用來(lái)篩選數(shù)據(jù)的,具體用法參考公眾號(hào)之前的教程。
如果沒有這個(gè)函數(shù)的話,只能使用公式2的萬(wàn)金油套路了,公式原理比較復(fù)雜,此處不贅述了。
第九類 多條件提取唯一值
公式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))
點(diǎn)評(píng):UNIQUE也是Excel365新增的一個(gè)函數(shù),功能就是用來(lái)提取唯一值的,具體用法參考公眾號(hào)之前的教程。
如果沒有這個(gè)函數(shù)的話,只能使用公式2的萬(wàn)金油套路了,公式原理比較復(fù)雜,此處不贅述了。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
10種職場(chǎng)人最常用的excel多條件查找方法?。ńㄗh收藏)
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)