說到排名，大家都會知道可以用Excel中的數(shù)據(jù)排序功能實現(xiàn)。但是，如果要分析的維度很多，希望任意選擇要分析的維度進行排序，使用排序功能就顯然不能滿足了，本文就來介紹如何利用Excel提供的Large和Small函數(shù)，建立高效實用的排序分析模板。
1、關(guān)鍵函數(shù)介紹
Large函數(shù)
語法：
Large(一組數(shù)字或單元格引用，k)
說明：獲取第K大數(shù)，可用于降序排序
Small函數(shù)
語法：
Small(一組數(shù)字或單元格引用，k)
說明：獲取第K小數(shù)，可用于升序排序
特別說明：
要排序的數(shù)據(jù)必須是數(shù)字，忽略單元格中的文本和邏輯值，不允許有錯誤值；
要排序的數(shù)字必須是一維數(shù)組，或一列或一行單元格區(qū)域；
k值是自然數(shù)序列，1表示第一大或小，2表示第二大或小，以此類推；
在實際排序中，可使用Row或Column函數(shù)自動填充k值。
2、創(chuàng)建排名分析模板：
源數(shù)據(jù)如下，包括：學(xué)員及語數(shù)外三科考試成績
分析模板功能如下：
可選擇分析的科目
可選擇排序方式：升序或降序
可輸入?yún)⑴c排名的數(shù)據(jù)個數(shù)
以下介紹實現(xiàn)方法：
定義名稱“科目”用于K2單元格設(shè)置數(shù)據(jù)驗證選擇科目
M2單元格設(shè)置數(shù)據(jù)驗證，以選擇排序方式
O2單元格設(shè)置數(shù)據(jù)驗證，輸入數(shù)據(jù)個數(shù)
增加F/G/H三列輔助列：
輔助列：F列
F列作用：
根據(jù)所選科目獲取待排序分析的源數(shù)據(jù)
F2公式：
=VLOOKUP(A2,$A$2:$D$13,MATCH($K$2,$A$1:$D$1,0),FALSE) RAND()/10000000
拖動拓展F2公式到F13
公式講解：
MATCH($K$2,$A$1:$D$1,0)
根據(jù)所選科目決定哪列分數(shù)
VLOOKUP(A2,$A$2:$D$13,所選科目列號,FALSE)
根據(jù)所選科目列號獲取該科分數(shù)
=VLOOKUP(...) RAND()/10000000
加上極小的隨機數(shù)，以規(guī)避相同分數(shù)無法區(qū)分學(xué)員姓名問題
輔助列：H列
H列作用：
根據(jù)選擇的排序方式，對數(shù)據(jù)進行排序
H2公式：
=IF($M$2='升序',SMALL($F$2:$F$13,ROW(A1)),LARGE($F$2:$F$13,ROW(A1)))
拖動拓展H2公式到H13
輔助列：G列
G列作用：
獲取分數(shù)排序后對應(yīng)的學(xué)員姓名
G2公式：
=INDEX($A$2:$A$13,MATCH(H2,$F$2:$F$13,0))
拖動拓展G2公式到G13
公式講解：
MATCH(H2,$F$2:$F$13,0)
H2單元格分數(shù)排序前在F列的位置
=INDEX($A$2:$A$13,分數(shù)排序前在F列的位置)
根據(jù)位置獲取對應(yīng)的學(xué)員姓名
分析圖表數(shù)據(jù)源名稱定義：
排序分數(shù)：
根據(jù)O2輸入的個數(shù)在H列提取參與排名分析的分數(shù)數(shù)據(jù)
=OFFSET(排名分析!$H$2,,,排名分析!$O$2,1)
排序姓名：
根據(jù)O2輸入的個數(shù)在G列提取參與排名分析的姓名數(shù)據(jù)
=OFFSET(排名分析!$G$2,,,排名分析!$O$2,1)
插入柱形圖并設(shè)置數(shù)據(jù)源：
插入空白柱形圖，在圖標上右擊選擇“選擇數(shù)據(jù)”，為圖表添加數(shù)據(jù)
點擊“添加”
確定返回數(shù)據(jù)源設(shè)置畫面
繼續(xù)點擊“編輯”
設(shè)置圖表標題：學(xué)員成績分析
根據(jù)個人風(fēng)格對圖表進行其他美化修飾，至此完成模板制作，可通過改變K2、M2、O2單元格值，看看對應(yīng)的分析效果。
聯(lián)系客服
