Excel数据排名不只有Rank函数，条件排名这三组函数公式更高效

有人的地方就有江湖，有数据的地方就有排名。相信大家对Excel数据排名不会陌生，里面使用最多的一个排名函数，它就是Rank函数。

因为场景的不同，数据排名也有不同的操作，Rank函数已经不能解决我们的操作。今天我们就来学习一下，Excel中如何根据不同的场景，利用函数公式进行排名操作。

案例说明：我们需要对全部的人员，按照成绩从大到小进行综合排名，在这种集中简单排名操作下，我们用Rank函数就可以进行解决。

函数公式：

=RANK(D3,$D$3:$D$12,0)

函数解析：

1、Rank函数总共有3个参数，第一参数为我们要排名的单个单元格数据，第二参数为需要排名的全部数据区域；第三参数有0和1两个值（0代表从到到小降序排序，1代表从小到大升序排序）

案例说明：我们需要按照综合成绩，单独的对每个部门人员进行成绩排名。这种条件排名的情况下Rank函数就解决不来。

函数公式：

=SUMPRODUCT(--(($C$3:$C$12=C3)*($D$3:$D$12)>D3))+1

函数解析：

1、使用多条件排名的时候，我们需要利用到Sumproduct函数，它可以将多个条件用*符号连接起来；

2、($C$3:$C$12=C3)代表统计对应的部门区域=C3单元格销售一部的值，会返回True或者False两个逻辑值；

3、($D$3:$D$12)>D3)代表对应的数据区域急速单元D3单元格的值，同样会以True或False逻辑结果来显示；

4、因为两个条件属于逻辑值，转化为数值的时候前面需要用--来转化。最后在当前值的基础上+1就可以计算出当前的排名。

案例说明：如上图数据所示，我们需要将有服务态度的人取消排名。这里可以搭配IF函数来进行快速操作。

函数公式：

=IF(E3>0,"取消排名",RANK(D3,$D$3:$D$12,0))

函数解析：

1、IF函数在这里进行判断，当对应的服务态度的数量大于0时会取消排名；反正会进行Rank函数来进行排名操作。

现在你学会如何根据不同的场景来进行数据综合排名了吗？

在各项比赛，或职场绩效管理中，都会对数据进行排名次，那就用到了RANK函数，当遇到多条件排名时，该如何处理？

如下所示，是一场比赛的得分情况，排名依据是：总体分高得名次高，总体分一致时，再看技术分，技术分高者高，举个实例：吕布的总得分是100分，程咬金是90分，那吕布的名次就在程咬金前面，马可波罗总得分也是100分，那再看技术分，吕布的高于马可波罗，吕布排在前。

仅以总得分排名

在D2中输入=RANK(B2,B:B)，得到了排名的结果

得分+技术双高排名

首先建立一个辅助列，D2=B2+C2/1000，然后在E2中输入=RANK(D2,D:D)即可

如果直接用B列+C列排名的话，技术分有的加起来就会立马变得很高，所以我们把总得分和技术分的权重为1000：1，甚至可以更大的比例，根据实际数据来进行排名。

得分高，时间少名次更好

如果现在要求总得分一致的情况下，时间越少，排名越好，那就是说吕布和马可波罗同样100分的情况下，马可波罗17比20少，可以排在前面，这个时候就需要用辅助列D2单元格输入公式=B2+0.01/C2，然后在E列使用公式=RANK(D2,D:D)即可，得到的结果如下所示：

其中0.01可以调的更小，根据实际数据来

本节完，欢迎留言讨论，期待您的转发分享

---------------

欢迎关注，更多精彩内容持续更新中....

工作和学习中经常会用涉及到成绩或者是销量排名，怎么样用函数快速解决排名问题呢，其实用 RANK函数+COUNTIFS函数就可以轻松搞定各类排名问题。

RANK函数有3个参数 RANK(number,ref,[order])

① number：要进行排序的数字。通常为单元格引用。

② ref：要在哪个区域进行排序。通常为单元格引用区域。

