Excel_关联匹配类函数

关联匹配类函数

1.1关联匹配类知识体系图

1.2   关联匹配类

1.2.1 函数中的大众情人VLOOKUP

功能：用于查找首列满足条件的元素。

语法：=VLOOKUP（要查找的值、要在其中查找值的区域、区域中包含返回值的列号、精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE）。

例如，希望在3万条数据中，找出用户id为以下9个用户的部分信息：一级注册渠道、注册时间、年龄和性别。

查找区域如下：

通过右下角的计数可以看到，总共有30000+条记录。

查找的目标用户：

1.建立查询结果显示区域，这里需要查询的字段有一级注册渠道、注册时间、年龄和性别:

2.在一级注册渠道底下的单元格C2输入“=Vl”，Excel就会提示VLOOKUP:

3.鼠标双击选中，然后，选择对应的参数:

4.第一，选择的是要查找的目标，选择B2单元格，然后输入英文状态下的逗号。

5.第二，选择要查找的目标列，这里选择前5列，如下图所示:

6.选择完后，输入逗号（英文状态下），然后输入第几列，之前在选择列的时候，可以看到右上角（见上图）出现一个2C，这个2其实就是列号，输入2，然后输入逗号。

7.最后，需要输入查找的类型，这里输入数字，1代表TRUE，0代表FALSE，这里输入0，回车。

8.然后其他信息，例如注册时间、年龄、性别都需要查询出来，可以采用拖动单元格的方法。

1.2.2  绝对定位符$

接着上面的VLOOKUP，往右拖动一个，发现注册时间被查询出来了，但是观察公式后，发现查询的目标由B2变成了C2，相当于注册时间是通过一级注册渠道查询出来的，这样会导致查询出来的数据不准确，因为用户id是唯一的，一级注册渠道是重复的，所以不想让B2这个单元格的列变动。

1>B2，B表示列号，2表示行号，如果不需要列号变动，则可以在B前面增加$，同理，后面的查找的目标列的列号也不希望变动，只需要在列号前面加上$。

2>然后向右拖动单元格，并把第三个参数，列号改成对应的列号，即可得到第一个用户id的相关信息。

3>继续查找B3至B10的相关信息，选中C2：F2，鼠标移至选中区域的右下角，当光标变成十字的时候，双击鼠标左键即可完成其余用户id的查询。

1.2.3  INDEX

功能：INDEX 函数返回表格或区域中的值或值的引用。

语法：= INDEX(array, row_num, [column_num])

1.array必需。单元格区域或数组常量。

(1)如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。

(2)如果数组有多行和多列，但是如果只使用 row_num 或 column_num，函数 index 返回数组中的整行或整列，且返回值也为数组。

2.row_num必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num，则必须有 column_num。

3.column_num可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num，则必须有row_num。

示例：

有一张表，有班级名称、班级人数及平均成绩等信息，现在想查询6班的平均成绩，可以使用index实现。

输入公式，“=INDEX(B2:D7,6,3)”，回车即可得到结果。

可以看到，第一个参数B2:D7表示一个区域，即查询范围，第二参数6表示查询目标在区域的第几行，第三个参数3表示查询目标在区域的第几列。

1.2.4   MATCH

功能：用于返回指定内容在指定区域（某行或者某列）的位置。

语法：= MATCH(lookup_value, lookup_array, [match_type])

1.lookup_value 必需。要在 lookup_array 中匹配的值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。

2.lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

3.lookup_array必需。要搜索的单元格区域。

4.match_type可选。数字 -1、0 或 1。match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。此参数的默认值为1。

说明：匹配类型有三个值，0表示等于，1表示小于，-1表示大于。

示例：

例如希望查询6班所的单元格的位置（在指定区域的第几行），

输入公式“=MATCH(B7,B2:B21,0)”，

输入完成后，回车。

有了match函数后，index可以结合match来使用。

通过match函数来获得成绩在查询区域的第几行：

也可以通过vlookup来查询6班的平均成绩：

1.2.5   RANK

功能：返回一列数字的数字排位。数字的排位是其相对于列表中其他值的大小。

语法：RANK(number,ref,[order])

1.number必需。 要找到其排位的数字。

2.ref必需。 数字列表的数组，对数字列表的引用。ref 中的非数字值会被忽略。

3.order可选，默认为0。一个指定数字排位方式的数字。

如果order为0，Microsoft Excel对数字的排位是基于ref为按照降序排列的列表。

如果order不为零，Microsoft Excel对数字的排位是基于ref为按照升序排列的列表。

示例：

如果需要各个班级的平均成绩的排名，当然可以通过对单元格进行排序来实现，但是如果不改变目前的顺序，如何得到排名呢？就需要有用到rank函数。

输入公式，=RANK(D2,D:D,0)，D2表示需要排序的数据，D:D表示排序的数据范围，0表示降序，1表示升序，这里采用降序排名：

输入公式后，回车，并双击单元格，完成其他班级成绩的排名：