教程顯示了如何使用排序函數(shù)動態(tài)對數(shù)據(jù)數(shù)組進行分類。您將學習一個公式，在Excel中按字母順序排序，以升序或降序排列數(shù)字，按多列排序等等。

排序功能已經存在了很長時間。但是，隨著Excel 365中動態(tài)陣列的引入，似乎有一種非常簡單的方法可以與公式進行排序。該方法的優(yōu)點在于，當源數(shù)據(jù)更改時，結果會自動更新。

Excel排序功能

Excel中的排序功能分類數(shù)組或范圍的內容，按列或行，上升或下降順序。

排序屬于動態(tài)數(shù)組函數(shù)組。結果是一個動態(tài)陣列，該陣列會自動垂直或水平溢出到相鄰的單元格上，具體取決于源陣列的形狀。

排序函數(shù)的語法如下：

在哪里：

陣列（必需） - 是一個值的數(shù)組或要排序的一個單元格。這些可以是任何值，包括文本，數(shù)字，日期，時間和等。

sort_index （可選） - 一個整數(shù)，指示要排序哪個列或行。如果省略，則使用默認索引1。

sort_order （可選） - 定義排序順序：

• 1或省略（默認） - 上升順序，即從最小到最大
• -1-降序，即從最大到最小的

BY_COL（可選） - 一個指示排序方向的邏輯值：

• false或省略（默認） - 按行排序。您將大部分時間都使用此選項。
• true-按列排序。如果您的數(shù)據(jù)是在此示例中的列中水平組織的，則使用此選項。

Excel排序功能 - 提示和注釋

排序是一種新的動態(tài)數(shù)組函數(shù)，因此它具有兩個特殊性，您應該知道：

• 當前，該排序功能僅在Microsoft 365和Excel 2021中可用。Excel2019，Excel 2016不支持動態(tài)陣列公式，因此這些版本中的排序功能不可用。
• 如果按排序公式返回的數(shù)組是最終結果（即未傳遞到另一個函數(shù)），則Excel會動態(tài)創(chuàng)建適當?shù)姆秶?，并用排序的值將其填充。因此，請確保您始終將足夠的空單元格向下或/以及進入公式的單元格的右側，否則會發(fā)生#spill錯誤。
• 隨著源數(shù)據(jù)的變化，結果動態(tài)更新。但是，提供給公式的數(shù)組不會自動擴展到包含引用數(shù)組之外添加的新條目。要包含此類項目，您需要在公式中更新數(shù)組參考，或如本示例所示，將源范圍轉換為表，或創(chuàng)建一個動態(tài)命名范圍。

基本的Excel排序公式

此示例顯示了一個基本公式，用于在Excel中以升和降序排序數(shù)據(jù)。

假設您的數(shù)據(jù)是按字母順序排列的，如下面的屏幕截圖所示。您正在尋找在B列中對數(shù)字進行排序，而不會破壞或混合數(shù)據(jù)。

公式按順序排序

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

=SORT(A2:B8, 2, 1)

在哪里：

• A2：B8是源數(shù)組
• 2是要排序的列號
• 1是上升順序

由于我們的數(shù)據(jù)是行的，因此可以省略最后一個參數(shù)以默認為false-按行排序。

只需在任何空單元格中輸入公式（在我們的情況下D2），然后按Enter ，結果將自動溢出至D2：E8。

以降序排序的公式

為了對數(shù)據(jù)進行排序，即從最大到最小的，將sort_order參數(shù)設置為-1這樣：

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

在目標范圍的左上方單元格中輸入公式，您將獲得此結果：

以類似的方式，您可以按字母順序從A到Z或從Z到A進行文本值。

如何使用公式在Excel中對數(shù)據(jù)進行排序

以下示例顯示了Excel和幾個非平凡功能中排序功能的一些典型用途。

符合列排序

當您在Excel中排序數(shù)據(jù)時，在大多數(shù)情況下，您會更改行順序。但是，當您的數(shù)據(jù)與包含標簽和包含記錄的列的行水平組織時，您可能需要從左到右進行排序，而不是從上到下進行排序。

要按Excel中的列進行排序，請將BY_COL參數(shù)設置為true。在這種情況下， sort_index將表示一行，而不是列。

例如，按QTY對以下數(shù)據(jù)進行排序。從最高到最低，使用此公式：

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

在哪里：

• B1：H2是要排序的源數(shù)據(jù)
• 2是排序索引，因為我們在第二行中對數(shù)字進行排序
• -1表示降序排序訂單
• 真正的手段來對列進行排序，而不是行

按不同順序排序多個列（多級排序）

使用復雜的數(shù)據(jù)模型時，您通常可能需要多層次排序。可以用公式完成嗎？是的，很容易！您要做的是為sort_index和sort_order參數(shù)提供數(shù)組常數(shù)。

例如，首先按區(qū)域（A列）從A到Z進行以下數(shù)據(jù)，然后按QTY對。 （C列）從最小到最大，設置以下參數(shù)：

• 數(shù)組是A2：C13中的數(shù)據(jù)。
• sort_index是數(shù)組常數(shù){1,3}，因為我們首先按區(qū)域（1 ^st列）排序，然后按QTY進行排序。 （^第3列）。
• sort_order是數(shù)組常數(shù){1，-1}，因為第一列應按升序排序，而^第3列則以降序排序。
• 省略了BY_COL ，因為我們對行進行排序，這是默認的。

將論點匯總在一起，我們得到了這個公式：

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

它可以很好地工作！第一列中的文本值按字母順序排序，第三列中的數(shù)字從最大到最小：

在Excel中排序和過濾

如果您想用一些標準過濾數(shù)據(jù)并將輸出放置在順序上，請將排序和過濾功能一起使用：

