Excel SORT函数

本教程展示了如何使用SORT函数对数据数组进行动态排序。 你将学会在Excel中按字母顺序排序、按升序或降序排列数字、按多列排序等公式。

排序功能已经存在了很长时间。 但随着Excel 365中动态数组的引入，出现了一种惊人的简单方法，即用公式进行排序。 这种方法的好处是，当源数据发生变化时，结果会自动更新。

Excel中的SORT函数按列或行对一个数组或范围的内容进行升序或降序排序。

SORT属于动态数组函数，其结果是一个动态数组，根据源数组的形状，自动地垂直或水平地溢出到相邻的单元。

SORT函数的语法如下。

在哪里？

阵列 (必填) - 是一个要排序的数值数组或单元格范围。 这些可以是任何数值，包括文本、数字、日期、时间等。

排序_索引 (可选) - 一个整数，表示按哪一列或哪一行排序。 如果省略，则使用默认索引1。

排序_顺序 (可选) - 定义了排序顺序。

By_col（可选） - 一个逻辑值，表示排序的方向。

SORT是一个新的动态数组函数，因此它有一些你应该注意的特殊性。

本例展示了Excel中按升序和降序对数据进行排序的基本公式。

假设你的数据是按字母顺序排列的，如下面的截图所示。 你想在不破坏或混合数据的情况下对B列的数字进行排序。

要对B列中的数值从小到大进行排序，这里要用到的公式是。

=sort(a2:b8, 2, 1)

在哪里？

由于我们的数据是以行为单位组织的，最后一个参数可以省略，默认为FALSE - 按行排序。

只要在任何一个空单元格（在我们的例子中是D2）中输入公式，按回车键，结果就会自动溢出到D2：E8。

要对数据进行降序排序，即从大到小的排序，请设置 排序_顺序 参数为-1，像这样。

=sort(a2:b8, 2, -1)

在目标区域的左上角单元格中输入公式，你会得到这样的结果。

以类似的方式，你可以按字母顺序将文本值从A到Z或从Z到A进行排序。

下面的例子显示了SORT函数在Excel中的几个典型用途和几个非典型用途。

当你在Excel中对数据进行排序时，在大多数情况下，你改变的是行的顺序。 但当你的数据是横向组织的，行中包含标签，列中包含记录时，你可能需要从左到右排序，而不是从上到下。

要在Excel中按列排序，请设置 by_col 参数为 "真"。 在这种情况下。 排序_index 将代表一个行，而不是一个列。

例如，要对以下数据按数量从高到低排序，请使用此公式。

=sort(b1:h2, 2, 1, true)

在哪里？

在处理复杂的数据模型时，你可能经常需要一个多级排序。 这可以用一个公式来完成吗？ 是的，很容易！你所做的是为以下内容提供数组常数 排序_index 和 排序_顺序 争论。

例如，要对以下数据进行排序，首先要按 地区 (A列)从A到Z，然后通过 数量 .（C列）从小到大，设置以下参数。

把这些论点放在一起，我们得到这个公式。

=sort(a2:c13, {1,3}, {1,-1})

而且效果非常好！第一列的文本值按字母顺序排序，第三列的数字从大到小排序。

如果你想用一些标准来过滤数据，并把输出的数据按顺序排列，可以同时使用SORT和FILTER函数。

FILTER函数根据你定义的标准获得一个数值数组，并将该数组传递给SORT的第一个参数。

这个公式最棒的地方是，它还能以动态溢出范围的形式输出结果，而不需要你按下Ctrl + Shift + Enter，或者猜测要复制到多少个单元格。 像往常一样，你在最上面的单元格中输入一个公式，然后按回车键。

作为一个例子，我们将从A2:B9的源数据中提取数量等于或大于30（;=30）的项目，并将结果按升序排列。

为此，我们首先在单元格E2中设置条件，例如，如下图所示。 然后，以这种方式建立我们的Excel SORT公式。

=SORT(FILTER(A2:B9, B2:B9;=E2), 2)

除了 阵列 由FILTER函数生成，我们只指定 排序_index 其余两个参数被省略了，因为默认值完全按照我们的需要工作（按行升序排序）。

