数据透视表汇总

数据透视表提供了比分类汇总功能更丰富的汇总功能，满足了不同情况下的统计汇总工作，完全可以实现日常办公中常见的统计汇总工作。

数据透视表是Excel中一种交互式的汇总表，可以根据需要改变汇总指标，每一次改变行标题与列标题时，数据透视表都会立即按照新的行、列标题重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。

2.3.3.1 创建与设置数据透视表

1.在Excel中录入数据

新建一个工作表或工作簿文件，在A1：I61单元格区域中输入表2－2中的数据，然后以“宏光银行员工样本数据”为工作表或工作簿名保存数据。

2.创建数据透视表

鼠标单击出A1：I61区域内任意单元格（注：这样操作，Excel中会自动选择数据区域），单击功能区选项【插入】→【数据透视表（T）】，出现如图2－32所示的对话框。

3.确定数据透视表显示位置

单击【选择放置数据透视表的位置】下方的单选框【现有工作表（E）】，然后单击【位置（L）】右侧文本框，将光标置于其中，用鼠标单击距离数据区域较远的空白单元格，如N1单元格，单击【确定】按钮，则屏幕上出现如图2－33所示的【数据透视表字段】和【数据透视表区域】；同时，在功能区中出现了【数据透视表工具】，其包含【分析】和【设计】选项卡，这些选项卡的功能都可以通过数据透视表快捷菜单来实现。

图2－32 创建数据透视表对话框

图2－33 数据透视表

4.设置汇总表行（列）标题及汇总项

进行汇总时，单击选中【数据透视表字段】中的字段，并按住鼠标左键将其拖到数据透视表区域中的【将报表筛选字段拖至此处】、【将行字段拖至此处】、【将列字段拖至此处】、【将值字段拖至此处】位置，即可生成汇总表。

其中，【将行字段拖至此处】指汇总表的行标题所在的位置，【将列字段拖至此处】指汇总表的列标题所在的位置。

在设置行字段、列字段时，最好将“性别”、“工作岗位”、“是否为少数民族”这三个取值较少的品质标志（变量）作为行字段（行标题）、列字段（列标题），这样汇总表的行数、列数不会太多，便于比较与分析。

若以“员工编号”、“教育水平（年）”等数量标志（变量）作为行标题或列标题，则形成的组太多，不便于进行比较与分析。

【将值字段拖至此处】指用于汇总的标志（变量）所在的位置，这与【分类汇总】的【选定汇总项（）】的含义是一致的。

汇总不同“性别”、“工作岗位”、“是否为少数民族”等品质标志时，计算方式默认为【计数】，即汇总出不同组别的总体单位数。

若根据“年薪（元）”、“开始年薪（元）”、“教育水平（年）”等数量标志（变量）进行计算，计算方法默认为【求和】，其他计算方式有最大值、最小值、平均值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差。

5.值字段设置

若需要更改为其他计算方式，操作步骤如下：

①在数据透视表数据显示区域内，单击由【计数】或【求和】得出的数据。

②按鼠标右键，在弹出的快捷菜单（如图2－34所示）中选择【值字段设置】，进入【值字段设置】对话框（如图2－35所示）进行设置。

图2－34 数据透视表快捷菜单

特别注意：

①为了便于理解汇总表，一次选择一个字段作为数据项，不要同时选择两个字段作数据项。

②若原始数据中有变动，通过选择数据透视表快捷菜单中的【刷新】，就可以实现自动重新运算。

图2－35 值字段设置对话框

6.复制并粘贴汇总数据数值

为了便于利用汇总表中的指标绘制统计图，或者进行其他方面的分析，往往需要保存汇总结果，操作步骤如下：

①选中比数据透视表汇总结果表多两三行或列的单元格区域；

②按鼠标右键，在弹出的快捷菜单中选择【复制（）】；

③用鼠标单击其他工作表，单击一个空白单元格，按鼠标右键，在弹出的快捷菜单中选择【选择性粘贴（）…】，在弹出的【选择性粘贴（）】菜单中选择【数值（）】。

