体验零代码搭建

关于VLOOKUP,你必须知道的23件事(中)

网友投稿  ·  2023-11-28 10:11  ·  产品资讯  ·  阅读 459

7.你可以强迫VLOOKUP执行完全匹配

7.你可以强迫VLOOKUP执行完全匹配

关于VLOOKUP,你必须知道的23件事(中)

要强迫VLOOKUP找到完全匹配,确保设置第4个参数(range_lookup)为FALSE或0。下面两个公式等价:

=VLOOKUP(value,table,column,FALSE)

=VLOOKUP(value,table,column,0)

在完全匹配模式下,当VLOOKUP不能找到值时,返回#N/A。清楚地表明没有在表中找到值。

8.你可以告诉VLOOKUP执行近似匹配

要使用VLOOKUP的近似匹配模式,忽略第4个参数(range_lookup)或者设置其为TRUE或1。下面3个公式等价:

=VLOOKUP(value,table,column)

=VLOOKUP(value,table,column,1)

=VLOOKUP(value,table,column,TRUE)

推荐总是显式设置range_lookup参数,即使VLOOKUP不需要。这样,你总能明显地看到你期望的匹配模式。

9.对于近似匹配,数据必须排序

如果使用近似匹配模式,那么数据必须根据查找值按升序排序。否则,可能得到的是错误结果。同时注意,有时文本数据可能看起来已排序,虽然实际上并没有排序。

10.VLOOKUP能够合并不同表中的数据

VLOOKUP的常见使用示例是连接来自两个或多个表中的数据。例如,可能在一个表中有订单数据,在另一个表中是客户数据,想要将一些客户数据合并到订单表中进行分析:

图7

由于客户Id在两个表中都存在,可以在VLOOKUP中使用这个值来提取数据,只需配置VLOOKUP使用表1中的Id值,表2中相应的列索引和数据。在上例中,使用两个VLOOKUP公式,一个提取客户名称,另一个提取客户状态。

图8

11.VLOOKUP能够识别或分类数据

如果需要将任意类别应用于数据记录,那么可以使用VLOOKUP轻松完成此操作,方法是使用担当“键”的表来赋值分类。

一个经典的例子是基于分数来赋值成绩:

图9

本例中,VLOOKUP设置为近似匹配,因此表按升序进行排列是重要的。

然而,也可以使用VLOOKUP来赋值任意类别。在下面的例子中,使用VLOOKUP来为每个部门计算一个组,使用了定义分组的小表(称为“key”)。

图10

12.绝对引用使VLOOKUP更具可移植性

在打算从表中获取多于1列的信息的情形下,或者需要复制和粘贴VLOOKUP时,可以通过对查找值和表数组使用绝对引用来节省时间。这可让你复制公式,然后仅改变列索引编号以使用相同的查找来从不同列中获取值。

下面的示例中,因为查找值和表数组是绝对引用,所以可以跨列复制公式,然后按需要回来修改列索引。

图11

13.命名区域使VLOOKUP更容易阅读(并且更可移植)

绝对单元格区域相当难看,因此可以通过使用命名区域代替绝对引用使VLOOKUP公式更简洁易读。

在图11中,命名输入单元格为“id”,命名表中的数据为“datas”,可以编写公式:

图12

不仅公式易读,而且更具可移植性,因为命名区域自动为绝对引用。

14.插入列可能中断现有的VLOOKUP公式

如果工作表中已经存在VLOOKUP公式,那么在表中插入列时可能中断公式。这是因为当插入或删除列时,硬编码的列索引值不会自动更改。

本示例中,当在Year和Rank之间插入新列后,查找Rank和Sales被中断,而Year工作正常,因为其所在列在插入列的左侧,没有受到影响:

图13

为了避免这种问题,可以使用下文描述的技巧计算列索引号。

15.可以使用ROW或COLUMN计算列索引号

如果不想在复制公式后还要对公式进行编辑,那么可以使用ROW或COLUMN来生成动态的列索引号。如果从连续列中获取数据,这个技巧可让你设置一个VLOOKUP公式,然后将其复制而无需进行任何修改。

本例中,使用COLUMN函数生成动态的列索引号。在单元格C3中,COLUMN函数返回当前列的列号3,将其减去1得到表中列的索引号,然后向右复制该公式:

图14

所有的公式都是相同的,不需要任何编辑。使用的公式如下:

=VLOOKUP(ids,datax,COLUMN()-1,0)

(未完待续……)


图书进销存软件 图书进销存管理系统软件功能 << 上一篇
2023-11-28 10:11
关于VLOOKUP,你必须知道的23件事(上)
2023-11-28 10:11
下一篇 >>

相关推荐