Excel Rank函數(shù)怎么用的11個(gè)實(shí)例，含與Rank.EQ和Rank.AVG的區(qū)別、并順自動(dòng)排序和排名次及分段排序

在 Excel 中，Rank函數(shù)用于返回指定數(shù)字在數(shù)字列表中的排位，應(yīng)用此功能可實(shí)現(xiàn)把數(shù)字列表按升序或降序排序或排名次。在 Excel 新版本中，已用Rank.EQ函數(shù)和Rank.AVG函數(shù)取代了Rank函數(shù)。

Rank函數(shù)對于有重復(fù)數(shù)字的排名會(huì)把重復(fù)數(shù)字并排且會(huì)留出間隔，如果要求重復(fù)數(shù)字后的數(shù)字緊跟重復(fù)數(shù)字排名，需要用 Sum + If + CountIf、SumProduct + CountIf 或 Sum + If + FreQuency 組合實(shí)現(xiàn)。以下是Rank函數(shù)怎么用的11個(gè)實(shí)例，包含Rank函數(shù)的基本用法，Rank 與 Rank.EQ、Rank.AVG 的區(qū)別，有重復(fù)數(shù)字的并排或順排自動(dòng)排名、分段排序、把數(shù)字列表排序放到行和中國式排名。

一、Excel Rank函數(shù)的語法

1、表達(dá)式：RANK(Number, Ref, [Order])

中文表達(dá)式：RANK(要排位的數(shù)字, 數(shù)字列表, [排序方式])

2、說明：

A、Ref 為對包含數(shù)字列表的數(shù)組或單元格區(qū)域的引用；如果 Ref 中有非數(shù)字值將被忽略。

B、Order 為可選項(xiàng)，用于指定數(shù)字的排位方式；如果 Order 省略或?yàn)?0，則Rank函數(shù)返回的排位是基于數(shù)字列表按降序排列；如果Order 為非 0，則Rank函數(shù)返回的排位是基于數(shù)字列表按升序排列。

C、如果數(shù)字列表中有相同的數(shù)字，它們?nèi)绾闻盼灰?ldquo;Rank函數(shù)怎么用的實(shí)例”。

二、Excel Rank函數(shù)、Rank.EQ函數(shù)與Rank.AVG函數(shù)的區(qū)別

Rank.EQ函數(shù)與Rank.AVG函數(shù)用于取代Rank函數(shù)，它們的區(qū)別如下：

1、Rank.EQ 與 Rank 的功能完全一樣，即 Rank.EQ 繼承了 Rank 的所有功能，Rank.EQ 的表達(dá)式如下：

RANK.EQ(Number, Ref, [Order])

2、Rank.AVG 與 Rank（Rank.EQ) 的區(qū)別主要表現(xiàn)在對相同數(shù)字的排位上；對相同數(shù)字，Rank.AVG 返回平均排位，Rank（Rank.EQ) 返回最佳排序，即并列排序。Rank.AVG 的表達(dá)式為：

RANK.AVG(Number, Ref, [Order])

三、Excel Rank函數(shù)怎么用的實(shí)例（Rank.EQ函數(shù)怎么用的實(shí)例）

（一）無重復(fù)數(shù)字且數(shù)字列表按降序或升序排列的實(shí)例

1、假如返回銷量為 2685 按降序和按升序的排位。雙擊 D8 單元格，把公式 =RANK(2685,D2:D7) 復(fù)制到 D8，按回車，返回 2；雙擊 D9，把同一公式復(fù)制到 D9，然后在 D2:D7 后輸入“,1”，按回車，返回 5；操作過程步驟，如圖1所示：

圖1

2、說明：

A、公式 =RANK(2685,D2:D7) 中，2685 為要排位的數(shù)字，D2:D7 為數(shù)字列表，公式省略了排序方式 Order 參數(shù)，D2:D7 默認(rèn)按降序排列；返回 2685 在 D2:D7 中的排位為 2，正是 D2:D7 按降序排列的結(jié)果。

