1.3 数据透视表和模拟运算表
1.3.1 数据透视表
Excel表是由多列不同属性的数据组成的，例如，在Excel人事数据统计表中通常包含职工的“工号”、“姓名”、“性别”、“年龄”、“部门”、“职务”、“职称”、“专业”、“工作年限”、“工资”等数据。在实际工作中，经常需要对其中一些数据进行交叉汇总。例如，要求以“部门”为行、“职称”为列、“人数”为交叉因素的统计汇总表。数据透视表是一种对大量数据进行快速汇总和建立交叉列表的交互式工具，在实际工作中有广泛应用。
例1.2 在光盘文件“例1.2研究所员工资料”中，创建以“职务”为行字段、“性别”为列字段、“人数”为数据区域的数据透视表。
首先打开“研究所员工资料”Excel文件。
（1）打开Excel菜单“数据”|“数据透视表和数据透视图”，如图1.10所示。
图1.10 数据透视表菜单
（2）单击菜单，出现图1.11所示的对话框。
图1.11 数据透视表向导3步骤之1
选定“数据源类型”和“所需创建的报表类型”选项，单击“下一步”。出现并在Excel表中选定数据区域，如图1.12所示。
图1.12 数据透视表向导3步骤之2
单击“下一步”，出现“数据透视表显示位置”对话窗口，如图1.13所示。
图1.13 数据透视表向导3步骤之3
选定“新工作表”。单击“下一步”，单击“完成”。出现图1.14所示数据透视表的空白表和“数据透视表字段列表”、“数据透视表”工具条。如果此时鼠标单击空白表以外的区域，“数据透视表字段列表”、“数据透视表”工具条消失；鼠标单击空白表内部，“数据透视表字段列表”、“数据透视表”工具条将重新出现。
图1.14 拖入数据透视表的行字段和列字段
用鼠标将“数据透视表字段列表”中的“部门”字段拖到行字段位置，将“性别”字段拖到列字段位置，将“姓名”字段拖到数据项位置，即出现图1.15所示数据透视表。
图1.15 生成的单层数据透视表
从以上数据透视表得到的汇总数据可以看出该研究所职工总数、各部门职工数以及各部门男、女职工人数。打开“部门”或“性别”右边的下拉按钮，如图1.16所示。
图1.16 改变数据透视表中字段的显示值
可以在复选框中选择或者取消这个字段的某些值，例如，不选“办公室”，单击“确定”后，数据透视表中就不包括“办公室”这个部门的汇总统计，如图1.17所示。
图1.17 改变数据透视表中字段的显示值后的视图
如果需要删除一个行字段、列字段或者数据项字段，只要用鼠标按住数据透视表中要删除的字段名的单元格，把它拖出数据透视表范围以外就可以了。删除某一个字段以后，从“字段列表”中选择一个新字段，再把它拖到被删除字段所在的单元格。这样就可以更新行字段或列字段。
这里需要说明的是，为什么将“姓名”字段拖到数据项区域，在数据透视表中出现的数值会是人数。右键单击数据项区域，出现右键菜单，选定“字段设置”，如图1.18所示。
图1.18 改变数据透视表中字段的属性
得到字段设置对话窗口，如图1.19所示。
图1.19 定义数据透视表中字段属性的对话窗口
由于“姓名”字段是字符型字段而不是数值型字段，默认的“汇总方式”为“计数”。而该研究所115名员工没有姓名相同的，因此“姓名”字段的“计数”汇总方式就是职工人数。如果字段是数值型的，就有“求和”、“平均值”、“最大值”、“最小值”、“乘积”、“数值计数”等多种汇总方式可以选择。
数据透视表工具还可以产生多层行字段或列字段的数据透视表。例如，在“职务”字段中分“职称”统计，在“性别”字段中分“学历”统计。
例1.3 在光盘文件“例1.2研究所员工资料”中，创建以“部门”为第一行字段、“职称”为第二行字段、“性别”为第一列字段、“学历”为第二列字段、“月薪”为数据区域的数据透视表。
在“部门”为行字段、“性别”为列字段的数据透视表中，用鼠标按住“职称”字段，把它从“字段列表”中拖到数据透视表中“职务”字段右边线位置，当右边线变成粗的虚线时，放开鼠标。用同样的方法，把“学历”字段拖到列字段“性别”下方的下边线位置上。将数据透视表左上角的“计数项：姓名”拖出数据透视表外，再将字段“月薪”从“字段列表”中拖进“数据项区域”，如图1.20所示。
图1.20 拖入第二层行字段和列字段
就得到两层行字段、两层列字段的数据透视表，如图1.21所示。
图1.21 两层行字段和列字段的数据透视表
右键单击“月薪”的数据区域，出现“月薪”字段的属性对话窗口，如图1.22所示。
图1.22 重新设置数据透视表的字段属性
当前“月薪”字段的“汇总方式”是“求和”，也可以将“月薪”字段重新设置为“平均值”、“最大值”或“最小值”等。
例1.4 对新推出的一款家用轿车的购车者的调查问卷如下：
您所在的城市：
□北京 □南京 □西安 □昆明 □长沙
您的性别：
□男 □女
您的年龄：
□25岁以下 □25—55岁 □55岁以上
您的年薪：
□10万元以下 □10万—20万元 □20万—30万元 □30万元以上
您对这辆车外观的评价：
□好（5分） □较好（4分） □一般（3分） □较差（2分） □差（1分）
您对这辆车动力性能的评价：
□好（5分） □较好（4分） □一般（3分） □较差（2分） □差（1分）
您对这辆车安全性能的评价：
□好（5分） □较好（4分） □一般（3分） □较差（2分） □差（1分）
对车外观评价的统计表如下：
对车动力评价的统计表如下：
对车安全评价的统计表如下：
1.3.2 模拟运算表
在Excel工作表中，经常有一些单元格的值是由其他一些单元格的值通过某些运算得到的。例如，“销售额”是“销售价格”和“销售量”相乘的结果，“平均收入”是“总收入”除以“人数”的结果。如果把结果单元格称为函数y，把运算单元格称为自变量x1, x2, x3, …则它们之间的关系可以表示为y=f（x1, x2, x3, …），其中f表示运算关系。运算关系可以是加、减、乘、除等简单运算，也可以是平方、开方、指数、对数等比较复杂的函数关系，甚至可以是投资的净现值、行列式的值、规划求解的结果等非常复杂的运算关系。
当运算关系确定以后，如果给定一个自变量单元格的值，相应的函数值就可以用模拟运算表很方便地得到。
模拟运算表分为一维表和二维表两种。先介绍一维模拟运算表的创建。
在一维模拟运算表中，参加运算的自变量可能有多个（x1, x2, x3, …），但列变量只能有一个（x1）。因此，创建一维模拟运算表需要指定以下内容：
（1）函数和自变量的运算关系。
（2）在所有自变量中，哪一个自变量的值作为列变量。
例1.5 产品的成本包括固定成本和变动成本。固定成本是与产品产量无关的成本，变动成本是与产品批量相关的成本。设某种产品的生产总成本的计算公式为：
y =cf+cvx2
式中，y为总成本，cf为固定成本，cv为单位产品的变动成本，x为产量。
将总成本y分摊到每单位产品，每单位产品分摊到的平均成本的计算公式为：
如果要观察产量x变化时平均成本{L-End} 
的相应变化，可以创建一维运算表，步骤如下：
（1）首先创建“总成本”单元格B5和“平均成本”单元格B6的计算公式，创建“模拟运算表”列变量“产量”的变化范围数据以及“平均成本”单元格G3对单元格B6的引用，如图1.23所示。见光盘文件“例1.5一维模拟运算表”。
图1.23 一维模拟运算表的数据和公式准备
（2）将“模拟运算表”的“产量”和“平均成本”两列数据范围F3:G17选定，打开Excel菜单“数据”|“模拟运算表”，出现“模拟运算表”对话窗口，如图1.24所示。
图1.24 定义一维模拟运算表的引用列单元格
（3）在“模拟运算表”对话窗口的“输入引用列的单元格”中输入自变量“产量”的单元格位置B4，单击“确定”，完成一维“模拟运算表”计算，如图1.25所示。
图1.25 生成的一维模拟运算表
由图1.25可知，当产量等于105吨时，平均成本最低，为94.87元/吨。
同样，创建二维模拟运算表时需要指定以下内容：
（1）函数和自变量的运算关系。
（2）在所有自变量中，哪一个自变量的值作为“引用行”变量，哪一个自变量的值作为“引用列”变量。
例1.6 二维“模拟运算表”的例子如下：
（1）首先创建总成本单元格B5和平均成本单元格B6的计算公式，创建“模拟运算表”列变量“固定成本”的变化范围、行变量“产量”的变化范围以及“平均成本”单元格F3对单元格B6的引用，如图1.26所示。见光盘文件“例1.6二维模拟运算表”。
图1.26 二维模拟运算表的数据和公式准备
（2）将“模拟运算表”的数据范围F3:L18选定，打开Excel菜单“数据”|“模拟运算表”，出现“模拟运算表”对话窗口，如图1.27所示。
图1.27 定义二维模拟运算表的“引用行”和“引用列”单元格
（3）在“模拟运算表”对话窗口的“输入引用行的单元格”中输入自变量“固定成本”的单元格位置B2，“输入引用列的单元格”中输入自变量“产量”的单元格位置B4，单击“确定”，完成二维“模拟运算表”计算，如图1.28所示。
图1.28 生成二维模拟运算表
在图1.28中，加框的单元格是固定成本取某一个值时，产量变化使平均成本变化的最小值。由此可以看出，平均成本及其最小值随产量和固定成本的变化情况。