|
关于数据查询，大家想到的可能想到用VLOOKUP函数或HLOOKUP函数查找功能实现，但是通过INDEX与MATCH这两个函数的组合应用，不仅可以实现VLOOKUP函数和HLOOKUP函数的功能，并且可以实现任意方向的查询以及多条件查询等。今天通过一个案例彻底掌握INDEX与MATCH这两个函数的应用。 一，INDEX与MATCH函数的语法 INDEX(array,row_num,[column_num])，指返回表或区域中的值或对值的引用 三个参数分别为数据表区域，行数，列数 MATCH(lookup_value,lookup_array,[match_type])，指返回指定数值在指定数组区域中的位置 三个参数分别为查找的值、查找单元格区域、查找方式 下图为某公司导出的数据源： 一，单个数据查找 根据表一要求：通过A列的客户ID在数据源中查找公司名称（向右查找） 首先：明白INDEX函数和MATCH函数分别的应用 例：查找A2单元格在数据源A列的位置 在C20单元格输入公式=MATCH(A2,数据源!A:A,0)，结果为15，表示A2单元格在数据源A列的位置为第15行。 例：查找B1单元格在数据源A1:K1中的位置 在C21单元格输入公司=MATCH(B1,数据源!A1:K1,0)，结果为2，表示B1单元格在数据源A1:K1中的位置为第2列。 例：在数据源B列中找15行的数据 在C22单元格输入公式=INDEX(数据源!B:B,15)，结果为“浩天旅行社”，表示在数据源B列中找15行的数据结果为“浩天旅行社” 其次：在明白了INDEX函数和MATCH函数后，通过组合即可实现数据查询 方法一，通过VLOOKUP实现 在B2单元格输入公式=VLOOKUP(A2,数据源!A:B,2,0)，回车向下拖动即可。 方法二，通过INDEX与MATCH这两个函数组合实现 在C2单元格输入公式=INDEX(数据源!B:B,MATCH(A2,数据源!A:A,0))，回车向下拖动即可。 根据表二要求：通过A列的公司名称在数据源中查找客户ID（向左查找） 方法一，通过VLOOKUP实现 在B2单元格输入公式=VLOOKUP(A2,IF({1,0},数据源!B:B,数据源!A:A),2,0)，回车向下拖动即可。 方法二，通过INDEX与MATCH这两个函数组合实现 在C2单元格输入公式=INDEX(数据源!A:A,MATCH(A2,数据源!B:B,0))，回车后向下拖动即可。 二，快速多个数据同时查找 情况一，返回数据的表头和数据源所在的表头顺序一致 根据表三要求：快速查找数据 常规思维：我们在每一个列都使用查找引用函数 ， 在E4单元格输入公式=VLOOKUP(D4,数据源!A:K,2,0); 在F4单元格输入公式 =VLOOKUP(D4,数据源!A:K,3,0); 在G4单元格输入公式=VLOOKUP(D4,数据源!A:K,4,0); 在H4单元格输入公式=VLOOKUP(D4,数据源!A:K,5,0)… 分析：关察发现，返回数据的表头和数据源对应数据的表头所在的列的顺序一致，列号不一致，都差3 通过VLOOKUP+COLUMN快速解法: COLUMN函数，COLUMN(reference) Reference为需要得到其列标的单元格或单元格区域 如果省略reference，则假定为是对函数COLUMN所在单元格的引用。简单讲就是返回单元格的列号。 在E4单元格输入公式VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0)，然后向右向下拖动即可得到结果，'COLUMN()-3'可以理解为一个变量，根据实际情况变动。 情况二，返回数据的表头和数据源所在的表头顺序不一致 根据表四要求：快速查找数据 观察发现，返回数据的表头和数据源对应数据的表头所在的列的顺序不一致，没规律，但通过MATCH函数我们可以查找表头在数据源中的位置。 方法：通过INDEX与MATCH这两个函数组合快速实现，在E4单元格输入公式=INDEX(数据源!$A:$K,MATCH($D4,数据源!$A$1:$A$92,0),MATCH(E$3,数据源!$A$1:$K$1,0))，然后向右向下拖动即可。 三，通过Index+Match实现图片的引用 根据表五要求：实现引用图片 效果：G5单元格设置了数据有效性，当我们选择不同的姓名，可以看到员工的照片可以跟着变动。 方法：先在G8单元格设置公式：=INDEX($D$5:$D$8,MATCH($G$5,$A$5:$A$8,0)),注意单元格的引用，复制公式后删除；然后，选择表外任意单元格，在工具栏中选择 公式，然后定义名称 照片，引用位置处复制我们刚才的函数公式；然后在G8处输入=照片 就可以实现引用。EXCEL10版本要通过照相机功能实现哟。 INDEX与MATCH这两个函数的应用你学会了吗？ 编辑：共享财税实务 转载请备注出处
|
|
|
