COUNTIF : 符合某一個條件者計算個數
=COUNTIF(範圍,條件)
COUNTIFS : 符合多個條件者計算個數
=COUNTIFS(範圍1,條件1,範圍2,條件2,範圍3,條件3......)
COUNTIF、COUNTIFS差別在於條件式有幾個,條件式一個以上要用COUNTIFS,
但條件式只有一個,也是可以使用COUNTIFS喔,
所以使用此公式時,通常就直接使用COUNTIFS了,這樣的話如果突然要多條件,也不用再改前面公式囉。
情境說明 :
下圖就是很基本的COUNTIFS用法。
F欄位打好第一個,下拉後就自動填滿,完成9筆資料囉!
這公式可以搭配 " ? " 以及 " * " 一起用,
字串字數統一時就可以搭配?一起使用。
字串字數不確定時就可以搭配*一起使用。
請參考下圖,
公式中" ?色 "代表了,只要是兩個字且最後一個字是"色",就計算在內。
如果把" ?色 "改成" *色"也是可以的喔,代表字串不確定幾個字,但最後一個字是"色"就計算在內。
若下圖,把 " * "改成 "?"是不行的喔,
因為名字字數不確定,有三個字有四個字,可能還有兩個字,所以不能使用" ? ",這樣會將字數限制住。
多條件計算,條件可以很多很多很多,全部都用逗號隔開就好了。
也可以配合"大於>"&"小於<"&"不等於<>"計算喔。
進階一點的例子,有點像公式方法的移除重複,這除了COUNTIF/COUNTIFS之外,還搭配了IF公式及VLOOKUP公式。
寫上去也是提醒我自己不要忘記這個用法,非常實用。
希望可以呈現向下圖右邊,將貨號資料移除重複。
做法如下:
新增一個A欄位,
A2的值為1
A3的公式為 : =IF(COUNTIFS(C$2:C3,C3)=1,A2+1,A2)
A3完成後就可以將A3的公式往下拉填滿 (只有第一筆資料的公式跟其他不一樣)
這樣A欄位就完成了。
新增一個G欄位,依序打上1、2、3....... (依資料多寡判定給多少數值)
H欄位公式=IFERROR(VLOOKUP(G2,A:C,3,0),"")
H欄向下拉填滿。
然後將A跟G欄位隱藏,就可以得到類似移除重複的結果。
當然若B、C欄位的貨號有繼續增加,H攔也會自動新增囉。 (前提是公式都有被填滿喔)
步驟拆解說明:
新增一個欄位(A),新增的欄位一定要在所有資料最前方,
因為之後搭配VLOOKUP公式,只能往後查詢找資訊喔。
A2的貨號為第一資料,所以一定為1,若找到第2筆不同的貨號,就標2,找到第3筆不同的貨號,就標3...以此類推。
若找到前面出現過的貨號,就不能給他新的編號。
A3的公式為 : =IF(COUNTIFS(C$2:C3,C3)=1,A2+1,A2)
A3下拉填滿,如果是會一直新增資料的,可以公式可以下拉到非常非常非常下面,這樣可以確保新增的資料都有被填滿公式囉。
A3公式拆解:
COUNTIFS(C$2:C3,C3)
因為會下拉,所以意思會變成COUNTIFS(C$2:Cn,Cn)
C2~Cn的範圍中,有幾個Cn?
IF(COUNTIFS(C$2:C3,C3)=1,A2+1,A2)
如果Cn=1,就代表是新的貨號,上面沒出現過,就給他新的編號,新的編號就是上面的編號+1
如果Cn>1,就代表那個貨號上面出現過了,不能給他新的編號,所以編號就跟上面一樣就好了。
這樣A欄位就完成了,在第一個出現的1、2、3.......中,就是一筆新的貨號。
新增一個G欄位,依序打上1、2、3....... (依資料多寡判定給多少數值)
如果是會一直新增資料的,可以下拉到非常非常非常下面,這樣可以確保新增的資料都有被填滿公式囉。
H欄位公式=IFERROR(VLOOKUP(G2,A:C,3,0),"")
H欄向下拉填滿。
如果是會一直新增資料的,可以下拉到非常非常非常下面,這樣可以確保新增的資料都有被填滿公式囉。
H欄位公式拆解:
VLOOKUP(G2,A:C,3,0)
因為會下拉,所以公式變成VLOOKUP(Gn,A:C,3,0)
在A欄中尋找跟Gn一樣的值,在A~C的查閱範圍中,抓取第3欄的值。(詳細可參考VLOOKUP公式)
IFERROR公式=IFERROR(A,B) 如果A發生錯誤,就執行B)
IFERROR(VLOOKUP(G2,A:C,3,0),"")
如果VLOOKUP(G2,A:C,3,0)錯誤,那就空白不顯示 ""
(如圖,因為左邊會找不到9,所以9以後都會出現錯誤,這時就希望他能顯示空白,不要出現錯誤字樣)
最後最後把A跟G欄位隱藏,就可以得到類似移除重複的結果囉。
留言列表