Excel排名函数公式大全:

2024年1月24日发(作者：)

Excel排名函数公式大全：

1. 首先，用RANK函数两参数用法做基础排名（默认降序排列）。

特点：如果有两个数字排名相同，比如说数据序列：4,2,2,1。这时排名次序是：1,2,2,4，也就是最大的次序和总数据量一致，其中的第2个2占据了“第3”这个名次。

2. 用COUNTIF函数也可以实现上述RANK函数的两参数用法结果。

3. 用公式：=SUMPRODUCT((B$2:B$9>B2)*(1/(COUNTIF(B$2:B$9,B$2:B$9))))+1，获得中国式排名结果。

中国式排名特点：会出现并列名次。比如说：4,2,2,1。中国式排名次序是：1,2,2,3。也就是并列第二名后是第三名，不会漏掉名次的数字。

4. 用RANK和COUNTIF函数组合，获得不重复排名。

C2=RANK(B2,$B$2:$B$9)+COUNTIF(B$2:B2,B2)-1

特点：用这个公式，根据Excel行的特点，相同分数时，行数越大排名越大。如下图C6和C8单元格。

5. 只用COUNTIF函数也可以实现上一步的不重复排名：

C2=COUNTIF(B$2:B2,B2)-1+COUNTIF($B$2:$B$9,">"&B2)+1

6. 在C2输入RANK函数的三参数模式：=RANK(B2,$B$2:$B$9,1)，即可获得倒序排名。分数最小的排名倒数第1。

7. 和正序排名类似，用COUNTIF函数也可以替代RANK函数实现倒序排名，公式如下：

C2=COUNTIF($B$2:$B$9,"<"&B2)+1

首先，用RANK函数两参数用法做基础排名（默认降序排列）。特点：如果有两个数字排名相同，比如说数据序列：4,2,2,1。这时排名次序是：1,2,2,4，也就是最大的次序和总数据量一致，其中的第2个2占据了“第3”这个名次。

用COUNTIF函数也可以实现上述RANK函数的两参数用法结果。

用公式：=SUMPRODUCT((B$2:B$9>B2)*(1/(COUNT诔罨租磊IF(B$2:B$9,B$2:B$9))))+1，获得中国式排名结磨营稼刻果。中国式排名特点：会出现并列名次。比如说：4,2,2,1。中国式排名次序是：1,2,2,3。也就是并列第二名后是第三名，不会漏掉名次的数字。

用RANK和COUNTIF函数组合，获得不重复排名。C2=RANK(B2,$B$2砜实蛰斓:$B$9)+COUNTIF(B$2多唉捋胝:B2,B2)-1特点：用这个公式，根据Excel行的特点，相同分数时，行数越大排名越大。如下图C6和C8单元格。

只用COUNTIF函数也可以实现上一步的不重复排名：C2=COUNTIF(B$2:B2,B2)-1+COUNTIF($B$2:$B$9,">"&B2)+1

在C2输入RANK函数的三参数模式：=RANK(B2,$B$2:$B$9,1)，即可获得倒序排名。分数最小的排名倒数第1。

和正序排名类似，用COUNTIF函数也可以替代RANK函数实现倒序排名，公式如下：C2=COUNTIF($B$2:$B$9,"<"&B2)+1

简介

在Excel中，offset函数是一个引用函数，表示引用某一个单元格或者区域。其也是我们日常做一些组合函数中的一种，需要熟练掌握。

工具/原料

Office Excel

以单元格做参考系

offset函数是以指定的应用为参考系，通过上下左右偏移得到新的区域的引用。返回的引用可以是一个单元格也可以是一个区域。并且可以引用指定行列数的区域。

它的语法嚼嘛叽堑形式是OFFSET(reference,rows,cols,height,width)，下面就以一个实例应用做解释。以A1单元格作为参考系，向下移动2行，向右蛔歹朽耍移动2列，我们可以直接找到引用的单元格是 C3，结果是6。

