美轮美奂的深圳人才公园 在《Excel中反向查找方法大全》中,讲解了实现单一条件查找的四种方法,遇到多条件查找需求怎么办?本文就主要来讲解多条件查找的十种实现方法。 如下B:D列是产品不同型号的单价信息,要求根据B13和C13两个单元格对应的产品和型号,在D13单元格查找对应的单价。 实现非常之多,逐一介绍如下: 【1】VLookup函数: D13单元格公式: {=VLOOKUP(B13&C13,IF({1,0},B2:B9&C2:C9,D2:D9),2,FALSE)} B13&C13 : 多条件构成的组合查询条件 B2:B9&C2:C9 :数组运算返回组合的查询区域 IF({1,0},B2:B9&C2:C9,D2:D9):返回组合的查询区域和结果区域 VLOOKUP按常规正向查询返回结果 特别提醒: 因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。 【2】Lookup函数: D13单元格公式: =LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13)),D2:D9) ((B2:B9=B13)*(C2:C9=C13)) 符合多条件的返回1,否则返回0 0/((B2:B9=B13)*(C2:C9=C13))符合多条件的返回0,否则返回0/0的错误值 =LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13)),Lookup函数在查找的时候是忽略错误的,所以返回小于1的最大值位置,即符合条件的0值位置,从而返回D2:D9对应位置的单价。 理解0/((B2:B9=B13)*(C2:C9=C13))的目的就是将正确结果用0表示,其他的变成错误值,利用Lookup函数查找忽略错误这个特点完成查找。 【3】Index Match函数: D13单元格公式: {=INDEX(D2:D9,MATCH(B13&C13,B2:B9&C2:C9,0))} B13&C13 : 多条件构成的组合查询条件 B2:B9&C2:C9 :数组运算返回组合的查询区域 MATCH(B13&C13,B2:B9&C2:C9,0):返回组合查询条件满足的行序号 INDEX(D2:D9,行序号)返回D2:D9对应行序号的单价 特别提醒: 因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。 【4】Offset Match函数: D13单元格公式: {=Offset(D1,Match(B13&C13,B2:B9&C2:C9,0),)} B13&C13 : 多条件构成的组合查询条件 B2:B9&C2:C9 :数组运算返回组合的查询区域 MATCH(B13&C13,B2:B9&C2:C9,0):返回组合查询条件满足的行序号 OFFSET(D1,行序号, )返回对应行序号的单价 特别提醒: 因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。 【5】Indirect Match函数: D13单元格公式: {=INDIRECT('D' & (MATCH(B13&C13,B2:B9&C2:C9,0) 1))} B13&C13 : 多条件构成的组合查询条件 B2:B9&C2:C9 :数组运算返回组合的查询区域 MATCH(B13&C13,B2:B9&C2:C9,0):返回组合查询条件满足的区域行序号 MATCH(B13&C13,B2:B9&C2:C9,0) 1:返回组合查询条件满足的D列行号 INDIRECT函数作用:根据字符串拼接得到的单元格引用返回单元格值。 特别提醒: 因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。 【6】SUM函数: D13单元格公式: {=SUM((B2:B9=B13)*(C2:C9=C13)*D2:D9)} (B2:B9=B13)*(C2:C9=C13):满足组合查询条件返回1 (B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求和即是单价 特别提醒: 因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。 【7】SUMPRODUCT函数: D13单元格公式: =SUMPRODUCT((B2:B9=B13)*(C2:C9=C13)*D2:D9) (B2:B9=B13)*(C2:C9=C13):满足组合查询条件返回1 (B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求和即是单价 【8】SUMIFS函数: D13单元格公式: =SUMIFS(D2:D9,B2:B9,B13,C2:C9,C13) 满足多条件的只有一条数据,求和即是单价 【9】MAX函数: D13单元格公式: {=MAX((B2:B9=B13)*(C2:C9=C13)*D2:D9)} (B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求最大值即是单价 【10】MIN IF函数: D13单元格公式: {=MIN(IF((B2:B9=B13)*(C2:C9=C13),D2:D9))} IF((B2:B9=B13)*(C2:C9=C13),D2:D9):满足组合查询条件返回单价,否则返回FALSE MIN忽略逻辑值FALSE,满足多条件的单价只有一条数据,求最小值即是单价。 其实实现多条件查找的还远不止这十种方法,写到此,深感Excel函数功能还真是丰富和强大。 分享Excel实用技术,助力办公效率提升 欢迎加入Excel表哥之家
|
|