Excel中如何进行多维度排名分析

说到排名，大家都会知道可以用Excel中的数据排序功能实现。但是，如果要分析的维度很多，希望任意选择要分析的维度进行排序，使用排序功能就显然不能满足了，本文就来介绍如何利用Excel提供的Large和Small函数，建立高效实用的排序分析模板。

1、关键函数介绍

Large函数

语法：

Large(一组数字或单元格引用，k)

说明：获取第K大数，可用于降序排序

Small函数

语法：

Small(一组数字或单元格引用，k)

说明：获取第K小数，可用于升序排序

特别说明：

• 要排序的数据必须是数字，忽略单元格中的文本和逻辑值，不允许有错误值；

• 要排序的数字必须是一维数组，或一列或一行单元格区域；

• k值是自然数序列，1表示第一大或小，2表示第二大或小，以此类推；

• 在实际排序中，可使用Row或Column函数自动填充k值。

2、创建排名分析模板：

源数据如下，包括：学员及语数外三科考试成绩

分析模板功能如下：

• 可选择分析的科目

• 可选择排序方式：升序或降序

• 可输入参与排名的数据个数
  

以下介绍实现方法：

定义名称“科目”用于K2单元格设置数据验证选择科目

M2单元格设置数据验证，以选择排序方式

O2单元格设置数据验证，输入数据个数

增加F/G/H三列辅助列：

辅助列：F列

F列作用：

根据所选科目获取待排序分析的源数据

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)

根据所选科目决定哪列分数

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

根据所选科目列号获取该科分数

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

加上极小的随机数，以规避相同分数无法区分学员姓名问题

辅助列：H列

H列作用：

根据选择的排序方式，对数据进行排序

H2公式：

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

拖动拓展H2公式到H13

辅助列：G列

G列作用：

获取分数排序后对应的学员姓名

G2公式：

=INDEX($A$2:$A$13,MATCH(H2,$F$2:$F$13,0))

拖动拓展G2公式到G13

公式讲解：

MATCH(H2,$F$2:$F$13,0)

H2单元格分数排序前在F列的位置

=INDEX($A$2:$A$13,分数排序前在F列的位置)

根据位置获取对应的学员姓名

分析图表数据源名称定义：

排序分数：

根据O2输入的个数在H列提取参与排名分析的分数数据

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

排序姓名：

根据O2输入的个数在G列提取参与排名分析的姓名数据

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

插入柱形图并设置数据源：

插入空白柱形图，在图标上右击选择“选择数据”，为图表添加数据

点击“添加”

确定返回数据源设置画面

继续点击“编辑”

设置图表标题：学员成绩分析

根据个人风格对图表进行其他美化修饰，至此完成模板制作，可通过改变K2、M2、O2单元格值，看看对应的分析效果。