你会用查找函数吗
哈哈,今天学习的vlookup是我们都比较熟悉的函数啦,用的多才能更了解它的特性,而我最最想要了解的是index和match,因为之前对他们不熟,看实例也觉得很绕,但今天学起来比较轻松,因为视频讲解的很清楚,再加上里面的案例和试题自己都操作了,然后顺带了解了offset的使用,终于将之前请教的问题搞得比较清楚了,那么我之前请教的问题是什么呢?
问题:
1、如何根据团队、区域、业绩去核算业绩达到的标准是不及格、及格、良好、优秀、超越?
2、如何根据业绩达到的标准去核算提点?(问题1的升级版)
具体请看下图,有兴趣的小伙伴可以试试哦!
请教题好了,开始我们今天的学习内容。
一、基本用法(vlookup、hlookup、index、match)
1、vlookup
语法:vlookup(lookup_value,table_array,col_index_num,[range_lookup])
理解:vlookup(要查找的值,查找值所在的区域,查找第几列,精确还是模糊查找)
vlookup-实例2、match
语法:match(lookup_value, lookup_array, match_type)
理解:match(查找的值,查找的区域或数组,小于、精确、大于)
作用:在一个区域或数组中查找指定数值的位置,也就是查找的值所在哪一行或哪一列。
注意:其要查找的区域必须是一维数据而且是连续的,即只能包含一行或一列数据
大家有没有发现,match其实跟vlookup的语法很类似,记得时候可以一起记忆,毕竟vlookup我们还是用的比较多的。
match-实例3、index
语法:index(array,row_num,column_num)
理解:index(指定区域,查找值所在行数,查找值所在的列数)
作用:根据指定的行数和列数,返回指定区域的值。(行列交叉的值)
一般与match搭配使用
index-实例4、offset(补充)
语法:offset(reference,rows,cols,[height],[width])
理解:offset(参考位置,往上下移动几行,往右移动几列,[返回后显示几行],[返回后显示几列]),行列高度宽度移动都是根据参考位置来的,参考位置可以是一个单元格,也可以是一个区域,如果是区域,就以区域的形式移动,返回需使用数组的形式。
=offset(b2,1,2,1,1)
=offset(a1:b2,3,2,2,1),以ctrl+shift+enter键结束
二、借助名称框查找
=VLOOKUP(D2,查找区域,2,0)
将查找的区域定义名称,在引用数据区域的时候,不需选取区域,直接输入已经定义好的名称即可,也可用F3调取出来,碰到数据较多,表格不在同一张表,函数又比较复杂的时候特别方便。
名称框三、通配符模糊查找
“*”通配任意个字符
“?”通配单个字符
=VLOOKUP("*老师*",$A$2:$B$12,2,0)
通配符四、查找返回多列数据
这个是利用column和row,也可以用match来实现,但column和row只能引用单元格或单元格区域,不能引用公式返回的结果,所以match刚好可以用来弥补。
=VLOOKUP($G2,$A$2:$E$9,COLUMN(B1),0)
=HLOOKUP(H$1,$A$1:$E$9,MATCH($G2,$A$1:$A$9,0),0)
查找返回多列数据五、查找指定区域
要求:左侧表格是数据源区域,包含姓名,1月,2月,3月,4月数据,需要在右侧根据姓名调取对应的数据,月份利用下拉列表形式,所以这里面就用column不能实现,需用到match。
=VLOOKUP($G2,$A$2:$E$9,MATCH($H$1,$A$1:$E$1,0),0)
查找指定区域六、多条件查找
要求:左表为产品销售额情况(产品列包含重复的产品),要求在F:H列的蓝色区域根据F2的产品名称查找所有销售情况。
难点:数据源区域中产品有多种,也就是需要找出1对多的信息。
这个在前面学习countif中也学习过,在最左侧添加辅助列用countif将产品编序(A-A1)
方法1:=IFERROR(INDEX($B$2:$E$15,MATCH($G$2&ROW(A1),$B$2:$B$15,0),MATCH(G$8,$B$1:$E$1,0)),"")
方法2:=IFERROR(VLOOKUP($G$2&ROW(A1),$B$2:$E$15,MATCH(J$8,$B$1:$E$1,0),0),"")
方法3:=IFERROR(HLOOKUP(M$8,$B$1:$E$15,MATCH($G$2&ROW(A1),$B$1:$B$15,0),0),"")
方法4:=IFERROR(VLOOKUP($G$2&ROW(A1),$B$2:$E$15,COLUMN(B1),0),"")
方法5:=IFERROR(OFFSET(INDEX($B$2:$B$15,MATCH($G$2&ROW(A1),$B$2:$B$15,0),1),0,1,1,3),""),按ctrl+shift+enter结束
一对多查找七、区间查询
要求:需要按照等级划分规则,将成绩划分到其对应的等级中
=VLOOKUP(B2,$J$2:$K$5,2)
=LOOKUP(B2,$J$2:$J$5,$K$2:$K$5)
注意引用的区域,其区间是按照升序排列的
区间查询八、动态图表
预先设置好查找的值,利用下拉列表实现多行数据变动。
动态图表