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

采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物0102数据处理探索性数据分析目录CONTENTS 基于Excel的数据分析方法03挖掘预测采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物01数据处理1、清洗2、关联3、统计4、时间序列采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物清洗 数据分析的第一步是提高数据质量，统一数据标准，否则直接影响数据分析结论。针对拼写错误，数据异常点，数据缺失，无用信息等做初步处理，主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。数据越多，这个步骤花费的时间越长。e.g.拼写错误：数据异常：格式不统一：空格，导致匹配出错月发送量1000亿，高得不要不要的采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物清洗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，查找替换采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物清洗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，从第一个开始查找）与LeftRightMid结合能完成简单的文本提取。e.g.=FIND(“玄武”,“1898玄武科技”,1)，返回结果 5 =MID(“1898玄武科技”,FIND(“玄武”,“1898玄武科技”,1),4)，返回结果 玄武科技 Search(find_text,within_text,start_num)，和Find类似，区别是Search大小写不敏感，但支持通配符7、指定返回格式 Text 将数值转化为指定的文本格式，平时多用于数据格式的处理，例如，保留百分比、保留两位小数、返回年月日/年月 的格式。采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物关联匹配 在进行信息查找，多表关联，行列比对是多用到的函数，越复杂的表用得越多。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)采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物关联匹配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来表示。采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物逻辑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嵌套使用。采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物统计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()返回01之间的随机值 Randbetween(bottom,top）返回指定范围的随机数。9、其他统计函数四分位数、标准差、相关系数：Quartile、Stdev、Correl采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物日期函数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的天数之差，忽略年。按照月、日计算天数采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物02探索性数据分析1、选择合适的表格/图表2、图表设计经验采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物探索性数据分析1、汇总 数据透视表/图 数据透视表的主要功能是将数据聚合，按照各子段进行sum()，count()的运算，其核心思想是聚合运算，将字段名相同的数据聚合起来，所谓数以类分。列和行的设置，则是按不同轴向展现数据。简单说，你想要什么结构的报表，就用什么样的拖拽方式，支持对筛选条件统计结果。如果说上节内容是“精耕细作”用于个性设置，那数据透视表则是“万金油”通配使用，日常主要用于实现以下功能：数据汇总 筛选唯一性 数据透视图采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物探索性数据分析2、选择合适的图表进行数据展示采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物探索性数据分析2、选择合适的图表进行数据展示折线图：按照时间序列分析数据的变化趋势时使用柱图：指定一个分析轴进行数据大小的比较时使用饼图：指定一个分析轴进行所占比例的比较时使用仪表图：单独关注一个指标的绩效表现时使用关于图表的选择：（1）图表是语言的一种形式，它的存在是为了比表格更快更好的表达你想要表达的内容（2）决定图表的不是数据也不是尺寸，而是你想说明的主题（3）图表贵精不贵多，只有当图表能帮你表达主题时才使用（4）图表是直观教具，但它不能取代书写和讲述，在帮你传达主题时，它能起到重要作用麦肯锡的用图表说话 https:/ 480 378 438 467 487 515 512 0 0 0 0 0 0 100 200 300 400 500 600 2016年1月2月3月4月5月6月7月8月9月10月11月12月241 480 378 438 467 487 515 512 200 300 400 500 600 2016年1月2月3月4月5月6月7月8月9月10月11月12月 起伏波动差异较大，容易混淆解读者 针对不同的业务性质，调整不同的纵坐标轴起始点，比如单价趋势0.03900.04420.03900.02000.02500.03000.03500.04000.04500.05007月 8月 9月 10月 11月 12月 1月2月 3月 4月 5月 6月 7月采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物03挖掘预测1、简单线性拟合2、时间序列预测采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物1、看趋势2、计算相关性3、选择合适的拟合公式4、误差分析简单线性拟合y=0.312x+0.2458R2=0.92710204060801001201401600100200300400500发送量毛利采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物4、误差分析，检验预测结果误差大小，通过残差图检测，如果残差图成随机分布，则可认为回归模型通过检验。简单线性拟合SUMMARY OUTPUT回归统计Multiple R0.9641506R Square0.9295864Adjusted R Square0.9275864标准误差2.7142129观测值501方差分析dfSSMSFSignificance F回归分析148628.5548628.55 6600.90537.1946E-290残差500 3683.4758 7.3669517总计501 52312.026Coefficients标准误差t StatP-valueLower 95%Upper 95%下限 95.0%上限 95.0%Intercept0#N/A#N/A#N/A#N/A#N/A#N/A#N/AX Variable 1 0.3134532 0.0038581 81.245956 3.03E-2900.305873189 0.3210333 0.3058732 0.3210333-40-20020400100200300400500残差残差X Variable 1X Variable 1 残差图残差图0501001502000100200300400500YX Variable 1X Variable 1 线性拟合图线性拟合图Y预测 YRESIDUAL OUTPUT观测值预测 Y残差标准残差1 1.567266-1.16727-0.430492 0.238224-0.01022-0.0037730.35571-0.01527-0.005634 0.090852-0.03288-0.01213采用PP管及配件：根据给水设计图配置好PP管及配件，用管件在管材垂直角切断管材，边剪边旋转，以保证切口面的圆度，保持熔接部位干净无污物时间序列预测 Excel 2016 有一项功能叫“预测工作表”，可以从历史数据分析出事物发展的未来趋势，并以图表的形式展现出来，对于直观地观察事物发展方向或发展趋势，有一定帮助。time月发送量趋势预测(月发送量)置信下限(月发送量)置信上限(月发送量)2017/7/1737.3737.3737.3737.32017/8/1826.0698.8953.22017/9/1873.9731.71016.12017/10/1918.6753.01084.22017/11/1973.4776.81170.02017/12/11089.8855.61324.02018/1/11048.4771.11325.62018/2/11005.2680.11330.22018/3/11092.4715.51469.32018/4/11109.5677.11541.92018/5/11120.2628.91611.52018/6/11142.4589.21695.7