数据透视表无法找到当年1月到上一年12月之间的差异. Excel 2010 [英] Pivot table cannot find difference between January current year to December of previous year. Excel 2010
问题描述
我已经有几周时间要制作我的第一个数据透视表，不能相信它们的功能，但是遇到了我无法克服的障碍.
I am weeks into making my first ever pivot tables, cant believe the power of them but have hit a snag I cannot overcome.
我有一个工作表，其中有一个包含原始数据的列(手动估算)
I have a worksheet which has a column with raw data (manually imputed)
然后我还有另外2列，用于计算与上个月的差异和％差异.效果很好.
Then I have another 2 columns that calculate the difference from the previous month and the % difference. This works fine.
您会看到2015年1月为空白，因为差异字段正在寻找上个月，而1月是第一个月.
As you can see January 2015 is blank as the difference field is looking for the previous month and January is the first month.
如何了解2014年12月的情况
how can I get it to understand to look at December 2014
推荐答案
当存在两种不同级别的分组时，即年和月，则无法使Excel将一组的最后一项与第一项进行比较上一组的人.
When there are two different levels of grouping, i.e. Year and Month, it is not possible to get Excel to compare the last item of one group with the first item of the previous group.
Excel将附加分组级别视为不同的字段，在查看数据透视表字段时可以看到该字段.日期的年份显示为单独的字段:
Excel treats the additional level of grouping as a different field, which is visible when looking at the PivotTable fields. The Years of the Date show as a separate field:
在选择计算差异时，Excel只能与同一字段中的值进行比较.请注意，您可以与特定月份的值进行比较，但不能与特定年份的特定月份进行比较:
When choosing to calculate the difference, Excel can only compare to values within the same field. Note that you can compare to a specific month value, but not a specific month of a specific year:
类似地，即使人类逻辑知道它是整个序列中的先前值，Excel也无法将其与先前分组中的值进行比较.
Similarly, Excel can't compare to values from the previous grouping, even though human-logic knows it's the previous value in the overall sequence.
要查看年份之间每个月之间的差异，必须将所有Year-Month(月份)都放在一个字段中.在正常日期使用数据透视表分组是不可能的.
To see the difference between each month across years, it's necessary to have all Year-Months in a single field. This isn't possible using PivotTable grouping on normal dates.
要解决此问题，需要将一个新字段添加到数据中，该字段每个年/月包含一个值.然后，数据透视表将根据这些Year-Month值自然地对数据进行分组.
The workaround requires a new field to be added to the data which contains a single value per Year-Month. The PivotTable will then naturally group the data by these Year-Month values.
有两种方法可以实现:
- 计算每年每个月的第一天
使用此公式插入另一列(指的是A2单元格中的日期):
Insert another column with this formula (referring to a date in the A2 cell):
=Date(Year(A2),Month(A2),1)
这通过将每个日期视为月份的第一天来在数据进入数据透视表之前执行分组.在数据透视表中，默认情况下将显示完整日期.您可以将自定义数字格式"应用于该字段，以仅显示月份和年份，例如:"mmm yyyy"
This performs the grouping before the data goes into the PivotTable by treating every date as the first of the month. In the PivotTable, the full date will appear by default. You can apply Custom Number Formatting to the field to display only the Month and Year, for example: "mmm yyyy"
当按月分组时，这是一个更好的解决方案，因为它允许更多的格式设置选项.
This is a better solution when grouping by months as it allows more formatting options.
- 计算每个年月的文本值
使用以下公式插入一列:
Insert a column with this formula:
=TEXT(A2,"yyyy mm")
例如，以下数据:
可以生成此数据透视表(先加上和再加上差):
Can generate this PivotTable (with sums and then differences):
文本解决方案的缺点是，如果希望数据透视表按时间顺序自动对值进行排序，则必须先数字指定年"，然后再指定月"(分别填充到4位和2位数字).
The text solution has the drawback that you must specify the Year and then Month numerically (and padded to 4 and 2 digits respectively) if you want the PivotTable to automatically order the values in chronological order.
文本解决方案的好处是更大的灵活性.例如，您可以每两个月使用更高级的公式进行分组，或者通过串联将实际数据的两个字段分组在一起.
The benefit of the text solution is greater flexibility. For example, you could group bi-monthly with more advanced formulas or group two fields of actual data together by concatenation.
这篇关于数据透视表无法找到当年1月到上一年12月之间的差异. Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
