Excel Pivot Tables 简明教程

Excel Pivot Tables - Sorting Data

你可以对数据透视表中的数据进行排序，这样可以让你更容易找到你想分析的项。你可以按照从最小值到最大值或从最大值到最小值或者你选择的任何其它自定义顺序来对数据进行排序。

You can sort the data in a PivotTable so that it will be easy for you to find the items you want to analyze. You can sort the data from lowest to highest values or highest to lowest values or in any other custom order that you choose.

思考以下数据透视表，你在其中区域明智、销售人员明智和月份明智地总结了销售数据。

Consider the following PivotTable wherein you have the summarized sales data region-wise, salesperson-wise and month-wise.

sum of order amount

Sorting on Fields

你可以在上面的数据透视表中对位于行或列中的字段进行排序 - 区域、销售人员和月份。

You can sort the data in the above PivotTable on Fields that are in Rows or Columns – Region, Salesperson and Month.

若要使用字段销售人员对数据透视表进行排序，请按下列步骤进行：

To sort the PivotTable with the field Salesperson, proceed as follows −

  1. Click the arrow in the Row Labels.

  2. Select Salesperson in the Select Field box from the dropdown list.

sorting

显示以下排序选项 −

The following sorting options are displayed −

  1. Sort A to Z.

  2. Sort Z to A.

  3. More Sort Options.

此外，默认情况下，按照升序对销售人员字段进行排序。单击 Sort Z to A 。销售人员字段将按降序排序。

Further, the Salesperson field is sorted in ascending order, by default. Click Sort Z to A. The Salesperson field will be sorted in descending order.

sort z to a

同样，您可以单击列标签中的箭头来按字段排序 – 月份。

In the same way, you can sort the field in column – Month, by clicking on the arrow in the column labels.

Sorting on Subtotals

假设要根据总订单数量对数据透视表进行排序 – 每个区域从最高到最低。也就是说，你要根据子总数对数据透视表进行排序。

Suppose you want to sort the PivotTable based on total order amount – highest to lowest in every Region. That is, you want to sort the PivotTable on subtotals.

sorting on subtotals

您可以看到，子总数没有箭头。您仍然可以按如下方式对子总数对数据透视表进行排序 −

You can see that there is no arrow for subtotals. You can still sort the PivotTable on subtotals as follows −

  1. Right-click on the subtotal of any of the Salespersons in the Grand Total column.

  2. Select Sort from the dropdown list.

  3. Another dropdown list appears with the sorting options – Sort Smallest to Largest, Sort Largest to Smallest and More Sort Options. Select Sort Largest to Smallest.

grand total

在每个区域中，按从最高值到最低值对“总计”列中的子总数进行排序。

The subtotals in the Grand Total column are sorted from highest to lowest values, in every region.

click sort

同样，如果您要按区域对子总数对数据透视表进行排序，请执行以下操作 −

Likewise, if you want to sort the PivotTable on subtotals region wise, do the following −

  1. Right click on the subtotal of any of the regions in the Grand Total column.

  2. Click Sort in the dropdown list.

  3. Click Sort Largest to Smallest in the second dropdown list. The PivotTable will get sorted on subtotals region-wise.

total amount

正如您所观察到的，南方拥有最高的订单金额，而北方拥有最低的订单金额。

As you can observe, South has the highest order amount while North has the lowest.

您还可以按月总金额对数据透视表进行排序，如下所示 −

You can also sort the PivotTable based on the total amount month wise as follows −

  1. Right click on any of the Subtotals in the Grand Total row.

  2. Select Sort from the dropdown list.

  3. Select Sort Largest to Smallest from the second dropdown list.

数据透视表按总金额按月排序。

The PivotTable will be sorted on total amount month wise.

more sort options

您可以观察到，2 月的订单金额最高，而 3 月的订单金额最低。

You can observe that February has highest order amount while March has the lowest.

More Sort Options

假设您想按 1 月的总金额按区域对数据透视表进行排序。

Suppose you want to sort the PivotTable on total amount region wise in the month of January.

  1. Click on the arrow in Row Labels.

  2. Select More Sort Options from the dropdown list. The Sort (Region) dialog box appears.

region

如您所见，在“摘要”下，当前的排序顺序为升序按区域排序。升序 (A 到 Z) 按排序选项选中。在该框下方，显示 Region 。

As you can observe, under Summary, the current Sort order is given as Sort Region in ascending order. Ascending (A to Z) by is selected under Sort Options. In the box below that, Region is displayed.

  1. Click the box containing Region.

  2. Click Sum of Order Amount.

more options

单击 More Options 按钮。将出现 More Sort Options (Region) 对话框。

Click the More Options button. The More Sort Options (Region) dialog box appears.

values in selected column

如您所见，“排序依据”下选择总计。在“摘要”下，当前的排序顺序为 Sort Region by Sum of Order Amount 升序。

As you can observe, under Sort By, Grand Total is selected. Under Summary, the current sort order is given as Sort Region by Sum of Order Amount in ascending order.

  1. Click Values in selected column: under Sort By.

  2. In the box below that, type B5.

ascending order

如您所见，“摘要”下，当前的排序顺序如下所述 −

As you can observe, under Summary, the current sort order is given as follows −

  1. Sort Region by Sum of Order Amount in ascending order using values in this column: January. Click OK.

  2. The Sort (Region) dialog box appears. Select Descending (Z to A) by: under Sort Options.

under summary

在“摘要”下，当前的排序顺序如下所述 −

Under Summary, the current sort order is given as follows −

按订单金额之和降序按区域排序，使用此列中的值：1 月份。单击“确定”。将按 1 月份的值对数据透视表按区域进行排序。

