Countif函數,大家并不陌生,其功能就是單條件計數,但如果僅僅将它用于單條件計數,你就真的Out了,太小看次函數的功能了。
一、功能及語法結構。
功能:計算指定區域中滿足給定條件的單元格數目。
語法結構:=Countif(條件範圍,條件)。
二、用法。
1、按“性别”統計人數。
方法:
在目标單元格中輸入公式:=COUNTIF(D3:D12,J3)。
解讀:
此方法為Countif函數最基本的用法,即符合指定條件的值的個數。但如果“條件”發生變動,此方法還适用嗎?
2、統計除“大專”之外的人數。
方法:
在目标單元格中輸入公式:=COUNTIF(F3:F12,'<>'&J3)。
解讀:
符号“<>”為不等于,所以“條件”可以解讀為不等于當前單元格的值。
3、統計“月薪”高于4500的人數。
方法:
在目标單元格中輸入公式:=COUNTIF(G3:G12,'>'&J3)。
解讀:
如果要統計“月薪”小于或小于等于4500的人數,隻需将公式中的“>”更換為“<”或“<=”。
4、統計“月薪”列的空白單元格個數。
方法:
在目标單元格中輸入公式:=COUNTIF(G3:G12,'=')。
解讀:
空值并不是一個空格,空格也是一個值,所以在統計空白單元格時一定要注意條件的寫法。
5、統計“月薪”列的非空白單元格個數。
方法:
在目标單元格中輸入公式:=COUNTIF(G3:G12,'<>')。
解讀:
非空白的單元格,即單元格中有值,所以不等于(<>)空值即為非空。
6、統計除“大專”、“大本”之外的人數。
方法:
在目标單元格中輸入公式:=COUNTIF(F3:F12,'<>'&J3)。
解讀:
1、星号(*)在Excel中為通配符,可以匹配任意長度的字符。
2、分析“大本”和“大專”之後發現,2個字段的第一個字都為“大”,而且在“學曆”列中,再沒有以“大”開始的字段,所以除“大專”、“大本”之外,可以表述為“<>”&'大*'。
7、根據“姓名”判斷人員信息是否重複。
方法:
在目标單元格中輸入公式:=IF(COUNTIF(B$3:B12,B3)>1,'重複','')。
解讀:
1、“重複”就是個數>1,而單條件計數計數可以用到Countif函數,然後用IF函數判斷Countif函數的統計結果,如果>1,返回“重複”,否則返回另外指定的值。
2、在統計指定值的個數時,數據範圍指定的開始單元格地址都是B3,所以要在行号3的前面添加絕對引用符号$。
8、根據“姓名”判斷人員信息是否第一次出現。
方法:
在目标單元格中輸入公式:=IF(COUNTIF(B$3:B3,B3)=1,'是','否')。
解讀:
1、充分利用IF函數的第三個參數,幫助我們解決問題。
2、如果不是第一次出現,那就是重複,即個數>1,根據IF函數的判斷,返回指定的值。
9、當錄入的人員姓名重複時,填充背景色。
方法:
1、選定姓名列,【條件格式】-【新建規則】,打開【新建格式規則】對話框。
2、在【選擇規則類型】中選擇【使用公式确定要設置格式的單元格】,并在【為符合此公式的值設置格式】中輸入:=COUNTIF(B$3:B3,B3)>1。
3、單擊右下角的【格式】,打開【設置單元格格式】對話框,單擊【填充】選項卡,選擇填充色,并【确定】-【确定】即可。
解讀:
1、重複時填充顔色,即指定的值個數>1條件成立時,執行填充背景色的命令。
2、填充的背景色可以根據需要自定義哦!
10、禁止錄入重複值。
方法:
1、選定目标單元格區域,【數據】-【數據驗證】,打開【數據驗證】對話框。
2、選擇【驗證條件】-【允許】中的【自定義】,在【公式】中輸入:=COUNTIF(B$3:B3,B3)=1。
3、單擊【出錯警告】選項卡,在【标題】中輸入:重複!,在【錯誤信息】中輸入:内容重複,請重新輸入!,并【确定】。
解讀:
當條件=COUNTIF(B$3:B3,B3)=1成立時,則正常執行,否則彈出【警告】對話框。
11、提取不重複值的個數。
方法:
在目标單元格中輸入公式:=SUMPRODUCT(1/COUNTIF(F3:F12,F3:F12))。
解讀:
1、=COUNTIF(F3:F12,F3:F12)的計算過程為:計算第二個F3:F12範圍内每個值在第一個F3:F12中的個數,即=COUNTIF(F3:F12,F3:F12)的計算結果為{3;3;4;4;3;1;1;1;4;4}。
2、1/COUNTIF(F3:F12,F3:F12),即1/{3;3;4;4;3;1;1;1;4;4},得到的值為{0.33;0.33;0.25;0.25;0.33;1;1;1;0.25;0.25},然後用Sumproduct函數對每個值進行求和,得到不重複值的個數為5。
有話要說...