寫出3000條Excel公式後,我發現80%的高級公式都離不開它們仨!

excel教程2019-09-12 00:32:12

關注回覆[目錄]學習113篇Excel教程


全套Excel視頻教程,掃碼觀看

編按:
哈嘍,大家好!在上週五的教程中我們給大家分享了統計不重複數據的第一個套路,今天我們繼續分享第二個套路。相比於第一個套路,第二個會稍難一點,但是這個套路中所用到的思路和方法確是在很多高級公式中經常用到的。學會了它們,有助於提升你運用公式的能力。
 
數據源照舊,如下圖所示,要求統計出不重複的客户數:
 
 
在昨天我們掌握了破解公式的方法後,今天我們再來看看計算不重複數據個數的第二個公式套路。


 
COUNT和MATCH的組合


這個公式的難度就稍微有點大了,一起看看操作過程。


這個公式是數組公式,完成輸入後記得按CTRL+SHIFT+回車鍵,公式兩邊會自動出現大括號。

温馨提示:加入下面QQ羣:109723835,下載教程配套的課件練習操作。


公式=COUNT(1/(MATCH(B2:B23,B2:B23,0)=ROW(1:22)))用到了三個函數,COUNT、MATCH和ROW,不管操作還是原理,這個公式都要難於第一個公式。
 
那麼為什麼還要介紹這個公式呢?
 
這是因為在這個公式中,用到的一些思路和方法,會在很多強大的公式中反覆遇到的,因此理解第二個套路,有助於提升公式運用的能力。
 
言歸正傳,還是用公式求值來破解這個公式的原理吧。
 

 
從圖中可以看出,首先計算的是MATCH這部分,關於這個函數,之前有詳細的教程介紹《MATCH:函數哲學家,找巨人做伴。新出道必學!》,建議先去搞清楚函數的基本功能。
 
簡單來説MATCH有三個參數,查找值、查找區域和查找方式,公式得到的是查找值在查找區域內首次出現的位置,點一下求值就能看到結果。
 

 
還是看客户42337,一共出現了五次,MATCH函數得到的結果都是1,説明這個客户首次出現的位置是1。
 
要強調一下,這個1是查找範圍中的位置,我們的查找範圍是從第二行開始的。
 
對於MATCH得到的這一組數據,一定要理解其含義。繼續點求值可以得到ROW這部分的結果。
 

 
ROW可以得到參數對應的行號,例如ROW(A1),結果就是1,而ROW(1:22),得到的就是前22行的行號,也就是1到22這一組數字。
 
注意公式MATCH(B2:B23,B2:B23,0)=ROW(1:22)中的範圍是不同的,MATCH是2到23行,實際是22行數據,而ROW的範圍是以實際數據的行數為依據的。
 
繼續點求值,公式會判斷MATCH得到的這一組數據與ROW得到的這一組數據是否一致,結果是一組邏輯值。
 

 
從結果可以發現,每個客户首次出現的位置,公式結果就是TRUE。
 
這裏有必要普及一下邏輯值的知識了。
 
在Excel中有六個比較的符號,=(等於)、>(大於)、=(大於或等於)、(不等於),本例中用的是等於。
 
比較的結果就是邏輯值,邏輯值有兩個,分別是TRUE和FALSE,TRUE表示結果正確,FALSE表示結果不正確。
 
例如1>2,這個比較的結果就是FALSE。
 
邏輯值在和數字進行加減乘除等運算時,TRUE相當於1,FALSE相當於0。
 

 
在這一步計算中,就是用數字1和這一組邏輯值進行計算,當分母為TRUE時,1/1得到1;當分母為FALSE時,1/0會得到錯誤值,分母為零。
 
點求值就能看到這個結果。
 

 
如果理解了以上原理,最後的結果就很好理解。
 
因為COUNT只做一件事,統計有幾個數字。在這一組結果中,只有五個1是數字,因此最終的結果就是5。
 

 
很多時候,1/都用0/代替了,也許這是高手們的一種習慣吧。
 
當你真正的懂得了公式原理之後,1/和0/將不會再是造成你困擾的原因。
 
對第二個公式的原理就分析到這了,在這個公式中,用到了很多高級公式常用的技能,例如使用ROW得到一個數組,使用各種比較運算得到一組邏輯值,進而通過對邏輯值的計算得到一些錯誤值(錯誤值並不是一點用都沒有哦)。而使用0/還是1/,除了一部分特殊情況之外,大多數情況是沒有區別的。
 
好了,對於統計不重複數據個數的兩個公式分析就告一段落,如果你還遇到什麼無法破解的公式,可以留言告訴小編,咱們一起來搞明白。

掃一掃添加老師微信


在線諮詢Excel課程


Excel教程相關推薦



MATCH:函數哲學家,找巨人做伴。新出道必學!

VLOOKUP&LOOKUP;雙雄戰(四):在橫向和逆向查詢上的血拼!

看看excel絕頂高手用的這些壓箱底查找公式,其實都是你熟悉但從沒深入用過的簡單東西……

https://hk.wxwenku.com/d/201357634