首页 > 新闻 > 正文
excel函数与公式应用大全(Excel有哪些和vlookup一样重要的函数或功能)

 2022-08-28 07:46  阅读 8

摘要:在Excel中,Vlookup函数的基本功能就是查询引用,请看下文: 一、Vlookup函数:功能及语法结构。 功能: 在指定的数据范围内返回符合查询要求的值。 语法: =Vlookup(查询值,数据范围,

在Excel中,Vlookup函数的基本功能就是查询引用,请看下文:

一、Vlookup函数:功能及语法结构。

功能:在指定的数据范围内返回符合查询要求的值。

语法:=Vlookup(查询值,数据范围,返回值列数,匹配模式)。

其中匹配模式有两种,分别为“0”或“1”。其中“0”为精准匹配,“1”为模糊匹配。

目的:查询“商品”的“销量”。


方法:

在目标单元格中输入公式:=VLOOKUP(H3,B3:C9,2,0)。

解读:

第三个参数(返回值列数)是根据第二个参数(数据范围)来确定的,“数据范围”中的第一列为1,第二列为2……以此类推。


二、Vlookup函数:反向查询。

目的:根据“编码”查询“商品”名称。


方法:

1、在目标单元格中输入公式:=VLOOKUP(I3,IF({1,0},C3:C9,B3:B9),2,0)。

2、Ctrl+Shift+Enter填充。

解读:

公式中的IF({1,0},C3:C9,B3:B9)的作用为形成一个以C3:C9为第一列、B3:B9为第二列的临时数组。


三、Vlookup函数:多条件查询。

目的:根据“商品”名称和“型号”查询“销量”。


方法:

1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF({1,0},B3:B9&C3:C9,D3:D9),2,0)。

2、快捷键Ctrl+Shift+Enter填充。

解读:

1、当有多个查询的条件时,用连接符“&”连接在一起,对应的数据区域也用“&”连接在一起。

2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列,D3:D9为第二列的临时数组。


四、Vlookup函数:多条件反向查询。

目的:根据“商品”的销售“地区”查询对应的“销量”。


方法:

1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0)。

2、快捷键Ctrl+Shift+Enter填充。

解读:

当有多个条件和数据范围时,对应的值用符号“&”连接。


五、Vlookup函数:屏蔽错误值。

目的:无查询匹配结果时,不显示错误代码#N/A, 将单元格的置空。


方法:

在目标单元格中输入公式:=IFERROR(VLOOKUP(I3&J3,IF({1,0},B3:B9&F3:F9,D3:D9),2,0),"")。

解读:

Iferror函数的作用为:判断一个表达式是否有误,如果有误,则返回本身,否则返回指定的值;语法结构为:=Iferror(表达式,表达式有误时的返回值)。


六、Vlookup函数:批量查询。

目的:根据“商品”名称批量返回相关信息。


方法:

在目标单元格中输入公式:=VLOOKUP($I3,$B$3:$F$9,MATCH(J$2,$B$2:$F$2,0),0)。

解读:

1、巧妙利用Match函数获取返回值对应的列数。

2、注意参数的引用方式,不变为“绝对”、变为“相对”,也可以是“混合引用”。


七、Vlookup函数:一对多查询。

目的:根据对应的值返回多个查询结果。

步骤1:插入辅助列。


方法:

1、在“商品”列的前面插入“辅助列”。

2、输入公式:=COUNTIF(C$3:C3,C3)。

解读:

利用Countif函数统计“商品”在对应的区域出现的次数。


步骤2:根据“商品”名称查询对应的“型号”。


方法:

1、在目标单元格中输入公式:=IFERROR(VLOOKUP($J$3&ROW(A1),IF({1,0},C3:C9&B3:B9,D3:D9),2,0),"")。

2、快捷键Ctrl+Shift+Enter填充。

解读:

公式主要运用了“逆向查询”和“屏蔽错误值”两种主要方法。


步骤3:根据“商品”名称和“型号”查询对应的销量。


方法:

1、在目标单元格中输入公式:=IFERROR(VLOOKUP($J$3&$K3,IF({1,0},C3:C9&D3:D9,E3:E9),2,0),"")。

2、快捷键Ctrl+Shift+Enter填充。

解读:

公式主要应用了多条件的方法。



当然,除了Vlookup之外,还有Lookup等函数也可以实现查询引用的功能,


一、Lookup函数。

Lookup函数具有两种使用形式。

1、向量形式。

功能:是指在单行或单列中查询指定的值,然后返回第二个单行或单列相同位置的值。