在分析大量的信息时，经常需要提取一定数量的最高值。 也许不仅仅是提取，还要将它们按所需的顺序排列。 最好是选择在结果中包括哪些列。 听起来很棘手吗？ 有了新的动态数组函数就不一样了

这里有一个通用的公式。

在哪里？ n 是你想返回的值的编号。

从下面的数据集中，假设你想根据C列的数字得到一个前三名的名单。

要做到这一点，首先要对数组A2:C13按第三列降序排序。

排序(a2:c13, 3, -1)

然后，将上述公式嵌套在第一个( 阵列 )参数，使数组从大到小排序。

对于第二个( 行数_num 由于我们需要3个最高值，我们使用SEQUENCE(3)，这与在公式中直接提供一个垂直数组常数{1;2;3}是一样的。

对于第三个( col_num )参数，它定义了要返回多少列，以水平数组常数的形式提供列号。 我们想返回B和C列，所以我们使用数组{2,3}。

最终，我们得到以下公式。

=index(sort(a2:c13, 3, -1), sequence(3), {2,3})

而且它产生的结果正是我们想要的。

返回 3底部 为此，请改变一下 排序_顺序 参数从-1到1。

=index(sort(a2:c13, 3, 1), sequence(3), {2,3})

从另一个角度看，如果你只想返回一个特定的排序位置怎么办？ 比如，只返回排序列表中的第1条、第2条或第3条记录？ 要做到这一点，可以使用上面讨论的简化版INDEX SORT公式。

在哪里？ n 是感兴趣的位置。

例如，要从顶部获得一个特定的位置（即从数据降序排序），使用这个公式。

=index(sort(a2:c13, 3, -1), f1, {2,3})

要从底部获得一个特定的位置（即从数据中升序排序），请使用这个。

=index(sort(a2:c13, 3, 1), i1, {2,3})

其中A2:C13是源数据，F1是从顶部开始的位置，I1是从底部开始的位置，{2,3}是要返回的列。

正如你已经知道的，当你对原始数据做任何改变时，排序的数组会自动更新。 这是所有动态数组函数的标准行为，包括SORT。 然而，当你在引用的数组之外添加新条目时，它们不会自动包含在公式中。 如果你希望你的公式能对这种变化做出反应，将源范围转换成一个完全在你的公式中使用结构化引用。

为了了解它在实践中是如何运作的，请考虑以下例子。

假设你使用下面的Excel SORT公式，按字母顺序排列A2:B8范围内的数值。

=sort(a2:b8, 1, 1)

然后，你在第9行输入一个新的条目......并失望地看到新增加的条目被排除在溢出范围之外。

现在，将源范围转换为表，为此，只需选择包括列头（A1:B8）在内的范围，然后按Ctrl + T 。 在建立公式时，用鼠标选择源范围，表名将自动插入公式中（这被称为结构化引用）。

=SORT(Table1, 1, 1)

当你在最后一行的正下方输入一个新条目时，表格会自动展开，新的数据会被包含在SORT公式的溢出范围内。

如果你的SORT公式出现了错误，很可能是因为以下原因。

SORT是一个新的函数，只在Excel 365和Excel 2021中工作。 在不支持这个函数的旧版本中，会出现#NAME? 的错误。

如果溢出区的一个或多个单元格没有完全空白或合并，就会显示#SPILL！错误。 要解决这个问题，只需清除堵塞物即可。 更多信息，请参见Excel #SPILL！错误--它意味着什么以及如何解决。

每当你遇到#VALUE！错误时，检查一下 排序_index 和 排序_顺序 争论。 排序_索引 不应超过列的数量是 阵列 ，以及 排序_顺序 应该是1（升序）或-1（降序）。

由于动态数组对工作簿之间的引用支持有限，SORT函数要求两个文件都是打开的。 如果源工作簿被关闭，公式将抛出一个#REF！错误。 要解决这个问题，只需打开被引用的文件。

这就是在Excel中使用公式对数据进行排序的方法。 感谢你的阅读，希望下周能在我们的博客上见到你!

在Excel中用公式进行排序(.xlsx文件)