Excel CountACountBlank函数的使用方法,含CountA(1:1)、CountA($A:$A)、跨表、自动求和与分段统计

网友投稿 2808 2022-06-08

excel 中,CountA函数用于统计除空单元外各种单元格的个数,它与Count函数的区别是,CountA统计为文本和逻辑值的单元格,而Count函数不统计。CountBlank函数专用于统计空单元格的个数,包括公式返回的空单元格。

如果不统计 0 和公式返回空值的单元格,可以用 CountA + CountBlank + CountIf 组合实现;如果要实现添加数据后自动求和,可以用 OffSet + CountA函数组合;如果要实现分段统计,可以用 If + Mod + CountA + Row函数组合。

 

一、Excel CountA函数与CountBlank函数的语法

(一)CountA函数

1、表达式:COUNTA(Value1, [Value2], ...)

中文表达式:CountA(值1, [值2], ...)

 

2、说明:

A、CountA函数统计数字、日期、文本、逻辑值(True 和 False)、空值(如公式返回的空值"")和错误值,它们可以是单元格或数组;而Count函数只统计数字、日期和能转为数字的单元格。

B、CountA函数至少要有一个 Value 参数,最多只能有 255 个 Value 参数。

 

(二)CountBlank函数

1、表达式:COUNTBLANK(Range)

中文表达式:COUNTBLANK(范围)

 

2、说明:

A、CountBlank函数统计空单元格、空文本(即""),不统计有 0 值的单元格。

B、运行CountBlank函数,需要关闭迭代计算,方法为:依次选择“文件”→ 选项 → 公式 → “计算选项”下 → 不勾选“启用迭代计算”→ 确定。

 

 

二、Excel CountA函数的使用方法及实例

(一)统计既有文本又有数字单元格区域的实例

1、双击 D8 单元格,输入公式 =COUNTA(D1:D7),按回车,返回统计结果 7;操作过程步骤,如图1所示:

图1

2、D1 至 D7 中有一个文本单元格和 6 个数字单元格,统计结果为 7,说明文本和数字都统计了。

 

(二)统计空值和错误值、不统计空单元格的实例

1、双击 A4 单元格,把公式 =COUNTA(A1:A3) 复制到 A4,按回车,返回统计结果 2;双击 A1,里面什么都没有为空单元格;双击 A2,里面是一个 If 公式返回空值;再双击 A1,输入 5,统计结果变为 3;操作过程步骤,如图2所示:

图2

2、当 A1 为空时,统计结果为 2,当 A1 输入 5 后,统计结果为 3,说明空单元格不统计,但公式返回的空值统计。

 

(三)统计一行不为空的单元格总数实例

1、统计第一行不为空的单元格总数。双击 A8 单元格,把公式 =COUNTA(1:1) 复制到 A8,按回车,返回统计结果 4;再次双击 A8,在两个 1 前都添加 $,按回车,也返回 4;操作过程步骤,如图3所示:

图3

2、公式说明:

公式 =COUNTA(1:1) 中的 1:1 表示引用第一行;=COUNTA(:) 中的 : 也表示引用第一行,所不同的是 1:1 是相对引用,而 : 是绝对引用;如果公式要往下拖且要求拖到第几行统计该行的单元格总数,则应该使用 =COUNTA(1:1),因为往下拖时,1:1 会变为 2:2,3:3 等。

提示:公式 =COUNTA(1:1) 和 =COUNTA(:) 不能写到第一行,否则无法统计出正确的结果。

 

(四)统计一列不为空的单元格总数实例

1、统计第一列不为空的单元格总数。双击 B8 单元格,把公式 =COUNTA(A:A) 复制到 B8,按回车,返回统计结果 7;再双击 B8,在两个A 前都加 $,按回车,同样返回 7;操作过程步骤,如图4所示:

图4

2、公式 =COUNTA(A:A) 中 A:A 表示对 A 列的引用,=COUNTA($A:$A) 也表示对 A 列的引用;A:A 与 $A:$A 区别也是前者为相对引用后者为绝对引用;另外,公式也不能写到 A 列,否则也会返回不正确的值。

 

(五)跨表统计实例

1、假如要统计“水果销量表1和水果销量表2”中的 B2:B7 的单元格总数。当前在“水果销量表1”,单击“水果销量表2”标签切掉到该工作簿,双击 A8 单元格,把公式 =COUNTA(水果销量表1:水果销量表2!B2:B7) 复制到 A8,按回车,返回统计结果 12;操作过程步骤,如图5所示:

图5

 

2、公式说明:

公式 =COUNTA(水果销量表1:水果销量表2!B2:B7) 中“水果销量表1:水果销量表2”表示引用两个工作簿,! 用于工作簿与引用单元格之间作为分隔符,引用两个工作簿单元格区域相同时,可以把工作簿写在一起,否则要把工作簿分开写。

 

 

三、Excel CountBlank函数的使用方法及实例

(一)统计所有空单元格实例

1、双击 A5 单元格,把公式 =COUNTBLANK(A1:A4) 复制到 A5,按回车,返回统计结果 2;双击 A1,里有一个 If 返回空值 "" 公式,再双击 A3,是一个空单元格;操作过程步骤,如图6所示:

图6

2、A1:A4 有两个空单元格,一个是公式返回的空值单元格,另一个是没有输入任何文字的空单元,两个都被统计了,说明CountBlank函数都统计它们。

 

