比較提取不重復(fù)項的5個常用套路和最強UNIQUE函數(shù)法
?
作者:郅龍來源:部落窩教育發(fā)布時間:2021-06-09 09:46:30點擊:6259
編按:
在日常工作中,我們?nèi)绾翁崛『陀嬎悴恢貜?fù)項?下面,小E將給大家盤點解決此類問題的六大操作方法,除了篩選法、刪除法、數(shù)據(jù)透視表法、常用公式套路法等,更有一個office-Excel 365發(fā)布的最強函數(shù),讓你1秒得到精準(zhǔn)結(jié)果!
做數(shù)據(jù)統(tǒng)計的朋友平時經(jīng)常遇到求和、平均值、最大值、最小值這些核算問題,了不起再來個條件求和、條件計數(shù)等等。這些問題用對應(yīng)的統(tǒng)計函數(shù)都很容易搞定,即便函數(shù)不太熟練,用透視表也都是分分鐘的事情。
但是,筆者最近從小伙伴們在群里的提問中發(fā)現(xiàn),大家覺得“統(tǒng)計不重復(fù)項的計數(shù)問題”很難。所以,筆者今天用案列詳細講解一下這個問題。
通常對于不重復(fù)項計數(shù)有兩種途徑:操作+公式、純公式。
例如,圖中這個數(shù)據(jù)源,B列有一組姓名,實際上只涉及到3個人,需要怎么計算?
下面,筆者將用不同的方法把這個問題聊透。
第一類途徑:操作+公式
邏輯分析:先把剔除重復(fù)項后的數(shù)據(jù)單獨列出來,然后用最簡單的計數(shù)函數(shù)統(tǒng)計(有時直接用眼睛也能看出結(jié)果)。因此只要明白了刪除重復(fù)項的方法,得到結(jié)果就不是問題。
通常有三種方法:高級篩選、刪除重復(fù)項、數(shù)據(jù)透視表,它們都是比較基礎(chǔ)的操作。
下面,開始逐個演示。
1.高級篩選法。
注意:只選擇姓名所在的單元格區(qū)域。
2.刪除重復(fù)項法。
注意:因為不能破壞數(shù)據(jù)源,所以需要先把姓名這一列單獨復(fù)制出來,再刪除重復(fù)項。
3.透視表法。
以上三種方法,都能一眼看出正確結(jié)果,但是如果不重復(fù)項數(shù)量過多,還需要大家進行求和操作。那么,有沒有可以一步完成匯總的操作呢?有,那就是公式法!下面介紹2個常用公式套路,和一個最強的函數(shù)——UNIQUE的使用!
第二類途徑:純公式
根據(jù)Excel版本不同,公式法也有至少三個思路。
1.一對非常經(jīng)典的函數(shù)組合套路,SUMPRODUCT函數(shù)與COUNTIF函數(shù)組合。
就本例而言,公式=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))統(tǒng)計出不重復(fù)的人數(shù),結(jié)果如圖所示。
公式解析:
①公式中,COUNTIF(B2:B15,B2:B15)統(tǒng)計出了每個人在區(qū)域中出現(xiàn)的次數(shù)。
②COUNTIF的第二參數(shù)使用的不是一個單元格而是一個區(qū)域,所以得到的結(jié)果也是多個值(需特別注意)。
③1/COUNTIF(B2:B15,B2:B15)這部分是對每個姓名出現(xiàn)的次數(shù)進行平均。
例如,夏淼一共出現(xiàn)了5次,那么每次的平均值都是1/5(0.2),最后將五個1/5相加為1,也就是一個人。
對每個人都按這樣計算一遍,最后得到的就是實際不重復(fù)的人數(shù)了。這個計算過程可以用F9功能鍵去進行分析,如圖所示。
這個方法其實是一種數(shù)學(xué)邏輯的應(yīng)用,除此之外,還有一個經(jīng)典的套路,也能統(tǒng)計不重復(fù)數(shù)據(jù)的個數(shù)。
2.套路2,使用COUNT和MATCH函數(shù)進行組合。
公式為:=COUNT(0/(MATCH(B2:B15,B2:B15,0)=ROW(1:14)))。這個公式是數(shù)組公式,需要按Ctrl、Shift和Enter完成輸入,結(jié)果如圖所示。
公式解析:
①公式中MATCH(B2:B15,B2:B15,0)的作用是對B2:B15中的每一個姓名做了一次定位,會得到一組數(shù)字{1;2;1;2;1;6;1;2;6;1;2;6;1;2}。
如果有重復(fù)的姓名,得到的都是這個姓名第一次出現(xiàn)的位置序號,如圖所示。
②ROW(1:14)的作用是得到與數(shù)據(jù)源姓名行數(shù)相同的自然數(shù)序列,本例有14行數(shù)據(jù),所以是1:14。
③MATCH(B2:B15,B2:B15,0)=ROW(1:14)得到一組邏輯值,通過下圖可以看出,相同姓名只有第一次出現(xiàn)時得到TRUE。
④0/(MATCH(B2:B15,B2:B15,0)=ROW(1:14))則得到一組包含0和錯誤值的數(shù)據(jù),只有TRUE對應(yīng)的位置是0,FALSE對應(yīng)的位置都是錯誤值,如下圖所示。
⑤最后由COUNT統(tǒng)計出數(shù)字的個數(shù)。
3.如果你使用的是Excel365版本,那么恭喜你,你可以直接使用最新的函數(shù)來1秒完成統(tǒng)計。
UNIQUE搭配COUNTA輕松實現(xiàn)不重復(fù)項的計數(shù),公式格式為:=COUNTA(UNIQUE(單元格區(qū)域))。
在這里寫作:=COUNTA(UNIQUE(B2:B15))。
注意:除了365版本之外,都不能用這個公式!Excel2016版可能不報錯,但是結(jié)果是不對的:
此外,關(guān)于office-Excel 365更多的新函數(shù)教程,大家可以參閱之前的教程:
12個 Office 365版本新增功能,速度圍觀!Excel粉絲們看過來!
如果大家還沒有office-Excel 365,還是需要掌握上面兩個比較傳統(tǒng)的公式套路及其原理哦~
總結(jié):
與套路1不同,套路2是非常經(jīng)典的一種數(shù)組計算應(yīng)用,也是高手們非常喜歡的用法??墒强v觀以上所有的方法,最簡單高效的方法是用最新的函數(shù)UNIQUE并搭配COUNTA!
不知道今天的教程你收獲了多少,歡迎分享。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
新函數(shù)UNIQUE提取不重復(fù)項比透視表更簡單高效
兩個神仙技巧,帶你看破excel統(tǒng)計不重復(fù)數(shù)的秘密
兩個神仙技巧,帶你看破excel統(tǒng)計不重復(fù)數(shù)的秘密(下篇)
版權(quán)申明:
本文作者郅龍;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!