基于Excel的数据分析方法

0102数据处理探索性数据分析目录CONTENTS基于Excel的数据分析方法03挖掘预测01数据处理1、清洗2、关联3、统计4、时间序列清洗数据分析的第一步是提高数据质量，统一数据标准，否则直接影响数据分析结论。针对拼写错误，数据异常点，数据缺失，无用信息等做初步处理，主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。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(“18818849894”,8,4,“****”)，返回结果1881884****•Substitute(text,old_text,new_text,[instance_num])最后一个参数Instance_num，当为确定数值时，用来指定以new_text替换第几次出现的old_text，如果忽略，则替换所有old_text，也是它与replace的区别。=Substitute(“18818849894”,8,“*”,2)，返回结果18*18849894•快捷方式：Ctrl+F，查找替换清洗4、截取•Left(test，num_chars)从左第一个位置开始截取，截取字符数为num_chars•Right(test，num_chars)从右第一个位置开始截取，截取字符数为num_chars•Mid(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将数值转化为指定的文本格式，平时多用于数据格式的处理，例如，保留百分比、保留两位小数、返回年月日/年月的格式。关联匹配在进行信息查找，多表关联，行列比对是多用到的函数，越复杂的表用得越多。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来表示。逻辑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、求和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、配置日期=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的天数之差，忽略年。按照月、日计算天数02探索性数据分析1、选择合适的表格/图表2、图表设计经验探索性数据分析1、汇总数据透视表/图数据透视表的主要功能是将数据聚合，按照各子段进行sum()，count()的运算，其核心思想是聚合运算，将字段名相同的数据聚合起来，所谓数以类分。列和行的设置，则是按不同轴向展现数据。简单说，你想要什么结构的报表，就用什么样的拖拽方式，支持对筛选条件统计结果。如果说上节内容是“精耕细作”用于个性设置，那数据透视表则是“万金油”通配使用，日常主要用于实现以下功能：•数据汇总•筛选唯一性•数据透视图探索性数据分析2、选择合适的图表进行数据展示探索性数据分析2、选择合适的图表进行数据展示折线图：按照时间序列分析数据的变化趋势时使用柱图：指定一个分析轴进行数据大小的比较时使用饼图：指定一个分析轴进行所占比例的比较时使用仪表图：单独关注一个指标的绩效表现时使用关于图表的选择：（1）图表是语言的一种形式，它的存在是为了比表格更快更好的表达你想要表达的内容（2）决定图表的不是数据也不是尺寸，而是你想说明的主题（3）图表贵精不贵多，只有当图表能帮你表达主题时才使用（4）图表是直观教具，但它不能取代书写和讲述，在帮你传达主题时，它能起到重要作用麦肯锡的《用图表说话》探索性数据分析2、选择合适的图表进行数据展示关于图表的设置：（1）简洁可读（2）重点突出（3）客观（4）色彩搭配简洁241480378438467487515512000000100200300400500600241480378438467487515512200300400500600•起伏波动差异较大，容易混淆解读者•针对不同的业务性质，调整不同的纵坐标轴起始点，比如单价趋势0.03900.04420.03900.02000.02500.03000.03500.04000.04500.05007月8月9月10月11月12月1月2月3月4月5月6月7月03挖掘预测1、简单线性拟合2、时间序列预测1、看趋势2、计算相关性3、选择合适的拟合公式4、误差分析简单线性拟合y=0.312x+0.2458R²=0.92710204060801001201401600100200300400500发送量毛利4、误差分析，检验预测结果误差大小，通过残差图检测，如果残差图成随机分布，则可认为回归模型通过检验。简单线性拟合SUMMARYOUTPUT回归统计MultipleR0.9641506RSquare0.9295864AdjustedRSquare0.9275864标准误差2.7142129观测值501方差分析dfSSMSFSignificanceF回归分析148628.5548628.556600.90537.1946E-290残差5003683.47587.3669517总计50152312.026Coefficients标准误差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.3210333-40-20020400100200300400500残差XVariable1XVariable1残差图0204060801001201401600100200300400500YXVariable1XVariable1线性拟合图Y预测YRESIDUALOUTPUT观测值预测Y残差标准残差11.567266-1.16727-0.4304920.238224-0.01022-0.0037730.35571-0.01527-0.0056340.090852-0.03288-0.01213时间序列预测Excel2016有一项功能叫“预测工作表”，可以从历史数据分析出事物发展的未来趋势，并以图表的形式展现出来，对于直观地观察事物发展方向或发展趋势，有一定帮助。time月发送量趋势预测(月发送量)置信下限(月发送量)置信上限(月发送量)2017/7