過濾器函數(shù)根據(jù)您定義的標準獲得一個值數(shù)組，并將該數(shù)組傳遞到排序的第一個參數(shù)。

關于此公式的最好的事情是，它還將結果輸出為動態(tài)溢出范圍，而無需按CTRL Shift Enter或猜測將其復制到多少個單元格。像往常一樣，您可以在最高單元格中鍵入一個公式，然后按Enter鍵。

例如，我們將從a2：b9中的源數(shù)據(jù)中提取等于或大于30（> = 30）的項目，并按上升順序排列結果。

為此，我們首先在單元E2中設置條件，如下圖所示。然后，以這種方式構建我們的Excel排序公式：

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

除了由過濾器函數(shù)生成的數(shù)組外，我們僅指定sort_index參數(shù)（第2列）。其余兩個參數(shù)被省略，因為默認值完全按照我們的需要工作（排序逐行）。

獲得最大或最小的值，然后對結果進行排序

在分析大量信息時，通常需要提取一定數(shù)量的最高值。也許不僅提取，還可以按照所需的順序排列它們。理想情況下，選擇在結果中包含哪些列。聽起來很棘手？沒有新的動態(tài)數(shù)組功能！

這是一個通用公式：

其中n是您要返回的值的數(shù)量。

從下面的數(shù)據(jù)集中，假設您想根據(jù)C列中的數(shù)字獲得前3個列表。

要完成，您首先按^第三列按降序排序數(shù)組A2：C13：

SORT(A2:C13, 3, -1)

然后，將上述公式嵌套在索引函數(shù)的第一個（數(shù)組）參數(shù)中，以使數(shù)組從最高到最小排序。

對于第二個（ row_num ）參數(shù)，該參數(shù)指示要返回多少行，使用序列函數(shù)生成所需的順序數(shù)字。由于我們需要3個頂值，因此我們使用序列（3），這與直接在公式中直接提供垂直陣列常數(shù){1; 2; 3}相同。

對于第三個（ col_num ）參數(shù)，該參數(shù)定義了要返回多少列的參數(shù)，請以水平數(shù)組常數(shù)的形式提供列號。我們想返回列B和C，因此我們使用數(shù)組{2,3}。

最終，我們得到以下公式：

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

它準確地產生了我們想要的結果：

要返回3個底部值，只需將原始數(shù)據(jù)從最小數(shù)據(jù)排序。為此，將sort_order參數(shù)從-1更改為1：

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

在特定位置返回排序值

從另一個角度看，如果您只想返回特定的位置怎么辦？說，只有第一個，僅第二個或僅從排序列表中的第三記錄？要完成，請使用上面討論的索引排序公式的簡化版本：

n是感興趣的位置。

例如，要從頂部獲得特定位置（即從數(shù)據(jù)排序的降序中），請使用此公式：

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

要從底部獲得特定的位置（即從數(shù)據(jù)排序的升序中），請使用以下位置：

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

如果A2：C13是源數(shù)據(jù)，F(xiàn)1是頂部的位置，i1是底部的位置，{2,3}是要返回的列。

使用Excel表獲取排序數(shù)組以自動展開

如您所知，當您對原始數(shù)據(jù)進行任何更改時，排序的陣列會自動更新。這是所有動態(tài)數(shù)組函數(shù)的標準行為，包括排序。但是，當您在引用數(shù)組之外添加新條目時，它們不會自動包含在公式中。如果您希望您的公式對此類更改做出響應，請將源范圍轉換為功能齊全的Excel表，并在公式中使用結構化引用。

要查看其在實踐中的工作原理，請考慮以下示例。

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

=SORT(A2:B8, 1, 1)

然后，您在第9行中輸入了一個新的條目，并感到失望地看到新添加的條目不在溢出范圍之外：

現(xiàn)在，將源范圍轉換為表。為此，只需選擇您的范圍，包括列標題（A1：B8），然后按Ctrl t 。構建公式時，使用鼠標選擇源范圍，并且表名將自動插入公式中（這稱為結構化參考）：

=SORT(Table1, 1, 1)

當您在最后一行下方鍵入新條目時，表將自動擴展，新數(shù)據(jù)將包含在Sort公式的溢出范圍中：

Excel排序功能不起作用

如果您的排序公式會導致錯誤，則很可能是由于以下原因。

#NAME錯誤：較舊的Excel版本

排序是一個新功能，僅在Excel 365和Excel 2021中起作用。在不支持此功能的較舊版本中，#name？發(fā)生錯誤。

#spill錯誤：某些東西阻止了溢出范圍

如果溢出范圍內的一個或多個單元格并非完全空白或合并，則為#spill！顯示錯誤。要修復它，只需刪除阻塞即可。有關更多信息，請參閱Excel #spill！錯誤 - 它的含義以及如何修復。

#Value錯誤：無效的參數(shù)

每當您遇到#Value時！錯誤，檢查sort_index和sort_order參數(shù)。 sort_index不應超過列數(shù)為數(shù)組， sort_order應為1（上升）或-1（下降）。

#ref錯誤：源工作簿已關閉

由于動態(tài)數(shù)組對工作簿之間的參考的支持有限，因此Sort功能需要兩個文件打開。如果源工作簿已關閉，則公式將拋出#ref！錯誤。要修復它，只需打開引用文件即可。

這就是如何使用公式在Excel中對數(shù)據(jù)進行排序的方法。我感謝您閱讀，并希望下周在我們的博客上見到您！

練習工作簿下載

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

以上是Excel排序功能 - 使用公式自動排序數(shù)據(jù)的詳細內容。更多信息請關注PHP中文網(wǎng)其他相關文章！