本篇文章給大家帶來了關于excel的相關知識,其中主要介紹了關于countif函數的相關問題,包括了怎么輸入不一樣的序號、怎么計算不重復的人數等等內容,下面一起來看一下,希望對大家有幫助。
相關學習推薦:excel
一、不一樣的序號
如下圖所示,要在A列按部門輸入序號,不同部門的序號從1開始。
在A2單元格輸入公式,向下復制:
=COUNTIF(B$2:B2,B2)
COUNTIF函數的統計區域是B$2:B2,第一個B2是行絕對引用,第二個B2,是相對引用。當公式向下復制時,就會變成B$2:B3、B$2:B4……一個不斷擴展的區域,從這個動態區域中統計B列部門的個數。
二、計算不重復的人數
如下圖所示,要計算C列不重復的人數。
公式為:
=SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14))
這是一個十分常用的統計不重復數據個數的公式,其中包含了一個簡單的數學邏輯:
任意一個數據重復出現N次,N個1/N的和值為1。
公式中“COUNTIF(A2:A14,A2:A14)”部分是數組計算,作用是分別統計A2:A14單元格區域中每個元素出現的次數。
運算過程相當于:
=COUNTIF(A2:A14,A2)
=COUNTIF(A2:A14,A3)
……
=COUNTIF(A2:A14,A14)
返回內存數組結果為:
{2;2;2;2;2;2;2;2;2;2;1;1;1}
再使用1除以返回的內存數組,得到以下結果:
{0.5;0.5;0.5……;1;1;1}
用1除,即相當于計算COUNTIF函數所返回內存數組的倒數。
為便于理解,把這一步的結果中的小數部分使用分數代替,結果為:
{1/2;1/2;1/2;1/2;……;1;1;1}
如果單元格的值在區域中是唯一值,這一步的結果是1。
如果重復出現兩次,這一步的結果就有兩個1/2。
如果單元格的值在區域中重復出現3次,結果就有3個1/3…
即每個元素對應的倒數合計起來結果仍是1。
最后用SUMPRODUCT函數求和,得出不重復的人員總數。
三、提取不重復名單
如下圖所示,要提取C列不重復的名單。
公式為:
=INDEX(C:C,1+MATCH(0,COUNTIF(E$1:E1,C$2:C$15),0))&””
注意是數組公式,編輯完成后,要按Ctrl+Shift+回車。
首先利用COUNTIF函數,在公式所在位置上方的單元格區域中,分別查找C$2:C$15單元格區域每個數據的個數。返回一個由0和1構成的數組,如果C$2:C$15單元格區域的元素在公式上方出現過,結果就是1;如果沒出現,結果就是0。
和第一個例子里一樣,COUNTIF函數的第一參數是一個擴展的區域,公式的提取結果會被重復利用。
再利用MATCH函數,在COUNTIF函數返回的數組中查找第一個0的位置,也就是查找首次出現的數據所在的位置。
由于數據表的標題行占了1行,將這個數字加1,就是需要提取的不重復數據在數據表中列的位置。
再利用INDEX函數,以MATCH函數的計算結果作為索引值,提取C列對應位置上的數據。
這個函數從最后開始往上看,比較容易理解一些哦。
相關學習推薦:excel