首页 > Excel专区 > Excel函数 >

恭喜!VLOOKUP函数重启!

Excel函数 2021-12-27 21:31:03

在我们的工作中,基本每天都会遇到这样的场景。比如从总表中,根据姓名匹配身份证号信息,根据考核等级确定奖金比例。

这些工作本质上都是匹配调用:匹配同样的数据,调用出我们需要的数据。要解决这个问题,最常用到的就是VLOOKUP函数。

那么VLOOKUP函数究竟如何使用呢?

VLOOKUP函数语法结构:VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP(查找值,查找范围,返回的数值所在的列数,精确匹配还是模糊匹配)。

说清楚大方向之后,我们来分享一下VLOOKUP的几个常规操作方法:

1、常规查找

查找姓名对应的销售额。在F3单元格中输入公式=VLOOKUP(E3,$A$2:$C$9,3,0),按Enter键完成。如下图所示:

 

2、日期查找

在查找日期的时候查找的结果通常会是一串数字,为了使日期能够返回相应的格式,那么需要配合TEXT函数才能完成查找需求。

在F3单元格中输入公式=TEXT(VLOOKUP(E3,$A$2:$C$9,2,0),”yyyy/m/d”),按Enter键完成。如下图所示:

 

注:如返回格式为2018/12/03,则TEXT的第二个参数的格式可以设置为“yyyy/mm/dd”即可。

3、查找的值为空时

在当查找的值为空时,通常情况下会返回结果为0,那么如果让结果返回空白呢,解决的方法就是在公式后面一个“”。

在F3单元格中输入公式=VLOOKUP(E3,$A$2:$C$9,3,0)&””,按Enter键完成。如下图所示:

 

4、当查找的目标格式不统一时报错如何解决

5、区域查找

6、模糊查找

7、查找顺序与数据区域中顺序一致的多项时

8、十字交叉查询

9、多条件查询

10、反向查找

11、一对多查询

……

以上就是VLOOKUP的主要应用场景总结。看下来之后,是不是感觉自己之前完全不懂VLOOKUP函数?

 

财务最常用的Excel函数公式

公式用得好,下班肯定早!很多复杂的做法,用一个简单的公式就可以解决,下面注会菌给大家分享几个常用的公式:

一、按条件求和

如下图所示,要统计不同门店的销售额。F3单元格公式为:

=SUMIF($B$2:$B$12,E3,$C$2:$C$12)

 

SUMIF函数常规用法为:

=SUMIF(条件区域,求和条件,求和区域)

如果条件区域中的内容符合指定的求和条件,就对对应的求和区域进行汇总。

二、忽略错误值求和

如下图所示,B列数据中有部分错误值,使用以下公式,可以对B列数据进行求和。

=SUMIF(C2:C12,”<9e307″

9e307,就是9*10^37,是一个非常大的数值。

SUMIF函数省略求和区域时,会自动对求和区域进行汇总。

本例的求和条件使用”<9e307″,就是对条件区域中,小于这个最大值的所有数值进行汇总,并且SUMIF函数会自动忽略错误值哦。

三、错列求和

像下图这样的表格形式,大家一定不陌生吧。要在这样的表格中按指定条件进行汇总,需要什么公式呢?

=SUMIF(A:E,H3,B:F)

SUMIF函数的条件区域使用A:E的整列引用,求和区域使用B:F的整列引用,如果A:E等于指定的店铺,就对B:F对应的内容进行求和。

四、使用通配符求和

除了按精确条件进行求和汇总,还可以根据部分关键字进行汇总。

如下图,要根据D列的商品名称关键字,来统计对应的销售数量。

=SUMIF($A$2:$A$11,”*”&D2&”*”,$B$2:$B$11)

公式中的求和条件使用”*”&D2&”*”,也就是在D列商品名称前后各连接上一个星号*。 星号在这里的作用是通配符,就是A列中只要包含关键字,就会对B列对应的数量进行汇总。

五、按条件计数

六、小于1000的业务笔数

七、判断销售额是否达标

八、多条件判断销售状况

Excel里的小技巧,你都知道吗?

此外,Excel中还有一些小技巧,很多人都不知道,但是他们能解决工作中很多实际问题,我们一起来看下:

1、快速定位循环引用单元格

如果某个文件打开时,出现了下面这样的提示,你要怎么办呢?

相信很多小伙伴在公式编写时都遇到过类似的情况,其实,咱们可以快速找到出问题的单元格,然后修改公式就可以了。

2、公式中的引号分不清

编写公式时出现了这样的提示,你觉得会是啥原因?

Excel 2016和Excel 2019的默认字体是“正文字体”,如果使用默认字体时,双引号到底是半角还是全角,在编辑栏中很难识别出来,像图中这个公式,就是不留神使用了全角双引号,所以Excel无法识别了。

怎么办呢?只要把默认字体换成宋体,就很容易区分了。

设置完成后,重启一下Excel,再来看看就一目了然了:

3、断开与其他工作簿的数据链接

如果使用函数引用了其他工作簿中的数据,文件打开时就会有这样一个提示对话框:

如果这样的文件直接发给领导,那可不怎么好。

其实在文档发给其他人员之前,可以先建立一个副本,然后把要给其他人员的这份文件断开链接就好了

4、快速清除不可见字符

5、数字无故变金额

6、快速复制格式给其他图表

7、批注快速复位

……

Excel里有太多的小技巧,受限于篇幅,这里就不一一展示了!

这些技巧全部记住也不太现实,幸好我们将财务常用到的一些Excel技巧都归类了,这样方便大家学习。为了整理这套资料,我牺牲了很多休息的时间,虽然辛苦,只要能帮助到大家,也就所谓了!


标签: SUMsumifsumifssumif函数excel函数

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.