(二)CountA + CountBlank + CountIf函数组合实现不统计 0 和公式返回空值的单元格实例

1、双击 A5 单元格,把公式 =COUNTA(A1:A4)-COUNTBLANK(A1:A4)-COUNTIF(A1:A4,"=0") 复制到 A5,按回车,返回统计结果 2;双击 A1 单元格,里面是一个返回空值 "" 的 If 公式;操作过程步骤,如图7所示:

图7

 

2、公式 =COUNTA(A1:A4)-COUNTBLANK(A1:A4)-COUNTIF(A1:A4,"=0") 说明:

=COUNTA(A1:A4) 用于统计 A1:A4 中包含文本、数值、空单元格的个数,结果为 4;COUNTBLANK(A1:A4) 用于统计 A1:A4 中空单元格的个数,结果为 1;COUNTIF(A1:A4,"=0") 用于统计 A1:A4 中值为 0 的单元格个数,结果为 1;把各部分统计结果相减,即 4 - 1 - 1,所以最终统计结果为 2。

Excel CountA与CountBlank函数的使用方法,含CountA(1:1)、CountA($A:$A)、跨表、自动求和与分段统计

 

 

四、Excel CountA函数的扩展使用实例

(一)OffSet + CountA函数组合实现添加数据后自动求和

1、假如要实现在表格最后一行添加销量后自动求和。双击 E2 单元格,把公式 =SUM(OFFSET(D1,1,,COUNTA(D:D)-1)) 复制到 E2,按回车,返回求和结果 6607;双击 D8,输入 783,按回车,E2 中的值自动变为 7390,说明累加了新输入的 783;操作过程步骤,如图8所示:

图8

 

2、公式 =SUM(OFFSET(D1,1,,COUNTA(D:D)-1)) 说明:

A、COUNTA(D:D) 用于统计 D 列有内容的单元格总数,结果为 7,正好是 D1:D7 的单元格总数;再用 7 减 1,结果为 6。

B、则 OFFSET(D1,1,,COUNTA(D:D)-1) 变为 OFFSET(D1,1,,6),接着以 D1 为基准,返回 D1 下 1 行 0 列且高度为 6 的单元格引用,即对 D2:D7 的引用。

C、则公式变为 =SUM(D2:D7),最后用 Sum 对 D2:D7 求和,最终结果为 6607。当在最后一行 D8 输入 783 后,COUNTA(D:D) 的统计结果变为 8,8 减 1 后变为 7,则 OffSet 返回 D2:D8,因此在表格后每添加一行,公式都会把销量自动计入求和。

 

(二)If + Mod + CountA + Row函数组合实现分段统计

1、假如要统计“1月和2月”的销量个数。双击 E2 单元格,把公式 =IF(MOD(ROW()-2,4)=0,COUNTA(OFFSET($D$2:$D$5,INT((ROW()-2)/4)*4,)),"") 复制到 E2,按回车,返回统计结果 3;选中 E2,把鼠标移到 E2 右下角,鼠标变为加号后,按住左键,往下拖一直拖到最后一行,则分别统计出“1月和2月”的销量个数,操作过程步骤,如图9所示:

图9

 

2、公式 =IF(MOD(ROW()-2,4)=0,COUNTA(OFFSET($D$2:$D$5,INT((ROW()-2)/4)*4,)),"") 说明:

A、公式中的 4 表示一段为四行,ROW() 返回公式所在的行号 2,则 (ROW()-2)/4 返回 0;Int函数用于取整,则 INT(0) 返回 0。

B、则 OFFSET($D$2:$D$5,INT((ROW()-2)/4)*4,) 变为 OFFSET($D$2:$D$5,0*4,),接着用 OffSet 返回以 D2:D5 左上角的单元格 D2 为基准、D2 下 0 行 0 列且高度和宽度与 D2:D5 一样的单元格引用,即返回 D2:D5。

C、则 COUNTA(OFFSET($D$2:$D$5,INT((ROW()-2)/4)*4,)) 变为 COUNTA($D$2:$D$5),进一步计算,对 D2:D5 进行统计,结果为 3。

D、则公式变为 =IF(MOD(ROW()-2,4)=0,3,"");由于 ROW() 返回 2,因此 MOD(ROW()-2,4) 变为 MOD(2-2,4),接着用Mod函数把 0 与 4 取模,结果为 0;则公式变为 =IF(0=0,3,""),由于 If 条件 0=0 成立,所以返回 3。

E、当公式在 E6 时,ROW() 返回 6,代入 INT((ROW()-2)/4) 为 INT((6-2)/4),进一步计算变为 INT(1),结果为 1;则 OFFSET($D$2:$D$5,INT((ROW()-2)/4)*4,) 变为 OFFSET($D$2:$D$5,1*4,),接着,返回以 D2:D5 左上角的单元格 D2 为基准、D2 下 4 行 0 列且高度和宽度与 D2:D5 一样的单元格引用,即返回 D6:D9,以下的计算方法跟上面一样。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:Excel Average函数的使用方法,包含与If、Int、Left、Find、Year、Month组合求隔行、指定条件、年月和季度
下一篇:Excel Count函数的使用方法,包含与If、Find与Frequency组合实现统计满足指定条件、重复值与唯一值个数实例
相关文章