Excel多条件查找的多种思路

目录

示例    2

一、SUM函数    2

二、SUMPRODUCT函数    2

三、MAX函数    4

四、lookup函数    4

五、MIN+IF函数    4

六、SUM+IF函数    4

七、INDEX+MATCH函数组合    4

八、OFFSET+MATCH函数    5

九、INDIRECT+MATCH函数    5

十、VLOOKUP+CHOOSE函数    5

十一、VLOOKUP函数    5

十二、HLOOKUP+TRANSPOSE+CHOOSE函数    5

十三、VLOOKUP+IF函数    5

十四、SUMIFS函数    6

十五、数据库函数    6

示例

题目：如下图所示，根据第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函数不需要数组运算

用函数SUMPRODUCT计算符合多条件的数据和，其基本格式是：SUMPRODUCT（条件1*条件2*……，求和数据区域）

例1：

在任意单元格内输入公式

=SUMPRODUCT((H20:H24="cc")*(I19:L19="B"),I20:L24)

得到的答案均为1，公式解释：同时满足H20:H24="cc"和I19:L19="B"时，在I20:L24范围内寻找对应的数据

例2：

B26中输入公式：SUMPRODUCT(($A$2:$A$21=B24)*($B$2:$B$21=$A$26)*$C$2:$C$21)

B27中输入公式：

SUMPRODUCT(($A$2:$A$21=B24)*($B$2:$B$21=$A$27)*$C$2:$C$21)

这个函数对于提供了基础数据后整理数据有很好的用处。

三、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)

十一、VLOOKUP函数

公式=VLOOKUP(C63&D63&E63&F63,B52:G60,6,0)

将两个条件用&连接起来

解决错误数据的公式：=IF(ISERROR(VLOOKUP(C63&D63&E63&F63,B52:G60,6,)),"无记录",VLOOKUP(C63&D63&E63&F63,B52:G60,6,))

十二、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)