GetPivotData #REF错误，数据格式问题? [英] GetPivotData #REF Error, data format issue?

问题描述

当我尝试从数据透视表引用元素时，出现#Ref错误.

I am getting a #Ref error when I try to reference an element from a pivottable.

我实际上是在做一个"="，然后点击数据透视表以获取公式，甚至返回了#REF错误.

I am literally doing a "=" and clicking on the pivot table to get the formula and even that is returning a #REF error.

更具体地说，我正在使用数据透视表来汇总跨某些行和列的数据字段.这些列的数字格式设置为字符串，因此当我执行公式时，会得到: = getpivotdata("my_sum"，ptable_ref，col_name，"COL_VAL" ，row_name，row_val)

To be a bit more specific, I am using a pivot table to sum up a datafield across some rows and columns. The columns have numbers formatted as string, so when I do the formula I get: =getpivotdata("my_sum", ptable_ref, col_name, "COL_VAL", row_name, row_val)

COL_VAL是一个类似于123、234的数字，但格式为字符串.现在，当我执行"123"的公式时，它可以工作，但是相同的"234"的公式却给我一个#REF错误.如果我不使用引号来使用数字本身，我仍然会收到错误消息.

COL_VAL is a number like 123, 234 but formatted as a string. Right now when I do the formula for "123" it works, but the same formula for "234" gives me a #REF error. If I leave off the quotes to use the number itself, I still get an error.

我已经重新启动了excel和我的计算机，但这无济于事.数据是从数据库查询中提取的，但col_name的格式相同.我也看不到任何尾随或前导空格.我为这可能是scratch头.我宁愿不必遍历各行以获取所需的值，而只需使用该函数即可轻松完成自己的工作.

I've restarted excel and my computer, but that doesn't help. The data is being pulled from a database query, but col_name is the same format for all of them. I don't see any trailing or leading spaces either. I am scratching my head about what this could be. I'd rather not have to iterate through the rows for the values I need and just use the function to make it easy on myself.

推荐答案

我通常不希望对Excel使用GetPivotData函数.您可以通过不在文件选项卡的excel选项中选择使用GetPivotData函数来删除该选项.请参阅ScreenShot

I usually prefer not to use the GetPivotData function for excel. You can remove the option by not selecting the use GetPivotData function in excel options on the file tab. Please see the ScreenShot

如果我们需要在数据透视图中引用数据单元，那么绝对不要通过getpivotdata引用它(如"F5")更好吗?很好奇，为什么有人要在创建数据透视表后引用数据透视表?他/她本可以直接在数据透视表数据上使用求和，计数或平均函数……这只是一个建议. #Ref错误可能意味着数据不支持枢轴单元(数据中缺少vlaue).最好清除缓存或不使用getpivotdata并直接引用该单元格.

If we need to refer to a data cell in a pivot, wouldn't it be better to refer to it absolutely (like "F5") instead of through getpivotdata? Just curious, why would anyone want to refer to the pivotdata after making a pivot table? He/She could have used sum, count or average function directly on the pivot data instead... Its just a suggestion. #Ref error may mean the pivot cell is not supported by the data(missing vlaue in data). Its better to clear cache or not to use getpivotdata and refer the cell directly.

这篇关于GetPivotData #REF错误，数据格式问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！