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

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

Excel排序功能

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

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

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

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

在哪裡：

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

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

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

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

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

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

Excel排序功能 - 提示和註釋

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

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

基本的Excel排序公式

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

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

公式按順序排序

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

=SORT(A2:B8, 2, 1)

在哪裡：

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

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

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

以降序排序的公式

為了對(duì)數(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中對(duì)數(shù)據(jù)進(jìn)行排序

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

符合列排序

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

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

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

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

在哪裡：

• B1：H2是要排序的源數(shù)據(jù)
• 2是排序索引，因?yàn)槲覀冊(cè)诘诙兄袑?duì)數(shù)字進(jìn)行排序
• -1表示降序排序訂單
• 真正的手段來(lái)對(duì)列進(jìn)行排序，而不是行

按不同順序排序多個(gè)列（多級(jí)排序）

使用複雜的數(shù)據(jù)模型時(shí)，您通?？赡苄枰鄬哟闻判颉？梢杂霉酵瓿蓡幔渴堑?，很容易！您要做的是為sort_index和sort_order參數(shù)提供數(shù)組常數(shù)。

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

• 數(shù)組是A2：C13中的數(shù)據(jù)。
• sort_index是數(shù)組常數(shù){1,3}，因?yàn)槲覀兪紫劝?em>區(qū)域（1 ^st列）排序，然後按QTY進(jìn)行排序。 （^第3列）。
• sort_order是數(shù)組常數(shù){1，-1}，因?yàn)榈谝涣袘?yīng)按升序排序，而^第3列則以降序排序。
• 省略了BY_COL ，因?yàn)槲覀儗?duì)行進(jìn)行排序，這是默認(rèn)的。

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

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

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

在Excel中排序和過(guò)濾

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

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

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

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

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

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

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

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

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

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

這是一個(gè)通用公式：

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

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

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

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

SORT(A2:C13, 3, -1)

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

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

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

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

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

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

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

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

在特定位置返回排序值

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

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

n是感興趣的位置。

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

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

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

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

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

使用Excel表獲取排序數(shù)組以自動(dòng)展開(kāi)

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

要查看其在實(shí)踐中的工作原理，請(qǐng)考慮以下示例。

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

=SORT(A2:B8, 1, 1)

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

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

=SORT(Table1, 1, 1)

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

Excel排序功能不起作用

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

#NAME錯(cuò)誤：較舊的Excel版本

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

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

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

#Value錯(cuò)誤：無(wú)效的參數(shù)

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

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

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

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

練習(xí)工作簿下載

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

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