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中文網其他相關文章！