Excel在公司理财中的课件.ppt

1、Excel在公司理财中的应用1.1单变量求解l 功能：已知公式预期的结果，但是不知得到这个结果所需的某一输入值，就可以使用“单变量求解”功能。l 1.1.1在Excel中建立模型 l1.1.2单变量求解 1.1.1在Excel中建立模型l 在我们使用Excel进行分析前，我们需要针对某一问题，建立它的模型。例如某企业要做利润分析，可以在系统中以这种形式和步骤建立模型:l 利润收入支出l 收入销售收入其它收入l 销售收入收入1收入2收入3l 收入1l 收入2l 收入3l 支出 固定成本可变成本利息l 固定成本l因此，根据实际模型，我们可以建立一个与之对应的模型：l l A1=A2A3l l A2

2、=B1+B2l B1=C1+C2+C3l C1=l C2=l C3=l A3=D1+D2+D31.1.2单变量求解 l例如，我们在工组表中建立下述模型：l 单元格 内容l A1 =A2+A3-B1l A2 12000l A3 2100l B1 =B2+B2*0.01+B3l B2 3000l B3 2000“单变量求解”执行步骤：l（1）执行“工具”菜单中的“单变量求解”指令，这时在屏幕上出现如图13-1的对话框。“单变量求解”执行步骤：l（2）在其中的“目标单元格”中输入“$A$1”。在其中的“目标值”框中输入“10000”。在“可变单元格”框中输入“$B$3”。按下“确定”按钮，我们就会看

3、到如图13-2所示的结果，在单元格B3的内容已变为新的数值。1.2 模拟运算表 l 模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。l 单输入模拟运算表：用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。l 双输入模拟运算表：用户对两个变量输入不同值，而查看它对一个公式的影响。1.2.1单输入模拟运算表 l 当对公式中的一个变量以不同值替换时,这一过程将生成一个显示其结果的数据表格。l面向列的模拟运算表。l面向行的模拟运算表。面向列的模拟运算表l A1=A2A3l A2=B1+B2l B1=C1+C2+C3l C1=l C2=l C3=l A3=D1+D2

4、+D3l 我们对上面模型进行模拟运算，假设可变成本分别为固定成本的10%、15%、20%、25%和30%，而其他条件不改变时整个公司的利润会怎样变动？l 操作步骤：l（1）在单一列的输入单元格内，输入要Excel替换的值的序列，我们在“A6”单元格中向下输入上述的序列。在第一个值的上面一行和值列右边的单元格中，键入引用输入单元格的公式，输入单元格可以是工作表上的任一空单元格，我们指定“A5”单元格为输入单元格。输入附加的公式到同一行中第一个公式的右边，即输入“=A2+A3-B2*A5-B2”。如图2所示。l （2）选定包含公式和替换值序列的矩形区域，如图3所示。l （3）执行“数据”菜单中“模

5、拟运算表”命令，出现如图4的对话框。l（4）在“输入引用列的单元格”框中，输入可变单元格地址，在这里我们输入“A5”单元格。按下“确定”按钮。之后，Excel就会替换输入单元格中的所有值，且把结果显示在每一个输入值的右侧，如图5所示。l 如果要观察一个输入值的变化对多个公式的影响，可以在已存在的单输入数据表格中增加一个或多个公式。其操作步骤是在包含已存在公式的行或列中输入新公式，然后选定包含公式和输入值的区域，最后执行“模拟运算表”命令。1.2.2双输入模拟运算表双输入模拟运算表 l 可以使用两个变量对各种情况进行模拟。例如，上例中的利息修改为可变利率时，在不同情况下的利润应该怎样变化。l 在