③ order：按升序还是降序排列。非0值升序排列，0或忽略降序排列。

RANK函数如果只是用来单纯的排名很是好用，但是如果我们需要按照部门或者班级进行内部排序，显然用RANK就有点麻烦，那怎么办呢？看看下面的案例吧。

① 在D2单元格录入公式：=RANK(C2,$C$2:$C$12)

② 下拉填充公式。

RANK函数非常好理解，C2的数值在$C$2:$C$12数据列中的名次，为了避免公式向下填充过程中C2:C12区域变化，需将数据列$进行绝对引用。但是RANK函数有个问题，同名次要占位，例并列第5之后就是第7名。

如果不分部门进行混排的话，无疑RANK是不二之选，但要分部门排序，就需要用到COUNTIFS函数。（如果您对COUNTIFS不太了解，可关注今日头条“EXCEL学习微课堂”，查看往期教程《Excel学会多条件计数函数COUNTIFS，再多条件的计数都不怕！》）

① 在C2单元格录入公式：=COUNTIFS(B:B,B2,C:C,">"&C2)+1

② 下拉填充公式。

公式解析：

COUNTIFS(B:B,B2,C:C,">"&C2)用来算出在C列比C2这个数字大的有几个。然后再加上1就是C2的排名了。之所以用COUNTIFS是因为如果还有别的更多的条件，我们也可以用这种方法。

我是EXCEL学习微课堂，头条教育视频原创作者，如果我的分享对您有帮助，欢迎点赞、收藏、评论、转发，更多的EXCEL技能，可以关注今日头条“EXCEL学习微课堂”。

前面已经介绍了最重要的求和sum,sumifs,sumproduct等函数，统计类函数还有很多，比如计数函数count,countb,countifs,求平均值函数average,avergeifs,排名函数rank.eq,rank.avg最大值max,maxifs，最小值min,minifs，百分位排名函数percentrank.inc,分位值函数percentile.inc,筛选条件下分类汇总函数subtotal，本文都会做出讲解并按实例进行演示：

问题1：提取成绩单表格里是数值的单元格个数，和非空的单元格总的个数

问题2：提取全部总成绩的最大值、最小值，以及华山派总成绩的最大值、最小值

问题3：按总成绩分数多少进行排名

问题4：分别求出各门派的人数，各门派性别为男的人数

问题5：求出所有成绩的平均值，以及求出按门派分类总成绩的平均值

问题6：求出总成绩在90%分位上的值，以及求出每个人成绩排名的分位值

Excel实现：问题1：提取成绩单表格里是数值的单元格个数，以及非空单元格总的个数

只统计数值单元格公式=COUNT(A1:I19)

统计包括文本单元格公式=COUNTA(A1:I19)

说明：

COUNT(value1, [value2], ...)，函如其名count英文本身就是计数的意思，所以这个函数计算包含数字的单元格个数以及参数列表中数字的个数

COUNTA(value1, [value2], ...) 这里面count后的a是英文all的意思，即COUNTA函数计算包含任何类型的信息（包括错误值和空文本 ("")）的单元格

类似的函数还有一个COUNTBLANK,即只统计指定区间内单元格为空值的个数

问题2：提取全部总成绩的最大值、最小值，以及华山派总成绩的最大值、最小值

最大值=MAX(I2:I19)

最小值=MIN(I1:I19)

MAX(number1, [number2], ...) 函如其名，返回一组值中的最大值，最多255个参数，和sum函数参数个数一样

参数可以是数字或者是包含数字的名称、数组或引用。

例如：把总成绩列命名为总成绩

然后MAX的参数直接引用总成绩=MAX(总成绩)

结果一样

逻辑值和直接键入到参数列表中代表数字的文本被计算在内。

如果参数是一个数组或引用，则只使用其中的数字,数组或引用中的空白单元格、逻辑值或文本将被忽略。

比如把岳灵珊的成绩改成文本“我想你”，则MAX计算过程自动忽略这个文本

如果参数不包含任何数字，则 MAX 返回 0，比如以姓名列作为MAX的参数