语法结构:=Lookup(查询值,查询值所在的范围,[返回值所在的范围])。

前提条件:查询值所在范围的值,必须按照升序排序,否则无法得到正确的结果。

目的:查询销售员的销量。


方法:

1、以“销售员”为主要关键字进行升序排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:B9,D3:D9)。

解读:

1、由于Lookup函数本身的特点,在查询一用之前,必须对查询范围的值进行升序排序,否则无法得到准确的查询结果。

2、当查询范围和返回返回相同时,返回范围可以省略哦!


2、数组形式。

作用:在对应的数据源中的第一列或第一行中查找值,并返回最后一列或最后一行对应的值。

语法结构:=Lookup(查询值,查询值和返回值所在的范围)。

前提条件:查询值所在范围的值,必须按照升序排序,否则无法得到正确的结果。

目的:查询销售员的销量。


方法:

1、以“销售员”为关键字进行升序排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:D9)。

解读:

1、由于Lookup函数本身的特点,在查询一用之前,必须对查询范围的值进行升序排序,否则无法得到准确的查询结果。

2、使用数组形式时,查询值范围必须在第一列,返回值范围必须在最后一列哦!


3、单条件查询。

在实际工作中,数据源中的数据不可能按照升序有序排列,难倒就不能用Lookup函数查询引用了吗……为了解决这一难题,Lookup衍生出了“变异用法”。

目的:查询销售员的销量。


方法:

在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解读:

1、在学校Lookup函数的基础语法时,已经强调过,要向得到正确的查询结果,必须对查询值所在范围的值进行升序排序,但在“单条件”查询时,并未对查询值所在范围的值进行升序排序,而是采用了奇诡的公式,查询值为1,查询范围为0/(B3:B9=H3),Why?

2、Lookup函数的特点:当在查询范围中找不到需要查询的值时,Lookup函数就会进行匹配,原则是用小于查询值的最大值进行替代查询值。

3、当B3:B9=H3成立时,返回True,暨1,不成立时返回False,暨0,而0/0则返回错误,所已查询范围就变成了一个由0和1组成的新数组,Lookup进行向下最大值匹配,从而返回0对应位置上的值。


4、多条件查询。

目的:查询销售员在相应地区的销量。


方法:

在目标单元格中输入公式:=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),"无销量")。

解读:

多条件查询和单条件查询的原理是相同的,当两个或多个条件都成立时,返回True,暨1,否则返回False,暨0。


二、Index+Match组合函数。

1、单列查询。

目的:查询销售员对应的销量。


方法:

在目标单元格中输入公式:=INDEX(D3:D9,MATCH(H3,B3:B9,0))。

解读:

1、Index函数的作用为:返回给定的单元格区域中,行列交叉处的值或引用。语法结构:=Index(区域,行,[列])。当省略“列”时,默认为第1列。

2、Match函数的作用是:返回指定值在指定范围内的相对位置。语法结构:=Match(定位置,定位范围,匹配模式)。其中“1”为:小于;“0”为精准;“-1”为:大于。


2、多列查询。

目的:返回销售员对应的所有信息。


方法:

在目标单元格中输入信息:=INDEX($C$3:$E$9,MATCH($B$13,$B$3:$B$9,0),MATCH(C$12,$C$2:$E$2,0))。

解读:

利用Match函数定位目标值所在的行和列,然后用Index提取。


上述就是Excel中最典型的3个查询引用技巧,希望对大家有所帮助哦!

关键字: 大全 / 哪些 / 公式 / 函数 / 一样 / 应用 / 重要的 / VLOOKUP / 
抖音直播点赞震动怎么关掉点赞震动在哪设置关闭教程 抖音访客周报最关心你的好友是怎么出来的访客周报怎么关闭 毫米级精度造航天领域“跳楼机” 三星990PRO旗舰SSD固态硬盘确认支持PCIe5.0x4 抖音访客周报为什么看不了抖音访客周报怎么看为什么不更新 我国IPv6活跃用户数达6.97亿行业整体发展势头良好 部分外资银行被纳入碳减排支持工具金融机构范围 M2MacBookAir将延迟三周发货因为订单一直增长 曝抖音电商将迎来调整 运营负责人李恬离职 即将改款?特斯拉中国:刻意误导消费者的造谣行为

本站涵盖的内容、图片、视频等模板演示数据,部分未能与原作者取得联系。若涉及版权问题,请及时通知我们并提供相关证明材料,我们将及时予以删除!谢谢大家的理解与支持!


Copyright © 版权所有 | 联系方式:osnev93x5o@gmail.com