lookup实现多条件查询,含中文参数解释
分类:美高梅-办公

问题:excel中怎样双条件查找?

VLOOKUP及HLOOKUP可进行条件引,大家都知道的,相信很多朋友工作中都在使用。但遇到多条件(如同产品有2至4个型号)时进接用VLOOKUP就不行了,下面总结四种方面,希望给需要的同仁带来帮助。
如以下为SHEET1之参考资料:
品名 型号 单价
螺丝刀 六角 25
螺丝刀 梅花 30
钳子 大 10
钳子 小 8
SHEET2资料如下:
品名 型号 单价
螺丝刀 六角
螺丝刀 梅花
螺丝刀 六角
螺丝刀 梅花
钳子 大
钳子 小
在C列需要用公式引用SHEET1之单价,四种公式如下:
=VLOOKUP(A2&B2,IF(COLUMN(A1:B1)=COLUMN(A1:A1),sheet1!$A$2:$A$110&sheet1!$B$2:$B$110,sheet1!$C$2:$C$110),2,)
=INDEX(sheet1!C$2:C$110,MATCH(A2&B2,sheet1!A$2:A$110&sheet1!B$2:B$110,0))
=LOOKUP(2,1/((sheet1!$A$2:$A$110=A2)*(sheet1!$B$2:$B$110=B2)),sheet1!$C$2:$C$110)
=SUMPRODUCT((sheet1!$A$2:$A$110=A2)*(sheet1!$B$2:$B$110=B2),sheet1!$C$2:$C$110)
前两种为数组公式,建议大家使用时选第四种。

关于VLOOKUP函数的用法

vlookup和lookup相信大多人都会使用了,可以实现最基本的单条件引用查询功能,如果遇到多个条件,这两个函数是否还有用呢?看看下图的题目

回答:

“Lookup”的汉语意思是“查找”,在Excel中与“Lookup”相关的函数有三个:VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。

在这组数里张三重名了三次,我们除了人为的给添上张三1、张三2、张三3或者要求提供三姓名的ID外,还有其他区分方法吗?答案是有的,因为有组别,姓名+组别已经在这组数据里构成了唯一识别码。

  1. 当我们需要根据地址、类别找出相对应对额供应商时,我们可以用很多种办法,现在我给大家分享一种给大家

    美高梅4858官方网站 1

  2. 使用LOOKUP,这里介绍下LOOKUP的一种经典的使用方法:LOOKUP(1,0/((条件1)*(条件2)*(条件n)),返回区域),就当做是一种模板;在这里我们就会使用到改公式=LOOKUP(1,0/(($A$2:$A$11=F2)*($B$2:$B$11=G2)),$C$2:$C$11)。

    美高梅4858官方网站 2

  3. 3

    这里多条件查找就完成了

一、功能 
在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的数据。

美高梅4858官方网站 3

回答:

二、语法 
标准格式: 
VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

题目

用vlookup函数。 第一表sheet1,第二表sheet2,数据在第2行: 在sheet2的b2输入=if(iserror(vlookup($a2,sheet1!$a:$d,column(),0)),"",vlookup($a2,sheet1!$a:$d,column(),0))回车并向右拉d2,再选b2~d2一起向下拉。 这样,在a列输入品名(如vcd1等),对应的价格就显示出来。

解释:

先用常规的vlookup试一下,不试你是不会发现它是错的

VLOOKUP(‘你要检索的内容或指定单元格’,‘你要检索的范围,检索到内容时返回你检索表的第几列中的内容’, ‘真或假参数真代表查询的表已经排序,假代表没有排序’)

美高梅4858官方网站 4

例:VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE)

三个一样的值

说 明:在表SHEET2中检索当前表中A2中的内容,如果检索到,就返回表SHEET2中B2中的内容,因为B2是表SHEET2中的第二列,所以 VLOOKUP的第三个参数,使用2,表示如果满足条件,就返回查询表的第二列,最后的参数FALSE表示‘假’,意思是被查询的表,没有排序,这种情况 下,会从被查询的表中第一行开始,一直查询到结束。