7.重新设置或清除数据透视表

若需要汇总不同的其他数据，只需要将【数据透视表区域】原有的字段移回【数据透视表字段列表】，即可回到数据透视表初始状态（如图2－33所示）。

若要清除数据透视表，选中比数据透视表汇总结果表多两三行或列的单元格区域，然后按鼠标右键，在弹出的快捷菜单中选择【删除（）…】。

注意：

在图2－33中，应当掌握以下几点。

①数据透视表“字段”的含义与统计中的“标志”、“变量”意义相同。页字段、行字段、列字段最好放置取值比较少的标志；“数据项”与分类汇总中的“汇总项”意义相同。

②【将报表筛选字段拖至此处】与【数据透视表字段】中的【筛选器】作用相同。

③【将列字段拖至此处】与【数据透视表字段】中的【列】作用相同，都是表示表格的列标题。

④【将行字段拖至此处】与【数据透视表字段】中的【行】作用相同，都是表示表格的行标题。

⑤【将值字段拖至此处】与【数据透视表字段】中的【∑值】作用相同，都是根据拖动相应位置的变量进行汇总。

2.3.3.2 数据透视表汇总应用实例

以表2－7中的数据为例，说明Excel数据表透视在统计汇总中的基本应用。

表2－7 宏光银行员工样本数据

续表

续表

1.简单分组表的汇总

表2－8、表2－9、表2－10均为简单分组表，表2－10是由表2－8和表2－9合并得到的。以表2－10为例，说明如何通过数据透视表得出简单分组表。

表2－8 不同工作岗位人数

表2－9 不同工作岗位年薪合计额

表2－10 不同工作岗位人数及年薪合计额

例2.4 根据表2－2中的数据，汇总出表2－10中的数据。

（1）在Excel中录入数据

新建一个工作表或工作簿文件，在A1：I61单元格区域中输入表2－7中的数据，以“宏光银行员工样本数据”为工作表或工作簿名，然后保存数据。

（2）创建数据透视表

鼠标单击A1：I61区域内任意单元格（注：这样操作，软件会自动选择数据区域），单击【插入】→【数据透视表（）】，出现如图2－32所示对话框。

（3）确定数据透视表显示位置

单击【选择放置数据透视表的位置】下方的单选框【现有工作表（）】，然后单击【位置（）】右侧文本框，将光标置于其中，用鼠标单击距离数据区域较远的N3空白单元格。

（4）设置简单分组表行（列）标题

单击【数据透视表字段】中的“工作岗位”，并拖至【将行字段拖至此处】位置（或【数据透视表字段】中的【行】下方的文本框），然后松开鼠标左键。

（5）选择简单分组表汇总项

单击【数据透视表字段】中的“年薪（元）”，并拖至【将值字段拖至此处】位置（或【数据透视表字段】中的【∑值】下方的文本框），然后松开鼠标左键，就会出现如图2－36所示的汇总结果。

图2－36 简单分组表——求和

（6）复制汇总数据

选择N2：O10单元格区域，右击，在弹出的快捷菜单中选择【复制（C）】。

（7）选择性粘贴汇总数据

单击另外一张空白工作表，单击某一空白单元格，假设为B2单元格，按鼠标右键，在弹出的【选择性粘贴（V）…】菜单中选择【粘贴数值】中的【值（）】，然后改成表2－3的样式，并将该工作表命名为“简单分组汇总结果”。

（8）通过【值字段设置（E）…】，汇总出表2－10中的数据

单击“1212750、86550、895700、2195000”中的任意一个数值，然后右击，在弹出的快捷菜单中选择【值字段设置（E）…】，则弹出如图2－35所示的对话框，选择【计算类型】下方的【计数】，然后单击【确定】按钮，则O7：O10显示成“44、3、13、60”，得到表2－10中的数据（注：若需要用百分比样式显示汇总数据，单击【值显示方式】，根据需要在其下拉列表中选择），如图2－37所示。

（9）将表2－8和表2－9合并为表2－10