B、公式 =RANK(2685,D2:D7,1) 中，把排序方式設(shè)置為 1，則 D2:D7 按升序排位，返回 2685 在 D2:D7 中的排位為 5 正是 D2:D7 按升序排列的結(jié)果。

（二）無重復(fù)數(shù)字的自動(dòng)排序?qū)嵗?/strong>

1、假如要把銷量按降序排位。雙擊 E2 單元格，把公式 =RANK(D2,D$2:D$7) 復(fù)制到 E2，按回車，返回 2；選中 E2，把鼠標(biāo)移到 E2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)榧犹?hào)后，雙擊左鍵，則返回剩余數(shù)值的排位，銷量列也按降序排列好了；操作過程步驟，如圖2所示：

圖2

2、公式 =RANK(D2,D$2:D$7) 說明：

A、D2 是對列和行的相對引用，往下拖時(shí)，D2 會(huì)變?yōu)?D3、D4 等；D$2:D$7 中，$ 表示對行的絕對引用，往下拖時(shí)，D$2 不會(huì)變?yōu)?D3、D4 等，D$7 與 D$2 一樣。

B、當(dāng)公式在 D2 時(shí)，返回 D2 在 D2:D7 中的排位 2；當(dāng)公式在 D3 時(shí)，返回 D3 在 D2:D7 中的排位 1；其它的以此類推。

（三）重復(fù)數(shù)字并排的自動(dòng)排名次實(shí)例

1、假如要把價(jià)格按升序排名次。雙擊 E2 單元格，把公式 =RANK(C2,C$2:C$7,1) 復(fù)制到 E2，按回車，返回 4；選中 E2，用雙擊單元格填充柄的方法返回剩余數(shù)值的排位，價(jià)格列也隨之排好了名次；操作過程步驟，如圖3所示：

圖3

2、公式 =RANK(C2,C$2:C$7,1) 說明：

A、公式 =RANK(C2,C$2:C$7,1) 與上例的公式 =RANK(D2,D$2:D$7) 是一個(gè)意思，只是這里把排序方式設(shè)置為 1，即 C2:C7 按升序排列。

B、C2:C7 中有一個(gè)重復(fù)數(shù)字 5，兩個(gè) 5 都排在第 4 位，說明 Rank函數(shù)把重復(fù)數(shù)字并排。

（四）重復(fù)數(shù)字順排的自動(dòng)排名次實(shí)例

1、同樣以把價(jià)格按升序排名次為例。雙擊 E2 單元格，把公式 =RANK(C2,C$2:C$7,1)+COUNTIF(C$2:C2,C2)-1 復(fù)制到 E2，按回車，返回4；選中 E2，同樣用雙擊單元格填充柄的方法返回剩余數(shù)值的排位，價(jià)格列也隨之按升序順排好了名次；操作過程步驟，如圖4所示：

圖4

2、公式 =RANK(C2,C$2:C$7,1)+COUNTIF(C$2:C2,C2)-1 說明：

A、C$2:C2 用于返回 C2 至 C2 的數(shù)值，往下拖時(shí)，C2 會(huì)變 C3、C4 等，而 C$2 不會(huì)變，也就實(shí)現(xiàn)了從 C2 單元格到拖到單元格數(shù)字個(gè)數(shù)的統(tǒng)計(jì)。那么CountIf函數(shù)是怎么實(shí)現(xiàn)把重復(fù)數(shù)字順排的？

B、當(dāng)公式在 E2 時(shí)，COUNTIF(C$2:C2,C2) 在 C2 統(tǒng)計(jì) C2（即 5）的個(gè)數(shù)，結(jié)果為 1；RANK(C2,C$2:C$7,1) 返回 C2 在 C2:C7 中的排位為 4，4 + 1 - 1 恰好等于 4。

C、當(dāng)公式在 E3 時(shí)，COUNTIF(C$2:C3,C3) 在 C2:C3 統(tǒng)計(jì) C3（即 3）的個(gè)數(shù)，結(jié)果也為 1；RANK(C3,C$2:C$7,1) 返回 C3 在 C2:C7 中的排位為 2，2 + 1 - 1 恰好等于 2。