6、“B5”单元格内输入下面的计算公式“=A1+A2-B2*A5-B3*A4”。l 从公式下面的单元格开始，在与公式同列的单元格中输入想要替换的值，从公式右侧的单元格开始，在与公式同行的单元格中输入想要替换的值，如图6所示。l 选定包含公式及输入值的行和列的单元格区域。执行“数据”菜单中“模拟运算表”命令，出现模拟运算表对话框。在“输入引用行的单元格”框中，输入可变单元格的地址，在这里我们输入“A4”单元格。在“输入引用列的单元格”框中，输入“A5”单元格。按下“确定”按钮。l 按下“确定”按钮之后，Excel就会替换输入单元格中的所有值，且把结果显示为一个表格，如图7所示。我们还可以提供新值来替

7、换工作表上原来输入的值，这样Excel将使用新值重新进行计算。l 3.从模拟运算表中清除结果从模拟运算表中清除结果l 对于不再需要的运算结果，可以将它们从工作表中清除。由于运算结果是在数组中，所以我们不能清除单个值，而必须清除所有的值。还应注意不能选定公式和输入值，否则Excel将清除包括公式和输入值在内的整个表。l 操作步骤如下：l （1）选定数据表格中的所有结果值。l （2）在“编辑”菜单中，选择“清除”，然后再选择“全部”命令1.3使用方案l 在Excel中，对于假设分析的更高级应用是使用方案。所谓方案是指：我们可以建立产生不同结果的输入值集合，并作为方案保存起来。方案是一组称为可变单元

8、格的输入值，并按用户指定的名字保存起来。每个可变单元格的集合代表一组假设分析的前提，我们可以将其用于一个工作簿模型，以便观察它对模型其它部分的影响。我们可以为每个方案定义多达32个可变单元格，也就是说对一个模型我们可以使用多达32个变量来进行模拟分析。1.3.1 建立模型 l 假设生产不同数量的光盘，所产生对利润的影响。在该模型中有4个可变量：单价、数量、推销费率和单片成本。l 利润=销售金额成本费用*（1+推销费率）l 销售金额=单价*数量l 费用=20000l 成本=固定成本单价*单片成本l 固定成本=70000l 然后我们建立一个如图13-10的工作表。我们假设该表是以公司去年的销售为基

9、础的。在单元格“B7:B10”中保存要进行模拟的4个变量分别是：单价、数量、推销费率和单片成本。1.3.2 建立方案 l (1)在“工具”菜单中选择“方案”指令，出现一个如图13-11的对话框。l(2)按下“添加”按钮。出现一个如图13-12所示的“添加方案”对话框。l (3)在“方案名”框中键入方案名。在“可变单元格”框中键入单元格的引用，在这里我们输入“B7:B10”。可以选择保护项“防止更改”。按下“确定”按钮。就会进入到图13-13所示的对话框。l(4)编辑每个可变单元格的值，在输入过程中要使用Tab键在各输入框中进行切换。将方案增加到序列中，如果我们再建立附加的方案，可以选择“增加”

10、按钮重新进入到图13-12的对话框中。l 重复输入全部的方案。当输入完所有的方案后，按下“确定”按钮，就会看到图13-14的对话框。l(5)选择“关闭”按钮，完成该项工作。1.3.3显示方案 l (1)在“工具”菜单中选择“方案”指令，出现一个如图13-14的对话框。l (2)在“方案”列表框中，选定要显示的方案，例如选定3000片方案。l (3)按下“显示”按钮。选定方案中可变单元格的值出现在工作表的可变单元格中，同时工作表重新计算，以反映模拟的结果，如图13-15所示。l (4)重复显示其它方案，最后按下“关闭”按钮。1.3.4 建立摘要报告l 在“工具”菜单中选择“方案”指令，出现方案管

11、理器对话框。按下“总结”按钮，出现一个如图13-16所示的对话框。l 在“报告类型”框中，选定“方案总结”选项。在“结果单元格”框中，通过选定单元格或键入单元格引用来指定每个方案中重要的单元格。这些单元格中应有引用可变单元格的公式。如果要输入多个引用，每个引用间用逗号隔开。最后按下“确定”按钮。Excel就会把方案摘要表放在单独的工作表中，如图13-17所示。1.4记录单l功能：让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。输入数据的原则：l 避免在一个工作表上建立多个数据清单，因为数据清单的某些处理功能（如筛选等），一次只能在同一工作表的一个数据清单中使