在D3单元格输入公式=OFFSET(A1,2,2,1,1)，其中A1是参考系，接着擢爻充种的2,2分别表示下，右移穰惩较瘁动的行数和列数，同样向上，左则是负数。最后面的1,1表示引用的区域大小是一行一列，也就是一个单元格。

结果和我们肉眼看到的一样，函数公式中最后面的1,1可以省略，如下图，其就表示直接引用一个单元格。

以区域做参考系

还是这个例子，我们以A1到B2的区域作为参考系，来引用下移2行，右移动1列的2行2列的区域。

在D3输入公式=OFFSET(B1:C2,2,1,2,2)，其表示的意思与上文中以撮劝丛食单元格为参考系的基本一致，然后同时按下CTRL、SHIFT和ENTER三个键（因引用的是数组区域）。

D3单元格中显示的是引用到区域中第一行第一列的单元格，此时将鼠标放在该单元格右下角出现十字的时候点住，右拉到2列，再接着点住下拉到2行，那么此时所有引用的区域就出现了。如下图所示。

注意事项

offset函数中单元格引用和区域引用其实是一样的，只不过单元格是区域的单一格，一行一列罢了

引用的数据不要超出范围，否则会报错。

Excel函数OFFSET偏移引用教程

时间：2013-10-12发布者：marswawa本站编辑：爱说篇 申请删除本条内容

今天继续我们的函墙绅褡孛数系列教程，本次课程要来介绍的是OFFSET偏移引用函数，作用与Indi鸡堕樱陨rect类似，区别在于Offset函数是基于目标基准区域的偏移引用。下面就详细的来介绍一下该函数的使用方法。函数语

法OFFSET(reference,rows,cols,height,width)Reference：偏移量的引用区域。必须为对单元格或相连单元格区域的引用。Rows：上下偏移的行数。Cols：左右偏移的行数。Height：高度，所要返回的引用区域的行数。Width：宽度，所要返回的引用区域的列数。注：表示引用区域的高度和宽度，两个参数均可省略，在省略的情况下默认与基准区域的维度尺寸相一致。实例剖析①下图，OFFSET（A1,2,3）省略了后面2个参数，

表示与选定的区域尺寸一致，向下偏移2行，向右偏移3列，所以得到的结果就是杭州。②再来看看参数为负的意义，OFFSET(A4:A5,-2,5)，又是省略了后面2个参数，类似上面的例子，可想而知，返回的也应该是类似的，是一个两排的数据。-2,5，向反方向偏移，也就是向上偏移2个单位，向右偏移5个，结果那就是庐山西湖。③内嵌一个match匹配函数，OFFSET(A1,MATCH("张家界",F2:F7,0),0)，F2：F7中匹

配出张家界所在的位置，结果是4，所以公式可以简化为OFFSET(A1,4,0)，跟第一个例子一样了，结果为湖南。

match函数的使用方法

简介

match函数是excel中众多函数之一，平时我们可能并不常用到，但是不常用不代表没有用，或许你下次使用excel的时候就会用到它，为了以后用到它的时候不至于手忙脚乱，我们可以先简单的来了解一下这个函数。

方法/步骤

我们龅歧仲半打开excel，点击“插入”菜单，找到函数选项，然后在类别中选择全部，一直拉到M开头的函数列表，找到match函数。有的朋友可能会说淡馋

衔锂何必怎么麻烦呢，直接在搜索函数中输入match不就可以了，小编起初也是这么做的，但是好像达不到预期的效果，所以还是老老实实的使用这种笨方法了。

首先我们来看看match函数的定义，“返回符合特定值特定顺序的项在数组中的相对位置”，这是excel给出的关于match函数的定义，如果只看这些文字的话恐怕很难理解，我们可以暂时不用深究它，知道即可，运用过这个函数之后再来细细品味它。

