Excel的函数：前言、概念、基本使用（IF+AND+OP、COUNT、IF嵌套、SUMIF、日期函数、ROUND、MOD、Replace、生成间断序号）

前言

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))

操作结果：