excel用vlookup显示公式,excel利用vlookup函数计算单价

首页 > 实用技巧 > 作者:YD1662023-06-08 17:14:02

所谓一对多,就是一个查找值对应了多个结果值,如何使用vlookup函数来完成一对多的查找引用,是我们要学习的进阶课题。

一对多查找又有两个常用方法,一个是创建辅助列来提取,另一个则是嵌套其他函数的组合公式引用。

excel用vlookup显示公式,excel利用vlookup函数计算单价(1)

一对多匹配引用是excel表格比较常见的一个场景,但也成为许多使用者的难题。

在过去通常要利用嵌套多个函数的数组公式来达到匹配效果,但随着excel版本更新,新函数filter可以一步到位,快速提取所有符合条件的值。

但今天作者还是以过去应用较多的vlookup函数来介绍具体匹配引用方法。

首先还是继续介绍创建辅助列来一对多查询。

1、创建辅助列


创建辅助列的作用是为了获取一个唯一不重复的查询列表,因此结合原查询列表和countif函数计数值,得到新的查询列。

公式为:=B2&COUNTIF($B$2:B2,B2)

excel用vlookup显示公式,excel利用vlookup函数计算单价(2)

接下来直接设置vlookup函数公式:

=VLOOKUP($E$5&COLUMN(A1),$C:$D,2,0)

其第1参数查找值使用了column函数,它的作用是返回单元格的列序号,比如COLUMN(A1)等于1,COLUMN(B1)则等于2;

第2参数查找区域以辅助列为查询列,然后返回销量列对应的数据。

这个公式的特点,就是通过查找值与column函数的结合,来动态引用查找值对应的多个结果。

如下图所示,由于是横向拖动公式,因此嵌套column函数,将依次得到1、2、3、4的列序号值,而辅助列中查找值与1-4的合并数据,都是唯一不重复的,因而得以被vlookup函数进行动态引用。

excel用vlookup显示公式,excel利用vlookup函数计算单价(3)

既然可以横向引用,自然也可以纵向引用,只需要将column函数替换为引用行序号的row函数即可!

公式为:=VLOOKUP($E$6&ROW(A1),$C:$D,2,0)

excel用vlookup显示公式,excel利用vlookup函数计算单价(4)

从这样一个案例来看,其实vlookup函数的一对多应用非常简单,没有任何复杂的地方,都是基础函数的常见用法。

但接下来要介绍的第二种一对多公式写法,就相对繁琐了,其中嵌套了多个函数,执行数组运算,理解起来需要费些脑细胞。

2、组合公式引用


通过一个公式怎么快速引用所有的结果值,首先要思考解题的思路。

有了创建辅助列解答的经验,其实我们可以借鉴countif函数获取一个唯一值列表

但countif函数输出的是一个结果,如何使一个结果转换成一个列表,则是问题的关键。

这里作者利用row函数来获取一个数字列表,然后结合indirect函数的单元格引用,来创建一个单元格列表,再利用countif函数计数时,将会得到一个数组结果。

到了这一步,其实已经能够获取不重复的唯一值列表。

但语言描述太笼统,下面直接上公式:

=VLOOKUP($D$5&COLUMN(A1),IF({1,0},$B$2:$B$29&COUNTIF(INDIRECT("b2:b"&ROW($2:$29)),$D$5),$C$2:$C$29),2,0)

excel用vlookup显示公式,excel利用vlookup函数计算单价(5)

这个公式组合了if数组、countif、indirect和row函数,如上所讲,countif函数得到一个计数的数组结果时,然后直接与货号列进行合并,直接得到了与创建辅助列的结果相同的查询列表。

然后利用if数组在vlookup函数中的应用,进行查询列和返回列的设置。从这一步来看,其实之前讲解的vlookup if函数的两种用法,也是这个案例中的一个解题要点!

最后给vlookup的第1参数查找值连接上column函数,进行横向的动态引用。

excel用vlookup显示公式,excel利用vlookup函数计算单价(6)

对于这个组合公式的理解,可以在公式编辑栏中对公式各组成部分进行分步解析,它的关键点还是在于数组列表的创建和查找区域的指定设置。

阅读更多:


栏目热文

文档排行

本站推荐

Copyright © 2018 - 2021 www.yd166.com., All Rights Reserved.