知道了它的定义之后我们来看一下它的参数，ma隋茚粟胫tch函数一共有三个参数，首先我们来看第一个参数lookup_value，含义：需要在数据表（造婷用痃lookup_array）中查找的值。接着我们来看第二个参数lookup_array，含义：可能包含有所要查找数值的连续的单元格区域。第三个参数match_type，含义：排列顺序，一共有三个值，1代表升序排列，0代表任意顺序排列，-1代表降序排列。其中第一个和第二个必选参数，第三个为可选参数，默认为1。

光说不练假把式，下面小编就通过一个实例来应用一下这个函数。首先新建一个工作表，输入测试的数据（新的数据表和数据比较方便大家的理解），小编建立的是一个六行五列的数据，而且每行或者每列数据之间都存在递增的关系。

然后我们找一个空白的单元格，用来输入我们今天的主角函数match函数。首先自然鋈守踬痊是等号了，然后就是match(),接着我们填写参数，首先是第一个参数，也就是需要查找的值，选择45，然后填写第二个焊剑杂锭参数范围，也就是连续的单元格（只能是一行或者一列），我们填写A1:E1,这样数据是递增的，所以我们第三个参数填写1，默认的就是1，不填也可以.

然后我们点击回车，我们惊奇的发现，经过match函数运算过后这个单元格的数值变为了3，按照整个数据范围递增的顺序，45的确是在第三位，可见函数计算的并没有错。那么我们把它第三个参数改为-1会发生什么状况呢。

当我们改为-1再次点击回车的时候居然出错了，为什么呢？其实我们读读三个数值的含义并不难理解，其中-1代表的是降序排列，而这个数据范围之内并不存在递减的关系，自然会出错了，如果你无法确认是升序或者降序，我们可以选择填写0，这样升序降序都不会出错了。

关于match函数的用法小编就介绍到这里，讲的比较简单，并没有深入的去探究match函数，想要深入的了解这个函数的用法，还需要朋友们亲自动手试试，有一点要提示的是，当其中的数据是无规律的数据的时候，得到的结果或许不是你想要的，至于各种缘由有待自行发掘。

注意事项

数据选择上只能是一行或者一列的数据，否则会出错。

excel函数实例教程：[10]OFFSET函数怎么用

简介

OFFSET函数的重要使用是根据以指定的引用为参照，通过偏移量来得到新的引用。其返回值为对某一单元格或单元格区域的引用。下面就一起来具体了解一下OFFSET函数的具体使用方法。

方法/步骤

首先来看一下OFFSET函数的基本语法：OFFSET(reference,rows,cols,height,width)

Reference：作为偏移量参照系的引用区域。Rows：相对于参照系的左上角单元格，上（下）偏移的行数。其行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。 Cols：相对于参照系的左上角单元格，左（右）偏移的列数。其列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。 Height：高度，即所要返回的引用区域的行数。Width：宽度，即所要返回的引用区域的列数。

在此为了讲解的需要，特制作如图所示的数据表格：如果想以C3为参照，引用B2单元格中的内容，则使用公式“=OFFSET(C3,-1,-1)”即可。

当然，我们可以返回一个单元格区域：如图，如果想以C3为参照，返回A1：B2单元格区域，则使用公式“=OFFSET(C3,-1,-1,2,2)”实现。具体生成方法，选择由Height和Width指定的单元格区域，然后输入公式“=OFFSET(C3,-1,-1,2,2)”。

接下来同时按住键盘上的“Shift”+“Ctrl”，并按Enter键以生成数组公式，从而得结果数据。

另外，我们也可以指定正的“Row”和“Col”参数，以便获取相对于参照实现对参照右侧和下面单元格的引用。例如：以A1为参照，实现对C3单元格引用，使用公式“=OFFSET(A1,2,2)”即可实现。

Excel中如何活用INDEX和MATCH函数

简介

在Excel中MATCH函数可以返回指定内容所在的位置，而INDEX又可以根据指定位置查询到位置所对应的数据，各取其优点，我们可以返回指定位置相关联的数据。

工具/原料

Excel软件

