Excel Data Analysis 简明教程

Excel Data Analysis - Sorting

对数据进行排序是数据分析的一个组成部分。您可以按字母顺序排列姓名列表，从最高到最低排列销售数字列表，或者按颜色或图标对行进行排序。对数据进行排序有助于您快速查看和更好地理解您的数据，组织和查找所需的数据，并最终做出更有效的决策。

Sorting data is an integral part of Data Analysis. You can arrange a list of names in alphabetical order, compile a list of sales figures from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

您可以按列或按行进行排序。您使用的大多数排序都将是按列排序。

You can sort by columns or by rows. Most of the sorts that you use will be column sorts.

您可以按以下内容在多个列中对数据进行排序

You can sort data in one or more columns by

  1. text (A to Z or Z to A)

  2. numbers (smallest to largest or largest to smallest)

  3. dates and times (oldest to newest and newest to oldest)

  4. a custom list (E.g. Large, Medium, and Small)

  5. format, including cell color, font color, or icon set

表的排序条件会与工作簿一起保存，这样您每次打开工作簿时都可以对该表重新应用排序。不会为单元格区域保存排序条件。对于多列排序或创建需要花费很长时间的排序，您可以将区域转换为表格。然后，您可以在打开工作簿时重新应用该排序。

Sort criteria for a table are saved with the workbook such that you can reapply the sort to that table every time you open the workbook. Sort criteria are not saved for a range of cells. For multicolumn sorts or for sorts that take a long time to create, you can convert the range to a table. Then, you can reapply the sort when you open a workbook.

在以下各部分中的所有示例中，您只会找到表格，因为对表格进行排序更有意义。

In all the examples in the following sections, you will find tables only, since it is more meaningful to sort a table.

Sort by Text

你可以使用包含文本的列对表格进行排序。

You can sort a table using a column containing text.

下表包含有关组织中员工的信息（您只能看到数据中的前几行）。

The following table has information about employees in an organization (You are able to see only the first few rows in the data).

sort by text

  1. To sort the table by the column title that contains text, click the header of the column – Title.

  2. Click the Data tab.

  3. In the Sort & Filter group, click Sort A to Z

sort and filter

将按标题列对表格按升序字母数字顺序进行排序。

The table will be sorted by the column – Title in the ascending alphanumeric order.

Note − 您可以通过单击 Sort Z to A ，按降序字母数字顺序进行排序。您还可以使用区分大小写的选项进行排序。请参阅下方给出的 Sort by a Custom List 部分。

Note − You can sort in the descending alphanumeric order, by clicking Sort Z to A. You can also sort with case-sensitive option. Go through the Sort by a Custom List section given below.

sort by custom list

Sort by Numbers

要按包含数字的列 ManagerID 对表进行排序，请按照下面给出的步骤进行操作 -

To sort the table by the column ManagerID that contains numbers, follow the steps given below −

  1. Click the header of the column – ManagerID.

  2. Click the Data tab.

  3. In the Sort & Filter group, click Sort A to Z

sort by numbers

ManagerID 列将按升序数字顺序进行排序。您可以通过单击从 Z 到 A 排序，按降序数字顺序进行排序。

The column, ManagerID will be sorted in the ascending numeric order. You can sort in the descending numeric order, by clicking Sort Z to A.

managerid

Sort by Dates or Times

要按包含日期的列 HireDate 对表进行排序，请按照以下步骤操作 -

To sort the Table by the column HireDate that contains Dates, follow the steps given below −

  1. Click the Header of the column – HireDate.

  2. Click Data tab.

  3. In the Sort & Filter group, click Sort A to Z as shown in the screen shot given below −

sort by dates or times

按列排序 – HireDate，按最旧到最新的顺序对日期进行排序。您可以通过单击 Sort Z to A ，按从最新到最旧的顺序对日期排序。

The column – HireDate will be sorted with the dates sorted from oldest to newest. You can sort the dates from newest to oldest, by clicking Sort Z to A.

hiredate

Sort by Cell Color

要按包含带颜色的单元格（条件格式）的列总分对表进行排序 -

To sort the table by the column total marks that contains cells with colors (Conditionally Formatted) −

  1. Click the Header of the column – Total Marks.

  2. Click Data tab.

  3. In the Sort & Filter group, click Sort. The Sort dialog box appears.

sort by cell color

  1. Choose Sort By as Total Marks, Sort on as Cell Color and specify the color green in Order. Click Add Level.

  2. Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Yellow in Order. Click Add Level.

  3. Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Red in Order.

choose sort by

  • 总分列将按“顺序”中指定的单元格颜色进行排序。

The column – Total Marks will be sorted by the cell color as specified in the Order.

total marks sorted by cell color

Sort by Font Color

要对包含字体颜色（条件格式）的单元格的表格中的总分列进行排序，请执行以下操作：

To sort the column Total Marks in the table, that contains cells with font colors (conditionally formatted) −

  1. Click the header of the column – Total Marks.

  2. Click Data tab.

  3. In the Sort & Filter group, click Sort. The Sort dialog box appears.

  4. Choose Sort By as Total Marks, Sort On as Font Color and specify the color green in Order. Click Add Level.

  5. Choose Sort By as Total Marks, Sort On as Font Color and specify the color yellow in Order. Click Add Level.

  6. Choose Sort By as Total Marks, Sort On as Font Color and specify the color red in Order.

