排名與百分位函式整理：RANK、LARGE、PERCENTILE、PERCENTRANK、QUARTILE

月底要排名了：每家分店賣了幾杯，誰第幾名、第 3 名賣多少、「我這家贏過幾成的分店」。這幾個問題剛好對到一整族函式，RANK、LARGE、PERCENTILE、PERCENTRANK、QUARTILE，而且每個還有 .INC、.EXC 的版本，名字長得很像，一開始真的會搞混。

我自己平常最常用的是 RANK 跟 LARGE，PERCENTILE 偶爾，QUARTILE.EXC 這種在工作上幾乎沒碰過。這篇當工具書，需要哪個就跳到哪個的標題查。

---

這篇用的資料

10 家分店某個月賣出的杯數，放在 A1:B11，其中有兩組同分（967 杯、1103 杯），後面講並列時會用到：

由小到大排好是 743、856、967、967、1019、1103、1103、1176、1284、1342，一共 10 筆。下面的範例都以 B2:B11 當資料範圍。

---

排名

這一族是「給一個值，告訴我它排第幾」。

如果規則是先比金牌，再比銀牌、銅牌，就要同時比較多欄。逐欄計算「有幾隊在前面」、同牌並列後跳號的做法，見 獎牌榜排序與排名。

← 左右滑動看完整的圖，點圖可放大

圖：同分的時候，RANK 並列、下一名跳號，RANK.AVG 取平均。

RANK

傳回某個值在資料中的排名。

語法：=RANK(值, 資料, [是否遞增])

• 值：要查排名的數字。
• 資料：整份要比較的範圍。
• 是否遞增：選填。省略或填 FALSE 是由大到小（最大的是第 1 名），填 TRUE 是由小到大。

舉例來說，要算「新北板橋」（B4，1103 杯）排第幾：

比 1103 大的有 1342、1284、1176 三個，所以是第 4 名，你應該會得到 4！

要往下複製給每一家分店的話，資料範圍要加 $ 鎖住（$B$2:$B$11），不然複製到下一列，範圍會跟著往下滑，第一筆資料就掉出去了。

把第 3 個參數改成 TRUE，就變成「由小到大」：

由小到大，比 1103 小的有 743、856、967、967、1019 五個，所以是第 6 名。

並列的時候，RANK 會給同分的人一樣的名次，下一個名次跳過去。兩家 1103 杯都是第 4 名，下一家 1019 杯就直接是第 6 名，沒有第 5 名。全部 10 家的結果如下：

「值」如果不在資料裡，例如 =RANK(1000, $B$2:$B$11)，會是 #N/A。

RANK.EQ

跟 RANK 一樣，同分時給同分的人最前面的那個名次。

語法：=RANK.EQ(值, 資料, [是否遞增])

=RANK.EQ(B4, $B$2:$B$11) 一樣是 4。這個名字的 EQ 是 equal（相等）的意思，指的是「相等的值拿同樣的名次」。

我個人幾乎都寫 RANK，因為兩個的結果沒有不同，少打三個字。如果同事習慣用有 .EQ 的寫法（Excel 的函式清單裡兩種都有），照他們的寫法也沒問題。

RANK.AVG

同分時，傳回這幾個名次的平均。

語法：=RANK.AVG(值, 資料, [是否遞增])

兩家 1103 杯本來佔第 4 和第 5 名，平均是 4.5：

結果是 4.5。兩家 967 杯佔第 7、第 8 名，每一家是 7.5。

名次要頒獎、公告的時候，我用 RANK，大家看得懂「並列第 4」。要拿名次再去做統計（例如算名次的平均），就用 RANK.AVG，因為每個名次加起來的總和不會被並列改掉。

---

第 N 大與第 N 小

這一族反過來，「告訴我第幾名的值是多少」。

LARGE

傳回資料中第 N 大的值。

語法：=LARGE(資料, N)

• 資料：要找的範圍。
• N：第幾大，1 是最大。

由大到小是 1342、1284、1176，所以第 3 大是 1176。

並列的時候，LARGE 看的是位置，同分的值會各佔一個名額。=LARGE(B2:B11, 4) 跟 =LARGE(B2:B11, 5) 都是 1103，要到第 6 大才是 1019。

N 如果是 0，或大於資料的個數（這份資料是 10 個，寫 11），會是 #NUM!。

SMALL

傳回資料中第 N 小的值，語法跟 LARGE 一樣。

語法：=SMALL(資料, N)

由小到大是 743、856，第 2 小是 856。