电脑

函数介绍：

函数(返回指定内容所在的位置）

MATCH(lookup-value,lookup-array,match-type) lookup-value:表示要在区域或数组中查找的值，可以是直接输入的数组或单元格引用。 lookup-array:表示可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。 match-type:表示查找方式，用于指定精确查找（查找区域无序排列）或模糊查找（查找区域升序排列）。取值为-1、1、0 。其中0为精确查找。

函数（返回制定位置中的内容） INDEX(array,row-num,column-num)

array:要返回值的单元格区域或数组。 row-num:返回值所在的行号。 column-num:返回值所在的列号。

MATCH函数应用：

建立如图所示的Excel表格，其中坐标为数据区域，右边为查询方式。

选择F3单元格，在单元格中输入：=MATCH("二月",A2:A13,0)，回车可以看到二月所对应的行数为2.

INDEX函数应用：

选择G5单元格，在单元格中输入：=INDEX(A2:B13,2,2)，回车可以看到A2:B13区域中2行2列交叉对应的值.

MATCH和INDEX联合查询：

选择G7单元格，在单元格中输入：=INDEX(B2:B13,MATCH(F7,A2:A13,0))，用MATCH函数返回F7单元格中的之所对应的行，再利用ATCH函数返回的行号用INDEX函数查询出月份对应的金额。

用同样的方法再H7单元格中输入：=INDEX(C2:C13,MATCH(F7,A2:A13,0))，即可查询出月份对应的年龄。

为了方便起见，我们可以在F7单元格中制作下拉列表，这样可以方便查找月份。

简介

实例比较lookup与index函数套用match函数的差异

工具/原料

excel 电脑

方法/步骤

要通过在b列中找到最小值返回与其同行的a列日期

min函数的作用是返回b列最小值

选中min函数然后按f9，查看返回值。

返回值为0.5

lookup函数第二个参数不符合要求

因为第二个参数没有排序，所以返回了错误值。

解读index函数嵌套match函数

Excel中index函数

简介

你还在为Excel中index函数擢爻充种的使用方法而苦恼吗，今天小编教你Excel中index函数的使用方法，让拉慝盐後你告别Excel中index函数的使用方法的烦恼。经验主要从四方面对Excel函数进行讲解，1.函数的含义，2.函数的语法格式，3.函数在日常办公中运用的实例介绍，4.函数使用的注意点。更多Excel经验请关注Excel小新，如果帮到了你，请在上方给个投票谢谢支持。好人一生平安。先顶后看年薪百万，如果帮到了你，帮忙给个好评，这对小编很重要，万分感谢。

工具/原料

Excel

index函数

函数的含义

函数的含义返回数据表区域的值或对值的引用。Index函数的两种形式：数组和引用。数组形式——返回数组中指定单元格或单元格数组的数值。引用形式——返回引用中指定单元格或单元格区域的引用。

函数的语法格式

2.i艘绒庳焰ndex函数的语法格式数组形式=index(array,row_num,column_num)=index（数据表区域，若益肽屙行数，列数）引用形式=index(reference,row_num,column_num,area_num)=index（一个或多个单元格区域的引用，行数，列数，从第几个选择区域内引用）

函数数组形式案列

3.如图所示，输入公式=INDEX(B3:D6,4,3)数据表区域（B3:D6)行数(4)列数(3)返回数据表区域（B3:D6)第四行第三列的值120。

4.案列：坡纠课柩index函数和match函数实现单条件匹配查找。如图所示，利用index进行匹配查找，当数据很多时，我们紧鋈笆珀不可能通过点数来确定index函数中的行数和列数。而是要通过match函数来确定行数和列数。输入公

式=INDEX($F$2:$I$6,MATCH(A9,$F$2:$F$6,0),MATCH($B$8,$F$2:$I$2,0))这里使用绝对引用要注意。B8代表6月份不变要使用绝对引用。有关match函数，可以观看小编的经验Excel中match函数的使用方法。