常规的vlookup果然无效,它只对单一条件有效。上面也提到过姓名+组别就是唯一识别码也就是一个单一条件,把姓名和组别变成一个条件只需要使用“&”连接即可,不过数据区域要添加个辅助列,具体如图。

三、语法解释 
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为: 
VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False) 
1.Lookup_value为“需在数据表第一列中查找的数据”,可以是数值、文本字符串或引用。 
2.Table_array 为“需要在其中查找数据的数据表”,可以使用单元格区域或区域名称等。 
⑴如果 range_lookup 为 TRUE或省略,则 table_array 的第一列中的数值必须按升序排列,否则,函数 VLOOKUP 不能返回正确的数值。 
如果 range_lookup 为 FALSE,table_array 不必进行排序。 
美高梅4858官方网站,⑵Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时,不区分文本的大小写。 
3.Col_index_num 为table_array 中待返回的匹配值的列序号。 
Col_index_num 为 1 时,返回 table_array 第一列中的数值; 
Col_index_num 为 2 时,返回 table_array 第二列中的数值,以此类推。 
如果Col_index_num 小于 1,函数 VLOOKUP 返回错误值 #VALUE!; 
如果Col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。 
4.Range_lookup 为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。

公式:=VLOOKUP(F2&G2,$A$2:$D$7,4,0) ,注意A列的辅助列

四、应用例子 
A B C D 
1 编号 姓名 工资 科室 
2 2005001 周杰伦 2870 办公室 
3 2005002 萧亚轩 2750 人事科 
4 2005006 郑智化 2680 供应科 
5 2005010 屠洪刚 2980 销售科 
6 2005019 孙楠 2530 财务科 
7 2005036 孟庭苇 2200 工 会

美高梅4858官方网站 5

美高梅网站是多少,A列已排序(第四个参数缺省或用TRUE) 
VLOOKUP(2005001,A1:D7,2,TRUE) 等于“周杰伦” 
VLOOKUP(2005001,A1:D7,3,TRUE) 等于“2870” 
VLOOKUP(2005001,A1:D7,4,TRUE) 等于“办公室” 
VLOOKUP(2005019,A1:D7,2,TRUE) 等于“孙楠” 
VLOOKUP(2005036,A1:D7,3,TRUE) 等于“2200” 
VLOOKUP(2005036,A1:D7,4,TRUE) 等于“工 会” 
VLOOKUP(2005036,A1:D7,4) 等于“工 会”

加上辅助列,用&连接两个条件

若A列没有排序,要得出正确的结果,第四个参数必须用FALAE 
VLOOKUP(2005001,A1:D7,2,FALSE) 等于“周杰伦” 
VLOOKUP(2005001,A1:D7,3,FALSE) 等于“2870” 
VLOOKUP(2005001,A1:D7,4,FALSE) 等于“办公室” 
VLOOKUP(2005019,A1:D7,2,FALSE) 等于“孙楠” 
VLOOKUP(2005036,A1:D7,3,FALSE) 等于“2200” 
VLOOKUP(2005036,A1:D7,4,FALSE) 等于“工 会”

如果不用辅助列,那么也可以用lookup的多条件查询

五、关于TRUE和FALSE的应用 
先举个例子,假如让你在数万条记录的表格中查找给定编号的某个人,假如编号已按由小到大的顺序排序,你会很轻松地找到这个人;假如编号没有排序,你只好从上到下一条一条地查找,很费事。 
用VLOOKUP查找数据也是这样,当第一列已排序,第四个参数用TRUE(或确省),Excel会很轻松地找到数据,效率较高。当第一列没有排序,第四 个参数用FALSE,Excel会从上到下一条一条地查找,效率较低。

=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*条件N),查询区域) ,可以当成一个固定用法,具体看下图:

笔者觉得,若要精确查找数据,由于计算机运算速度很快,可省略排序操作,直接用第四个参数用FALSE即可。

公式:=LOOKUP(1,0/(($A$2:$A$7=E2)*($B$2:$B$7=F2)),$C$2:$C$7) ,注意多个条件是用 乘号 * 连接的

=========================

美高梅4858官方网站 6

