I have a pivot table with 2 columns spanning dates (Create Date & Target Date). I am unable to sort any field within my pivot table, but I need to be able to sort the date fields. I have double checked that the format is date (when changed to number format, the date serial is shown and ISTEXT shows FALSE)
我有一个数据透视表,其中有2列跨越日期(创建日期和目标日期)。我无法对数据透视表中的任何字段进行排序,但我需要能够对日期字段进行排序。我已经仔细检查过格式是日期(当更改为数字格式时,显示日期序列,ISTEXT显示为FALSE)
Photos of field list and relevant columns below. The bottom photo is just to drive home the point that the format is indeed date.
字段列表和相关列的照片如下。底部的照片只是为了让格式确实是日期。
Will this not sort due to all fields being displayed under rows? Is there another way to sort this with the current set up?
由于所有字段都显示在行下,这不会排序吗?有没有其他方法可以使用当前设置对此进行排序?
I am open to VBA solution as I already have a macro that loops through each pivot for other reasons.
我对VBA解决方案持开放态度,因为我已经有一个宏,它可以出于其他原因遍历每个数据透视表。
1 个解决方案
#1
1
Posting a quick synopsis of what was uncovered in the comments:
发表评论中发现的内容的快速概要:
tl;dr : If you have a pivot table in tabular form, sorts applied to columns will only apply to sub-grouped fields and will not sort all columns of the table (therefore the first column is the only field capable of reordering all rows of the table.
tl; dr:如果您有一个表格形式的数据透视表,应用于列的排序将仅适用于子分组字段,并且不会对表的所有列进行排序(因此第一列是唯一能够重新排序所有行的字段桌子。
If you start with the table completely unsorted, and sort the first column, it will reorder all rows across all columns, because all other columns are sub-columns(children) of the first column:
如果您从完全未排序的表开始,并对第一列进行排序,它将重新排序所有列中的所有行,因为所有其他列都是第一列的子列(子):
Unsorted Starting Pivot Table 
未分类的起始数据透视表
First Column Sorted Pivot Table 
第一列分类数据透视表
Subsequently sorting the 2nd column (in this example), effectively does nothing to alter the table, because the values in Target Date are already tied to the values in Created Date 1-to-1. So they are locked to the sorting order chosen for column 1.
随后对第二列进行排序(在此示例中),实际上无法更改表,因为目标日期中的值已经与创建日期1对1中的值相关联。因此,它们被锁定到为第1列选择的排序顺序。
Sorting the third column in this case however, does alter the structure of the remaining fields, because there is a 2-to-1 relationship of Task Number to Target Date. The two versions of the table you can get out of sorting the third column follow. Notice that the are being sorted within the duplication of Target Date:
但是,在这种情况下对第三列进行排序确实会改变其余字段的结构,因为任务编号与目标日期之间存在2对1的关系。您可以从第三列的排序中得到的表的两个版本如下。请注意,正在重复目标日期内进行排序:
Task Number Sorted Largest to Smallest 
任务编号最大排序到最小
Task Number Sorted Smallest to Largest 
任务编号从最大到最大排序
#1
1
Posting a quick synopsis of what was uncovered in the comments:
发表评论中发现的内容的快速概要:
tl;dr : If you have a pivot table in tabular form, sorts applied to columns will only apply to sub-grouped fields and will not sort all columns of the table (therefore the first column is the only field capable of reordering all rows of the table.
tl; dr:如果您有一个表格形式的数据透视表,应用于列的排序将仅适用于子分组字段,并且不会对表的所有列进行排序(因此第一列是唯一能够重新排序所有行的字段桌子。
If you start with the table completely unsorted, and sort the first column, it will reorder all rows across all columns, because all other columns are sub-columns(children) of the first column:
如果您从完全未排序的表开始,并对第一列进行排序,它将重新排序所有列中的所有行,因为所有其他列都是第一列的子列(子):
Unsorted Starting Pivot Table
未分类的起始数据透视表
First Column Sorted Pivot Table
第一列分类数据透视表
Subsequently sorting the 2nd column (in this example), effectively does nothing to alter the table, because the values in Target Date are already tied to the values in Created Date 1-to-1. So they are locked to the sorting order chosen for column 1.
随后对第二列进行排序(在此示例中),实际上无法更改表,因为目标日期中的值已经与创建日期1对1中的值相关联。因此,它们被锁定到为第1列选择的排序顺序。
Sorting the third column in this case however, does alter the structure of the remaining fields, because there is a 2-to-1 relationship of Task Number to Target Date. The two versions of the table you can get out of sorting the third column follow. Notice that the are being sorted within the duplication of Target Date:
但是,在这种情况下对第三列进行排序确实会改变其余字段的结构,因为任务编号与目标日期之间存在2对1的关系。您可以从第三列的排序中得到的表的两个版本如下。请注意,正在重复目标日期内进行排序:
Task Number Sorted Largest to Smallest
任务编号最大排序到最小
Task Number Sorted Smallest to Largest
任务编号从最大到最大排序