本教程解釋了具體內容 功能 Excel 中的排名 並展示如何在 Excel 中根據各種條件進行排序、按組別對資料進行排序、計算百分位數排名等。當你需要的時候 確定數字清單中數字的相對位置，最簡單的方法是將數字按升序或降序排列。如果由於某種原因無法進行排序，Excel 中的範圍公式是完成這項工作的完美工具。

Excel 範圍函數

La 排名功能 在 Excel 中 傳回數值與同一列表中其他值相比的順序（或排名）。換句話說，它告訴您哪個值是最高的、第二高的等等。

也許您可能感興趣： Excel選擇函數。公式和例子

在有序列表中，某個數字的排名就是位置。 Excel 中的 Range 函數可以決定從最大值（如同按降序排序）或最小值（如同按升序排序）開始的範圍。這 RANK 函數語法 Excel 的內容如下：

排名（數字，參考，[順序]）

其中：

數量（必填）： 您想要尋找其排名的值。

參考（必填）： 用於排序的數值列表。它可以作為數字數組或數字列表的引用提供。

訂單（可選）： 指定如何對數值進行排序的數字：

如果為0或省略，則值按降序排序，即從最高到最低。

如果是1或任何其他非零值，則值按升序排序，即從最低到最高。

Excel 中的 RANK.EQ 函數

RANK.EQ 它是RANK函數的改進版本，在 Excel 2010 中引入。 （EQ 的意思是「平等」）。

RANK.EQ（數字，參考，[順序]）

在 Excel 2007 及更低版本中，您應始終使用 RANK 函數。在 Excel 2010、Excel 2013 和 Excel 2016 中，您可以選擇 RANK 或 RANK.EQ。但是，建議使用 RANK.EQ，因為 RANK 可以隨時暫停。

Excel RANGE.AVG函數

RANK.AVG 是另一個函數 在excel中尋找範圍 僅在 Excel 2010、Excel 2013、Excel 2016 及更高版本中可用。它與其他兩個函數具有相同的語法：

RANK.AVG（數字，參考，[順序]）

差異在於，如果多個數字具有相同的排名，則會傳回平均排名（AVG 表示「平均」）。

關於 Excel 中的 RANK，您應該了解的 4 件事

以下是有關 Excel 中的 Rank 函數的一些注意事項：

Excel 中的任何範圍公式僅適用於數值：正數和負數、零、日期和時間值。 ref 參數中的非數字值將被忽略。

所有 RANGE 函數對於重複值都傳回相同的範圍並跳過進一步排序，如下例所示。

在 Excel 2010 及更高版本中，RANK 函數已替換為 RANK.EQ 和 RANK.AVG。為了向後相容，RANK 仍然適用於所有版本的 Excel，但將來可能無法使用。

如果在參考中找不到該數字，則任何 Excel 範圍函數都會傳回 #N/A 錯誤。

基本 Excel 範圍公式（從最高到最低）

要了解有關使用 Excel 中的 Rank 函數進行排名的更多信息，請查看此螢幕截圖：

所有三個公式均按降序對 B 列中的數字進行排序（省略 order 參數）：

在 Excel 2003 – 2016 的所有版本中：

=排名($B2,$B$2:$B$7)

在 Excel 2010 – 2016 中：

=RANK.EQ($B2,$B$2:$B$7)

=RANK.AVG($B2,$B$2:$B$7)

不同之處在於這些公式處理重複值的方式。正如您所看到的，相同的分數在儲存格 B5 和 B6 中出現兩次，這會影響後續排序：

RANK 和 RANK.EQ 公式將兩個重複分數的排名設為 2。第二高分（丹妮拉）排名第四。沒有人被授予 3 級。

RANK.AVG 公式為每個幕後重複項目分配不同的排名（本例中為 2 和 3），並傳回這些排名的平均值 (2.5)。再次強調，第三名並不屬於任何人。

如何在 Excel 中使用 RANK – 公式範例

他們說，通往卓越的道路是由實踐鋪成的。因此，為了更好地學習如何在 Excel 中單獨使用 RANK 函數或與其他函數結合使用，讓我們來看看一些實際任務的解決方案。

Excel如何從小到大排序

如上例所示，若要從最大到最小對數字進行排序，請使用 Excel 的範圍公式之一，並將 order 參數設為 0 或省略（預設）。

