excel数据分析应用指南(培训讲座课件

1、Excel XP 数据分析应用指南Excel 之所以强大，是因为其具有完整的数据分析函数和图表功能，让我们对数据进行归纳 后，能够利用这些函数和图表对数据进行分析。 本文以教育、金融和财务应用为例， 介绍 Excel XP数据分析的主要手段和操作方法。一、数据的排序与筛选排序与筛选是数据分析的基本手段。下面以 图 1 所示的“学籍管理表”为例，介绍数据排序 与筛选的操作方法。1. 数据排序（1）数据清单：Excel 排序和筛选的操作对象是“数据清单”。它是在工作表首行的单元格内输入列标记 （ 相 当于数据库中的字段，可以作为关键字参与各种操作 ） ，在列标记以下各行依次输入数据 （ 相 当于数

2、据库是的一条条记录 ）而构成的表。其基本结构如图 1 所示，与 Access 数据表基本相 同。（2）常规排序：Excel 数据清单中的列标记可以作为关键字参与排序，具体操作方法有以下两种：如果你需 要根据“年龄”、“总分”等数值关键字 （ 字段）对数据清单排序，只须将图 1 中的“年龄” 或“总分”选中，然后点击工具栏中的“升序排序”或“降序排序”按钮，整个数据清单就 会根据该关键字重新排列。如果你需要按姓氏笔划对图1数据清单排序，则应选中图1中B1单元格，点击“数据-排序” 菜单命令，在打开的对话框中点击“选项”按钮打开“排序选项”对话框。 选中“笔画排序” 选项，根据图 1 中的数据排列

3、方向选择“按列排序”选项，点击“确定”按钮后返回到“排 序”对话框。将其中的“有标题行”选中，然后打开“主要关键字”下拉列表，选择其中的“姓名”选面，最后选中排序方式 （“升序”或“降序” ）并确定，数据清单就会根据姓名的 笔划重新排列。如果数据清单排序依据多个关键字，你只要打开“排序”对话框，在“次要关键字”和“第 三关键字”下拉列表中选择即可。（3）自定义排序：如果你要求图 1 中的数据按照“广东省”、“云南省”、“四川省”、“山西省”和“河北 省”的顺序排列， 前面介绍的两种方法就无能为力了， 只能采取自定义排序规则的方法处理。首先你要建立一个自定义序列： 点击“工具-选项”菜单命令，

4、打开“选项”对话框中的“自 定义序列”选项卡。选中左边“自定义序列”下的“新序列”选项，此时光标就会在右边的“输入序列”框内闪动，你可以输入“广东省”、“云南省”等自定义序列，输入的每个序 列之间要用英文逗号分隔，或者每输入一个序列就敲一次回车。完成后点击“添加”按钮， 将自定义序列添加到“自定义序列”框内备用。自定义序列排序的方法与笔划排序很相似，你只要打开“排序选项”对话框中的“自定义排 序次序”下拉列表，选中前面定义好的序列，其他选项保持不变。回到“排序”对话框后根 据需要选择“升序”或“降序”，“确定”后即可完成数据清单的排序。注意：假如数据按自定义序列的顺序排列，就应选中“排序”对话

5、框中的“升序”排列，否 则应该选择“降序”排列。2. 数据检索从数据结构的角度看，图 1 所示“数据清单”就是一个小型数据库，其中的每一条记录占用 一个表格行。对此，可以采用下面的方法检索数据：点击“数据一记录单”菜单命令，在打开的对话框中点击“条件”按钮。对话框中的字段就 会变成空白等待输入，而且“条件”按钮转变为“表单”按钮。你可以在对话框的相应字段 中输入条件，例如在“姓名”字段框内输入“李小丫”回车，则姓名是“李小丫”的数据就 会显示在对话框中。当然，你输入的检索条件可以使用 、=、=、等逻辑符号。例如 点击“条件”按钮后在“总分”框内输入“ 500”，表示检索“总分”大于 500 分

6、的所有记 录，回车后“新建”按钮上方就会显示“ 1/6”字样，说明 6 条记录中的第一条符合条件。点 击“上一条”或“下一条”按钮，可以查看检索出来的其他记录，并显示“ 4/6”、“ 5/6” 字样。3. 自动筛选上面介绍的方法可以逐条查看检索出来的记录。如果你要批量查看符合条件的所有记录，仅 靠“记录单”进行检索就不能满足你的需要。此时可以使用 Excel 的自动筛选功能，具体操 作方法是：点击“数据一筛选一自动筛选”菜单命令，数据清单的列标记（字段）右侧会显示一个下拉按钮。如果你需要将总分大于 500 分的记录全部筛选出来，可以点击“总分”下拉 按钮，选择“自定义”选项打开“自定义自动筛选

7、方式”对话框。点击“总分”下面第一行 右侧的下拉按钮，选择“大于”选项，然后在其右边的框内输入“500”，选中两行中间的“与”后确定，工作表就会显示所有符合筛选条件的记录。当然，筛选出来的记录还可以再次筛选。假如你要将总分大于500 分中“性别”为“女”的记录筛选出来。可以按相同方法打开“性别”下拉列表选择“女”，则“张晓菲”的记录就 会显示在工作表中。自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛选 结果，点击“数据T筛选一自动筛选”菜单命令即可。 另外，执行数据自动筛选时，如果“自 定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应

8、当选 中“或”选项。4. 高级筛选虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。为此， Excel 提供了“高级筛 选”功能。它能够使用各种条件对数据清单进行筛选，其功能强大和使用灵活远非“自动筛 选”可比。(1) 条件区域： 使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。条件标记和数据清单 的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方 构造，是执行高级筛选的关键部分。构造高级筛选的条件区域需要注意：如果条件区域放在数据清单的下方，那么两者之间应至 少有一个空白行。如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余 一个

9、或几个空白行 (一般不要这样设计，这样影响其他功能使用数据清单 ) 。(2) 单列多条件： 如果某一个条件标记下面输入了两个或多个筛选条件，我们将其称为单列多条件，你只要在 条件标记下自上而下依次输入筛选条件即可。 例如你需要列出图 1 中年龄大于 17 和年龄小于 17的学生名单，只需在图1条件标记“年龄”的下方D10和D11单元格输入“ 17和“ =85即可。(4) 多行单条件： 构造高级筛选条件有这样的要求：如果多个筛选条件需要同时满足，它们必须分布于条件区 域的同一行，这就是所谓的“与条件，否则筛选条件必须分布于条件区域的不同行，也就 是“或条件。 假如你要在图 1 中找出“性别是“男

10、， 或“语文成绩“ =80，或“化 学”成绩“ =90的所有记录。可以在图1条件区域的C10单元格内输入“男”，然后在G11 单元格内输入“ =80，最后在 J12 单元格内输入“ =90即可。(5) 两列两组条件：如果你需要在图 1 中寻找物理成绩大于等于 90的男生或者物理成绩大于等于 80的女生，可 以按照如下方法构造条件区域。C10 C11单元格内分别输入“男”和“女”，接着在 H10 H11单元格内分别输入“ =90和“ =80即可。(6) 执行筛选操作： 数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：点击“数 据f筛选f高级筛选”菜单命令，在打开的对话框中

11、首先选择筛选结果的显示方式，若选择“在原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复 制到”框内指定筛选结果的显示位置。 接着点击“数据区域”框， 选中数据清单所在的区域， 使该区域的绝对引用进入框内； 再用相同方法完成对“条件区域”的绝对引用。 点击“确定” 按钮，就会在你选择的位置显示筛选结果。如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数据清单。此时点击“数据一筛选一全部显示”菜单命令，即可恢复原来的数据清单。二、数据的分析与求解Excel 提供了多种数据分析手段，从函数、分析工具库、加载宏等，一直到数据透视表和数 据透视图。

12、下面介绍以函数和加载宏为主的若干分析工具。1. 成绩分布频率分析学生成绩分析的一项重要任务， 就是统计各分数段中的人数， 为研究成绩分布提供基础数据。下面以图1为例，说明如何计算 550500、500450、450400、400350,以及小于350 分数段内的人数。首先在图1中的M2 M6区域依次输入550、500、450、400、350,表示统计上述分数段内的 成绩个数。当然，你可以根据数据分析的具体要求，选择其他方式划分数据分段方法。接着 在M2:M6区域右侧预留相同大小的单元格区域（N2: N6），作为存放数据统计结果的位置。上述工作完成后，选中存放统计结果的区域 （N2: N6），在

13、编辑栏内输入公式“=FREQUENCY（L2：L7M2:M6）,最后让光标停留在公式的末尾。按下 Shift+Ctrl 键敲回车， 编辑栏内将显示“ =FREQUENCY（L2:L，6M2:M6）” （大括号表示这是一个数组公式 ） ， N2: N6 区域就会显示各分数段中的成绩个数。注意:数组公式“ =FREQUENCY（B2:B， 6C2:C6） ”可以对一组或多组数值进行多重计算，并 得出一个或多个计算结果。数组公式中的大括号“ ”不能用手工输入，只能按组合键 Shift+Ctrl+Enter 自动生成。2. 对象相关性分析教育教学研究的任务之一是了解各学科之间的相互关系，例如数学的学习

14、是否对物理成绩有 影响。而商品销售领域，也需要分析两组数据 （ 单位可以不同 ） 之间是否相关。例如空调和冷 饮的销售量与气温之间的关系，或者商场的客流人数与销售额是否相关等等。这里仍然以图 1 所示的“学籍管理表”为例，我们的任务是分析这些学生的物理成绩是否与 数学成绩相关。分析的操作方法是:选中数据清单中的一个空白单元格，在编辑栏内输入公 式“ =CORREL（F2:F,7 12:17） ”，回车即可得到数学和物理成绩的相关系数。这个计算结果有 以下三种情况:如果物理成绩中的高 （低） 分与数学成绩中的高 （ 低） 分对应，说明这两个数据 集合是正相关。计算结果应该是一个小于 1 的实数，

15、它越接近于 1，说明两者相关性越强； 如果物理成绩中的低 （ 高） 分与数学成绩中的高 （ 低） 分对应，说明这两个数据集合是负相关。计算结果应该是接近 -1 的实数，它越接近于 -1 ，说明两者负相关越强；如果上述计算结果接 近零，说明这两个集合中的数据互不相关。另外，为了保证分析结果的准确性。用于分析的 两个数据集合中的数据个数不能太少，一般应在 30 个以上。如果你将 图 1 中的数学成绩换成一段时间内的商场客流量，同时将物理成绩换成同一时间内 的商场销售额。运用相同的方法进行计算，就可以得知客流量与销售额是否相关，从而为制 定营业策略提供依据。3. 方差分析在质量检验等领域，经常需要检

16、验两个对象的平均值是否存在差异。例如两个班级的数学课 分别采用了不同的教学方法， 我们需要通过期中考试成绩检验两种方法是否存在实质性差异， 以便对教学改革的成果做出判断。再如，一种手机经过改进，我们要了解两种手机的通话距 离是否存在实质性差异。就可以随机抽取数目相同的两种手机，在不同条件下测试它们的通 话距离，从而了解手机的改进是否有效。在上述两个例子中，影响实验结果的因素只有一个。我们将其称为单因素实验，对应的方差 分析就是单因素方差分析。下面我们以不同教法的两个班级的数学课为例，说明单因素方差 分析的操作方法： 假设这两个班级的期中考试数学成绩分别存放在 A2:A46 和 B2:B46 区

17、域，请你将光标放在该 区域以外的任意一个单元格。点击“工具数据分析”菜单命令，在打开的对话框中选中“方 差分析：单因素方差分析”， “确定”之后打开同名对话框 （ 如无此选项， 请检查你的 Office XP安装方式）。其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行” 排列的相邻数据区域组成， 我们这个实例应当输入“ S|AS|2:S|BS|46 ” （可以用鼠标选中区域 的方法输入 ）；“分组方式”用来确定输入区域中的数据如何排列。 由于两个班级的成绩分别 存放在A、B列，所以应当选中“分组方式”中的“列”选项；又由于A1、B1单元格带有班级代码，故应将“标志

18、位于第一行”选中；它下面的“0.05”称为显著性水平，一般取默认值 0.05 即可；“输出区域”必须输入一个空白单元格引用， 用来确定计算结果存放区域左上 角的位置；如果你选中了“新工作表组”，就需要在右侧的框中输入该工作表的名称；若选 中了“新工作簿”，则可以创建一个新的工作簿，并在其中粘贴计算结果。以上设置完成后点击“确定”按钮，就可以在选定区域内输出分析结果。其中的“组间”就 是影响成绩的因素 （不同教学方法 ）， “组内”就是误差， “总计”就是总和， “差异源”则 是方差来源，“ SS就是平方和，“ df”称为自由度（上例为1），“MS就是均方，“ F”称 为F比，“P-value

19、”则是原假设（结论）成立的概率（这个数值越接近0,说明原假设（实验班 和对照班的数学成绩没有显著差异 ）成立的可能性越小，反之原假设成立的可能性越大 ）， “ Fcrit ”为拒绝域的临界值。假如上面两个班级的计算结果是“P -value ”等于0.1，因此在显著性水平0.05的条件下原 假设不成立，实验班和对照班的数学成绩有显著的差异，说明教学方法对成绩有显著影响。4. 实现利润分析上面介绍的内容属于统计分析的范畴，目的是大量数据中寻找统计规律。而在企业管理等领 域，管理人员则要了解不同因素或方案对经营目标的影响。例如确定四季度的销售利润总额 以后，如何确定其他开支的数额，才能保证完成销售利

20、润目标等。下面介绍一个分析实例：假设某企业四季度的销售利润指标定为 1650 万元，如果其他条件保持不变， 销售收入需要增 加多少。由于销售利润与销售收入不是简单的线性关系 （例如销售收入增加 2 万元，销售利润 同比增加 1.5 万元） ，而要受到多种因素的制约。 例如增加销售收入就要加大销售成本和费用， 还要缴纳更多的营业税。使用手工解决这类问题非常麻烦，需要根据销售收入与成本的关系 逐步计算。如果利用 Excel 的单变量求解命令，就可以快速计算出结果，甚至可以针对不同情况反复计 算。下面介绍这个问题的求解方法：设 Excel工作表A2、B2、C2、D2和E2单元格分别存放 “销售收入”

21、、“销售成本”、“销售费用”、“营业税”和“销售利润”的数值。根据经 验，“销售成本”约占“销售收入”的 8%，“销售费用”约占“销售收入”的 25%，“营业 税”约占“销售收入”的 10%。据此可以建立“销售收入”与其他因素的数学关系，这是执 行单变量求解的关键。根据上面的分析，可以选中E2单元格，在编辑栏输入公式“ =A2（A2*0.08+A2*0.25+A2*0.1） ”。点击“工具单变量求解”菜单命令，在打开的对话框中可见E2自动进入“目标单元格”。接着在对话框的“目标值”内输入“ 1650”，在“可 变单元格”框内输入“ S|AS|2” （也可以将光标放入框中，然后点击A2单元格）。

22、上述操作完 成后点击“确定”按钮，就会弹出“单变量求解状态”对话框，说明已经求得一个解，而且 目标值和“当前解”相同。与此同时，你可以在 A2单元格中看到求出的“销售收入”，上面 这个例子的计算结果是 2894.737 万元，即要想完成 1650万元的销售利润指标，销售收入必 须达到 2894.737 万元。5. 成绩评价分析上面的实例讲解的是目标设定以后，实现这个目标必须满足的条件。但是实际中往往存在这 样的问题，就是实现目标的某个或多个条件发生了变化，它会对结果产生哪些影响。下面以图 1 所示的“学籍管理表为例”，说明某个学生单科成绩发生的变化，会对全体学生 的总平均分有什么影响。假如我们

23、要分析学生“赵明君”的“数学”成绩提高到了 70、 80或90 分，全班学生的总平 均分的变化情况如何。由于这种分析只涉及一种数据 （“赵明君”的“数学”成绩 ），所以称 之为单变量数据表。分析的具体操作过程是：在F10、F11、F12单元格中，依次输入70、80和90。然后在第一个数据（70）的上一行，而 且位于该数据列右边的单元格（即G9）中输入公式“ =SUM（F5:K5”，敲回车计算出结果。然 后在这个公式右边的单元格（即H9）中输入总平均分计算公式“ =AVERAGE（L2:L7），再次敲 回车计算出结果。接着选中含有待分析数据 (70 、80、90)和个人总分及总平均分计算公式在内

24、的单元格区域 (F9:H12),点击“数据一模拟运算表”菜单命令打开相应对话框。因为我们要引用的数据放在列方向，所以必须将光标放入“输入引用列的单元格”，点击“赵明君”的“数学”成绩 所在的单元格(即F5)，让它的绝对引用“ S|FS|5”进入“输入引用列的单元格”，确定即可 看到如 图 2 所示的计算结果。如果模拟分析数据(70、80、90)是沿着行存放(例如F9、G9和H9)，就应该在第一个数据所 在单元格 (F9) 左边一列，而且位于数据行下方的单元格 (E10) 中输入公式“ =SUM(F5:K5)”， 然后在其下方输入总平均分计算公式“ =AVERAGE(L2:L7”) 。最后选中

25、E9:H11 区域，按上面 介绍的方法打开“模拟运算表”对话框，点击“赵明君”的“数学”成绩所在的单元格 ( 即 F5)，让它的绝对引用“ S|FS|5”进入“输入引用行的单元格”，回车即可计算出如图3所示 的计算结果。6. 贷款成本分析上面介绍的单变量模拟运算表只能分析其他因素不变时，一个参数的变化对目标值的影响。 如果要分析两个参数的变化对目标值的影响，例如贷款利率和偿还期限同时变化时，每月偿 还金额发生的变化，就必须使用双变量模拟运算表。假设某企业准备贷款 6000 万元，贷款期限预计为 10 年，已知该笔贷款的现行月利率为 5%。 企业领导考虑到这笔贷款的期限较长，必须分析利率变动和还

26、款时间变化的影响。为此，双 变量模拟运算表分析以上两个因素对偿还金额的影响。下面介绍这类问题的解决方法：首先打开一个空白工作表，在有关单元格中输入说明数据意义的文字图4,然后在B3 B4和B5单元格中依次输入“现行年利率”、“贷款年限”和“贷款金额(万元)”的值。接着选中B2单元格，在其中输入公式“ =PMT(B3/12,B4*12,-B5)”。公式中的第一个参数是利率，因 为还贷额是按月计算的，所以要将年利率除以 12 变为月利率；第二个参数是还款年限，由于 按月还贷的缘故，必须将B4中的还贷年限乘以12；第三个参数为贷款金额，如果不在 B5前 面加负号，计算出来的月还款金额就是负数。为了照

27、顾人们的阅读习惯，事先在贷款金额前 加上负号，即可使计算出来的还贷金额便为正数。此时依据上述公式计算出来的结果是 “63.64”，即年利率为 5%、期限 10 年的条件下，每月偿还贷款的金额是 63.64 万元。另外，PMT函数还有Fv和Type两个参数。Fv是贷款全部归还完毕后剩余的金额，省略时该 值为零，即一笔贷款归还完毕后其账面金额为零。Type的值是0或1，用来指定贷款的还款时间是在月初还是月末， 0 或省略表示还款时间是月初。为了给模拟运算表提供分析依据，要紧接着公式“ =PMT(B3/12,B4*12,-B5)”的右侧，即D2、E2、F2和G2单元格中分别输入“可能发生的还款年限”

28、(8、9、11、12)。最后在公式下方的C3 C4和C5中依次输入“可能贷款利率”(4% 6% 7%)。完成后将公式所在的单元格、“可能发生的还款年限”和“可能贷款利率”两种数据所在的 区域(C2:G5)选中。点击“数据模拟运算表”菜单命令，在打开的对话框中，在“输入引用行的单元格”框中，输入由行数值 (就是“可能发生的还款年限” ) 替换的输入单元格 (B4) 的绝对引用 (S|BS|4) 。然后在“输入引用列的单元格”框中， 输入由列数值 ( 就是“可能贷款 利率” ) 替换的输入单元格 (B3) 的绝对引用 (S|BS|3) 。上述内容输入结束以后，点击“确定”按钮， D3:G5区域就会

29、显示分析结果。从中可以看出不同还款年限和利率所对应的月还款金额，从而为贷款成本评估提供依据。7. 最小还贷分析模拟运算表可以分析某个或某两个因素改变时，由它们决定的结果会发生怎样的变化。但在企业管理、金融证券等领域，还存在着另外一类问题。就是在财力、物力和劳动力等资源受到限制的情况下，如何使经营利润最大或生产成本最小。这就是所谓的规划问题，寻求答案 的过程就是“规划求解”。下面介绍它的加载及使用方面的有关问题：(1) 加载“规划求解”：与上面介绍的其他分析工具不同，“规划求解”是以“加载宏”形式提供的工具。在默认情 况下，它并不随着 Excel 的启动而运行，所以在“工具”菜单中看不到“规划求

30、解”命令。 如果你要加载“规划求解”，可以点击“工具一加载宏” 菜单命令，在打开的对话框中选中 “加载宏”命令，确定之后即可在“工具”菜单下看到“规划求解”命令。(2) 规划求解方法：假设某公司需要从不同银行贷三笔款项，金额分别为5000万元、 6000 万元和 3000万元。假设贷款年利率的计算办法是贷款年限加一，然后乘以6%。，原定三笔贷款的还款期限分别是 8年、9 年和 10 年，现在需要计算各笔贷款的还款期限分别是几年，才能确保到期时的还贷总 金额最少。首先建立有关的数据清单，在 A2、A3和A4单元格分别输入三笔贷款的金额，接着在 C2、C3 和C4单元格输入原定的贷款年限。然后在

31、B2单元格建立利率计算公式“ =(C2+1)*0.006 ”， 并将它复制到B3和B4单元格，分别计算出三笔贷款的利率。继续在D2单元格建立公式“=PMT(B2 C2, -A2)” (相关参数的意义见上文)，并将它复制到D3和D4单元格，分别计算 出三笔贷款的年还款金额。最后在 D5单元格内输入公式“ =SUM(D2:D4),计算出到贷款期 时的还款总金额。因为我们的目的是寻找D5单元格满足什么条件才能使还贷总金额最少， 所以是一个求解“最 小值”的问题，并且应该将 D5将其作为目标单元格。点击“工具一规划求解” 菜单命令， 打开“规划求解参数”对话框 (如图5 所示)。此时D5单元格的绝对引

32、用S|DS|5会自动出现在“目标单元格”框中，接下来就应该将“最 小值”选中。由于贷款期限是决定还款总金额的因素，选择合适的贷款期限是我们的目标。所以要用鼠标点击“可变单元格”框，将光标拖过C2:C4区域，其绝对引用S|CS|2:S|CS|4自动进入其中。 由于贷款年限都是整数， 因此要对“可变单元格”进行“约束”。 请点击“添 加”按钮打开“添加约束”对话框，在“单元格引用位置”中指定 C2:C4区域的地址S|CS|2 : S|CS|4 ;再打开对话框中间的下拉列表，选择“ INT”关系符，使“约束值”框内显示“整 数”。点击“求解”按钮， Excel 开始进行计算，最后出现“规划求解结果”

33、对话框。在规划求解找到结果的情况下，一般应出现在“报告”下的“运算结果报告”。确定之后， 即可在原来的工作表旁边建立一个“运算结果报告”。从计算结果中可以看出，最佳的还款是 14 年。原来的计划需要归还贷款 2087.57 万元，而现在的计划只需 1798.06 万元，后一方 案可以节约 289.50 万元。当然，不是每一个规划求解问题都可以获得答案。如果问题的数学关系建立错误，约束条件选取不当等等。均可能导致目标单元格数值不收敛，或者在目标或约束条件单元格中发现错 误。这都需要我们仔细分析问题的数学关系，重新建立模型和设置约束条件。三、分析图表Excel 提供的信息不容易理解。如果你要更直观

34、地观察数据反映的信息，必须借助图表这种 数据分析和表现手段。1. 直接制作图表如果你要利用图 1 的数据制作一个各科成绩随序号变化的簇状柱形图，只要选中相关的数据 区域（图1的F2: K7），然后按F11键即可在当前工作簿插入一个图表。2. 图表向导点击“插入一图表”菜单命令，或者点击工具栏中的“图表向导”按钮，就可以打开“图表 向导”，在它的指引下一步步建立图表。下面以建立 60090105dM的分析结果图表为例，说 明图表向导的操作方法:（1）选择图表类型:“图表向导”第一步是选择你需要的图表类型，它的“标准类型”以及“自定义类型”选项 卡总共提供了 14（不含子图表）和 20种图表。各种

35、图表都有自己的最佳适用范围，例如柱形 图显示一段时间内的数据变化或比较结果，用来反映数据随时间的变化很合适。条形图可以 对数据进行比较，用来反映数据间的相对大小比较好。如果你不知道当前工作表中的数据使用何种图表，可以通过以下方法找到满意的图表类型:选中工作表数据清单中的任意一个数据，对标准类型的图表来说，你可以选中“图表类型” 及其“子图表类型”。然后点击“按下不放可查看实例”按钮，就可以在选项卡右侧看到数 据生成的图表实例。查看“自定义类型”选项卡的图表实例更简单，你只要将其中的图表类 型选中，就可以在选项卡右侧看到结果了。（2）选择数据源:图表向导的第二步打开“图表源数据”对话框。如果你需

36、要将 图 4中的数据按“还款年限”分类，应把对话框中的“列”选中；如果你需要将图 4中的数据按“可能贷款利率”分类， 应把对话框中的“行”选中。然后将光标放进“数据区域”框内，根据图标制作需要，选中 工作表中的数据区域 （如图 4中的 D3:G5） ，则“ =Sheet1 ! S|DS|3:S|GS|5 ”引用就会出现在 “数据区域”框内。如果你要引用的数据区域不连续。 可以按下 Ctrl 键，然后用鼠标逐个选中工作表中的数据区 域。采用这种方法可以在一个图表中引用多个工作表中的多处数据。“图表源数据”对话框中的“系列”选项卡用来添加新的数据区域和 X 轴标志等内容。如果 你要将新的数据区域

37、( 即“系列” )加入图表，点击“添加”按钮，就可以看到“系列”框中 增加了一个“系列 3”。将其选中后按上面介绍的方法将光标放入“值”框内，就可以根据 需要选中工作表 ( 包括其他工作表 )中的数据区域。(3) 设置图表选项： 图表选项的第三步是设置图表选项，该对话框共有六个选项卡供你输入或设置图表选项。例 如“标题”选项卡可供你输入 X轴和Y轴的名称(如“周”、“数量和售价”)，“坐标轴” 选项卡可以设置Y轴显示“刻度”与否等。这里的操作比较简单，你只要边预览边尝试就可 以掌握。3. 图表加工向导制作图表结束后，可以对图中的内容进行加工，从而使它变得更加美观。(1) 修改图例格式：如果你感

38、觉向导使用的图例字体和背景等不合适， 可以右击图表中的图例， 选择“图例格式” 命令。打开“图例格式”对话框， 可以看到“图案”、 “字体”、 “位置”三个选项卡。 “图 案”选项卡用来设置图例的边框和内部填充色等属性。 通过“字体”选项卡你可以像 Word那 样设置图例的字体、字号、字形等。“位置”选项卡提供了“靠下”、“右上角”等五个位 置选项，你只须选中一个并确定，图例就会自动放到你需要的地方。当然，你也可以选中图 例，然后用鼠标将它拖到图表的任何位置。如果你不需要图例，选择右键菜单中的“清除” 命令即可删除。(2) 修改坐标格式：与“图例”相似，图表的坐标轴文字的属性也可以修改。你只要

39、右击坐标轴文字，选择“坐 标轴格式”命令，即可打开“坐标轴格式”对话框。其中“图案”、“字体”两个选项卡的 用途与“图例”相同。“对齐”选项卡用来设置坐标轴文本的对齐方式，它和“单元格格 式”、“对齐”选项卡基本相同，了解工作表格式设置的用户很容易掌握有关操作。(3) 修改背景和字体：如果你感觉图表的背景不够靓丽，只须右击图表的背景，选择“图表区格式”命令打开相应 的对话框。其中“图案”选项卡可以设置图表区的边框样式、是否显示阴影和圆角等，还可 以选择不同颜色或填充效果美化图表背景。“字体”选项卡可以修改图表的文字格式，使文 字显示的更加协调。“属性”选项卡用来设置“对象位置”和“锁定”等。你

40、可以充分发挥 自己的想象力，把图表打扮得漂漂亮亮。如果你感觉向导中选择的图表类型不合适，只要右击图表中的任意位置，选择“图表类型” 命令，即可打开“图表类型”对话框，你就可以再次进行选择了。(5) 添加趋势线：为了准确了解数据的变化趋势，我们还可以给图表加上趋势线，具体操作方法是：用鼠标右 击图表中需要添加趋势线的数据系列 (就是与数据对应的图线 ) ，选择“添加趋势线”命令， 就可以在对话框中选择你需要的趋势线类型了。4. 单变量分析图表上文介绍过的某企业四季度销售利润总额指标定为 1650 万元，如果其他条件保持不变， 销售 收入需要增加多少的问题。这个问题采用图表同样可以解决，下面介绍处

41、理的具体方法：(1) 创建图表： 选中存放“销售收入”、“销售成本”、“销售费用”、“营业税”和“销售利润”的A2、B2、C2、D2和E2单元格，点击“图表向导”按钮，按提示制作一个柱形图。为方便查看， 在“图表向导步骤之 2”中，打开“系列”选项卡，点击“名称”框，点击数据清单中的列 表记，点击“添加”按钮，将系列名称放入图例完成图表制作。(2) 分析图表：接着双击利润总额数据的柱状图，使其四周出现六个黑色的控点。再用鼠标指向利润总额柱 状图的上沿， 向上拖动直到显示的数据为 1650为止(光标附近会显示当前数据大小 )。此时松 开鼠标，就会弹出“单变量求解”对话框。 Excel自动将利润总

42、额地址“ E2填入“目标单 元格”框， 同时将“ 1650”填入到“目标值”框 ( 如果利润总额的数值不准确， 可以在这里手 工填写)。在可变单元格框中输入销售收入所在的单元格“ S|AS|2”，也可以点击“可变单元 格”选项，然后用鼠标点击 A2单元格。点击“确定”按钮开始执行单变量求解，完成后再次 “确定”。与图表关联的工作表数据就会发生变化，显示出单变量求解的结果。四、数据透视表Excel 图表侧重于数据的静态分析，很难使用数据字段的组合动态查看数据。为此， Excel 提 供了面向数据清单的汇总和图表功能，这就是数据透视表和数据透视图。下面重点介绍数据 透视表的数据分析方法。1.创建数

43、据透视表数据透视表可以根据分析要求进行数据操作。例如进行不同级别的数据汇总，添加或删除分 析指标，显示或隐藏细节数据，甚至改变数据透视表的版式等等。下面以 图 6 所示的数据清 单为例，说明数据透视表的创建方法。(1) 选择数据来源和报表类型：首先将作为数据源的数据清单全部选中(即图6中的A1:D9区域)，点击“数据一数据透视表 和图表报告” 菜单命令，打开“向导步骤之一”对话框，选中其中的“ Microsoft Excel 数 据清单或数据库”和“数据透视表”进入“下一步”。(2) 选择数据区域：由于我们事先选中了待分析的数据区域， 所以区域的绝对引用“ S|AS|1:S|DS|9 ”会自动

44、进入 “选定区域”框。否则你必须点击“选定区域”框，然后拖动鼠标选择要分析的数据区域。(3) 选择显示位置： 制作的最后一步是选择数据透视表的显示位置， 可以选择“新建工作表”或“现有工作表”。 如果选择后者， 则需要点击当前工作表的某个空白单元格， 以确定数据透视表左上角的位置。 点击向导中的“确定”按钮，带有数据源列字段的“数据透视表”对话框弹出，前面选定的 位置显示报表框架。(4) 字段拖放： 这一步是创建数据透视表的关键，你必须根据数据分析的需要，挑选某些数据字段将其拖入 报表框架。假如我们需要分析各家电连锁店 5、6 月份的销售数量， 应当将“数据透视表”对 话框中的“连锁店”拖入报

45、表框架的“列字段”处，再将“月份”拖入报表框架的“行字 段”处，最后将“销售数量 (台)” 拖入报表框架中间的“数据项”， 一个销量分析报表就建 好了。2.分析内容调整与普通表格相比， 用户对数据透视表的内容有更大控制权， 这给数据分析带来了更多的自由。(1) 内容分组：上面建好的数据透视表含有“月份”，它是一个日期型数据。此类型的数据可以根据需要按日、月或季度等重新分组。假如你需要分析 20XX年度的家电销量，其具体操作步骤是：用鼠 标右击数据透视表中“5 月”或“6月”所在的单元格， 选择“组及分级显示”中的“组合” 命令。打开“分组”对话框，先点击“依据”列表框中的“月”(将其取消 )，

46、再点击其中的“年”并确定。则数据透视表“月份”下的“5月”、“6月”就会消失，代之以“ 20XX年”如果你希望按年度查看各月的数据，可以同时选中“依据”列表框中的“月”和“年”。数 据透视表就会在“求和项”下分别显示“年”和“月”，并据此分组显示数据。(2) 修改分析对象：如果你想再次分析各月份不同家电的销售量，也就是更换分析对象。并不需要将已完成的数 据透视表删除，只须在原来基础上删除或添加分析字段，即可生成一个新的报表，这是数据 透视表的重要优点。(8) 修改显示方式：生成新报表的操作方法是：将数据透视表中的原有列字段删除，你只须用鼠标按住图中的列 字段“连锁店”， 将它拖到数据透视表对话

47、框即可。 再将数据透视表对话框中的“家电名称” 拖入列字段位置，即可获得各月不同家电销售量的分析报表。按照这种方法，我们很容易对行字段和数据项进行修改，从而进行多个项目的快速分析。(3) 分类显示：Excel 中所有行、列字段都带有下拉按钮。如果你想了解表中某个分类的数据，可以点击该 字段的下拉按钮， 在列表中选择需要显示的分类数据。 假如你想显示 5 月份的家电销售数据， 可以点击“月份”下拉按钮。保留列表中的“ 20XX年5月”，将其他项目前的选中标记全部 去掉，此后的数据透视表仅仅显示 5 月份的销售数据。(4) 显示明细数据：数据透视表的数据一般都是由多项数据汇总而来。为此， Excel 提供了查看明细数据的方法。 假如你想查看5月份各家连锁店的销售额，只须双击行字段中的“ 20XX年5月”，就会弹出 一个“显示明细数据”对话框。 你只要选中明细数据所在的字段 (例如“连锁店” )，点击“确 定”按钮。数据透视表就会增加一个“连锁店”字段，并显示该字段对应的明细数据。此后，