数据分析--Excel函数
Excel-函数
前情介绍：EXCEL中使用频率最高的一个模块—函数。可能有的同学会说，一般在EXCEL中函数
都是拿来计算 的，跟数据分析有什么关系啊？大家别忘了，在数据分析中最重要的一环不是分析
数据，而是在得到数据以 后进行的数据清洗。我们在得到任何的数据的时候都不是直接可以拿来
使用的，我们需要对数据进行清洗以 后，把我们所需的数据拿出来才能够进行分析。在这个过程
中，我们一般用的比较多的工具就是EXCEL了， 在EXCEL中，我们在清洗工具时就会用到各种各
样的函数，数据在用函数进行清洗时，可以帮助我们节省很 多时间，因为EXCEL强大的计算功能
基本上就可以满足我们日常的数据清洗。
常用函数
| 函数类型 | 函数名字 |
|---|---|
| 数学函数 | 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 |
公式和函数(面试题:重要)
-
相同点：公式与函数的相同点就是：都是以“=”开头的，并且都会得到一个返回值。
公式：公式是一个等式，以“=”开头，后面紧跟数据和运算符，并得到返回值。 -
函数：函数是excel内部预定义的功能，以“=”开头，按照特定的规则进行计算，并得到返回值。
-
不同点:函数可以是公式里面的一部分，但公式不一定总需要包含函数。所以公式的范畴是更大的，公式包含函 数。
-
函数有唯一的函数名称，而公式没有。 比如说我们刚刚举例用到的这个sum函数，它的名称就叫sum。每个函数都有特定的功能和用途，比如 sum函数是用来求和的。
-
函数和公式的录入方法不同，录入函数可以通过“插入函数”，如下所示：（公式—>文本—>插入函数）
IF+AND_OR例子
IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。 例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。
AND函数，它是一个逻辑函数，用于确定测试中的所有条件是否均为 TRUE，参数最多256个。
OR函数，它是一个逻辑函数，用于确定测试中的所有条件是否均为 TRUE。
1. 案例1：所有学科分数都要大于60，才算通过 方案：=IF(AND(E2>60,F2>60,G2>60),"通过","不通过")
2. 案例2：只要有1门通过就算通过 方案：=IF(OR(E2>60,F2>60,G2>60),"通过","不通过")
3. if嵌套使用:=IF(D3<=240,D3*B$11,IF(D3<=400,D3*C$11,D3*D$11))
COUNTIF统计(函数)
- COUNT() 统计整数
- COUNTA()统计非空
- COUNTIF()根据单个条件统计
- COUNTIFS()根据多个条件统计
语法如下:
=COUNT(范围)
=COUNTA(范围)
=COUNTIF(范围,"条件") 示例: =COUNTIF(B3:K3,"b")
=COUNTIFS(范围,"条件",范围"条件",...) 示例: =COUNTIFS(B2:B11,"a",C2:C11,"a")
SUMIF(范围,‘条件’,[所求条件的范围–可选参数])
如果第三个参数和第一个参数的范围是一致,那就忽略
当二次筛选的时候,最后求的是第三个参数范围的和例如，如果某列中含有数字，你只需对大于 10000 的数值求和。 可使用以下公式：
=SUMIF(B2:B25,">10000")SUMIF(range, criteria, [sum_range])如果第一个参数是数字，那么就可以省略第三个参数
例如:借款金额总和
=SUMIF(D3:D14,"借",H3:H14)SUMIFS(求和的范围,判断的范围,....) 多条件判断
SUMIFS是office2007新增函数，使用该函数可快速对多条件单元格求和，sumifs函数功能十分 强大，
可以通过不同 范围的条件求规定范围的和。
例如:=SUMIFS(H3:H14,D3:D14,"贷",E3:E14,"建行")
日期函数
=YEAR(日期类型数据或是地址引用) -- 返回年份
例如:=YEAR(A2)=MONTH(日期类型数据或是地址引用) -- 返回月份
例如:=MONTH(A2) =DAY(日期类型数据或是地址引用) -- 返回日
例如:DAY(A2)
#注意:日期数据必须是标准格式的日期数据=TODAY() --返回当前系统的日期=DATE() --返回指定日期 例如:=DATE(2020,9,18)=NOW() -- 返回当前日期和时间=EDATE(时间,增加的月份) -- 返回第一个参数后第二个参数的月份时间 例如:=EDATE(A2,1)
#注意:第二个参数可以为负数(前一个月)=EOMONTH(引用的日期数据,第几个月的时间) -- 返回第二个参数中第一个参数后几个月的最后一天
例如:=EOMONTH(A11,0) 即:返回A11月份中的最后一天(如果为1,那就是下一个月的最后一天)
日期函数实例使用
=DAY(EOMONTH(A2,0)) -- 返回当月有多少天(结合使用)=IF((MONTH(A2)/3)<=1,"第一季度", IF((MONTH(A2)/3)<=2,"第二季度", I
F((MONTH(A2)/3)<=3,"第三季度", IF((MONTH(A2)/3)<=4,"第四季度"))))
#当前时间的月份是第几季度
MOD函数使用
=MOD(参数1,参数2) -- 返回参数1除参数2的余数例如:判断平年还是闰年
=IF(OR(AND(MOD(A2,100)=0,MOD(A2,400)=0),AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"闰年","平年")
ROUND函数使用
=ROUND(参数1,参数2) -- 四舍五入(参数2非0时保留参数2 位,为0四舍五入到整数)=ROUNDDOWN(参数1,参数2) -- 向下四舍五入=ROUNDUP(参数1,参数2) -- 向上四舍五入
REPLACE函数使用
=Replace（要替换的字符串，开始位置，替换个数，新的文本）
#注意：第四个参数是文本，要加上引号。例如:隐藏8到10位电话号码
=REPLACE(A2,8,3,"***")
REPT函数使用
=REPT("文本",个数) -- 根据指定次数生成文本
例如:=REPT("*",3)
合并单元格并且计算
1. 案例：将三个单元格计算总和
2. 方案：计算三个单元格总和---》选中三个单元格---》下拉
生成间断序号(重要)
案例:使空得单元格不做统计，并且排序
函数:=IF(B2=" ","",COUNTA(B$2:B2)) -- COUNTA(统计非空单元格)
ROW与COLUMN
=ROW -- 返回当前单元格的行号
=COLUMN --返回当前单元格的列号
INDIRECT()
语法：INDIRECT(ref_text, [a1])
INDIRECT(引用区域，引用格式）参数：Ref_text 必需。 对包含 A1 样式的引用、R1C1 样式的引用、定义为引用的名称或对单元格的引用作为 文本字符串的单元格的引用。 如果 ref_text 不是有效的单元格引用, 则间接返回 #REF! 。行政区划分
省直辖市自治区和关联市/区--下拉选框格式
方案：
（1）公式---》名称管理器---》新建---》名称---》省直辖市自治区---》引用位置
（2）公式---》选中市区数据---》根据所选内容创建---》首行
（3）数据---》数据验证---》序列---》来源---》=省直辖市自治区
（4）数据---》数据验证---》序列---》来源---》=indirect($A$2)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！