选择O6：O10区域，复制“汇总、44、3、13、60”，单击“汇总数据”工作表中的D2单元格，右击，在弹出的快捷菜单中选择【插入复制单元格（E）…】，选择【活动单元格右移】，单击【确定】按钮，把“汇总”改成“人数（人）”，得出表2－10。

图2－37 简单分组表——计数

2.复合分组表汇总

表2－11～表2－13都是复合分组表，表2－13由表2－12和表2－11合并而成。以表2－12为例，说明如何通过数据透视表得出复合分组表。

表2－11 不同工作岗位按是否为少数民族统计年薪合计　单位：元

表2－12 不同工作岗位按是否为少数民族统计人数　单位：人

表2－13 不同工作岗位按是否为少数民族统计人数及年薪合计额

例2.5 根据表2－7数据，汇总出表2－11～表2－13中的数据。

（1）在Excel中录入数据

新建一个工作表或工作簿文件，在A1：I61单元格区域中输入表2－7中的数据，以“宏光银行员工样本数据”为工作表或工作簿名，然后保存数据。

（2）创建数据透视表

单击A1：I61区域内任意单元格（注：这样操作，软件会自动选择数据区域），单击功能区选项【插入】→【数据透视表（T）】，出现如图2－32所示对话框。

（3）确定数据透视表显示位置

单击【选择放置数据透视表的位置】下方的单选框【现有工作表（E）】，然后单击【位置（L）】右侧文本框，将光标置于其中，单击距离数据区域较远的N3空白单元格。

（4）选择复合分组的第一个分组标志（变量）

单击【数据透视表字段】中的“工作岗位”，并移至【将行字段拖至此处】位置（或【数据透视表字段】中的【行】下方的文本框），然后松开鼠标左键。

（5）设置复合分组表汇总项

单击【数据透视表字段】中的“年薪（元）”，并移至【将值字段拖至此处】位置（或【数据透视表字段】中的【∑值】下方的文本框中），然后松开鼠标左键，就会出现如图2－36所示的汇总结果。

（6）选择复合分组的第二个分组标志（变量）

第一种方法：单击【数据透视表字段】中的“是否为少数民族”，并移至【将列字段拖至此处】位置（或【数据透视表字段】中的【列】下方的文本框），然后松开鼠标左键，就会出现如图2－38所示的复合分组表2－11（1）汇总结果。

第二种方法：单击【数据透视表字段】中的“是否为少数民族”，并移至“工作岗位、办事员、保管员、经理、总计”左侧，此时会出现一个绿色的粗线条的“I”，然后，松开鼠标左键，就会出现如图2－38所示的复合分组表2－11（2）汇总结果。

（7）复制汇总数据

从N2单元格开始，用鼠标选择复合分组表行列标题及汇总结果所在单元格区域，右击，在弹出的快捷菜单中选择【复制（C）】。

（8）选择粘贴汇总数据

新建工作表，并命名为“复合分组汇总结果”，单击“复合分组汇总结果”工作表，单击某一空白单元格，假设为H2单元格，右击，在弹出的【选择性粘贴（）…】对话框中，选择【粘贴数值（）】，然后改成表2－11的样式。

（9）通过【值字段设置（）…】汇总出表2－12

单击“宏光银行员工样本数据”工作表，然后单击数据透视表中的任意一个汇总数值，假设单击选中了“861900”所在单元格，右击，在弹出的快捷菜单中选择【值字段设置（）…】，则弹出【值字段设置对话框】，选择【计数】，单击【确定】按钮（注：若需要用百分比样式显示汇总数据，单击【值显示方式】，根据需要在其下拉列表选择），则图2－38中的数据会变为如图2－39所示。

图2－38 复合分组表——求和

（10）复制与粘贴汇总数据数值

从N2单元格开始，选择数透视表所在单元格区域，右击，在弹出的快捷菜单中选择【复制（）】，右击，在弹出的【选择性粘贴（）…】对话框中选择【粘贴数值】下的【值（）】，然后再改成表2－12样式。

（11）用表2－11、表2－12合并成表2－13

图2－39 复合分组表——计数