兩個神仙技巧,帶你看破excel統(tǒng)計不重復數(shù)的秘密(下篇)
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-09-02 09:36:43點擊:3827
編按:
哈嘍,大家好!在前幾天的教程中我們通過學習公式求值和F9鍵,看懂了統(tǒng)計不重復數(shù)據(jù)的第一種套路。今天我們接著上回所說,繼續(xù)給大家分享第二種套路。趕緊來看看吧~
數(shù)據(jù)源照舊,如下圖所示,要求統(tǒng)計出不重復的客戶數(shù):
在上期我們掌握了破解公式的方法后,今天我們再來看看計算不重復數(shù)據(jù)個數(shù)的第二個公式套路。
套路2:COUNT和MATCH的組合
這個公式的難度就稍微有點大了,一起看看操作過程。
這個公式是數(shù)組公式,完成輸入后記得按Ctrl+Shift+回車鍵,公式兩邊會自動出現(xiàn)大括號。
公式=COUNT(1/(MATCH(B2:B23,B2:B23,0)=ROW(1:22)))用到了三個函數(shù),COUNT、MATCH和ROW,不管操作還是原理,這個公式都要難于第一個公式。
那么為什么還要介紹這個公式呢?
這是因為在這個公式中,用到的一些思路和方法,會在很多強大的公式中反復遇到的,因此理解第二個套路,有助于提升公式運用的能力。
言歸正傳,還是用公式求值來破解這個公式的原理吧。
從圖中可以看出,首先計算的是MATCH這部分,關于這個函數(shù),之前有詳細的教程介紹《MATCH:函數(shù)哲學家,找巨人做伴。新出道必學!》,建議先去搞清楚函數(shù)的基本功能。
簡單來說MATCH有三個參數(shù),查找值、查找區(qū)域和查找方式,公式得到的是查找值在查找區(qū)域內首次出現(xiàn)的位置,點一下求值就能看到結果。
還是看客戶42337,一共出現(xiàn)了五次,MATCH函數(shù)得到的結果都是1,說明這個客戶首次出現(xiàn)的位置是1。
要強調一下,這個1是查找范圍中的位置,我們的查找范圍是從第二行開始的。
對于MATCH得到的這一組數(shù)據(jù),一定要理解其含義。繼續(xù)點求值可以得到ROW這部分的結果。
ROW可以得到參數(shù)對應的行號,例如ROW(A1),結果就是1,而ROW(1:22),得到的就是前22行的行號,也就是1到22這一組數(shù)字。
注意公式MATCH(B2:B23,B2:B23,0)=ROW(1:22)中的范圍是不同的,MATCH是2到23行,實際是22行數(shù)據(jù),而ROW的范圍是以實際數(shù)據(jù)的行數(shù)為依據(jù)的。
繼續(xù)點求值,公式會判斷MATCH得到的這一組數(shù)據(jù)與ROW得到的這一組數(shù)據(jù)是否一致,結果是一組邏輯值。
從結果可以發(fā)現(xiàn),每個客戶首次出現(xiàn)的位置,公式結果就是TRUE。
這里有必要普及一下邏輯值的知識了。
在Excel中有六個比較的符號,=(等于)、>(大于)、<(小于)、>=(大于或等于)、<=(小于或等于)、<>(不等于),本例中用的是等于。
比較的結果就是邏輯值,邏輯值有兩個,分別是TRUE和FALSE,TRUE表示結果正確,FALSE表示結果不正確。
例如1>2,這個比較的結果就是FALSE。
邏輯值在和數(shù)字進行加減乘除等運算時,TRUE相當于1,FALSE相當于0。
在這一步計算中,就是用數(shù)字1和這一組邏輯值進行計算,當分母為TRUE時,1/1得到1;當分母為FALSE時,1/0會得到錯誤值,分母為零。
點求值就能看到這個結果。
如果理解了以上原理,最后的結果就很好理解。
因為COUNT只做一件事,統(tǒng)計有幾個數(shù)字。在這一組結果中,只有五個1是數(shù)字,因此最終的結果就是5。
很多時候,1/都用0/代替了,也許這是高手們的一種習慣吧。
當你真正的懂得了公式原理之后,1/和0/將不會再是造成你困擾的原因。
對第二個公式的原理就分析到這了,在這個公式中,用到了很多高級公式常用的技能,例如使用ROW得到一個數(shù)組,使用各種比較運算得到一組邏輯值,進而通過對邏輯值的計算得到一些錯誤值(錯誤值并不是一點用都沒有哦)。而使用0/還是1/,除了一部分特殊情況之外,大多數(shù)情況是沒有區(qū)別的。
好了,對于統(tǒng)計不重復數(shù)據(jù)個數(shù)的兩個公式分析就告一段落,如果你還遇到什么無法破解的公式,可以留言告訴小編,咱們一起來搞明白。
本文配套的練習課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
MATCH函數(shù)的應用《MATCH:函數(shù)哲學家,找巨人做伴。新出道必學!》
ROW函數(shù)的應用《看看excel絕頂高手用的這些壓箱底查找公式,其實都是你熟悉但從沒深入用過的簡單東西……》
0/的應用《VLOOKUP&LOOKUP雙雄戰(zhàn)(四):在橫向和逆向查詢上的血拼!》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!