求EXCEL多条件查找函数
篇一:excel中条件查找函数
求EXCEL多条件查找函数(实例)?
2011-12-8 10:52
提问者:匿名 | 浏览次数:1013次
我来帮他解答
2011-12-8 10:56
满意回答
很多函数
下面介绍一个
假设你的原始数据在sheet1工作表,数据在A3:G19,其中B3:G3是上表头,
A4:A19是左表头。
新的数据即计划放在sheet2工作表,数据在A3:G19,其中B3:G3是上表头,
A4:A19是左表头。
千万注意:1.表格总体位置,以及左右表头的位置。2.假设的新旧表头位置尽管
一样,但内容已经不一样了,具体的你自己根据情况调整
这样,在sheet2工作表的B4单元格输入 (注意是B4)
=INDEX(Sheet1!$A$3:$G$19,MATCH($A4,Sheet1!$A$4:$A$19,0)+1,MATCH
(B$3,Sheet1!$B$3:$G$3,0)+1)
右下拉动即可
EXCEL 多条件查找
在表1中分别输入品名,型号,规格,根据这三个条件在表2中查找对应的条
形码
满意回答
假设表1 表2 分别是sheet1 sheet2
接着假设
sheet2中ABCD列分别是品名,型号,规格,及条形码
再假设,怎么全得假设
sheet1表ABC三列分别用来输入品名,型号,规格
那sheet1表D1输入
=lookup(1,0/((sheet2!A$1:A$100=a1)*(sheet2!B$1:B$100=b1)*(sheet2!c$1:C$
100=c1)),sheet2!D$1:D$100)
公式是假设了你的sheet2表这个基础表的数据有100行,根据实际修改。
三个条件查找的函数
=LOOKUP(1,0/((A2:A1062=F2)*(B2:B1062=G2)*(C2:C1062=H2)),D2:D1062)
=SUMPRODUCT(((A2:A1062)=F2)*((B2:B1062)=G2)*((C2:C1062)=H2)*(D2:D1062)) 这个
也可以
D列中不能有空格,否则不能计算。
通过IF({1,0}和VLOOKUP函数实现Excel的多条件查找 附Excel实例
2012-02-27 17:18
三个条件A1&B1&C1,查询目标F,H,I三列,返回J列,公式为:
=VLOOKUP(A1&B1&C1,IF({1,0},Sheet1!$F$2:$F$12&Sheet1!$H$2:$H$12&I2:I12,Sheet1!$J$2:$J$12),2,FALSE)
输入完毕后按ctrl+shift+enter~
至于false前边的第"2"列呢?是因为excel把FHI三列合并为一列,A1B1C1三个格子合成一个单元格,只要顺序不变即可~
第一部分:excel vlookup函数使用介绍excel中条件查找函数。
Excel中vlookup函数怎么用呢,VLOOKUP函数几乎是大家学习查找引用函数最先接
触到的一个函数。
excel vlookup函数的写法可以套用这个结构:
VLOOKUP(查找值,查询区域,返回列,查找方式:精确或模糊查找)。
excel vlookup函数的写法:VLOOKUP(查找值,查询区域,返回列,0),最后一个
参数0是什么意思呢?其实这个0表示FALSE的意思,也就是我们想让VLOOKUP函数
实现精确查找,其实我们现实中很多用到的都是这个精确查找,那最后个参数不为0会是
什么效果呢?如果此参数不为0,就是说最后一个参数为TRUE,此时VLOOKUP函数实
现的是模糊查找,也就是说如果VLOOKUP查不到我们想要找的那个值,就返回小于这个查找值中的最大的那个值。
第二部分:excel vlookup函数实例介绍
1、excel vlookup函数等级查询
excel vlookup函数在等级、折扣等这些方面有着很大的用处,下面是一个excel vlookup函数模糊查找的例子。
上图中,A1:B5是一个等级对应表,我们根据此等级,使用excel vlookup函数完成D8:D12区域的级别查找。在D8输入公式:=VLOOKUP(C8,$A$1:$B$5,2,TRUE),下拉复制即可完成。
分析:上面的公式,vlookup函数第四参数为TRUE,使用的是模糊查找。根据excel vlookup函数的帮助说明,当最后一个参数为TRUE,此时VLOOKUP函数实现的是模糊查找,也就是说如果VLOOKUP查不到我们想要找的那个值(77),就返回小于这个查找值中的最大的那个值(A3的70)对应的级别就是“中”。
说明:VLOOKUP函数的模糊查找,仅限于首列为升序排列。
2、excel vlookup函数反向查找实例:根据员工姓名返回员工号
excel vlookup函数的反向查找也是非常常见的。Excel中VLOOKUP函数通常只能从左往右的垂直方向有序查找。如果需要用到逆序,反向查找就需要使用IF或CHOOSE其中一个函数嵌套使用。IF函数在VLOOKUP函数的使用通常是这样的形式:IF({1,0},查找内容的列,返回内容的列)。
比如上图所示,B4单元格,我们输入公式:=VLOOKUP(B3,IF({1,0},E2:E10,D2:D10),2,)。
也可以使用此公式:=VLOOKUP(B3,CHOOSE({1,2},E2:E10,D2:D10),2,)
就是将vlookup函数的第二参数IF改为choose。因为IF{1,0}函数只能用到2个条件,使用有一定局限性。借助CHOOSE函数同样能做到,而且CHOOSE比IF更灵活。比如choose可以有三个甚至更多的条件:=VLOOKUP(B3,CHOOSE({1,2,3},E2:E10,D2:D10,F2:F10),2,)。
3、IF({1,0})在vlookup函数中的使用解释
IF(条件,返回值1,返回值2),首先弄懂这个函数,如果条件为真,函数的结果为"返回值1",如果条件为假,函数的结果是"返回值2"。比如:
if(true,10,100)=10
if(1,"A","B")="A"
if(false,20,30)=30
if(0,"你","我")="我"
{1,2,3,4}是一个数组,一个数组能存储多个数值,数组的表示方式是{}。
{1,0}是个水平数组,它有两个值,一个是1,另一个是0。
该公式通过IF函数改变了列顺序,利用常量数组{1,0}重新构建了一个新的二维内存数组,再提供给VLOOKUP作为查找范围使用。
那么IF({1,0}这个怎么解释,IF({1,0}中的1用其它数代替也可以,如:IF({2,0}、IF({0.8,0}、IF({-1,0},但0只能用FALSE代替。因此,也可以这样理解:0等于FALSE,非0数值则等于TRUE。
在公式的中IF({1,0}只是公式中一部分if(a,b,c),if函数有三个参数,a为true执行b,a为false执行c。那么IF({1,0},E2:E10,D2:D10)这样的公式,简单的理解就是其中的参数a为{1,0},实际上{1,0}是一个水平数组,他有两种情况一个是1一个是0,1表示true,0表示flase,因此两种情况都要执行,整个公式执行后就是把E2:E10和D2:D10两个区域合并了,而vlookup返回值为第2列的值即为D列,这样就达到了反向查询的效果。
excel vlookup函数反向查找总结: vlookup要查找的列必须在第一列,而我们就是利用数组公式的特性配合if公式,给vlookup组建一个满足vlookup要求的表范围,把条件列前面的内容移到后面去,而如果直接在vlookup中选取这个范围是反向的是不能直接选取的。
Excel的双条件多条件查找
篇二:excel中条件查找函数
Excel的双条件多条件查找
VLOOKUP函数一般情况下,只能实现单条件查找。excel中条件查找函数。
如果想通过VLOOKUP函数来实现双条件或多条件的查找并返回值,那么,只需要加上IF({1,0}就可以实现。
下面,我们就一起来看看IF({1,0}和VLOOKUP函数的经典结合使用例子吧。
我们要实现的功能是,根据Sheet1中的产品类型和头数,找到Sheet2中相对应的产品类型和头数,并获取对应的价格,然后自动填充到Sheet1的C列。实现此功能,就涉及到两个条件了,两个条件都必须同时满足。
如下图,是Sheet1表的数据,三列分别存放的是产品类型、头数和价格。
上图是一张购买产品的表,其中,购买产品的行数据,可能存在重复。如上图的三七头,就是重复数据。
现在,我们再来看第二张表Sheet2。
上表,是固定好的不存在任何重复数据的产品单价表。因为每种三七头对应的头数是不相同的,如果要找三七头的单价,那么,要求类型是三七头,同时还要对应于头数,这就是条件。
1
现在,我们在Sheet1中的A列输入三七头,在B列输入头数,然后,利用公式自动从Sheet2中获取相对应的价格。这样就免去了输入的麻烦。
公式比较复杂,因为难于理解,先看下图吧,是公式的应用实例。
C2的公式为:
{=VLOOKUP(A2&B2,IF({1,0},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12),2,FALSE)}
公式解释:
①VLOOKUP的解释
VLOOKUP函数,使用中文描述语法,可以这样来理解。
VLOOKUP(查找值,在哪里找,找到了返回第几列的数据,逻辑值),其中,逻辑值为True或False。
再对比如上的公式,我们不能发现。
A2&B2相当于要查找的值。等同于A2和B2两个内容连接起来所构成的结果。所以为A2&B2,理解为A2合上B2的意思。
IF({1,0},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12)相当于要查找的数据
=VLOOKUP(A9&B9&C9,IF({1,0},个人检查记录明细!$A$7:$A$225&个人检查记录明
细!$B$7:$B$225&个人检查记录明细!$C$7:$C225,个人检查记录明细!$D$7:$D$225),2,0) 2代表返回第二列的数据。最后一个是False。
②IF({1,0}的解释
刚才我们说了,IF({1,0},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12)相当于VLOOKUP函数中的查找数据的范围。
由于本例子的功能是,根据Sheet1中的A列数据和B列数据,两个条件,去Sheet2中查找首先找到对应的AB两列的数据,如果一致,就返回C列的单价。
因此,数据查找范围也必须是Sheet2中的AB两列,这样才能被找到,由于查找数据的条件是A2&B2两个单元格的内容,但是此二单元格又是独立的,因此,要想构造查找范围,也必须把Sheet2中的AB两列结合起来,那就构成了
2
Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12;Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12:相当于AB两列数据组成一列数据。
那么,前面的IF({1,0}代表什么意思呢?
IF({1,0},相当于IF({True,False},用来构造查找范围的数据的。最后的
Sheet2!$C$2:$C$12也是数据范围。
现在,整个IF({1,0},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12)区域,就形成了一个数组,里面存放两列数据。
第一列是Sheet2AB两列数据的结合,第二列数据是Sheet2!$C$2:$C$12。
公式
{=VLOOKUP(A2&B2,IF({1,0},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12),2,FALSE)}中的数字2,代表的是返回数据区域中的第二列数据。结果刚好就是Sheet2的C列,即第三列。因为在IF({1,0}公式中,Sheet2中的AB两列,已经被合并成为一列了,所以,Sheet2中的第三列C列,自然就成为序列2的列编号了,所以,完整的公式中,红色的2代表的就是要返回第几列的数据。
上面的完整的公式,我们可以使用如下两种公式来替代:
=VLOOKUP(A2&B2,CHOOSE({1,2},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$
12),2,FALSE)
=VLOOKUP(A2&B2,IF({TRUE,FALSE},Sheet2!$A$2:$A$12&Sheet2!$B$2:$B$12,Sheet2!$C$2:$C$12),2,FALSE)
关于Choose函数的使用示例
CHOOSE函数语法
函数功能:可以根据给定的索引值,从多达29个待选参数中选出相应的值。 函数语法:CHOOSE(index_num,value1,value2,...)。
参数介绍:excel中条件查找函数。
Index_num是用来指明待选参数序号的值,它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;
Value1,value2,...为1到29个数值参数,可以是数字、单元格,已定义的名称、公式、函数或文本。
实例1:公式“=CHOOSE(2,"大众","计算机") 返回“计算机”。因为参数2代表要返回第二个值,也就是“计算机”。
公式“=SUM(A1:CHOOSE(3,A10,A20,A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3,A10,A20,A30)返回A30)。
实例2:SUM(Choose(2,A1:A20,B3:B15))与SUM(B3:B15)等价。
再仔细看看一个实例:
公式:=Choose(要哪个,"第一个","第二个","第三个","第四个","第五个")
上述的值中,共有五个,想要哪个就在参数一那里填写序号,比如,想要第四个,那么,就这样来填写:
=Choose(4,"第一个","第二个","第三个","第四个","第五个")
3
注意哦,“要哪个”这个数字,必须在[1,29]这个范围;并且,值列表的个数,也必须在在[1,29]这个范围。
4
EXCEL中多条件查找的方法
篇三:excel中条件查找函数
EXCEL中多条件查找的15种方法 excel多条件查找的思路很多,例如查找、求和、
最值、数据库等函数等等。像SUM函数、lookup函数、VLOOKUP+CHOOSE函数、OFFSET+MATCH函数、INDIRECT+MATCH函数等等如下所示:
示例:
题目:如下图所示,根据第9行的产品和型号,从上面表中查找“销售数量”,结果如C10所示
SUM函数:
公式{=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)}
公式简介:使用(条件)*(条件)因为每行符合条件的为0,不符合的为1,所以只有条件都符合的为非零数字。所以SUM求和后就是多条件查找的结果
SUMPRODUCT函数:
公式:=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6)
公式简介:和SUM函数用法差不多,只是SUMPRODUCT函数不需要数组运算 MAX函数:
{=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)}
SUM是通过求和把符合条件的提出来,这里是使用MAX提取出最大值来完成符合条件的值提取。
lookup函数:
公式1=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6)
公式简介:LOOKUP函数可以直接进行数组运算。查找的连接起来,被查找区域也连接起来。
公式2:=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6)
公式3=LOOKUP(1,0/((A2:A6&B2:B6)=(A9&B9)),C2:C6)
公式4=LOOKUP(1,1/(((A2:A6=A9)+(B2:B6=B9))=2),C2:C6)
MIN+IF函数:
公式=MIN(IF((A2:A6=A9)*(B2:B6=B9),C2:C6))
SUM+IF函数:
公式
=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))
INDEX+MATCH函数组合:
公式1:{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))}
公式2:{=INDEX(C2:C6,MATCH(1,(A9=A2:A6)*(B9=B2:B6),0))}
OFFSET+MATCH函数:
公式
=OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),)
INDIRECT+MATCH函数:
公式
=INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0))
VLOOKUP+CHOOSE函数:
公式 :
=VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0)
HLOOKUP+TRANSPOSE+CHOOSE函数:
公式
=HLOOKUP(A9&B9,TRANSPOSE(CHOOSE({1,2},A2:A6&B2:B6,B2:C6)),2,0)
VLOOKUP+IF函数:
公式1 =VLOOKUP(A9&B9,IF({1,0},A2:A6&B2:B6,B2:C6),2,0)
公式2=VLOOKUP(A9&"|"&B9,A31:D35,4,0)„添加辅助列
SUMIFS函数:
excel2007中开始提供的函数SUMIFS =SUMIFS(C1:C6,A1:A6,A9,B1:B6,B9) 数据库函数:
=DSUM(A1:C6,3,A8:B9)
=DGET(A1:C6,3,A8:B9)
=DAVERAGE(A1:C6,3,A8:B9) =DMAX(A1:C6,3,A8:B9)
=DMIN(A1:C6,3,A8:B9)
=DPRODUCT(A1:C6,3,A8:B9)
excel多条件查找公式大全
篇四:excel中条件查找函数
excel多条件查找公式大全
发布时间:08-09 来源:兰色幻想-赵志东
今天学习多条件查找的方法,本文整理了10种excel多条件查找方法和公式。
【例】如下图所示,要求在C12单元格,根据A12车型和B12排量,从上表中查找库存数。
公式1:VLOOKUP函数(数组公式)
{=VLOOKUP(A12&B12,IF({1,0},A2:A9&B2:B9,C2:C9),2,0)}
注: 用if({1,0}结构重构数据
公式2:LOOKUP函数
=LOOKUP(1,0/(A2:A9=A12)*(B2:B9=B12),C2:C9)
注:用lookup(1,0/(条件) 结构完成查找
公式3:SUM函数(数组公式)
{=SUM((A2:A9=A12)*(B2:B9=B12)*C2:C9)}
注:用多条件求和的方法实现查找,前提是不能有重复的行和查找的内容为数字。 公式4:SUMIFS函数
=SUMIFS(C2:C9,A2:A9,A12,B2:B9,B12)
注:同
3
公式5:SUMPRODUCT函数
=SUMPRODUCT((A2:A9=A12)*(B2:B9=B12)*C2:C9)
注:同3
公式6:MAX函数(数组公式)
{=MAX((A2:A9=A12)*(B2:B9=B12)*C2:C9)}
注:用求最大值的方法,实现查找,前提是查找内容为数字。
公式7:INDEX+MATCH函数(数组公式)
{=INDEX(C2:C9,MATCH(A12&B12,A2:A9&B2:B9,0))}
注:match函数支持数组,所以可以把区域直接连接起来,不过需要使用数组形式输入。 公式8:OFFSET+MATCH函数(数组公式)
{=OFFSET(C1,MATCH(A12&B12,A2:A9&B2:B9,0),)}
注:同7
公式9:INDIRECT+MATCH函数(数组公式)
{=INDIRECT("C"&MATCH(A12&B12,A1:A9&B1:B9,0))}
注:同7
公式10:DGET函数
=DGET(A1:C9,3,A11:B12)
注:Dget函数是数据库函数,可以用来条件查找。第3个参数必须是单元格区域作为条件引用。
http://m.myl5520.com/fanwendaquan/124139.html