当前位置:首页 > 办公 > Excel教程 > 正文内容

恭喜!VLOOKUP函数重启!

酷网3个月前 (10-22)Excel教程44

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

这些工作本质上都是匹配调用:匹配同样的数据,调用出我们需要的数据。要解决这个问题,最常用到的就是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技巧都归类了,这样方便大家学习。为了整理这套资料,我牺牲了很多休息的时间,虽然辛苦,只要能帮助到大家,也就所谓了!

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40295.html

分享给朋友:

相关文章

简单2招帮你快速搞定SUMIFS函数公式中的日期条件!

简单2招帮你快速搞定SUMIFS函数公式中的日期条件!

大家都知道,SUMIFS函数是用来进行多条件求和的,它的基本语法结构为: =SUMIFS(求和区域,条件区域1,条件1,[条件区域2,条件2],…) 如下图表格,我们现在要统计销售一部2019/2/1到2019/3/31...

SUMIF函数14种经典用法,速收!

SUMIF函数14种经典用法,速收!

SUMIF函数是使用频率很高的函数,通常用于对区域中符合指定的单个条件的值求和。 她只有3个参数,看起来像清纯的少女。 =SUMIF(条件区域,指定的条件,需要求和的区域) 如果省略求和区域,会将条件区域作为求和区域。...

excel矩阵数据在工作表中绘制线条?

excel矩阵数据在工作表中绘制线条?

Q:如下所示,左侧是一个4行4列的数值矩阵,要使用VBA根据这些数值绘制右侧的图形。 绘制规则是这样的:找到最小的数值(忽略),将其与第2小的数值用点划线连接,再将第2小的数值与第3小的数值用点划线连接,依此类推,直到...

7个Excel小技巧,提高表格查看效率!

日常工作中,我们的表格中经常会有跨表引用其他表格的情况,查看表格时,就需要同时打开多个表格查看;当我们一个表格特别大时,需要对比查看前后或者左右的数据等等,这些问题在平时貌似不起眼,但是学会一些小技巧后,可以大大提升表格...

INDIRECT+ADDRESS,查找每个订单的订货型号

INDIRECT+ADDRESS,查找每个订单的订货型号

问题情境 如下样表: 每个订单号产生订货量的型号规格不同,需要把每个订单号订货的型号规格填写到G列对应型号列。 例如:第一个单号,有订货量的型号规格为1-2#,要求把1-2#写入G4单元格。 公式实现 在G4单元格输入...

怎能不会SUBTOTAL--筛选或隐藏状态下的数据统计函数

怎能不会SUBTOTAL--筛选或隐藏状态下的数据统计函数

怎能不会SUBTOTAL–筛选或隐藏状态下的数据统计函数?函数解析 语法 SUBTOTAL(function_num,ref1,[ref2],…) SUBTOTAL 函数语法具有以下参数: Fun...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。