sort by font color

  • 总分列将按“顺序”中指定的字体颜色进行排序。

The column – Total Marks is sorted by the font color as specified in the Order.

total marks sorted by font color

Sort by Cell Icon

要按包含单元格图标（条件格式）的总分列对表格进行排序，请按照以下步骤操作：

To sort the table by the column Total Marks that contains cells with Cell Icons (Conditionally Formatted), follow the steps given below −

  1. Click the Header of the column – Total Marks.

  2. Click Data tab.

  3. In the Sort & Filter group, click Sort. The Sort dialog box appears.

  4. Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Click Add Level.

  5. Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Click Add Level.

  6. Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order.

sort by cell icon

  • 总分列将按“顺序”中指定的单元格图标进行排序。

The column – Total Marks will be sorted by Cell Icon as specified in the Order.

total marks sorted by cell icon

Sort by a Custom List

您可以创建自定义列表，并按自定义列表对表格进行排序。

You can create a custom list and sort the table by the custom list.

在下面给出的表格中，你会发现一个带标题 - 排名的指示器列。它具有高、中和低的值，基于总分相对于整个范围的位置。

In the table given below, you find an indicator column with title – Position. It has the values high, medium and low based on the position of total marks with respect to the entire range.

sort by a custom list

现在，假设您想要对排名列进行排序，所有高值在顶部，所有低值在底部，所有中等值在中间。这意味着您想要的是低、中、高。使用 Sort A to Z ，您获得的高、低和中等的顺序。另一方面，使用 Sort Z to A ，您获得低、中和高的顺序。

Now, suppose you want to sort the column - Position, with all High values on top, all low values at bottom, and all medium values in between. That means the order you want is low, medium and high. With Sort A to Z, you get the order high, low and medium. On the other hand, with Sort Z to A, you get the order medium, low and high.

您可以解决方法是创建自定义列表。

You can resolve this is to create a custom list.

  1. Define the order for the custom list as high, medium and low in a range of cells as shown below.

  2. Select that Range.

select range

  1. Click the File tab.

  2. Click Options. In the Excel Options dialog box, Click Advanced.

  3. Scroll to the General.

  4. Click Edit Custom Lists.

edit custom lists

Edit Custom Lists 对话框将出现。工作表中选择的范围将出现在 Import list from cells Box 中。单击 Import 。

The Edit Custom Lists dialog box appears. The select range in worksheet appears in the Import list from cells Box. Click Import.

import list

你的自定义列表已添加到 {s2}。单击确定。

Your custom list is added to the Custom Lists. Click OK.

custom lists

下一步是用此自定义列表对表格进行排序。

The next step is to sort the table with this Custom List.

  1. Click the Column – Position. Click on Sort. In the Sort dialog box, ensure Sort By is Position, Sort On is Values.

  2. Click on Order. Select Custom List. Custom Lists dialog box appears.

  3. Click on the High, Medium, Low Custom List. Click on OK.

sort table with custom list

在 {s10} 对话框中，{s11} 框中会出现 {s12}。单击确定。

In the Sort dialog box, in the Order Box, High, Medium, Low appears. Click on OK.

order box

表格将按照定义的顺序排序 - 高、中、低。

The table will be sorted in the defined order – high, medium, low.

table sorted

你可以根据以下数值创建自定义列表 −

You can create Custom Lists based on the following values −

  1. Text

  2. Number

  3. Date

  4. Time

你不能根据格式创建自定义列表，即，按单元格/字体颜色或单元格图标创建。

You cannot create custom lists based on format, i.e. by cell / font color, or cell icon.

Sort by Rows

你也可以按行对表格进行排序。按照以下步骤进行 −

You can sort a table by rows also. Follow the steps given below −

  1. Click the row you want to sort the data.

  2. Click Sort.

  3. In the Sort dialog box, Click Options. The Sort Options dialog box opens.

  4. Under Orientation, click Sort from left to right. Click OK.

sort by rows

  1. Click Sort by row. Select the row.

  2. Choose values for Sort On and Largest to Smallest for Order.

sort on

按选择的行降序排列数据。

The data will be sorted by the selected row in a descending order.

descending order

Sort by more than one Column or Row

可以按多列或多行对表进行排序。

You can sort a table by more than one column or row.

  1. Click the Table.

  2. Click Sort.

  3. In the Sort dialog box, specify the column by which you want to sort first.

在下面提供的屏幕截图中，选择了 Sort By 标题、 Sort On 值、 Order A – Z。

In the screen shot given below, Sort By Title, Sort On Values, Order A – Z are chosen.

sort by

  1. Click Add Level in the Sort dialog box. The Then By dialog appears.

  2. Specify the column by which you want to sort next.

  3. In the screen shot given below, Then By HireDate, Sort On Values, Order Oldest to Newest are chosen.

  4. Click OK.

then by hiredate

数据将按标题以升序字母数字顺序排序，然后按雇用日期排序。你将看到按标题排序的员工数据，并且在每个标题类别中，按资历顺序排序。

The data will be sorted for Title in the ascending alphanumeric order and then by HireDate. You will see the employee data sorted by title, and in each title category, in the seniority order.

seniority order