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)