EXCEL多条件查询

EXCEL多条件查询1．概述前几天群里面有人提出一个涉及到多条件查询问题，用函数解决此问题需要很高的技巧，相信认真学习完本文，粉丝们的Excel函数使用水平定有很大提高。

其实对数据进行多条件查询，笔者推荐首选的方法是在数据库中Select ××或者在Excel 中使用VBA，但作为Excel的高级应用，在这里还是要讲一下如何通过使用函数实现，SQL 查询和VBA就不进行讨论，有需要的可以去讨论组中探讨。

2．基本函数说明实现多条件查询有很多种方法，用到的主要函数无非是常用的几个查询函数Sumproduct、Sum、Vlookup和Index+Match。

其中Sum、Vlookup和Index+Match需要数组操作（同时按下Shift+Ctrl+Enter），Sumproduct本来就是数组函数，直接回车即可。

2.1 SumproductSUMPRODUCT(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 VlookupVlookup函数在前几次讲解中已详细讲过，详见“VLOOKUP()函数基础”和“Excel函数讲解之vlookup() & iferror()/iserror()”。

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])，搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。

2.4 IndexINDEX(array, row_num, [column_num])，返回表格或区域中的值或值的引用。

函数INDEX 有两种形式：数组形式和引用形式。

本文用到它的数组形式，INDEX(A1:C10, 2,3)意思是返回区域A1：C10的第2行第3列即C2的值。

Index经常和Match函数配合使用。

2.5 MatchMATCH(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 IfIf函数应该是大家常用的函数，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中有数据：那么再选中一个3×2区域，比如说A6:B8，输入公式后，注意Shift+Ctrl+Enter。

当然，可以不用把得到的结果放在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参考实例查询要求：按照课程代码和课程性质来查询成绩。

其实在数据库中用SQL语言查询就是“Select 成绩From 成绩表Where 课程代码=’4110211’ And 课程性质=’公共基础课’”，使用VBA就是在For语句中套几个If和Find，但在这里面就需要函数的组合了。

当然这里的课程代码是惟一属于某一课程性质的，但如果不唯一，例如课程“4110211”同时属于专业任选课和专业限选课；某几家店铺同时出售某几种商品，查出这些商品在不同店家的价格等等，这样查找起来会更突显多条件查找的意义。

Sheet2!A1:E32区域如下表，我们要用四种方法分别编写公式一二三四。

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。

晚安地球人2012年4月22日。