Excel 统计函数 #

┌─────────────────────────────────────────────────────────────┐
│                    统计函数一览                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  COUNTIF   条件计数    =COUNTIF(A:A, ">60")                 │
│  COUNTIFS  多条件计数  =COUNTIFS(A:A, ">60", B:B, "男")     │
│  SUMIF     条件求和    =SUMIF(A:A, ">60", B:B)              │
│  SUMIFS    多条件求和  =SUMIFS(B:B, A:A, ">60", C:C, "销售")│
│  AVERAGEIF 条件平均    =AVERAGEIF(A:A, ">60", B:B)          │
│  RANK      排名        =RANK(A1, A:A)                       │
│  LARGE     第N大值     =LARGE(A:A, 2)                       │
│  SMALL     第N小值     =SMALL(A:A, 2)                       │
│  MEDIAN    中位数      =MEDIAN(A:A)                         │
│  MODE      众数        =MODE(A:A)                           │
│  STDEV     标准差      =STDEV(A:A)                          │
│  VAR       方差        =VAR(A:A)                            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

=COUNTIF(range, criteria)

参数：
- range: 要计数的范围
- criteria: 计数条件

=COUNTIF(A:A, "销售部")        计算销售部人数
=COUNTIF(A:A, ">60")           计算大于60的个数
=COUNTIF(A:A, "<>0")           计算非零值的个数
=COUNTIF(A:A, "")              计算空单元格个数
=COUNTIF(A:A, "<>")            计算非空单元格个数

等于：=COUNTIF(A:A, "销售部")
不等于：=COUNTIF(A:A, "<>销售部")
大于：=COUNTIF(A:A, ">60")
大于等于：=COUNTIF(A:A, ">=60")
小于：=COUNTIF(A:A, "<60")
小于等于：=COUNTIF(A:A, "<=60")

包含"销售"：=COUNTIF(A:A, "*销售*")
以"销售"开头：=COUNTIF(A:A, "销售*")
以"销售"结尾：=COUNTIF(A:A, "*销售")
单个字符：=COUNTIF(A:A, "销售?")

=COUNTIF(A:A, B1)              条件在单元格中
=COUNTIF(A:A, ">"&B1)          条件包含运算符和单元格引用
=COUNTIF(A:A, "*"&B1&"*")      包含单元格内容的条件

销售数据：
    A          B
1   产品       销售额
2   产品A      1000
3   产品B      2000
4   产品A      1500
5   产品C      3000
6   产品A      2500

产品A的销售次数：
=COUNTIF(A2:A6, "产品A")  → 3

销售额大于2000的次数：
=COUNTIF(B2:B6, ">2000")  → 2

销售额大于平均值的次数：
=COUNTIF(B2:B6, ">"&AVERAGE(B2:B6))  → 2

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

多条件计数，所有条件必须同时满足

=COUNTIFS(A:A, "销售部", B:B, ">5000")
销售部且销售额大于5000的人数

=COUNTIFS(A:A, "男", B:B, ">30", C:C, "本科")
男性、年龄大于30、学历本科的人数

员工数据：
    A          B          C
1   部门       性别       年龄
2   销售部     男         25
3   销售部     女         30
4   技术部     男         35
5   销售部     男         28
6   技术部     女         32

销售部男性人数：
=COUNTIFS(A2:A6, "销售部", B2:B6, "男")  → 2

年龄大于25的女性人数：
=COUNTIFS(B2:B6, "女", C2:C6, ">25")  → 2

销售部年龄大于25的人数：
=COUNTIFS(A2:A6, "销售部", C2:C6, ">25")  → 2

计算60-80分的人数：
=COUNTIFS(A:A, ">=60", A:A, "<=80")

计算某月份的数据：
=COUNTIFS(A:A, ">=2024-01-01", A:A, "<=2024-01-31")

=SUMIF(range, criteria, [sum_range])

参数：
- range: 条件判断范围
- criteria: 条件
- sum_range: 求和范围（可选，默认与 range 相同）

=SUMIF(A:A, "销售部", B:B)     销售部的销售额总和
=SUMIF(A:A, ">60", B:B)        大于60的对应值求和
=SUMIF(A:A, ">60")             范围本身大于60的值求和

销售数据：
    A          B          C
