EXCEL分析暑期实训报告

成果形式：实践报告成果名称：EXCEL分析EXCEL分析暑期实训报告一、实践目的本次实训的主要目的就是为了弥补学校对我们在商务数据分析方面的教育短板，通过这次培训加强自己对基本商务数据分析的理解，充分感受到商务数据分析所需要的基本知识和技能，并且能了解到商务数据分析的发展及应用前景，从而对自己专业以后的就业情况进行更一步了解。实训也是对我们每一个人专业知识的一种检验，它可以让我们学到更多在课堂上学不到的知识，既开阔了视野又增长了见识，为我们以后进一步走向社会打下坚实的基础，这是我们走向工作岗位的第一步。二、实践内容数据处理在于数据分析中是最重要的一步，其相关的工作时间占据了一个项目的70%以上。数据的质量，直接决定了模型的预测和泛化能力的好坏。它涉及很多因素，包括：准确性、完整性、一致性、时效性、可信性和解释性。而在真实数据中，我们拿到的数据可能包含了大量的缺失值，可能包含大量的噪音，也可能因为人工录入错误导致有异常点存在，非常不利于算法模型的训练。数据清洗的结果是对各种脏数据进行对应方式的处理，得到标准的、干净的、连续的数据，提供给数据统计、数据挖掘等使用。数据预处理的主要步骤分为：获取数据、清洗数据、数据抽取及合并、计算数据、数据类型转换、数据分析和数据展示（图表）。实训过程中也是从这几个方面进行详细介绍及具体的方法，如果在一个项目中，在这几个方面的数据处理做的还不错的话，那对于之后的工作就有了更大的帮助了，并且还能快速达到一个还不错的结果。三、实践过程第一步：获取数据方法一为手动输入数据，数据输入的类型有文本，数值，日期；文本的输入：直接输入；数值的输入：直接输入；较长数值的输入：英文单引号+数值（例：’61042319xxxxxxxxxx）;日期的输入：年月日的顺序，年月日之间用斜杆分隔（例：2019/08/12）;方法二为导入来自其他类型文件的数据若导入文本文档：第一步：设置语言格式第二步：设置分隔符第三步：设置各列数据格式数据库数据网站数据第二步：清洗数据1、重复数据的处理方法一为菜单删除法（删除重复值）方法二为标识法（标识值）方法三为高级筛选法（把重复值提取出来，放到其他位置）方法四为countif统计个数方法五为数据透视表2、缺失值的处理（1）、多个单元格中输入同一个值的方法：选中多个单元格——输入值——按ctrl+回车（2）、选中多个不连续空单元格的方法：开始——选择——定位条件——空值3、空白值的处理方法1：替换方法2：函数：trim（）第三步、数据抽取及合并1、 数据抽取数据抽取是从数据源中抽取数据的过程，其分为字段拆分和记录随机抽取（1）、字段拆分方法一：菜单法步骤：选中列——数据——分列第一步：设置固定宽度第二步：设置分列线第三步：设置各列类型，选择目标位置方法二：函数法Mid（）从字符串中，指定位置起，返回指定长度的字符Left（）从字符串中，第一个字符开始，返回指定长度的字符Right（）从字符串中，最后一个字符开始，返回指定长度的字符（2）、记录随机抽取①、生成随机数rand（）②、对随机数排序rank（）③、把排序结果中，前200名数据提取出来Vlookup（）搜索提取函数第一空：要搜索的值第二空：搜索区域注意：要绝对引用第三空：要返回的值所在的列编号第四空：false（精确匹配）/true（大致匹配）Rank（）排序函数第一空：要排序的数第二空：区域（要绝对引用）第三空：0（降序）/1（升序）2、 数据合并（1）、列的合并方法一：函数concatenate（）方法二：连接符&方法三：日期连接函数date（）例：date（2019,8,13）方法四：条件匹配vlookupVlookup的执行原理，从区域（第二个空）的首列，搜索第一个空的值Vlookup（1个号码，区域，列号，false）Ppt中的多条件匹配，使用了excel数组运算数组运算执行方法：ctrl+shift+回车第四步：数据计算1、 简单计算：加（+）减（－）乘（*）除（/）2、 日期计算：datedif（起始日期，终止日期，格式）作用：计算起始和终止日期之间所经历的时长时长格式：y：以年显示m：以月显示d：以日显示Year（）提取日期中的年份Month（）提取日期中的月Day（）提取日期中的日Today（）返回当前系统日期3、 标准化计算：使一些数据的异常值，落入到正常的0-1的区间计算方法：x标准化＝（x－最小值）/（最大值－最小值）Max（）求最大值Min（）求最小值4、 加权求和：通过加权，让数据的占比均化Sumproduct（）实质：各数据乘以权重，并求各乘积的和5、 分组［0,50）［50,100）［100,∞）方法一：if函数分组If（条件判断，条件为真时的结果，条件为假时的结果）方法二：vlookup函数模糊匹配分组操作：vlookup函数的第四个空填‘true’第五步、数据类型转换1、 行/列转换操作：复制数据区域——到目标位置——右击——选择性粘贴对话框中，选择‘转置’2、 文本到数值方法一：分列操作：选中数据区域——数据——分列——分列第三步选择‘常规’类型方法二：选择性粘贴——运算操作：先复制‘1’——选中数据——选择粘贴——运算方法三：智能标记3、 数值——文本方法一：分列分列二：函数text（数值，‘格式’）常用格式：小数（0.00）百分比（0.0%）日期（00年00月00日）4、 数值——日期方法一：分列5、 二维表转换为一维表方法：使用数据透视表制作向导操作：1、ALT+D+P（打开向导对话框）2、多重合并数据区域3、自定义页字段4、选择数据区域5、双击数据透视表的总计值（可把二维转一维）第六步：数据分析1、 对比分析环比：同一年中，上个月和下个月的数据比较（3月－2月）/2月同比：两年中，同一时间的比较（2012年－2011年）/2011年★注意：数据透视表中，计算环比：选择‘值显示方式’为差异百分比，选择字段为‘注册时间’，基本项为‘上一个’。计算同比：选择‘值显示方式’为差异百分比，选择字段为‘年’，基本项为‘上一个’。2、结构分析（占比分析）占比：一个项目中，各决定因素所在的比例计算方法：透视表中，值显示方式——列汇总百分比/行汇总百分比3、分布分析：根据分析的目的，将数值型数据进行等距或不等距的分组分组：相应值落入到各组中的比率方法一：vlookup方法分组特点：可以进行不等距分组操作：用vlookup分组后，用数据透视表统计分组结果方法二：数据透视表分组特点：只能进行等距分组操作：直接用数据透视表的分组功能完成即可例：0-60不及格60-75及格75-90良好90-100优秀4、交叉分析目的：从两个维度对我们的客户进行分类操作：（1）、用vlookup确定各客户，两个维度的性质（2）、用数据透视表，从两个维度进行分类，来统计各分类的客户人数（3）、从一维表的角度，查看客户分类情况的操作（1.以表格形式显示2.重复所有标签3.不显示汇总结果）5、矩阵分析操作：1、先进行定性分组，对数据进行平均值的计算平均值的计算方法：在透视表的计算区域上，右击——值字段设置——平均值2、复制透视表数据到新的位置注意：粘贴数据时，用‘选择性粘贴’中的值3、制作散点（矩阵）图选中月平均消费和月平均流量的值——插入——散点图注意：不选行/列标签及总计4、散点图x/y坐标轴，移动到平均值交叉位置方法：在坐标轴上右击——坐标轴格式，设置即可5、重新绘制x/y的坐标轴6、给散点添加标签（1）、在点上右击——添加标签（2）、选中标签——右击——设置标签格式——单元格中的值——选择文字性的标签——取消y值6、多表关联分析：通过地区和通讯品牌两个维度，统计消费用户数操作：1、分析两个表之间为何的连接字段（列）注意：连接字段，就是两个表的公共列（例如：省份）2、把两个表添加到模板中方法：插入——表格3、插入——透视表——选择多个表4、进行多表连接方法：透视表工具——分析——关系5、数据分析即可案例：各手机品牌是否开通微信的用户数（1）、手机品牌，微信公共列为：号码（2）、用透视表做关联①、把表放到模板中②、插入——透视表——添加多个表案例：各地区是否开通微信的用户数地区：地区微信：号码表７、RFM分析（从三个角度对用户进行分类）R：最近一次的消费时间（时长）F：最近的消费次数M：消费额度步骤：1.计算R.F.M的值（用数据透视表完成）R：日期最大值F：订单ID计数M：金额求平均值用客户ID定性分组2.把上面透视表结果复制到新表中（粘贴时，使用选择性粘贴的‘值’）注意：把R用datedif换算成天数3.对R.F.M评分4.用透视表，对评分结果做分析第七步：数据展现EXCEL图表：饼图：占比成分操作1：图表各对象格式设置方法：在对象上右击——选择相应操作操作2：图表布局操作3：图表设计（数据选择，图表类型更改）制作图表方法：选中做图表的数据——插入——图表类型——进行图表设置1、 双坐标图表用途：把量级差别较大或单位不同的数据，在一个图表上表示步骤：（1）、制作图表（柱形图）（2）、把较小单位的值，用次坐标轴表示方法：选中图表——右击——设置格式——次坐标轴（3）、修改次坐标轴，图表类型为折线图格式设置：①、文字大小，方向②、坐标轴的刻度③、隐藏次坐标轴图例一2、 目标完成率图作用：反映业务目标的完成情况操作：类似双坐标轴操作★注意：把完成值绘制在次坐标轴上格式设置：（1）、系列图形的填充色，线条色，系列间隙宽度（2）、隐藏次坐标轴（3）、给完成值添加完成率的数据标签图例二3、雷达图作用：系列有2组以上数据时，用该图操作：选中数据——插入——雷达图格式设置：系列宽度设置在1以下图例三4、矩阵图作用：用两组相关数据，对客户进行分类操作：（1）、选择数据（不选行/列标题和平均值）（2）、给每个点添加标签（行标题）（3）、移动x/y坐标轴到平均值位置（4）、重新绘制x/y坐标图例四5、迷你图作用：当数据系列比较多时，快速查看每个系列的趋势或变化情况操作方法：光标放在迷你图位置——插入——迷你图——选择类型设置内容：（1）、图表样式（2）、设置高点，低点图例五6、漏斗图作用：一般用来表示，一个商业行为的变化过程例如：购物（浏览产品——放入购物车——下单——支付——完成）操作：选中数据——插入——堆积条形图注意：（1）、逆序系列标签方法：选中坐标轴——右击——设置格式——逆序类别（2）、添加占位数据方法：在图表上右击——添加数据注意：把占位数据放到系列数据的前面（3）、把占位数据的图形，填充和线条都设置为无（4）、形成封闭的漏斗方法：图表工具——设计——添加元素——线条——系列线图例六7、旋风图作用：展现不同数据在同一组指标下比较结果操作：选中数据——插入——堆积条形图注意：（1）、绘制其中一组数据到次坐标轴（2）、修改主次坐标轴的刻度最小值：负数最大值：负数绝对值（3）、将次坐标轴刻度进行逆序方法：在次坐标轴上右击——逆序刻度值格式设置：（1）、将坐标轴标签移动到左侧方法：设置标签位置为‘低’（2）、隐藏次坐标轴方法：设置次坐标轴标签为‘无’（3）、修改主坐标轴的数字格式为‘0：0：0’