如何在Microsoft Excel中对值进行排序

Excel has “Sort A to Z” and “Sort Z to A” features that let you sort values alphabetically or numerically, and a “Custom Sort” feature that lets you sort using multiple criteria.  Here’s how to use them.

Excel具有“将A排序为Z”和“将Z排序为A”的功能，可让您按字母或数字顺序对值进行排序，以及“自定义排序”的功能，可让您使用多个条件进行排序。 这是使用方法。

让我们看一个例子 (Let’s Look at an Example)

In this example, we have chosen nine chemical elements from the periodic table, and we will sort the data based on the cell contents using the “Sort A to Z” feature and the “Custom Sort” feature.

在此示例中，我们从元素周期表中选择了九种化学元素，然后将使用“从A到Z排序”功能和“自定义排序”功能根据单元格内容对数据进行排序。

The “Sort & Filter” drop-down is located on the far right of the “Home” tab.

“排序和筛选”下拉列表位于“主页”选项卡的最右侧。

The drop-down has several features listed, but we are focusing on the first three.

该下拉列表列出了几个功能，但我们主要关注前三个功能。

Here is our example data, taken from the periodic table. Each “Element” has information that is unique to it, such as its “Symbol” and “Atomic Number.”  But each element also belongs to other categories, such as its “Phase at STP” (i.e., whether it is a solid, liquid, or gas in a room at standard temperature and pressure) and its “Element Category” on the period table.

这是我们的示例数据，取自元素周期表。 每个“元素”具有唯一的信息，例如其“符号”和“原子序号”。 但是每个元素还属于其他类别，例如其“在STP处的相”(即，在标准温度和压力下在室内是固体，液体还是气体)，以及在周期表中的“元素类别”。

Let’s start simple and sort the data by the “Atomic Number” column. Start by selecting the column (you can include the header row if your data has one).

让我们开始简单，并按“原子序数”列对数据进行排序。 首先选择列(如果数据包含一个，则可以包括标题行)。

Now navigate to the “Sort & Filter” drop-down and select either the first or second option. In this example, we will sort from the lowest to highest numerical value using the “Sort A to Z” feature.

现在，导航到“排序和筛选”下拉菜单，然后选择第一个或第二个选项。 在此示例中，我们将使用“从A到Z的排序”功能从最低到最高数值进行排序。

A “Sort Warning” box will appear if there are other columns of data Excel thinks you might want to include in your sort. Let’s examine the results of the “Continue with the current selection” option, first. Click that option and press the “Sort” button.

如果Excel认为您可能希望将其他数据列包含在排序中，则会出现“排序警告”框。 首先，让我们检查“继续当前选择”选项的结果。 单击该选项，然后按“排序”按钮。

You can see that the “Atomic Number” column has been sorted from lowest to highest, but that the corresponding information in the other columns has not changed.  This could be problematic if you wanted to sort all of the columns.  So, before moving on, look at some of the rows to see if they make sense.  In this case, it is obvious that the other columns have not been sorted because Hydrogen, the element with the lowest “Atomic Number,” is shown as having the “Atomic Number” of Lead.

您可以看到“原子序号”列已从最低到最高排序，但其他列中的相应信息未更改。 如果要对所有列进行排序，则可能会出现问题。 因此，在继续之前，请查看一些行以了解它们是否有意义。 在这种情况下，很显然其他列未进行排序，因为氢(具有最低“原子序数”的元素)显示为具有铅的“原子序数”。

Now let’s try the other option to see how it works. Select the “Expand the selection” option and click the “Sort” button.

现在，让我们尝试另一个选项，看看它是如何工作的。 选择“扩展选择范围”选项，然后单击“排序”按钮。

You can see that the data in the “Atomic Number” column has been sorted and the data in the other rows followed it. (You could also just highlight all the data and Excel won’t show a “Sort Warning” box.)

您可以看到“原子序号”列中的数据已排序，其后的其他行中的数据也已排序。 (您也可以仅突出显示所有数据，Excel不会显示“排序警告”框。)

Now let’s see how we can use the “Custom Sort” feature to sort the data by several different columns at once.

现在，让我们看看如何使用“自定义排序”功能一次按几个不同的列对数据进行排序。

Select all of the data, include the header row if your data has one.

选择所有数据，如果数据中有一个，则包括标题行。

Now navigate to the “Sort & Filter” drop-down and select the “Custom Sort” command.

现在，导航到“排序和筛选”下拉列表，然后选择“自定义排序”命令。

This brings up the Sort window.

这将打开“排序”窗口。

We will sort our data first by using the “Phase at STP” column so that all of the gasses will appear higher on the list than the solids. Select the “Phase at STP” column.

我们将首先使用“在STP处的相数”列对数据进行排序，以使所有气体在列表上都比固体高。 选择“ STP相位”列。

Now press the “Add Level” button.

现在按“添加等级”按钮。

A new level appears. Note that the level says “Then by.”  This means it will be the second column used to sort the data. We will choose the “Element Category” as the second level so that the gasses are sorted by their category and noble gasses will appear higher on the list than the reactive gasses. Select the “Element Category” column.

出现一个新级别。 请注意，该级别显示“ Then by”。 这意味着它将是用于对数据进行排序的第二列。 我们将选择“元素类别”作为第二级，以便根据气体的类别对气体进行分类，并且惰性气体在列表中的位置将高于React性气体。 选择“元素类别”列。

Notice that there are other options.  Let’s take a look at the “Sort On” drop-down options. There are a variety of options, but we will use the “Cell Values” option.

请注意，还有其他选择。 让我们看一下“排序”下拉选项。 有多种选项，但是我们将使用“单元格值”选项。

Add another level by pressing the “Add Level” button again.

再次按“添加级别”按钮添加另一个级别。

Select the “Element” column. This means that the “Element” values in each “Element Category” will be sorted alphabetically so that the noble gas Argon will come before the other noble gas, Neon.

选择“元素”列。 这意味着将按字母顺序对每个“元素类别”中的“元素”值进行排序，以使惰性气体氩气先于其他惰性气体氖。

Let’s look at the “Order” drop-down. There are a few options, but for this example, we will choose the default “A to Z” sort order.

让我们看一下“订单”下拉列表。 有一些选项，但是在此示例中，我们将选择默认的“ A到Z”排序顺序。

You can add as many levels as will fit in the “Sort” box. When you’re finished, click the “OK” button.

您可以在“排序”框中添加尽可能多的级别。 完成后，单击“确定”按钮。

The data has been successfully sorted. The sort order was first by “Phase at STP,” then “Element Category,” and then by “Element.” You can see that the “Phase at STP” column is sorted alphabetically and within each “Phase at STP” type, the “Element Category” values are sorted alphabetically. And finally, the “Element” values are sorted alphabetically within each “Element Category” type.

数据已成功排序。 排序顺序首先是“按STP的阶段”，然后是“元素类别”，然后是“元素”。 您可以看到“ STP的相位”列按字母顺序排序，并且在每种“ STP的相位”类型中，“元素类别”值均按字母顺序排序。 最后，“元素”值在每种“元素类别”类型中按字母顺序排序。

Note: Be sure to select all of the columns you need when preparing to sort your data. Excel will not warn you that you may be overlooking columns when you use the “Custom Sort” feature.

注意：在准备对数据进行排序时，请确保选择所需的所有列。 当您使用“自定义排序”功能时，Excel不会警告您可能会忽略列。

翻译自: https://www.howtogeek.com/400679/how-to-sort-values-in-microsoft-excel/