|
|
中级押题卷来了
距离中级考试还有4天，在最后考试的几天，老师给大家准备了一套押题卷，3科全部重点考题，一科6套，三科18套卷，我们只押会考的，全是抢分点，现在打包免费送给大家，扫码回复 ： 押题 即可领取↓
@注会菌
微软新发布的XLOOKUP功能真的太强大了，我们要和VLOOKUP说再见了！
突发，34岁的VLOOKUP要退出EXCEL圈了！
2019年8月28日微软发布了XLOOKUP，同时支持竖向和横向查找、从左向右、从右向左查找。
这标志着34岁的VLOOKUP将正式退出历史舞台！
XLOOKUP的面市，将不仅代替VLOOKUP，LOOKUP、HLOOKUP函数可能也将退出Excel。
本文福利资料
36套Excel财务函数用法大全.XLS
财务函数公式和数据分析的55个技巧.XLS
《45节课，财务Excel从入门到精通》
新函数 Xlookup用法.PDF+新函数 Xlookup用法.PDF
? 附200套财务报表、工资表、考勤表.XLS
没有套路只有诚意
长按识别下方二维码关注
回复 函数 即可全部免费领取
功能强大的VLOOKUP函数
做财务会计的，熟悉的Excel函数除了SUM和IF外，VLOOKUP是最常用的一个函数。
VLOOKUP被称为Excel中的效率之王，但是95%的Excel使用者都不能很好使用VLOOKUP。但是，VLOOKUP函数又是Excel中的大众情人。有平台曾经做过“如果只能选择学习Excel中的一项功能，你会选择哪个”的调查，VLOOKUP函数竟然高居第二位。
在我们的工作中，基本每天都会遇到这样的场景。比如从总表中，根据姓名匹配身份证号信息，根据考核等级确定奖金比例。
这些工作本质上都是匹配调用：匹配同样的数据，调用出我们需要的数据。要解决这个问题，最常用到的就是VLOOKUP函数。
那么VLOOKUP函数究竟如何使用呢？
VLOOKUP函数语法结构：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找范围，返回的数值所在的列数，精确匹配还是模糊匹配）。
一、VLOOKUP函数精确匹配，返回你需要的唯一数据
比如根据姓名匹配身份证号信息，对于这种匹配调用唯一的数据，就要用到VLOOKUP函数的精确匹配了。
操作步骤：查找范围为绝对引用，可按快捷键F4，精确匹配下参数为0或FALSE。
注意事项：查找范围和要返回的数值所在的列数都是要从查找值所在的列开始计算。
VLOOKUP查询调用精确匹配
二、VLOOKUP函数模糊匹配，返回你需要的区间数据
比如根据考核等级确定奖金比例，对于这种在区间范围内匹配调用数据，就要用到VLOOKUP函数的模糊匹配了，这个功能完全可以替代掉IF函数的多层嵌套，再也不用为写错顺序发愁。
操作步骤：查找范围依然为绝对引用，可按快捷键F4，模糊匹配下参数为1或TRUE。
注意事项：等级表的编制要从小到大
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、当查找的目标格式不统一时报错如何解决
（1）如果查找的目标值是文本格式，而数据区域中是数值格式。
如下图所示，A列中的员工编号为数值格式，而F3单元格中的员工编号为文本格式。
在G3单元格中输入公式：=VLOOKUP(--F3,$A$2:$D$9,4,0)，按Enter键完成。
注：--为两个负号，即减负的意思，可以理解为负负得正，这里是把文本强制转换为数值，所以问题就很容易被解决了。
（2）如果查找的目标值是数值格式，而数据区域中是文本格式。
如下图所示，A列中的员工编号为文本格式，而F3单元格中的员工编号为数值格式。
在G3单元格中输入公式：=VLOOKUP(F3&"",$A$2:$D$9,4,0)，按Enter键完成。
注：&""是强制地把数值格式转换成文本格式。
5、区域查找
有时候需要查找某一个值处于那个区间里。比如查找下列的销售额对应的销售提点为多少。在E2单元格中输入公式：=VLOOKUP(D2,$H$2:$I$8,2,1)，按Enter键完成。
注：这里使用该函数最后一个参数为1，即模糊查找，来确定查找的值处于给定的那一个区间。
6、模糊查找
VLOOKUP函数也是支持模糊查找，即支持通配符查找。
查找姓名中带有“冰”字的员工的销售额，在H3单元格中输入公式：
=VLOOKUP("*"&G3&"*",$B$2:$D$9,3,0)，按Enter键完成。
注：如果要查找以“冰”开头的那么公式的第一参数为："*"&G3; 如果查找以“冰”结尾那么公式的第一个参数为：G3&"*".
7、查找顺序与数据区域中顺序一致的多项时
VLOOKUP函数查找顺序一致的多项时，可以借助COLUMN函数构建查找序列。
在H2单元格中输入公式：=VLOOKUP($G2,$A$2:$D$9,COLUMN(B1),0)，按Enter键后向右填充。
注：COLUMN函数是返回列号。第一个参数一定要锁定列号，这样才能正确的结果。
8、十字交叉查询
VLOOKUP函数如果有两个条件是呈现十字交叉时且顺序与数据区域中的顺序不一致时，可以与MATCH函数完成查询。
在H2单元格中输入公式：=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter键完成后向下向右填充。
注：一定要锁定VLOOKUP函数的第一个参数的列号，MATCH函数的第一个参数的行号，这样才能得到正确的结果。
9、多条件查询
VLOOKUP还能进行多条件查询，这个用法相信有很多人不知道吧。
在I2单元格中输入公式：
{=VLOOKUP(G2&H2,IF({1,0},$A$2:$A$9&$B$2:$B$9,$D$2:$D$9),2,0)}
按组合键<Ctrl+Shift+Enter>完成后向下填充。
注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。VLOOKUP的第三个参数为2，第四个参数为0是固定的。
10、反向查找
VLOOKUP函数也可以进行反向查找。
在H2单元格中输入公式：{=VLOOKUP(G2,IF({1,0},$B$2:$B$9,$A$2:$A$9),2,0)}，按组合键<Ctrl+Shift+Enter>键完成后向下填充。
注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。
11、一对多查询
VLOOKUP函数还能进行一对多查询，但是这个方法并不鼓励大家去使用。
在H2单元格中输入公式：
{=VLOOKUP($G$2&ROW(A1),IF({1,0},$A$2:$A$9&COUNTIF(INDIRECT("a2:a"&ROW($2:$9)),$G$2),$D$2:$D$9),2,0)}，按组合键<Ctrl+Shift+Enter>完向下填充。
注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。
以上就是VLOOKUP的主要应用场景总结。那么，问题来了！利用率如此之高的函数为什么会退休？
本文福利资料
36套Excel财务函数用法大全.XLS
财务函数公式和数据分析的55个技巧.XLS
《45节课，财务Excel从入门到精通》
新函数 Xlookup用法.PDF+新函数 Xlookup用法.PDF
? 附200套财务报表、工资表、考勤表.XLS
没有套路只有诚意
长按识别下方二维码关注
回复 函数 即可全部免费领取
XLOOKUP比VLOOKUP好在哪？
在微软的官方的介绍中，XLOOKUP的功能是这样的：
从右向左查
多条件查找
从上向下查
查找最后一个
.......
（图片来源于秋叶Excel）
从这张图中可以看出XLOOKUP拥有着其它函数无可比拟的优势，当需要在表格或区域中按行查找项目时, 就可以使用XLOOKUP函数。
XLOOKUP广泛应用后，对于我们财务而言，将可以少学不少很难的数组公式，VLOOKUP的函数的各种用法也不用学了！
根据官方的的解释，可以简单理解为：XLOOKUP 可以按照行或者列进行查询，并返回对应的结果。
语法：
XLOOKUP 函数搜索区域或数组, 并返回与它找到的第一个匹配项相对应的项。如果不存在匹配项, 则 XLOOKUP 可以返回最接近 (近似) 匹配。
看着有些复杂，简单解释就是：
注意，参数一共有5个，如果后两个省略，那么就是精确匹配！
目前该函数只有部分Office 365预览版用户才能使用。
XLOOKUP到底能怎么用？
也许还是有很多人看着糊涂，我们接下来以具体的案例来说明，看看XLOOKUP到底在什么场景下能应用，以及它的强大功能！
一、单条件精确匹配：查找注会菌会计的分数
=Xlookup(A11,A2:A8,B2:B8)
二、反向精确匹配：从右到左找到注会菌的准考证号码
=XLOOKUP(A11,B1:B8,A1:A8)
VLOOKUP一般只能从左向右查找，虽然可以实现，但是会复杂很多！用VLOOKUP的公式如下：
=VLOOKUP(A11,IF({1,0},B1:B8,A1:A8),2,FALSE)
简单地说，XLOOKUP就是把INDEX+MATCH整合在了一起。
三、多条件匹配：查找注会菌的审计分数
=XLOOKUP(A11&B11,A1:A8&B1:B8,D1:D8)
多项查找也方便了很多。
四、匹配最后一个：查找注会菌最后一次会计模拟考试成绩
=XLOOKUP(A14,A2:A11,B2:B11,0,-1)
针对有多个结果，有时候往往需要匹配最近的一条数据。这里只需要将第5参数改为-1，就能从后向前查了，一般默认为1，是从前向后查找。
如果用VLOOKUP来查找最后一个就非常复杂了，一般可以用LOOKUP构造数据。
五、横向精确匹配：按行上下查找注会菌的会计分数
XLOOKUP用横向精确查找非常简单，因为这个函数没有横向和纵向的区别。
但是，如果用VLOOKUP就不简单了，可以用HLOOKUP代替或者INDEX+MATCH。
以上就是XLOOKUP的一部分功能，总的来说，它整合了VLOOKUP，HLOOKUP，以及INDEX+MATCH的功能，可以说非常强大。不过注会菌还是要提醒一下大家，目前该函数只有部分Office 365预览版用户才能使用，还没有全面开放，我们可以一起期待一下。
有人说财务工资的高低和Excel技能的熟练程度有很大的关系，希望今天这些内容能帮助到大家，想看更多干货内容，猛戳右下角“在看”，你们懂的
！
本文福利资料
36套Excel财务函数用法大全.XLS
财务函数公式和数据分析的55个技巧.XLS
《45节课，财务Excel从入门到精通》
新函数 Xlookup用法.PDF+新函数 Xlookup用法.PDF
? 附200套财务报表、工资表、考勤表.XLS
没有套路只有诚意
长按识别下方二维码关注
回复 函数 即可全部免费领取
今日重磅福利
免费领取新版CPA必备教材
▎本文由注册会计师独家整理，编辑Tony，部分素材来源于24财务excel，Excel精英培训。若需引用或转载，需申请授权。
|
|
发表于 2019-9-4 09:37:51
