专题07：Excel数据分析及绘图.doc

西南财经大学文化素质学校第二课堂办公自动化软件应用教学讲义刘科专题七：Excel数据分析及绘图目录1.排序1【案例】：按姓氏笔划排序1【案例】：按职称排序22.筛选（单项和组合）3【案例】：设置组合条件筛选数据33.冻结窗口4【案例】：固定行（列）方便查询大型表格数据44.基础统计4【案例】：统计成绩数据表45.分类汇总6【案例】：制作每名同学的成绩通知单66.数据透视表7【案例】：成绩数据库统计及排名87.绘图向导9【案例】：营销竞赛图9【案例】：用雷达图辅助分析公司经营情况11Excel并非一个表格编辑排版的软件，就编辑排版而言，Word也可以胜任了。之所以选择Excel来进行表格数据的编辑排版，主要是它能够方便快捷地进行数据分析。用表格来展示数据，一方面是为了美观，另一方面也是为了便于统计和分析。1. 排序排序是数据分析的一项最基本操作，很多情况下，数据表都是需要进行排序了。在Excel中，用户先选定要进行排序的工作表（或者数据区域），然后在“数据”菜单中选择“排序”命令，在弹出的选项框中可以按照三个层次对数据表进行排序，分为“主要”、“次要”和“第三”关键字。通常排序方向是按行排序，但也可以在“选项”中设置成按列排序，方式可以选择“拼音”和“笔划”，甚至还可以设置成按“自定义序列”的顺序。【案例】：按姓氏笔划排序通常，Excel对数据是按拼音或字母进行排序的，但在许多情况下，我们会遇到的需要对姓名进行按笔划排序的要求。如图1.1，在这个数据表中，要求按姓氏笔划进行排序。首先选定这个工作表（或者仅选择要排序的数据表区域A1到F17），然后在“数据”菜单中选择“排序”命令，在弹出的选项框中设置“主要关键字”为“姓名”。再选择“选项”，在弹出的选项框中设置排序“方法”为“笔划排序”，确定即可。图1.1【案例扩展】数据表一般都要在附注是注明排序方式，或者以编号，或者以姓氏笔划，这作为一项基本常识是读者应该知道的。【案例】：按职称排序在有些场合，即不按照姓名的拼音，也不按照笔划，而要按照职务或者职称排序。参见图1.1，在上个数据表中，要求按照“教授”、“副教授”、“讲师”、“助教”的顺序对姓名进行排序。就“职称”这列数据，无论是按“拼音”，还是按“笔划”，都不能达到上述效果。这属于一种新的序列，需要进行自定义序列设置。如图1.2，在“工具”菜单中选择“选项”命令，在弹出的选项框中选择“自定义序列”栏目，在左侧窗口选择“新序列”，然后在右侧窗口旁选择“添加”，在右侧窗口中输入该序列的内容，各元素用英文状态下的逗号分隔开（也可以在Excel中以表格形式列好，通过“导入”来添加自定义序列）。图1.2如图1.3，选定这个工作表（或数据区域），在“数据”菜单中选择“排序”命令，在弹出的选项框中设置“主要关键字”为“职称”。然后选择“选项”，在弹出的选项框中选择“自定义排序次序”为刚才设置的自定义序列，确定即可。图1.3【案例扩展】教授是学术上最高的职称了，至于博导只是代表一种辅导能力，并非一项职称。与此类似，博士是学术上最高的学位，博士后并非一种学位，只是说明一种研究经历。2. 筛选（单项和组合）在对数据表中众多数据数据的分析中，经常会遇到筛选有用数据的情况。Excel提供自动筛选的功能，还可以组合多项条件同时进行筛选。【案例】：设置组合条件筛选数据在一个学生成绩数据库中，要求筛选所有“必修课”和“公共课”的学生成绩。如图2.1，选中整个工作表（或者数据表区域），在“数据”菜单中选择“筛选”，在右侧弹出菜单中选择“自动筛选”命令，此时数据表的标题行会变成带有下拉箭头的选项框。在“课程类型”一列的下拉菜单中选择“自定义”。然后在弹出的对话框中，设置课程类型“等于”“必修课”，“或”“公共课”，确定即可。图2.1【案例扩展】如果筛选条件涉及不同的列，则只需要依次在各列的下拉菜单中选择对应的选项即可。3. 冻结窗口在大型数据表中浏览数据，当然要用到“垂直滚动条”或者“水平滚动条”进行定位，但如果移动这些滚动条，往往就不能立即得到该列或者该行的标题。此时，可以利用窗口冻结功能来固定列标题或者行标题，以方便查询者迅速得到所查数据所在列或者行。【案例】：固定行（列）方便查询大型表格数据参见图2.1，在这个大型数据表中，如果向下拖动“垂直滚动条”，则第一行“学号”等列标题则会上移出可视区域，此时如果要迅速得知所查单元格对应列标题，可以将第1行冻结窗口。如图3.1，将光标置于要固定行的下行，在“窗口”菜单中选择“冻结窗口”，确定即可。此时无论如何拖动“垂直滚动条”，第1行都会固定不变，以方便用户得知所在列的名称。图3.1【案例扩展】如果要列固定不变，则需要将光标置于固定列的右行，再在“窗口”菜单中选择“冻结窗口”即可。4. 基础统计数据之所以用数据表的形式保存，一方面是为了美观，另一方面也是为了方便统计分析。Excel提供了求和、计数、【案例】：统计成绩数据表有一个二维的学生成绩数据表，需要统计每名同学的平均分，每科的平均分、最高分和最低分，并计算学生总人数。如图4.1，选择B1到I17，空出I列，在“常用工具栏”中选择“自动求和”快捷按钮下拉菜单中的“平均值”命令，则在I2到I17单元格中计算每名同学的平均分。图4.1如图4.2，选择B2到H18单元格，空出第18行，再从“自动求和”按钮中选择“最大值”，则计算出然后将鼠标置于B19单元格中，选择“自动求和”按钮中的“平均值”，系统会自动用虚线框选定B2到B18单元格，需要重新用鼠标选定B2到B17单元格，确定即可，再用鼠标将该单元格公式拖曳复制到H19单元格，则计算出每科课程的平均分。再如法炮制计算出每科课程的最低分。图4.2如图4.3，将鼠标置于B21单元格中，选择“自动求和”按钮中的“计数”，手动将虚线框调整为B2到B17，则计算出该成绩数据表共包含16名学生的成绩数据。图4.3【案例扩展】B21单元格计数用的函数是Count()，它计算的是选定区域内数字单元格的数目，此外还有一个统计函数是Counta()，它计算的是选定区域内非空单元格的数目。如果统计区域内不是数字，而是文字的话，则需要用到后一个函数。5. 分类汇总在对数据表中数据进行分析时，经常需要对具有某一共同属性的数据进行汇总，这就称为分类汇总。如百货公司对销售数据记录的分析时，分家电类、日化类、服装类、食品类，计算各类销售额。如销售部对各营销员数据记录分析时，按甲、乙、丙、丁等人分类，计算各人的业绩，再进行奖惩。【案例】：制作每名同学的成绩通知单参见图2.1，在这样的数据库中，要分别对每名同学制作一张成绩通知单，实际上是按“姓名”分类，再将各类的成绩汇总（也可以是求平均值、计数等运算）。如图5.1，选中这个数据表（或者数据区域），首先按“姓名”为主要关键字进行排序。然后在“数据”菜单中选择“分类汇总”，在弹出的选项框中，设置“分类字段”为“姓名”（即排序的主要关键字），“汇总方式”为“平均值”（求和也可以，即是计算总分），“选定汇总项”为“成绩”（即是对“成绩”一列进行计算）。确定即可得如图5.2的效果。图5.1图5.2【案例扩展】如果在“分类汇总”选项框下方选上“每组数据分页”复选框前面的勾，则每页只显示一名同学的成绩；如果在页眉中设置“成绩通知单”字样，甚至在页脚设置“开学报到注册须知”，则每页正是一名同学的成绩单。这样的成绩通知单，不能做到每列都有标题标示。6. 数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，就可以使用数据透视表。数据透视表是交互式报表，可快速合并和比较大量数据。用户可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。【案例】：成绩数据库统计及排名参见图2.1，这是一个数据库，而非二维的数据表。我们往往需要如图4.2这样类型的二维数据表，以便于统计各项指标和排名。如图6.1，在“数据”菜单中选择“数据透视表和数据透视图”，在弹出的对话框中选择“下一步”，系统会自动选定全部数据区域的。图6.1如图6.2，在系统自动新建的工作表中，将“姓名”字段拖动到“行字段”处，将“课程名称”字段拖动到“列字段”处，将“成绩”字段拖动到“数据项”处。如图6.3，统一调整列宽（全选，任意调整一列宽度，则所有列同时调整）。图6.2图6.3如图6.5，在I列“总计”项计算的是每名同学的总分，可以更改为平均分。在“数据透视表工具栏”上选择“字段设置”按钮，在弹出的选项框中将“汇总方式”改为“平均值”即可。图6.4最后将该表复制，选择性粘贴在新工作表中，设置好格式，再按成绩排序则列出一二三名来。【案例扩展】有时“数据项”中全是“1”，这是因为在“汇总方式”中选择了“计数”，将其更改为“求和”或者“平均值”，则能在“数据项”中显示各科成绩数值。有时“数据项”中表示是非法字符，这是因为图2.1这个数据库中，“成绩”一列单元格格式为“文本”，需要将其改为“数字”，但仅在单元格格式中改还不行，需要在“成绩”列前新插入一列，再选定“成绩”列，复制，选择性粘贴到新列中，如图6.5，在弹出的选项框中“运算”中选择“加”，即是粘贴后统一加上零，这样就将文本列转换为数字列了。图6.57. 绘图向导图表具有较好的视觉效果，可方便用户查看数据的差异、图案和预测趋势。例如，用户不必分析工作表中的多个数据列就可以立即看到各期间销售额的升降，或很方便地对实际销售额与销售计划进行比较。Excel提供了丰富的绘图选项，以方便用户利用图形分析数据，如柱开图、条形图、折线图、饼图、散点图、面积图、圆柱图、圆柱图等很多种。【案例】：营销竞赛图如图7.1，某销售部针对六名营销人员对年度业绩的完成情况定期考评，制作竞赛图以激励营销人员。首先选中数据区域，然后在“插入”菜单中选择“图表”（也可以直接在“常用工具栏”上点击“图表向导”快捷按钮）。在弹出的选项框中选择“图表类型”，如“柱形图”，再选择“子图表类型”，如选择不明确，可点击“按下不放可查看示例”，从中能大致知道图表的形式。点击“下一步”。图7.1如图7.2，在“标题”栏目中输入适当文本。在其他栏目中还可以进行更为深入的设置。图7.2如图7.3，图表生成后，如果希望取消其背景图案，可以用鼠标双击背景处，在弹出的“绘图区格式”选项框中将“区域”设置为“无”即可。图7.3如图7.4，目标业绩仅100万元，但图表中却显示出120万元，这是刻度设置的问题。用鼠标双击刻度处，在弹出的“坐标轴格式”选项框中将“最大值”设为100即可。图7.4图形的每处格式都有对应的设置方式，用鼠标双击各处即可进入设置选项框。【案例扩展】Excel中绘图的源数据表与图形是对应链接了，如果数据表中数据发生变化，图形上也对应变化，而不需要重新绘图。各单元格的数据对公式也是一样，一变俱变。但在Word的表格中，如果运用了表格中的公式，表格数据变化，公式单元格并不会随之变化，需要“更新域”才行。【案例】：用雷达图辅助分析公司经营情况雷达图主要用于显示数值相对于中心点的变化情况，它描绘现有状况与目标之间差距的大小程度。对企业财务分析时，根据资产负责表和相关信息，可以反映出企业的几种能力，如盈利能力、偿债能力、现金管理能力、资产负责管理能力、成长能力，或者也有提法是盈利性、安全性、生产性、发展性等。每种能力有若干指标衡量，如净资产收益率，每股收益，资产负债比，总资产周围率，主营业务收入增长率等。如图7.5，在证券之星网站（）上直接找到G招商的财务指