实验1 企业数据处理与分析

1、物流管理专业管理信息系统课程实验与课程设计指导- 6 -实验一 企业数据处理与分析一、前言随着社会信息化的发展，如今的企业与个人都面临着前所未有的挑战与压力。激烈的竞争要求企业的财务管理、市场分析、生产管理甚至日常办公管理必须逐渐精细和高效。Excel作为一个简单易用、功能强大的数据处理软件，是目前应用最广泛的数据处理软件之一。但是很多用户应用Excel仅仅限于建立表格和一些简单的计算，对于Excel在财会、审计、营销、统计、金融、工程、管理等各个领域的应用知之甚少。其实，Excel提供了功能齐全的函数计算和分析工具，如能熟练用它来进行数据分析，必将能获得更为精确的信息，并大提高工作效率，从而

2、增强个人以及企业的社会竞争力。二、实验目的1、通过模拟案例，针对企业销售数据进行分析，掌握用Excel进行数据分析的基本方法；2、了解Excel的强大功能，要求同学在今后学习和工作中注意利用。三、实验内容1、销售数据统计与分析随着“五一”黄金周的到来，又将迎来一个家电销售的旺季，各大卖场都使出浑身解数，在风起云涌的家电浪潮中迎来一个接一个的销售高峰。7天过去了，各大商家都开始整理核算各自的销售业绩。本实验以某家电商场在“五一”期间的销售数据为例，用Excel统计与分析该商场的家电销售情况。分析思路如图：图1：销售数据统计与分析设计思路（1）创建并用记录单输入销售清单，主要字段有：日期、类别、商

3、品型号、市场定价、促销方式、成交单价、数量、销售额、销售员；（2）计算销售额和降价幅度；（3）用自动筛选的方法查询某一员工的销售记录；（4）按销售商品类别进行统计分析：按商品类别排序和分类汇总、创建销售额的三维簇状柱形图；（5）按销售员和商品型号统计分析：按销售员和商品型号分类汇总、用饼形图分析销售员销售业绩构成；（6）日销售额走势分析：按日期进行升序排序、用数据点折线图分析日销售总额走势；（7）利用数据透视表统计每种商品型号每位销售员的销售业绩。2、销量统计分析根据某权威统计单位的统计数据，已知北京地区性的奥迪1.8T、别克1.6L、奇瑞QQ这3种车型的市场价格以及在2005年1月至2006

4、年5月之间的销量。从这3种车型的售价情况，可分别代表车市中高档车、经济型和小排量车型。那么油价的上涨和汽车价格的下跌是否对汽车的销量存在影响呢？请根据给出的实验数据进行统计分析。（1）按月按车型绘制销量的数据点折线图；（2）产品销量增长率和影响因素分析：计算增长率、分别创建三个车型的各季度、销量增长率以及油价增长率的两轴线柱图的图表进行销量和影响因素分析；四、实验要求1、根据上述实验要求，使用EXCELL表格建立相应的数据模型。2、独立完成上述内容，研究学习Excel的数据管理和数据分析功能。五、附实验原始数据实验一企业数据处理与分析实验指导一、销售数据统计与分析操作要点1、建立Excel表格

5、，依次输入原始数据，对原始数据表进行初步格式化，如定义字体、对齐、对栏目名称栏目设置底色等，并保存原始数据表。2、在原如数据表中插入两列，输入栏目名称：降价幅度、销售额。计算公式为：降价幅度=（市场定价-成交单价）/市场定价；销售额=成交单价*数量。3、插入一张新表，复制原始数据并命名，如“自动筛选查询表”。如果在数据表中只显示自己需要的数据，而将不需要的数据隐藏起来，就要用到筛选功能。执行“数据”菜单“筛选”下的“自动筛选”功能，单击列标题“销售员”从中选某一销售员查询结果。4、插入一张新表，复制原始数据并命名，如“按商品类别统计分析表”。按类别统计分析一般使用分类汇总功能。其操作过程是：选