D、當(dāng)公式在 E4 時(shí)，COUNTIF(C$2:C4,C4) 在 C2:C4 統(tǒng)計(jì) C4 的個(gè)數(shù)，結(jié)果為 2（C2:C4 有兩個(gè) 5），而 RANK(C4,C$2:C$7,1) 返回 C4 在 C2:C7 中的排位為 4，4 + 2 - 1 恰好等于 5；這樣就實(shí)現(xiàn)了重復(fù)數(shù)字 5 的順排（一個(gè)排 4，另一個(gè)排 5）。

四、Excel Rank函數(shù)（Rank.EQ函數(shù)）與Rank.AVG函數(shù)的區(qū)別舉例

（一）返回重復(fù)數(shù)字的排位

1、假如用三個(gè)函數(shù)分別返回 5 在“價(jià)格”中的排位。雙擊 E2 單元格，把公式 =RANK(5,C2:C7) 復(fù)制到 E2，按回車，返回 2；雙擊 F2，把公式 =RANK.EQ(5,C2:C7) 復(fù)制到 F2，按回車，也返回 2；雙擊 G2，把公式 =RANK.AVG(5,C2:C7) 復(fù)制到 G2，按回車，返回 2.5；操作過程步驟，如圖5所示：

圖5

2、公式說明：

從演示可知，Rank函數(shù)與Rank.EQ函數(shù)返回的結(jié)果一樣，而Rank.AVG函數(shù)返回 2.5，這個(gè) 2.5 是怎么來的？C2:C7 中共有兩個(gè) 5，按降序排列，一個(gè) 5 排在第 2 位，另一個(gè) 5 排在第 3 位，Rank.AVG 返回相同數(shù)值的平均排位，2 與 3 的平均值為 2.5，因此 5 的排位為 2.5。

（二）重復(fù)數(shù)字自動(dòng)排序

1、假如用三個(gè)函數(shù)分別把“價(jià)格”按降序排位。雙擊 E2 單元格，把公式 =RANK(C2,C$2:C$7) 復(fù)制到 E2，按回車，返回 2；用雙擊單元格填充柄的方法返回剩余價(jià)格的排位。雙擊 F2，把公式 =RANK.EQ(C2,C$2:C$7) 復(fù)制到 F2，按回車，返回 2，用同樣方法返回剩余價(jià)格的排位；再把公式 =RANK.AVG(C2,C$2:C$7) 復(fù)制到 G2，按回車，返回 2.5，用同樣方法返回剩余價(jià)格的排位；操作過程步驟，如圖6所示：

圖6

2、公式說明：

從演示可知，Rank函數(shù)與Rank.EQ函數(shù)的排序結(jié)果完全一致，而Rank.AVG函數(shù)對于重復(fù)數(shù)字取平均值排位，兩個(gè) 5 都排 2.5。

五、Excel Rank函數(shù)的擴(kuò)展應(yīng)用實(shí)例

（一）Rank + OffSet + Row 組合實(shí)現(xiàn)分段排序

1、假如要求把銷量按季度分段排序。雙擊 C2 單元格，把公式 =RANK(B2,OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,),1) 復(fù)制到 C2，按回車，返回 2；選中 C2，用雙擊單元格填充柄的方法返回其它數(shù)字的排位；操作過程步驟，如圖7所示：

圖7

2、公式 =RANK(B2,OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,),1) 說明：

A、公式中的 3 表示每段 3 個(gè)數(shù)字；ROW() 用于返回公式所在行的行號(hào)；Int函數(shù)用于取整；INT((ROW()-2)/3)*3,) 用于計(jì)算 OffSet 函數(shù)要往下移的行數(shù)。

B、B$2:B$4 為第一段；OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,) 用于移到一下段，它是怎么實(shí)現(xiàn)移到下一段的？

第一段（B$2:B$4）：