12、用。l 在工作表的数据清单与其它数据间至少留出一个空白列和一个空白行。在执行排序、筛选或插入自动汇总等操作时，这将有利于 Microsoft Excel 检测和选定数据清单。l 避免在数据清单中放置空白行和列，这将有利于 Microsoft Excel 检测和选定数据清单。l 避免将关键数据放到数据清单的左右两侧。因为这些数据在筛选数据清单时可能会被隐藏。l 列标志l 在数据清单的第一行里创建列标志。Microsoft Excel 使用这些标志创建报告，并查找和组织数据。l 列标志使用的字体、对齐方式、格式、图案、边框或大小写样式，应当与数据清单中其它数据的格式相区别。l 如果要将标志和其它数

13、据分开，应使用单元格边框（而不是空格或短划线），在标志行下插入一行直线。l 行和列内容l 在设计数据清单时，应使同一列中的各行有近似的数据项。l 在单元格的开始处不要插入多余的空格，因为多余的空格影响排序和查找。l 不要使用空白行将列标志和第一行数据分开。1.4.1记录单的创建l 选择字段l 为数据库选择字段是极其重要的，它决定了在每个数据库记录中的信息及数据库本身的能力。例如，要决定在应收帐数据库中是否应包括公司名称、发票日期、金额等。通用的原则是，根据要建立的数据库，将字段分得更细些。例如，要建立一个客户欠款的数据库，可以定义下列字段：公司名称、地址、电话、欠款日期、欠款金额等等。在建立字

14、段时要根据当前和将来的需要。通常字段越多越灵活。对于数据库中的每个字段应该是唯一的。用这种方式，数据库的记录也是唯一的，因为记录中的每个字段将包含特有的信息。例如一个属于该记录的日期或时间。在这种方式下，字段与当前可存取的记录确定了数据项的唯一性。l 安排字段位置l 字段名称构成数据库顶部的行，因此它们必须安排在一行连续的每一列中。字段名称应该按逻辑顺序组织，相似的信息应该组织到一起。例如，使用发票号、日期、公司名称作为相邻的字段描述信息会使数据库更易于理解和使用。l 命名字段l 字段名称是数据库操作的标识成分。Excel根据字段名称来执行排序和查找等数据库操作。因此在选择字段名时应该慎重，最

15、好选择容易记忆的字段名。和命名区域和文件的规则一样，数据库的字段名字必须遵循以下的规则：字段名只能是文字或文字公式（如=1992）。字段名不能包含数字、数值公式、逻辑值。字段名可以使用1255个字符，字段名必须是唯一的。1.4.2建立一个数据库 l 当完成对一个数据库的结构设计后，就可以在工作表的首行依次输入各个字段：员工姓名、地区、日期、产品名称、销售数量、销售金额、销售金额，如图12-1所示。1.4.3 输入数据 l要加入数据至所规定的数据库内,有两种方法：l一种是直接键入数据至单元格内。l 一种是利用“记录单”输入数据。使用“记录单”输入数据的操作步骤：l（1）在想加入记录的数据清单中选

16、中任一个单元格。l （2）从“数据”菜单中选择“记录单”命令。屏幕上会出现一个如图12-2的对话框。l 选择“确定”按钮，出现图12-3的对话框l 在各个字段中输入新记录的值。要移动到下一个域中，按Tab键。当你输完所有的记录内容后，按Enter键即可加入一条记录。如此重复加入更多的记录，当你加完所有记录后，选择“关闭”按钮。我们就会看到在清单底部加入了新增的记录，如图12-4所示。直接输入数据的操作步骤：l在现有记录的中间插入空的单元格，然后键入记录数据；原来所输入的数据库内容会自动下移。l(1)选定要插入记录的单元格。在“插入”菜单中选择“单元格”命令，在屏幕上出现一个对话框。l (2)选

