跟我一起，穿越時間！

在上一期的連載中，我們通過分類彙總、數據透視、Rank函數、SUMPRODUCT函數等方式統計了每個人的銷售排名，沒有閱讀的可以點擊頭像或鍊接跳轉：

穿越時間Excel升級之路連載9:MATCH函數及SUMPRODUCT不重複值計數

穿越時間Excel升級之路連載10：Index函數Offset函數提取不重複值

穿越時間Excel升級之路連載11：分類彙總 數據透視 SUMPRODUCT排名

統計排名是為了什麼呢？當然是為了更直觀地展示每個人的銷售情況并确定榮譽，那麼今天的内容就是讓每個人的銷售狀态更直觀，同時确定每個人的榮譽。涉及應用條件格式、PERCENTRANK.INC函數百分比排位、Vlookup函數近似查詢自動分級等内容。

下面讓我們走到Excel升級之路連載12：百分比排位及Vlookup函數近似查詢

我們可以先來看看下面這張對比圖片，圖片的上半部分就是連載11中的結果，無論是銷售額還是排名，都隻是數字，整個表格顯得光秃秃空蕩蕩的；圖片的下半部分則在單元格中加入了代表數據大小的長條、代表名次高低的彩色圓點，瞬間就有了高端大氣上檔次的感覺，這是怎麼實現的呢?

1、條件格式——數據條

雖然上面的效果看起來類似Excel中的圖表——條形圖，但這并不是通過插入圖表實現的，當然也肯定不是手工繪制的，實現他們的方法都是——條件格式。

在前面處理銷售人員重複的身份證号時（連載9：MATCH 函數及SUMPRODUCT不重複值計數），我們曾用條件格式突出顯示重複的身份證号，因此相信你已經非常清楚條件格式按鈕的位置了。

除了突出顯示重複值，條件格式中還有非常多高效的功能可以讓Excel表格更加直觀美觀，例如“數據條”、“色階”、“圖标集”。今天我們用到的兩項為“數據條”和“圖标集”。

默認情況下，“數據條”的長度就代表單元格中數值的大小，數值越大，數據條越長。“數據條”提供兩種填充方案：漸變填充和實心填充，因此隻需應用一下，即可為銷售額添加效果。

通過點擊“其他規則”，我們可以自由地調整不同的效果。

注意，無論我們調整單元格尺寸還是調整數據大小，“數據條”都可以實現自動更新。

2、條件格式——圖标集

至于每個人排名前面的彩色圓點，我們是通過條件格式中的圖标集來實現的。

“圖标集”中的圖标分為“方向類”、“形狀類”、“标記類”、“等級類”，可以根據需要來進行選擇。

首先，我們确定一套規則：

排名前10%（不含）為星耀黑燈，授予“勝天至尊”榮譽；

排名10%-50%（不含）為華麗紅燈，授予“一代宗師”榮譽；

排名50%-90%（不含）為燦爛黃燈，授予“江湖豪俠”榮譽；

排名90%以後的為萌新綠燈，授予“門派新秀”榮譽。

要實現這樣的效果，必須通過“其他規則”來自己設置。

首先選擇“圖标樣式”為四色交通燈，默認圖标順序為“綠、黃、紅、黑”；

然後在下方調整規則，輸入我們定義的分隔數值90%、50%、10%，然後确定。

這時效果就添加好了：

第一部分中我們已經确定了一套排名的規則及榮譽稱号，但怎麼把榮譽稱号填進去呢？

1、分析：要想将榮譽稱号填進去，就需要知道每個人的排名所處的位置是前百分之幾，那麼這要怎麼計算？

我們繼續向下看。

2、計算某人排名所處的百分比排位，有兩種規則。

規則一：

某人排名所處的百分比排位=（比此排名小的數據個數）/（總數據個數-1）

通過下面兩個公式都可以計算：

=COUNTIF($AI$2:$AI$9,"<"&AI2)/(COUNT($AI$2:$AI$9)-1)

=SUMPRODUCT(($AI$2:$AI$9<AI2)*1)/(COUNT($AI$2:$AI$9)-1)

Excel中提供了直接的函數：

Percentrank函數和Percentrank.inc函數，字面意思就是百分比排名，百分比值的範圍為 0 到 1（含 0 和 1）。

Percentrank函數PERCENTRANK(array,x,[significance])

Percentrank.inc函數PERCENTRANK.INC(array,x,[significance])

其中array代表數值數組或者數據區域，x代表要知道其排位的值，Significance是用來調整結果的小數位數的，默認顯示3位。

這兩個函數的計算結果都是一樣的，之所以存在兩個，是因為Percentrank函數可以保持和更早版本的Excel兼容。

所以我們使用的公式為：

=PERCENTRANK($AI$2:$AI$9,AI2)

=PERCENTRANK.INC($AI$2:$AI$9,AI2)

