Excel排序功能 - 使用公式自动排序数据

教程显示了如何使用排序函数动态对数据数组进行分类。您将学习一个公式，在Excel中按字母顺序排序，以升序或降序排列数字，按多列排序等等。

排序功能已经存在了很长时间。但是，随着Excel 365中动态阵列的引入，似乎有一种非常简单的方法可以与公式进行排序。该方法的优点在于，当源数据更改时，结果会自动更新。

• Excel排序功能
• 基本排序公式
• 如何使用公式 - 示例在Excel中对数据进行排序
  • 符合列排序
  • 按多个列以不同顺序排序
  • 在Excel中排序和过滤
  • 排序并获取n顶值或底部值
• 如何使排序数组自动扩展
• Excel排序功能不起作用

Excel排序功能

Excel中的排序功能分类数组或范围的内容，按列或行，上升或下降顺序。

排序属于动态数组函数组。结果是一个动态阵列，该阵列会自动垂直或水平溢出到相邻的单元格上，具体取决于源阵列的形状。

排序函数的语法如下：

sort（array，[sort_index]，[sort_order]，[by_col]）

在哪里：

阵列（必需） - 是一个值的数组或要排序的一个单元格。这些可以是任何值，包括文本，数字，日期，时间和等。

sort_index （可选） - 一个整数，指示要排序哪个列或行。如果省略，则使用默认索引1。

sort_order （可选） - 定义排序顺序：

• 1或省略（默认） - 上升顺序，即从最小到最大
• -1-降序，即从最大到最小的

BY_COL（可选） - 一个指示排序方向的逻辑值：

• false或省略（默认） - 按行排序。您将大部分时间都使用此选项。
• true-按列排序。如果您的数据是在此示例中的列中水平组织的，则使用此选项。

Excel排序功能 - 提示和注释

排序是一种新的动态数组函数，因此它具有两个特殊性，您应该知道：

• 当前，该排序功能仅在Microsoft 365和Excel 2021中可用。Excel2019，Excel 2016不支持动态阵列公式，因此这些版本中的排序功能不可用。
• 如果按排序公式返回的数组是最终结果（即未传递到另一个函数），则Excel会动态创建适当的范围，并用排序的值将其填充。因此，请确保您始终将足够的空单元格向下或/以及进入公式的单元格的右侧，否则会发生#spill错误。
• 随着源数据的变化，结果动态更新。但是，提供给公式的数组不会自动扩展到包含引用数组之外添加的新条目。要包含此类项目，您需要在公式中更新数组参考，或如本示例所示，将源范围转换为表，或创建一个动态命名范围。

基本的Excel排序公式

此示例显示了一个基本公式，用于在Excel中以升和降序排序数据。

假设您的数据是按字母顺序排列的，如下面的屏幕截图所示。您正在寻找在B列中对数字进行排序，而不会破坏或混合数据。

公式按顺序排序

要在B列中从最小到最大的值分类值，以下是要使用的公式：

=SORT(A2:B8, 2, 1)

在哪里：

• A2：B8是源数组
• 2是要排序的列号
• 1是上升顺序

由于我们的数据是行的，因此可以省略最后一个参数以默认为false-按行排序。

只需在任何空单元格中输入公式（在我们的情况下D2），然后按Enter ，结果将自动溢出至D2：E8。

以降序排序的公式

为了对数据进行排序，即从最大到最小的，将sort_order参数设置为-1这样：

=SORT(A2:B8, 2, -1)

在目标范围的左上方单元格中输入公式，您将获得此结果：

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

如何使用公式在Excel中对数据进行排序

以下示例显示了Excel和几个非平凡功能中排序功能的一些典型用途。

符合列排序

当您在Excel中排序数据时，在大多数情况下，您会更改行顺序。但是，当您的数据与包含标签和包含记录的列的行水平组织时，您可能需要从左到右进行排序，而不是从上到下进行排序。

要按Excel中的列进行排序，请将BY_COL参数设置为true。在这种情况下， sort_index将表示一行，而不是列。

例如，按QTY对以下数据进行排序。从最高到最低，使用此公式：

=SORT(B1:H2, 2, 1, TRUE)

在哪里：

• B1：H2是要排序的源数据
• 2是排序索引，因为我们在第二行中对数字进行排序
• -1表示降序排序订单
• 真正的手段来对列进行排序，而不是行

按不同顺序排序多个列（多级排序）

使用复杂的数据模型时，您通常可能需要多层次排序。可以用公式完成吗？是的，很容易！您要做的是为sort_index和sort_order参数提供数组常数。

例如，首先按区域（A列）从A到Z进行以下数据，然后按QTY对。 （C列）从最小到最大，设置以下参数：

• 数组是A2：C13中的数据。
• sort_index是数组常数{1,3}，因为我们首先按区域（1 ^st列）排序，然后按QTY进行排序。 （^第3列）。
• sort_order是数组常数{1，-1}，因为第一列应按升序排序，而^第3列则以降序排序。
• 省略了BY_COL ，因为我们对行进行排序，这是默认的。