17、择“整行”，按下“确定”按钮，我们就可以看到插入的单元格。输入记录内容到单元格中。1.4.5 编辑记录 l 对于数据库中的记录，我们可以采用在相应的单元格上进行编辑，也可以对记录单进行编辑，其操作过程如下：l (1)选择数据清单中的任一单元格。l (2)从“数据”菜单中选择“记录单”命令，出现一个记录单对话框。l (3)查找并显示出要修改数据的记录。编辑该记录的内容。l (4)选择“关闭”按钮退出。1.4.6 删除一条记录l 对于数据库中不再需要的记录，我们可以使用“删除”命令将其从数据库中删除。使用记录单删除一条记录的操作步骤如下：l (1)选择数据清单中的任一单元格。从“数据”菜单中选择“

18、记录单”命令。出现一个记录单对话框。l (2)查找并显示出要删除的记录。选择“删除”按钮，出现一个如图12-5的确认对话框。l (3)回答确认信息。按下“确定”按钮。l 注意：当我们使用数据记录单来删除数据时，不能通过“恢复”按钮或“取消”命令来恢复数据。1.5分类汇总l1.5.1建立分类汇总l1.5.2分类汇总的嵌套l1.5.3分类汇总的删除l1.5.4创建汇总报表1.5.1建立分类汇总l 我们经常需要对图1的二维数据表格中某列数据字段(如“四级部门”)进行分类汇总。建立分类汇总的具体步骤：l（1）选中四级部门列的任意一个单元格，按一下“常用”工具栏上的“升序排序”或“降序排序”按钮，对数据

19、进行排序。l注意：使用“分类汇总”功能时，一定要按分类对象进行排序！l（2）执行“数据分类汇总”命令，打开“分类汇总”对话框，此时如果Excel无法确定当前列表或选定区域哪一行包含列标签，就会报如下错误，如图2；l 我们点击“确定”，会另外打开“分类汇总”对话框,如图3；l（3）将“分类字段”设置为“四级部门”；“汇总方式”设置为“计数”；“选定汇总项”为“二级部门”和“有无相片”；再选中“每组数据分页”选项。最后确定返回。l 汇总函数可用于自动分类汇总、数据合并、数据透视表和数据透视图报表中。函数功能Sum对数值求和。是数字数据的默认函数。Count数据值的数量。汇总函数 Count 的功能

20、与工作表函数 COUNTA 的功能相同。Count 是除了数字型数据以外其他数据的默认函数。Average求数值平均值。Max求最大值。Min求最小值。Product求数值的乘积。Count Nums计算数字数据值的数量。汇总函数 Count Nums 的功能与工作表函数 COUNT 的功能相同。StDev估算总体的标准偏差，样本为总体的子集。StDevp计算总体的标准偏差。汇总的所有数据为总体。Var估计总体方差，样本为总体的子集。Varp计算总体的方差。汇总的所有数据为总体。l（4）分类汇总完成（图4是“四级部门”类的打印预览结果）。1.5.2分类汇总的嵌套 l（1）首先进行分类汇总。然后

21、，单击“数据”“分类汇总”菜单命令，弹出“分类汇总”对话框。（2）汇总方式选“求和”，在“选定汇总项”列表中选择需要的复选框，取消选取“替换当前分类汇总”复选框，单击“确定”即可。1.5.3删除分类汇总 l单击“数据”“分类汇总”菜单命令，弹出“分类汇总”对话框，选择“全部删除”按钮，即可删除分类汇总。1.5.4.创建汇总报表 l 将分类汇总添加到列表中时，列表就会分级显示，这样您可以查看其结构。单击分级显示符号、和 可创建汇总报表，这样可以隐藏明细数据，而只显示汇总。1.6条件求和l【条件求和】加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，如果采用【典型安装】

22、或默认安装，则【条件求和】工具没有被安装。l 希望在第一次安装Office时就安装“条件求和”，则需要在安装Office时选择自定义安装，再选中“选择应用程序的高级自定义”选择框，选择“下一步”，会弹出一个对话框，在Excel的加载宏中把“条件求和”的安装方式改为“从本机运行”。l 希望在Excel中安装“条件求和”，单击“工具”菜单中的“加载宏”命令，在弹出的“加载宏”对话框中选择“条件求和”，单击“确定”按钮，此时系统要求插入Office安装盘。l 如图1所示各产品的销售业绩工作表，我们希望分别求出“东北区”和“华北区”两部门各类产品的销售业绩，或者在同一部门中的不同组也要求出各产品的销售

