《数据分析与管理》PPT课件.ppt

第8章 数据分析与管理 合并计算 数据透视表和数据透视图 假设求解 8.1 合并计算 8.1.1 建立合并计算 所谓合并计算，是指用来汇总一个或多个源区域中的数据的 方法。Excel提供了两种合并计算数据的方法：一是按位置合 并计算，即将源区域中相同位置的数据汇总；二是按分类合 并计算，当源区域中没有相同的布局时，采用分类方式进行 汇总。 1按位置合并计算数据 按位置合并计算数据，是指在所有源区域中的数据被相同 地排列以进行合并计算，也就是说从每一个源区域中合并 计算的数值必须在被选定源区域的相同位置上。 “飞扬分公司C”同 前两个工作表不在 一个工作簿中 输入各分公司工 作表中的数据 为合并计算的数据 选定目的区。选择“ 飞扬总公司”工作表 为当前工作表，然 后选择“数据”“合 并计算”菜单 选择要引用的 数据区域 合并后的 所有数据 添加“飞扬分公司 B”的数据引用区 域 当进行合并计算时，还可以引用不同工作簿中的工作表区域 ，本例中“飞扬分公司C”与其他两个分公司的工作表不在同 一工作簿中，操作时应先打开“飞扬分公司C”工作表。 添加飞扬分公司A 和B的引用区域 切换到“飞扬分公司C” 工作表所在工作簿 在打开的工作簿中选择“飞扬分 公司C”工作表并选择引用区域 添加所有引 用数据区域 合并后的 所有数据 2通过分类来合并计算数据 通过分类来合并计算数据，是指当多重来源区域 包含相似的数据却以不同的方式排列时，可依不 同分类进行数据的合并计算。也就是说，当选定 的表格具有不同的内容时，我们可以根据这些表 格的分类来分别进行合并计算。 下面以某班的成绩统计为例，具体介绍如何利用 Excel合并计算功能来登记考分，提高工作效率。 新建一个工作簿，并在其中建立6个工作表 在“汇总”工作表中单 击合并计算数据目标 区域左上角的单元格 A1，然后 选择“数据 ”“合并计算”菜单 选择“求和”函数， 然后单击“引用位置” 输入框后面的折叠按 钮 在“语文”工作表中，用 鼠标拖动选定“姓名”和 “语文”两列数据 单击“添加”按钮， 将选择的源数据 添加到“所有引用 位置”列表框中 将“英语”、“数学”、“化学” 和“物理”等工作表的数据重 复执行上述步骤，将他们所 在的地址全部添加到“所有 引用位置”列表框中 在“标签位置”区 选择“首行”和“最 左列”复选框 每个学生的各科 成绩汇总在一起 3合并计算的自动更新 单击这些+号可查看可 查看相关合并计算的明 细数据，例如查看源区 域中的明细数据 如果希望数据源改变时， Excel自动更新合并的工作 表，只需在“合并计算”对话 框中选中“创建连至源数据 的链接”复选框即可 8.1.2 更改合并计算 对于一个已建立合并计算的工作表文件，还可以做进一步的 编辑，例如为合并计算增加源区域。但必须注意，只有对没 有建立与源区域链接的合并工作表，才能够执行编辑操作。 1为合并计算增加源区域 单击合并计算结果 工作表任一单元格 ，然后选择“数据 ”“合并计算”菜单 选择“政治”工作表中 的引用区域，然后 单击折叠按钮返回“ 合并计算”对话框 单击折 叠按钮 增加源区域 后的工作表 单击“添加”按钮， 将“政治”添加到“所 有引用位置”列表中 2删除源区域 单击合并计算结果工 作表中任一单元格， 然后选择“数据”“合 并计算”菜单 选择要删除的 源区域，如物 理，然后单击“ 删除”按钮 单击“合并计算”对话 框中的“确定”按钮， 结果得到删除物“理 源”区域后的工作表 3改变源区域的引用 单击合并计算结果工作表中任一单元 格，然后选择“数据”“合并计算”菜单 ，打开“合并计算”对话框 选择要更改的源区域， 如“数学”，然后单击“ 引用位置”折叠按钮 在工作表重新 选择引用区域 单击“添加”按钮将新选 择的数据区域添加到“ 所有引用位置”列表中 选择编辑前的源区域，单 击“删除”按钮将其删除 改变区域引 用后的结果 1创建数据透视表 假设某图书公司有3个部门，分别为一部门、二部门、三部 门，销售的图书有高等数学、工程制图、政治经济学等，每 天的销售情况都输入到一个工作表中。如果要统计每个部门 销售的各种图书的数量、总数、销售收入等，可通过创建数 据透视表来实现。其操作步骤见视频8-1。 8.2 数据透视表及数据透视图 8.2.1 建立数据透视表与数据透视图 数据透视表是一个交互式表格，它可以快速地组 合和比较数据。它不仅具有转换行和列来查看源 数据的不同汇总结果、显示不同页面以筛选数据 、根据需要显示区域中的细节数据和设置报告格 式等功能，还具有链接图表的功能。数据透视图 则是一个动态的图表，它可以将创建的数据透视 表以图示的形式显示出来。 完成后的数 据透视表 2创建数据透视图 创建数据透视图与创建数据透视表的方法相同，只需在以 上第3步中选择“数据透视图（及数据透视表）”单选钮即可 ，操作步骤见视频8-2。当创建数据透视表后，单击“数据透 视表”工具栏上的“图表向导”按钮，也可快速地创建数据透 视图。 根据“一部门”图书销售 情况创建的数据透视图 3“数据透视表”工具栏 8.2.2 重新布局透视表 在设置透视表的布局时，可随意将数据清单的字段拖到“行”、 “列”或“数据”的位置上，因此，要想改变行列的顺序，可重新 布局数据透视表，再改变一下字段的显示位置就可以了，操作 步骤见视频8-3。 重新布局后的 数据透视表 8.2.3 添加或删除字段 用户可以在数据透视表中添加新的字段或删除不需要的字段 ，以改变数据透视表中使用的数据。有两种添加和删除字段 的方法： 在工作表中添加或删除字段：将要添加的字段从“数据透视 表字段列表”框中拖动到工作表中要创建的字段类型所在的 区域即可；删除字段，只要将字段拖出工作表即可。 使用向导添加或删除字段：与使用向导重新布局图表的方法 类似。在打开“数据透视表和数据透视图向导布局”对话 框后，若要添加字段，可将需要添加的字段从右侧的字段列 表拖动到图形区；若要删除字段，可将其拖到图形区之外。 完成布局后，单击“确定”按钮，再单击“完成”按钮。 8.2.4 利用页面字段简化透视表 为了简化数据透视表，Excel可以按照页字段分页显示数据透 视表，每指定页字段中的一个值，数据透视表就给出一种显示 （页面），从而便于设计分类报表。 本例中，“图书名 称”便是页字段 将“图书名称”字 段拖到“页”区 单击透视表中单元格B1右 侧的按钮，在弹出的列表 框中选择某类书，例如“ 高等数学”， 单独统计出这类书的 销售数量、销售额等 建立页字段后的数据透视表可以将每页放在不同的工作表中， 以进行单独的打印或绘图操作。 选定页字段“ 图书名称” 工作簿中增加了新的工作表“Photoshop CS2”、“法学概论”等，每个工作表中都 有相关类型的图书统计 8.2.5 改变透视表中的数据 要改变数据透视表中的数据，首先 应切换到存放数据清单的工作表， 在数据清单中修改所需数据 完成修改后再切换到需要更新的数 据透视表，单击数据“数据透视表”工 具栏上的“刷新数据”按钮，此时可看 到当前数据透视表闪动一下，数据 透视表中的数据自动更新 8.2.6 变换汇总方式 数据透视表使用概要函数来汇总数据。默认情况下，透视表用“ 求和”（SUM）函数来计算“数值型”数据，用“计数”（COUNT ）函数来计算“文本型”数据。 默认的函数有时不能满足用户需要，因此Excel提供了其他概要 函数，例如平均值、最小值和最大值等函数。 选择汇总方式，如“平均 值”，单击“选项”按钮， 选择数据显示方式，如“ 占总和的百分比” 变换汇总方式后 的数据透视表 8.2.7 删除数据透视表 单击要删除的数据透视表中的 任一单元格，单击“数据透视 表”工具栏上的“数据透视表” 按钮，在弹出的菜单中选择“ 选定”选项中的“整张表格” 选择“编辑”“清除”“ 全部”菜单，工作表中 的“数据透视表”被删除 8.3 假设求解 假设求解是在一定的假设条件或约束条件下，按一 定的算法求得一个或一组结果。 8.3.1 模拟运算表 模拟运算表就是将工作表中的一个单元格区域的数 据进行模拟计算，测试使用一个或两个变量对运算 结果的影响。在Excel中，可以构造两种模拟运算表 ：单变量模拟运算表和多变量模拟运算表。 单变量模拟运算表就是基于一个输入变量，用它来测试对公式 计算结果的影响。 1单变量模拟运算表 例如，我们准备贷一笔款，比如50 000元，贷款期限为10年， 年利率为8%，如果贷款利率发生了变化，如果按月偿还，那 么每月偿还的金额是多少呢？利用单变量模拟运算表解决此 问题的操作步骤见视频8-4。 双变量模拟运算表就是考虑两个变量的变化对公式计算结果的 影响。 2双变量模拟运算表 下面以刚才介绍的单变量模拟运算的假设作为例子来了解一下 双变量模拟运算的方法，其操作步骤见视频8-5。 8.3.2 单变量求解 一般用公式计算时，是根据变量求结果。若先确定结果值，再求 变量值，就需要用单变量求解来解决问题，它是正常运算的逆运 算，在实际工作中有很高的实用价值。但是，值得注意的是，用 单变量求解一次只能求一个变量。 假设要投建一个新的项目，希望从银行贷款，若有能力按月付款 50 000元，利率一定，如年利率为7.5%，偿还期为10年，那么最 高能贷到多少款呢？操作步骤见视频8-6。 8.3.3 规划求解 规划求解可以对有多个变量的线性和非线性规划问题 进行求解，省去了人工编制程序和手工计算的麻烦。 在求解规划问题之前，必须建立规划模型。 例如，某工厂1月份向用户交货80T（吨），2月份交 货120T（吨），3月份交货150T（吨），工厂的最大 生产能力每月250T（吨），月生产费用为 f(x)=(90x+0.2x2)，这里的x为月产量。从函数可知，生 产规模越大，平均生产费用越低。若生产数量大于交 货数量，多余部分可以用于下月交货，但需支付每T （吨）6元仓储费用。那么如何生产才能使工厂的成 本最低呢？ 根据实际问题建立规划模型。设1月份，2月份、3月 份的产量分别为x1、x2、x3，目标值应是工厂的费用 最小，其中费用包括生产费用P和可能发生的存储费 用V之和，P和V的计算公式分别为： p=(90 x 1+0.2 x 12) +(90 x 2+0.2 x 22) +(90 x3+0.2 x 32) v=6(x 1-80)+6(x 1+ x 2-80-120) 因此，目标值是：min(P+V) 本问题的约束条件包括： 生产能力的约束条件：x1250，x2250，x3250 交货数量的约束条件：x180，x2200，x3350。 使用“规划求解”的方法见视频8-7。 8.3.4 使用方案 1建立方案 假设我们希望把“单变量模拟运算表”中建立的“偿还 贷款计算”作为方案保存起来，则建立方案的操作 步骤见视频8-8。 2管理方案 建立方案后，可以对其进行修改。首先选择“工具”“方案”菜 单，打开“方案管理器”对话框，选择待编辑方案的名称，然 后单击“编辑”按钮，即可进行相应的修改，例如在“方案变量 值”对话框中输入可变单元格所需的数值。如果要保存所做的 修改，可单击“确定”按钮；如果要返回“方案管理器”对话框 而不改变当前方案，可