Excel函数技巧：聚合计算函数梳理

一、计数函数

• COUNT
  • 功能：统计包含数字的单元格数量。
  • 语法：=COUNT(范围)
  • 示例：=COUNT(A1:A10) 统计 A1:A10 中数字单元格的数量。
• COUNTA
  • 功能：统计非空单元格的数量（包括文本、数字、逻辑值等）。
  • 语法：=COUNTA(范围)
  • 示例：=COUNTA(B1:B10) 统计 B1:B10 中非空单元格的数量。
• COUNTBLANK
  • 功能：统计空单元格的数量。
  • 语法：=COUNTBLANK(范围)
  • 示例：=COUNTBLANK(C1:C10) 统计 C1:C10 中空白单元格的数量。
• COUNTIF
  • 功能：按条件统计单元格数量（单条件）。
  • 语法：=COUNTIF(范围, 条件)
  • 示例：
    • =COUNTIF(D1:D10, ">50") 统计 D1:D10 中大于 50 的单元格数量。
    • =COUNTIF(E1:E10, "苹果") 统计 E1:E10 中为“苹果”的单元格数量。
• COUNTIFS
  • 功能：按多个条件统计单元格数量（多条件）。
  • 语法：=COUNTIFS(范围1, 条件1, 范围2, 条件2, ...)
  • 示例：
    =COUNTIFS(F1:F10, ">50", G1:G10, "<100") 统计 F 列大于 50 且 G 列小于 100 的行数。

案例1

我们接下来通过一个例子，来综合学习一下以下的函数，我现在有一张表格，上面有产品、类别、单价和销量四个字段，其中有一些缺失值。

任务1对数列条数进行计数

这里对第一列、第一列count函数计数的话，返回是0

第三列用count进行技术的话，返回是15，详见公式栏

或者选中第一列，用counta也可以得到总行数，详见公式栏

任务2 第二列空值和非空值各是多少个

用counta函数，非空值有13个，详见公式栏

用countblank函数，空值有2个，详见公式栏

任务3 单价大于等于5的销售记录有几条

有7条，用countif实现，详见公式栏

任务4 销量大于等于100 小于200的记录有多少条

有5条，用countifs函数实现，详见公式栏

二、求和函数

• SUM
  • 功能：对一组数字求和。
  • 语法：=SUM(范围)
  • 示例：=SUM(A1:A10) 计算 A1:A10 的和。

• SUMIF
  • 功能：按条件求和（单条件）。
  • 语法：=SUMIF(范围, 条件, [求和范围])
  • 示例：
    • =SUMIF(B1:B10, ">20", C1:C10) 对 B 列中大于 20 的对应 C 列单元格求和。
    • =SUMIF(D1:D10, "苹果", E1:E10) 对 D 列为“苹果”的对应 E 列单元格求和。

案例2 （数据延续上例） 单价大于5件的商品一共销售多少件

250件，用sumif函数实现，详见公式栏

• SUMIFS
  • 功能：按多个条件求和（多条件）。
  • 语法：=SUMIFS(求和范围, 条件范围1, 条件1, 条件范围2, 条件2, ...)
  • 示例：
    =SUMIFS(F1:F10, G1:G10, ">50", H1:H10, "男") 对 G 列大于 50 且 H 列为“男”的对应 F 列求和。

案例3 （数据延续上例）单价大于等于3的水果商品一共销售多少件

595件，用sumifs实现，详见公示栏

• SUBTOTAL
  • 功能：对可见单元格进行求和或计数（常用于筛选后计算）。
  • 语法：=SUBTOTAL(函数代码, 范围)
  • 常用代码：
    • 9 或 109：求和（SUM）
    • 2 或 102：计数（COUNT）
    • 3 或 103：非空计数（COUNTA）

• • 示例：
    =SUBTOTAL(9, A1:A10) 对 A1:A10 的可见单元格求和。

三、其他聚合计算函数

• AVERAGE：计算平均值。
  • 示例：=AVERAGE(A1:A10)

• MAX/MIN：返回最大值或最小值。
  • 示例：=MAX(B1:B10)

• AGGREGATE：类似 SUBTOTAL，但支持更多功能（如忽略错误值）。
  • 示例：=AGGREGATE(9, 6, A1:A10)（求和时忽略错误值）。