excel多条件查询

excel多条件查询 EXCEL多条件查询2012年04月22日 15:44:43 1(概述 前几天群里面有人提出一个涉及到多条件查询问题，用函数解决此问题需要很高的技巧，需要用到之前讲解的几乎所有内容，相信认真学习完本文，粉丝们的Excel函数使用水平定有很大提高。 其实对数据进行多条件查询，笔者推荐首选的方法是在数据库中Select ××或者在Excel中使用VBA，但作为Excel的高级应用，在这里还是要讲一下如何通过使用函数实现，SQL查询和VBA就不进行讨论，有需要的可以去讨论组中探讨。 2(基本函数 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 实现多条件查询有很多种方法，用到的主要函数无非是常用的几个查询函数Sumproduct、Sum、Vlookup和Index+Match。其中Sum、Vlookup和Index+Match需要数组操作(同时按下Shift+Ctrl+Enter)，Sumproduct本来就是数组函数，直接回车即可。 2.1 Sumproduct SUMPRODUCT(array1, [array2], [array3], ...)，来自Excel帮助的官方解释是:在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 在本文的用法是SUMPRODUCT( (条件1)*(条件2) *(…) )，这里星号 “*”的意义不是相乘，而是同时满足条件1、条件2等几个条件的结果。 2.2 Sum 看到Sum函数好像不太对劲，Sum不是求和函数么, SUM(number1,[number2],...])，来自Excel帮助的官方解释是:将您指定为参数的所有数字相加。每个参数都可以是区域、单元格引用、数组、常量、公式 。 本文的用法是Sum的另一种用法，SUM ( (条件1)*(条件2) *(…) )，星号“*”的意义同SUMPRODUCT，返回同时满足条件1、条件2等几个条件的结果。 2.3 Vlookup Vlookup函数在前几次讲解中已详细讲过，详见“VLOOKUP()函数基础”和“Excel函数讲解之vlookup() & iferror()/iserror()”。 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])，搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。 2.4 Index INDEX(array, row_num, [column_num])，返回 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 格或区域中的值或值的引用。函数 INDEX 有两种形式:数组形式和引用形式。 本文用到它的数组形式，INDEX(A1:C10, 2,3)意思是返回区域A1:C10的第2行第3列即C2的值。Index经常和Match函数配合使用。 2.5 Match MATCH(lookup_value, lookup_array, [match_type])，在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。它的用法在“Hello World”中涉及过，当初笔者给了它的使用示例，只是没有深入讲解，作为大家自己思考学习的函数，不知看过那篇文章的粉丝们，现在对Match函数的了解成度如何, MATCH("x", {"e";"x";"c";"e";"l"}, 0)就是在数组{"e";"x";"c";"e";"l"}中精确查找“x”第一次出现的位置，返回2，第2行第一次出现。 2.6 If If函数应该是大家常用的函数，IF(logical_test, [value_if_true], [value_if_false])。不过笔者此处要介绍一下它的数组用法:IF({1,0}, [value_if_true], [value_if_false])，返回一个(value_if_true)& (value_if_false)的数组。 看例子，比如A1:C3中有数据: 类型 1 值 1 No.1 类型 2 值 2 No.2 类型 3 值 3 No.3 那么再选中一个3×2区域，比如说A6:B8，输入公式后，注意Shift+Ctrl+Enter。 1、输入“=IF({1,0},A1:A3,B1:B3)” Shift+Ctrl+Enter，类型 1 No.1 得到结果如右: 类型 2 No.2 类型 3 No.3 2、输入“=IF({1,0},A1:A3&B1:B3,C1:C3)” No.1类型 1 值 1 Shift+Ctrl+Enter，得到结果如右: No.2类型 2 值 2 No.3类型 3 值 3 3、输入“=IF({1,0},A1:A3,B1:B3&C1:C3)” 类型 1值 1 No.1 Shift+Ctrl+Enter，得到结果如右: 类型 2值 2 No.2 类型 3值 3 No.3 当然，可以不用把得到的结果放在Excel单元格里面，可以作为一个公式的引用，例如用在Vlookup里面，对于上表中2: VLOOKUP("No.1类型 1",IF({1,0},A1:A3&B1:B3,C1:C3),2,0) Shift+Ctrl+Enter，得到返回值“值 1”。 3(实例应用 3.1参考实例 Sheet1!A1:I31中有如下数据: 学年 学期 课程代码 课程名称 课程性质 学分 成绩 学院名称 高等数学(工本上) 公共基础课 数理学院 2007-2008 1 4110211 6 97 大学物理(上) 公共基础课 数理学院 2007-2008 1 4110012 3 75 高等数学(工本下) 公共基础课 数理学院 2007-2008 2 4110250 6 95 足球初级班 公共基础课 体育部 2007-2008 2 4210251 1 95 土木工程制图 专业基础课 土木工程学院 2007-2008 2 1411010 1.5 95 大学物理(下) 公共基础课 数理学院 2008-2009 1 4110011 3 90 经济管理基础 公共基础课 经济管理学院 2008-2009 1 1816130 3 85 理论力学 专业基础课 机械工程学院 2008-2009 1 1111100 3 78 土木工程材料 专业基础课 土木工程学院 2008-2009 1 1411030 3 83 线性代数 公共基础课 数理学院 2008-2009 1 4110710 2 85 材料力学 专业基础课 机械工程学院 2008-2009 2 1111030 3.5 96 测量学3 专业基础课 土木工程学院 2008-2009 2 1411051 3 78 概率论与数理统计 公共基础课 数理学院 2008-2009 2 4110750 3 91 计算机绘图 专业基础课 土木工程学院 2008-2009 2 1414521 1.5 95 乒乓球中级班 公共基础课 体育部 2008-2009 2 4210262 1 95 钢结构 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 原理 专业基础课 土木工程学院 2009-2010 1 1411100 2.5 69 工程地质学 专业基础课 土木工程学院 2009-2010 1 1411391 1.5 91 工程经济学 专业基础课 土木工程学院 2009-2010 1 1411160 2 95 混凝土结构基本原理 专业基础课 土木工程学院 2009-2010 1 1411640 4 86 结构力学 专业基础课 土木工程学院 2009-2010 1 1411070 4 90 土力学 专业基础课 土木工程学院 2009-2010 1 1421080 2 82 基础工程 专业基础课 土木工程学院 2009-2010 2 1411130 2 82 建筑法规 专业任选课 土木工程学院 2009-2010 2 1413361 1 85 路基路面工程 专业限选课 土木工程学院 2009-2010 2 1411270 2.5 88 水力学与桥涵水文 专业限选课 土木工程学院 2009-2010 2 1411790 2.5 87 土木工程施工 专业基础课 土木工程学院 2009-2010 2 1411090 3.5 86 现代预应力设计 专业任选课 土木工程学院 2009-2010 2 1411360 2 84 城市道路设计 专业限选课 土木工程学院 2010-2011 1 1411290 2 81 桥梁工程 专业限选课 土木工程学院 2010-2011 1 1411281 2.5 73 桥梁施工 专业任选课 土木工程学院 2010-2011 1 1411380 2 78 查询要求:按照课程代码和课程性质来查询成绩。 其实在数据库中用SQL语言查询就是“Select 成绩 From 成绩表 Where 课程代码=’4110211’ And 课程性质=’公共基础课’”，使用VBA就是在For语句中套几个If和Find，但在这里面就需要函数的组合了。 当然这里的课程代码是惟一属于某一课程性质的，但如果不唯一，例如课程“4110211”同时属于专业任选课和专业限选课;某几家店铺同时出售某几种商品，查出这些商品在不同店家的价格等等，这样查找起来会更突显多条件查找的意义。 Sheet2!A1:E32区域如下表，我们要用四种方法分别编写公式一二三四。 课程性质 课程代码 (Sheet2!A1) 公共基础课 专业基础课 专业任选课 专业限选课 公式一 公式二 公式三 公式四 4110211 4110012 4110250 4210251 1411010 4110011 1816130 1111100 1411030 4110710 1111030 1411051 …… …… 1411380 3.2 解决问题 3.2.1方法一Sumproduct 用Sumproduct来查询同时满足课程代码和课程性质为公共基础课的各课程成绩。公式一(直接回车): =SUMPRODUCT((Sheet1!$C$2:$C$31=A3)*(Sheet1!$E$2:$E$31=$B$2)*Sheet1!$G$2:$ G$31) 意思是返回同时满足Sheet1!$C$2:$C$31=A3(课程代码)和Sheet1!$E$2:$E$31=$B$2(课程性质)和Sheet1!$G$2:$G$31(成绩)的值。 注意Sumproduct和Sum只能查找全部为数字的值，如果把“95”换成“优秀”，则出错#Value，若查不到相应的记录不会出错“#N/A”，而是返回“0”。 3.2.2方法二Sum 用Sum来查询同时满足课程代码和课程性质为专业基础课的各课程成绩。公式二(Shift + Ctrl + Enter): =SUM((Sheet1!$C$2:$C$31=A3)*(Sheet1!$E$2:$E$31=$C$2)*Sheet1!$G$2:$G$31) 这里只是把Sumproduct换成Sum，并且采用数组操作，满足条件2(Sheet1!$E$2:$E$31 = $C$2)是否可以采用Offset函数来完成，笔者还没尝试，有兴趣的可以尝试一下。 3.2.3方法三Index + Match 用index + match来查询同时满足课程代码和课程性质为专业任选课的各课程成绩。公式三 (Shift + Ctrl + Enter): =INDEX(Sheet1!$G$2:$G$31,MATCH(A3&$D$2,Sheet1!$C$2:$C$31&$E$2:$E$31,0)) Index查找区域是Sheet1!$G$2: $G$31(成绩列)，匹配行数为Match匹配结果，因为 Sheet1!$G$2:$G$31只有一列，所以省略了列数[column_num]; “&”将两个单元格合并成一个1×1数组、两列合并成一列得到30×1数组，用Match查找; 若找不到符合条件的内容，则返回错误#N/A，肿么办,还记得IfError么,见“Excel函数讲 解之vlookup() & iferror()/iserror()”: =IFERROR(INDEX(Sheet1!$G$2:$G$31,MATCH(A3&$D$2,Sheet1!C2:C31&Sheet1!E2:E31 ,0)),"") 3.2.4方法四Vlookup 用Vlookup来查询同时满足课程代码和课程性质为专业限选课的各课程成绩。公式四(Shift + Ctrl + Enter): =VLOOKUP(A3&$E$2,IF({1,0},Sheet1!$C$2:$C$31&Sheet1!$E$2:$E$31,Sheet1!$G$2:$G $31),2,0) 查找值是A3&$E$2(课程代码专业限选课)，查找区域是If生成的一个数组，偏移值2， 精确查找。 踢除错误值，还是用iferror/iserror。 3.3 进阶 3.3.1自动判断总行数 拿公式一来说: =SUMPRODUCT((Sheet1!$C$2:$C$31=A3)*(Sheet1!$E$2:$E$31=$B$2)*Sheet1!$G$ 2:$G$31) 第一个条件Sheet1!$C$2:$C$31可以用 Sheet1!$C$2:INDEX(Sheet1!C:C,COUNTA(Sheet1!C:C))来替换， Sheet1!$C$2:INDEX(Sheet1!C:C,COUNTA(Sheet1!C:C))意思是C列所有有数据的单元格，无论Sheet1 中C列再追加多少行，其返回值都是C列所有数据，当源数据Sheet1需要追加记录时，Sheet2中的查找公式不需要改变。 3.3.2完全数组 3.2中解决问题的方法是在某个单元格中输入公式，然后往下托，但既然用数组了，那就全部用数组，还记得之前的数组操作的状态么, 方法一和方法二用Sumproduct和Sum，全部使用数组会唤醒他们求和的本性;方法三和方法四可以再行改进:完全采用数组的方式。 方法三，全选专业任选课区域D3:D32，输入公式 =IFERROR(INDEX(Sheet1!$G$2:$G$31,MATCH(A3&$D$2,Sheet1!$C$2:$C$31&Sheet1!$E $2:$E$31,0)),"") Shift + Ctrl + Enter，OK，整列搞定。 方法四，全选专业限选课区域E3:E32，输入公式 =IFERROR(VLOOKUP(A3:A32&$E$2,IF({1,0},Sheet1!$C$2:$C$31&Sheet1!$E$2:$E$31,S heet1!$G$2:$G$31),2,0),"") Shift + Ctrl + Enter。