526互联

excel函数用法小结

发布时间 2023-04-01 10:47:11作者: 一个不会玩的狗子

Excel函数使用小结

  1. if与数组的结合使用——多条件查询

    多条件统计查询：
	获取：
		广州战区、A类的数据最小值：
			=MIN(IF(($A$3:$A$21=$K$2)*($B$3:$B$21=$J$2),$C$3:$C$21))
			注：if函数，在使用数组结合的过程中，出现多个条件判断时，不可以使用and函数，需将【and函数】换成【*】号
		最大值也是同理：
			=MAX(IF(($A$3:$A$21=$K$2)*($B$3:$B$21=$J$2),$C$3:$C$21))
		中位数：
			=MEDIAN(IF(($A$3:$A$21=$K$2)*($B$3:$B$21=$J$2),$C$3:$C$21))
			
注：按下：Ctrl+shift+enter组合键，可以将公式转为数组计算，这点要特别注意

  2. 多条件排名——rank函数、countifs函数

    如上图：
	分别求出每个部门的排名，即广州战区A类的排名，广州战区B类的排名...分别各自的排名，等
	方式一：=COUNTIFS($A$2:$A$21,A3,$C$2:$C$21,">"&C3)+1
	方式二：=RANK(C3,C3:C21)  //此函数当前是没有条件排名，不能满足题目要求
注：countifs函数可以巧妙使用单元格的引用达到不同的效果，这是一个隐藏的使用技巧

拓展思维：方式二的rank函数是否可以结合数组，或者if函数实现多条件进行排名，是否可以实现题目的要求呢？

  3. 数组乘积求和公式——SUMPRODUCT

    =SUMPRODUCT(G3:G21,H3:H21)
注释：G3到G21的单元格分别乘以H3到H21的单元格，再求和。即SUMPRODUCT函数的作用

  4. 字符串切割函数

    方式一：TEXTSPLIT函数——新版本的Excel含有的公式
=TEXTSPLIT(C3,"/")

方式二：mid函数 +find函数+len函数等组合
=MID(C3,LEN(J3)+2,FIND("/",C3,LEN(J3)+1)-1)
如下图的K列的深圳市，不过组合函数相对复杂，需要嵌套使用，多层的切割也需区分