1   产品       数量       金额
2   产品A      100        1000
3   产品B      200        2000
4   产品A      150        1500
5   产品C      300        3000
6   产品A      250        2500

产品A的总数量：
=SUMIF(A2:A6, "产品A", B2:B6)  → 500

产品A的总金额：
=SUMIF(A2:A6, "产品A", C2:C6)  → 5000

数量大于200的总金额：
=SUMIF(B2:B6, ">200", C2:C6)  → 5500

=SUMIF(A:A, "产品*", B:B)      以"产品"开头的求和
=SUMIF(A:A, "*A*", B:B)        包含"A"的求和

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

注意：第一个参数是求和范围，与 SUMIF 不同

=SUMIFS(C:C, A:A, "销售部", B:B, ">5000")
销售部且销售额大于5000的总和

=SUMIFS(D:D, A:A, "男", B:B, ">30", C:C, "本科")
男性、年龄大于30、学历本科的工资总和

销售数据：
    A          B          C          D
1   日期       产品       数量       金额
2   2024-01-01 产品A      100        1000
3   2024-01-01 产品B      200        2000
4   2024-01-02 产品A      150        1500
5   2024-01-02 产品B      180        1800
6   2024-01-03 产品A      200        2000

产品A的总金额：
=SUMIFS(D2:D6, B2:B6, "产品A")  → 4500

2024-01-01的总金额：
=SUMIFS(D2:D6, A2:A6, "2024-01-01")  → 3000

产品A在2024-01-02的金额：
=SUMIFS(D2:D6, B2:B6, "产品A", A2:A6, "2024-01-02")  → 1500

数量大于150的总金额：
=SUMIFS(D2:D6, C2:C6, ">150")  → 5800

1月份的总销售额：
=SUMIFS(C:C, A:A, ">=2024-01-01", A:A, "<=2024-01-31")

某日期范围的总和：
=SUMIFS(C:C, A:A, ">="&开始日期, A:A, "<="&结束日期)

=AVERAGEIF(range, criteria, [average_range])

条件平均值

=AVERAGEIF(A:A, "销售部", B:B)    销售部的平均销售额
=AVERAGEIF(A:A, ">60")            大于60的值的平均

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

多条件平均值

=AVERAGEIFS(C:C, A:A, "销售部", B:B, ">5000")
销售部且销售额大于5000的平均值

=RANK(number, ref, [order])

参数：
- number: 要排名的数值
- ref: 参考范围
- order: 排序方式
  - 0 或省略：降序（最大值为第1名）
  - 1：升序（最小值为第1名）

=RANK(A1, A$1:A$10)           降序排名
=RANK(A1, A$1:A$10, 0)        降序排名
=RANK(A1, A$1:A$10, 1)        升序排名

成绩数据：
    A          B          C
1   学生       成绩       排名
2   张三       85         =RANK(B2, $B$2:$B$6)  → 3
3   李四       92         =RANK(B3, $B$2:$B$6)  → 1
4   王五       78         =RANK(B4, $B$2:$B$6)  → 5
5   赵六       88         =RANK(B5, $B$2:$B$6)  → 2
6   钱七       82         =RANK(B6, $B$2:$B$6)  → 4

=RANK.EQ(number, ref, [order])
相同值返回最高排名（如两个第2名，下一个是第4名）

=RANK.AVG(number, ref, [order])
相同值返回平均排名（如两个第2名，都显示2.5，下一个是第4名）

数据：100, 90, 90, 80

RANK.EQ：1, 2, 2, 4
RANK.AVG：1, 2.5, 2.5, 4

=LARGE(array, k)    返回第k大的值
=SMALL(array, k)    返回第k小的值

=LARGE(A:A, 1)      最大值（等同于 MAX）
=LARGE(A:A, 2)      第二大值
=LARGE(A:A, 3)      第三大值

=SMALL(A:A, 1)      最小值（等同于 MIN）
=SMALL(A:A, 2)      第二小值
=SMALL(A:A, 3)      第三小值

销售数据：
    A          B
1   销售员     销售额
2   张三       5000
3   李四       8000
4   王五       6000
5   赵六       9000
6   钱七       7000

第一名销售额：
=LARGE(B2:B6, 1)  → 9000

