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

一、查找

1、vlookup-行查找

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

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

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

	列A	列B	列C	列D	列E	列F	列G
行1		月份	值
行2	大力	1月	3		条件	查找值
行3	小李	1月	4		小李	4
行4	中张	1月	7

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

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

所在列，条件n)

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

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

	列A	列B	列C	列D	列E	列F	列G
行1		月份	值
行2	大力	2月	5		条件1	条件2	查找值
行3	小李	2月	6		小李	1月	4
行4	中张	2月	8
行5	大力	1月	3
行6	小李	1月	4
行7	中张	1月	7

3、sumproduct-行列查找合计

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

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

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

	列A	列B	列C	列D	列E	列F	列G
行1		1月	2月
行2	大力	3	5		列条件	行条件	查找值
行3	小李	4	6		小李	1月	4
行4	中张	7	8

二、排序

1、升降序

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

场景：适合于值比大小

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

	月份	值	降序排序	升序排序
大力	2月	5	4	3
小李	2月	6	3	4
中张	2月	8	1	6
大力	1月	3	6	1
小李	1月	4	5	2
中张	1月	7	2	5

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)

	月份	值	降序排序	升序排序	单分组排列	多分组排列
中张	2月	8	1	6	1	1
中张	1月	7	2	5	1	1
小李	2月	6	3	4	2	1
大力	2月	5	4	3	3	1
小李	1月	4	5	2	2	1
大力	1月	3	6	1	3	1
中张	2月	3				2

三、定位

1、值最大/最小的人

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

场景：适合单个最值

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

	月份	值
大力	2月	5
小李	2月	6
中张	2月	8		最优秀的人	中张
大力	1月	3
小李	1月	4
中张	1月	7

2、多个表现最佳的人

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

场景：适合多个最值

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

	月份	值
大力	2月	5
小李	2月	6
中张	2月	8		最优秀的人	中张
大力	1月	8
小李	1月	4		最优秀的人们	中张
中张	1月	7			大力