LARGE(資料, 1) 等於 MAX，SMALL(資料, 1) 等於 MIN，這兩個我反而都直接寫 MAX、MIN。

順便找出是誰

LARGE 只給你數字，要知道是哪家分店，可以搭配 FILTER（用法在 FILTER 函數教學）：

第 4 大是 1103，所以會溢出兩列：

用 XLOOKUP（見 XLOOKUP 函式入門）也可以查，不過它只會回傳第一個符合的，同分的第二家就看不到了。

---

百分位數

「百分位數」是反過來問：要落在整份資料的 40% 位置，那個值是多少。

← 左右滑動看完整的圖，點圖可放大

圖：INC 與 EXC 差在「兩端要不要算進去」，後面三族函式都分這兩個版本。

.INC 是 inclusive（包含），最小值是 0、最大值是 1，10 筆資料平均分在中間，間距 1/9。.EXC 是 exclusive（排除），0 跟 1 都不算，10 筆資料落在中間，間距 1/11。資料只有 10 筆的時候，兩種算法差得很明顯。

PERCENTILE

傳回資料的第 k 個百分位數，k 介於 0 到 1。

語法：=PERCENTILE(資料, 百分位數)

• 百分位數：0 到 1 的小數，0.4 就是 40%。

PERCENTILE 等同 PERCENTILE.INC。位置落在兩個資料中間時，會用內插法算出中間值。以 0.4 為例，位置是 0.4 × (10 − 1) = 3.6，也就是排好序後第 4 個（967）往第 5 個（1019）走 0.6：

手算：967 + 0.6 × (1019 − 967) = 998.2，結果就是 998.2。

寫 0.5 的話，結果是 1061，剛好是 1019 與 1103 的平均，也就是中位數。

PERCENTILE.INC

跟 PERCENTILE 同一個東西，只是名字多了 .INC。

語法：=PERCENTILE.INC(資料, 百分位數)

=PERCENTILE.INC(B2:B11, 0.4) 是 998.2，=PERCENTILE.INC(B2:B11, 0.9) 是 1289.8。

.INC 的 k 可以是 0 跟 1，分別傳回最小值 743 跟最大值 1342。

PERCENTILE.EXC

排除 0 跟 1 的百分位數。

語法：=PERCENTILE.EXC(資料, 百分位數)

位置的算法變成 k × (n + 1)，同樣 0.4，位置是 0.4 × 11 = 4.4，也就是第 4 個（967）往第 5 個（1019）走 0.4：

手算：967 + 0.4 × 52 = 987.8，比 INC 的 998.2 小。

k 有範圍限制：要在 1/(n+1) 到 n/(n+1) 之間，10 筆資料就是約 0.0909 到 0.9091。所以 =PERCENTILE.EXC(B2:B11, 0.05) 跟 =PERCENTILE.EXC(B2:B11, 1) 都是 #NUM!。=PERCENTILE.EXC(B2:B11, 0.9) 還在範圍內，結果是 1336.2。

要選哪個？我會看使用的場合：學校成績、公司內部排名，我用 INC，因為最高分就是 100%，直覺。統計課本或要跟別人對數字的時候，要先確認對方用的是哪一種。

---

百分比排名

這一族是百分位數的反向：給一個值，告訴我它在整份資料的百分之幾。

PERCENTRANK

傳回某個值的百分比排名，0 到 1 之間。

語法：=PERCENTRANK(資料, 值, [有效位數])

• 值：要查的數字。
• 有效位數：選填，預設 3 位。

PERCENTRANK 等同 PERCENTRANK.INC，算法是「比它小的有幾個，除以（總數 − 1）」。台南東區 1019 杯，比它小的有 4 個（743、856、967、967）：

4 ÷ 9 = 0.4444…，預設只留 3 位有效數字，結果是 0.444。

如果「值」不在資料裡，但落在最小與最大之間，會用內插法。例如 1050 在 1019（4/9）與 1103（5/9）之間：

手算：4/9 + (1050 − 1019) ÷ (1103 − 1019) × 1/9 ≈ 0.4854，顯示 0.485。

「值」比資料的最小值還小或比最大值還大，例如 =PERCENTRANK(B2:B11, 1500)，會是 #N/A。

你可能會想：這跟 =COUNTIF(B2:B11, "<"&B4) / COUNT(B2:B11) 算「贏過幾成的人」一不一樣？不太一樣。後者分母是 10，1103 杯那家是 5 ÷ 10 = 0.5；PERCENTRANK 分母是 9，5 ÷ 9 = 0.555555…，省略精度引數時 PERCENTRANK 保留 3 位並四捨五入，結果是 0.556，不是 0.555。我個人要講「贏過幾成」的時候用 COUNTIF 那條，因為對老闆最好解釋。

