精华内容
下载资源
问答
  • excel函数交叉定位查找内容+根据内容查找行列号(反向查找) 1、excel函数交叉定位查找内容 1)lookup+match 函数 2)offset +match 函数 3)index +match 函数 2、根据内容查找单元格位置 1)获取...

    excel函数交叉定位查找内容+根据内容查找行列号(反向查找)

    1、excel函数交叉定位查找内容

    在这里插入图片描述

    在这里插入图片描述
    1)lookup+match 函数
    在这里插入图片描述
    2)offset +match 函数
    在这里插入图片描述
    3)index +match 函数
    在这里插入图片描述

    2、根据内容查找单元格位置(若存在两个以上相同单元格无法实现,可以加个count计数,确保唯一性)

    1)获取行在这里插入图片描述
    2)获取列
    在这里插入图片描述
    3)获取位置在这里插入图片描述
    4)结果
    在这里插入图片描述

    展开全文
  • VLOOKUP正向的应用介绍资料较多,简单回顾一下各参数如下: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数 说明 输入参数类型 ...要查找的值 ...返回数据在查找区域的第几...

    VLOOKUP正向的应用介绍资料较多,简单回顾一下各参数如下:
    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    参数 说明 输入参数类型
    lookup_value 要查找的值 数值、引用或文本字符串
    table_array 要查找的区域 数据表区域
    col_index_num 返回数据在查找区域的第几列数 正整数
    range_lookup 模糊匹配/精确匹配 TRUE/FALSE(或不填)

    VLOOKUP 逆向查找技巧
    以查找替代料号对应的原料号为例,如下图公式
    =VLOOKUP(F2,IF({1,0},C2:C34,A2:A34),2,0)
    在这里插入图片描述
    其中
    第二个参数–查找区域是IF({1,0},D2:D19,B2:B19)的返回结果。
    IF函数条件为真时返回第一个值,条件为假返回第二值。
    查IF函数使用帮助,它不仅可以返回一个值,还可以返回一个区域引用。
    我们将IF({1,0},D2:D19,B2:B19)拆解为如下两条公式,也就好理解了:
    下面展示一些 内联代码片

    IF(1,D2:D19,B2:B19)  //返回D2:D19
    IF(0,D2:D19,B2:B19)  //返回B2:B19
    

    IF函数的第一个参数为数组{1,0}时会分别计算两次,第一次以“1”判断,返回D2:D19,第二次以“0"判断,返回B2:B19
    通过这个IF函数,两次计算的结果会重新组合成一个数组返回,也即D2:D19在第一列,B2:B19在第二列,从而实现了数据列位置的调整,再套用VLOOKUP函数规则。

    补充两个函数
    INDEX
    INDEX(一列数据,数字N),返回结果为这一列的数据第N个。
    举例如下图
    "=INDEX(C2:C34,4)",
    返回C2:C19 数据区域的,第4个值,也即”Y4010500004“。
    在这里插入图片描述
    MATCH
    MATCH(查找值,查找列,0),表示查找值在查找列里面的第几个,及已知料号求料号所在行。
    =MATCH(F3,C2:C23,0)
    在这里插入图片描述

    展开全文
  • VLOOKUP函数是EXCEL最常用的查找函数,本文将常见的VLOOKUP函数相关问题总结一下,主要为VLOOKUP函数怎么用?如何使用VLOOKUP函数进行反向逆向查找、模糊查找、区间...

    VLOOKUP函数是EXCEL最常用的查找函数,本文将常见的VLOOKUP函数相关问题总结一下,主要为VLOOKUP函数怎么用?如何使用VLOOKUP函数进行反向逆向查找、模糊查找、区间查找、多条件查找、多项查找。

    01

    VLOOKUP函数怎么用?

    VLOOKUP是一个查找函数,给定一个查找的目标,它就能从指定的查找区域中查找返回想要查找到的值。

    它的基本语法为:

    VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找)

    下面以一个实例来介绍一下这四个参数的使用。

    例:如下图所示,要求根据表二中的姓名,查找姓名所对应的年龄。

    公式:B13 =VLOOKUP(A13,$B$2:$D$8,3,0)

    参数说明:

    查找目标:就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。

    公式:B13 =VLOOKUP(A13,$B$2:$D$8,3,0)

    查找范围(VLOOKUP(A13,$B$2:$D$8,3,0) ):指定了查找目标,如果没有说从哪里查找,EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找,也可以从一个常量数组或内存数组中查找。

    本例中要从表一中进行查找,那么范围我们要怎么指定呢?这里也是极易出错的地方。大家一定要注意,给定的第二个参数查找范围要符合以下条件才不会出错:

    • 查找目标一定要在该区域的第一列。本例中查找表二的姓名,那么姓名所对应的表一的姓名列,那么表一的姓名列(列)一定要是查找区域的第一列。像本例中,给定的区域要从第二列开始,即$B$2:$D$8,而不能是$A$2:$D$8。因为查找的“姓名”不在$A$2:$D$8区域的第一列。

    • 该区域中一定要包含要返回值所在的列,本例中要返回的值是年龄。年龄列(表一的D列)一定要包括在这个范围内,即:$B$2:$D$8,如果写成$B$2:$C$8就是错的。

    返回值的列数(B13 =VLOOKUP(A13,$B$2:$D$8,3,0))。这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”,它是第二个参数查找范围$B$2:$D$8的第3列。

    这里一定要注意,列数不是在工作表中的列数(不是第4列),而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别,第3个参数的值应该设置为多少呢。答案是2。因为性别在$B$2:$D$8的第2列中。

    精确OR模糊查找(VLOOKUP(A13,$B$2:$D$8,3,0) ),最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样,模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找,而值为1或TRUE时则表示模糊。

    这里小编提醒大家切记切记,在使用VLOOKUP时千万不要把这个参数给漏掉了,如果缺少这个参数默为值为模糊查找,我们就无法精确查找到结果了。

    02

    VLOOKUP的反向查找

    VLOOKUP的反向查找,需要用IF函数把数据源倒置一下。一般情况下,VLOOKUP函数只能从左向右查找。但如果需要从右向右查找,则需要把区域进行“乾坤大挪移”,把列的位置用数组互换一下。

    例:要求在如下图所示表中的姓名反查工号。

    公式:=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)

    公式剖析:

    • 这里其实不是VLOOKUP可以实现从右至左的查找,而是利用IF函数的数组效应把两列换位重新组合后,再按正常的从左至右查找。

    • IF({1,0},B2:B5,A2:A5)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时(前提是该函数的参数支持数组),返回的结果也会是一个数组。这里1和0不是实际意义上的数字,而是1相关于TRUE,0相当于FALSE,当为1时,它会返回IF的第二个参数(B列),为0时返回第二个参数(A列)。根据数组运算返回数组,所以使用IF后的结果返回一个数组(非单元格区域):{'张一','A001';'赵三','A002';'杨五','A003';'孙二','A004'}

    03

    VLOOKUP的模糊查找

    模糊查找就是匹配查找。在A列我们知道如何查找型号为“AAA”的产品所对应的B列价格,即:=VLOOKUP(C1,A:B,2,0)

    如果需要查找包含“AAA”的产品名称怎么表示呢?如下图表中所示。

    公式=VLOOKUP('*'&A10&'*',A2:B6,2,0)

    公式说明:VLOOKUP的第一个参数允许使用通配符“*”来表示包含的意思,把*放在字符的两边,即'*' & 字符 & '*'。

    04

    VLOOKUP的区间查找

    数字的区间查找即给定多个区间,指定一个数就可以查找出它在哪个区间并返回这个区间所对应的值。

    VLOOKUP的第4个参数,如果为0或FALSE是精确查找,如果是1或TRUE或省略则为模糊查找,那么实现区间查找正是第4个参数的模糊查找应用。

    首先需要了解一下VLOOKUP函数模糊查找的两个重要规则:

    • 引用的数字区域一定要从小到大排序。杂乱的数字是无法准确查找到的。如下面A列符合模糊查找的前题,B列则不符合。

    • 模糊查找的原理是:给一定个数,它会找到和它最接近,但比它小的那个数。详见下图说明。

    最后看一个实例:

    例:如下图所示,要求根据上面的提成比率表,在提成表计算表中计算每个销售额的提成比率和提成额。

    公式:=VLOOKUP(A11,$A$3:$B$7,2)

    公式说明:

    • 上述公式省略了VLOOKUP最后一个参数,相当于把第四个参数设置成1或TRUE。这表示VLOOKUP要进行数字的区间查找。

    • 图中公式中在查找5000时返回比率表0所对应的比率1%,原因是0和10000与5000最接近,但VLOOKUP只选比查找值小的那一个,所以公式会返回0所对应的比率1%。

    05

    VLOOKUP的多条件查找

    VLOOKUP函数需要借用数组才能实现多条件查找。例:要求根据部门和姓名查找C列的加班时间。

    分析:不是让VLOOKUP本身实现多条件查找,而是想办法重构一个数组。多个条件可以用&连接在一起,同样两列也可以连接成一列数据,然后用IF函数进行组合。

    公式:

    {=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}

    公式剖析:

    • A9&B9 把两个条件连接在一起。把他们做为一个整体进行查找。

    • A2:A5&B2:B5,和条件连接相对应,把部分和姓名列也连接在一起,作为一个待查找的整体。

    • IF({1,0},A2:A5&B2:B5,C2:C5) 用IF({1,0}把连接后的两列与C列数据合并成一个两列的内存数组。

    按F9后可以查看的结果为:

    {'销售张一',1;'销售赵三',5;'人事杨五',3;'销售赵三',6}

    完成了数组的重构后,接下来就是VLOOKUP的基本查找功能了,另外公式中含有多个数据与多个数据运算(A2:A5&B2:B5),所以必须以数组形式输入,即按ctrl+shift后按ENTER结束输入。

    06

    VLOOKUP的多项查找

    VLOOKUP一般情况下只能查找一个,那么多项该怎么查找呢?例3 要求把如图表中所有张一的消费金额全列出来。

    分析:经过前面的学习,我们也有这样一个思路,我们在实现复杂的查找时,努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找,我们可以对查找的内容进行编号,第一个出现的是后面连接1,第二个出现的连接2。

    公式:

    {=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT('b2:b'&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

    公式剖析:

    • B$9&ROW(A1) 连接序号,公式向下复制时会变成B$9连接1,2,3

    • 给所有的张一进行编号。要想生成编号,就需要生成一个不断扩充的区域(INDIRECT('b2:b'&ROW($2:$6)),然后在这个逐行扩充的区域内统计“张一”的个数,在连接上$B$2:$B$6后就可以对所有的张一进行编号了

    • IF({1,0}把编号后的B列和C组重构成一个两列数组

    通过以上的讲解,需要知道,VLOOKUP函数的基本用法是固定的,要实现高级查找,就需要借助其他函数来重构查找内容和查找数组。

    End.

    作者:青路

    来源:360个人图书馆

    本文为转载分享,如侵权请联系后台删除

    电影院要开门了,最受期待的电影是...

    12000+字超详细 SQL 语法速成!

    后台回复“入群”即可入群交流数据干货
    
    展开全文
  • vlookup函数是工作中excel中最常用的查找函数。但遇到反向、双向等复杂的表格查找,还是要请出今天的主角:index+Match函数组合。本文分别图文详解使用index+Match函数组合实现反向、双向等复杂的表格查找的方法。 ...

    vlookup函数是工作中excel中最常用的查找函数。但遇到反向、双向等复杂的表格查找,还是要请出今天的主角:index+Match函数组合。本文分别图文详解使用index+Match函数组合实现反向、双向等复杂的表格查找的方法。

    1、反向查找

    【例1】如下图所示,要求根据产品名称,查找编号。

    index+Match函数组合

    分析:

    先利用Match函数根据产品名称在C列查找位置

    =MATCH(B13,C5:C10,0)

    再用Index函数根据查找到的位置从B列取值。完整的公式即为:

    =INDEX(B5:B10,MATCH(B13,C5:C10,0))

    2、双向查找

    【例2】如下图所示,要求根据月份和费用项目,查找金额

    index+Match函数组合

    分析:

    先用MATCH函数查找3月在第一行中的位置

    =MATCH(B10,$A$2:$A$6,0)

    再用MATCH函数查找费用项目在A列的位置

    = MATCH(A10,$B$1:$G$1,0)

    最后用INDEX根据行数和列数提取数值

    INDEX(区域,行数,列数)

    =INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))

    3、多条件查找

    【例3】如下图所示,要求根据入库时间和产品名称,查找入库单价。

    index+Match函数组合

    分析:

    由于match的第二个参数可以支持合并后的数组所以可以直接进行合并查找:

    =MATCH(C32&C33,B25:B30&C25:C30,0)

    查找到后再用INDEX取值

    =INDEX(D25:D30,MATCH(C32&C33,B25:B30&C25:C30,0))

    由于公式中含有数组运算(一组数同另一组数同时运算),所以公式需要按ctrl+shift+enter三键完成输入

    转载于:https://www.cnblogs.com/janicemvidal/p/8608466.html

    展开全文
  • VLOOKUP函数是EXCEL最常用的查找函数,本文将常见的VLOOKUP函数相关问题总结一下,主要为VLOOKUP函数怎么用?如何使用VLOOKUP函数进行反向逆向查找、模糊查找、区间...
  • 2、反向查找 根据表二姓名,查找表一编号。但表一中编号列在姓名列之前,无法直接使用VLOOKUP。用INDEX+MATCH函数组合一招搞定。 3、交叉查找 根据表二费用、月份查找表一中金额。VLOOKUP+MATC...
  • vlookup配合if函数实现反向查询欢迎来到知了Excel函数教学 欢迎来到知了Excel函数教学 vlookup在excel函数中也算是一个大哥级别的函数了,功能非常强大。把vlookup用好可以大大提高我们的工作效率。 缺点就是只能从...
  • VLOOKUP函数 功能:按列查找 语法格式:=VLOOKUP(查找目标,查找范围,返回值的列数,精确or 模糊查找查找方式有2种: 精确查找:0或FALSE 模糊查找:1或TRUE 查询方式 (1) 单条件查询(使用单个...
  • VLOOKUP函数

    千次阅读 2018-04-04 14:38:57
    VLOOKUP函数是EXCEL最常用的查找函数,VLOOKUP函数栏目有很多该函数的各种使用方法的教程,但还是有很多同学提问与该函数有关的问题。本文将常见的VLOOKUP函数相关问题总结一下,主要为VLOOKUP函数怎么用?如何使用...
  • VLOOKUP函数精讲(一)

    2021-04-26 00:17:23
    大家好!我是小v今天给大家介绍的是Excel函数中的“万金油”函数vlookup。vlookup函数Excel中使用最频繁的函数,它可以用来精确查找,模糊查找、反向查找、多条件查找……,...
  • Excel使用攻略(1)

    2019-07-29 19:20:00
    Excel使用攻略(1)1.SUM函数(数学)基本语法:算例:累计求和SUM函数的数组公式2.IF函数(逻辑)基本语法:算例:累计求和3.VLOOKUP函数(筛选)基本语法:3.1基础单条件查找3.2反向查找3.3多条件查询3.4查询返回...
  • 商业数据分析从入门到入职(3)Excel进阶应用

    千次阅读 多人点赞 2020-09-15 13:37:08
    对某一类数据汇总时使用分类汇总,需要先进行排序,还可以进行多个汇总;可以对数据输入进行限制,有多种限制方式。...Match和Index可以结合进行反向查找;可以返回多列;数组也可以实现多匹配查找。
  • 1 VLOOKUP反向查找 =IFERROR(VLOOKUP(Sheet1!K:K,IF({0,1},Sheet2!G2:G1133,Sheet2!L2:L1133),2,0),"不存在") VLOOKUP添加“IF({0,1},”后进行反向查找 2INDEX 和 MATCH 此示例显示了一个较小的列表,其中我们要...
  • VLOOKUP函数的使用方法(高级篇)

    千次阅读 2014-04-21 10:24:36
    前言:前面我们分别学习了VLOOKUP函数的... 一、VLOOKUP的反向查找。  一般情况下,VLOOKUP函数只能从左向右查找。但如果需要从右向右查找,则需要把区域进行“乾坤大挪移”,把列的位置用数组互换一下。  例
  • Excel函数:VLOOKUP

    2020-11-16 20:53:09
    Excel函数:VLOOKUP VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 第1个参数:查找的值 ...注:只能从左往右查找,而不能从右往左的反向查找,即需要把与查找的值的列放在查找区域的第一列 ..
  • Excel百宝箱

    2012-10-27 17:09:21
    【显示经典菜单】:在Excel 2007或者2010中显示2003样式的菜单,方便初学Excel 2007或2010的用户查找菜单 【截取窗口对象】:将选定的窗口或者对象转换成图片文件,可截Excel界面和其它任意界面 【截取矩形对象】:将...
  • 前言:前面我们分别学习了VLOOKUP... 一、VLOOKUP的反向查找。  一般情况下,VLOOKUP函数只能从左向右查找。但如果需要从右向右查找,则需要把区域进行“乾坤大挪移”,把列的位置用数组互换一下。  例1:要求在如
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    【修复Excel】:当您的Excel某些功能无法使用时,或者无缘无故多出很多菜单、工具栏时,本工具可以瞬间恢复Excel到默认状态 【破解VBA密码】:将VBA编写的且有密码的程序进行破解,瞬间完成 【生成2003样式菜单】:...
  • 【显示经典菜单】:在Excel 2007或者2010中显示2003样式的菜单,方便初学Excel 2007或2010的用户查找菜单 【截取窗口对象】:将选定的窗口或者对象转换成图片文件,可截Excel界面和其它任意界面 【截取矩形对象】...
  • EXCEL百宝箱8.0终极版

    2011-11-05 16:48:02
    反向选择】:选择当前区域中未选择的区域 【千年日历】:工单元格中生成千年日期。默认显示本月的日历,可以自由调整年月 【保护公式】:保护当前工作表所有公式,不让人看到公式本身,只能看到公式结果 【生成...
  • excel百宝箱 正版

    2013-04-04 23:39:28
    ║ 【快捷取日期】 【查找与替换】 【隐藏与显示磁盘】 【修复EXCEL】 【屏幕画笔】 ║ ║ 【快捷综合取数】 【EXCEL比较工具】 【端口进程查询】 【筛选选区唯一值行】 【俄罗斯方块】 ║ ║ 【自动填充】 【资料...
  • Excel工具箱教学指导

    2014-03-27 23:07:35
    ║ 【快捷取日期】 【查找与替换】 【隐藏与显示磁盘】 【修复EXCEL】 【屏幕画笔】 ║ ║ 【快捷综合取数】 【EXCEL比较工具】 【端口进程查询】 【筛选选区唯一值行】 【俄罗斯方块】 ║ ║ 【自动填充】 【资料...
  • EXCEL集成工具箱V6.0

    2010-09-11 01:44:37
    查找与替换】 增强型EXCEL内置查找与替换功能。可以按设置搜索任何字符串(包括*或?符以及公式中包含的某字符),也可以将搜索结果写入到一新的工作表中以备查。 【工具箱选项】 集成工具箱的基本选项设置。基本...
  • Excel百宝箱9.0无限制破解版

    热门讨论 2012-02-03 19:05:29
    【显示经典菜单】:在Excel 2007或者2010中显示2003样式的菜单,方便初学Excel 2007或2010的用户查找菜单 【截取窗口对象】:将选定的窗口或者对象转换成图片文件,可截Excel界面和其它任意界面 【截取矩形对象】:将...
  • 另外,在“函数向导”对话框中会生成28个新的函数,用于扩展Excel的计算功能。且所有功能都通用于Excel 2002、2003、2007和2010。  Excel百宝箱 79个菜单工具的功能介绍如下: 功能名称功能介绍 【公农双历查询】...
  • ║ 【快捷取日期】 【查找与替换】 【隐藏与显示磁盘】 【修复EXCEL】 【屏幕画笔】 ║ ║ 【快捷综合取数】 【EXCEL比较工具】 【端口进程查询】 【筛选选区唯一值行】 【俄罗斯方块】 ║ ║ 【自动填充】 【资料...
  • ║ 【快捷取日期】 【查找与替换】 【隐藏与显示磁盘】 【修复EXCEL】 【屏幕画笔】 ║ ║ 【快捷综合取数】 【EXCEL比较工具】 【端口进程查询】 【筛选选区唯一值行】 【俄罗斯方块】 ║ ║ 【自动填充】 【资料...
  • 查找与替换】 增强型EXCEL内置查找与替换功能。可以按设置搜索任何字符串(包括*或?符以及公式中包含的某字符),也可以将搜索结果写入到一新的工作表中以备查。 【工具箱选项】 集成工具箱的基本选项设置。基本...
  • 查找与替换】 增强型EXCEL内置查找与替换功能。可以按设置搜索任何字符串(包括*或?符以及公式中包含的某字符),也可以将搜索结果写入到一新的工作表中以备查。 【工具箱选项】 集成工具箱的基本选项设置。基本...

空空如也

空空如也

1 2 3
收藏数 58
精华内容 23
关键字:

excel反向查找函数