Lookup和Vlookup有哪些区别?2009-11-17 14:18Excel查询函数中,Lookup和Vlookup有哪些区别?它们在应用中应该如何把握?请看本文讲解。

lookup多条件查询

  ★Lookup——数与行列比

从写法上来说还是vlookup加上& 写起来会更简单点,lookup再写的过程中很容易遗漏关键部分,需要多几次实际练习。

  Lookup的工作职责是什么呢?用一个数与一行或一列数据依次进行比较,发现匹配的数值后,将另一组数据中对应的数值提取出来。

  ·工资税率表:用数值比较

  根据不同的工资进行不同的税率计算是一个常见的应用。我们来看这张“工资税率查询”表(见图1)。现在要在右侧根据“收入”(F列),直接得到 对应的“税率”(G列)。在计算第1个“税率”时,输入函数公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”,回车,便可得到 “36.00%”。

美高梅4858官方网站 7

  这个结果是怎么来的?用F4中的第1个收入数“$123,409”,与左侧表的“收入最低”各档数据(“$B$3:$B$8”)进行对比,虽然 “$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是会与其中小于它的最大数“$58,501”相匹配。这样,同一行对应的 “36.00%”就提取出来了。

  ·图书销售表:用文本比较

  Lookup函数的对比数还可以是文本。在这张图书销售查询表中(见图2),用下表输入的“编号”(A15单元格)文本当作查询数,与上表的 “编号”一列($A$3:$A$11)进行对比,查询到了匹配的文本后,将“教材名称”一列($B$3:$B$11)对应的数据提取出来。公式是 “=LOOKUP(A15,$A$3:$A$11,$B$3:$B$11)”。

美高梅4858官方网站 8

  ★Vlookup——数与表格比

  Lookup有一个大哥——Vlookup函数。两兄弟有很多相似之处,但大哥本领更大。Vlookup用对比数与一个“表”进行对比,而不是Lookup函数的某1列或1行,并且Vlookup可以选择采用精确查询或是模糊查询方式,而Lookup只有模糊查询。

  ·模糊匹配

  用Vlookup函数进行模糊查询时,几乎与Lookup的作用完全一致。我们用Vlookup函数来提取第1个例子中的工资税率结果。函数公式为“=VLOOKUP(F4,$B$3:$D$8,3,TRUE)”。

  在这个函数中,用第1个收入“$123,409”(F4单元格)当作对比数,用它与左侧表(“$B$3:$D$8”)的第1列数进行对比,虽然 “$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配,但是函数的最后一个参数是“TURE”(“TURE”就是模糊查询),所以它会 与其中小于它的最大数“$58,501”相匹配。并将表中第3列(函数的第3个参数为“3”)对应的数据提取出来,所以结果同样是“36.00%”。

  ·订单明细表:精确匹配

  有时候,我们需要精益求精。在下面这个“订单明细表”(见图3)中,最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司表”中进行匹配查询。这是一个典型的精确查询的例子,计算第1个数据的函数公式是“=VLOOKUP(H3,$B$2:$D$6,3,FALSE)”。

美高梅4858官方网站 9

  小提示:

  把最后一个参数从“TRUE”变更成“FLASE”,就是精确匹配。而精确查询,就是查询数要与查询表第1列中的数据完全一致才能匹配提取,否则结果返回错误值“#N/A”。

  点评:

  Excel为我们提供了近20个有关“查找和引用”的函数,除了最常用的Lookup、Vlookup,还有Choos、Row、Colum、 Index和Match等,大家可以通过函数的帮助查看具体的功能。这些函数往往不是单独使用,可以与其他函数和Excel中的一些功能进行配合。

出处:

=========================================================================

今天在百度知道的时候,看到旁边有人问excel中条件查找vlookup的问题,有几位高手都知道使用vlookup作答,可惜都是没有经过测试,直接复制别人的答案,让所有的读者都无法实施,一头雾水。今天我们详细解答一下vlookup函数的实际应用问题:

问题:如下图,已知表sheet1中的数据如下,如何在数据表二 sheet2 中如下引用:当A列学号随机出现的时候,如何在B列显示其对应的物理成绩?

