文章目录
- 前言
- 一.概念
- 1.含义
- 2.常用函数
- 3.公式与函数的同异
- 二.函数基本使用
- 1.IF+AND+OR
- 2.COUNT / COUNTA / COUNTIF / COUNTIFS
- 3.IF嵌套
- 4.SUMIF / SUMIFS
- 5.日期函数
- 6.ROUND / ROUNDDOWN / ROUNDUP
- 7.MOD
- 8.Replace
- 9.生成间断序号
前言
EXCEL中使用频率最高的一个模块就是函数。
思考:一般在EXCEL中函数都是拿来计算的,跟数据分析有什么关系?
其实在数据分析中最重要的一环不是分析数据,而是在得到数据以后进行的数据清洗。我们在得到任何的数据的时候都不是直接可以拿来使用的,我们需要对数据进行清洗以后,把我们所需的数据拿出来才能够进行分析。在这个过程中,我们一般用的比较多的工具就是EXCEL了,在EXCEL中,我们在清洗工具时就会用到各种各样的函数,数据在用函数进行清洗时,可以帮助我们节省很多时间,因为EXCEL强大的计算功能基本上就可以满足我们日常的数据清洗。
一.概念
1.含义
Excel 里的函数是预先写好的一个运算公式,接受零或多个参数返回计算结果,以常用的求和函数SUM为例,它的语法是sum(number1,number2)
2.常用函数
函数类型 | 函数名字 |
---|---|
数学函数 | int(),mod(),round(),abs(),sqrt(),rand(),randbetween() |
统计函数 | min(),sum(),count(),counta(),average(),countif(),sumif() averageif(),countifs(),sumifs(),averageifs(),frequency(),rank() |
日期函数 | year(),month(),day(),today(),date(),now(),edate(),eomonth(),datedif() |
文本函数 | mid(),left(),right(),len(),text(),rept(),replace(),substitute() |
逻辑函数 | if(),and(),or(),not() |
查找与引用函数 | vlookup(),offset(),match(),index(),indirect(),row(),column(),hlookup |
3.公式与函数的同异
(1)相同点:
公式与函数的相同点就是:都是以“=”开头的,并且都会得到一个返回值。
公式:公式是一个等式,以“=”开头,后面紧跟数据和运算符,并得到返回值。
函数:函数是excel内部预定义的功能,以“=”开头,按照特定的规则进行计算,并得到返回值。
(2)不同点:
a. 函数可以是公式里面的一部分,但公式不一定总需要包含函数。所以公式的范畴是更大的,公式包含函数。
b. 函数有唯一的函数名称,而公式没有。
c. 函数和公式的录入方法不同,录入函数可以通过“插入函数”,如下所示:(公式--》插入函数)
也可以通过在单元格中输入 = 号,然后从下拉列表中选择函数。如下所示:
而公式可以直接在单元格中输入等于号,并用运算符连接单元格即可进行计算。
二.函数基本使用
1.IF+AND+OR
(1)AND函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE,参数最多256个。
(2)OR函数,它是一个逻辑函数,用于确定测试中的所有条件是否均为 TRUE。
需求1:所有学科分数都要大于60,才算通过
方法:=IF(AND(E2>60,F2>60,G2>60),“通过”,“未通过”)
操作结果:
需求2:只要有1门通过,就算通过
方法:=IF(OR(E2>60,F2>60,G2>60),“通过”,“不通过”)
操作结果:
2.COUNT / COUNTA / COUNTIF / COUNTIFS
(1)COUNT() 计算数值的个数
需求:统计有多少个数值
方法:=COUNT(A13:A18)
操作结果:
(2)COUNTA() 计算非空单元格个数
需求:统计有多少个非空单元格
方法:=COUNTA(A13:A18)
操作结果:
(3)COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量
COUNTIF 的最简形式为:=COUNTIF(要检查哪些区域? 要查找哪些内容?)
需求:统计迟到的次数,旷课的次数(a:全勤,b:迟到,c:旷课)
方法:=COUNTIF(B2:K2,“b”),=COUNTIF(B2:K2,“c”)
操作结果:
(4)COUNTIFS函数将条件应用于跨多个区域的单元格,然后统计满足所有条件的次数。
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
COUNTIFS 函数语法具有以下参数:
criteria_range1 必需。 在其中计算关联条件的第一个区域。
criteria1 必需。 条件的形式为数字、表达式、单元格引用或文本,它定义了要计数
的单元格范围。 例如,条件可以表示为 32、">32"、B4、"apples"或 “32”。
criteria_range2, criteria2, … 可选。 附加的区域及其关联条件。 最多允许 127 个区域/条件对。
需求:统计1月1号和1月2号有多少人全勤
方法:=COUNTIFS(B2:B4,“a”,C2:C4,“a”)
3.IF嵌套
需求:计算电费
方法:=IF(D3<=240,D3B$11,IF(D3<=400,D3C$11,D3*D$11))
操作结果:
4.SUMIF / SUMIFS
(1)SUMIF 函数对范围中符合指定条件的值求和
需求:金额大于10000的钱进行求和(条件求和)
方法:=SUMIF(H3:H14,">10000")
操作结果:
(2)SUMIF(range, criteria, [sum_range])如果第一个参数是数字,那么就可以省略第三个参数
需求:借款金额总和
方法:=SUMIF(D3:D14,“借”,H3:H14)
操作结果:
(3)SUMIFS是office2007新增函数,使用该函数可快速对多条件单元格求和,sumifs函数功能十分强大,可以通过不同范围的条件求规定范围的和。
需求:计算贷款方,建设银行的金融综合
方法:=SUMIFS(H3:H14,D3:D14,“贷”,E3:E14,“建行”)
操作结果:
5.日期函数
(1)YEAR(serial_number)
需求:查看某日期的年份
方法:=YEAR(A2)
操作结果:
(2)MONTH(serial_number)
需求:查看某日期的月份
方案:=MONTH(A3)
操作结果:
(3)DAY(serial_number)
需求:查看某日期的日
方法:=DAY(A4)
操作结果:
(4)TODAY()
需求:返回当前日期
方法:=TODAY()
(5)DATE(year,month,day)
需求返回指定日期
方法:=DATE(2020,7,20)
(6)NOW()
需求:返回当前日期和时间
方法:=NOW() 注意在常规的前提下
(7)EDATE(start_date,months)
需求1:获取当前时间的后一个月时间
方法:=EDATE(A2,1)
操作结果:
需求2:获取当前时间的前一个月时间
方法:=EDATE(A2,-1)
操作结果:
(8)EOMONTH(start_date,months)
需求1:该月最后一天日期
方法:EOMONTH(A2,0)
操作结果:
需求2:返回某一个月的下一个月的最后一天的日期
方法:EOMONTH(A1,1)
操作结果:
需求3:该月有多少天
方法:=DAY(EOMONTH(A2,0))
操作结果:
需求4:第几季度
方法:=IF((MONTH(A2)/3)<=1,“第一季度”,IF((MONTH(A2)/3)<=2,“第二季度”,IF((MONTH(A2)/3)<=3,“第三季度”,IF((MONTH(A2)/3)<=4,“第四季度”))))
操作结果:
6.ROUND / ROUNDDOWN / ROUNDUP
(1)ROUND
round函数语法是ROUND(number, num_digits),number 必需,要四舍五入的数字;num_digits 必需,位数,按此位数对 number 参数进行四舍五入;
操作结果:
(2)ROUNDDOWN
rounddown函数语法是ROUNDDOWN(number,num_digits),Number 为需要向下舍入的任意实数;Num_digits 舍入后的数字的位数;
操作结果:
(3)ROUNDUP
roundup函数语法是ROUNDUP(number, num_digits),number必需。 需要向上舍入的任意实数。 num_digits 必需。 要将数字舍入到的位数。
操作结果:
7.MOD
MOD(number, divisor) ,MOD 函数语法具有下列参数:number必需。 要计算余数的被除数。divisor必需。 除数。
公式 | 说明 | 值 |
---|---|---|
=MOD(3, 2) | 3/2 的余数 | 1 |
=MOD(-3, 2) | -3/2 的余数。 符号与除数相同 | 1 |
=MOD(3, -2) | 3/-2 的余数。 符号与除数相同 | -1 |
=MOD(-3, -2) | -3/-2 的余数。 符号与除数相同 | -1 |
需求:判断闰年或平年
方法:=IF(OR(AND(MOD(A2,100)=0,MOD(A2,400)=0),AND(MOD(A2,4)=0,MOD(A2,100)<>0)),“闰年”,“平年”)
操作结果:
8.Replace
replace函数的语法格式:
=Replace(old_text,start_num,num_chars,new_text)
=Replace(要替换的字符串,开始位置,替换个数,新的文本)
注意:第四个参数是文本,要加上引号。
需求:隐藏8到10位电话号码
方法1:=REPLACE(A2,8,3,"**")
方法2:=REPLACE(A3,8,3,REPT("",3))
操作结果:
9.生成间断序号
需求:是空的单元格不做统计,并且排序
方法:=IF(B8="","",COUNTA(B$2:B8))
操作结果:
更多推荐
Excel的函数:前言、概念、基本使用(IF+AND+OP、COUNT、IF嵌套、SUMIF、日期函数、ROUND、MOD、Replace、生成间断序号)
发布评论