第二名销售额：
=LARGE(B2:B6, 2)  → 8000

第三名销售额：
=LARGE(B2:B6, 3)  → 7000

倒数第一名：
=SMALL(B2:B6, 1)  → 5000

=SUM(LARGE(B2:B6, {1,2,3}))
或
=LARGE(B2:B6, 1)+LARGE(B2:B6, 2)+LARGE(B2:B6, 3)

=MEDIAN(number1, [number2], ...)

返回一组数的中位数

=MEDIAN(1, 2, 3, 4, 5)     → 3
=MEDIAN(1, 2, 3, 4)        → 2.5
=MEDIAN(A1:A100)           范围的中位数

=MODE(number1, [number2], ...)

返回出现次数最多的数

=MODE(1, 2, 2, 3, 3, 3)    → 3
=MODE(A1:A100)             范围的众数

=STDEV.S(number1, [number2], ...)   样本标准差
=STDEV.P(number1, [number2], ...)   总体标准差

=STDEV.S(A1:A100)        样本标准差
=STDEV.P(A1:A100)        总体标准差

=VAR.S(number1, [number2], ...)     样本方差
=VAR.P(number1, [number2], ...)     总体方差

=VAR.S(A1:A100)          样本方差
=VAR.P(A1:A100)          总体方差

数据范围：A1:A100

计数：=COUNT(A1:A100)
求和：=SUM(A1:A100)
平均：=AVERAGE(A1:A100)
中位数：=MEDIAN(A1:A100)
众数：=MODE(A1:A100)
最大值：=MAX(A1:A100)
最小值：=MIN(A1:A100)
标准差：=STDEV.S(A1:A100)
方差：=VAR.S(A1:A100)

按部门统计人数：
=COUNTIF(部门列, 部门名)

按部门统计销售额：
=SUMIF(部门列, 部门名, 销售额列)

按部门统计平均销售额：
=AVERAGEIF(部门列, 部门名, 销售额列)

=PERCENTRANK.INC(A:A, A1)
返回 A1 在范围中的百分比排名（0-1）

=PERCENTRANK.EXC(A:A, A1)
排除端点的百分比排名

数据：
    A          B          C          D
1   销售员     部门       销售额     月份
2   张三       华东       50000      1月
3   李四       华东       80000      1月
4   王五       华北       60000      1月
5   赵六       华北       90000      2月
6   钱七       华东       70000      2月

华东区总销售额：
=SUMIF(B2:B6, "华东", C2:C6)  → 200000

华东区平均销售额：
=AVERAGEIF(B2:B6, "华东", C2:C6)  → 66666.67

销售额大于60000的人数：
=COUNTIF(C2:C6, ">60000")  → 3

华东区销售额大于60000的人数：
=COUNTIFS(B2:B6, "华东", C2:C6, ">60000")  → 2

销售额排名：
=RANK(C2, $C$2:$C$6)  → 5
=RANK(C3, $C$2:$C$6)  → 2

数据：
    A          B          C          D
1   学生       语文       数学       英语
2   张三       85         92         88
3   李四       78         85         90
4   王五       92         88         85
5   赵六       88         78         92

语文及格人数（>=60）：
=COUNTIF(B2:B5, ">=60")  → 4

语文优秀人数（>=90）：
=COUNTIF(B2:B5, ">=90")  → 1

总分：
=SUM(B2:D2)  → 265

平均分：
=AVERAGE(B2:D2)  → 88.33

最高分：
=MAX(B2:D5)  → 92

各科平均分：
=AVERAGE(B2:B5)  → 85.75 语文
=AVERAGE(C2:C5)  → 85.75 数学
=AVERAGE(D2:D5)  → 88.75 英语

数据：
    A          B          C
1   员工       日期       状态
2   张三       2024-01-01 出勤
3   张三       2024-01-02 出勤
4   张三       2024-01-03 请假
5   李四       2024-01-01 出勤
6   李四       2024-01-02 迟到
7   李四       2024-01-03 出勤

张三出勤天数：
=COUNTIFS(A2:A7, "张三", C2:C7, "出勤")  → 2

张三请假天数：
=COUNTIFS(A2:A7, "张三", C2:C7, "请假")  → 1

迟到总人次：
=COUNTIF(C2:C7, "迟到")  → 1