I've encountered an issue with a pivot table that I occasionally use, though I don't update. Specifically there are three Row Fields applied Year, Day (d-mon format), and then Transaction Date/Time (which displays only the hour component of the value). The pivot table is supposed to sort by Year, then Day, then Hour. The first two sorts work fine, but the third has started to sort the Hour values as text rather than numbers, so it's ordered 1 AM, 1 PM, 10 AM, 10 PM, etc. I've checked and there are no non-time values in the source field, the column of the pivot table itself is still set to a Time format type, and there are no filters applied to the field.
我遇到过使用pivot表的问题，我偶尔会使用它，不过我不会更新它。具体来说，有三个行字段被应用，分别是Year、Day (d-mon格式)和Transaction Date/Time(只显示值的hour组件)。pivot表应该是按年排序，然后是白天，然后是小时。第一两类工作很好,但已经开始第三小时值作为文本而不是数字,所以下令1点,1点,10点,10点,等我检查,没有本文的价值在源领域,数据透视表的列本身仍将时间格式类型,和没有过滤器应用于该领域。
At this point I'm not sure what else to look for. Any assistance would be much appreciated.
在这一点上，我不确定还需要寻找什么。如有任何帮助，将不胜感激。
UPDATE: The file is in Sharepoint so I tried taking the prior version (there are only two), dropping in the latest data from the current version, and refreshing the pivot table. The error dutifully appeared. So it does appear to be a data issue, but I can't imagine what's causing the issue. As stated above, I already checked that the values were Dates, specifically I used =IF(NOT(ISERROR(DATEVALUE(TEXT(C2, "m/d/yyyy h:mm"))), "", 1) and checked that all rows returned as blanks.
更新:文件在Sharepoint中，所以我尝试使用先前的版本(只有两个版本)，从当前版本中删除最新的数据，并刷新pivot表。错误地出现了。所以这确实是一个数据问题，但我无法想象是什么引起了这个问题。如上所述，我已经检查了这些值是日期，特别是我使用=IF(NOT(ISERROR) (DATEVALUE(文本(C2，“m/d/ yyyyyy h:mm”)))，“，”，并检查所有返回的行作为空格。
Something that may be causing issues is how the Year and Day fields come about. Specifically, there are no such fields in the Source Data, they are calculated Row Fields based on the one Transaction Date/Time column. I'm not even sure how this was done (to my knowledge calculated fields can only be columns) so I can't really look into if it may be causing the errors.
可能引起问题的是一年和一天的田地是如何形成的。具体来说，源数据中没有这样的字段，它们是基于一个事务日期/时间列计算的行字段。我甚至不确定这是如何完成的(我的知识计算字段只能是列)，因此我不能真正地研究它是否可能导致错误。
1 个解决方案
#1
1
I also had this problem and found no good way around it in Excel. What I ended up doing (that worked for me) is to extract the 24hour "hour value" from a date field using =VALUE(TEXT(A2,"h")) (where A2 contains the time/date code I'm looking for). I then used the date code itself (Column A in this example) as the first Row Label (first grouped item) then added the "hour value" as a secondary Row Label/Group. This allows leveraging the Pivot Table sort/group function naturally and then adds the detail in the order I want.
I did not solve the problem for 12 hour display with AM/PM as this was good enough for my needs.
我也有这个问题，在Excel中找不到好的解决方法。我最后做的(对我有用的)是从一个日期字段中提取24小时的“小时值”，使用= value(文本(A2，“h”)(其中A2包含我正在查找的时间/日期代码)。然后我使用日期代码本身(本例中的列A)作为第一行标签(第一个分组项)，然后添加“hour value”作为辅助行标签/组。这允许自然地利用Pivot表排序/组函数，然后按我想要的顺序添加细节。我没有用AM/PM 12小时的显示来解决这个问题，因为这已经足够满足我的需要了。
#1
1
I also had this problem and found no good way around it in Excel. What I ended up doing (that worked for me) is to extract the 24hour "hour value" from a date field using =VALUE(TEXT(A2,"h")) (where A2 contains the time/date code I'm looking for). I then used the date code itself (Column A in this example) as the first Row Label (first grouped item) then added the "hour value" as a secondary Row Label/Group. This allows leveraging the Pivot Table sort/group function naturally and then adds the detail in the order I want.
I did not solve the problem for 12 hour display with AM/PM as this was good enough for my needs.
我也有这个问题，在Excel中找不到好的解决方法。我最后做的(对我有用的)是从一个日期字段中提取24小时的“小时值”，使用= value(文本(A2，“h”)(其中A2包含我正在查找的时间/日期代码)。然后我使用日期代码本身(本例中的列A)作为第一行标签(第一个分组项)，然后添加“hour value”作为辅助行标签/组。这允许自然地利用Pivot表排序/组函数，然后按我想要的顺序添加细节。我没有用AM/PM 12小时的显示来解决这个问题，因为这已经足够满足我的需要了。