當(dāng)公式在 C2 時(shí)，ROW() 返回 2，INT((ROW()-2)/3)*3 變?yōu)?INT((2-2)/3)*3，結(jié)果為 0；則 OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,)變?yōu)?OFFSET(B$2:B$4,0,)，意思是把 B$2:B$4 都下移 0 行，即不下移，則 OFFSET(B$2:B$4,0,) 返回 B$2:B$4；則公式變?yōu)?=RANK(B2,B$2:B$4)，最后在 B2 到 B4 中返回 B2 的排位 2。

當(dāng)公式在 C3 時(shí)，ROW() 返回 3，(ROW()-2)/3 變?yōu)?INT(1/3)，接著取整，結(jié)果為 0，0 * 3 結(jié)果也是 0；OffSet 變?yōu)?OFFSET(B$2:B$4,0,)；則公式變?yōu)?=RANK(B3,B$2:B$4)，最后返回 B3 在 B2 至 B4 的排位 3。

當(dāng)公式在 C4 時(shí)，ROW() 返回 4，(ROW()-2)/3 變?yōu)?INT(2/3)，接著取整，結(jié)果為 0，0 * 3 結(jié)果也是 0；OffSet 變?yōu)?OFFSET(B$2:B$4,0,)；則公式變?yōu)?=RANK(B4,B$2:B$4)，最后返回 B4 在 B2 至 B4 的排位 1。

第二段（B$5:B$7）：

當(dāng)公式在 C5 時(shí)，ROW() 返回 5，(ROW()-2)/3 變?yōu)?INT(3/3)，接著取整，結(jié)果為 1，1 * 3 結(jié)果也是 3；OffSet 變?yōu)?OFFSET(B$2:B$4,3,)，意思是把 B$2:B$4 都下移三行，則 B$2:B$4 變?yōu)?B$5:B$7，也就移到了第二段；則公式變?yōu)?=RANK(B5,B$5:B$7)，最后返回 B5 在B$5:B$7 中的排位 1；其它的以此類推。

OFFSET(B$2:B$4,3,) 把 B$2:B$4 都下移三行的執(zhí)行過程：第一次從 B2:B4 中取出 B2，然后返回 B2 下距 B2 3 行的引用，即返回 B5；第二次從 B2:B4 中取出 B3，然后返回 B3 下距 B3 3 行的引用，即返回 B6；第三次也是這樣返回對 B7 的引用，從而實(shí)現(xiàn)把 B$2:B$4 都下移三行。

（二）Rank + OffSet + Column 組合實(shí)現(xiàn)把數(shù)字列表排序放到行

1、假如要把 A 列的數(shù)字排位放到行。雙擊 C2 單元格，把公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 復(fù)制到 C2，按回車，返回 6；選中 C2，把鼠標(biāo)移到 C2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)榧犹?hào)后，按住左鍵并往右拖，一直拖到排完所有數(shù)字；操作過程步驟，如圖8所示：

圖8

2、公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 說明：

A、$A1 表示對列的絕對引用，當(dāng)往右拖時(shí)，A1 不會(huì)變?yōu)?B1、C1 等；COLUMN() 用于返回公式所有列的列號(hào)。

B、OFFSET($A1,COLUMN()-3,) 用于返回 A1 至 A6 中的數(shù)值，具體如下：

當(dāng)公式在 C2 時(shí)，COLUMN() 返回 3，則 OffSet 變?yōu)?OFFSET($A1,3-3,)，意思是返回距 A1 0 行的引用，即返回對 A1 的引用，也就是返回 6；則公式變?yōu)?=RANK(6,$A1:$A6)，接著用Rank函數(shù)返回 6 在 A1:A6 中的排位 6。

當(dāng)公式在 D2 時(shí)，COLUMN() 返回 4，則 OffSet 變?yōu)?OFFSET($A1,4-3,)，意思是返回距 A1 1 行的引用，即返回對 A2 的引用，也就是返回 24；則公式變?yōu)?=RANK(24,$A1:$A6)，接著用Rank函數(shù)返回 24 在 A1:A6 中的排位 4；其它的以此類推。

