精华内容
下载资源
问答
  • Excel中VLOOKUP函数单,多条件查找&index与match
    2021-02-05 19:33:54

    VLOOKUP函数

    VLOOKUP(找什么?,在哪找?,返回第几列?,精确还是模糊?)


    参数解析:以在一张表格中通过用VLOOKUP函数查找某个员工姓名返回销售额为例,第一个参数:目标单元格数据(某个姓名),第二个参数:框选目标数据区域(一般是框选姓名列到销售额列),第三个参数:返回的结果是销售额,销售额假设在框选的所有列中是第三列,就填个3,第四个参数:0为精确搜索,1为模糊搜索(常用于区间提成问题,模糊找的只能是模糊的数值,一般是小于目标数值的最大数)


    VLOOKUP单条件查询:直接套公式
    VLOOKUP多条件查询:见下图,这是一个取巧的方法,将多条件通过辅助列合并成单条件
    在这里插入图片描述


    补充:VLOOKUP函数的核心是在数据源中查找指定数据并返回目标数据对应列数据,一般是从所需区域的第一列开始找,返回与之匹配的列数据,但是只能是左边查右边返回数据的顺序,不能右边查,左边返回,如果将该函数的思想拆分出来就是index(只查找数据没有返回值的动作)与match(只有返回值的动作没有查找的能力)


    index和match

    index:
    模板:index(在哪取?,取第几个?)
    如果是列查找,返回的是对应单元格数据
    match:
    模板:match(查啥?,在哪查?,精确还是近似)
    如果是列查找,返回的是行数

    VLOOKUP升级版模板:index(在哪取?,match(查啥?,在哪查?,精确还是近似))

    更多相关内容
  • excel中条件查找,涉及到XLOOKUP函数和VLOOKUP函数的使用。例如我们将某个坐标的信息输入到表格中,但是我们只需要其中一部分坐标的信息的时候,我们可以用到此方法。
  • EXCEL获取匹配单元格值 & VLOOKUP多条件查找
  • 其实这位学员的问题就是excel的多条件查找问题。 下面通过一个实例跟大家分享一下常用的3种excel多条件查找函数。 下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的...

    最近在Excel微信学习交流群中收到某位学员的问题咨询,问题是如何返回单据编号和物料长代码对应的含税数额。如下表:

     

    其实这位学员的问题就是excel的多条件查找问题。

    excel的多条件查找

     

    下面通过一个实例跟大家分享一下常用的3种excel多条件查找函数。

    下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。

    下面是三种excel双条件查找返回的方法,依次来看:

     

    第一 excellookup多条件查询

    excel多条件查找函数

    函数公式:=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)

    公式解析:首先通过A3单元格与B表I列数据做对比,同时用B3单元格与B表J列信息做对比。

    在excel中如果两个单元格对比,相等则返回TRUE,在四则运算中用1表示。如果不相等则返回FALSE,使用0表示。

     

    那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部分运算的结果就只有0或者1两种情况,因为只有0*1、1*1、1*0这三种情况。

     

    按照二分法原理,lookup函数会在二分位处查找符合条件的数据。大家都知道lookup函数想要精准查找那么这组数值必须要升序,但实际上这组数据运算结果0和1的顺序是混乱的。

     

    所以就想到了用0来除以0和1的方式来区分。由于分母不能为0,所以0/0返回的是错误,0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的,所以只有数据运算结果为1的公式满足条件。

     

    那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示,其他的变成错误值,利用函数查找忽略错误这个特点完成查找。

     

    总结:本函数由于使用了二分法原理查找,所以如果数据量较大时运算会很慢。

     

    第二 vlookup多条件查找

     

    vlookup函数是我们最常用的函数,vlookup函数主要用于垂直方向上向右查找。如下图:

    excel多条件查找函数

    使用G2单元格在A列中查找,如果查找到对应单元格则返回A列向右第二列的数据。简而言之:=VLOOKUP(查找什么,在哪查找,从条件所在列算起找到后返回对应的第几列数据,精确或模糊查找)。

     

    那vlookup如何才能完成多条件查询呢?。

    还以客户投诉表为例,按照姓名&地区来匹配产品型号返回到E里中。

    其实我们是可以将A、B两表中插入辅助列,将姓名和地区都合并到一个单元格中然后使用vlookup来完成。

    但是插入2个辅助列后整个表列数发生变动,在工作中往往单元格中有很多公式,如果列数发生变化将直接导致表格中函数公式运算结果错误。所以添加辅助列的方式虽然简单,但不是最好的方式。

     

    那么不用辅助列如何才能完成多条件查询呢?

     

    首先我们查找值合并很简单,输入函数vlookup时第一个参数可以写成A3&B3,即可将A3、B3两个单元格内容合并,作为查找值。

    现在问题查找区域也需要做合并。

     

    如果把两列内容合并在一起,可输入公式=H2:H19&I2:I19,按ctrl+shift+回车生成结果,然后下拉公式,这样两个条件就变成了一个。

    接下来通过IF函数提取对应的J列数据,可输入公式=IF({0,1},H2:H19&I2:I19,J2:J19),按ctrl+shift+回车生成结果,然后下拉公式,{0,1}表示逻辑值{FALSE,TRUE}。

    下面我们详细来解析一下:

    首先在excel中0表示错误,1以及其他所有数值表示正确。如下表示例:

    通过上面的例子我看到如果IF判断0则返回错误,判断1则返回正确。

    现在我们可以将公式拆分为以下两种情况:

    IF(0, H2:H19&I2:I19,J2:J19),0表示FALSE,所以只能返回J列数据。

    IF(1, H2:H19&I2:I19,J2:J19),1表示TRUE,所以只能返回H列和I列合并结果。

    那么IF({0,1},H2:H19&I2:I19,J2:J19)怎么理解呢?

    既然是数组公式,那么可以将它理解为同时返回两组数据,0对应的是J2:J19,1对应的H2:H19&I2:I19,构建了两列数据。

    最后我们使用vlookup函数完成嵌套,=VLOOKUP(A3&B3,IF({1,0},H3:H20&I3:I20,J3:J20),2,0),这里我们就可以理解为用A3&B3在H3:H20&I3:I20中查找对应J3:J20中的数据。因为公式中IF({1,0},H3:H20&I3:I20,J3:J20)返回的顺序是先返回H3:H20&I3:I20再返回J3:J20。

    注意:很多人不明白为什么嵌套的时候IF第一参数又变成了{1,0},因为这里我们需要返回的是H和I合并结果作为查找区域。PS:所有数组公式完成输入后要使用数组三键ctrl+shift+ener来返回运算结果!

     

    这样我们不用辅助列也能通过vlookup函数完成多条件查询。

     

    第三 OFFSET+MATCH函数公式

    很多excel高手都知道offset可以当vlookup函数使用,但职场新人大多都不了解。

    下面举例跟大家分享一下通过offset函数完成多条件查询。

    函数公式:{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}

    公式解析:

    完成多条件查询第一步先要确定A表中姓名&地区合并后对应在B表中姓名&地区的顺序。这里我们通过MATCH来完成,我们用个简单的例子说明。

    =MATCH(A2,E:E,0)表示使用A2单元格在E列中查找,0表示精确查找、1小于、-1大于,通常情况下都是精确查找。

    MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0)表示将A3与B3合并作为查找内容,H列和I列合并作为查找区域,0表示精确查找。

     

    确定顺序后我们通过OFFSET函数以顺序数据作为偏移行数返回对应数值。

    OFFSET函数的功能是以指定的单元格引用为参照系,通过给定偏移量得到新的引用。

    返回的引用可以为一个单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。

    =OFFSET(J2,1,0,1,1)表示以J2单元格作为参照物向下偏移1行,向右偏移0列,返回1行1列数据区域。

    =OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)表示以$J$2为参照单元格,通过MATCH查找出来顺序作为向下偏移的行数,偏移列数量省略表示不偏移,第三个、第四个参数省略表示只返回一个单元格区域。

     

    下面我们来总结一下本篇excel双条件查找返回的三种方式的利弊。LOOKUP函数使用过程中运算较慢;VLOOKUP函数使用IF({0,1})数组公式,理解上存在一定难度;OFFSET+MATCH函数公式简单,可以作为首选方案。

    展开全文
  • excel-LOOKUP函数多条件查找

    千次阅读 2021-01-27 17:15:46
    注意: Lookup() 多条件查找时无需排序,正常LOOKUP函数要求“查找区域”中的值必须按升序排列,因为是二分法原理 举例: LOOKUP(1,0/(($A2:2:2:A9=E2)∗(9=E2)*(9=E2)∗(B2:2:2:B9=F2)),9=F2)),9=F2)),C2:2:2:C$9)...

    语法: LOOKUP(查找值,查找区域,结果区域)

    • 注意: Lookup() 多条件查找时无需排序,正常LOOKUP函数要求“查找区域”中的值必须按升序排列,因为是二分法原理

    举例: LOOKUP(1,0/(($A$2:$A$9=E2)*($B$2:$B$9=F2)),$C$2:$C$9)

    • 自测公式 =LOOKUP(1,0/(($O$1:$O$22=C1)*($P$1:$P\$22=D1)),$R$1:$R$22)
    • 条件值显示:=LOOKUP(1,0/(
      ({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})
      *
      ({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})
      ),
      $R 1 : 1: 1:R$22)
    • 查找值显示: =LOOKUP(1,
      {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},
      $R 1 : 1: 1:R$22)

    分解步骤:

    • 第一步,括号内$A$2:$A$9=E2和$B$2:$B$9=F2,两个等式,很明显,分别代表两个条件。

      • 注意
        • 一,范围在等号前面,值在等号后面。顺序不能乱;
        • 二,返回的结果分别是一串数组而不是单一的值。这里数组里的数据有两种,TRUE和FALSE
    • 第二步,两个条件相乘,专业术语称为【逻辑与】,即两条件同时满足,返回的结果,由于两个条件每个返回的都是一串数组,那么相乘得到的结果,也是一串数组,而非单一的值。

      • 注意 :这里的数组数据也是两种,0和1。
    • 第三步,用零值,去除以第二步得到的结果,那么原本第二步是0值的,0作为分母无意义,返回错误值#DIV/0!,而仅有第二步结果为1的,正确算出结果得到零值。

    • 第四步,
      在这里插入图片描述

    • 第五步,接下来就是二分法原理了,在只有0和错误值组成的数组中,查找数字1,由于LOOKUP函数使用二分法,默认数据从小到大排序,二分值0比1小,就会一直往下查找,于是就找到了最后一个0值(LOOKUP函数忽略错误值,故二分法不会找到错误值),这条数据就是同时满足两个条件的、我们所需要的数据了。

      • 回到我们第三步提出的疑问,用零值去除以第二步的结果,意义何在?

        • 很简单,意义就在于将不符合条件的值从第二步的结果0,转变为错误值#DIV/0!,这样就可以避免LOOKUP函数查找时,在一 堆0和1组成的数组中,用二分法查找1,这样是不准确的,
          所以,用0去除,将第二步结果为0的转变为错误值,LOOKUP就可以直接将其PASS掉不考虑。

    前三步图解,示例:
    前三步图解

    公式在这里插入图片描述

    展开全文
  • EXCEL表格中如何使用VLOOKUP函数进行反向查找和多条件查找归纳.pdf
  • 自定义多条件查找函数,可自动输入件条件!!
  • VLOOKUP函数使用之三,多条件查找

    千次阅读 2020-04-08 13:29:28
    VLOOKUP函数使用之三,多条件查找 VBA语言专家 发布时间:18-08-2418:26学者,科技达人 讲了两篇VLOOKUP函数的文章,今日继续讲这个专题,之所以讲这么,是因为这个函数对于大众来说,利用之广泛,前所未有。...

    VLOOKUP函数使用之三,多条件查找

    该函数的语法规则如下:

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    参数

    简单说明

    输入数据类型

    lookup_value

    要查找的值

    数值、引用或文本字符串

    table_array

    要查找的区域

    数据表区域

    col_index_num

    返回数据在查找区域的第几列数

    正整数

    range_lookup

    精确匹配/近似匹配

    FALSE(或0)/TRUE(或1或不填)

    虽然我不太赞成用此函数,原则是能不用就不用,可是这并不妨碍我细致地给大家讲解此函数的用法。

    今日讲高级的查找方法,即多条件的查询。

    先看下面的例子:

     

    上面的截图中,想要查找到年龄为41的甲1的销售业绩,按照常规的做法是无论如何也实现不了的。那么怎么办呢?这就是我今日要讲的多值查询的方法,思路就是借助数组!

    下面我们一起,一步一步地实现我们的目标:

    在C19中录入公式:{=VLOOKUP( A19&B19 ,IF({1,0},A2:A16&B2:B16,C2:C16),2,0) }

    别忘了数组公式的录入方法,是CTRL+SHIFT+ENTER结束录入。

    先看返回值,然后我们一步一步地讲解:

    公式:{=VLOOKUP(A19&B19,IF({1,0},A2:A16&B2:B16,C2:C16),2,0) }

    我们先看公式计算的大概思路是怎能样的:

    1 我们的努力方向不是让VLOOKUP本身实现多条件查找,而是想办法重构一个数组。

    对于多个条件我们可以用&连接在一起,同样两列数值我们也可以连接成一列数据,然后用IF函数进行组合。

    2 A19&B19 把两个条件连接在一起,把它们做为一个整体进行查找。

    3 A2:A16&B2:B16,和条件连接相对应,把人员和年龄列也连接在一起,作为一个待查找的整体。

    4、IF({1,0}, A2:A16&B2:B16, C2:C16) 用IF把连接后的两列与C列数据合并成一个两列的内存数组

    5、完成了数组的重构后,接下来就是VLOOKUP的基本查找功能了。

    下面我们看具体的计算过程,这个过程也是我写函数以来最为小心的时候,尽可能不出差,之前小心翼翼的写过一次,这次是更加小心了:

    1 先计算A19&B19的值得出:“甲141”

    公式变成:=VLOOKUP(“甲141”,IF({1,0},A2:A16&B2:B16,C2:C16),2,0)

    2 计算A2:A16&B2:B16的值,得出:{ “甲115”;“甲220”;“甲335”;“甲430”;“甲535”;“甲640”;“甲141”;“甲220”;“甲335”;“甲440”;“甲541”;“甲646”;“甲1347”;“甲1448”;“甲1549” }

    公式变成:

    =VLOOKUP(“甲141”,IF({1,0},{ “甲115”;“甲220”;“甲335”;“甲430”;“甲535”;“甲640”;“甲141”;“甲220”;“甲335”;“甲440”;“甲541”;“甲646”;“甲1347”;“甲1448”;“甲1549” },C2:C16),2,0)

    3 计算:{ “甲115”;“甲220”;“甲335”;“甲430”;“甲535”;“甲640”;“甲141”;“甲220”;“甲335”;“甲440”;“甲541”;“甲646”;“甲1347”;“甲1448”;“甲1549” },C2:C16)

    的值,得到:

    { “甲115”,110;“甲220”,160;“甲335” ,150;“甲430”,140;“甲535” ,130;“甲640” ,120;“甲141” ,110;“甲220” ,100;“甲335” ,90;“甲440” ,100;“甲541” ,110;“甲646” ,120;“甲1347” ,130;“甲1448” ,140;“甲1549” ,150 } 【两列数组构建完成】

    公式变成:

    =VLOOKUP(“甲141”, { “甲115”,110;“甲220”,160;“甲335” ,150;“甲430”,140;“甲535” ,130;“甲640” ,120;“甲141” ,110;“甲220” ,100;“甲335” ,90;“甲440” ,100;“甲541” ,110;“甲646” ,120;“甲1347” ,130;“甲1448” ,140;“甲1549” ,150 },2,0)

    4 最后得出结果110

    也许很多朋友看了上面的过程,不知所云,希望能仔细的研究一下,真的不是很难,掌握了这个方法,对于VLOOKUP函数的应用和数组的应用,都是受益匪浅。

    今日内容回向:

    1 实现多值查询的思路是什么?

    2 数组在实现多值查询过程中的作用是什么?

    分享成果,随喜正能量

    展开全文
  • excel多条件查找(用到index和match)

    千次阅读 2020-01-16 16:54:50
    excel多条件查找和vlookup多条件查找有很相似之处 公式如下图:(公式完成后,以shift+Ctrl+enter结束) 以上这些只是例子。下面我们开始实战演练了。 公式如图: 上面只完成了一个单元格的值,要想下拉,完成...
  • notepad++ 正则表达式多条件查找替换

    千次阅读 2019-10-16 13:23:35
    基础语法参考: ...通常情况下我们查找的内容和要被替换掉的内容是一样的,我们只需要使用正则表达式精确框定查找内容,替换直接输入要替换的内容即可。 但有时会比较复杂,查找的内容,只需要替换其中...
  • 今天小编就为大家分享一篇python 实现查找文件并输出满足某一条件的数据项方法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
  • 1.如下图,左侧是某公司员工销售表,现在想要快速查询出右侧指定组别及姓名的员工销量。 2.如下图插入两列辅助列 3.分别在这两列辅助列输入公式=A2&"@"&B2,=F2&"@"&G2并填充到底 ...
  • Excel函数完成多条件希望对大家有用,具体用法自己在有excel中体验一下吧
  • 不要编写 VBA 来实现符合条件 元素组合,查找弄起来。要CTRL + SHIFT + ENTER 一起才生效。
  • 本文主要介绍一个小知识点就是利用Linux的find命令查找符合条件的文件,通过这个小知识点的学习,希望读者在今后的工作学习中遇到想要查找符合条件的文件,能用到词知识点。利用Linux的find命令可以查找指定文件的...
  • vlookup_多条件查找

    2012-12-25 10:19:07
    vlookup_多条件查找
  • 1 EXCEL查找数据的基础方法:菜单 crtl+F 查找 注意:查找数据,查找公式 注意:查找下一个 查找全部 注意:查找查找全部 替换功能 筛选功能 ...2 查找数据可使用公式 ... 情况2:存在有个符合条件得...
  • VBA实现EXCEL表格多条件查询 For i = 1 To Workbooks.Count For Each c In Sheets(i).UsedRange If c.Value >= 150 And c.Value c1 = c1 + c.Value n = n + 1 End If ‘更多条件…… Next Next
  • 其实我本来对EXCEL是一窍不通的,直到我舅舅给了一串大概1500多行的药品供应目录让帮他我查找。当他告诉他们平时都是花1~2天人工填写查找的时候,我当时就惊呆了,心想EXCEL设计者肯定已经哭晕在厕所,大家竟然都...
  • 当我们想要在df_data中查找brand的列等于品类1和品类2的所有行数,可以采用以下方法: 方法1: df_data[(df_data['brand']=="品类1") | (df_data['brand']=="品类2")] 方法2: df_data.query('brand=="锐舞" | ...
  • android中内嵌轻量级数据库sqlite,涉及到的操作无外乎增删改查,今天就来说一说其中的查找操作,更的时候我们用到的查找是要按条件查找的 例如表的结构如下:
  • Excel 多条件 比对 查询 个 重复值

    千次阅读 2019-12-03 20:52:49
    说明:多条件比对查找多个值,且可查找重复值(支持最多4个条件比对,同时查找3个值) 重复值得查找,可以将查到的所有重复值,依次填写在sheet1的重复的项目中,sheet1中重复项数多于sheet2的部分会空出来,避免了...
  • EXCEL多种方法实现多条件查找

    千次阅读 2019-04-28 17:40:47
    EXCEL中 多条件查找的方法有很,以下为总结的部分函数方法,欢迎大家完善补充: 数据说明:在A1-C6的范围内,找出名称为乙、型号为FF的数量 方法一:sum函数方法 =SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6) 方法...
  •  但是,VLOOKUP函数一般情况下,只能实现单条件查找。  如果想通过VLOOKUP函数来实现双条件或条件的查找并返回值,那么,只需要加上IF({1,0}就可以实现。  下面,我们就一起来看看IF({1,0}和VLOOKUP函数...
  • EXCEL如何进行多条件的数据查找返回

    千次阅读 2018-03-22 09:03:00
    在使用EXCEL时经常会碰到一个表里的同一款产品每天的销量都不一样,然后我们需要查导出每一款产品每天的销量,即一对多条件查找。这个教复杂,我们要用到好几个函数的综合,下面小编来教你吧。 工具/原料...
  • vue多条件查询

    千次阅读 2019-08-23 11:50:57
    类型 delete this.listQuery.groupname // 部门 delete this.listQuery.ts // 时间 delete this.listQuery['search_column'] // 只要填写了全局搜索,则清空前面的listQuery,只传listQuery.value查询 if (this....
  • VLOOKUP多条件匹配

    千次阅读 2022-03-03 22:14:22
    G1:结果条件列起始位置,如图,结果数据列为H,结果条件列为F和G列,起始位置为F1和G1,因为需要查找的是两个条件,但是vlookup函数只能输入一个条件,所以这里用【&】把两个条件连接起来; IF({1,0},$A$1:$A$...
  • Vlookup实现多条件匹配

    千次阅读 2021-04-08 10:35:42
    只要在目标区域的首列添加一个辅助列,目的就是将多条件转化为一个单条件,这个时候我们就可以用Vlookup进行匹配了,请看下面的示例: 1、在A列前插入一空列,输入公式=B2&C2 2、在H2输入函数公式,用VLOOKUP...
  • grep命令进行多条件查询

    千次阅读 2021-03-29 17:37:35
    1,grep 查找同时满足条件 (与) grep 'fail' a.txt | grep 'error' 2,grep 查找满足条件中的一个 (或) grep 'fail\|error' a.txt
  • EXCEL:LOOKUP多条件、精确查找的方法

    千次阅读 2019-12-25 11:55:00
    LOOKUP模糊查找 精确查找 多条件精确查找

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 809,429
精华内容 323,771
关键字:

多条件查找