双条件查找的两种情况 第一种情况【常规方法6种】第一种情况的双条件查找的函数组合公式 第一种双条件查找的情况,整理出来的函数公式方法有6种,公式如下: 方法1【数组公式】 =VLOOKUP(E2&F2,IF({1,0},A2:A15&B2:B15,C2:C15),2,) 方法2【普通公式】 =LOOKUP(,0/((A2:A15=E2)*(B2:B15=F2)),C2:C15) 方法3【数组公式】 =INDEX(C2:C15,MATCH(E2&F2,IF(1,A2:A15&B2:B15),)) 方法4【数组公式】 =OFFSET(C1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),) 方法5【数组公式】 =INDIRECT('C'&1+MATCH(E2&F2,IF(1,A2:A15&B2:B15),)) 方法6【普通公式】①如果所有内容均为文本,且返回值的字符数均一样(本例均为个2字符)时 =MID(PHONETIC(A2:C15),FIND(E2&F2,PHONETIC(A2:C15))+2,2) 方法6【数组公式】②如果返回值为数值,且符合条件的是唯一项时 =SUMPRODUCT((A2:A15=E2)*(B2:B15=F2),C2:C15) 第一种双条件查询这个情况,难度在于【计算满足条件的内容所在行】,我简单整理出以下4个方法: 方法1【数组公式】 =MAX(IF((A1:A15=E2)*(B1:B15=F2),ROW(1:15))) 方法2【数组公式】 =MATCH(E2&F2,IF(1,A1:A15&B1:B15),) 方法3【数组公式】 =SUM((A1:A15=E2)*(B1:B15=F2)*ROW(1:15)) 方法4【普通公式】 =LOOKUP(,0/((A1:A15=E2)*(B1:B15=F2)),ROW(1:15)) 用这四种方法,再结合查找引用的INDIRECT、OFFSET、INDEX等函数,可以组合成更多的函数组合。 第二种情况【常规方法6种】第二种双条件查找的函数组合公式 第二种双条件查找的情况,整理出来的函数公式方法有6种,公式如下: 方法1【普通公式】 =VLOOKUP(I2,A2:G15,MATCH(J2,A1:G1,),) 方法2【普通公式】 =LOOKUP(,0/(I2=A2:A15),OFFSET(A2:A15,,MATCH(J2,B1:G1,))) 方法3【普通公式】 =INDEX(姓名,MATCH(I2,A2:A15,),MATCH(J2,B1:G1,)) 方法4【普通公式】 =OFFSET(A1,MATCH(I2,A2:A15,),MATCH(J2,B1:G1,)) 方法5【普通公式】 =INDIRECT(CHAR(64+MATCH(J2,A1:G1,))&MATCH(I2,A1:A15,)) 方法6【数组公式】 =SQRT(MAX(IF(A2:A15=I2,B2:G15,)*IF(B1:G1=J2,B2:G15,))) 数据、取区域交集的方法一、第一种情况 取第一种情况的区域交集 上图中,用到了两个函数公式(这是OFFSET函数的两种获取区域的方法,供参考): =OFFSET(A1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),,,3) C2:C15 =OFFSET(A1:C1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),) C2:C15 二、第二种情况 取两个数据区域的交集 上图是用定义名称的方式实现的,用这种方式,会产生很多名义的名称,管理、维护起来不是很方便。那么,还可以有以下的方法: 函数生成数组,取交集 演示里面用到的两个公式: =OFFSET(A1,MATCH(I2,A2:A15,),1,1,6) OFFSET(A1,1,MATCH(J2,B1:G1,),14,1) =OFFSET(A2:A15,,MATCH(J2,B1:G1,)) OFFSET(B1:G1,MATCH(I2,A2:A15,),) 用这种方法,公式不见得是最简洁的,但是这种思路可以学习和掌握。 |
|
来自: 王意pu3eupsbkg > 《Excel文件》