上圖中可以看到依據這種規則計算出來的結果，舉個例子，“瓊英”的銷售額最高，排名為1，那麼她的百分比排位就是前0%，“周婉悅”的排名為2，那麼她的百分比排位就是前14.2%

規則二：

某人排名所處的百分比排位=（比此排名小的數據個數 1）/（總數據個數 1）

這種規則下計算的百分比排位範圍是0到1，不包含0和1

Excel中提供了名為Percentrank.exc的函數

PERCENTRANK.EXC(array,x,[significance])

我們可以驗證一下，通過具體的規則和Percentrank.exc函數的計算結果都是一樣的。

=(COUNTIF($AI$2:$AI$9,"<"&AI2) 1)/(COUNT($AI$2:$AI$9) 1)

=(SUMPRODUCT(($AI$2:$AI$9<AI2)*1) 1)/(COUNT($AI$2:$AI$9) 1)

=PERCENTRANK.EXC($AI$2:$AI$9,AI2)

下文中，我們使用的都是規則一中的百分比排位。

我們調整一下百分比排位的顯示方式，這樣就比較直觀了。

接下來，我們根據上面确定的規則，把每個人都榮譽獎項填寫到單元格裡。

注意，不是手動填寫！那怎麼操作？

1、Vloolup函數

我們需要借助Vlookup函數來完成。

先複習一下Vlookup函數的語法

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Vlookup(查詢值，查詢區域，要返回的目标值在查詢區域中的列号，[匹配方式])

相信很多人用得比較多的是精确匹配查詢，即最後一個參數為FALSE，如果找到了，那麼vlookup就返回找到的結果（當查詢區域第一列中有兩個或更多值與查詢值匹配時，使用第一個找到的值）；如果找不到則返回錯誤值#N/A

如果最後一個參數省略或者設為“TRUE”，那麼Vlookup執行的則是查找精确匹配值或近似匹配值，且要求查詢區域的第一列必須升序排列。

注意：是查找精确匹配值或近似匹配值，即如果找到精确匹配值，那就返回精确匹配值；如果找不到精确匹配值，則返回小于查找值的最大值。

利用Vlookup近似匹配的特性，我們就可以實現對不同分段區域自動填寫不同的值。

2、Vloolup近似查詢公式

=VLOOKUP(AJ2,{0,"勝天至尊";0.1,"一代宗師";0.5,"江湖豪俠";0.9,"門派新秀"},2,TRUE)

解釋一下：

{0,"勝天至尊";0.1,"一代宗師";0.5,"江湖豪俠";0.9,"門派新秀"}作為vlookup函數的第二個參數，即查詢區域，它是一個常量數組，當然也可以使用單元格引用。

隻是需要注意的是，vlookup函數近似匹配返回小于查找值的最大值，因此，我們隻需要規則範圍的下限值作為分界，即0、0.1、0.5、0.9，而且是升序排列！

這樣在一個單元格中輸入公式，然後拖動填充即可完成所有人員的榮譽填寫。

例如“蘭香凝”百分比排位42.8%，處于前10%-50%，應為“一代宗師”；vlookup查詢{0,"勝天至尊";0.1,"一代宗師";0.5,"江湖豪俠";0.9,"門派新秀"}，找小于等于42.8%的最大值為0.1，近似匹配的結果就是“一代宗師”。

這裡的用法就是Vlookup函數近似查詢實現的自動分級，如果我們換一種方法，通過if函數四層嵌套來判斷，然後拖動填充也是可以的。

=IF(AJ2<0.1,"勝天至尊",IF(AJ2<0.5,"一代宗師",IF(AJ2<0.9,"江湖豪俠",IF(AJ2<=1,"門派新秀",""))))

或者：

=IF(AJ2>=0.9,"門派新秀",IF(AJ2>=0.5,"江湖豪俠",IF(AJ2>=0.1,"一代宗師",IF(AJ2>=0,"勝天至尊",""))))

Excel中允許嵌套最多64個不同的IF函數，但是IF函數嵌套過多時公式會難以閱讀，目前在最新版本的Excel中已經有了IFS函數，相比多重IF函數嵌套，IFS函數要更加簡潔一些。

如果上面的都理解了，我們可以采取同樣的方法，根據銷售額或者百分位排名，為不同人員賦予不同的傭金比率，這些本文就不再詳述了。

以上就是連載12的全部内容，如果對數據背景不清楚或有不理解的可以先看前面的連載打牢基礎。

點擊頭像或鍊接跳轉：

穿越時間Excel升級之路連載9:MATCH函數及SUMPRODUCT不重複值計數

穿越時間Excel升級之路連載10：Index函數Offset函數提取不重複值

穿越時間Excel升級之路連載11：分類彙總 數據透視 SUMPRODUCT排名

更多精彩，敬請關注，投币贊賞，感謝支持。

（原創連載，個人觀點保留，禁止任何未經授權的非本人賬号複制文章到其他平台發布）

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!