基于Excel的数据分析方法课件

0102数据处理探索性数据分析目录CONTENTS

基于Excel的数据分析方法03挖掘预测0102数据处理探索性数据分析目录CONTENTS基于Ex01数据处理1、清洗2、关联3、统计4、时间序列01数据处理1、清洗清洗

数据分析的第一步是提高数据质量，统一数据标准，否则直接影响数据分析结论。针对拼写错误，数据异常点，数据缺失，无用信息等做初步处理

，主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。e.g.拼写错误：数据异常：格式不统一：←空格，导致匹配出错月发送量1000亿，高得不要不要的清洗数据分析的第一步是提高数据质量，统一数据标准清洗1、清除字符串两边的空格

Trim(text)2、字符串合并

Concatenate(text1，text2……)

常见的合并单元格中内容，还有方式是&,“A”&“B”＝AB。当需要合并的内容过多时，concatenate的效率更快。3、替换Replace(old_text,start_num,num_chars,new_test)常见的把手机号码后四位屏蔽掉，=Replace(,8,4,“****”)，返回结果1881884****Substitute(text,old_text,new_text,[instance_num])

最后一个参数Instance_num，当为确定数值时，用来指定以new_text替换第几次出现的old_text，如果忽略，则替换所有

old_text，也是它与replace的区别。=Substitute(,8,“*”,2)，返回结果18*18849894快捷方式：Ctrl+F，查找替换清洗1、清除字符串两边的空格清洗4、截取

Left

(test，num_chars)从左第一个位置开始截取，截取字符数为num_charsRight(test，num_chars)从右第一个位置开始截取，截取字符数为num_charsMid

(test，star_num，num_chars)

从指定位置（star_num）开始截取，截取字符数为num_chars5、返回字符串长度Len(test)，在len中，中文计算为一个，在Lenb中，中文计算为两个。

6、查找字符串Find(find_text,within_text,start_num)，返回的是find_text在within_text中的位置Find（要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找]）

与Left／Right／Mid结合能完成简单的文本提取。e.g.=FIND(“玄武”,“1898玄武科技”,1)，返回结果5=MID(“1898玄武科技”,FIND(“玄武”,“1898玄武科技”,1),4)，返回结果玄武科技Search(find_text,within_text,start_num)，和Find类似，区别是Search大小写不敏感，但支持＊通配符7、指定返回格式Text

将数值转化为指定的文本格式，平时多用于数据格式的处理，例如，保留百分比、保留两位小数、返回年月日/年月的格式。清洗4、截取关联匹配

在进行信息查找，多表关联，行列比对是多用到的函数，越复杂的表用得越多。1、匹配VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)，通俗的理解是从某列查找到某个值然后黏贴过来，查找顺序为从左到右HLOOKUP（查找目标，查找范围，返回值的行数，精确OR模糊查找)，行查找e.g.反向查询分公司=VLOOKUP(I3,IF({1,0},B2:B11,A2:A11),2,0)2、定位Match（查找指定的值，查找所在区域，查找方式的参数），返回查找指定值在查找区域的位置Index（查找的区域，区域内第几行，区域内第几列），返回查找区域指定范围的单元格Index和Match组合，媲美Vlookup，但是功能更强大。e.g.反向查询负责人=INDEX(A2:A11,MATCH(I8,B2:B11,0),1)关联匹配在进行信息查找，多表关联，行列比对是多用关联匹配3、

引用单元格

Indirect（ref_text,a1)

，通常excel常用A1引用样式，即a1参数为True或忽略查找A1的男朋友=INDIRECT(“A1”)，返回A1所对应的单元格内容查找A1的男朋友的女朋友=INDIRECT(A1)，返回A1所对应的单元格指向的内容e.g.

跨工作表引用：相对引用，查找“总表”工作表指定单元格的内容=INDIRECT("总表!"&"E"&"5")4、行、列的引用Row()返回单元格所在的行Column()返回单元格所在的列5、指定位置偏移

Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）

建立坐标系，以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。A1引用样式：这里的A就是列号，即A列，1表示行号，即第1行；所以在A1引用样式下，第1行第1列，用A1来表示，就是我们通常说的A1单元格。R1C1引用样式：这里的R就是Row的第一个字母，R1就是表示第1行，C就是Column的第一个字母，C1就是表示第1列；所以在R1C1引用样式下，第1行第1列就是用R1C1来表示。关联匹配3、引用单元格A1引用样式：逻辑1、条件if=if(logical_test,value_if_true,value_if_false)条件判断。当逻辑部分（logical_test）为真TRUE时，返回第二个参数value_if_true；当逻辑部分（logical_test）为假FALSE时，返回最后个参数value_if_false。2、并列满足条件

and(ogical_test1,ogical_test2,,,)

