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

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

Excel排序功能

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

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

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

在哪裡：

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

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

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

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

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

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

Excel排序功能 - 提示和註釋

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

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

基本的Excel排序公式

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

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

公式按順序排序

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

=SORT(A2:B8, 2, 1)

在哪裡：

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

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

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

以降序排序的公式

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

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

在目標(biāo)範(fàn)圍的左上方單元格中輸入公式，您將獲得此結(jié)果：

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

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

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

符合列排序

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

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

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

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

在哪裡：

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

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

使用複雜的數(shù)據(jù)模型時，您通常可能需要多層次排序?？梢杂霉酵瓿蓡?？是的，很容易！您要做的是為sort_index和sort_order參數(shù)提供數(shù)組常數(shù)。

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

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

將論點(diǎn)匯總在一起，我們得到了這個公式：

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

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

在Excel中排序和過濾

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

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

關(guān)於此公式的最好的事情是，它還將結(jié)果輸出為動態(tài)溢出範(fàn)圍，而無需按CTRL Shift Enter或猜測將其複製到多少個單元格。像往常一樣，您可以在最高單元格中鍵入一個公式，然後按Enter鍵。

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

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

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

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

獲得最大或最小的值，然後對結(jié)果進(jìn)行排序

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

這是一個通用公式：

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

從下面的數(shù)據(jù)集中，假設(shè)您想根據(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})

它準(zhǔn)確地產(chǎn)生了我們想要的結(jié)果：

要返回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ù)組以自動展開

如您所知，當(dāng)您對原始數(shù)據(jù)進(jìn)行任何更改時，排序的陣列會自動更新。這是所有動態(tài)數(shù)組函數(shù)的標(biāo)準(zhǔn)行為，包括排序。但是，當(dāng)您在引用數(shù)組之外添加新條目時，它們不會自動包含在公式中。如果您希望您的公式對此類更改做出響應(yīng)，請將源範(fàn)圍轉(zhuǎn)換為功能齊全的Excel表，並在公式中使用結(jié)構(gòu)化引用。

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

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

=SORT(A2:B8, 1, 1)

然後，您在第9行中輸入了一個新的條目，並感到失望地看到新添加的條目不在溢出範(fàn)圍之外：

現(xiàn)在，將源範(fàn)圍轉(zhuǎn)換為表。為此，只需選擇您的範(fàn)圍，包括列標(biāo)題（A1：B8），然後按Ctrl t 。構(gòu)建公式時，使用鼠標(biāo)選擇源範(fàn)圍，並且表名將自動插入公式中（這稱為結(jié)構(gòu)化參考）：

=SORT(Table1, 1, 1)

當(dāng)您在最後一行下方鍵入新條目時，表將自動擴(kuò)展，新數(shù)據(jù)將包含在Sort公式的溢出範(fàn)圍中：

Excel排序功能不起作用

如果您的排序公式會導(dǎo)致錯誤，則很可能是由於以下原因。

#NAME錯誤：較舊的Excel版本

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

#spill錯誤：某些東西阻止了溢出範(fàn)圍

如果溢出範(fàn)圍內(nèi)的一個或多個單元格並非完全空白或合併，則為#spill！顯示錯誤。要修復(fù)它，只需刪除阻塞即可。有關(guān)更多信息，請參閱Excel #spill！錯誤 - 它的含義以及如何修復(fù)。

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

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

#ref錯誤：源工作簿已關(guān)閉

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

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

練習(xí)工作簿下載

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

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