Excel 数据透视函数 #

┌─────────────────────────────────────────────────────────────┐
│                    数据透视函数一览                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  GETPIVOTDATA  从透视表获取数据                             │
│  AGGREGATE     灵活聚合函数                                 │
│  SUBTOTAL      分类汇总                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)

参数：
- data_field: 要获取的数据字段名
- pivot_table: 数据透视表中的任意单元格
- field1, item1: 字段名和项目名

=GETPIVOTDATA("销售额", A1)
获取透视表中的销售额总计

=GETPIVOTDATA("销售额", A1, "部门", "销售部")
获取销售部的销售额

=GETPIVOTDATA("销售额", A1, "部门", "销售部", "产品", "产品A")
获取销售部产品A的销售额

在数据透视表中点击单元格时，Excel 会自动生成 GETPIVOTDATA 公式：

1. 点击透视表中的任意单元格
2. 输入 =
3. 点击要获取数据的单元格
4. 自动生成 GETPIVOTDATA 公式

数据透视表结构：
    A          B          C          D
1              产品A      产品B      总计
2   销售部     5000       3000       8000
3   技术部     2000       4000       6000
4   总计       7000       7000       14000

获取销售部产品A的销售额：
=GETPIVOTDATA("销售额", A1, "部门", "销售部", "产品", "产品A")
→ 5000

获取总计：
=GETPIVOTDATA("销售额", A1)
→ 14000

使用单元格引用：
=GETPIVOTDATA("销售额", $A$1, "部门", H1, "产品", I1)

H1 = 部门名
I1 = 产品名

1. 字段名和项目名必须与透视表完全一致
2. 透视表结构改变时，公式可能失效
3. 可以关闭自动生成功能：
   文件 → 选项 → 公式 → 取消勾选"使用GetPivotData函数"

=AGGREGATE(function_num, options, array, [k])

参数：
- function_num: 函数编号（1-19）
- options: 忽略选项（0-7）
- array: 数据范围
- k: 某些函数需要的参数

1  AVERAGE    平均值
2  COUNT      计数
3  COUNTA     非空计数
4  MAX        最大值
5  MIN        最小值
6  PRODUCT    乘积
7  STDEV.S    样本标准差
8  STDEV.P    总体标准差
9  SUM        求和
10 VAR.S      样本方差
11 VAR.P      总体方差
12 MEDIAN     中位数
13 MODE.SNGL  众数
14 LARGE      第k大值
15 SMALL      第k小值
16 PERCENTILE.INC  百分位数
17 QUARTILE.INC    四分位数
18 PERCENTILE.EXC  百分位数（排除）
19 QUARTILE.EXC    四分位数（排除）

0 或省略：忽略嵌套 SUBTOTAL 和 AGGREGATE 函数
1：忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数
2：忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数
3：忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数
4：忽略空行
5：忽略隐藏行和空行
6：忽略错误值和空行
7：忽略隐藏行、错误值和空行

忽略隐藏行求和：
=AGGREGATE(9, 1, A1:A10)

忽略错误值求平均：
=AGGREGATE(1, 2, A1:A10)

忽略隐藏行和错误值求最大值：
=AGGREGATE(4, 3, A1:A10)

第2大的值（忽略错误）：
=AGGREGATE(14, 2, A1:A10, 2)

数据：
    A          B
1   数据       备注
2   10         正常
3   #N/A       错误
4   20         正常
5   30         正常

忽略错误值求和：
=AGGREGATE(9, 2, A2:A5)  → 60

忽略错误值求平均：
=AGGREGATE(1, 2, A2:A5)  → 20

忽略错误值求最大值：
=AGGREGATE(4, 2, A2:A5)  → 30

普通 SUM 遇到错误会返回错误：
=SUM(A1:A10)  → 如果有错误值，返回错误

AGGREGATE 可以忽略错误：
=AGGREGATE(9, 2, A1:A10)  → 忽略错误值求和

隐藏行时：
SUBTOTAL 可以忽略隐藏行
AGGREGATE 功能更强大，可以同时忽略多种情况

=SUBTOTAL(function_num, ref1, [ref2], ...)

参数：
- function_num: 函数编号（1-11 包含隐藏值，101-111 忽略隐藏值）
- ref1: 数据范围

包含隐藏值    忽略隐藏值    函数
1            101          AVERAGE
2            102          COUNT
3            103          COUNTA
4            104          MAX
5            105          MIN
6            106          PRODUCT
7            107          STDEV.S
8            108          STDEV.P
9            109          SUM
10           110          VAR.S
11           111          VAR.P

=SUBTOTAL(9, A1:A10)          求和（包含隐藏值）
=SUBTOTAL(109, A1:A10)        求和（忽略隐藏行）
=SUBTOTAL(1, A1:A10)          平均值（包含隐藏值）
=SUBTOTAL(101, A1:A10)        平均值（忽略隐藏行）

数据：
    A          B
1   部门       销售额
2   销售       1000
3   技术       2000
4   销售       1500
5   技术       1800

筛选后求和（只计算可见行）：
=SUBTOTAL(109, B2:B5)

筛选销售部后：
=SUBTOTAL(109, B2:B5)  → 2500（只计算销售部的数据）

SUM：计算所有值，不受筛选影响
=SUM(B2:B5)  → 6300

SUBTOTAL：只计算可见行
=SUBTOTAL(109, B2:B5)  → 筛选后的结果

使用 GETPIVOTDATA 创建动态报表：

    A          B          C
1   部门       产品       销售额
2   销售部     产品A      =GETPIVOTDATA("销售额",透视表!A1,"部门",A2,"产品",B2)
3   销售部     产品B      =GETPIVOTDATA("销售额",透视表!A1,"部门",A3,"产品",B3)

当透视表数据更新时，报表自动更新

数据：
    A          B          C
1   部门       员工       销售额
2   销售       张三       5000
3   技术       李四       3000
4   销售       王五       8000

筛选后统计：
筛选销售额：=SUBTOTAL(109, C2:C4)
筛选人数：=SUBTOTAL(103, B2:B4)
筛选平均：=SUBTOTAL(101, C2:C4)

数据：
    A
1   数据
2   100
3   #N/A
4   200
5   #DIV/0!
6   300

忽略错误求和：
=AGGREGATE(9, 2, A2:A6)  → 600

忽略错误求平均：
=AGGREGATE(1, 2, A2:A6)  → 200

忽略错误计数：
=AGGREGATE(2, 2, A2:A6)  → 3