Excel排名函数公式大全.docx

Excel排名函数公式大全.docx

《Excel排名函数公式大全.docx》由会员分享，可在线阅读，更多相关《Excel排名函数公式大全.docx（58页珍藏版）》请在冰豆网上搜索。

Excel排名函数公式大全

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函数是一个引用函数，表示引用某一个单元格或者区域。

其也是我们日常做一些组合函数中的一种，需要熟练掌握。

工具/原料

OfficeExcel

以单元格做参考系

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软件

电脑

函数介绍：

1.MATCH函数（返回指定内容所在的位置）MATCH（lookup-value,lookup-array,match-type）lookup-value:

表示要在区域或数组中查找的值，可以是直接输入的数组或单元格引用。

lookup-array:

表示可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。

match-type:

表示查找方式，用于指定精确查找（查找区域无序排列）或模糊查找（查找区域升序排列）。

取值为-1、1、0。

其中0为精确查找。

2.INDEX函数（返回制定位置中的内容）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函数

1.index函数的含义

1.index函数的含义返回数据表区域的值或对值的引用。

Index函数的两种形式：

数组和引用。

数组形式——返回数组中指定单元格或单元格数组的数值。

引用形式——返回引用中指定单元格或单元格区域的引用。

2.index函数的语法格式

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

3.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函数实现多条件查找

4.index函数引用形式案列

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。

5.index函数使用的注意点

8.Row_num和column_num必须指向数组中的某个单元格。

否则，index函数出错，返回#REF!

错误值

注意事项

本XX经验由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函数的用法是返回列表区域或数组中的指定位置的内容。

函数语法见下图：

例如：

我们要在下面表中查找产品3C001型号的数量，这里，提供三种方法，第一种，可以选定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指定的引用区域。

5.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函数和MATC