《基于excel的数据分析方法资料》由会员分享，可在线阅读，更多相关《基于excel的数据分析方法资料（19页珍藏版）》请在金锄头文库上搜索。
1、01,02,数据处理,探索性数据分析,目录,CONTENTS,基于Excel的数据分析方法,03,挖掘预测,01,数据处理,1、清洗 2、关联 3、统计 4、时间序列,清洗,数据分析的第一步是提高数据质量，统一数据标准，否则直接影响数据分析结论。针对拼写错误，数据异常点，数据缺失，无用信息等做初步处理 ，主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。,e.g. 拼写错误： 数据异常： 格式不统一：, 空格，导致匹配出错,月发送量1000亿，高得不要不要的,清洗,1、清除字符串两边的空格 Trim(text)
2、 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 ，当为确定数值时，用来指
3、定以 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）开始截
4、取，截取字符数为 num_chars 5、返回字符串长度 Len(test)，在len中，中文计算为一个，在Lenb中，中文计算为两个。 6、查找字符串 Find ( find_text, within_text, start_num )，返回的是 find_text 在within_text 中的位置 Find（要查找的文本，文本所在的单元格，从第几个字符开始查找可选，省略默认为1，从第一个开始查找） 与LeftRightMid结合能完成简单的文本提取。 e.g. =FIND(“玄武”, “1898玄武科技”,1)，返回结果 5 =MID(“1898玄武科技”,FIND(“玄武”, “189
5、8玄武科技”,1),4)，返回结果 玄武科技 Search ( find_text, within_text, start_num ) ，和Find类似，区别是Search大小写不敏感，但支持通配符 7、指定返回格式 Text 将数值转化为指定的文本格式，平时多用于数据格式的处理，例如，保留百分比、保留两位小数、返回年月日/年月 的格式。,关联匹配,在进行信息查找，多表关联，行列比对是多用到的函数，越复杂的表用得越多。,1、匹配 VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找) ，通俗的理解是从某列查找到某个值然后黏贴过来，查找顺序为从左到右 HLOOKUP（查找目标，查找
6、范围，返回值的行数，精确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引
7、用样式，即a1参数为True或忽略 查找A1的男朋友 =INDIRECT(“A1”)，返回 A1所对应的单元格内容 查找A1的男朋友的女朋友 =INDIRECT(A1)， 返回 A1所对应的单元格指向的内容 e.g. 跨工作表引用： 相对引用，查找“总表”工作表指定单元格的内容 =INDIRECT(“总表!“&“E“&“5“) 4、行、列的引用 Row() 返回单元格所在的行 Column() 返回单元格所在的列 5、指定位置偏移 Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列） 建立坐标系，以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反。,A1
8、引用样式： 这里的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
9、时，返回最后个参数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/sumifs 2、计数 count/countifs 3、平均值 average
10、/averageif/averageifs 4、最大/小值 max/min、 maxif/minif 5、汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化 Substotal（参数，引用区域 ），实际应用于对筛选结果的计数、求和等 6、统计总和相关，在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 Sumproduct (array1, array2, array3,)，其中每个array都表示一个数组 汇总求和 条件求和 条件计数 7、排序 =rank (number,ref,order)，order=1，为正向， order=-1，为反向 8、随机抽样 Ra
11、nd () 返回01之间的随机值 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”返回
12、时间段中的整月数 “d“ 返回时间段中的天数 “md”参数1和2的天数之差，忽略年和月 “ym“参数1和2的月数之差，忽略年和日 “yd”参数1和2的天数之差，忽略年。按照月、日计算天数,02,探索性数据分析,1、选择合适的表格/图表 2、图表设计经验,探索性数据分析,1、汇总 数据透视表/图 数据透视表的主要功能是将数据聚合，按照各子段进行sum( )，count( )的运算，其核心思想是聚合运算，将字段名相同的数据聚合起来，所谓数以类分。列和行的设置，则是按不同轴向展现数据。简单说，你想要什么结构的报表，就用什么样的拖拽方式，支持对筛选条件统计结果。 如果说上节内容是“精耕细作”用于个性设
13、置，那数据透视表则是“万金油” 通配使用，日常主要用于实现以下功能： 数据汇总 筛选唯一性 数据透视图,探索性数据分析,2、选择合适的图表进行数据展示,探索性数据分析,2、选择合适的图表进行数据展示,折线图：按照时间序列分析数据的变化趋势时使用 柱 图：指定一个分析轴进行数据大小的比较时使用 饼 图：指定一个分析轴进行所占比例的比较时使用 仪表图：单独关注一个指标的绩效表现时使用 关于图表的选择： （1）图表是语言的一种形式，它的存在是为了比表格更快更好的表达你想要表达的内容 （2）决定图表的不是数据也不是尺寸，而是你想说明的主题 （3）图表贵精不贵多，只有当图表能帮你表达主题时才使用 （4）
14、图表是直观教具，但它不能取代书写和讲述，在帮你传达主题时，它能起到重要作用 麦肯锡的用图表说话 ,探索性数据分析,2、选择合适的图表进行数据展示,关于图表的设置： （1）简洁可读 （2）重点突出 （3）客观 （4）色彩搭配简洁,起伏波动差异较大，容易混淆解读者 针对不同的业务性质，调整不同的纵坐标轴起始点，比如单价趋势,03,挖掘预测,1、简单线性拟合 2、时间序列预测,1、看趋势 2、计算相关性 3、选择合适的拟合公式 4、误差分析,简单线性拟合,4、误差分析，检验预测结果误差大小，通过残差图检测，如果残差图成随机分布，则可认为回归模型通过检验。,简单线性拟合,时间序列预测,Excel 2016 有一项功能叫“预测工作表”，可以从历史数据分析出事物发展的未来趋势，并以图表的形式展现出来，对于直观地观察事物发展方向或发展趋势，有一定帮助。,