六、Excel Rank函數(shù)無法實(shí)現(xiàn)中國式排名的三種解決方法

如果要求重復(fù)數(shù)字并排，重復(fù)數(shù)字下一個(gè)數(shù)字接著重復(fù)數(shù)字排位而不是隔一位排，例如 5、3、3、2，若按降序排名次，要求兩個(gè) 3 都排第 2 名，而 2 排第 3 名；這種排名方式通常稱為中國式排名，用Rank函數(shù)無法實(shí)現(xiàn)，但用其它函數(shù)組合能實(shí)現(xiàn)，具體如下：

（一）Sum + If + CountIf

1、同樣以把“價(jià)格”列降序與升序自動(dòng)排名為例。雙擊 E2 單元格，把公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 復(fù)制到 E2，按 Ctrl + Shift + 回車，返回 2；選中 E2，用雙擊 E2 右下角的單元格填充柄的方法返回剩余數(shù)字的排名；再雙擊 F2，把公式=SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 復(fù)制到 F2，按 Ctrl + Shift + 回車，返回 4，同樣用雙擊單元格填充柄的方法返回剩余排名；操作過程步驟，如圖9所示：

圖9

2、降序排名公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 說明：

A、中國式排名的關(guān)鍵有兩點(diǎn)，一點(diǎn)是找出重復(fù)數(shù)字，另一點(diǎn)是把重復(fù)數(shù)字后的排名上升一位。

B、C$2:C$7 以數(shù)組形式返回 C2 至 C7 中的值，$ 是對行的絕對引用，作用是往下拖時(shí)，C2 不會(huì)變 C3、C4 等，C7 不會(huì)變 C8、C9 等，以確保始終取 C2:C7 的數(shù)字。

C、1/COUNTIF(C$2:C$7,C$2:C$7) 用于找出重復(fù)數(shù)字；第一次執(zhí)行時(shí)，從 C2:C7 中取出 C2（即 5），然后統(tǒng)計(jì) C2 在 C2:C7 中的個(gè)數(shù)，結(jié)果為 2；第二次執(zhí)行時(shí)，從 C2:C7 中取 C3（即 3），然后統(tǒng)計(jì) C3 在 C2:C7 中的個(gè)數(shù)，結(jié)果為 1；其它的以此類推，最后返回 {2;1;2;1;1;1}。

D、則 1/COUNTIF(C$2:C$7,C$2:C$7) 變?yōu)?1/{2;1;2;1;1;1}，進(jìn)一步計(jì)算，用 1 除以數(shù)組中的每個(gè)元素，結(jié)果為 {0.5;1;0.5;1;1;1}，這樣就把重復(fù)的數(shù)字（兩個(gè) 5）變?yōu)榱艘粋€(gè)，后面的步驟會(huì)把兩個(gè) 0.5 相加，這其實(shí)就是過濾重復(fù)數(shù)字，在前面的篇章已經(jīng)介紹過。

E、C$2:C$7>=C2 為 If 條件，用于找出 C2:C7 中所有大于等于 C2 的數(shù)值；執(zhí)行時(shí)，第一次取出 C2（即 5），C2 >= C2 成立，返回 True；第二次取出 C3（即 3），C3 >= C2 不成立，也返回 False；其它的以此類推，最后返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。

F、則公式變?yōu)?=SUM(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},""))，執(zhí)行時(shí)，第一次從條件數(shù)組中取出第一個(gè)元素True，由于它為真，所以返回第二個(gè)數(shù)組中與條件數(shù)組對應(yīng)的元素，即返回第二個(gè)數(shù)組第一個(gè)元素 0.5；第二次從條件數(shù)組取出第二個(gè)元素 FALSE，由于它為假，所以返回 If 的第三個(gè)參數(shù) ""；其它以此類推，最后返回 {0.5;"";0.5;1;"";""}。

