excel函数用法小结

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列的深圳市，不过组合函数相对复杂，需要嵌套使用，多层的切割也需区分
   

   

5. 逻辑符号的使用：

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),"满足","不满足")
  这两个公式返回的结果一样。