Excel在枢轴表上未正确排序数字
正如标题所说，我的Excel Pivot表并未对数字进行排序。
当我从数据集中创建枢轴表时，目标是按年，每周编号和按pm_value（减少顺序）排序的数据对数据进行排序，如下所示。 “ PM_VALUE”列是一个计算的字段。
| 年度 | 周年 | 关键 | 值1 | PM_VALUE | 值2 |
|---|---|---|---|---|---|
| 2023 | 1 | 2022-SFS-R757-L05-L05 VAC1 | 5.777 | 833 | 6.936 |
| 2023 | 1 | 2022-GS7-R125-L05 | 4.407 | 833 | 5.292 5.292 |
| 2022 | 2022 | -GS7-R008-R008-L05-L05-L05-L05-L05 LAV | 5.347 | 83 | 6.47 |
| 6.422-12022222222222222222222222222222222222222222222222222222222222222222202 | 83 3 | 意值SFS-R735-L05 VAC1 | 5.757 | 830 | 6.938 |
| 2023 | 1 | 2022-GS4-R007-L06 | 13.431 | 829 | 16.195 |
| 2023 | 1 | 2022-SFS-R804-L05-L05 VAC1 | 5.646 | 829 | 6.811 6.811 |
| 2022 | - | GS7-2022-GS7-R083-R083-L083-L06 | 749 | 6.6656 | 6.6656 |
| 6.6656 | 4.983 | 6.656-12222222222222222222222222222222222222222222222222222222222222222222222222229号 | 4.941 | 749 | 6.600 |
问题是PM_VALUE列的零（或可能显示为零的错误），并且这些值无法正确排序。目前，De排序的枢轴表就像下面的DE。
| Year | WeekNum | Key | Value 1 | PM_Value | Value 2 |
|---|---|---|---|---|---|
| 2023 | 1 | 2022-SFS-R757-L05 VAC1 | 5.777 | 833 | 6.936 |
| 2023 | 1 | 2022-SFS-R735-L05 VAC1 | 5.757 | 830 | 6.938 |
| 2023 | 1 | 2022-GS7-R038-L06 | 5.055 | 749 | 6.753 |
| 2023 | 1 | 2022- GS7-R081-L06 | 5.081 | 748 | 6.795 |
| 2023 | 1 | ........................................................ | 144.750 | 29 | - |
| 2022 | 139.684 | L12 | 4.641 | 33 | 2023 |
| 2022 | 1 | L12 | 4.190 | - GS7 | PRJ52 - |
| -PRJ53 - | GS7 | - L06 RGD | 0 | 0 | 0 |
| 2023 | 1 | 2022-SFS-R719-L06 RGD | 0 | 0 | 0 |
| 2023 | 1 | 2022-SFS-R789-L06 RGD | 0 | 0 | 2023 |
| 2022 | 1 | -GS4-R054-L05-L05 VAC | 14.131 | 811 | 17.416 |
| 2023 | 1 | 20222-GS4-R015-L06-R06 | 15.052 | 802 802 | 802 802 18.776 |
| 2023 2023 2023 1 2022 | 1 | 20222-GS4-R0531-GS4-R0314-R05 R2 R2 R2 R2 R2 R2 R2 R2 R2 | 4.856 | 789 | 6.156 |
| 2023 | 1 | 2022-GS4-R058-L06 | 12.639 | 731 | 17.290 |
我已经尝试在枢轴表选项上停用“自定义列表”的“自定义列表”排序复选框，更改排序的顺序（即年/月/键，键/年/年，等等）但是，没有什么能使零保持在排序列的底部。
有没有人经历过这样的事情，可以帮助我？
It's as the title says, my excel pivot table is not sorting numbers corretly.
When I create the pivot table from my dataset, the goal is to have the data sorted by year, week number, and the Keys sorted by the PM_Value (decreasing order) as shown here. The "PM_Value" column is a Calculated Field.
| Year | WeekNum | Key | Value 1 | PM_Value | Value 2 |
|---|---|---|---|---|---|
| 2023 | 1 | 2022-SFS-R757-L05 VAC1 | 5.777 | 833 | 6.936 |
| 2023 | 1 | 2022-GS7-R125-L05 | 4.407 | 833 | 5.292 |
| 2023 | 1 | 2022-GS7-R008-L05 VAC | 5.347 | 833 | 6.421 |
| 2023 | 1 | 2022-SFS-R735-L05 VAC1 | 5.757 | 830 | 6.938 |
| 2023 | 1 | 2022-GS4-R007-L06 | 13.431 | 829 | 16.195 |
| 2023 | 1 | 2022-SFS-R804-L05 VAC1 | 5.646 | 829 | 6.811 |
| 2023 | 1 | 2022-GS7-R083-L06 | 4.983 | 749 | 6.656 |
| 2023 | 1 | 2022-GS7-R107-L06 | 4.941 | 749 | 6.600 |
The problem is that the PM_Value column has zeroes (or possibly errors shown as zeroes), and these values are not sorting correctly. Currently de sorted pivot table is like de one below.
| Year | WeekNum | Key | Value 1 | PM_Value | Value 2 |
|---|---|---|---|---|---|
| 2023 | 1 | 2022-SFS-R757-L05 VAC1 | 5.777 | 833 | 6.936 |
| 2023 | 1 | 2022-SFS-R735-L05 VAC1 | 5.757 | 830 | 6.938 |
| 2023 | 1 | 2022-GS7-R038-L06 | 5.055 | 749 | 6.753 |
| 2023 | 1 | 2022-GS7-R081-L06 | 5.081 | 748 | 6.795 |
| 2023 | 1 | .... | ... | ... | ... |
| 2023 | 1 | 2022-GS7-PRJ52-L12 | 4.641 | 33 | 139.684 |
| 2023 | 1 | 2022-GS7-PRJ53-L12 | 4.190 | 29 | 144.750 |
| 2023 | 1 | 2022-SFS-R715-L06 RGD | 0 | 0 | 0 |
| 2023 | 1 | 2022-SFS-R719-L06 RGD | 0 | 0 | 0 |
| 2023 | 1 | 2022-SFS-R789-L06 RGD | 0 | 0 | 0 |
| 2023 | 1 | 2022-GS4-R054-L05 VAC | 14.131 | 811 | 17.416 |
| 2023 | 1 | 2022-GS4-R015-L06 | 15.052 | 802 | 18.776 |
| 2023 | 1 | 2022-GS4-R031-L05 R2 | 4.856 | 789 | 6.156 |
| 2023 | 1 | 2022-GS4-R058-L06 | 12.639 | 731 | 17.290 |
I already tried to deactivate de "custom lists" sorting check box on the pivot table options, changing the order of the sort (i.e. Year/Month/Key, Key/Year/Month, etc) but nothing makes the zeroes stay on the bottom of the sorted column.
Has anyone experienced anything like that and could help me?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题在于，“ pm_value”的计算是一个划分，其中一些零是div/0误差，显示为零，这些错误与排序混乱。我在计算的字段上使用了IFERROR（CALC，0），问题消失了！
The problem was that the calculation of "PM_Value" is a division, and some of the zeroes were DIV/0 errors showing as zeroes and these errors that were messing with the sort. I used a IFERROR(calc,0) on the Calculated Field and the problem disappeared!