23、业绩。1.6.1条件求和分析工具法l（1）选取原始表格中A1:E7全部单元格，点击“向导条件求和”命令，会弹出条件求和的向导对话框，在第一步中已经会自动添加了需要求和计算的区域，如图7所示。l（2）点击“下一步”，在此步骤中添加求和的条件和求和的对象。如图8所示。在“求和列”下拉列表中选择要求和的数据所在列，而在“条件列”中指定要求和数据应满足的条件。设置好后，点击“添加条件”将其添加到条件列表中。条件列可多次设置，以满足多条件求和。l （3）在“条件求和向导-4步骤之3”界面中选择统计结果的显示方式为“复制公式及条件”，单击“下一步”按钮。（4）最后要选择目标数据存放的单元格，并在每次选择后

24、单击“下一步”按钮，直到最后单击“完成”按钮。此时就会出现我们要统计的数据结果。1.6.2分类汇总法l 首先选中A1:E7全部单元格，点击菜单命令“数据排序”，打开“排序”对话框。设置“主要关键字”和“次要关键字”分别为“部门”、“组别”，如图2所示。确定后可将表格按部门及组别进行排序。l 然后将鼠标定位于数据区任一位置，点击菜单命令“数据分类汇总”，打开“分类汇总”对话框。在“分类字段”下拉列表中选择“部门”，“汇总方式”下拉列表中选择“求和”，然后在“选定汇总项”的下拉列表中选中“A产品”、“B产品”、“C产品”复选项，并选中下方的“汇总结果显示在数据下方”复选项，如图3所示。确定后，可以

25、看到，东北区和华北区的三种产品的销售业绩均列在了各区数据的下方。l 再点击菜单命令“数据分类汇总”，在打开的“分类汇总”对话框中，设置“分类字段”为“组别”，其它设置仍如图3所示。注意一定不能勾选“替换当前分类汇总”复选项。确定后，就可以在区汇总的结果下方得到按组别汇总的结果了。如图4所示。1.6.3输入公式法输入公式法l比如我们要对东北区A产品的销售业绩求和。l（1）可以点击C8单元格，输入如下公式:=SUMIF($A$2:$A$7,=东北区,C$2:C$7)。回车后，即可得到汇总数据。l（2）选中C8单元格后，拖动其填充句柄向右复制公式至E8单元格，可以直接得到B产品和C产品的汇总数据。l