5.案列：index函数和match函数实现多条件匹配查找。具体可以观看小编的经验index函数和match函数实现多条件查找

函数引用形式案列

6.如图所示，输入公式=INDEX((B3:D6,G3:I6),4,3)公式解释：一个或多个单元格区域的引用（两个区域B3:D6,G3:I6）行数（4）列数（3）从第几个选择区域内引用（省略，默认第一个区域B3:D6）所以返回120。

7.如图所示，输入公式=INDEX((B3:D6,G3:I6),4,3,2)公式解释：一个或多个单元格区域的引用（两个区域B3:D6,G3:I6）行数（4）列数（3）从第几个选择区域内引用（第二个区域G3:I6）所以返回500。

函数使用的注意点

_num 和 column_num 必须指向数组中的某个单元格。否则，index函数 出错，返回 #REF! 错误值

注意事项

本百度经验由skylemon黄小新编写，转载请注明出处。

亲，如果帮到你的话，请在上方给个投票，或者在下方给个好评，也可以关注小编，让小编知道在茫茫人海中至少有你的支持，因为这是支持我写下去的唯一理由，谢谢。

亲，如果有疑问，可以再下方评论，小编会在第一时间回复你，谢谢。

vlookup函数与index+match函数联用使用比较

简介

Vlookup函数只能作正向查找，而不能反向查找，即根据前列查找后列的内容，而不能根据后列查找前列内容。如下图，vlookup只能根据ID查找姓名，而不能根据姓名查找ID。而index与match联用，即可根据ID查找姓名，又可根据姓名查找ID。

工具/原料

Excel

方法/步骤

如下图，在F3单元格中输入公式=IND苇质缵爨EX($D$3:$D$11,MATCH(C3,$C$3:$C$11,0))，可实现根据ID查找姓名的正窕掷烙宴向查找，在F6单元格中输入=INDEX($C$3:$C$11,MATCH(D6,$D$3:$D$11,0))，可实现根据姓名查找ID的反向查找。在F13单元格中输入=VLOOKUP(D11,$C$3:$C$11,0)，则查找出错“#NA”，即vlookup不能作反向查找。

Index+match的双向查找双向查找即双条件查找。如下图，查找3月份A的业绩，在E14单元格中输入公式=INDEX($E$3:$E$11,MATCH(C14,$B$3:$B$11,0),MATCH(D14,$D$3:$D$11,0))，即可查找到对应的值。

也可将上公式改成数组形式：=INDEX($E$3:$E$11,MATCH(C15&D15,$B$3:$B$11&$D$3:$D$11,0))，然后，同时按Ctrl+Shift+Enter完成输入，得到同样的效果。

Index+match多条件查找下图举例进行三个条件擢爻充种的查找，如要查找1月份A对product4的销量，在F吭稿荔徊14单元格中输入公式=INDEX($E$3:$E$11,MATCH(C14&D14&E14,$B$3:$B$11&$C$3:$C$11&$D$3:$D$11,0))，同时按Ctrl+Shift+Enter完成输入，得到相应的值。

vlookup跨表单向查找虽然vlookup与index+match联用相比逊色些，但其在跨表单向查找还是很有优势的。如下图，在不知道学生是哪个班的情况下，该如何查找呢？

