VLOOKUP函数的高级使用方法
发布时间:2023-12-11 22:22:52
VLOOKUP的反向查找
一般情况下,VLOOKUP函数只能从左向右查找。但如果需要从右向右查找,则需要把区域进行“乾坤大挪移”,把列的位置用数组互换一下。
例1:要求在如下图所示表中的姓名反查工号。
公式:=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)
公式剖析:
1、这里其实不是VLOOKUP可以实现从右至右的查找,而是利用IF函数的数组效应把两列换位重新组合后,再按正常的从左至右查找。
2、IF({1,0},B2:B5,A2:A5)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时(前提时该函数的参数支持数组),返回的结果也会是一个数组。这里1和0不是实际意义上的数字,而是1相关于TRUE,0相当于FALSE,当为1时,它会返回IF的第二个参数(B列),为0时返回第二个参数(A列)。根据数组运算返回数组,所以使用IF后的结果返回一个数组(非单元格区域):{"张一","A001";"赵三","A002";"杨五","A003";"孙二","A004"}
VLOOKUP函数的多条件查找
VLOOKUP函数需要借用数组才能实现多条件查找。
例2:要求根据部门和姓名查找C列的加班时间。
我们可以延用例1的思路,我们的努力方向不是让VLOOKUP本身实现多条件查找,而是想办法重构一个数组。多个条件我们可以用&连接在一起,同样两列我们也可以连接成一列数据,然后用IF函数进行组合。
公式:{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}
公式剖析:
1、A9&B9 把两个条件连接在一起。把他们做为一个整体进行查找。
2、A2:A5&B2:B5,和条件连接相对应,把部分和姓名列也连接在一起,作为一个待查找的整体。
3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF({1,0}把连接后的两列与C列数据合并成一个两列的内存数组。按F9后可以查看的结果为:
{"销售张一",1;"销售赵三",5;"人事杨五",3;"销售赵三",6}
4、完成了数组的重构后,接下来就是VLOOKUP的基本查找功能了,另外公式中含有多个数据与多个数据运算(A2:A5&B2:B5),,所以必须以数组形式输入,即按ctrl+shift后按ENTER结束输入。
VLOOKUP函数的批量查找
VLOOKUP一般情况下只能查找一个,那么多项该怎么查找呢?
例3 要求把如图表中所有张一的消费金额全列出来
经过前面的学习,我们也有这样一个思路,我们在实现复杂的查找时,努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找,我们可以对查找的内容进行编号,第一个出现的是后面连接1,第二个出现的连接2。。。
公式:{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}
公式剖析:
1、B$9&ROW(A1) 连接序号,公式向下复制时会变成B$9连接1,2,3
2、给所有的张一进行编号。要想生成编号,就需要生成一个不断扩充的区域(INDIRECT("b2:b"&ROW($2:$6)),然后在这个逐行扩充的区域内统计“张一”的个数,在连接上$B$2:$B$6后就可以对所有的张一进行编号了。
3、IF({1,0}把编号后的B列和C组重构成一个两列数组
通过以上的讲解,我们需要知道,VLOOKUP函数的基本用法是固定的,要实现高级查找,就需要借助其他函数来重构查找内容和查找数组。
猜你喜欢
- 如果要在图表中添加涨/跌柱线,可以按照以下步骤进行操作。打开“收入统计表.xlsx”工作簿,切换至“Sheet2”工作表。选中工作表中的二维
- 打印一张毕业论文的封面,需要用A3纸来打印,可以当我打开页面设置时取发现没有A3纸这一选项,奇怪怎么以前有呢?原来是因为打印机的缘故,下面给
- word试卷怎么添加作文稿纸?在制作试卷的时候,感觉作文草纸不够,该怎么添加草纸呢?下面我们就来看看word中给试卷添加作文稿纸的教程,需要
- 在【分页预览】视图模式下可以很方便的查看当前工作表的打印区域及分页设置,并且可以直接在视图中调整分页哦;单击【视图】选项卡的【工作簿视图】组
- 在职场中,遇到按时间变化的数据,老板让你处理一下,你可以用EXCEL表的条形图,也可以用它的折线图,但是......都显得普通而不入眼,下面
- 这篇文章主要为大家介绍了微软更新OfficeMix门户网站、以及PPT加入新功能,为了让用户使用Office更加得心应手,将Mix的分析导出
- 这篇文章将教大家一个非常实用的excel使用技巧,在excel中如何把多张明细表数据汇总在一起,也就是把excel明细表快速做成汇总表,方法
- 1、WORD文字的格式与此网站文字的格式不相配2、粘贴过程中出现问题3、WORD文字与此网站不兼容
- Acrobat怎么快速隐藏所有注释?Acrobat打开的pdf文件想要隐藏注释,该怎么快速隐藏所有注释呢?下面我们就来看看详细的教程,需要的
- 如果你是一个观察很仔细的人就会发现电脑存在的许多问题,小编在后台看见有用户提问Win10关机转圈转了很久,但心电脑出现什么问题,所以来求助小
- excel表格只有26行的解决方法下面是一个工作表的界面,你会发现只有26行,后面的行标不见了,全是灰色的。怎么回事呢?操作步骤很简单,选取
- DURATION函数用于计算假设面值¥100的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值,用于计量债券价格对于收益率
- 许多文稿,特别是比较正式的文稿都需要设置页眉和页脚。得体的页眉和页脚,会使文稿显得更加规范,也会给阅读带来方便。一般情况下,文档首页都不需要
- Excel是Office中一个表格办公软件,用Excel做报表比用Word上制作表格方便的多,不用自己去画那一格一格的表格,那下面小编就教大
- 在Excel中用户时常要和单元格、行、列打交道。但在注意它们的同时,是否还注意过包围这些单元格的边界,利用Excel在这些边界上提供的附加功
- excel表格是提供给别人填写的,我们不可能一个个通知他们写什么内容。那么有没有什么办法限制excel输入的内容呢?其实设置方法很简单,下面
- 我们在编辑Word文档的时候,经常需要在文章开头添加目录,那么下面就由小编给大家分享下word自动生成目录例文的技巧,希望能帮助到您,有需要
- Excel表格相信对于每个上班族都不会陌生,很多的企业公司都会把数据统计在Excel表格中,excel作为一个多功能的表格统计应用,很大的帮
- excel在方案建立完成后,有时可能还需要对其进行编辑修改,例如修改方案或删除方案等操作。如果用户要对方案进行编辑,则可以执行以下操作步骤。
- 在Word2010中内容较多的表格,难免会造成跨两页或者多页的情况,如果希望表格的标题可以自动地出现在页面表格的上方,那么下面就由小编给大家