美高梅4858官方网站 10

首先我们知道需要用到vlookup函数,那么先介绍一下使用 vlookup函数的几个参数,vlookup是判断引用数据的函数,它总共有四个参数,依次是:

1、判断的条件
2、跟踪数据的区域
3、返回第几列的数据
4、是否精确匹配

根据以上参考,和上述在sheet2表的B列显示问题的实际需求,在sheet2表的B2单元格输入这个公式是:

=vlookup(a2,sheet1!$a$2:$f$100,6,true)

详细说明一下,在此vlookup函数例子中各个参数的使用说明:

1、a2 是判断的条件,也就是说sheet1表和sheet2表中学号相同者,即sheet2表a列对应的数据和sheet1表中学号列a列的数据相同方能引用;
2、sheet1!$a$2:$f$100 是数据跟踪的区域,因为需要引用的数据在f列,所以跟踪的区域至少在f列,sheet1!是不同表间引用所用的表名称,和标志是表间引用的!符号,$是绝对引用(关于绝对引用可以参考这里),$a$2:$f$100 表明从A2到F100单元格的数据区域,如果数据区域不止100,那么可以直接使用A:F,这样虽然方便但是有风险,因为如果sheet1表的下方还有其它数据,就有可能出现问题;
3、6 这是返回什么数的列数,如上图的物理是第6列,所以应该是6,如果要求英语的数值,那么此处应该是5
4、是否绝对引用,如果是就输入 true 如果是近似即可满足条件 那么输入false (近似值主要用于带小数点的财务、运算等)
5、vlookup是垂直方向的判断,如果是水平方向的判断可使用Hlookup函数

结果如下图:

美高梅4858官方网站 11

不知道你是否已经会使用vlookup这个条件查找函数,如果你有兴趣可以试试本例。与本例结合紧密的是excel数据引用,更多的excel可以参考这里。

原地址  

==============================================================

使用HLOOKUP函数进行水平查找

在一些企业,会根据员工的销售奖金是根据业绩而定的,业绩越高奖金比例也就越高。根据这种情况,往往需要对业绩数据根据奖金比例标准经行查询并定位奖金比例,当员工数量较多时,人为查询会比较困难,用HLOOKUP函数水平查找就可以实现。
HLOOKUP函数主要用于在表格或数值组的首行查找指定的数值(即在水平查找),并返回表格显示当前列中指定行处的值。

方法/步骤
1
将两个工作表放在同意工作薄内。
美高梅4858官方网站 12

美高梅4858官方网站 13

2
在业绩表的选择C2,输入“=HLOOKUP(B2,奖金标准!$B$3:$E$4,2)”,按回车。
公式中B2表示要查找的值;
“奖金标准!$B$3:$E$4”表示在“奖金标准”工作表中的B3:E4区域中查找,加$是防止在向下填充公式时照成错误;如果搜索区域是在同一工作表中,直接在公式中输入“$B$3:$E$4”即可;
“2”表示要显示的数据为B3:E4区域中查找的值所在列的第2行的值。
整个公式表示:在“奖金标准”工作表中B3:E4区域的首行查找与B2近似匹配的值所在的列,并显示该列第2行的值。
美高梅4858官方网站 14

3
选择C2单元格,点击并拖动光标向下填充公式。填充后,C2列单元格区域都会根据公式显示对应结果。
美高梅4858官方网站 15

美高梅4858官方网站 16

4
在业绩表的选择D2,输入“=B2*C2”,按回车,计算出销售奖金;
选择D2单元格,点击并拖动光标向下填充公式。
美高梅4858官方网站 17

注意事项
使用HLOOKUP函数时,如果是查找近似匹配值,被搜索区域(例子中的B3:E4)的首行值以升序的顺序排列,可以防止搜索错误

出处:

本文由美高梅网站是多少发布于美高梅-办公,转载请注明出处:lookup实现多条件查询,含中文参数解释

上一篇:18款科鲁兹油耗怎么样,手游的通用替身 下一篇:没有了
猜你喜欢
热门排行
精彩图文