Excel不同列多條件計數

gaorongchao1990626發表於2013-03-08


=SUMPRODUCT(($C$1:$C$10000="hete->s288c")*($H$1:$H$10000<900))-SUMPRODUCT(($C$1:$C$10000="hete->s288c")*($H$1:$H$10000<895))



=SUMPRODUCT(($C$1:$C$1000000<50000)*($C$1:$C$1000000>45000)*$U$1:$U$1000000)

條件的求和


複製excel中的公式,而不是公式後得到的結果
假設公式在 A列
選中A列,  按Ctrl+~     資料---分列----固定寬度---下一步------下一步      列資料格式  選擇  文字-----完成  ------ 按Ctrl+~



25 大於30且小於75的單元格個數
30
44 結果 公式
11
8
=SUM(IF(($V$4:$V$75<30)*($V$4:$V$75>0),1,0))
13 8 =COUNTIF(V4:V75,">=0")-COUNTIF(V4:V75,">=30")
-3 8 =SUMPRODUCT((V4:V75<30)*(V4:V75>0))
6 8 =SUM((V4:V75>0)*(V4:V75<30))
45
-7
20
26
35 陣列在輸入完成後要同時按Ctrl+Shift+Enter進行確定.
20
30
99
-7
19


SUMPRODUCT函式可以方便地計算工作表內多列中對應值相乘後的和,其語法為:
SUMPRODUCT(array1,array2,array3, …)
其中,Array1, array2, array3, … 2 30個陣列,其相應元素需要進行相乘並求和。陣列引數必須具有相同的維數,否則,函式 SUMPRODUCT將返回錯誤值 #VALUE!該函式將非數值型的陣列元素作為 0處理。
下面通過示例介紹SUMPRODUCT函式的基本使用方法。如下圖1所示的工作表:


                                                
1
其中所定義的名稱為:

名稱

引用範圍

公司

=Sheet1!$D$3:$D$17

全部資料

=Sheet1!$A$2:$E$17

日期

=Sheet1!$A$3:$A$17

姓名

=Sheet1!$B$3:$B$17

性別

=Sheet1!$C$3:$C$17

用工數

=Sheet1!$E$3:$E$17

(1)要計算工作表中姓名是張三且公司為A的用工數統計,則可以使用下面的公式:
=SUMPRODUCT(0+(
姓名="張三"),0+(公司="A"),用工數)
返回結果24
(2)
要獲取姓名張三出現的次數,則可以使用下面的公式:
=SUMPRODUCT((
姓名="張三")*1)
=SUMPRODUCT(0+(姓名="張三"))
結果為5
(3)
要獲取姓名為張三且公司為A的總數,則可以使用下面的公式:
=SUMPRODUCT((
姓名="張三")*(公司="A")*1)
=SUMPRODUCT((姓名="張三")*(公司="A"))
結果為4


                                                     
2
(4)
探討
在計算工作表中姓名是張三且公司為A的用工數統計時,使用的是公式=SUMPRODUCT(0+(姓名="張三"),0+(公司="A"),用工數)。按照常規做法,可以使用公式:
=SUMPRODUCT(
姓名="張三",公司="A",用工數)
但其結果為0,即並不是所想要的正確結果24

·        Excel在公式中能將以文字表示的數字轉換為數字,例如公式:
="3"*5
雖然”3”是文字,但該公式能返回結果15

  • Excel也能將數字轉換為文字,例如:
    ="No" & 1
    返回的結果為No1。
  • Excel將邏輯值轉換為數值,例如:
    =0+TRUE返回的結果為1;
    =1*TRUE返回的結果為1。

因此,在公式中新增0強制將邏輯值進行轉換?!

 excel函式的絕對引用。

$E$2:$E$50




Excel中COUNTIF函式的使用方法彙總

 

一、求各種型別單元格的個數

(1)  求真空單元格單個數:                =COUNTIF(data,"=")

(2)  真空+假空單元格個數:   =COUNTIF(data,"")      相當於countblank()函式