26、（3）如果把上面公式中的“东北区”替换为“华北区”，那么就可以得到华北区各汇总数据了。l（4）如果要统计“东北区”中“辽宁”的A产品业绩汇总，那么可以在C10单元格中输入如下公式:=SUM(IF($A$2:$A$7=东北区,IF($B$2:$B$7=辽宁,Sheet1!C$2:C$7)。然后按下“Ctrl+Shift+Enter”键，则可看到公式最外层加了一对大括号(不可手工输入此括号)，同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了，如图5所示。l（5）拖动C10单元格的填充句柄向右复制公式至E10单元格，可以得到其它产品的业绩和。l（6）把公式中的“东北区”、“辽宁”换成

27、其它部门或组别，就可以得到相应的业绩和了。1.7数据透视表l 功能：您想对右边数据按照类别、年份和季度进行费用汇总，就可以使用数据透视表。l 应用数据透视表后的结果如右图l 应用范围：记录数量众多、以流水帐形式记录、结构复杂的工作表，想将其中的一些内在规律显现出来，就可建立数据透视表。1.7.1创建数据 l 将现有的电子表格或数据库中的数据导入到新 Excel 工作表中。l 删除所有空行或空列。l 删除所有自动小计。l 确保第一行包含各列的描述性标题。l 确保各列只包含一种类型的数据例如，一列中是文本，另一列中是数值。1.7.2启动“数据透视表和数据透视图向导”l 从要根据其创建数据透视表的表

28、格选择一个单元格。在“数据”菜单上，单击“数据透视表和数据透视图”启动“数据透视表和数据透视图向导”。l 在“数据透视表和数据透视图向导-3 步骤之 1”的“请指定待分析数据的数据源类型:”下，单击“Microsoft Office Excel 列表或数据库”。在“所需创建的报表类型:”下，单击“数据透视表”。单击“完成”。l 注意：如果工作簿是共享的（共享工作簿：允许网络上的多位用户同时查看和修订的工作簿。每位保存工作簿的用户可以看到其他用户所做的修订。），则“数据透视表和数据透视图”命令不可用。l Excel 会在您的工作簿中插入一个新工作表，该新工作表包含报表的布局区域（即“数据透视表”

29、工具栏）和“数据透视表字段列表”。1.7.3构建数据透视表 l 将“类别”字段从“数据透视表字段列表”拖动到数据透视表的“将行字段拖至此处”区域。l 不要担心会将字段放错位置，您以后可以随意移动它们。l 请注意，向导会使用您的工作表中“类别”列的数据自动填充行。此外，“数据透视表字段列表”中所使用的字段以粗体显示。l 行中为“类别”的数据透视表l 如果看不到“数据透视表字段列表”，请在带轮廓的布局区域中单击。l 如果仍然看不到它，请单击“数据透视表”工具栏上的“显示字段列表”按钮。该工具栏显示时应该浮动在工作表上或停靠在一侧。如果没有显示该工具栏，请单击“视图”，指向“工具栏”然后单击“数据透

30、视表”。1.7.4给报表添加数据 l 将包含要汇总数据的字段，例如，“金额”字段，从“数据透视表字段列表”拖动到数据透视表的“请将数据项拖至此处”区域。l 现在，数据透视表会显示每个类别的费用总计。l 包含类别和费用总计的数据透视表1.7.5给报表添加页排序字段 l 要按“类”筛选数据，可以在页顶部创建一个下拉箭头。l 将“类”字段从“数据表字段列表”拖动到“将页字段拖至此处”区域。单击“类”下拉箭头，然后选择一个类。l 现在，您可以看到某个时间某一个类的费用类别。l 按“类”筛选的结果.l 要查看所有“类”的数据，请单击“类”下拉箭头，然后单击“（全部）”。按金额排序费用类别l 您可以利用“

31、自动排序”功能来首先查看最大的费用类别。l 单击“类别”字段，单击“数据透视表”工具栏上的“数据透视表”，然后单击“排序并列出前 10 项”。在“自动排序选项”下，单击“降序”。在“使用字段”框中，单击“金额总和”，然后单击“确定”。l 按“金额”排序的费用类别查看各季度的费用类别l 将“日期”字段拖动到“行”区域。右键单击“日期”字段，指向“组及显示明细数据”，然后单击“组”。在“依据”框中，单击“季度”。如果源工作表包含多个年份的数据，还要单击“年份”。如有必要，清除“月份”选项，然后单击“确定”。l 各年份、季度和类别的费用查看各季度每个费用类别的变化情况l 您可以在很短的时间内更改数据

32、透视表中的行和列。将“类别”字段拖动到“年份”字段的左侧。l 各类别、年份和季度的费用按员工筛选结果要按员工筛选数据，可以在页的顶部创建第二个下拉箭头。l 将“员工”字段拖动到“类”字段之上或之下的“页字段”区域。单击“员工”下拉箭头，选择一名员工，然后单击“确定”。l 按“员工”筛选的结果在不同的工作表上显示每个员工的结果l 除了查看一名员工的结果，您还可以将每个员工的结果显示在不同的工作表中。l 在“数据透视表”工具栏上，单击“数据透视表”，然后单击“分页显示”。在“分页显示”对话框中，单击“员工”，然后单击“确定”。l 多个页将会作为工作表标签显示出来。l 位于单独的工作表上的员工结果

33、l 源数据发生变化时刷新数据透视表l 每当源电子表格中的数据发生变化时，可以立即更新您的数据透视表。l 单击“数据透视表”工具栏上的“刷新数据”按钮。l 瞬间让数据透视表看上去漂亮非凡l 使用“自动套用格式”迅速获得具有专业外观效果。l 在“数据透视表”工具栏上，单击“设置报告格式”按钮。l 单击一个报表或表格样式，然后单击“确定”。1.8规划求解l【规划求解】加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，如果采用【典型安装】或默认安装，则【规划求解】工具没有被安装。l 希望在第一次安装Office时就安装“规划求解”，则需要在安装Office时选择自定义安装，

34、再选中“选择应用程序的高级自定义”选择框，选择“下一步”，会弹出一个对话框，在Excel的加载宏中把“规划求解”的安装方式改为“从本机运行”。l 希望在Excel中安装“规划求解”，单击“工具”菜单中的“加载宏”命令，在弹出的“加载宏”对话框中选择“规划求解”，单击“确定”按钮，此时系统要求插入Office安装盘。l 例题：某企业在某月份生产甲、乙两种产品，其有关资料如图2-66所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？1.8.1构建模型l（1）目标函数：max销售利润=(14060)x+(180100)yl约束条件：6x+9y 360l 7x+4y 240l 18x+

35、15y 850l y 30l x 0,y 0，且为整数l设x和y分别表示甲产品和乙产品的生产量 l（2）单元格B11和C11为可变单元格，分别存放甲、乙产品的生产量。l（3）单元格B12为目标单元格（销售利润），计算公式为“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”；l（4）在单元格B14中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)”。l 在单元格B15中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)”，l 在单元格B16中输入产品消耗能源合计计算公式“=SUMPRODUCT(B8:C8,B11

36、:C11)”。l（5）单击【工具】菜单，选择【规划求解】项，则系统弹出【规划求解参数】对话框，如图2-67。1.8.3建立规划求解工作表 l（6）在【规划求解参数】对话框中，【设置目标单元格】中输入“$B$12”；【等于】选“最大值”；【可变单元格】中输入“$B$11:$C$11”；在【约束】中添加以下的约束条件：“$B$11:$C$11=整数”、“$B$11:$C$11=0”、“$B$14=$E$3”、“$B$15=$E$4”、“$B$16=$E$5”、“$B$11=$C$9”；l这里，添加约束条件的方法是：单击【添加】按钮，系统会弹出【添加约束】对话框，如图2-68所示，输入完毕一个约束条

