☰

引用数据透视表时，VLOOKUP 或 INDEX MATCH 不会更新列

tag-icon • tag-icon microsoft-excel pivot-table vlookup
引用数据透视表时，VLOOKUP 或 INDEX MATCH 不会更新列

我快疯了，想弄清楚这个问题。当我了解到 INDEX-MATCH 和比 VLOOKUP 更灵活的功能时，我以为自己中了大奖，但当公式引用数据透视表时，这种灵活性似乎没有帮助。

我的问题是，我需要向数据透视表添加另一列，但当我这样做时，引用新列右侧数据的所有 VLOOKUP 公式都无效。我可以让它仅在引用常规表时自动更新公式中的列字母。

如果 VLOOKUP 引用我向其添加列的数据透视表中的数据......灾难就发生了......

答案1

GETPIVOTDATA是数据透视表的原生查找函数，因此您可以使用它来代替VLOOKUP或INDEX和MATCH。函数中的字符串GETPIVOTDATA可以分配给您的变量，以实现有效的查找。

答案2

我意识到 OP 已经找到了答案GETPIVOTDATA，但是为了其他对此不起作用的用户，这里还有另一种选择。

如果出于某种原因，使用 GETPIVOTDATA 不能满足您的需求（我遇到过几次这个问题），那么您仍然可以使用 INDEX / MATCH 组合并进行少许修改，但这会使它变得更长。一般格式是您需要修改
=INDEX(ResultArray, MATCH(Lookup_Value, Lookup_Array, Match_Type))
和ResultArray动态Lookup_Array而不依赖于公式自动更新。您可以使用函数执行此OFFSET操作。例如，假设您有一个数据透视表，其中标题位于第 4 行。使用OFFSET和MATCH首先找到您想要的标题。

=INDEX(OFFSET(FirstColumnOfPivotTable, 0, MATCH("Result Header", "4:4", 0)), MATCH(Lookup_Value, OFFSET(FirstColumnOfPivotTable, 0, MATCH("Lookup Header", "4:4", 0)), Match_Type))

此解决方案存在一些问题：

  • 选择要使用的范围FirstColumnOfPivotTable至关重要。如果您选择某个静态范围，请将其设置为大于您需要的范围。最好也根据数据透视表的大小动态调整。
  • 如果你在数据透视表上添加或删除过滤器，第 4 行可能不再有标题，这会破坏公式
  • 任何依赖于更多事物对齐的事物都更容易失败


GETPIVOTDATA只能返回数据透视表中已显示的数字。如果您想要某个子标题的总数，但总数未显示在数据透视表中，则该函数无法为您提供该数字。如果可能，请修改要使用的数据透视表，GETPIVOTDATA因为它更可靠。如果您无法做到这一点，请尝试上述较长的公式，然后小心管理数据透视表。

相关内容