excel常见函数组合（零基础入门Excel函数篇）

在数据分析中，数据的查找、对比等非常常见，这就需要用到关联匹配类函数，本文将介绍Excel数据分析中常用的关联匹配类函数，如vlookup、hlookup、index、match及rank等。

1、vlookup

vlookup是Excel查找函数家族中最为常用的一个函数，如果你经常和Excel打交道，那么一定使用过vlookup。

功能：用于数据区域的纵向查找。

用法：

vlookup(lookup_value,table_array,col_index_num,range_lookup)

参数说明：

• 第1个参数lookup_value：根据什么查找。
• 第2个参数table_array：在哪个数据区域中查找。
• 第3个参数col_index_num：要查找的字段在数据区域中的第几列。
• 第4个参数range_lookup：匹配类型，精确匹配还是近似匹配，如果是近似匹配，则返回小于该数值的最大数值（关于Excel中的模糊匹配后面会单独发文）。

例如，需要根据用户编码（用户ID）查找用户的其他信息，如注册时间、年龄、性别、省份和城市等，表格名称为“vlookup”，如下图所示。

数据源，即查找的数据区域，在另外一个表里，表格名称为“用户数据源”，如下图所示。

如何使用vlookup查找注册时间呢？

在表格“vlookup”中，在单元格B2中输入公式：=VLOOKUP(A2,用户数据源!A:F,2,0)，如下图所示。

说明：

• 第1个参数：A2，表示第一条记录的用户编码。
• 第2个参数：用户数据源!A:F，代表表格“用户数据源”中所有列，即列A至列F。
• 第3个参数：2，表示要查找的字段，注册时间，在查找区域中的第2列。
• 第4个参数：0，表示精确匹配，因为这里是根据用户编码去匹配，每个用户的用户编码都是唯一的，所以是精确匹配（第4个参数，输入公式的时候会有提示，根据提示去选择即可）。

如果用vlookup可以查找年龄，公式为：=VLOOKUP(A2,用户数据源!A:F,3,0)，如下图所示。

因为要查找的字段“年龄”位于查找区域的第3列，所以第3个参数是3。

类似地，可以用vlookup将其余字段，性别、省份及城市，都查找出来，大家可以自行练习。

说明：关于本文中用到的数据表格，关注后回复“Excel公式”可免费领取！

2、hlookup

hlookup跟vlookup类似，只是查找的数据结构有些区别。

功能：用于数据区域的横向查找。

用法：

hlookup(lookup_value、table_array、row_index_num、[range_lookup])

参数说明：

• 第1个参数：lookup_value，表示根据什么查找。
• 第2个参数：table_array，要查找的数据区域，即在哪里查找。
• 第3个参数：row_index_num，要查找的字段位于数据区域的第几行（注意和vlookup中的列有所区别）。
• 第4个参数：[range_lookup]，匹配类型，精确匹配还是近似匹配。

例如，还是之前的问题，需要根据用户编码（用户ID）查找用户的其他信息，如注册时间、年龄、性别、省份和城市等，表格名称为“hlookup”，如下图所示。

这个图中，上方区域（灰色的）表示数据源（只有两条记录，每一列代表一条记录），下方区域表示要查找的区域，只有用户编码，其他字段均需要根据用户编码查找。

说明：这里只是为了方便讲解，将这两个区域放到了同一个表格中，实际工作中的数据一般不在同一个表格中，但hlookup的用法是一样的。

在单元格B9中输入公式：=HLOOKUP(B8,A1:C6,2,0)，如下图所示。

在上面的公式中，第3个参数2表示要查找的字段“注册时间”位于查找区域的第2行，其余参数跟vlookup中的类似。

通过公式可以看出，hlookup和vlookup的用法是类似的，只是数据区域不同。

如果需要查找出年龄，公式为：=HLOOKUP(B8,A1:C6,3,0)，这里只是将第3个参数变成了3，因为要查找的字段“年龄”位于查找区域的第3行，如下图所示。

用同样的方法可以将其余字段，如性别、省份及城市，均查找出来，大家可以自行练习。

3、index

功能：根据位置返回单元格的值

用法：= index(array, row_num, [column_num])

参数说明：

• 第1个参数：array，表示要查找的区域，即目标区域。
• 第2个参数：row_num，通过该参数指定要查找的值位于目标区域的第几行。
• 第3个参数：[column_num]，通过该参数指定要查找的值位于目标区域的第几列，可确省。

例如，在以下数据区域中，查找满足要求的数据，如下图所示。

问题1：查找排名第3的学员姓名？

在单元格E2中输入公式：=INDEX(A2:B6,3,2)，如下图所示。

说明：我们选择的目标区域是A2:B6，查找的目标“排名第3的学员”位于目标区域的第3行、第2列，所以index的后面两个参数为3和2。

问题2：查找排名第3的学员成绩？

在单元格E3中输入公式：=INDEX(A2:C6,3,3)，如下图所示。

说明：我们选择的目标区域是A2:C6，查找的目标“排名第3的学员成绩”位于目标区域的第3行、第3列，所以index的后面两个参数为3和3。

当然，对于问题2，还可以这样写公式：=INDEX(C2:C6,3)

因为要查找的目标“排名第3的学员成绩”位于C列，所以只选择C列，此时只需要指定第2个参数，即行的位置，由于只有一列，所以第三个参数可以直接省略。

4、match

match跟index相反，是根据值来返回位置。

功能：根据单元格的值返回位置。

用法：= match(lookup_value, lookup_array, [match_type])

参数说明：

• 第1个参数：lookup_value，表示要查找的值。
• 第2个参数：lookup_array，要查找的区域。
• 第3个参数：[match_type]，查找类型，精确匹配还是模糊匹配，跟vlookup中的模糊匹配是类似的。

例如，需要查找老王的排名，可以写公式：=MATCH("老王",B2:B6,0)，如下图所示。

说明：这里需要查找老王的排名，其实就是根据值“老王”去查找它的位置。第1个参数为“老王”，是一个字符串，第2个参数表示姓名这个区域，第3个参数，0，表示精确匹配。

5、rank

功能：返回一列数字的排名。

用法：rank(number,ref,[order])

参数说明：

• 第1个参数：number，表示参加排名的数字。
• 第2个参数：ref，表示排名的区域，即在哪个范围中排名。
• 第3个参数：[order]，表示排名的类型，升序还是降序，0表示降序，1表示升序，默认为降序。

例如，已知所有员工的销售业绩，根据员工的销售业绩给出对应的名次，如下图所示。

这里利用公式rank直接给出了排名，第1个参数B2表示要排序的数据，即编号为1的员工的销售业绩，第2个参数B:B表示要排序的区域B列，即所有员工的销售业绩，第3个参数0，表示进行降序排列。

总结：以上是Excel数据分析中常用的关联匹配类函数。

关注我，学习更多数据分析知识！

Excel数据分析全套视频教程已上线，涵盖6大模块：Excel函数、Excel常用技巧、数据透视表、Excel图表、基础统计分析和Excel VBA，目前限时优惠中！

,