如果参数为错误值或为不能转换为数字的文本，将会导致错误

比如把岳灵珊成绩改为#N/A错误，则函数返回错误

MIN的函数用法同MAX一样，只是取最小而已

各门派总成绩的最大值=MAXIFS(I:I,C:C,K2)

各门派总成绩的最小值=MINIFS(I:I,C:C,K2)

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

这个函数用法同SUMIFS类似，MAXIFS返回一组给定条件或标准指定的单元格中的最大值,

MINIFS则返回一组给定条件或标准指定的单元格中的最小值

问题3：按总成绩分数多少进行排名

公式如下：

我们可以看到，一共有三个排名函数，rank,rank.eq,rank.avg

其中RANK函数最初的时候只有一个，后面两个是从第一个函数进行拆分而来的，RANK.EQ与RANK是全继承关系，也就是功能完全一样，所以我们这里只介绍后面两个就可以

RANK.EQ(number,ref,[order]) rank英文是排序的意思，EQ是单词equal相等的缩写，这个函数返回一列数字的数字排位，其大小与列表中其他值相关；如果多个值具有相同的排位，则返回该组值的最高排位，也就说比如两个人并列第一名，则两个人的排名都是第1，后面直接从第3名往下接，而RANK.AVG的排法在这里会把两个人都弄成1.5名，即（1+2）/2=1.5 名，如果是三个人并列第一，则是（1+2+3）/3=2名

第一个参数是要找到排位的数字，这里面即是每个人的总成绩单元格

第二个参数是第一个参数所在列表的区域，Ref是单词reference引用的缩写

第三个参数是排序类型，默认或者填0为倒序，其他数字都是升序排列

RANK.AVG函数参数与RANK.EQ相同

如果把第三参数改为1，则变升序排列

问题4：分别求出各门派的人数，各门派性别为男的人数

各门派的人数=COUNTIFS(C:C,K2)

各门派性别为男的人数=COUNTIFS(C:C,K2,B:B,"男")

其实还是有COUNTIF这个条件函数的，个人建议直接PASS掉这个COUNTIF

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

用法和SUMIFS，MAXIFS函数都类似，这个函数返回统计满足所有条件的次数

最少两个条件，第一个是条件区域，第二个是条件，可以使用通配符进行匹配

问题5：求出所有成绩的平均值，以及求出按门派分类总成绩的平均值

=AVERAGE(I1:I19) 或者=AVERAGE(总成绩)（前面已经定义列区域名称）

AVERAGE(number1, [number2], ...) 单词本身就是平均值的意思，函如其名，返回参数平均值

这个函数只计算里面是数值的平均值，比如在最下面加一个文本我想你，则不计算

这个函数也不计算布尔值,如果要计算文本单元格在内的所有单元格平均值则要用=AVERAGEA(I1:I20)

AVERAGEA 比AVERAGE函数多了一个字母，A代表ALL的意思

求出按门派分类总成绩的平均值=AVERAGEIFS(I:I,C:C,K2)

问题6：求出总成绩在90%分位上的值，以及求出每个人成绩排名的分位值

求出总成绩在90%分位上的值:=PERCENTILE(I2:I19,0.9)

这个实际意义就是说只要成绩超过614.9分，就可以超过成绩单里面90%的人了

PERCENTILE(array,k)，percentile单词的意思是百分位数，也就是指排在某个百分比位数的值是多少

这个函数可以决定检查得分高于第某个百分点的候选人。

第一个参数是定义相对位置的数组或数据区域。

第二个参数0到1之间的百分点值，包含 0 和 1。

后面又衍生分裂为两个新函数，percentile.inc 和 percentile.exc, inc是include的缩写，exc是exclude缩写

percentile.inc完整地继承了percentile函数功能，percentile.exc,函数的第二个参数不能为0或者100%

