EXCEL中多条件查找并引用数据的方法探讨借鉴.pdfVIP

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(A1B1,IF({1,0},Sheet1!A$1:$A$1000Shee t1!B$1:B$1000,Sheet1!C$1:C$1000),2,0)) 用 INDEX和 MATCH函数解决方法 : =IF(OR(A1=,B1=),,INDEX(Sheet1!C$1:C$1000,MATCH(A1B1,Sheet1!A$1 :A$1000Sheet1!B$1:B$1000,0))) 这两个也是数组公式。 另提供两个不用数组公式的解决方法： =IF(OR(A1=,B1=),,INDIRECT(Sheet1!$CSUMPRODUCT((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(10MOD(SUMPRODUCT(MID(B1,ROW($1:$17),1)*MI D(0709100508040201060307091005080402,ROW($1:$17)*2-1,2)),11)+1,1) 点“格式”，将字体设为红色即可。