在职场办公中的亲们,遇到过多条件查找吗?这里小编风向一下多条件查找的各种套路,如果都会了,亲就是高手了,话不多说,上要求,如图,用公式查找出符合条件的工资,就是同时符合姓名 性别 部门的人的工资,亲能用多少种方法?小编整理了21种供大家学习。 21种公式:备注数组的,一定要按三键结束,就是ctrl+shift+回车,当公式两端有大括号的时候,才会返回正确的结果 =SUM((A2:A14=A18)*(B2:B14=B18)*(C2:C14=C18)*D2:D14) 数组 =SUMPRODUCT((A2:A14=A18)*(B2:B14=B18)*(C2:C14=C18)*D2:D14) =MAX((A2:A14=A18)*(B2:B14=B18)*(C2:C14=C18)*D2:D14) 数组 =LOOKUP(A18&B18&C18,A2:A14&B2:B14&C2:C14,D2:D14) =LOOKUP(1,0/(A2:A14=A18)*(B2:B14=B18)*(C2:C14=C18),D2:D14) =LOOKUP(1,0/(A18&B18&C18=A2:A14&B2:B14&C2:C14),D2:D14) =LOOKUP(1,0/(((A2:A14=A18)+(B2:B14=B18)+(C2:C14=C18))=3),D2:D14) =MIN(IF((A2:A14=A18)*(B2:B14=B18)*(C2:C14=C18),D2:D14)) 数组 =SUMIFS(D2:D14,A2:A14,A18,B2:B14,B18,C2:C14,C18) =INDEX(D2:D14,MATCH(A18&B18&C18,A2:A14&B2:B14&C2:C14,0)) 数组 =INDEX(D2:D14,MATCH(1,(A18=A2:A14)*(B18=B2:B14)*(C18=C2:C14),0)) 数组 =OFFSET(D1,MATCH(A18&B18&C18,A2:A14&B2:B14&C2:C14,0),) 数组 =INDIRECT("D"&MATCH(A18&B18&C18,A1:A14&B1:B14&C1:C14,0)) 数组 =VLOOKUP(A18&B18&C18,CHOOSE({1,2},A2:A14&B2:B14&C2:C14,C2:D14),2,0) 数组 =DSUM(A1:D14,4,A17:C18) =DGET(A1:D14,4,A17:C19) =DMAX(A1:D14,4,A17:C18) =DAVERAGE(A1:D14,4,A17:C18) =DMAX(A1:D14,4,A17:C18) =DMIN(A1:D14,4,A17:C18) =DPRODUCT(A1:D14,4,A17:C18) =HLOOKUP(A18&B18&C18,TRANSPOSE(CHOOSE({1,2},A2:A14&B2:B14&C2:C14,C2:D14)),2,0) 数组 亲会多少呢,欢迎关注小编,更多分享中,上面的,后期我们也会一一讲解,希望每位亲们都成为职场办公高手 |
|