精度由第三個引數決定：同一個 1103，省略精度與明填 3 都是 0.556，明填 6 則是 0.555556。這是函式依精度引數回傳的值，不是只把小數顯示位數調多；要跟 Excel 對數字時也要留意，Excel 的 PERCENTRANK 是直接截斷多餘位數（微軟官方範例把 6/9 寫成 0.666），同樣的 5/9 在 Excel 會是 0.555，Sheets 則是四捨五入的 0.556。

PERCENTRANK.INC

跟 PERCENTRANK 同一個東西。

語法：=PERCENTRANK.INC(資料, 值, [有效位數])

=PERCENTRANK.INC(B2:B11, 1019) 是 0.444。最小值 743 是 0，最大值 1342 是 1。

PERCENTRANK.EXC

排除 0 跟 1 的版本，算法是「位置 ÷（總數 + 1）」。

語法：=PERCENTRANK.EXC(資料, 值, [有效位數])

1019 在排好序後是第 5 個，5 ÷ 11 = 0.454545…。我把有效位數改成 4：

結果是 0.4545。同樣的資料，INC 版本是 0.444，EXC 版本是 0.4545，這個差別跟上一節的百分位數是同一回事。

最小值 743 在 EXC 是 1/11 ≈ 0.091，不是 0。

---

四分位數

四分位數是把資料切成四等份的三個切點。0、1、2、3、4 分別是最小值、25%、50%（中位數）、75%、最大值。

QUARTILE

傳回資料的四分位數。

語法：=QUARTILE(資料, 四分位數)

• 四分位數：0 到 4 的整數，0 最小值、1 第一四分位、2 中位數、3 第三四分位、4 最大值。

QUARTILE 等同 QUARTILE.INC，也就是 PERCENTILE.INC 的 0.25、0.5、0.75。

位置是 0.75 × 9 = 6.75，在第 7 個（1103）往第 8 個（1176）走 0.75：1103 + 0.75 × 73 = 1157.75。

第一四分位數 =QUARTILE(B2:B11, 1) 是 967，因為落在兩個 967 之間，內插結果還是 967。中位數 =QUARTILE(B2:B11, 2) 是 1061。

QUARTILE.INC

跟 QUARTILE 同一個東西。

語法：=QUARTILE.INC(資料, 四分位數)

=QUARTILE.INC(B2:B11, 3) 是 1157.75，=QUARTILE.INC(B2:B11, 4) 是最大值 1342。

QUARTILE.EXC

排除 0 跟 4 的版本。

語法：=QUARTILE.EXC(資料, 四分位數)

• 四分位數：只能 1、2、3。

位置是 0.25 × 11 = 2.75，在第 2 個（856）往第 3 個（967）走 0.75：856 + 0.75 × 111 = 939.25。第三四分位數 =QUARTILE.EXC(B2:B11, 3) 是 1203，中位數 =QUARTILE.EXC(B2:B11, 2) 一樣是 1061。

=QUARTILE.EXC(B2:B11, 0) 或 =QUARTILE.EXC(B2:B11, 4)，會是 #NUM!。

---

空白、文字與並列

這幾個函式遇到「不太乾淨」的資料，行為大致一致，整理如下：

• 資料範圍裡的空白儲存格與文字，會被略過，不算進個數。B2:B11 如果多了一格空白、一格「缺」，LARGE(B2:B13, 1) 還是 1342，RANK 的名次也不變。
• 「值」那個參數如果不在資料裡（RANK）或超出範圍（PERCENTRANK），會是 #N/A。
• LARGE、SMALL 的 N 超過資料個數，會是 #NUM!。
• .EXC 的百分位數與四分位數超出範圍，會是 #NUM!。
• 並列：RANK、RANK.EQ 給同樣的名次並跳號，RANK.AVG 給平均，LARGE、SMALL 每個同分的值各佔一個名額。

最容易出錯的是資料中混了「看起來是數字的文字」。對齊方式可以手動改，不能單靠左右對齊判斷型態；排名前先用 ISNUMBER 檢查，確認文字是否需要轉成數值，再決定要不要略過。

---

要拿名次做獎金分級，可以接 IFS；算整體的分布（標準差、中位數）在 統計分析函式。

我是喜特先生，Mr. Sheet，我們下個教學見！