1500字范文 > Excel数据分析从入门到精通（六）函数进阶-52个Excel函数之关联匹配函数

Excel数据分析从入门到精通（六）函数进阶-52个Excel函数之关联匹配函数

时间：2019-03-22 16:48:08

1.关联匹配函数VLOOKUP函数含义函数使用 2.关联匹配函数-hlookup函数含义函数使用 3.关联匹配函数-lookup函数含义函数使用 .关联匹配函数-INDEX函数含义函数使用 .关联匹配函数-match函数含义函数使用 .关联匹配函数-offset函数含义函数使用 .关联匹配函数-row函数含义函数使用 .关联匹配函数-column函数含义函数使用

1.关联匹配函数VLOOKUP

函数含义

解释：作用为在表格的首列查找制定的数据，并返回指定数据所在行中的指定列处的数据

使用：=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value：要匹配查找的内容某一单元值

table_array：去匹配查找的数组区域（匹配在首列）

col_index_num：返回的内容所在的列数

[range_lookup]：0为精确匹配，1为近似匹配

函数使用

有工资明细表和员工部门表如下

要求、公式及结果如下

[range_lookup]可以为0/1，也可以为true/false

其实可以理解为SQL中的inner join，lookup_value为on后的连接条件中的主表部分；
table_array相当于从表，是一个区域，要求首列中有on后连接条件，相当于连接条件的从表部分，函数会去查找选中区域的首列中是否有连接条件值；
最后查找的为从表中的值，因为连接条件即可确定数值所在行，故只需要在col_index_num给出需要select的值的列数即可；
最后的[range_lookup]false即为精确匹配，而true为查无对应值的时候近似返回小于查找值的最大值

以查找工资为1800的人的部门为例

select department_name from departments as deinner join salaries as sa#相当于选中区域table_arrayon de.name = sa.namewhere sa.salary = 1800;/*不过在Excel中不需要where函数确认name直接选中name作为lookup_value即可*/

= =写得有点乱，这个大概是我的理解，对于学过SQL的人这个函数还是比较好理解的

2.关联匹配函数-hlookup

函数含义

解释：可以在表格或数值数组的首行查找指定的数值，并返回表格或数组中指定行的同一列的数值

使用：=HLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value：要匹配查找的内容某一单元格

table_array：去匹配查找的数组区域，匹配行在首行

col_index_num：返回的内容所在行数（首行为1）

[range_lookup]：0为精确匹配，1位近似匹配

函数使用

有一个数据表如下

要求、公式及结果如下

同样可以用SQL中的where语句来理解

比如要查橙子的价格，语句为

select 价格 from 数据表 where 水果 = 'orange'

from后的数据表类比为table_array，但是要求where后面的条件值在所选中区域的首行

水果 = orange理解为锁定/查找列，即要在表格的首行寻找有lookup_value值的那一列，最后输出的数值在这一行

价格其实相当于行数，即col_index_num，价格在第三行，等同于select第三行的数据from数据表

通过行列即可锁定一个确定的数据

3.关联匹配函数-lookup

函数含义

解释：是一种运算函数，实质是返回向量或数组中的数值，要求数值必须按升序排序

使用： =lookup(lookup_value,lookup_vector,[result_vector])

lookup_value：要匹配查找的内容，可以为具体的数值或者字符，也可以是某一单元格

lookup_vector：去匹配查找的数组区域（列）lookup_value的值需要在此区域（列）内，为了锁定行

[result_vector]：返回的内容所在的列，选中的为一列数据

函数使用

有数据表格如下，姓名为E6

要求、公式及结果如下

经测试，lookup_vector的区域的列数需要与[result_vector]的列数相同

.关联匹配函数-INDEX

函数含义

解释：返回表或区域中的值或值的引用，以行列数返回对应内容

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

array：选定的内容区域

row_num：行（列数），以内容区域左上角为起点，注意当只有两个参数，即没有[column_num])时，所选区域为单行或者单列，返回值为该行/列的第row_num个内容

函数使用

有数据表格如下，姓名为E6

要求，公式和结果如下

如果index函数第二个或者第三个参数为0，函数将分别返回整列或整行的数组值，输出区域需要和数值数量相同，且用Ctrl+shift+enter结束公式

.关联匹配函数-match

函数含义

解释：返回指定数值在指定数组区域中的位置

使用：match(lookup_value,lookup_array,[match_type])

lookup_value：要查找的内容单元值，若是字符型需要加双引号

lookup_array：要在其查找的区域（行列）

[match_type]：1-小于或等于lookup_value的最大值，0-等于lookup_value的第一个值，-1-大于或等于lookup_value的最小值

函数使用

有数据表格如下，姓名为E6

要求、公式与结果如下

（1）Match_type：1 或省略，表示MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列，例如：…-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE。
（2）Match_type：0，表示MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。
（3）Match_type：-1，表示MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, … 等等。