37、件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，再输入第二个约束条件。当所有约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。l如果发现输入的约束条件有错误，还可以对其进行修改，方法是：选中要修改的约束条件，单击【更改】按钮，则系统弹出【改变约束】对话框，如图2-69所示，再进行修改即可。l入完毕约束条件后，若还需要添加约束条件，单击【添加】按钮，在弹出的【添加约束】对话框中输入约束条件即可。l（7）如果需要，还可以设置有关的项目，即单击【选项】按钮，弹出【规划求解选项】对话框，如图2-70所示，对其中的有关项目进行设置即可；l（8）在建立好所有的规划求解参

38、数后，单击【求解】，则系统将显示如图2-71所示的【规划求解结果】对话框，选择【保存规划求解结果】项，单击【确定】，则求解结果显示在工作表上，如图2-66所示。l（9）如果需要，还可以单击【规划求解结果】对话框中的【保存方案】，以便于对运算结果做进一步的分析。1.9excel中的分析工具l【分析工具库】加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，如果采用【典型安装】或默认安装，则【分析工具库】工具没有被安装。l 希望在第一次安装Office时就安装“分析工具库”，则需要在安装Office时选择自定义安装，再选中“选择应用程序的高级自定义”选择框，选择“下一步”

39、，会弹出一个对话框，在Excel的加载宏中把“分析工具库”的安装方式改为“从本机运行”。l 希望在Excel中安装“分析工具库”，单击“工具”菜单中的“加载宏”命令，在弹出的“加载宏”对话框中选择“分析工具库”，单击“确定”按钮，此时系统要求插入Office安装盘。l 如果要浏览已有的分析工具，单击【工具】菜单，选择【数据分析】项，则系统弹出【数据分析】对话框。1.9.1相关系数分析l 在进行分析前先将数据按图4-1的格式输入EXCEL工作表中，并对输入的数据进行相关分析。l 单击【工具】菜单，选择【数据分析】项，在出现的数据分析工具栏中选择相关系数。l 单击确定，将出现如图4-2对话框，对话

