Excel2003精品教程__数据分析与管理

第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右侧的按钮，在弹出的列表框中选择某类书，例如“高等数学”，单独统计出这类书的销售数量、销售额等建立页字段后的数据透视表可以将每页放在不同的工作表中，以进行单独的打印或绘图操作。选定页字段“图书名称”工作簿中增加了新的工作表“PhotoshopCS2”、“法学概论”等，每个工作表中都有相关类型的图书统计8.2.5改变透视表中的数据要改变数据透视表中的数据，首先应切换到存放数据清单的工作表，在数据清单中修改所需数据完成修改后再切换到需要更新的数据透视表，单击数据“数据透视表”工具栏上的“刷新数据”按钮，此时可看到当前数据透视表闪动一下，数据透视表中的数据自动更新8.2.6变换汇总方式数据透视表使用概要函数来汇总数据。默认情况下，透视表用“求和”（SUM）函数来计算“数值型”数据，用“计数”（COUNT）函数来计算“文本型”数据。默认的函数有时不能满足用户需要，因此Excel提供了其他概要函数，例如平均值、最小值和最大值等函数。选择汇总方式，如“平均值”，单击“选项”按钮，选择数据显示方式，如“占总和的百分比”变换汇总方式后的数据透视表8.2.7删除数据透视表单击要删除的数据透视表中的任一单元格，单击“数据透视表”工具栏上的“数据透视表”按钮，在弹出的菜单中选择“选定”选项中的“整张表格”选择“编辑”“清除”“全部”菜单，工作表中的“数据透视表”被删除8.3假设求解假设求解是在一定的假设条件或约束条件下，按一定的算法求得一个或一组结果。8.3.1模拟运算表模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。1．单变量模拟运算表例如，我们准备贷一笔款，比如50000元，贷款期限为10年，年利率为8%，如果贷款利率发生了变化，如果按月偿还，那么每月偿还的金额是多少呢？利用单变量模拟运算表解决此问题的操作步骤见视频8-4。双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响。2．双变量模拟运算表下面以刚才介绍的单变量模拟运算的假设作为例子来了解一下双变量模拟运算的方法，其操作步骤见视频8-5。8.3.2单变量求解一般用公式计算时，是根据变量求结果。若先确定结果值，再求变量值，就需要用单变量求解来解决问题，它是正常运算的逆运算，在实际工作中有很高的实用价值。但是，值得注意的是，用单变量求解一次只能求一个变量。假设要投建一个新的项目，希望从银行贷款，若有能力按月付款50000元，利率一定，如年利率为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=(90x1+0.2x12)+(90x2+0.2x22)+(90x3+0.2x32)v=6(x1-80)+6(x1+x2-80-120)因此，目标值是：min(P+V)本问题的约束条件包括：生产能力的约束条件：x1≤250，x2≤250，x3≤250交货数量的约束条件：x1≥80，x2≥200，x3≥350。使用“规划求解”的方法见视频8-7。8.3.4使用方案1．建立方案假设我们希望把“单变量模拟运算表”中建立的“偿还贷款计算”作为方案保存起来，则建立方案的操作步骤见视频8-8。2．管理方案建立方案后，可以对其进行修改。首先选择“工具”“方案”菜单，打开“方案管理器”对话框，选择待编辑方案的名称，然后单击“编辑”按钮，即可进行相应的修改，例如在“方案变量值”对话框中输入可变单元格所需的数值。如果要保存所做的修改，可单击“确定”按钮；如果要返回“方案管理器”对话框而不改变当前方案，可单击“取消”按钮。如果在对方案进行修改后，没有改变它原来的名称，那么修改后的可变单元格中的新值将替换原来方案中的值。3．多种方案汇总一个方案只能说明一种情况，要进行决策就应该分析、比较多种方案，从中找到决策的最佳效益点。此处要求输入包含每个方案有效结果的单元格引用，多个引用间需用逗号隔开8.3.5方差分析方差分析是对数据结果做分析的一种常用的统计方法，其类型包括3种：单因素方差分析、可重复双因素分析和无重复双因素分析。下面通过一个例子来说明，操作步骤见视频8-9。