若要使一個數字與其他數字按升序排序，請將 1 或任何其他非零值放入可選的第三個參數中。例如，要對學生的 100 公尺衝刺時間進行評分，您可以使用以下任一公式：

= RANK（B2，$ B $ 2：$ B $ 7,1）

=RANK.EQ(B2,$B$2:$B$7,1)

注意我們鎖定了參數的範圍 文獻 透過使用絕對儲存格引用，這樣當我們將公式複製到列中時它就不會改變。

7 個最佳餐廳管理程序

結果，最小值（最快時間）獲得第一名，最大值（最慢時間）獲得最低排名 6。

如何在 Excel 中對資料進行唯一分類

如上所述，所有 Excel 範圍函數對於相等值的元素傳回相同的範圍。如果這不是您想要的，請使用以下公式之一來解決平局決勝情況，並為每個數字賦予唯一的排名。

從最高到最低的獨特排名

若要僅按降序對學生數學成績進行排序，請使用以下公式：

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

從最低到最高的獨特排名

若要按升序對 100 公尺比賽成績進行排序且不重複，請使用以下公式：

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

這些公式如何運作

如您所知， 這兩個公式之間的唯一區別是 RANK.EQ 函數的 order 參數：省略以降序排序值，1 表示升序排序。在這兩個公式中，巧妙使用相對和絕對單元格引用的 COUNTIF 函數發揮了作用。

簡而言之，使用 COUNTIF 找出要排序的數字在前面的儲存格（包括數字儲存格）中出現了多少次。在輸入公式的頂行中，範圍由單一儲存格 ($B$2:B2) 組成。但由於它只鎖定第一個引用 ($B$2)，因此最後一個相對引用 (B2) 會根據複製公式的行而變化。

因此，對於第 7 行，範圍擴展到 $B$2:B7，並將 B7 中的值與先前的每個單元格進行比較。因此，對於所有第一次出現的情況，COUNTIF 會傳回 1；並將公式末尾減1恢復原來的範圍。

對於第二次出現，COUNTIF 傳回 2。如果相同值出現 1 次，則 COUNTIF() – 1 會在排序中加入 3，依此類推。

打破 Excel RANK 關係的解決方法

另一種形式 將excel中的數字排序 唯一的方法是新增兩個 COUNTIF 函數：

第一個函數決定有多少值大於或小於要排序的數字，這取決於您是降序排序還是升序排序。

第二個函數（如上例所示，具有「擴展範圍」$B $2:B2），您將獲得等於數字的值的數量。

例如，要僅從最高到最低對數字進行排序，您可以使用以下公式：

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

如下圖所示，決勝局已成功解決，並且為每個學生分配了唯一的排名：

Excel 中以多個標準為基礎的分類

前面的範例示範了針對以下情況的兩種工作解決方案： Excel 排名決勝局。然而，僅根據其在列表中的位置來對相同的數字進行不同的排名似乎是不公平的。

為了提高您的排名，您可能需要添加一個在出現平局時考慮的標準。在我們的範例資料集中，我們將 C 列中的總分相加併計算範圍，如下所示：

首先，以數學分數（主要標準）分類。

當出現平手時，以總分打破平手（次要標準）

為此，我們將使用常規 RANK/RANK.EQ 公式來尋找排名，並使用 COUNTIF 函數來打破平局：

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)

與前面的例子相比，這個排名公式更客觀：Timothy 排名第二，因為他的總分高於 Julia：

這個公式是如何運作的

公式的 RANK 部分很明顯，且 COUNTIFS 函數執行以下操作：

第一對 criteria_range/criterion ($B$2:$B$7,$B2) 計算您正在排名的數值的出現次數。請注意，我們使用絕對引用修復了範圍，但我們不鎖定條件行 ($B2)，以便公式單獨檢查每行中的值。

第二個 criteria_range/criterion 對 ($C$2:$C$7, ">" & $C2) 求出有多少總分大於正在排名的數值的總分。

由於 COUNTIFS 使用 AND 邏輯，即僅計算滿足所有指定條件的單元格，因此它為 Timothy 返回 0，因為沒有其他具有相同數學分數的學生具有更高的總分。因此，RANK.EQ 返回的 Timothy 排名不會改變。

對茱莉亞來說，函數 COUNTIF 返回 1，因為數學成績相同的學生總分較高，因此他的排名會增加 1。

什麼是 Mozilla Firefox。用途、特色、意見、價格使用多個條件對數字進行排序的替代解決方案

