返回目录:excel表格制作
这是我之前一篇头条文章可以解答您的疑问,https://www.toutiao.com/i6766204432771187204/
如下图所示,要求根据设备分类和品牌来查找相应的销售数量。
1. VLOOKUP+辅助列
思路:把多条件进行合并,利用辅助列将其转换为单条件进行查找。
具体方法是在数据区域的最前面添加辅助列(由于VLOOKUP函数的特性,该辅助列只能放在数据区域的最前面),A2处的辅助列公式为:=B2&C2。然后再采用VLOOKUP函数按单条件进行查找,H2处的公式为=VLOOKUP(F2&G2,A1:D8,4,0)。VLOOKUP函数基础应用
2. VLOOKUP+IF函数
思路:通过IF函数构建数组将多条件合并为单条件。
H2处公式为:=VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0),这是一个数组公式,按CTRL + SHIFT +ENTER完成输入。Excel数组的输入,VLOOKUP函数之多条件查找
公式中的IF({1,0},A2:A8&B2:B8,C2:C8)是一个数组公式,它的返回值为:{"电脑联想",1546;"手机三星",1452;"电脑苹果",225;"手机苹果",2364;"电视机小米",154;"电视机三星",225;"手机小米",149},这其实也是把多条件通过内存数组合并为一个条件来进行查找。
3. 使用SUM进行多条件查找
思路:分别对各个条件进行判断,使用数组公式的相乘功能来变相实现查找功能。
SUM是求和公式,但在本例中用它来进行查找。
这是一个数组公式:=SUM((A2:A8=E2)*(B2:B8=F2)*C2:C8),按CTRL + SHIFT +ENTER完成输入。数组的运算
4. 使用SUMPRODUCT进行多条件查找
SUMPRODUCT返回数组乘积之和,所用公式如下所示:
=SUMPRODUCT((A2:A8=E2)*(B2:B8=F2)*C2:C8),它的实现原理和上面的SUM函数类似。
5. LOOKUP的多条件查找
LOOKUP的万金油查找公式完全可以实现多条件查找:=LOOKUP(1,1/((A2:A8=E2)*(B2:B8=F2)),C2:C8)
6. VLOOKUP和CHOOSE函数组合
VLOOKUP和CHOOSE函数组合为数组公式进行查询:=VLOOKUP(E2&F2,CHOOSE({1,2},A2:A8&B2:B8,C2:C8),2,0),这是一个数组公式,按CTRL + SHIFT + ENTER完成输入。Excel--CHOOSE函数简介
CHOOSE函数构建了一个内存数组:
{"电脑联想",1546;"手机三星",1452;"电脑苹果",225;"手机苹果",2364;"电视机小米",154;"电视机三星",225;"手机小米",149},然后使用VLOOKUP函数进行查找,这也相当于变相将多条件转换为单条件,从而实现查询效果。
综上几种方法,大都是利用函数转换的方法,将多条件查询转为单条件,从而实现查询功能。
用vlookup或index+match都可以实现,篇幅关系,先讲解vlookup方法。
关于index+match的方法,欢迎关注我,查看相关文章。
案例:
如下图所示,如何按照三门课的成绩,查找出姓名?
解决方案 :
1. 先给下表的 F 至 H 列制作下拉菜单
2. 选中 F2 单元格 --> 选择菜单栏的“数据”-->“数据有效性”-->“数据有效性”
3. 在弹出的对话框中选择“设置”--> 在“允许”中选择“序列”--> 在“来源”中输入“优,良,中”--> 确定
* 请注意:“优,良,中”中间的逗号是英文半角符号
4. “语文”下拉菜单制作好了,选中 F2 单元格,向右拖动,即可完成“数学”、“英语”的下拉菜单制作
5. 在 I2 单元格输入以下公式 --> 同时按下Ctrl+Shift+Enter 键即可:
=VLOOKUP(F2&G2&H2,IF({1,0},B:B&C:C&D:D,A:A),2,0)
公式释义:
F2&G2&H2:将三门课的成绩连接成一个字符串,作为查找条件
IF({1,0},B:B&C:C&D:D,A:A):
{1,0} 表示 true or false
B:B&C:C&D:D:表示将三个条件区域拼接成一个字符串来匹配上面的目标字符串
A:A:需要返回的结果列
2:表示返回第 2 列的结果,即 A 列
0:表示精确查找
* 请注意:这是个数组公式,所以必须同时按下Ctrl+Shift+Enter 键才能生效,按下以后可以看到公式外面多出来一对 { },表示数组公式应用成功
6. 这就完成了