Excel制表技巧(50)函数与表格.doc

Excel制表技巧（50）函数与表格Excel中用内置序列功能快速输入员工姓名我们在制作公司的相关表格时，经常要输入员工的姓名，一个一个地输入(特别是有些生僻字)，太麻烦了，可以将他们制作成内置序列，然后用“填充柄”来拖拉输入：先将员工姓名输入到一列(行)连续的单元格区域中，然后选中他们，执行“工具选项”命令，打开“选项”对话框(如图)，切换到“自定义序列”标签下，按“导入”按钮将其导入到序列中，确定返回。以后，只要输入序列中某个员工的姓名，然后用“填充柄”拖拉，即可输入该员工后续的姓名了。注意：序列定义好了以后，原来工作表中输入的员工姓名可以删除；此序列对所有的Excel工作簿文档都有效。在“自定义序列”标签，我们可以看到所有的“内置序列”。Excel模拟运算表一旦我们在工作表中输入公式后，就可进行假设分析，查看当改变公式中的某些值时怎样影响其结果，模拟运算表提供了一个操作所有变化的捷径。模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。有两种类型的模拟运算表：单输入模拟运算表和双输入模拟运算表。单输入模拟运算表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。双输入模拟运算表中，用户对两个变量输入不同值，而查看它对一个公式的影响。13.2.1单输入模拟运算表当对公式中的一个变量以不同值替换时,这一过程将生成一个显示其结果的数据表格。我们既可使用面向列的模拟运算表，也可使用面向行的模拟运算表。面向列的模拟运算表例如我们对图13-3中的模型进行模拟运算，假设可变成本分别为固定成本的10%、15%、20%、25%和30%，而其他条件不改变时整个公司的利润会怎样变动？其操作步骤如下：（1）在单一列的输入单元格内，输入要Excel替换的值的序列，我们在“A6”单元格中向下输入上述的序列。在第一个值的上面一行和值列右边的单元格中，键入引用输入单元格的公式，输入单元格可以是工作表上的任一空单元格，我们指定“A5”单元格为输入单元格。输入附加的公式到同一行中第一个公式的右边，即输入“=A2+A3-B2*A5-B2”。如图13-4所示。（2）选定包含公式和替换值序列的矩形区域，如图13-5所示。（3）执行“数据”菜单中“模拟运算表”命令，出现如图13-6的对话框。（4）在“输入引用列的单元格”框中，输入可变单元格地址，在这里我们输入“A5”单元格。按下“确定”按钮。之后，Excel就会替换输入单元格中的所有值，且把结果显示在每一个输入值的右侧，如图13-7所示。还可以提供新值来替换工作表上原来输入的值，这样Excel将使用新值重新进行计算。使用基于行的模拟运算表的过程和列类似，读者可以自己练习一下。如果要观察一个输入值的变化对多个公式的影响，可以在已存在的单输入数据表格中增加一个或多个公式。其操作步骤是在包含已存在公式的行或列中输入新公式，然后选定包含公式和输入值的区域，最后执行“模拟运算表”命令。13.2.2双输入模拟运算表复杂的情况，我们还可以使用两个变量对各种情况进行模拟。例如，上例中的利息修改为可变利率时，在不同情况下的利润应该怎样变化。当以不同的值替换公式中的两个变量时，这一过程将生成一个显示其结果的数据表格。在一个单元格中输入引用替换值的公式。公式应引用两个输入单元格，或者是直接的或者是引用其它单元格，这些单元格又引用输入单元格，输入单元格就是其值将要被替换的单元格，例如我们在“B5”单元格内输入下面的计算公式“=A1+A2-B2*A5-B3*A4”。从公式下面的单元格开始，在与公式同列的单元格中输入想要替换的值，从公式右侧的单元格开始，在与公式同行的单元格中输入想要替换的值，如图13-8所示。选定包含公式及输入值的行和列的单元格区域。执行“数据”菜单中“模拟运算表”命令，出现模拟运算表对话框。在“输入引用行的单元格”框中，输入可变单元格的地址，在这里我们输入“A4”单元格。在“输入引用列的单元格”框中，输入“A5”单元格。按下“确定”按钮。按下“确定”按钮之后，Excel就会替换输入单元格中的所有值，且把结果显示为一个表格，如图13-9所示。我们还可以提供新值来替换工作表上原来输入的值，这样Excel将使用新值重新进行计算。13.2.3从模拟运算表中清除结果对于不再需要的运算结果，可以将它们从工作表中清除。由于运算结果是在数组中，所以我们不能清除单个值，而必须清除所有的值。还应注意不能选定公式和输入值，否则Excel将清除包括公式和输入值在内的整个表。操作步骤如下：（1）选定数据表格中的所有结果值。（2）在“编辑”菜单中，选择“清除”，然后再选择“全部”命令。Excel单变量求解Excel2000除了可以做一些一般的计算工作外，更可以做许多的分析工作。例如，使用Excel的规划求解，可以求解最佳值，Excel的目标搜索，可用来寻找要达到目标时，需要有怎样的条件等等。Excel的方案管理器可用来分析各种方案，例如，最佳可能状态、最坏可能状态下可能得到的结果。假设分析是指模型中某一变量的值、某一语句或语句组发生变化后,所求得的模型解与原模型的比较分析。也就是说,系统允许用户提问“如果”,系统回答“怎么样”。这是手工所无法做到的。这不仅解决了复杂性问题,还可通过反复询问在多种方案间进行权衡，以减小风险性。例如，汽车发动机由上千个零部件组成,铝材的涨价会影响材料成本。要求计算使用涨价铝材的成本和采用替代品的成本并进行比较。再如，火车票涨价一倍，那么本年度的差旅费会怎样变动，对全年利润影响怎样等等，这些都属于假设分析。在Excel中，我们通过使用单变量求解和模拟运算表来实现假设分析。13.1.1在Excel中建立模型在我们使用Excel进行分析前，我们需要针对某一问题，建立它的模型。例如某企业要做利润分析，可以在系统中以这种形式和步骤建立模型:利润收入支出收入销售收入其它收入销售收入收入1收入2收入3收入1收入2收入3支出固定成本可变成本利息固定成本因此，根据这些实际模型，我们可以建立一个与之对应的模型：A1=A2A3A2=B1+B2B1=C1+C2+C3C1=C2=C3=A3=D1+D2+D3在建立这种比较复杂的模型时，我们可以利用Excel提供的“名字”功能，来建立和上例实际模型相一致的模型，从而增加模型的可读性，便于其他人理解。13.1.2单变量求解例如，我们在工组表中建立下述模型：单元格内容A1=A2+A3-B1A212000A32100B1=B2+B2*0.01+B3B23000B32000现在我们可以假设，如果我们要单元格A1的内容为“10000”，则单元格B3相应变化，或者我们还有一种说法，如果利润为“0”，那么费用应该怎样变化？在Excel中我们可以通过使用“单变量求解”指令，来进行该类问题的分析。执行步骤如下：（