excel进阶常用函数（查找、排序、定位）

大家好，欢迎来到IT知识分享网。

一、查找

1、vlookup-行查找

函数说明：VLOOKUP(条件,查询范围(第一列是条件所在列),值所在列是查询范围的第几列,FALSE)

场景：用于查找满足某行条件的某列对应值，适合只满足一个小条件查找，数据结果具有唯一性的情况：

案例：小李对应的值是多少，可用=VLOOKUP(E3,A1:C4,3,FALSE)

2、sumifs-多列查找或多行查找

函数说明：SUMIFS(值所在列，条件1 所在列，条件1，条件2 所在列，条件2，…，条件n

所在列，条件n)

场景：用于查找满足多个条件的某列对应值求和，条件分布在多列的情况：

案例：小李在1月的值是多少，可用SUMIFS(C1:C7,B1:B7,F3,A1:A7,E3)

3、sumproduct-行列查找合计

函数说明：SUMPRODUCT((行值范围=行条件)*(列值范围=列条件),值范围)

场景：用于查询矩阵条件，即同时满足行条件和列条件的取值情况

案例：小李在1月的值是多少，可用SUMPRODUCT((A2:A4=E3)*(B1:C1=F3),B2:C4)

二、排序

1、升降序

函数说明：RANK(值,值范围列,1表示升序0表示降序)，相同数值时排名相同

场景：适合于值比大小

案例：升序=RANK(C2,$C$2:$C$7,1)，降序=RANK(C2,$C$2:$C$7,0)

2、分组排序：

对值进行手动降序/升序后，按照分组列进行排序

函数说明：COUNTIF(分组第一个值固定:所在值,所在值)

场景：一个分组内的排序

案例：如各月中小朋友们的排名情况，COUNTIF($B$2:B2,B2)，COUNTIF($B$2:B3,B3)

3、分多组排序

对值进行手动降序/升序后，按照分组列进行排序

函数说明：COUNTIFS(分组1第一个值固定:分组1所在值,分组1所在值,分组2第一个值固定:分组2所在值,分组2所在值,…,分组n第一个值固定:分组n所在值,分组n所在值)

场景：多分组的排序

案例：如每个人各月表现的最佳值排序，COUNTIFS($B$2:B2,B2,$A$2:A2,A2)

三、定位

1、值最大/最小的人

函数说明：INDEX(反馈值范围,MATCH(求最大/最小函数(值范围),值范围,))

场景：适合单个最值

案例：单月表现值最好的人是谁，INDEX(A2:A7,MATCH(MAX($C$2:$C$7),$C$2:$C$7,))

2、多个表现最佳的人

函数说明：FILTER(反馈值范围,值范围=求最大/最小函数(值范围))

场景：适合多个最值

案例：单月表现值最好的都有谁，FILTER($A$2:$A$7,$C$2:$C$7=MAX($C$2:$C$7))