Excel函数使用小结
-
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组合键,可以将公式转为数组计算,这点要特别注意
-
多条件排名——rank函数、countifs函数
如上图: 分别求出每个部门的排名,即广州战区A类的排名,广州战区B类的排名...分别各自的排名,等 方式一:=COUNTIFS($A$2:$A$21,A3,$C$2:$C$21,">"&C3)+1 方式二:=RANK(C3,C3:C21) //此函数当前是没有条件排名,不能满足题目要求 注:countifs函数可以巧妙使用单元格的引用达到不同的效果,这是一个隐藏的使用技巧 拓展思维:方式二的rank函数是否可以结合数组,或者if函数实现多条件进行排名,是否可以实现题目的要求呢?
-
数组乘积求和公式——SUMPRODUCT
=SUMPRODUCT(G3:G21,H3:H21) 注释:G3到G21的单元格分别乘以H3到H21的单元格,再求和。即SUMPRODUCT函数的作用
-
字符串切割函数
方式一:TEXTSPLIT函数——新版本的Excel含有的公式 =TEXTSPLIT(C3,"/") 方式二:mid函数 +find函数+len函数等组合 =MID(C3,LEN(J3)+2,FIND("/",C3,LEN(J3)+1)-1) 如下图的K列的深圳市,不过组合函数相对复杂,需要嵌套使用,多层的切割也需区分
-
逻辑符号的使用:
a、- 除可用 And 组合表示“与”条件外,也可以用星号 * 组合,每个条件要用括号括起来,条件与条件之间用 * 连接,如演示中的条件(C2="女装")*(F2>=600)*(E2<70),它等同于 AND(C2="女装",F2>=600,E2<70)。
b、- 把公式 =IF(OR(F2<400,F2>=800,E2>90),"满足","不满足")
**用加号 + 代替 OR 变为:**
=IF((F2<400)+(F2>=800)+(E2>90),"满足","不满足")
这两个公式返回的结果一样。