<复习>Excel Day1 - part 1

1.1 关于数据分析

（1）定义：根据方法论的指导，使用数据分析软件实现数据价值发现

（2）目的：记录业务轨迹，为未来业务决策提供参考及思考

（3）分类：

分类	区分	工作内容	工具
业务数据分析	1、对历史数据的分析、发现 2、对数据的分类汇总	1、描述性分析 2、数据透视 3、可视化图表	Excel、SQL、可视化软件
数据挖掘	1、建模预测数据 2、用业务来解释结果的含义	1、协同过滤 2、分类分析 3、关联分析等	Python、SPSS、SAS、R
大数据分析	1、优化代码、代码参数化 2、实现段时间内千万级数据展示	1、Hadoop大数据平台 2、数据整理 3、建模、分析与展示	Hadoop、Spark等

（4）数据分析项目中参与的角色与其相对应的负责事项

（5）数据分析流程

1.2 Excel 常用函数

（1）常用函数-总表

序号	函数类别	分序号	函数名称	用途
1	数学	1.1	SUM(number1,number2,…)	计算某个目标区域中单元格里数字的总和
		1.2	ROUND(number,num_digits)	设定指定位数，按此位数对目标数字进行四舍五入取整
		1.3	MOD(number,divisor)	对目标值除以指定数后的余数，余数的符号和除数相同。
		1.4	INT(number)	对目标数字进行舍入处理，处理的结果是得到小于目标数的最大整数
		1.5	ROUNDUP(number,num_digits)	对目标数字按照指定的条件进行舍入处理
		1.6	ROUNDDOWN(number,num_digits)	对目标数字按照指定的条件进行舍去处理
2	查询和引用	2.1	VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)	在给定区域的首列里查找目标数值，然后返回目标数值所在行里某一列的相关值。
		2.2	COLUMN(reference)	返回目标单元格或单元格区域（可返回列序号）
		2.3	ROW(reference)	返回目标单元格或单元格区域的行序号
		2.4	INDEX(array,row_num,column_num)	按照相关条件返回目标区域里的值
		2.5	OFFSET(reference,rows,cols,height,width)	将给定的区域作为目标引用区域，同时设定一个偏移量，然后依据该偏移量得到新的引用，最终的结果包括单元格和单元格区域的形式。
3	文本	3.1	MID(text,start_num,num_chars)	在目标字符串中指定一个开始位置，按设定的数值返回该字符串中的相应数目字符内容。
		3.2	LEN(text)	计算目标字符中的字符数
		3.3	LEFT(text,num_chars)	从字符串的左侧开始，按照指定的数值返回相应数目的字符内容。
		3.4	RIGHT(text,num_chars)	从字符串的右侧开始，按指定的数值返回相应数目的字符内容。
4	统计	4.1	COUNTIF(range,criteria)	在目标区域中统计满足预设条件的单元格数目。
4	统计	4.2	SUMIF(range,criteria,[sum_range])	在目标区域中对满足条件的单元格求和。
5	日期和时间	5.1	YEAR(serial_number)	返回给定日期所属的年份
		5.2	MONTH(serial_number)	返回给定日期所属的月份
		5.3	DAY(serial_number)	返回给定日期所属的天数
		5.4	NOW()	显示系统当前的日期和时间
		5.5	TODAY()	显示系统当前的日期
		5.6	NETWORKDAYS(start_date,end_date,holidays)	计算两个给定参数间的工作日数值
		5.7	EOMONTH(start_date,months)	给定一个起始日期，再辅以一个指定数字，将起始日期的月份向前或向后拨运该数字大小距离，最后返回所得月份的最后一天日期。
		5.8	WEEKDAY(start_date,return_type)	根据返回规则返回某个日期对应的星期数。
6	逻辑	6.1	IF(logical test,value if true,value if false)	用于对目标区域进行判断，返回真假逻辑值，据此输出相应的结果。
		6.2	AND(logical1,logical2,…)	对指定区域内的逻辑值进行判断，当逻辑值都为TRUE时返回TRUE，否则返回FALSE
		6.3	OR(logical1,logical2,…)	对指定区域内的逻辑值进行判断，当逻辑值任意一个为TRUE时返回TRUE，否则返回FALSE

（2）COUNTIF与COUNTIFS函数

a.在填写判断逻辑时，如果含有大于、小于时，需要将这两个符号放进""里面

b.COUNTIFS函数中条件1与条件2是“且”的关系

c.COUNTIFS函数中条件1与条件2的条件区域必须一致

d.若想要用COUNTIF与COUNTIFS来表达“或”的关系，可以用OR辅助或者用（全量-“且”部分值）

（3）累计求和-SUM

业务场景：每月的销售目标叠加

公式：=SUM（$B$2:$B2）——将起始单元格固定，后面的单元格固定行或者固定列

（4）SUMIF与SUMIFS函数

a.SUMIF：条件区域、条件、求和区域（注：当求和区域和条件区域一致的话可以不填，但不一致的情况下必须填写）

b.SUMIFS：求和区域、条件区域1、条件1、条件区域2、条件2……

注意这两个公式的参数顺序

（5）条件格式：以选择区域中左上角单元格为起始单元格、自带填充柄功能、

a.突出显示单元格规则：单元格值介于x和x之间，介于是指左闭右闭区间

b.使用公式确定：第二列值大于第一列【=$H10>$G10】

c.使用公式确定：单元格值为奇数【=MOD(G3,2)=1】

d.使用公式确定：偶数行加颜色【=MOD(ROW($J10),2)=0】（该偶数行是指Excel中的行号）

e.数据条：所选区域中的最大值设为满格，以此为基准调整其他单元格的数据条长度

f.色阶图：单元格的颜色深浅由其数据大小决定，一般值由小到大则颜色就由浅及深

g.图标集：一般将区域数据划分为三类，有四种类型可选择

数字：建议使用此类型，以原值来进行比较，遇到百分比数字，用小数点表示

百分比：计算公式为(当前单元格-最小值）/（最大值-最小值）

公式：输入对应的公式

百分点值：计算公式=PERCENTILE(array,K)

注意：数据条和色阶图一般可以用来做热力图

（6）日期时间类函数

函数	功能	输入	输出	输出结果
year	获取指定日期的年	标准日期型	数值型	2020
month	获取指定日期的月	标准日期型	数值型	11
day	获取指定日期的日	标准日期型	数值型	15
today	获取系统当前日期		标准日期型	2020/6/30
now	获取系统当前日期和时间		标准日期时间型	2020/6/30 23:39:21
date	返回标准日期格式的时间序列	数值型	标准日期型	2019/11/1
eomonth	获取指定日期所在月（或前后月）的最后一天	标准日期型	标准日期型	2020/6/30
weekday	获取指定日期的星期数	标准日期型	数值型	2
networkdays	返回两个日期之间的完整工作日数	标准日期型-date函数	数值型	21

注意：1.EOMONTH函数：months参数需要注意

0：表示当前月的最后一天

-1：之前一个月的最后一天

1：之后一个月的最后一天

2.WEEKDAY函数：return_type默认为1，则表示1-7为本周日至下周六

3.NETWORKDAYS函数：如果计算时间区间中有节假日，该公式支持计算，但需要额外准备节假日的数据

（7）补充：

a.单元格自定义：当格式自定义为：0"汉字"，此时该单元格输入的是数字型，可以参与计算

b.随机函数：=RANDBETWEEN(bottom,top)