您可以使用 COUNTIF 來檢查主要條件，並使用 COUNTIFS 或 SUMPRODUCT 來解決決勝問題，而不是使用 RANK 或 RANK.EQ 函數：

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

這些公式的結果與上圖完全相同。

如何在 Excel 中計算百分位數排名

在統計學中，百分位數是給定資料集中一定百分比的值低於該值的值。例如，如果 70% 的學生等於或低於您的測驗分數，則您的百分位排名為 70。

若要在 Excel 中取得百分位數排名，請使用帶有非零順序參數的 RANK 或 RANK.EQ 函數對數字從最低到最高進行排名，然後將排名除以數字計數。因此，通用的 Excel 百分位數排名公式如下：

RANK.EQ(topmost_cell, 排名, 1) / COUNT(排名)

為了計算學生的百分位排名，公式採用以下形式：

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

為了正確顯示結果，請確保在公式儲存格中設定百分比格式：

也許您想知道： 如何在Excel中按類別新增

如何對不相鄰單元格中的數字進行排序

在需要對非連續單元格進行排序的情況下，請直接在參數中提供這些單元格 文獻 您的 Excel 範圍公式採用引用並集的形式，以 $ 符號阻止引用。例如：

=排名(B2,($B$2,$B$4,$B$6))

為了避免未排序的儲存格出現錯誤，請調整 秩 在功能上 錯誤， 所以：

=IFERROR(排名(B2,($B$2,$B$4,$B$6)),»»)

請注意，儘管公式中不包含儲存格 B5，但重複的數字也會分配一個範圍：

如果需要對多個不連續的單元格進行排序，上面的公式可能會太長。在這種情況下，更優雅的解決方案是定義一個命名範圍並在公式中引用該名稱：

=IFERROR(RANK(B2,範圍),»»)

Excel中如何依群組排序

當處理以某種類型的資料結構組織的條目時，資料可能屬於多個群組，並且您可能希望單獨對每個組內的數字進行排序。 Excel 的 RANK 函數無法解決這個挑戰，因此讓我們使用更複雜的 SUMPRODUCT 公式：

按組降序排列：

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

按組升序排列：

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

其中：

A2： A7 是分配有數字的組別。

C2： C7是分類編號。

在此範例中，我們使用第一個公式對每組中的數字從最大到最小進行排序：

這個公式是如何運作的

基本上，公式評估 2 個條件：

首先，檢查組 (A2=$A$2:$A$7)。此部分根據範圍元素是否與 A2 屬於同一組，傳回 TRUE 和 FALSE 的陣列。

其次，檢查分數。若要將數值從最高到最低（降序）排序，請使用條件 (C2 < $C$2:$C$11)，對於大於或等於 C2 的儲存格傳回 TRUE，否則傳回 FALSE。

由於在 Microsoft Excel 術語中，TRUE = 1 且 FALSE = 0，因此將兩個矩陣相乘會得到一個由 1 和 0 組成的矩陣，其中僅對於滿足兩個條件的行傳回 1。然後， SUMPRODUCT 它將數組元素 1 和 0 相加，因此傳回 0 作為每組中最大的數字。並將結果加 1 以從 1 開始排序。

將組內的數字從小到大（升序）分類的公式採用相同的邏輯。不同之處在於 和積 對於特定組中的最小數字回傳 0，因為該組中沒有數字滿足第二個條件 (C2 > $C$2:$C$2)。再次，在公式結果中加 7 將零階替換為第一階。

代替 SUMPRODUCT你可以 使用功能 和 新增矩陣的元素。但這需要使用數組公式，透過以下方式完成 Ctrl + Shift + Enter。 例如：

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

如何分別對正數和負數進行分類

如果您的數字列表同時包含正值和負值，Excel 中的 RANK 函數將立即對它們進行排名。但是如果您想將正數和負數分開分類怎麼辦？對於單元格 A2 到 A10 中的數字，使用以下公式之一獲得正值和負值的單獨排名：

對正數進行降序排序：

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

將正數依升序排列：

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

對負數進行降序排序：

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

將負數依升序排列：

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

結果看起來與此類似：

這些公式如何運作

首先，讓我們來看看按降序對正數進行排序的公式：

在功能的邏輯測試中 IF，檢查數字是否大於零。

如果數字大於 0，則 COUNTIF 函數傳回大於正在排序的數字的值的計數。