全部参数为True，则返回True，经常用于多条件判断。3、满足条件之一or(ogical_test1,ogical_test2,,,)只要参数有一个True，则返回Ture，经常用于多条件判断。4、IS系列常用判断检验，返回的都是布尔数值True和False。常用ISERR，ISERROR，ISNA，ISTEXT，可以和IF嵌套使用。逻辑1、条件if统计1、求和sum/sumif/sumifs2、计数count/countifs3、平均值average/averageif/averageifs4、最大/小值max/min、maxif/minif5、汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化

Substotal（参数，引用区域），实际应用于对筛选结果的计数、求和等6、统计总和相关，在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和

Sumproduct

(array1,[array2],[array3],…)，其中每个array都表示一个数组汇总求和条件求和条件计数7、排序=rank(number,ref,[order])，order=1，为正向，order=-1，为反向8、随机抽样Rand()返回0~1之间的随机值Randbetween(bottom,top）返回指定范围的随机数。9、其他统计函数四分位数、标准差、相关系数：Quartile、Stdev、Correl统计1、求和sum/sumif/sumifs日期函数1、配置日期=date（year,month,day）2、读取年月日year（value)、month(value)、day(value)3、月份数加减=edate(起始日期,月份数)月份数正数为前，负数为后4、计算日期差=DATEDIF(起始日期,结束日期，所需信息的返回时间单位代码)所需信息的返回时间单位代码，含义如下："y“返回时间段中的整年数"m”返回时间段中的整月数"d“返回时间段中的天数"md”参数1和2的天数之差，忽略年和月"ym“参数1和2的月数之差，忽略年和日"yd”参数1和2的天数之差，忽略年。按照月、日计算天数日期函数1、配置日期02探索性数据分析1、选择合适的表格/图表2、图表设计经验02探索性数据分析1、选择合适的表格/图表探索性数据分析1、汇总数据透视表/图数据透视表的主要功能是将数据聚合，按照各子段进行sum()，count()的运算，其核心思想是聚合运算，将字段名相同的数据聚合起来，所谓数以类分。列和行的设置，则是按不同轴向展现数据。简单说，你想要什么结构的报表，就用什么样的拖拽方式，支持对筛选条件统计结果。

如果说上节内容是“精耕细作”用于个性设置，那数据透视表则是“万金油”通配使用，日常主要用于实现以下功能：数据汇总

筛选唯一性

数据透视图探索性数据分析1、汇总探索性数据分析2、选择合适的图表进行数据展示探索性数据分析2、选择合适的图表进行数据展示探索性数据分析2、选择合适的图表进行数据展示折线图：按照时间序列分析数据的变化趋势时使用柱图：指定一个分析轴进行数据大小的比较时使用饼图：指定一个分析轴进行所占比例的比较时使用仪表图：单独关注一个指标的绩效表现时使用关于图表的选择：（1）图表是语言的一种形式，它的存在是为了比表格更快更好的表达你想要表达的内容（2）决定图表的不是数据也不是尺寸，而是你想说明的主题（3）图表贵精不贵多，只有当图表能帮你表达主题时才使用（4）图表是直观教具，但它不能取代书写和讲述，在帮你传达主题时，它能起到重要作用麦肯锡的《用图表说话》/view/0bcc20fd0b4c2e3f5627639a.html探索性数据分析2、选择合适的图表进行数据展示折线图：按照时间探索性数据分析2、选择合适的图表进行数据展示关于图表的设置：（1）简洁可读（2）重点突出（3）客观（4）色彩搭配简洁起伏波动差异较大，容易混淆解读者针对不同的业务性质，调整不同的纵坐标轴起始点，比如单价趋势探索性数据分析2、选择合适的图表进行数据展示关于图表的设置：03挖掘预测1、简单线性拟合2、时间序列预测03挖掘预测1、简单线性拟合1、看趋势2、计算相关性3、选择合适的拟合公式4、误差分析简单线性拟合1、看趋势简单线性拟合4、误差分析，检验预测结果误差大小，通过残差图检测，如果残差图成随机分布，则可认为回归模型通过检验。简单线性拟合SUMMARYOUTPUT回归统计MultipleR0.9641506RSquare0.9295864AdjustedRSquare0.9275864标准误差2.7142129观测值501方差分析

dfSSMSFSignificanceF回归分析148628.5548628.556600.90537.1946E-290残差5003683.47587.3669517总计50152312.026

Coefficients标准误差tStatP-valueLower95%Upper95%下限95.0%上限95.0%Intercept0#N/A#N/A#N/A#N/A#N/A#N/A#N/AXVariable10.31345320.003858181.2459563.03E-2900.3058731890.32103330.30587320.3210333RESIDUALOUTPUT观测值预测Y残差标准残差11.567266-1.16727-0.430492