在目标单元格内输入公式=IFERROR(VLOOKUP(E5,Ƈ班'!B:C,2,0),IFERROR(VLOOKUP(E5,ƈ班'!B:C,2,0),IFERROR(VLOOKUP(E5,Ɖ班'!B:C,2,0),"无此人信息")))。当无此人信息时，会显示“无此人信息”。效果如下图。

注意事项

数组公式要同时按Ctrl+Shift+Enter完成输入。

index+match多条件查找只能使用数组公式。

INDEX函数和MATCH函数的应用实例

简介

我们经常在Excel函数公式中会看到INDEX函数，INDEX函数是很常用的查找引用函数。通常情况下，和其他函数一起嵌套使用，比如index+match, 本文我们将通过直观的实例来认识Excel中index函数、match函数、以及index和match函数嵌套的使用方法。

工具/原料

EXCEL 工作表

方法/步骤

INDEX函数的用法： INDEX函数的用法是返回列表区域或数组中的指定位置的内容。函数语法见下图：

例如：我们要在下面表中查找产品3 C001型号的数量，这里，提供三种方法，第一种，可以选定A1到F11区域，查找第4行第4列的数值并返回此数值；第二种，可以选定A2到E11这个区域，查找第3行第4列的数值并返回此数值；当然，还可以选定其他区域，来进行查询并返回数值，如第三种等等，尽管查找区域和行列号不同，返回的数值都是451。总结一下：利用INDEX函数时有以下几个注意点： 1.行列号是所选定区域的行列号，取决于第一个参数array。 2.函数INDEX返回 行号和列号交叉处的单元格中的值。 3.如果将 row_num 或

column_num 设置为 0（零），INDEX 将分别返回整列或整行的引用。 4.如果省略了 row_num 和 column_num，INDEX 将返回由 array 指定的引用区域。

是对一个或多个单元格区域的引用。如果要对引用输入一个非连续区域，请使用括号将该引用括起来，在行列号后面第三个参数为所引用 区域的序号（即第几个区域）。如下图2所示.

可见，同时使用参数行号和列号，函数INDEX返回 行号和列号交叉处的单元格中的值。但是，大多数情况下，区域的序列号我们是不知道的，查找起来不是很复杂呢？这时，我们先来学习另外一种函数MATCH函数。MATCH函数的用法： MATCH函数可以称之为匹配函数,指返回指定数值在指定数组区域中的位置 MATCH函数也是EXCEL主要的查找函数之一， MATCH函数含义：返回指定数值在指定数组区域中的位置，语法如下图所示： 利用MATCH函数查找功能时，当查找条件存在时，MATCH函数结果为具体位置（数值），否则显示#N/A错误。 该函数通常有以下几方面用途： （1)确定列表中某个值的位置； (2)

对某个输入值进行检验，确定这个值是否存在某个列表中； (3)判断某列表中是否存在重复数据； (4)定位某一列表中最后一个非空单元格的位置。

如下如所示：要查找产品3 的C001型号的数量451所在的行列号,我们可以分两步进行查询：第一步；查找451所在行号：在要返回值得单元格中录入公式=MATCH(A4，A2：A11，0),回车，返回结果为3，可见，451在A2到411这个区域的第三行；第二步：查找451所在列号：在要返回值得单元格中录入公式=MATCH(D1，B1：F11，0),回车，返回结果为3。我们验证一下结果是正确的。

INDEX函数和MATCH函数嵌套使用： 好了，INDEX函数和MATCH函数的基本用法我们已经了解了，现在我们来学习一下怎样把这两个函数结合在一起进行查找，为我们工作带来便利。 我们仍然查找产品3型号为C001的数量。在单元格中录入函数=INDEX(B2:F11，MATCH(A4，A2：A11，0),MATCH(D1，B1：F1)),返回结果为(A4，A2：A11，0)返回结果为3，说明A4(产品3)在A2到A11区域的第三行，MATCH(D1，B1：F1)返回结果为3，说明D1（型号C001）在B1到F1区域的第三列。

求上述产品3型号为C001的数量还有很多种方法，可以通过选定不一样区域来实现，例如：”=INDEX(A1：F11，MATCH(A4，A1：A11，0),MATCH(D1，A1：F1，0))"‘再如：”=INDEX(A2：E10，MATCH(A4，A2：A11，0),MATCH(D1，A1：E1，0))“大家动手试一试吧！

注意事项

需要注意的是：在使用MATCH函数的时候，MATCH函数在多次出现的数字中，查找第一次出现的位置，忽略其他次数的位置。

如果本经验对您有一点帮助，请投我一票，在此万分感谢！！