【www.myl5520.com--个人简历表格】
EXCEL中多条件查找的15种方法
篇一:满足双重条件查找引用
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中多条件查找并引用数据的方法探讨
在实际工作中，我们经常会遇到需要对满足两个以上条件的数据进行查找并引用的问题，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。
根据上述校验码计算公式和原理，设置条件格式检查校验码方法如下：
假定身份证在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中多条件查找并引用数据的方法
篇三:满足双重条件查找引用
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=A1)*(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)) support.microsoft.com/kb/275170/zh-cn
关键点：（目前只试验了2个条件）
1.使用index函数与Match函数
2.公式格式与参数说明：index(结果查找范围,Match(条件1&条件2,条件1查找范围&条件2查找范围,0))
3.在输出的结果栏中输入以上公式
如：=INDEX(Sheet4!$C$2:$C$26,MATCH(B2&D2,Sheet4!$A$2:$A$26&Sheet4!$B$2:$B$26,0))
4.按 Shift + Ctrl+Enter 将公式作为数组公式输入
实用软件应用技巧 考试82分
篇四:满足双重条件查找引用
(一) 单选题：每题1分，共30题，只有唯一一个选项正确 1. 美图秀秀软件是由（）开发制作完成 (A) 新浪网 (B) 美图网 (C) 搜狐网 (D) 腾讯 [分值：2.
Word常用工具栏中的格式刷可用于复制文本或段落的格式，若要将格式重复应用多次，应（）
(A)
单击格式刷
(B)
双击格式刷
(C)
右击格式刷
(D)
拖动格式刷
[分值：3.
下列对象中，不能使用格式刷复制格式的是（）
(A)
图片
(B)
文本
(C)
艺术字
(D)
自选图形
[分值：4.
在Excel中若要将第3行和B列固定在窗口中，应选中
（）再执行冻结窗格命令
(A)
B3
(B)
C3
(C) B4
(D)
C4
[分值：5.
Word中选定栏位于页面的（）
(A)
上边距
(B)
下边距
(C)
左边距
(D)
右边距
[分值：
6. 下列Excel的单元格引用中，属于绝对地址引用的是（） (A) $A2 (B) C$ (C) E8 (D) $G$9 [分值：7.
在一个单元格中若输入了“0 1/2”，确认后应显示为（）
(A)
1/2
(B)
1月
2日
(C)
01/2
(D)
0.5
[分值：8.
Excel中如果要选中所有工作表，应先单击第一张工作表的工作表标签，按下（）键，再单击最后一张工作表的工作表标签
(A)
Shift
(B)
Tab
(C)
Ctrl
(D)
Alt