2019年12月

VLOOKUP基础用法
VLOOKUP函数是常用的一个内容查找函数,用于通过某一条件查询数据源中需要的内容。

语法:=VLOOKUP(查询值,数据源,显示序列,匹配参数)

以通过代码查询材料名称为例,录入公式:

=VLOOKUP(F2,B1:D9,2,0)
01.png

大家会发现,VLOOKUP函数的前三个参数都是一一关联的。

查找依据决定查找范围的起点;

而查找范围的选择又决定了显示序列。

模糊匹配
VLOOKUP函数第四个参数有两种:FALSE/0为精确查找,TRUE/1为模糊匹配,在工作中大部分场景下所应用的都是精确查找,那什么情况下会用到模糊匹配呢?

先来看应用场景,根据不同金额计算提成比例:
02.png

按照以往的思维模式,大家可能会选择通过IF函数的多层嵌套来实现比例的计算:

=IF(B2>=20000,12%,IF(B2>=15000,9%,IF(B2>=10000,7%,IF(B2>=5000,5%,3%))))*B2

是不是括号都不太能数的清?而一旦提成比例选项越多,公式就越复杂,这时VLOOKUP函数的模糊匹配就能完美解决区间匹配的问题!

来看第四个参数的区别:

1、FALSE/0:精确查找,必须有一个对应的值存在,否则显示#N/A
03.png

2、TRUE/1:模糊匹配,如果找不到精确匹配值,则返回小于查询值的最大数值
04.png

因此,VLOOKUP函数的模糊匹配可以实现区间查找,在一定程度上可以替代IF函数的多层嵌套。
05.gif

注意:由于模糊匹配时,如果找不到精确匹配值则返回小于查询值的最大数值,因此要求数据源第一列中的值必须以升序排序。

原文链接:https://blog.csdn.net/happy_leon/article/details/80919069

特别提醒,是lookup函数,不是vlookup函数!

1、普通正向查询

公式:=LOOKUP(1,0/($B$2:$B$7=A11),$D$2:$D$7)

万能套用:=lookup(1,0/(查找值=查找列),结果列)
01.jpeg

2、逆向查询

公式:=LOOKUP(1,0/(A11=$B$2:$B$7),$A$2:$A$7)

其实LOOKUP函数不分正逆向,万能套用是一样的

万能套用:=lookup(1,0/(查找值=查找列),结果列)
02.jpeg

3、多条件查询

公式:=LOOKUP(1,0/((B1:B8=A12)*(D1:D8=B12)),A1:A8)

万能套用:=lookup(1,0/(条件1=查找列1)*(条件2=查找列2),结果列)
03.jpeg

4、模糊查找匹配

公式:=LOOKUP(B2,$G$2:$G$7,$F$2:$F$7)

万能套用:=lookup(查找值,查找列,结果列)
04.jpeg

5、查找最后一条记录

公式:=LOOKUP(1,0/($B$2:$B$10=A14),$C$2:$C$10)

万能套用:=lookup(1,0/(查找值=查找列),结果列)
05.jpeg

6、提取末尾的数字

公式:=LOOKUP(9^9,RIGHT(A2,ROW($1:$6))*1)

万能套用:=lookup(9^9,right(查找值,row(1:最大数字是几位))*1)
06.jpeg

7、提取开头的数字

公式:=LOOKUP(9^9,LEFT(A6,ROW($1:$6))*1)

万能套用:=lookup(9^9,left(查找值,row(1:最大数字是几位))*1)
07.jpeg

8、提取中间的数字

公式:=LOOKUP(9^9,MID(A8,MATCH(1,MID(A8,ROW(1:6),1)^0,0),ROW(1:6))*1)

输入完按CTRL+SHIFT+ENTER

万能套用的公式是:A8是查找值,6是最大位数,往里面套用

9、查找一列中最后一个

查找最后一个文本:LOOKUP("座",A:A)

查找最后一个数字:LOOKUP(9E+307,A:A)

查找最后一个文本或数字:LOOKUP(1,0/(A:A<>""),A:A)

万能套用:将A:A改成需要查找的列
08.jpeg

10、通过简称查找全称:

公式:=LOOKUP(9^9,FIND(A9,$A$1:$A$5),$B$1:$B$5)

万能套用:=lookup(9^9,find(查找值,查找列),结果列)
09.jpeg

11、通过全称查找简称

公式:=LOOKUP(9^9,FIND($A$1:$A$5,A9),$B$1:$B$5)

万能套用:=lookup(9^9,find(查找列,查找值),结果列)
10.jpeg

能理解就好,如果不能理解的话,那就收藏,下次碰到类似的要求的,直接套用公式即可。
本文转自百度百家号:Excel自学成才

0ABCD EFGH
1姓名金额是否支付 统计合计
2张三100 张三312
3李四200 李四321
4张三200否        
5李四300否      
6李四200是     
7张三200否      

countif(列表,条件)。求列表中符合条件的单元格的数量。
countifs(列表1,条件1,列表2,条件2,……)。求列表1中符合条件1,且对应列表2中符合条件2,……的数量。

F2填写:=COUNTIF(A:A,E2)、G2填写: =COUNTIFS(A:A,E2,C:C,G$1)。
说明:这里在填写G1条件时加入了一个“$”符合,表示固定$符号后面的数字1不变,作用是,方便后续自动填充下面的单元格时,所选的条件单元格始终是G1不变。

0ABCD EFGH
1姓名金额是否支付 统计合计
2张三100 张三500100400
3李四200 李四700400300
4张三200否        
5李四300否      
6李四200是     
7张三200否      

sumif(姓名列表,姓名,金额列表)。求金额列表中,对应姓名列表里所有姓名等于姓名的金额的加和。
sumifs(数值列表,条件列表1,条件1,条件列表2,条件2,……)。求数值列表中,条件列表1中条件1,条件列表2中条件2对应数值的加和。

比如:F2填写:=SUMIF(B:B,E2,C:C)、G2填写:=SUMIFS(C:C,B:B,E2,D:D,G$1)
说明:这里在填写G1条件时加入了一个“$”符合,表示固定$符号后面的数字1不变,作用是,方便后续自动填充下面的单元格时,所选的条件单元格始终是G1不变。