数据表查找和引用,大家一般都最先想到 vlookup 函数,然而当我们需要查找引用数据透视表的数据时,vlookup 函数就未必总能奏效,而且一旦数据透视表发生了变化,还会导致查找结果出错。 此时就需要一个专门针对数据透视表的函数登场了,今天就向大家介绍 GetPiovtData 函数 。 用途: 从数据透视表的可见数据中,根据条件检索并返回数据。 语法: GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...) Data_field: 必需 包含要检索的数据的数据字段的名称,用引号引起来 Pivot_table: 必需 对数据透视表中任何单元格、单元格区域或单元格已命名区域的引用 此信息用于确定包含要检索数据的数据透视表 Field1、Item1、Field2、Item2...: 可选 描述要检索的数据的 1 到 126 个字段名称对和项目名称对;这些对可按任何顺序排列 字段名称和项目名称引号括起来 案例: 下表的 A 至 E 列是数据透视表,需要在 I 至 P 列按月提取每个人的奖金数。 在实际应用当中,查询结果和数据透视表大多都会出现在不同的 sheet 中,本教学案例中为了便于大家查看公式,我就把目标和结果表放在同一个 sheet 了。 解决方案: 1. 在 J5 单元格输入“=”--> 然后鼠标单击 B5 单元格,就会自动出现以下公式: =GETPIVOTDATA('Value',$A$3,'Row','于筱怀','Page1','一月') 'Value':要提取的结果列 $A$3:数据透视表中的任意一个单元格 'Row','于筱怀':Row 字段为 '于筱怀' 'Page1','一月':Page1 字段为 '一月' 从上述公式的参数中可以看出,除了第 3 组参数自动取了 '于筱怀' 的奖金以外,其他都已经符合我们需要了,接下来我们就手工调整一下第 3 组参数 2. 如下图所示,将公式中的 '于筱怀' 改为 I5: 由于 I5 是单元格地址,不是值,所以外面不需要加引号 3. 回车以后,奖金值就变成了“2435”,与数据透视表中“郭德纲”“一月”的奖金值一致,即查找成功 4. 向下拖动 J5 单元格,复制整列公式,即可完成一月所有人奖金的查找 * 请注意: 即便 I 列的姓名顺序与 A 列顺序完全一致,我们也要执行第 2 步,即将公式中的 '于筱怀' 改为 I5 如果不改的话,向下拖动公式时,'于筱怀' 这个参数不会发生变化,即整列所有人奖金取的都是 '于筱怀' 一个人的值 5. 接下来用同样的方式取“二月”的奖金:在 M5 单元格输入“=”--> 然后鼠标单击 C5 单元格,就会自动出现以下公式: =GETPIVOTDATA('Value',$A$3,'Row','于筱怀','Page1','二月') 6. 将公式中的 '于筱怀' 改为 L5 --> 向下拖动 M5 单元格,整列复制公式,即可完成二月奖金查找 7. 按照同样方式完成三月奖金查找 |
|