Excel商务数据分析实训总结

成果形式：实践报告成果名称：Excel商务数据分析实训总结实践报告一、实践目的1.弥补高校在商务数据分析方面的短板，此次培训通过基本商务数据分析的讲解，让高校在校生充分感受到商务数据分析所需要的基本知识和技能。2.让学生了解到目前商业数据的发展及应用场景，对自己的职业定位有个更加清晰的认识。3.学生了解商务数据的重要作用与应用。4.学生学会商务数据分析的基本方法，技能。为以后从事更深层次的数据科学内容做准备。5.让学生了解数据分析和挖掘岗位目前的就业形式和前景及所需相关技能，了解企业实际需求。对于自身以后的学习方向和就业范围有初步的印象，为走好数据分析方向的职业道路提供有力的指引。二、实践内容EXCEL数据预处理EXCEL数据计算EXCEL数据分析EXCEL图表数据展示EXCEL分析实战三、实践过程第一天，我们学习了数据的导入。首先，我们明确了实训纪律要求，主要包含以下几个要点：1、原则上不允许请假；2、上课期间不允许玩手机和睡觉；3、每天上午、下午上课均要签到；4、每天要写实训课程日志，将每天练习项目等内容填入实训日志，实训结束要写个人总结，实训最终会有查重和项目考核分数；5、注意打扫教室卫生；6、本次实训要服从指导老师的安排和管理，有问题可以举手示意。接着，进行简要的Excel介绍以及了解了Excel数据分析课程大纲：获取数据—脏数据（清洗数据）--计算数据—数据分析—数据展示（图表）获取数据有以下两种方法。方法一：手动输入数据法—较简单！{数据分类：文本、数值、日期。}；文本的输入：直接输入；数值的输入：主要是较长数据的输入，英文单引号+数值；日期的输入：年月日的顺序，年月日之间用斜杠/分隔}。方法二：导入来自其他类型文件的数据，点击数据—获取外部数据。(1)文本导入：数据—自文本—设置语言格式—设置分隔符—设置各列数据格式。（先要将语言设置为任意一种简体中文，例题中分隔符是逗号，一般较长数据设置为文本格式）;(2)数据库导入：数据—自Access—省略。(3)网站导入：数据—自网站—输入包含数据的网址—选择数据—导入即可。第二天学习了数据的清理及数据的抽取：1.重复数据的清理(1)菜单删除法：选中所有数据--数据—删除重复项（只保留去掉重复值的结果）；操作：选中数据区域(ctrl+shift+向下)不包含字段名，在菜单栏找到数据，“删除重复项”对话框打开后，勾选所有复选框、即所有字段名。(2)条件格式标识法：选择一列数据—开始—条件格式—突出显示规则—重复值（能标识出重复项，但是无法去掉重复值）；操作：只选择号码列(ctrl+shift+向下)，在菜单栏找到开始，条件格式“突出显示重复值”，设置填充、字体格式。(3)高级筛选法：数据—筛选中的高级（既能提取出重复值，还能将其放置到其他位置，但是不能显示出重复了几次）；操作：选择所有数据区域(ctrl+shift+向下)，菜单栏找到数据，筛选中“高级筛选”，弹出对话框，如有需要点击将筛选结果复制到其他位置，勾选“选择不重复数据”，即可得到所有的不重复记录。(4)函数法：用到函数countif(range筛选的范围,criteria筛选条件).目的：计算数据的重复次数.操作：在“重复次数”列，选择放结果的单元格C2，输入“=countif”，利用编辑栏打开函数对话框，在第一空选择数据区域(ctrl+shift+向下)、注意绝对引用，第二空A2，回车，向下填充复制。(5)数据透视表：概念—从多个角度对数据进行汇总统计的表格，数据透视表字段对话框及作用分别为：筛选—筛选数据、行/列—分类、值—计算（既能计算重复次数，又能显示去掉重复值项后的结果）操作：选择整个数据区域，在菜单栏找到插入，单击数据透视表，一般创建在新工作表，有时也可放置在现有工作表，在数据透视表的右侧会弹出字段列表，将“号码”依此拉入到行标签、数值字段对话框。这样既能排列出去掉重复值的结果，又能显示每个号码的重复次数。2.缺失数据的处理(1)多个单元格中输入同一个值的操作：选中多个目标单元格—输入值—按Ctrl+回车；(2)当多个目标单元格不连续时的操作：选中多个不连续的空单元格—开始—选择—定位条件—空值—输入值—按Ctrl+回车。3.空白数据的处理(1)菜单“替换”法；操作：选择出字段名以外的数据区域，菜单栏找到“开始-编辑-查找与替换”，替换对话框打开，第一空填入空格，点击全部替换即可。(2)函数trim()；操作：在存放结果单元格输入：=trim()，利用编辑栏打开函数对话框，只有一个参数，填入要删除空格的字符串A2。4.字段拆分(1)菜单法操作：选中列—数据—分列，第一步、设置固定宽度，第二步、设置分列线，第三步、设置各列格式，较长数据设置为“文本”格式，不需要的数据列设置为“不导入此列”，最后、选择目标单元格；(2)函数法：mid()从字符串中，指定位置起，返回指定长度的字符、left()从字符串中，第一个字符开始，返回指定长度的字符、right()从字符串中，最后一个字符开始，返回指定长度的字符。5.记录随机抽取：第一步、生成随机数rand()，第二步、对随机数排序rank(),第三步、把排序结果中，前200名数据提取出来vlookup()，在此操作之前，首先填充1-200，操作如下：开始—填充—序列—打开对话框后：序列产生在：“列”，类型“等差序列”，步长值“1”，终止值“200”第三天学习了数据的计算1.简单的加减乘除计算；注意：一般这样的简单加减乘除都是相对引用，但也不乏绝对引用的情况，要留意判断。2.日期计算：datedit（起始日期，终止日期，时间格式）该函数的作用：计算起始日期和终止日期之间经历的时长。时间格式：y-以年显示、m-以月显示、d-以日显示3.标准化计算：常用的数据标准化为0-1标准化，它使得一些数据的异常值，落入到0-1的区间。计算方法：x标准化=(x-最小值)/(最大值-最小值)Max()求最大值min()求最小值4.加权求和：通过加权，可以使数据的占比均化。函数法：Sumproduct(区域1,区域2).它的实质：各数据乘以权重，并求各乘积的和注意：数据区域2，即各个权重要绝对引用！5.数据分组：(1)if函数分组，(2)vlookup函数模糊匹配分组，(3)数据透视表接下来认识了二维表以及学习了数据的几种类型转换1.二维表就是由行列组成的，知道行号列号就可以确定一个表中的数据，这是二维表的特点。在关系数据库中，存放在数据库中的数据的逻辑结构一二维表为主。2.行列转换：操作—复制数据区域，定位到目标位置，右键，在粘贴选项中选择转置项。2.文本到数值的转换靠左排列的是文本型数据，不能计算；靠右排列的是数值型数据。方法一：分列(优先使用)操作：选中数据区域，菜单栏中找到数据，分列，它的前两步跳过，默认点击“下一步”，在分列操作的第三步选择“常规”类型（数据格式将数值转换为数字）方法二：利用选择性粘贴操作：先复制”1”、选中需要转换的数据区域，右键打开选择性粘贴对话框，点击运算中的”乘”还可以先复制”0”、选中需要转换的数据区域，右键打开选择性粘贴对话框，点击运算中的”加”或者”减”。方法三：智能标记法操作：选择需要转换的数据区域，选中后在所选区域右侧有一个智能标记(黄色菱形)，单击下拉符，选择转换为数字(第二项)。3.数值到文本的转换方法一：分列(优先使用)操作：选中数据区域，菜单栏中找到数据，分列，它的前两步跳过，默认点击“下一步”，在分列操作的第三步选择“文本”类型方法二：用函数text(数值，格式)函数作用：将数值转换为文本，根据自己需要指定显示格式参数：(1)、要改变格式的单元格，(2)、要做的转换类型，需要用””引起来.常用格式：小数（0.00）百分比(0.0%)日期（00年00月00日）操作：在需要转换的数据旁边空格处，即B2处利用编辑栏插入函数text，输入“=text(A2/10000，”0.0万”)”4.数值到日期的转换方法1：分列操作：选中需要转换的区域，菜单栏找到数据，分列，跳过前两步，在第三步选择格式：日期型YMD方法2：利用对话框选中需要转换的区域，右键打开设置单元格格式对话框，选择日期、选一种格式方法3：菜单法(不推荐使用)5、二维表转换为一维表判断：判断数据表是一维表还是二维表最简单的一个方法就是看列的内容，看字段名是否是一个独立的参数。如果每一列都是独立的参数那就是一维表，如果有两列及以上都是同类参数那就是二维表。可以看字段名来判断表格的维数。方法：使用数据透视表向导Excel2010操作：(1)、添加“数据透视表和数据透视图向导”功能：鼠标放置在菜单栏“文件”处右击，选择自定义功能区(2)、打开对话框后，在“从下列位置选择命令”中选择“不在功能区中的命令”，选择“数据透视表和数据透视图向导”(3)、在“自定义功能区”选择“主选项卡”，在“数据”主选项卡下面添加“新建组”，点击“添加”，把“数据透视表和数据透视图向导”添加到“数据”主选项卡的“新建组”中，点击“确定”(4)、”Alt+D”+”P”，打开数据透视表和数据透视图向导对话框，在数据源类型中选择“多重合并计算数据区域”，点击下一步(5)、选择“自定义页字段/(创建单页字段)”、单击“下一步”；(6)、在“选定区域”选择整个表格的二维表数据区域，点击“添加”、“下一步”(7)、选择“新工作表”，点击“完成”(8)、在表格右侧“数据透视表字段”窗格，取消“选择要添加到报表的字段”列表中的“列”、“行”(可省略)(9)、双击求和值，完成一维表的创建6.数据分析(1)对比分析—对时间分组计算方法：选择‘值显示方式’为差异百分比。(2)结构/占比分析—定性分组计算方法：透视表中，值显示方式—列汇总百分比/行汇总百分比(3)分布分析—定量分组计算方法：vlookup、数据透视表(4)交叉分析--定性分组（5）矩阵分析--将消费人群进行分类操作步骤：(1)先进行定性分组，对数据进行平均值的计算~~~平均值计算方法：在透视表的计算区域上，右击—值字段设置—平均值(2)复制透视表数据到新的位置，用“选择性粘贴”中的值(3)制作散点(矩阵)图：方法如下，选中月平均消费和月平均流量的值，插入散点图。注意：不选行列标签及总计(4)散点图x/y坐标轴，移动到平均值交叉位置。方法：在坐标轴上右击—坐标轴格式，设置即可(5)重新绘制x/y的坐标轴(6)给散点添加标签操作方法1：选中其中一个点---启动XYChartLabels---打开安装目D:\AppsPro\ChartLabeler---XYChartLabeler.xla加载项---XYChartLabels---AddChartlabels--选择标签范围(SelectaLabelRange)---行标签(大区所有内容)操作方法2：(1)在点上右击--添加标签 (2)选中标签--右击--设置标签格式--单元格中的值--选择文字性的标签--取消Y值(6).多表关联分析操作步骤：(1)分析两个表之间的连接字段（列）；注意：连接字段，就是两个表的公共列(例如：省份)(2)把两个表添加到模板中；方法：插入--表格(3)插入--透视表--选择多个表；(4)进行多表连接；方法：透视表工具--分析--关系(5)数据分析即可。(7)RFM分析：从三个角度对用户进行分类R:最近一次的消费时间（时长）F:最近的消费次数M:消费额度步骤：(1)用客户ID定性分组,计算R,F,M值(用数据透视表完成) R：日期最大值；F：订单ID计数； M:金额求平均值 (2)把上面透视表结果复制，到新表中（粘贴时，使用选择性粘贴的值）----注意：把R用datedif换算成天数 (3)对R,F,M评分 (4)用透视表，对评分结果做分析7.数据展现(excel图表工具)（1）双坐标轴图 作用：把量级差别较大或单位不同的数据，在一个图表上表示 操作：选中数据--插入--柱形图设置内容：1.文字格式----2.绘制自坐标轴----3.更改图表类型----4.添加标签----5.隐藏次坐标轴（2）目标完成率图 作用：反映业务目标的完成情况 操作：类似双坐标轴操作 注意：把完成值，绘制在次坐标轴上格式设置：1.系列图形的填充色，线条色，系列间隙宽度 2.隐藏次坐标轴 3.给完成值，添加完成率的数据标签（3）雷达图 作用：系列有2组以上数据时，用该图 操作：选中数据--插入--雷达图 格式设置：系列宽度设置在1以下 图例位置：靠下（4）矩阵图 作用：用两组相关数据，对我们的客户进行定性分类 操作： 注意：1.选择数据（不选行/列标题和平均值） 2.给每个点添加标签(行标题) 3.移动x/Y坐标轴，到平均值位置 4.重新绘制x/y坐标（5）迷你图 作用：当数据系列比较多时，快速查看每个系列的趋势或变化情况 操作方法：光标放在放迷你图位置--插入--迷你图--选择类型 设置内容：1.图表样式2.设置高点，低点（6）条件格式的设置 作用：对满足条件的数据，设置相应格式四、实践体会我是一名大一新生，第一次参加夏季短学期实训，这打