当前位置:首页 > 技巧 > Excel教程 > 正文内容

恭喜!VLOOKUP函数重启!

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

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

这些工作本质上都是匹配调用:匹配同样的数据,调用出我们需要的数据。要解决这个问题,最常用到的就是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

分享给朋友:

相关文章

如何使用Excel修改证件照底色?

如何使用Excel修改证件照底色?

  在日常生活当中,不管是因为考试还是因为投简历,不管是学生党还是上班族,都会有用到自己证件照的时候,大多情况下,准备好白蓝红三种底色就足以应对。去照相馆修改底色还需要花钱,今天就给大...

Excel怎么打印出网格线?Excel打印网格线的方法

Excel怎么打印出网格线?Excel打印网格线的方法

  很多时候,我们都使用Excel表格来统计数据,统计完成后,有些是需要打印的,不过小编却发现打印出来的表格没有网格线,那么Excel该怎么打印出网格线呢?别着急,接下来由小编来教大家...

Excel如何设置分页符?Excel分页符怎么删除?

Excel如何设置分页符?Excel分页符怎么删除?

  Excel分页符怎么删除?分页符的线框是蓝色的,而且比较粗大,如果我们设置了分页符,就会变的很难看,不过打印时不受影响,若是需要删除Excel分页符这需要将光标定位在最后一个象限的...

怎么用EXCEL表格计算应付利息

怎么用EXCEL表格计算应付利息

本文将教你如何在EXCEL表格中创建一个利息支付计算器。Windows版本和MAC版本的EXCEL都可以。 步骤 打开EXCEL软件。双击EXCEL应用程序图标,该图标在深绿色背景上有个白色类似“X”的字母。 单击空白...

怎么将XML转换成Excel文件

怎么将XML转换成Excel文件

本指南介绍如何在Windows或macOS上将XML文件导入Microsoft Excel工作簿。 步骤 方法 1Windows 打开Microsoft Excel。Excel位于Windows菜单的所有应用区域中的“...

求包含文本内容的单元格中的数字之和

求包含文本内容的单元格中的数字之和

单元格中的数据包含文本和数字(如),如何使用公式求出该单元格中的数字之和? 先不看答案,自已动手试一试。 公式思路 先将文本屏蔽,然后求数字之和。 公式 数组公式如下: =SUM(IFERROR(1*MID(A1,RO...

发表评论

访客

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