6、中全表，执行“数据”菜单“排序”功能，排序的主要关键字选择“类别”。排序后执行“数据”菜单“分类汇总”功能，在分类汇总对话框中分类字段选择“类别”、汇总方式选择“求和”、选定汇总项中选中“数量”和“销售额”。在分类汇总表中创建销售额的三维簇状柱形图的过程是；同时选中“类别”列和“销售额”列，执行“图表向导”功能，在图表类型对话框中选中“三维簇状柱形图”、默认刚选中的数据区域，在标题选项卡中输入图表标如“按类别统计销售额”，单击完成。调整图至合适大小和位置。5、插入一张新表，复制原始数据并命名，如“按销售员和商品型号统计分析表”。其操作过程是：选中全表，执行“数据”菜单“排序”功能，排序的主要关

7、键字选择“销售员”，次要关键字选择“商品型号”排序。执行分类汇总功能分类字段选择“销售员”，汇总方式选择“求和”选定汇总项同时选中“数量”和“销售额”。再执行分类汇总功能分类字段选择“商品型号”，汇总方式选择“求和”选定汇总项同时选中“数量”和“销售额”并取消选择“替换当前分类汇总”选项。用饼形图分析销售员销售业绩构成分析中以林小英为例，在三级汇总结果中选中林小英的的商品型号和销售额两个区域，执行执行“图表向导”功能，在图表类型对话框中选中“饼图”、默认刚选中的数据区域，在标题选项卡中输入图表标如“林小英的销售额结构”，并在数据模块选项卡中“百分比”复选框，单击完成。6、插入一张新表，复制原始

8、数据并命名，如“日销售额走势分析表”。其操作过程是：选中全表，执行“数据”菜单“排序”功能，排序的主要关键字选择“日期”排序。执行分类汇总功能分类字段选择“日期”，汇总方式选择“求和”选定汇总项同进选中“数量”和“销售额”。在2级分类汇总结果中，同时选中日期和销售额两个数据区，执行“图表向导”功能，在图表类型对话框中选中“数据点折线图”、默认刚选中的数据区域，在标题选项卡中输入图表标如“日总销售额趋势分析”，单击完成。调整图至合适大小和位置。7、数据透视表具有交互分析能力，能全面、灵活地对数据进行分析、汇总。只需要改变对应的字段位置，即可得到多种分析结果。利用数据透视表统计每种商品型号每位销售

9、员的销售业绩的操作过程是：打开原始数据表，以商品型号进行排序，选中全表执行“数据”下的“数据透视表和数据透视图”功能，选中数据透视表，默认刚选中的数据区域，数据透视的位置选择新建工作表，单击完成即创建了数据透视表的雏形，将“商品型号”字段拖至行标题处，将“销售额”拖至数据项处，将“销售员”拖至列标题处即可。当然也可对数据透视进行美化。二、销量统计分析操作要点1、建立Excel表格，依次输入原始数据，对原始数据表进行初步格式化，如定义字体、对齐、对栏目名称栏目设置底色等，并保存原始数据表，表名如“数据”。2、按月按车型绘制销量的数据点折线图进行销量分析的操作过程是：新插入一个工作表，将工作表改名

10、为“销量分析”，然后从“数据”工作表中复制“月份”和各车型的销量数据到当前表中。选中当前数据表中的数据区域，执行“图表向导”功能，在图表类型对话框中选中“数据点折线图”、默认刚选中的数据区域，在标题选项卡中输入图表标如“销售趋势图”，单击完成。调整图至合适大小和位置。3、产品销量增长率和影响因素分析操作过程：新插入工作表并命名为“增长率分析”，创建如图所的奥迪车的增长率与汽油平均增长率表格，统计各季度销量和汽油平均价格可以在原始数据表中按季度求和、求平均数然后复制而来。2005年一季度增长率为0，2005年二季度增长的计算方法是：（2005年二季度的销量-2005年一季度的销量）/2005年一季度的销量，其它季度的计算方法以此类推。油价平均增长率的计算方法也是如此。奥迪的增长率与汽油平均增长率季度2005年一季度2005年二季度2005年三季度2005年四季度2006年一季度2006年二季度奥迪（销量）汽油平均价格增长率油价平均增长率 创建增长率与销量图表：同时选中上表中“季度到2006年二季度销量值”区域和“增长率到2006年二季度油价平均增长率值”区域；执行“插入-图表”功能，单击“自定义类型”选项卡选择“两轴线一柱图”然后输入图表标题，如“奥迪增长率与油价增长率比较”。下面的操作步骤将设