(3)  非真空單元格個數:      =COUNTIF(data,"<>")     相當於counta()函式

(4)  文字型單元格個數:      =COUNTIF(data,"*")    假空單元格也是文字型單元格

(5)  區域內所有單元格個數:   =COUNTIF(data,"<>""")

(6)  邏輯值為TRUE的單元格數量 =COUNTIF(data,TRUE)

小說明:

EXCEL單元格內資料主要有以下幾類:數值型,文字型,邏輯型,錯誤值型。其中時間型別也是一種特殊的數值。文字型別的數字是文字型。

空單元格:指什麼內容也沒有的單元格,姑且稱之為真空。

假空單元格:指0字元的空文字,一般是由網上下載來的或公式得來的,姑且稱之為假空。

date指單元格區域,該引數不能是陣列

二、求><=某個值的單元格個數

(1) 大於50                           =COUNTIF(data,">50")

(2) 等於50                            =COUNTIF(data,50)

(3) 小於50                           =COUNTIF(data,"<50")

(4) 大於或等於50                     =COUNTIF(data,">=50")  

(5) 小於或等於50                     =COUNTIF(data,"<=50")&not;

(6) 大於E5單元格的值                =COUNTIF(data,">"&$E$5) 

(7) 等於E5單元格的值                 =COUNTIF(data,$E$5)  

(8) 小於E5單元格的值                =COUNTIF(data,"<"&$E$5)  

(9) 大於或等於E5單元格的值           =COUNTIF(data,">="&$E$5)

(10) 小於或等於E5單元格的值          =COUNTIF(data,"<="&$E$5)

三、等於或包含某N個特定字元的單元格個數

(1) 兩個字元                       =COUNTIF(data,"??")

(2) 兩個字元並且第2個是B         =COUNTIF(data,"?B") 

(3) 包含B                         =COUNTIF(data,"*B*")

(4) 第2個字元是B                 =COUNTIF(data,"?B*")

(5) 等於“你好”                    =COUNTIF(data,"你好")  

(6) 包含D3單元格的內容            =COUNTIF(data,"*"&D3&"*")

(7) 第2字是D3單元格的內容       =COUNTIF(data,"?"&D3&"*")

 

注:countif()函式對英文字母不區分大小寫,萬用字元只對文字有效

四、兩個條件求個數

(1)   >10並且<=15       =SUM(COUNTIF(data,">"&{10,15})*{1,-1}) 

(2)   >=10並且<15       =SUM(COUNTIF(data,">="&{10,15})*{1,-1})  

(3)   >=10並且<=15     =SUM(COUNTIF(data,{">=10",">15"})*{1,-1})

(4)   >10並且<15         =SUM(COUNTIF(data,{">10",">=15"})*{1,-1})

注:一般多條件計數使用SUMPRODUCT函式,以上方法較少使用,僅供參考。

補充:三個區域計數:

  三個區域中>=60        =SUM(COUNTIF(INDIRECT({"a46:a48","b48:b50","c47:c48"}),">=60"))

五、各種特殊統計要求的計算    A2:A32 為存放資料的區域

(1)非空文字(僅包括可鍵入文字) =COUNTIF(A2:A32,">=!") 

(2)所有非空文字

=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">=") 

或 {=SUM(COUNTIF(A2:A32,">="&{"!",""})*{1,-1})+COUNTIF(A2:A32,">="&CHAR(1))}

(3)全部可見單元格 

{=SUM(N(IF(ISERROR(A2:A32),1,SUBSTITUTE(A2:A32,"",""))<>""))}

(4)有效可見單元格 

=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">=")+COUNT(A2:A32)

(5)全部不見單元格(真空+空格+空文字)

=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">=")

(6)空格 =COUNTIF(A2:A32,">=")-COUNTIF(A2:A32,">=!") 

(7)空文字"" =COUNTIF(A2:A32,"")-COUNTIF(A2:A32,"=")

(8)邏輯與錯誤  =COUNTIF(A2:A32,"<>")-COUNTIF(A2:A32,"*")-COUNT(A2:A32)





相關文章