Excel不同列多條件計數
=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 |
|
=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")¬
(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)
相關文章
- Excel 條件格式Excel
- excel條件格式怎麼設定 excel條件格式在哪裡Excel
- Excel教程——excel如何使用條件格式Excel
- 多執行緒程式設計介紹-條件變數執行緒程式設計變數
- 多執行緒06:條件變數執行緒變數
- where條件中使用case when來實現不同列的join
- python多執行緒程式設計5: 條件變數同步Python執行緒程式設計變數
- jquery 多條件匹配jQuery
- Pandas根據篩選條件對指定excel列進行篩選!神器!Excel
- Laravel 多條件查詢Laravel
- Laravel 多條件去重Laravel
- SQL多條件查詢SQL
- excel將一個工作表根據條件拆分成多個工作簿Excel
- Java 物件陣列多屬性條件排序問題(詳解)Java物件陣列排序
- Mysql按條件計數的幾種方法MySql
- Laravel 條件陣列 in 的用法Laravel陣列
- ReentrantLock的條件佇列ReentrantLock佇列
- 使用slice和條件變數實現一個簡單的多生產者多消費者佇列變數佇列
- 用VBA計算EXCEL中的行數和列數Excel
- 多執行緒(2)-執行緒同步條件變數執行緒變數
- 【C/C++多執行緒程式設計之八】pthread條件變數C++執行緒程式設計thread變數
- 多條件查詢---ssh版本
- 使用Excel資料進行條件刪除Excel
- excel圖表中的條件格式化Excel
- Condition條件變數變數
- pthread 條件變數thread變數
- 關於條件變數變數
- MySQL 變數和條件MySql變數
- [程式碼結構設計]根據不同條件使用不同實現類的業務程式碼設計
- .NET 通用多條件動態引數查詢方法 - SqlSugar ORMSqlSugarORM
- 條件編譯、多檔案程式設計、結構體編譯程式設計結構體
- 每個鎖建立多個條件佇列以避免虛假喚醒佇列
- C++列舉演算法之滿足條件的整數C++演算法
- PostgreSQL任意列組合條件行數估算實踐-取樣估算SQL
- 使用channel代替條件變數變數
- Java 設定Excel條件格式(高亮條件值、應用單元格值/公式/資料條等型別)JavaExcel公式型別
- 陣列多重篩選條件排序方法陣列排序
- 深入淺出AQS之條件佇列AQS佇列