【www.myl5520.com--实用内容】
EXCEL中多条件查找并引用数据的方法探讨
篇一:lookup多条件引用execl
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多条件引用查询及反向引用查询
篇二:lookup多条件引用execl
大家在excel中可能要经常用到查询引用的函数，我就在这里介绍一下几个查询函数的用法，有不对的地方希望大家指出 单条件引用你只要用lookup()或者vlookup()就能解决，如果多条件引用查询可能就有点麻烦，下面我就举个例子，我就假设，你有三列数据分别在，A,B,C三列内容如下
产品名称 产品规格 数量
A 1-2 20
A 1-3 25
B 2-1 24
如果你要根据前两列内容引用第三列的话，假设你要在D1中输入产品名称，E1中输入产品规格，想在F1中自动显示对应的数量的话，你可以这这样设置函数
=INDEX($C$2:$C$100,MATCH(D2&E2,$A$2:$A$100&$B$2:$B$100,))
但已经变成数组公式了。数组公式要按Ctrl+Shift+Enter结束公式输入。
如果不想要数组公式
=SUMPRODUCT(($A$2:$A$100=D2)*($B$2:$B$100=E2)*$C$2:$C$100)
但这个公式要保证产品名称加产品规格具有唯一性，否则就是求和了。lookup多条件引用execl。
如果C列数据在前面,你可以这样设置函数（也就是反向引用查询）
=INDEX($A$2:$A$100,MATCH(D2&E2,$B$2:$B$100&$C$2:$C$100,))
但已经变成数组公式了。数组公式要按Ctrl+Shift+Enter结束公式输入。
如果不想要数组公式
=SUMPRODUCT(($B$2:$B$100=D2)*($C$2:$C$100=E2)*$A$2:$A$100)
但这个公式要保证产品名称加产品规格具有唯一性，否则就是求和了。
如果是用VLOOKUP
=VLOOKUP(D2&E2,IF({1,0},$B$2:$B$100&$C$2:$C$100,$A$2:$A$100),2,)
不过也是数组公式了。
Chr(charcode)
必要的 charcode 参数是一个用来识别某字符的 Long。 说明
0 到 31 之间的数字与标准的非打印 ASCII 代码相同。例如，Chr(10) 可以返回换行字符。charcode 的正常范围为 0 – 255。然而，在 DBCS 系统，charcode 的实际范围为 -32768 到 65535。
注意 ChrB 函数作用于包含在 String 中的字节数据。ChrB 总是返回一个单字节，而不是返回一个字符，一个字符可能是一个或两个字节。ChrW 函数返回包含 Unicode 的 String，若在不支持 Unicode 的平台上，则其功能与 Chr 函数相同。
注意 Visual Basic for the Macintosh 不支持Unicode 字符串。因此，当n 值在128 – 65,535 范围内时， ChrW(n) 不能像在Windows环境中那样返回所有的 Unicode 字符。相反地，当Unicode 的n 值大于127 时，ChrW(n) 会试图做一个“最好的猜测”。因此，在Macintosh 环境中，不能使用ChrW 。 我们在Execl的使用中主要应用的字符集为0—127，现将其值张贴如下：
0 · 32 [space] 64 @ 96 `
1 · 33 ! 65 A 97 a
2 · 34 " 66 B 98 b
3 · 35 # 67 C 99 c
4 · 36 $ 68 D 100 d
5 · 37 % 69 E 101 e
6 · 38 & 70 F 102 f
7 · 39 ' 71 G 103 g
8 * * 40 ( 72 H 104 h
9 * * 41 ) 73 I 105 i
10 * * 42 * 74 J 106 j
11 · 43 + 75 K 107 k
12 · 44 , 76 L 108 l
13 * * 45 - 77 M 109 m
14 · 46 . 78 N 110 n
15 · 47 / 79 O 111 o
16 · 48 0 80 P 112 p
17 · 49 1 81 Q 113 q
18 · 50 2 82 R 114 r
19 · 51 3 83 S 115 s 20 · 52 4 84 T 116 t 21 · 53 5 85 U 117 u 22 · 54 6 86 V 118 v 23 · 55 7 87 W 119 w 24 · 56 8 88 X 120 x 25 · 57 9 89 Y 121 y 26 · 58 : 90 Z 122 z 27 · 59 ; 91 [ 123 { 28 · 60 < 92 \ 124 | 29 · 61 = 93 ] 125 } 30 · 62 > 94 ^ 126 ~ 31 · 63 ? 95 _ 127 • · Microsoft Windows 不支持这些字符。
* *值 8、9、10 和 13 分别转换为退格、制表、换行和回车字符。它们并没有特定的图形显示，但会依不同的应用程序，而对文本显示有不同的影响。
EXCEL中多条件查找的方法
篇三:lookup多条件引用execl
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))lookup多条件引用execl。
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 15种方法多条件查找
篇四:lookup多条件引用execl
excel多条件查找15种思路
题目：如下图所示，根据第9行的产品和型号，从上面表中查找“销售数量”，结果如C10所示lookup多条件引用execl。
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)