40、框内容如下：l 1 输入区域：选取图4-1数据表中的绿色和黄色区域，表示标志与数据。l 2分组方式：根据数据输入的方式选择逐行或逐列，此例选择逐列。l 3由于数据选择时包含了标志，所以要勾选标志位于第一行。l 4根据需要选择输出的位置。l 将信息按图4-2输入对话框后，选择确定，输出结果如图4-3所示。1.9.2协方差分析l 在进行分析前先将数据按图4-1的格式输入EXCEL工作表中，并对输入的数据进行相关分析。l 单击【工具】菜单，选择【数据分析】项，在出现的数据分析工具栏中选择协方差。l 单击确定，将出现如图4-2对话框，对话框内容如下：l 1 输入区域：选取图4-1数据表中的绿色和黄色区

41、域，表示标志与数据。l 2分组方式：根据数据输入的方式选择逐行或逐列，此例选择逐列。l 3由于数据选择时包含了标志，所以要勾选标志位于第一行。l 4根据需要选择输出的位置。l 将信息按图4-2输入对话框后，选择确定，输出结果如图4-4所示。1.9.3回归分析l 现假设为了解某指标Y与因素X1、X2之间的影响关系，进行了10次试验，试验数据结果输入EXCEL表中如图10-1。l 单击【工具】菜单，选择【数据分析】项，在出现的数据分析工具栏中选择回归分析。l单击确定，出现如图10-2的对话框，对话框内容如下：l1 Y值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。l2 X值输入区域：

42、选择因素取值数据所在的区域，可以包含标志。.l3 如果选择数据时包含了标志则选择标志复选框。l4 如果强制拟合线通过坐标系原点则选择常数为零复选框。l5 置信度：分析置信度，一般选择95%。l6 输出选项：根据需要选择分析结果输出的位置。l7 残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图。l8 如果希望输出正态概率图则选择响应得复选框。分析的数据结果如图10-3分析的数据结果如图10-4图形结果如图10-5所示。图形结果如图10-6所示。1.10用excel绘制图表l 请根据右表数据绘制图表1.10.1 启动启动“图表向导图表向导”l 先在“情况表”中选中用于绘制

43、图表的数据。比如a3d9区域。l 单击【插入】菜单，选择【图表】项，或从工具栏中找到“图表向导”图标，并单击它，出现右图对话框。1.10.2设置图表类型设置图表类型 l根据恰当地反映数据的情况原则，选择柱形图、条形图、折线图1.10.3 图表数据源图表数据源 l 单击确定按钮，出现“图表数据源”对话框。1.10.4修改图表选项修改图表选项 l 单击“下一步”，出现右图对话框。1.10.5确定图表位置确定图表位置 l 单击“下一步”，出现右图对话框。l excel提供两种图表存放方式，放在新工作表中单独显示，或者嵌入某一工作表中，与相关表格共同显示。l 确定存放方式之后（假定选择“嵌入方式”），单击“完成”按钮，数据绘制的标准图表就完成了。l利用“图表向导”绘制的图表很可能有许多地方不符合你的设想，但你可以直接在已绘制好的图表上进行修改。将鼠标的指示箭头停留在图表的不同区域，你将看到类似图7圆圈内的简要说明，找到需要修改的内容，比如绘图区、数据系列、图例无论哪一项，按下鼠标右键，都会立刻出现一个下拉菜单，挑选合适的项目。l 最终图标