Sort Region by Sum of Order Amount in descending order, using values in this column: January. Click OK. The PivotTable will be sorted on region, using values in January.

sorting data manually

如您所见，在 1 月份，西部地区的订单金额最高，而北部的订单金额最低。

As you can observe, in the month of January, West has the highest order amount while North has the lowest.

Sorting Data Manually

在数据透视表中，数据将根据您选择的排序选项自动排序。这称为自动排序。

In the PivotTable, the data is sorted automatically by the sorting option that you have chosen. This is termed as AutoSort.

将光标放置在行标签或列标签中的箭头处。

Place the cursor on the arrow in Row Labels or Column Labels.

select manual

AutoSort 出现，显示数据透视表中每个字段的当前排序顺序。现在，假设你要按区域字段对字段进行排序——东、西、北和南。你可以按照以下方式手动完成：

AutoSort appears, showing the current sort order for each of the fields in the PivotTable. Now, suppose you want to sort the field Region in the order – East, West, North and South. You can do this manually, as follows −

  1. Click the arrow in Row Labels.

  2. Select Region in the Select Field box from the dropdown list.

  3. Click More Sort Options. The Sort (Region) dialog box appears.

  4. Select Manual (you can drag items to rearrange them).

  5. Click OK.

select region

在“摘要”下，当前排序顺序以“将“区域”字段的项目拖动到此处以按任意顺序显示它们”给出。

Under Summary, the current sort order is given as Drag items of the Region field to display them in any order.

单击“东”，然后将其拖动到顶部。在你拖动“东”时，一系列横向绿线会出现在整行中。

Click on the East and drag it to the top. While you are dragging East, a horizontal green bar appears across the entire row moves.

click on east

用“区域”字段的其他项目重复拖动，直到获得所需排列。

Repeat the dragging with other items of the Region field until you get the required arrangement.

repeat

您可以观察到以下内容 −

You can observe the following −

  1. The items of the nested field – Salesperson also move along with the corresponding Region field item. Further, the values in the other columns also moved accordingly.

  2. If you place the cursor on the arrow in Row Labels or Column Labels, AutoSort appears showing the current sort order of the fields Salesperson and Month only. As you have sorted Region field manually, it will not show up in AutoSort.

Note ——你无法对数据透视表字段列表中位于“∑ VALUES”区域的字段项目进行此手动拖动。因此，你无法拖动此数据透视表中的“订单金额总和”值。

Note − You cannot use this manual dragging of items of the field that is in ∑ VALUES area of the PivotTable Fields list. Therefore, you cannot drag the Sum of Order Amount values in this PivotTable.

Setting Sort Options

在上一节中，你学习了如何将字段的排序选项设置为手动。你可以设置如下所示的更多排序选项：

In the previous section, you have learnt how to set the sorting option for a field to manual. You have some more sort options that you can set as follows −

  1. Click the arrow in Row Labels.

  2. Select Region in the Select Field box.

  3. Click More Sort Options. The Sort (Region) dialog box appears.

  4. Click the More Options button.

将出现“更多排序选项（区域）”对话框。你可以在此对话框中设置更多排序选项。

More Sort Options (Region) dialog box appears. You can set more sort options in this dialog box.

click ok

在“自动排序”下，你可以选中或取消选中该框——每次更新报表时自动排序，以便在数据透视表数据更新时允许或停止自动排序。

Under AutoSort, you can check or uncheck the box - Sort automatically every time the report is updated, to allow or stop automatic sorting whenever the PivotTable data is updated.

  1. Uncheck the box – Sort automatically every time the report is updated.

如今，“第一个键的排序顺序”选项变得可用。你可以使用此选项选择你想要使用的自定义的顺序。

Now, First key sort order option becomes available. You can use this option to select the custom order you want to use.

  1. Click the box under First key sort order.

click the box

正如你能观察到的，在一周中某天和一年的某月在下拉列表中提供了自定义列表。你可以使用任何这些，或者你可以使用你自己的自定义列表，例如高、中、低或按字母顺序排列的 S、M、L、XL 尺寸列表。

As you can observe, day-of-the-week and month-of-the year custom lists are provided in the dropdown list. You can use any of these, or you can use your own custom list such as High, Medium, Low or the sizes list S, M, L, XL that are not in alphabetical order.

你可以在功能区的“文件”选项卡中创建自定义列表。文件 → 选项。在 Excel 选项对话框中，单击高级，并浏览至常规。你会在“创建用于排序和填充序列的列表”旁边找到“编辑自定义列表”按钮。

You can create your custom lists from the FILE tab on the Ribbon. FILE → Options. In the Excel Options dialog box, click on advanced and browse to General. You will find the Edit Custom Lists button next to Create lists for use in sort and fill sequences.

advanced

要注意的是，当你在数据透视表中更新（刷新）数据时，不会保留自定义列表的排序顺序。

Note that a custom list sort order is not retained when you update (refresh) data in your PivotTable.

在“排序依据”下方，你可以单击选定列中的 Grand Total or Values 来按这些值排序。当你将排序设置为“手动”时，此选项不可用。

Under Sort By, you can click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

Points to consider while sorting PivotTables

当你对数据透视表中的数据进行排序时，记住以下几点 −

When you sort data in a PivotTable, remember the following −

  1. Data that has leading spaces will affect the sort results. Remove any leading spaces before you sort the data.

  2. You cannot sort case-sensitive text entries.

  3. You cannot sort data by a specific format such as cell or font color.

  4. You cannot sort data by conditional formatting indicators, such as icon sets.