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 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])，返回表格或区域中的值或值的引用。函数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中有数据：

那么再选中一个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()”：