G、則公式變?yōu)?=SUM({0.5;"";0.5;1;"";""})，最后用 Sum 把數(shù)組中的每個(gè)元素相加，由于忽略空值只把數(shù)字相加，所以結(jié)果為 2；兩個(gè)0.5 是重復(fù)數(shù)字 C2 與 C4 中 5 的值，1 是大于 5 的值（即 C5 中的 12 ），把它們相加恰好是重復(fù)數(shù)字 5 的排名。

H、當(dāng)公式在 C5 時(shí)，公式變?yōu)?=SUM(IF(C$2:C$7>=C5,1/COUNTIF(C$2:C$7,C$2:C$7),""))，1/COUNTIF(C$2:C$7,C$2:C$7) 同樣返回{0.5;1;0.5;1;1;1}；C$2:C$7>=C5 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}，即只有 C5（即 12）的位置為真；則公式變?yōu)椋?SUM(IF

(FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},""))，進(jìn)一步計(jì)算變?yōu)?=SUM({"";"";"";1;"";""})，數(shù)組中只有 C5 的位置為1，其它的都為 ""，最后返回 1，也就是最大值 C5 的排名。

另外，用這個(gè)公式 =SUM(IF(C$2:C$7<=C2,"",1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以實(shí)現(xiàn)降序排名，即把 >= 改為 <=，同時(shí) If 的后面兩個(gè)參數(shù)要調(diào)換次序，并且還要在公式后加 1。

3、升序排名公式 =SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 說明：

升序排名公式與降序排名公式的區(qū)別僅是 If 條件中的 >= 變?yōu)?<=，即由找出所有大于等于當(dāng)前單元格（C2）的數(shù)字變?yōu)檎页鏊行∮诘扔诋?dāng)前單元格的數(shù)字。

（二）SumProduct + CountIf

1、同樣以把“價(jià)格”列降序與升序自動(dòng)排名為例。雙擊 E2 單元格，把公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 復(fù)制到 E2，按回車，返回 2；選中 E2，用雙擊單元格填充柄的方法返回剩余數(shù)字的排名；再雙擊 F2，把公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 復(fù)制到 F2，按回車，返回 4，再用雙擊單元格填充柄的方法返回剩余排名；操作過程步驟，如圖10所示：

圖10

2、降序排名公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 說明：

A、公式與 Sum + If + CountIf 是一個(gè)意思，只是這里用 SumProduct 代替了 Sum + If。

B、COUNTIF(C$2:C$7,C$2:C$7) 返回 {2;1;2;1;1;1}；(C$2:C$7>=C2) 返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。

C、則公式變?yōu)?=SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}/{2;1;2;1;1;1})，進(jìn)一步計(jì)算，第一次分別取出分子與分母數(shù)組的第一個(gè)元素 TRUE 和 2，計(jì)算時(shí)，TRUE 被轉(zhuǎn)為 1、FALSE 被轉(zhuǎn)為 0，1/2 = 0.5；第二次再分別取出它們的第二元素 FALSE 和 1，0/2 = 0；其它的以此類推。

D、則公式變?yōu)?=SUMPRODUCT({0.5;0;0.5;1;0;0})，最后對數(shù)組中的元素求和，結(jié)果為 2，即重復(fù)數(shù)字 5 的排名為 2。

另外，用這個(gè)公式 =SUMPRODUCT((C$2:C$7>C2)*(1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以實(shí)現(xiàn)降序排名，也就是把條件 C$2:C$7>C2 提取出作為一個(gè)獨(dú)立的條件，再用 1 比 COUNTIF(C$2:C$7,C$2:C$7) 作為第二個(gè)條件，并用 * 把兩個(gè)條件連接起來表示“與”的關(guān)系，最后還要在公式后面加 1。

3、升序排名公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 說明：

升序排名公式與降序排名公式的區(qū)別僅是把分子 C$2:C$7<=C2 中的 >= 改為 <=，即由找出所有大于等于當(dāng)前單元格（C2）的數(shù)字變?yōu)檎页鏊行∮诘扔诋?dāng)前單元格的數(shù)字。