在此範例中，A2 包含第二大正數，COUNTIF 傳回 1，這表示只有一個數字比它大。為了從 1 而不是 0 開始排序，我們在公式結果中加入 1，因此 A2 的排序結果為 2。

什麼是 Avast？用途、特色、意見、價格如果數字大於 0，則公式傳回空字串 ("")。按升序對正數進行排序的公式的工作方式略有不同：如果數字大於 0，則第一個 COUNTIF 取得資料集中正數的總數和第二個 COUNTIF 找出有多少個值比該數字高。

然後從第一個值中減去最後一個值，得到所需的範圍。在此範例中，有 5 個正值，其中 1 個大於 A2。然後，用 1 減去 5，從而獲得 A4 的排名為 2。對負數進行分類的公式是基於類似的邏輯。

注意： 上述所有公式都忽略零值，因為0既不屬於正數集合也不屬於負數集合。若要在排序中包含零，請在公式邏輯需要時分別將 >0 和 <0 替換為 >=0 和 <=0。例如，若要從大到小對正數和零進行排序，請使用下列公式：

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

如何在 Excel 中對資料進行排序而忽略零值

正如你已經知道的，一個 RANGE公式是Excel 處理所有數字：正數、負數和零。但在某些情況下，我們只想對忽略 0 值的資料的儲存格進行排名。

忽略零的降序範圍數字：

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

忽略零的升序範圍數字：

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

哪裡 B2：B10 是要排序的數字的範圍。這個公式最好的一點是它對於正數和負數都非常有效，將零值排除在分類之外：

這個公式是如何運作的

乍一看，這個公式可能看起來有點複雜。仔細一看，邏輯非常簡單。這是 Excel 的 RANK IF 公式如何對數字從最高到最低進行排名（忽略零）：

第一個 IF 檢查數字是否為 0，如果是，則傳回一個空字串：

IF ($B2 = 0, «»,...)

如果數字不為零，則第二個 IF 檢查它是否大於 0，如果大於 XNUMX，則使用普通的 RANK/RANK.EQ 函數計算排名：

IF ($B2 > 0, 排名 ($B2, $B$2: $B$10),...)

如果數字小於 0，則按零計數調整排序。在此範例中，有 4 個正數和 2 個零。因此，對於 B10 中的最大負數，Excel 中的 Range 函數將傳回 7。為此，我們從範圍中減去零的數量：

範圍($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

是的，就是這麼簡單！將數字從最小到最大排序並忽略零的公式以類似的方式工作，並且可能是推斷其邏輯的良好心理練習。

Excel中如何以絕對值計算範圍

在處理正值和負值清單時，可能需要按數字的絕對值對數字進行排序，而忽略符號。該任務可以使用以下公式之一來完成，其核心是 ABS 函數，該函數返回 絕對值 一個數字：

向下 ABS 範圍：

=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 ) )) * (2 澳元:7 澳元<>»»))+1

向上 ABS 範圍：

=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 ) )) * (2 澳元:7 澳元<>»»))+1

結果，負數被分類為正數：

如何取得N個較大或較小的值

如果您想獲得真正的 N 個最大或最小值而不是它們的排名，請分別使用 LARGE 或 SMALL 函數。例如，我們可以使用以下公式來獲得前 3 名學生的成績：

=大($B$2:$B$7,$D3)

哪裡 B2：B7為分數列表，D3為所需範圍。此外，您可以使用以下公式檢索學生姓名 索引匹配 （只要前3名沒有重複分數即可）：

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

同樣，您可以使用 SMALL 函數提取底部 3 個值：

=小($B$2:$B$7, $D3)

看看： 如何將 Excel 與 Word 結合使用：將資料從 Excel 匯入到 Word

Pensamientos結局

這是使用 Excel 中的排名函數進行排名的方法。 感謝您的閱讀，我們希望下週在我們的部落格上見到您！有大量教程可以用來澄清與計算領域相關的疑問。回到主題，Excel 中計算範圍的替代方法有很多，每種方法都有其特殊性。您決定如何工作。

哈維爾·奇里諾斯我叫哈維爾‧奇里諾斯 (Javier Chirinos)，我對科技充滿熱情。從我記事起，我就喜歡電腦和電玩遊戲，這種愛好最終帶來了一份工作。

我在網路上發表有關技術和小玩意的文章已經超過 15 年了，尤其是在 mundobytes.COM

我也是線上交流和行銷方面的專家，並且了解 WordPress 開發。