例：考多少分可以超过100%(也就是最高分）的人：=PERCENTILE(I2:I19,1)

但是如果用：=PERCENTILE.EXC(I2:I19,1)就会报错

求出每个人成绩排名的分位值：=PERCENTRANK.INC($I$2:$I$19,I2)

这个函数也有.exc的变体：=PERCENTRANK.EXC($I$2:$I$19,I2)这里排名百位分没有了1和0的值

我们以前者为准，这个百分位排名函数也可以用rank间接计算出来

=(K2-1)/(COUNT($K$2:$K$19)-1)

略有一点差异，影响不大

PERCENTRANK.INC(array,x,[significance])，percent是百分比，rank是排名，即百分比排名函数，将某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为0 到1（含 0 和 1）。

第一个参数是定义相对位置的数值数组或数值数据区域

第二个参数是需要得到其排位的值

第三个参数可选。 用于标识返回的百分比值的有效位数的值，如果省略，则PERCENTRANK.INC使用 3 位小数

附加：Excel筛选条件下进行求和，计数，最大值，最小值

先筛选部门华山派，然后在最下方单元格输入公式=SUBTOTAL(9,I2:I19)即可求出筛选状态下的和

如果用SUM公式求和，则会返回所有单元格包括没筛选部分的成绩之和

SUBTOTAL(function_num,ref1,[ref2],...)

第一个参数是函数类型，输入数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。 如果使用 1-11，将包括手动隐藏的行，如果使用 101-111，则排除手动隐藏的行；始终排除已筛选掉的单元格。

第二个参数是引用的数据区域

例：我们筛选华山派求和人数=SUBTOTAL(2,I2:I19)

此时我们将令狐冲和岳灵珊两个人手动隐藏（非筛选隐藏）

此时我们得到的值里不包括隐藏项，所以这个第一参数官方文档说明是不是有些问题？

其他的一些功能：求最大值

求所有值相乘

Python实现：

问题1：提取成绩单表格里是数值的单元格个数，和非空的单元格总的个数

统计非空的单元格总的个数：

import pandas as pd

df=pd.read_excel("c:/study_note/xiao_subtotal.xlsx",sheet_name="成绩单")

df.count().sum()+df.shape[1]

因为pandas通过count函数并不统计第一行，列首是作为列索引字段的

统计表格里是数值的单元格个数：

df.iloc[:,[4,5,6,7,8]].count().sum()

或者

df.loc[:,"语文":"总成绩"].count().sum()

问题2：提取全部总成绩的最大值、最小值，以及华山派总成绩的最大值、最小值

df["总成绩"].max()

df["总成绩"].min()

df.groupby("部门")["总成绩"].max()["华山派"]

df.groupby("部门")["总成绩"].min()["华山派"]

问题3：按总成绩分数多少进行排名

倒序排名：

df["总成绩提名"]=df["总成绩"].rank(ascending=False,method="min")

相同名次下平均取值：

df["总成绩提名"]=df["总成绩"].rank(ascending=False,method="average")

升序排列

df["总成绩提名"]=df["总成绩"].rank(ascending=True,method="min")

问题4：分别求出各门派的人数，各门派性别为男的人数

df.groupby("部门")["部门"].count()

df[df["性别"]=="男"].groupby("部门")["部门"].count()

或者用个麻烦点的方法：

a=df.groupby(["部门","性别"]).count()

a=a.reset_index().set_index(["性别","部门"])

a["姓名"].loc["男",:]

问题5：求出所有成绩的平均值，以及求出按门派分类总成绩的平均值

df["总成绩"].mean()

df.groupby("部门")["总成绩"].mean()

问题6：求出总成绩在90%分位上的值，以及求出每个人成绩排名的分位值

df["总成绩"].quantile(0.9)

quantile的单词是分位点的意思

求出每个人成绩排名的分位值：

df["成绩排名"]=df["总成绩"].rank(ascending=True, method="min")

df["百分位排名"]=(df["成绩排名"]-1)/(df["总成绩"].count()-1)

SQL实现：

1提取成绩单表格里是数值的单元格个数，和非空的单元格总的个数

SELECT count(姓名) +count(性别)+ count(部门)+count(籍贯)+count(语文)+count(数学)+count(综合)+count(英语)

+count(总成绩) AS "单元格数" FROM score ;

这个是不包括列首的单元格数，只要再此基础上加字段数即可

提取成绩单表格里是数值的单元格个数

SELECT count(语文)+count(数学)+count(综合)+count(英语)+count(总成绩) AS "单元格数" FROM score ;

问题2：提取全部总成绩的最大值、最小值，以及华山派总成绩的最大值、最小值

SELECT max(总成绩) AS "最大值" FROM score ;

SELECT min(总成绩) AS "最大值" FROM score ;

SELECT max(总成绩) AS "最大值" FROM score WHERE 部门='华山派' ;

SELECT min(总成绩) AS "最大值" FROM score WHERE 部门='华山派';

问题3：按总成绩分数多少进行排名

降序排名：

SELECT *,rank() OVER (ORDER BY 总成绩 DESC) AS "分数排名" FROM score;

升序排名：

SELECT *,rank() OVER (ORDER BY 总成绩) AS "分数排名" FROM score;

其中SQL排名还有两种：

SELECT *,ROW_NUMBER() OVER (ORDER BY 总成绩 DESC) AS "分数排名" FROM score;

这种排名两个值相同时，并不会显示两个相同的并列名次，而是依然按顺序号进行往下排

比如把平一指的成绩也改为608：

UPDATE score SET 总成绩='608' WHERE 姓名='平一指';

可以看到第4名和第5名的成绩是一样的，但是还是按顺序进行排序

SELECT *,DENSE_RANK() OVER (ORDER BY 总成绩 DESC) AS "分数排名" FROM score;

这种形式出现两个并列排名后面依然会按顺序进行往下排，比如两个并列第4名，后面依旧接第5名

问题4：分别求出各门派的人数，各门派性别为男的人数

SELECT 部门,count(姓名) AS 部门人数 FROM score GROUP BY 部门;

SELECT 部门,count(姓名) AS 部门人数 FROM score WHERE 性别='男' GROUP BY 部门 ;

问题5：求出所有成绩的平均值，以及求出按门派分类总成绩的平均值

SELECT avg(总成绩) AS 平均值 FROM score ;

SELECT 部门,avg(总成绩) AS 平均值 FROM score GROUP BY 部门;

问题6：求出总成绩在90%分位上的值，以及求出每个人成绩排名的分位值

第一个问题没找到解决办法，后面找到再补

求出每个人成绩排名的分位值：

CREATE TABLE temp4(

姓名 VARCHAR(10),

排名 INT

);

INSERT INTO temp4 SELECT 姓名,rank() OVER (ORDER BY 总成绩 ) AS 排名 FROM score;

SELECT 姓名,(排名-1)/17 AS 百分位排名 FROM temp4 ORDER BY 百分位排名 DESC;

Tableau实现:

问题1：提取成绩单表格里是数值的单元格个数，和非空的单元格总的个数

可以像SQL一样用COUNT函数求出，这里没必要用Tableau来实现，焉用牛刀？

问题2：提取全部总成绩的最大值、最小值，以及华山派总成绩的最大值、最小值

把总成绩字段移到文本上面，然后点小三角下拉列表，找到最大值和最小值

把总成绩字段再复制一个，然后筛选为显示最小值，把这两个字段同时拖到左下角度量值里面，如下：

问题3：按总成绩分数多少进行排名

创建成绩排名字段，输入公式RANK(SUM([总成绩]),'desc')

然后将成绩排名字段拖到左下角度量值里面

问题4：分别求出各门派的人数，各门派性别为男的人数

一个取巧的办法，把部门拖到行空格里，然后把姓名拖到标记里面的详细信息，可以看到一个小方块代表一个人

或者把姓名拖到文本里，显示每个门派包括哪些人，

以上两个方法都不能直接计数，正确方法如下:

创建计算字段，命名为部门人数，输入公式COUNT([部门])

把部门人数字段拖到文本上面即可求出各门派的人数

把性别字段拖到筛选器下面，然后筛选性别为男，即可求出各门派性别为男的人数

问题5：求出所有成绩的平均值，以及求出按门派分类总成绩的平均值

直接在字段总成绩上面筛选度量-平均值就可以，拖到文本上面

把部门字段拖到行空格处，即可显示各部门平均值

问题6：求出总成绩在90%分位上的值，以及求出每个人成绩排名的分位值

直接筛选总成绩字段-度量-百分位-90即可得到在90%分位上的值

创建计算字段百分位数，输入公式：RANK_PERCENTILE(SUM([总成绩]),'asc')

然后将字段拖到左下角度量那里即可

小伙伴们好啊，今天咱们一起聊聊Excel中的数据查询那些事儿。

1、VLOOKUP

这个函数能够实现从左到右的数据查询，从查询区域最左侧列中找到查询值，然后返回同一行中对应的其他列的内容。

常用写法是：

VLOOKUP(查找内容,查找区域,返回第几列,匹配方式)

如下图中，要根据E3单元格中的领导，在B~C列的对照表中查找与之对应的秘书姓名。

F3单元格公式为：

=VLOOKUP(E3,B2:C8,2,0)

公式中，“E3”是要查找的内容。

“B2:C8”是查找的区域，在这个区域中，最左侧列要包含待查询的内容。

“2”是要返回查找区域中第2列的内容，注意这里不是指工作表中的第2列。

“0”是使用精确匹配的方式来查找。

2、HLOOKUP

下图中，要根据A7单元格中的领导，在2~3行的对照表中查找与之对应的秘书姓名。

B7单元格公式为：

=HLOOKUP(A7,2:3,2,0)

HLOOKUP函数与VLOOKUP的作用类似，能够实现从上到下的数据查询。先从查询区域第一行中找到查询值，然后返回同一列中对应的其他行的内容。

常用写法是：

HLOOKUP(查找值,查找区域,返回第几行,匹配方式)

公式中，“A7”是要查找的内容。

“2:3”是查找的区域，不要被数字迷惑了，这种写法就是第二到第三行的整行引用。

在这个区域中，第一行要包含待查询的内容。

第三参数“2”是要返回查找区域中第2行的内容，注意这里不是指工作表中的第2行。

“0”是使用精确匹配的方式来查找。

3、LOOKUP

下图中，要根据E3单元格中的秘书，在B~C列的对照表中查找与之对应的领导姓名。

F3单元格公式为：

=LOOKUP(1,0/(C3:C8=E3),B3:B8)

LOOKUP函数能够在指定的行或列中查询指定的内容，并返回另一个范围中对应位置的值。

常用写法是：

1、LOOKUP(查找值,单行或单列的查找区域,要返回结果的行或列）

提示：使用该写法时，查询区域要求升序排序

2、LOOKUP(1,0/(条件区域=指定条件),要返回结果的行或列）

公式中，“1”是要查找的内容。

“0/(C3:C8=E3)”是查找的区域，不要被这段公式迷惑了，这种写法是模式化的，就是0/(条件区域=查找值)。

先使用等号，将条件区域的内容与查找值进行逐一对比，返回逻辑值TRUE或是FALSE。再使用0除以逻辑值，在四则运算中，逻辑值TRUE相当于1，FALSE相当于0。相除之后变成了一组错误值和0：

{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

也就是条件区域中的某个单元格如果等于查找值，对应的计算结果就是0，其他都是错误值。

LOOKUP在这组内容中查找1的位置，这个函数有一个特点，当找不到查找值时，会以小于查找值的最接近值进行匹配，本例中0的位置是2，所以最终返回第三参数B3:B8中第2个单元格的内容了。

LOOKUP函数的查找区域和返回结果区域，都是一行或一列的写法，可以实现任意方向的查询。

4、INDEX和MATCH

以下图为例，要根据E3单元格中的秘书，在B~C列的对照表中查找与之对应的领导姓名。

F3单元格公式为：

=INDEX(B2:B8,MATCH(E3,C2:C8,0))

MATCH函数的作用是查找数据在一行或一列中所处的位置。

常用写法是：

MATCH(查找值,查找的行或列,匹配方式)

公式中的MATCH(E3,C2:C8,0)部分，就是精确查找E3单元格中的小袁秘书在C2:C8中所处的位置，结果是3。

INDEX函数的作用是根据指定的位置信息，返回数据区域中对应位置的内容。

本例中，先用MATCH函数计算出小袁秘书的位置3，再用INDEX函数返回B2:B8区域中第3个单元格的内容。

INDEX+MATCH函数二者组合，也能实现任意方向的数据查询。

5、XLOOKUP

如果你使用的是 Microsoft 365，Office 2021或者WPS 2021，还可以使用XLOOKUP函数。

XLOOKUP的作用是在一列（也可以是一行）中查找搜索项，并在同一行的另一列中返回结果。

常用写法是：

=XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式])

第一参数是要查找的内容。

第二参数是要搜索的单行或单列的区域。

第三参数指定要返回结果的区域。

第四参数指定在找不到匹配项目时返回的值。

第五参数指定匹配方式，默认使用0，表示精确匹配。

第六参数指定查询模式，默认使用1，表示从第一项开始执行搜索。

除了前面三个参数必须有，后面的参数是可选的。

如下图所示，F3单元格使用以下公式根据秘书查找对应的领导。

=XLOOKUP(E3,C$3:C$8,B$3:B$8,"查无此人")

工作过程中我们经常需要对各项数据进行排名，但是在排名的过程中可能会发现一个问题？

那就是，当要排名的数据中存在相同的数值时，就会出现多个相同的名次。

那么，相同名次的下一个名次应该如何取值呢？

这时候如果使用不同的排名方式，同一组数据最终的排名结果有可能是不同的。

EXCEL常规的排名函数用RANK，它可以称为“美式排名”法，与之相对的是“中国式排名”。除此之外，还有限定条件的排名，有单个条件也有多个条件。

简单介绍一下什么是美式排名、什么是中式排名？

美式排名在实际运用中应用得比较多。

例如：在一组10个人的成绩中当出现两个并列第1名时，下一个名次跳过2直接为3，排名的最后一名为10。

中式排名，在出现相同分数时，不会跳过下一个名次。

例如：当出现并列第1的时候，下一个排名仍然为2，虽然总人数为10，但它的最后一个排名为8。

美式排名最常用的函数RANK ,函数排名解析。

RANK函数：表示返回一列数字的数字排位。

语法：=RANK(排名的数字，引用区域，排序方式）

排序方式有两种：若是忽略或者为0，则为降序；若是非零值则是升序。

按升序排序公式：=RANK(B2,$B$2:$B$11)

按降序排序公式：=RANK(B2,$B$2:$B$11,1)

中式排名不能再使用RANK函数，需要变换公式。

中式排名公式：=SUMPRODUCT(($B$2:$B$11>=B2)*(MATCH($B$2:$B$11,$B$2:$B$11,0)=ROW($1:$10)))

Excel绩效排名公式

这是一个做什么都要排名的时代，其实呢，这种激励方式很早就开始使用了。

我们今天要讨论的是这样一个问题：对与业绩进行排名，按照数量排名，如果数量相同则参考金额数据，金额大的排名在前。

到了Excel2016排名函数有三个，RANK是最早的排名函数，RANK.AVG和RANK.EQ是新增的函数，其中RANK.EQ功能与兼容函数RANK基本一致，我们看看RANK.AVG和他们之间的区别：

可以看出RANK/AVG取得是排名的平均值，例如：两个19的排名应该是2，3取平均值2.5；3个18的排名应该是4，5，6取平均值5。

回到我们的问题，不能出现相同排名的情况，相同排名就要参考金额数据，应该如何参考金额数据呢？

其实也简单，就是在数量相同的情况下，比较金额的大小：

公式：=RANK.EQ([@数量],[数量],0)+SUMPRODUCT(([数量]=[@数量])*([金额]>[@金额]))

我们来看看这个公式的计算过程：

RANK部分就不用看了，结果是4，看后面的计算：

先计算等于8的条件，然后计算金额条件：

同时满足这两个条件得到的结果是：

数值等于18，同时金额大于31的结果是1

那么最终的排名是5。

这个公式很有用，在你做激励奖罚方案时会经常用到。

常见排名方式：举例以C列订单量做排名

1.灵活运用coutif排名，函数：=COUNTIF($C$2:$C$7,">"&C2)+1，问题：该排名的延伸性导致了只能降序；

2.正常的rank排名，函数：=RANK(C2,$C$2:$C$7,0)，0代表降序，1代表升序，问题：中国式排名，成绩相同则排名一致；

3.多条件排名：sumproduct+rank函数排名，函数：=E2+sumproduct(($C$2:$C$7=C2)*($B$2:$B$7>$B2))，如果C列有相同值，则再会判断一次B列销售额哪个大，再返回1，相加rank排名得出最终排名,想加更多条件的就都套在sumproduct函数里边，如：=E2+sumproduct(($C$2:$C$7=C2)*($B$2:$B$7>$B2)*(你要多加的条件))；如果实在不懂，直接私聊我要EXCEL文件练习下即可。

4.再解释sumproduct函数，延伸了sum和sumifs的条件乘积求和；

下图为举例的多条件求和，找A列是蔬菜的，B列是1月的，在B:F数据区域求和；

如果没有求和区域，只找蔬菜B列1月的蔬菜区域，但是没有带数据区域如果是真就返回1，如果是假则返回0，就会出现第一个蔬菜为0，第二个蔬菜为1；

这是【中学研究与管理】的第64篇原创文章

大数据时代，工作要求快捷、高效、精细，所以选择合适的工作工具，形成正确的工作方式，掌握一定的工作技巧是必需的。在教学中，一样需要大量、精准的数据分析。

掌握Excel这个办公利器，能大大提高我们的办公效率。比如在教学上，成绩分析和评价是教学过程中的一项重要内容，能准确、直观地分析和呈现出教学管理过程中的各项数据，是数字校园时代的教育工作者的一项必备技能。

EXCEL的应用涉及到如何采集数据？如何分析数据？如何使用数据？经调查大部分教师对常用数据办公软件EXCEL的熟悉程度仅仅停留在仅会浏览和在单元格填写内容的阶段。他们对以下能极大提高工作效率和工作质量的技能知之甚少：

1、如何快速统计每名学生的总分？2、如何统计每名学生的班名次？3、如何统计统计班级学生各科的平均分、及格分人数、及格率、优秀人数、优秀率、学困人数、学困率等？4、如何统计学生的学校名次？5、如何将学生成绩信息打印在一张纸上？6、如何运用数据透视表？7、如何用图表直观展示学情？

针对这七个问题，【中学研究与管理】以分期的形式逐步讲述如何实现。

本期文章主要讲述常用公式VLOOKUP的应用。

用公式VLOOKUP求校排名

一个学校同一个年级往往有多个班级，考试成绩出来以后，除了需要班级排名外，还需要一个重要数据，就是学生在整个年级的校排名。

校排名需要调用其它班级的成绩，由于数据不在同一个工作表或文件里，这里就需要用到公式VLOOKUP在跨表格调用数据。

VLOOKUP函数的语法规则如下：

我们通过实例来演示：求九六班校排名：

1、如果在不同的sheet表或excel表中想要提取九六班学生成绩的校排名，可利用Vlookup公式在数据列表中查找数值。如图二。

2、取值条件：如图三如果需要在“九年级段考拉练总成绩分析”中查找到九六班的校名次，查找值栏需为某一学生姓名例选中D2，数据表需引用“九年级段考拉练总成绩分析”sheet中的数据。

3、引用范围：如图四选中需引用的数据库，从姓名E1列往右拉到N1列，列序数为要提取引用的数据库表中第几列数据，校名次为姓名列往后数的第10列，故列序数可填10。

4、匹配条件：匹配条件，0代表精确匹配，1代表模糊匹配

5、填充复制：提取到D2的校名次后，向下填充即可提取出全部学生的校名次。