在工作中,不会Vlookup函数公式,那是打不了工的,
天天要用到,但是学起来难,用起来难,各种不同的查找匹配方式,要搭配各种不同的公式才能实现。
1、回顾Vlookup
举个例子,左边是员工信息表,现在需要查找匹配工资数据
这个很简单,我们只需要输入公式:
=VLOOKUP(F2,B:D,3,0)
1、查找值是F2单元格
2、查找数据是B到D列(VLOOKUP公式默认从选中数据源的第1列开始查找,员工姓名在B列,所以引用数据必须从B列开始)
3、结果列在数据源的第3列
4、0为精确查找
但是如果VLOOKUP遇到从右向左查找,瞬间你又不会了
2、新公式XLOOKUP
Excel新版本里面,推出了XLOOKUP公式,用起来非常简单,公式用法
=XLOOKUP(查找值,查找列,结果列)
所以没有什么从左向右查找,还是从右向左查找
查找值是F2单元格,查找列是B列,结果列是A列
所以我们只需要输入公式:
=XLOOKUP(F2,B:B,A:A)
是不是非常简单易理解
3、新公式FILTER
同样新出的FILTER筛选公式,用法是:
=FILTER(结果列,查找列=查找值)
所以上述查找匹配,我们只需要输入公式:
=FILTER(A:A,B:B=F2)
新公式是只要输入完公式,就能理解是什么意思,而且容易学会,快快用起来吧!
XLOOKUP语法
XLOOKUP 函数搜索区域或数组,然后返回对应于它找到的第一个匹配项的项。 如果不存在匹配项,则 XLOOKUP 可以返回最接近 (匹配) 值。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
参数 | 说明 |
---|
lookup_value 必需* | 查找值 *如果省略,则使用空白单元格进行匹配。 注意: 空字符串与空白单元格不同,无效 用于lookup_value,并生成错误。 |
lookup_array 必需 | 要搜索的数组或区域 |
return_array 必需 | 要返回的数组或区域 |
[if_not_found] 可选 | 如果找不到有效匹配项,则返回你if_not_found的 [if_not_found] 文本。 如果未找到有效匹配项,并且缺少 [if_not_found],则#N/A。 |
[match_mode] 可选 | 指定匹配类型: 0 - 完全匹配。 如果未找到,则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到,则返回下一个较小的项。 1 - 完全匹配。 如果没有找到,则返回下一个较大的项。 2 - 通配符匹配,其中 *, ? 和 ~ 有特殊含义。 |
[search_mode] 可选 | 指定要使用的搜索模式: 1 - 从第一项开始执行搜索。 这是默认选项。 -1 - 从最后一项开始执行反向搜索。 2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。 -2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。 |
示例
示例 1 使用 XLOOKUP 查找区域中的国家/地区名称,然后返回其电话国家/地区代码。 它包括 lookup_value (F2) 、lookup_array (区域 B2:B11) 和 return_array (区域 D2:D11 ) 参数。 它不包括 match_mode 参数, 因为 XLOOKUP 默认生成完全匹配项。
注意: XLOOKUP 使用查找数组和返回数组,而 VLOOKUP 使用单个表数组,后跟列索引号。 在这种情况下,等效的 VLOOKUP 公式为 :=VLOOKUP (F2,B2:D11,3,FALSE)
———————————————————————————
示例 2 基于员工 ID 号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回包含多个项的数组,因此单个公式可以从单元格 C5:D14 返回员工姓名和部门。
———————————————————————————
示例 3 将if_not_found参数添加到上一个示例。
———————————————————————————
示例 4 在 C 列中查找单元格 E2 中输入的个人收入,在列 B 中查找匹配的税率。它将 if_not_found 参数 (0( 如果) 找不到任何值)。 match_mode 参数 设置为 1,这意味着函数将查找完全匹配项,如果找不到匹配项,则返回下一个较大的项。 最后 ,search_mode 参数设置为 1,这意味着该函数将搜索第一个项到最后一个项。
注意: XARRAY lookup_array列 位于列 return_array右侧, 而 VLOOKUP 只能从左到右查找。
———————————————————————————
示例 5 使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 它首先在 B 列中查找 " 总利润",然后在表的首行中查找第 1 季度 (区域 C5:F5) ,最后返回两者交叉处的值。 这类似于同时使用 INDEX 和 MATCH 函数。
提示: 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。
注意: 单元格 D3:F3 中的公式为:=XLOOKUP (D2,$B 6:$B 17,XLOOKUP ($C 3,$C 5:$G 5,$C 6:$G 17) ) 。
———————————————————————————
示例 6 使用 SUM 函数和两个嵌套的 XLOOKUP 函数对两个范围之间的所有值求和。 在这种情况下,我们要对香蕉、香蕉和包括梨的值求和,这两者之间是一对。
单元格 E3 中的公式为:=SUM (XLOOKUP (B3,B6:B10,E6:E10) :XLOOKUP (C3,B6:B10,E6:E10) )
它如何工作? XLOOKUP 返回一个范围,因此在计算时,公式最终如下所示:=SUM ($E$7:$E $9) 。 通过选择具有类似于此公式的 XLOOKUP 公式的单元格,然后选择"公式>公式审核">"计算公式",然后选择"计算"以逐步执行计算,可自行了解其工作方式。
Excel中最强大的函数之一FILTER筛选函数重要用法实例讲解!
FILTER筛选函数,它可以根据筛选条件筛选出指定的数据记录,它的结果返回的是一个动态数组,Office365和新版的WPS都支持这个新出的函数,两个软件的区别是:Office支持动态溢出,而WPS的动态溢出需要配合数组三键。由于WPS是免费的,为了方便大家学习,使用WPS给大家作讲解。
FILTER筛选函数的语法是=FILTER(要筛选的数据区域,筛选条件,[找不到结果返回的值]),只有三个参数,第三参数可省略,需要特别注意的是,第二参数的高度或宽度要与第一参数的高度或宽度一致,否则结果出错。
1.筛选出符合条件的一个值
如图1所示,要筛选出学生“诸葛亮”的信息,用FILTER函数,操作步骤如下:首先鼠标选中F2:I2这个区域,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A1:D16,A1:A16="诸葛亮"),最后同时按住Shift+Ctrl+Enter三键,那么关于学生“诸葛亮”的信息会全部显示出来。
图1
2.筛选出符合条件的多个值
如图2所示,我们要筛选出所有“初二二班”的学生信息,首先我们用鼠标在空白地方选中足够多的数据区域(因为WPS不支持动态溢出,所以需要手动选择溢出后的数据区域,这个区域一定要足够大,否则符合条件的数据无法全部显示出来,根据源数据可以看出,字段信息为4列,那么我们选择的时候可以选择大于或等于4列的区域,至于行数可根据实际情况),本案例选择F2:I16,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A1:D16,C1:C16="初二二班"),最后同时按住Shift+Ctrl+Enter三键,那么关于学生“初二二班”的信息会全部显示出来。(多出的区域会显示为错误值#N/A,这个不影响结果可以不管它)
图2
3.筛选出符合条件的多个值——“且”关系
如图3所示,要筛选出班级为“初二二班”且“成绩大于75”的所有符合条件的记录。首先我们用鼠标选中区域F5:I16,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A1:D16,(C1:C16=F2)*(D1:D16>75)),最后同时按住Shift+Ctrl+Enter三键。用星号*连接两个判断条件,表示“且”关系,意味着多个条件都满足。
图3
4.筛选出符合条件的多个值——“或”关系
如图4所示,要筛选出班级为“初二二班”或“成绩大于75”的所有符合条件的记录。首先我们用鼠标选中区域F5:I18,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A2:D16,(C2:C16=F2)+(D2:D16>75)),最后同时按住Shift+Ctrl+Enter三键。用加号+连接两个判断条件,表示“或”关系,意味着满足其中任意一个条件。
图4
5.查询数据记录
如图5所示,根据姓名查询对应的成绩,选中数据区域D2:E2,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A1:B16,A1:A16="孙权"),最后同时按住Shift+Ctrl+Enter三键。注意,应用FILTER查询数据,查询值与结果值之间不允许有其他的字段列数据。
图5
6.不符合筛选条件返回特定值
以上案例,FILTER函数的第三参数都没有设置,在特定的筛选条件下查找不到结果,可以利用第三参数返回我们自行设定的值。如图6所示,选中数据区域D2:E2,然后用键盘输入一个等于号“=”,再输入以下公式:FILTER(A1:B16,A1:A16="小包","查无此人"),最后同时按住Shift+Ctrl+Enter三键。“小包”在原数据中不存在,不存在时,第三参数设置为“查无此人”,最后的结果就会显示为“查无此人”。
图6
7.筛选出符合条件的最大值/最小值
FILTER函数可以搭配LARGE或SMALL函数筛选出符合条件的最大值或最小值。如图7所示,要查找出大于75且小于90的最高成绩,其公式为:=LARGE(FILTER(B1:B16,(B1:B16>75)*(B1:B16<90)),1),直接按回车键就可以,无需使用数组三键。若查找出大于75且小于90的最小成绩,将公式中的LARGE换为SMALL即可。
图7
8.统计符合条件的数据之和
如图8所示,统计大于75且小于90的总成绩,其公式为:=SUM(FILTER(B1:B16,(B1:B16>75)*(B1:B16<90))),直接按回车键就可以,无需使用数组三键。
图8
9.统计符合条件的数据个数
如图9所示,统计大于75且小于90的总人数,其公式为:=COUNTA(FILTER(B1:B16,(B1:B16>75)*(B1:B16<90))),直接按回车键就可以,无需使用数组三键。
图9
FILTER 函数
一、函数介绍
FILTER 函数通常用来根据条件筛选数据。可以理解成一个过滤器,留下我们需要的数据,过滤掉不需要的。
二、函数解读
三、操作步骤
使用 FILTER 函数
例如:当需要筛选出颜色为红色,并且口味为甜的水果时。
选中单元格,在单元格内输入 =FILTER,或者点击工具栏中的 公式 按钮,选择 数据引用,然后选择 数据引用 中的 FILTER 函数 。
在单元格中输入公式
公式参数一: =FILTER(A1:C9,B1:B9=B2,C1:C9=C3)
公式参数二:=FILTER(A1:C9,B1:B9="红色",C1:C9="甜")
当表格中作为筛选条件的数据有可能随时被修改,但是筛选条件本身为固定的值、不会改变时,推荐使用公式二。
按 回车 键,单元格中将显示判断的结果 苹果 4 和 苹果 7 这两排。
删除 FILTER 函数
选中应用了 FILTER 函数的单元格,按删除键,清空单元格内的函数公式。
四、场景实践
作为产品经理,当面对大量的不同型号的产品时,可以使用 FILTER 函数提取所需要的信息