說到排名，大家都會知道可以用Excel中的數(shù)據(jù)排序功能實現(xiàn)。但是，如果要分析的維度很多，希望任意選擇要分析的維度進(jì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ù)所選科目決定哪列分?jǐn)?shù)

VLOOKUP(A2,$A$2:$D$13,所選科目列號,FALSE)

根據(jù)所選科目列號獲取該科分?jǐn)?shù)

=VLOOKUP(...) RAND()/10000000

加上極小的隨機(jī)數(shù)，以規(guī)避相同分?jǐn)?shù)無法區(qū)分學(xué)員姓名問題

輔助列：H列

H列作用：

根據(jù)選擇的排序方式，對數(shù)據(jù)進(jìn)行排序

H2公式：

=IF($M$2='升序',SMALL($F$2:$F$13,ROW(A1)),LARGE($F$2:$F$13,ROW(A1)))

拖動拓展H2公式到H13

輔助列：G列

G列作用：

獲取分?jǐn)?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單元格分?jǐn)?shù)排序前在F列的位置

=INDEX($A$2:$A$13,分?jǐn)?shù)排序前在F列的位置)

根據(jù)位置獲取對應(yīng)的學(xué)員姓名

分析圖表數(shù)據(jù)源名稱定義：

排序分?jǐn)?shù)：

根據(jù)O2輸入的個數(shù)在H列提取參與排名分析的分?jǐn)?shù)數(shù)據(jù)

=OFFSET(排名分析!$H$2,,,排名分析!$O$2,1)

排序姓名：

根據(jù)O2輸入的個數(shù)在G列提取參與排名分析的姓名數(shù)據(jù)

=OFFSET(排名分析!$G$2,,,排名分析!$O$2,1)

插入柱形圖并設(shè)置數(shù)據(jù)源：

插入空白柱形圖，在圖標(biāo)上右擊選擇“選擇數(shù)據(jù)”，為圖表添加數(shù)據(jù)

點擊“添加”

確定返回數(shù)據(jù)源設(shè)置畫面

繼續(xù)點擊“編輯”

設(shè)置圖表標(biāo)題：學(xué)員成績分析

根據(jù)個人風(fēng)格對圖表進(jìn)行其他美化修飾，至此完成模板制作，可通過改變K2、M2、O2單元格值，看看對應(yīng)的分析效果。