vba 使用VBA按最后一列降序对excel 2007数据透视表进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6521250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sort excel 2007 pivot table descending by last column with VBA
提问by Hans
Thanks guys! I think I may have explained my problem a bit vague. I'm attaching two print screens in order to illustrate my problem.
谢谢你们！我想我可能已经解释了我的问题有点含糊。我附上两个打印屏幕以说明我的问题。
Print screens: http://imageshack.us/photo/my-images/804/illustrasjon.jpg
打印屏幕：http: //imageshack.us/photo/my-images/804/illustrasjon.jpg
My pivot table shows stops [minutes] due to specified reasons on a production line. The 'Totalt' column of the week (in which the number of production days vary) is what I wish to focus on in order to allocate improvement efforts to the right place. The sorting will be performed on a daily basis by others than me, and they really should have a button to press rather than sorting via the options bar.
我的数据透视表显示由于生产线上的特定原因而停止 [分钟]。一周的“总计”列（其中生产天数有所不同）是我希望关注的内容，以便将改进工作分配到正确的位置。除了我之外，其他人每天都会进行排序，他们真的应该有一个按钮可以按下，而不是通过选项栏进行排序。
NB! The 'Sum' column is not part of the pivot table. That's just a calculation of the 'Totalt' column to a fixed column in order to make a sector chart of the sorted result, since it's not possible to make a pivot chart from the 'Totalt' column.
注意！“总和”列不是数据透视表的一部分。这只是将“总计”列计算为固定列，以便制作排序结果的扇区图，因为无法从“总计”列制作数据透视图。
The 'M?l per dag' column is just the daily (minutes) goal within which I wish to keep the production stops of a specified reason, so this should be kept out of the 'Totalt' column, but this is less important.
'M?l per dag' 列只是我希望保持特定原因的生产停止的每日（分钟）目标，因此应将其排除在 'Totalt' 列之外，但这并不重要。
I have a pivot table filtered by weeks showing any number of days from one to five. The total column to the right that sums up the values of the days are therefore "moving", and this is why I cannot simply record a macro. I want to create a macro that sort the pivot table descending by the total column.
我有一个按周过滤的数据透视表，显示从一到五的任意天数。右边汇总天数的总列因此“移动”，这就是为什么我不能简单地记录一个宏。我想创建一个宏，按总列降序对数据透视表进行排序。
I do not speak the international language of VBA, but imagine that this is fairly easy for someone who does.
我不会说 VBA 的国际语言，但想象一下，这对于会说的人来说相当容易。
Immensely grateful to anyone willing to write the code for me!
非常感谢任何愿意为我编写代码的人！
Best regards, Hans
最好的问候，汉斯
回答by Alain
Hans
汉斯
To start, you should use the built in sort feature on the pivot table as illustrated in the screenshots below.
首先，您应该使用数据透视表上的内置排序功能，如下面的屏幕截图所示。
Select the column you wish to sort by:
选择要排序的列：
This might be a little problematic for you because the pivot table doesn't automatically refresh when your data changes. If you want it to update in real time, then you will want to add the "Worksheet_Change" event handler to the VBA code. To do this, hit Alt+F11 to bring up VBA, and on the worksheet containing your source data enter the following:
这对您来说可能有点问题，因为数据透视表不会在您的数据更改时自动刷新。如果您希望它实时更新，那么您需要将“Worksheet_Change”事件处理程序添加到 VBA 代码中。为此，请按 Alt+F11 调出 VBA，然后在包含源数据的工作表上输入以下内容：
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
PivotTables("PivotTable1").RefreshTable
Application.EnableEvents = True
End Sub
Here "PivotTable1" will change to match whatever the name of your pivot table is. This name appears in the Ribbon when you have the pivot table selected.
此处“PivotTable1”将更改以匹配您的数据透视表的名称。当您选择了数据透视表时，此名称会出现在功能区中。
Now, as you change your data, the table will automatically re-sort as you go.
现在，当您更改数据时，表格将自动重新排序。
回答by rajah9
Hans, welcome to SO. You did not specify which version of Excel in your tags, but here are some Excel 2007 solutions.
汉斯，欢迎来到 SO。您没有在标签中指定哪个版本的 Excel，但这里有一些 Excel 2007 解决方案。
It is possible (and helpful!) to do this without VBA. You may be interested in this Microsoft solution.
在没有 VBA 的情况下执行此操作是可能的（并且很有帮助！）。您可能对这个Microsoft 解决方案感兴趣。
I would recommend that you select the range of the pivot table with the totals, exclude the header and the grand total, and then go to the Data tab and choose the Sort Z->A. This will sort the totals in descending order (and of course, change the headers to the left).
我建议您选择包含总计的数据透视表的范围，排除标题和总计，然后转到数据选项卡并选择排序 Z->A。这将按降序对总数进行排序（当然，将标题更改为左侧）。
You can also record a macro to do this if you would like to perform this sort on a regular basis, but that may be more trouble than it is worth.
如果您想定期执行这种排序，您也可以录制一个宏来执行此操作，但这可能会带来更多的麻烦。