将论点汇总在一起，我们得到了这个公式：

=SORT(A2:C13, {1,3}, {1,-1})

它可以很好地工作！第一列中的文本值按字母顺序排序，第三列中的数字从最大到最小：

在Excel中排序和过滤

如果您想用一些标准过滤数据并将输出放置在顺序上，请将排序和过滤功能一起使用：

sort（filter（array， criteria_range = criteria ），[sort_index]，[sort_order]，[by_col]）

过滤器函数根据您定义的标准获得一个值数组，并将该数组传递到排序的第一个参数。

关于此公式的最好的事情是，它还将结果输出为动态溢出范围，而无需按CTRL Shift Enter或猜测将其复制到多少个单元格。像往常一样，您可以在最高单元格中键入一个公式，然后按Enter键。

例如，我们将从a2：b9中的源数据中提取等于或大于30（> = 30）的项目，并按上升顺序排列结果。

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

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

除了由过滤器函数生成的数组外，我们仅指定sort_index参数（第2列）。其余两个参数被省略，因为默认值完全按照我们的需要工作（排序逐行）。

获得最大或最小的值，然后对结果进行排序

在分析大量信息时，通常需要提取一定数量的最高值。也许不仅提取，还可以按照所需的顺序排列它们。理想情况下，选择在结果中包含哪些列。听起来很棘手？没有新的动态数组功能！

这是一个通用公式：

索引（排序（...），序列（ n ），{ column1_to_return ， column2_to_return ，…}）

其中n是您要返回的值的数量。

从下面的数据集中，假设您想根据C列中的数字获得前3个列表。

要完成，您首先按^第三列按降序排序数组A2：C13：

SORT(A2:C13, 3, -1)

然后，将上述公式嵌套在索引函数的第一个（数组）参数中，以使数组从最高到最小排序。

对于第二个（ row_num ）参数，该参数指示要返回多少行，使用序列函数生成所需的顺序数字。由于我们需要3个顶值，因此我们使用序列（3），这与直接在公式中直接提供垂直阵列常数{1; 2; 3}相同。

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

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

=INDEX(SORT(A2:C13, 3, -1), SEQUENCE(3), {2,3})

它准确地产生了我们想要的结果：

要返回3个底部值，只需将原始数据从最小数据排序。为此，将sort_order参数从-1更改为1：

=INDEX(SORT(A2:C13, 3, 1), SEQUENCE(3), {2,3})

在特定位置返回排序值

从另一个角度看，如果您只想返回特定的位置怎么办？说，只有第一个，仅第二个或仅从排序列表中的第三记录？要完成，请使用上面讨论的索引排序公式的简化版本：

索引（排序（...）， n ，{ column1_to_return ， column2_to_return ，…}）

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}是要返回的列。

使用Excel表获取排序数组以自动展开

如您所知，当您对原始数据进行任何更改时，排序的阵列会自动更新。这是所有动态数组函数的标准行为，包括排序。但是，当您在引用数组之外添加新条目时，它们不会自动包含在公式中。如果您希望您的公式对此类更改做出响应，请将源范围转换为功能齐全的Excel表，并在公式中使用结构化引用。

要查看其在实践中的工作原理，请考虑以下示例。

假设您使用以下Excel排序公式按字母顺序排列A2：B8的值：

=SORT(A2:B8, 1, 1)

然后，您在第9行中输入了一个新的条目，并感到失望地看到新添加的条目不在溢出范围之外：

现在，将源范围转换为表。为此，只需选择您的范围，包括列标题（A1：B8），然后按Ctrl t 。构建公式时，使用鼠标选择源范围，并且表名将自动插入公式中（这称为结构化参考）：

=SORT(Table1, 1, 1)

当您在最后一行下方键入新条目时，表将自动扩展，新数据将包含在Sort公式的溢出范围中：

Excel排序功能不起作用

如果您的排序公式会导致错误，则很可能是由于以下原因。

#NAME错误：较旧的Excel版本

排序是一个新功能，仅在Excel 365和Excel 2021中起作用。在不支持此功能的较旧版本中，#name？发生错误。

#spill错误：某些东西阻止了溢出范围

如果溢出范围内的一个或多个单元格并非完全空白或合并，则为#spill！显示错误。要修复它，只需删除阻塞即可。有关更多信息，请参阅Excel #spill！错误 - 它的含义以及如何修复。

#Value错误：无效的参数

每当您遇到#Value时！错误，检查sort_index和sort_order参数。 sort_index不应超过列数为数组， sort_order应为1（上升）或-1（下降）。

#ref错误：源工作簿已关闭

由于动态数组对工作簿之间的参考的支持有限，因此Sort功能需要两个文件打开。如果源工作簿已关闭，则公式将抛出#ref！错误。要修复它，只需打开引用文件即可。

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

练习工作簿下载

用公式（.xlsx文件）在Excel中排序

以上是Excel排序功能 - 使用公式自动排序数据的详细内容。更多信息请关注PHP中文网其他相关文章！