【www.myl5520.com--实用内容】
EXCEL中多条件查找并引用数据的方法探讨
篇一:excel多条件查找
EXCEL中多条件查找并引用数据的方法探讨
EXCEL中多条件查找并引用数据的方法探讨
在实际工作中，我们经常会遇到需要对满足两个以上条件的数据进行查找并引用的问题，SHEET1工作表内容如图：
现在要求在SHEET2工作表的A、B列输入有关内容后，C列自动从SHEET1工作表中查找并引用相应的C列的内容。SHEET2工作表如图：
SHEET2工作表C1单元格使用以下数组公式，可达到目的：
=IF(OR(A1="",B1=""),"",OFFSET(Sheet1!$C$1,SUM(IF((Sheet1!A$1:A$1000=A
1)*(Sheet1!B$1:B$1000=B1),ROW(Sheet1!C$1:C$1000),0))-1,0,1,1)) 注意:输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
补充:
用VLOOKUP函数解决方法:
=IF(OR(A1="",B1=""),"",VLOOKUP(A1&B1,IF({1,0},Sheet1!A$1:$A$1000&Sheet1!B$1:B$1000,Sheet1!C$1:C$1000),2,0))
用INDEX和MATCH函数解决方法:
=IF(OR(A1="",B1=""),"",INDEX(Sheet1!C$1:C$1000,MATCH(A1&B1,Sheet1!A$1
:A$1000&Sheet1!B$1:B$1000,0)))
这两个也是数组公式。
另提供两个不用数组公式的解决方法：
=IF(OR(A1="",B1=""),"",INDIRECT("Sheet1!$C"&SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)*ROW(Sheet1!C$1:C$1000))))
=IF(OR(A1="",B1=""),"",LOOKUP(2,1/((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)),Sheet1!C$1:C$1000))
用条件格式检查身份证校验码是否正确的方法
2010-07-30 18:09
18位身份证校验码是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。
为什么除11，在于计算校验码时的函数。请看下边的函数： 公式如下：
∑(a[i]*W[i]) mod 11 ( i = 2, 3, ..., 18 ) （公式一） "*" 表示乘号
i--------表示身份证号码每一位的序号，从右至左，最左侧为18，最右侧为1。 a[i]-----表示身份证号码第 i 位上的号码
W[i]-----表示第 i 位上的权值 W[i] = 2^(i-1) mod 11
上述计算方法是从右至左，如果从左至右，第1-17位权重W[i]的计算结果为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
计算公式一 令结果为 R
根据下表找出 R 对应的校验码即为要求身份证号码的校验码C。
R 0 1 2 3 4 5 6 7 8 9 10
C 1 0 X 9 8 7 6 5 4 3 2
由此看出 X 就是 10，罗马数字中的 10 就是X，所以在新标准的身份证号码中可能含有非数字的字母X。
根据上述校验码计算公式和原理，设置条件格式检查校验码方法如下：excel多条件查找。
假定身份证在B列（均为18位），检查其校验码是否正确，不正确的用红色字体显示。方法如下：
选中B列，“格式”/“条件格式”，将条件设为：
公式
=RIGHT(B1)<>MID("10X98765432",MOD(SUMPRODUCT(MID(B1,ROW($1:$17),1)*MID("0709100508040201060307091005080402",ROW($1:$17)*2-1,2)),11)+1,1)
点“格式”，将字体设为红色即可。
excel多条件查找14种思路
篇二:excel多条件查找
excel多条件查找15种思路
题目：如下图所示，根据第9行的产品和型号，从上面表中查找“销售数量”，结果如C10所示
公式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函数
公式{=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)} 公式简介：使用(条件)*（条件)因为每行符合条件的为0，不符合的为1，所以只有条件都符合的为非零数字。所以SUM求和后就是多条件查找的结果
=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))excel多条件查找。
INDEX+MATCH函数组合
公式1：
{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))}
SUMPRODUCT函数
公式：
=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6) 公式简介：和SUM函数用法差不多，只是SUMPRODUCT函数不需要数组运算
公式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
MAX函数
{=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)} SUM是通过求和把符合条件的提出来，这里是使用MAX提取出最大值来完成符合条件的值提取
),)
INDIRECT+MATCH函数
=INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0))
lookup函数
公式1=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6) 公式简介：LOOKUP函数可以直接进行数组运算。查找的连接起来，被查找区域也连接起来。
VLOOKUP+CHOOSE函数
=VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0)
1
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)
2
Excel中如何用vlookup函数进行多条件查找引用
篇三:excel多条件查找
Excel中如何用vlookup函数进行多条件查找引用
我们平时用Excel中的vlookup函数以单条件查找引用为主，实际工作中很多时候查找条件不止一个，那么怎么办呢？下面就分享用vlookup函数进行多条件查找的用法。
根据A列班级和B列姓名查找C列对应的得分，怎么用呢？请看下图。 上图中公式输入完成以后，因为公式内含有数组，所以同时按下CTRL+SHIFT+ENTER
即可。下面对该函数进行分段解释.
解释要点
先看公式中的E2&F2，它表示将两个单元格连在一起当做一个整体进行查找。
A2:A7&B2:B7表示的意思与上面基本一致，就是班级和姓名作为一个整体。
IF({1,0},A2:A7&B2:B7,C2:C7)表示将班级和姓名作为一个整体，然后与得分列进行调换，
最后，其实完全还是通过vlookup函数求解，只不过将多条件连在一起作为单条件进行查找。好了，就到这里了，希望对您有帮助。
EXCEL中多条件查找的方法
篇四: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)