Excel数据分析（二）常用函数计算

1、常规计算

(1)SUM求和函数

• 语法：=sum(参数)；
• 参数可填入的内容有：单元格范围，1-255个单元格；（注意要使用英文状态下的标点符号）
• 示例：
  • 范围求和：=sum(A1:A3)；
  • 多个单元格求和：=sum(A1,B2,C3)。

（2）PRODUCT计算乘积

• 语法：=PRODUCT(参数)
• 参数可填入的内容有：单元格范围，1-255个单元格；
• 示例：
  • 范围求乘积：=PRODUCT(A1:A3)；
  • 多个单元格求乘积：=PRODUCT(A1,A3)。

（3）SUMPRODUCT计算数组元素的乘积和

• 语法：=SUMPRODUCT(参数)

• 参数填入的内容：数组；
  

• 示例：

  • 计算主机销售低于15台的人数：=SUMPRODUCT((B3:E3<15)*(B3:E3>=0))
  • 此处采用了布尔运算，计算步骤如下：
    • (B3:E3<15)：1,0,0,1
    • (B3:E3>=0)：1,1,1,1
    • {1,0,0,1}*{1,1,1,1}：{1,0,0,1}
    • {1,0,0,1}，求和为2

（4）ABS绝对值

• 语法：=ABS(参数)
• 参数的绝对值要是一个实数，如果参数不是数值将返回错误值“#VALUE!”;
• 示例：
  • 计算单元格A1的绝对值：=ABS(A1）；

2、按条件计算

(1)SUMIF条件求和

• 语法：=SUMIF(条件判断范围,判断条件,求和范围)
• 参数解读：
  • 条件判断范围：即：针对那些数据做判断
  • 判断条件：即判断标准；
  • 求和范围：即该公式想要求和的内容范围。
• 示例：
  • 求销售部的销售总额：=SUMIF(A1:A7,“销售部”，B1:B7)
  • 注意如果判断条件是文本时，需要用""将条件括起来；

扩展：SUMIFS函数

• 函数功能：对需要多个判定条件的单元格求和，解决SUMIF函数单一性问题。
• 语法：=SUMIFS(求和范围,第一个条件判断范围,第一个判断条件,第二个条件判断范围，第二个判断条件…)
• 参数解读：
  • 与SUMIF函数类似，但与其不同的是，“求和范围”在公式中的位置不一样，SUMIF函数在第三位，SUMIFS函数在第一位；（大概是为了函数拓展性做的调整吧）
• 示例：
  • 求上海地区显示屏的销售总额：=SUMIFS(D1:D7,A1:A7,“上海”,B1:B7,“显示器”)。
  • D1:D7：该区域为销售金额，也是求和区域
  • A1:A7：销售地区
  • B1:B7：销售品类
  • “上海”、“显示器”：判断条件

（2）SUBTOTAL分类汇总

• 语法：=SUBTOTAL(函数代码,进行分类汇总计算的区域1，进行分类汇总计算的区域2…)
• 参数解读：
  • 函数代码：指使用者指定使用何种函数在列表中进行计算，且填入的函数代码；
  • 进行分类计算的区域：即数据集区域；
  • 函数代码可以不用记忆，当你输入公式后，在给出的函数列表中选取即可；
• 示例：
  

3、按数值精确度计算

（1）ROUND四舍五入

• 语法：=ROUND(数值,保留位数)
• 示例：
  • 对金额保留2位小数：=ROUND(金额,2)

（2）INT数值取整

• 语法：=INT(数值)
• 示例：
  • 对面积去整：=INT(面积)

拓展：TRUNC函数

• 功能：将数字进行截断,注意此函数不会进行四舍五入操作；
• 语法：=TRUNC(数字,保留位数)
• 示例：
  • 取整：=TRUNC(A1)
  • 保留两位：=TRUNC(A1,2)

4、文本函数

（1）MID返回指定位置开始的特定数目的字符

• 语法：=MID(文本,开始字符下标,提取字符数目)
• 示例：身份证号码一共18位，提取出生年月日，共8位；
  • =MID(身份证号码,7,8)
  • 注意下标与编程语言不一样，不是从0开始，是从1开始；

（2）TEXT文本转换函数

• 语法：=TEXT(转换目标,转换格式)
• 示例：将日期格式下单元格内容：1993/11/11，转换为年月日形式
  • =TEXT(1993/11/11,“yyyy年mm月dd日”)
  • 单元格格式视情况，需要用""括起来；
  • 具体格式，不做详细说明，自行查询；

（3）SEARCH获取字符第一次出现位置

• 语法：=SEARCH(要查找的字符,字符所在的文本,开始查找位置)
• 示例：科技部：张三，查找姓名开始的字符位置；
  • =SEARCH(":",“科技部：张三”)
  • 不填写开始查找位置，则默认从1开始查找；
  • 一般会与其他函数一起使用，如：从“部门：姓名”中提取“姓名”，则可与MID函数一起使用；

5、逻辑函数

(1)IF判断函数

• 语法：=IF(逻辑判断,为真结果,为假结果)
• 示例：A列中分数小于60为及格
  • =IF(A1<60,“不及格”,“及格”)

（2）AND逻辑与判断函数

• 语法：=AND(逻辑判断1,逻辑判断2,······)
• 示例：A1申论和B1行测都达到80,则为通过；
  • =AND(A1>80,B1>80)

（3）OR逻辑或判断函数

• 语法：=OR(逻辑判断1,逻辑判断2,······)
• 示例：A1申论和B1行测只要有一个达到80,则为通过；
  • =OR(A1>80,B1>80)