（三）Sum +If + FreQuency

1、同樣以把“價(jià)格”列降序與升序自動(dòng)排名為例。雙擊 E2 單元格，把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 復(fù)制到 E2，按 Ctrl + Shift + 回車，返回 2；選中 E2，雙擊 E2 的單元格填充柄返回剩余數(shù)字的排名；再雙擊 F2，把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 復(fù)制到 F2，按回車，返回 4，再雙擊 F2 的單元格填充柄返回剩余排名；操作過程步驟，如圖11所示：

圖11

2、降序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 說明：

A、FREQUENCY(C$2:C$7,C$2:C$7) 用于找出 C2:C7 中每個(gè)數(shù)字在 C2:C7 出現(xiàn)的頻率，功能有點(diǎn)像 COUNTIF(C$2:C$7,C$2:C$7)，但FreQuency 對重復(fù)數(shù)字只統(tǒng)計(jì)一次，而 CountIf 統(tǒng)計(jì)兩次。

執(zhí)行時(shí)，第一次先從第二個(gè)參數(shù)中取 C2（即 5），然后在 C2:C7 中統(tǒng)計(jì)小于等于 5 且大于 3.5 的數(shù)值個(gè)數(shù)（結(jié)果為 2），因?yàn)橹苯影袰2:C7 作為 Frequency函數(shù)的 Bins_Array 參數(shù)（即對要統(tǒng)計(jì)的數(shù)值劃分區(qū)間參數(shù)），而比 5 小的值是 3.5，因此 3.5 與 5 為一個(gè)區(qū)間；第二次先從第二個(gè)參數(shù)中取 C3（即 3），同樣在 C2:C7 中統(tǒng)計(jì)小于等于 3 且大于 2.5 的數(shù)值個(gè)數(shù)（結(jié)果為 1）；第三次取出 5，由于 5 已經(jīng)統(tǒng)計(jì)過不再統(tǒng)計(jì)，因此結(jié)果為 0；其它的以此類推，最后返回?cái)?shù)組 {2;1;0;1;1;1;0}。

C2:C7 中只有 6 個(gè)數(shù)字，返回的數(shù)組為什么會(huì)有 7 個(gè)元素，因?yàn)镕reQuency函數(shù)返回的數(shù)組會(huì)比 Bins_Array 參數(shù)多一個(gè)元素。

B、C$2:C$7>C2 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。

C、IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2) 變?yōu)?IF({2;1;0;1;1;1;0},{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE})；執(zhí)行時(shí)，第一次從條件數(shù)組取第一個(gè)元素 2，由于大于 0 的視為真、小于 0 的視為假，所以返回 If 的第二個(gè)參數(shù)，即第二個(gè)數(shù)組中與 2 對應(yīng)的元素 FALSE；第二次從條件數(shù)組取第二個(gè)元素 1，由于為真，返回第二個(gè)數(shù)組中第二個(gè)元素 FALSE；第三次從條件數(shù)組取第三個(gè)元素 0，由于為假，所以返回 If 的第三個(gè)參數(shù)，由于 If 省略了第三個(gè)參數(shù)，默認(rèn)返回 FALSE；其它的以此類推，最后返回{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。

D、則公式變?yōu)?=SUM(--{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+1，進(jìn)一步計(jì)算，把數(shù)組的所有元素轉(zhuǎn)為數(shù)值，則公式變?yōu)?=SUM({0;0;0;1;0;0;0})+1，-- 用于把文本或邏輯值轉(zhuǎn)為數(shù)值，相當(dāng)于Value函數(shù)，最后用 Sum 把數(shù)組中的元素加起來再加 1，結(jié)果為 2。

3、升序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 說明：

升序排名公式與降序排名公式的區(qū)別僅為把 C$2:C$7>C2 中的 > 改為 <=，即由找出所有大于當(dāng)前單元格（C2）的數(shù)字變?yōu)檎页鏊行∮诘扔诋?dāng)前單元格的數(shù)字，并且把公式后面的 1 去掉。