如何使用Excel数据透视表？

Excel数据透视表是交互式报表，可快速地对大量数据进行合计、计数、乘积、平均数、方差等运算。会计核算中需要编制科目汇总表时，可以使用数据透视表。之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。

创建数据透视表，必须要有数据源，它可以来自单一的工作表，也可来自多个工作表或外部数据源等。创建数据透视表，必须要有字段，即列标题，可以是同一张工作表的单个字段或多个字段，也可以是不同工作表的同一个字段。创建数据透视表的目的是对相关重复数据进行汇总反映，所以，作为数据源的各工作表中的记录（行），其数据结构应当相同。

创建数据透视表，可以通过“向导式”工作方法完成；也可直接创建空白数据透视表，通过拖动字段的方式完成。建立数据透视表后，Excel将自动给出数据透视表工具栏，利用此工具栏，可对数据透视表中的数据进行增加、显示、隐藏、删除等的编辑。

在Excel中应用数据透视表功能，编制星科制造公司2022年1月份总账（一级）科目汇总表（该公司的会计科目表、年初余额表与会计分录表见学习情境7）。

在Excel中用数据透视表建立的总账科目汇总表（单字段）如图8-1所示。

图8-1　科目汇总表（Excel 2019数据透视设计选项卡）

（1）在Excel 2000—Excel 2003中插入空白数据透视表，方法如下：

a.打开“Excel财务会计”工作簿，选定“会计分录”表。

b.选定该表的A1至J112有数据的单元区域。

c.选择“数据/数据透视表和数据透视图”菜单命令进入向导对话框，如图8-2所示。

图8-2　Excel 2003数据透视表向导

d.选择数据源类型为“Excel数据列表”，创建报表类型为“数据透视表”；单击“完成”按钮，Excel将自动新建一张工作表，并在该表中显示创建的空白数据透视表，如图8-3所示；双击该表标签，将其标签名称修改为“单字段透视”。

图8-3　数据透视表及其工具与字段列表（Excel 2003）

（2）空白数据透视表与工具简介。插入的空白数据透视表有页字段、列字段（上部）、行字段（左部）、数据项（右下部）四个部分，如图8-3的A1至G16单元区域所示。

单击空白数据透视表，在Excel XP—Excel 2003中将显示数据透视表工具栏（如图8-3中部）、字段列表对话框（如图8-3右部）；其中，单击工具栏的“数据透视表（P）”将弹出有设置报告格式、顺序、表选项等命令的下拉菜单。在Excel 2000中，将显示如图8-4左下部所示的数据透视表工具栏，单击该工具栏的“显示域”按钮，工具栏下部将显示字段列表。

若单击空白数据透视表，上述工具栏、字段列表没有显示，可右击空白数据透视表，从快捷菜单中将工具栏、字段列表显示出来；也可通过“视图/工具栏/数据透视表”菜单命令显示。

类似的操作可隐藏数据透视表工具栏、字段列表。

（3）将字段拖入透视表。Excel 2000—Excel 2003中工作方法如下：

a.拖动页字段。从字段列表中选定“年”，拖至透视表上部的页字段处，然后释放鼠标；再选定“月”拖至“年”的下方，当出现虚线的倒“工”字状时，释放鼠标。

b.拖动行字段。从字段列表中拖动“一级科目”至透视表的左部行字段处。

c.拖动数据项。从字段列表中拖动“借方发生额”至透视表右部的数据项处，释放鼠标后，透视表的“一级科目”后部将出现“汇总”字样的单元格；然后从字段列表中拖动“贷方发生额”至“汇总”单元格的下一个单元格上，再释放鼠标。

d.字段拖动后释放的位置不当，可能形成难以理解的数据汇总项。若需修改字段的位置，可选定透视表中的该字段将其拖回“字段列表”中（也可以取消字段列表中该字段前的“√”标记），然后重新拖动至透视表中。

e.拖动后，透视表如图8-4所示。从该表可见，其汇总方式是“计数”而不是“求和”；同时，一个科目以两行反映借方与贷方金额，所以应修改。

图8-4　拖动后的数据透视表（Excel 2000）

（4）修改透视表，方法如下：

a.右击“计数项”（即B5单元格），在快捷菜单中选择“字段设置”（或单击B5单元格并选择数据透视工具栏下拉菜单的“字段设置”）命令，进入“数据透视表字段”对话框，如图8-4右部所示。选择“求和”并单击“确定”按钮。

b.类似地，将B6单元格的“计数项”修改为“求和”。

c.选定“数据”项（B4单元格），将其拖至“汇总”项（C4单元格）上，以使借方发生额、贷方发生额分两列反映。

（5）生成汇总表。Excel 2000—Excel 2003通过年、月后部单元格的下拉箭头，选择相应的日期；再进行格式设置，完成一级科目汇总表的编制。

Excel 2007—Excel 2019插入的空白透视表如图8-5所示，其工作方法如下。

图8-5　Excel 2007插入的空白透视表及字段列表

（1）选定会计分录表中有数据的A1至J112单元区域，通过“插入/数据透视表”命令进入“创建数据透视表”对话框；单击“确定”按钮，可创建空白的透视表（新工作表）。将其表标签的名称修改为“单字段透视”。

单击空白透视表，工作表中将显现“数据透视字段列表”；同时，Excel标题栏将显示“数据透视表工具”，其中Excel 2007、Excel 2010有选项、设计两个选项卡，Excel 2013—Excel 2019有分析、设计两个选项卡。

（2）将字段列表中的“年、月”分别拖至的“报表筛选”框（Excel 2019为“筛选”、Excel 2013—Excel 2016为“筛选器”、Excel 2007—Excel 2010为“报表筛选”）；

将“一级科目”拖至的“行标签”框（Excel 2013—Excel2019为“行”、Excel 2007—Excel 2010为“行标签”）；

将“借方发生额、贷方发生额”分别拖至“数值”框（Excel 2013—Excel 2019为“值”、Excel 2007—Excel 2010为“数值”）。

此时，字段列表将显示如图8-6右部所示。Excel 2007—Excel 2016的数据透视表将显示如图6左部所示的“计数项”，Excel 2019的数据透视表将显示“求和项”。

图8-6　计数项透视表（Excel 2016数据透视分析选项卡）

（3）Excel 2007—Excel 2016在数据透视表的“计数项”（即B4或C4单元格）上右击弹出快捷菜单；选择“值字段设置”，将其汇总方式改为“求和”。

（4）对数据透视表进行其他格式的设置。如，通过“数据透视表/选项（分析）”选项卡，取消各项目前“＋”的显示（Excel 2016—Excel 2019不用取消）；设置字体字号、千位分隔、行高列宽；选择B1、B2单元格的年月值等。

在Excel 97及其以前版本中，插入与编辑数据透视表的工作方法如下。

（1）选定会计分录表的A1至J112单元格区域，通过“数据/数据透视表”菜单命令进入“数据透视表指南（向导）4步骤”界面，单击“完成”按钮。再将自动新建的工作表标签的名称修改为“单字段透视”。

（2）插入空白数据透视表后，将自动显示A1至B2单元区域的文字以及“数据透视表”工具栏；若该工具栏没有显示，可通过“视图/工具栏/数据透视表”菜单命令显示；单击A1至B2单元格任意位置，单击透视表工具栏的“透视表向导”按钮，将弹出“数据透视表指南-步骤4之3”（或“数据透视表向导-步骤4之3”）对话框，如图8-7中部所示。

（3）将数据透视表指南（向导）界面右部的“年”字段，拖至左部的“分页”或“页”框之中，然后释放鼠标。再拖动右部的“月”字段，至左部的“年”的下方，当出现虚线的倒“工”字时，释放鼠标。

图8-7　数据透视表指南（Excel 5.0）

将指南（向导）界面右部的“一级科目”字段，拖至左部的“行”框之中。

将指南（向导）界面右部的“借方发生额、贷方发生额”2个字段，分别拖至中部的“数据”框之中。

单击指南（向导）界面的“完成”按钮，将显示“计数项”的数据透视表。

（4）在Excel 97及其以前版本中，拖动数据透视表B4单元格的“数据”项至C4单元格的“汇总（分类汇总）”项上。右击B5单元格的“计数项”选择“数据透视表字段”命令，将其汇总方式修改为“求和”。选定C5单元格后单击透视表工具栏“数据透视表字段”按钮，将其“计数项”修改为“求和”。拖动选定B、C两列，单击格式工具栏“千分撇”按钮。再通过B1、B2单元格的下拉列表，选择相应的年、月值。

在Excel中使用数据透视表，编制星科制造公司2022年1月份有一级科目、二级科目与三级科目3个字段的科目汇总表（图8-8）。(www.xing528.com)

在Excel中用数据透视表建立的3个字段的科目汇总表如图8-8所示。

图8-8　3个字段科目汇总表（Excel 2010数据透视选项卡）

（1）选择“会计分录”表A1至J112单元区域，插入空白数据透视表。将其表标签修改为“多字段透视”。

（2）Excel XP—Excel 2003中，通过“添加到”按钮设置字段，方法如下：

a.选择字段列表中的“年”，在下部的下拉框中选“页面区域”并单击“添加到”按钮。

选择字段列表中的“月”，在下部的下拉框中选“页面区域”并单击“添加到”按钮。

b.选择字段列表的“一级科目”，在下部的下拉框中选“行区域”，单击“添加到”按钮。

类似地，将“二级科目、三级科目”字段添加到“行区域”。

c.选择字段列表的“借方发生额”，在下部的下拉框中选“数据区域”，单击“添加到”按钮。

类似地，选择字段列表中的“贷方发生额”，添加到“数据区域”。

（3）Excel 2000及其以前版本中，没有单独的“数据透视表字段列表”对话框，无法使用“添加到”功能，只能在如图8-4、图8-7所示的对话框中进行字段的拖动。

Excel 2003及其以前版本中，字段拖动完成后的数据透视表，将显示如图8-9所示。

图8-9　待修改3字段数据透视表（Excel 97）

（4）透视表比较。将图8-8与图8-9比较可见，每个科目（包括明细科目）有多个汇总数据；同时，因为有明细科目汇总，有的总账科目还要分别显示各明细科目的汇总数，使得同一总账科目也占据多行并显示；该表是计数而不是求和；借、贷方发生额分两行而不是两列显示等。因此不便于阅读、理解，所以应修改显示方式。

（5）修改“计数项”为“求和项”（右击选择“字段设置”）。

（6）将“数据”项（D4单元格）拖动至“汇总”项（E4单元格）上。

（7）任意选定有“汇总”字样的单元格，在Excel XP—Excel 2003中，从“数据透视表工具栏”的下拉菜单中，去掉“分类汇总”前的“√”，该汇总行将不会显示。在Excel 2000及其以前版本中，右击有“汇总”字样的单元格选择“字段（字段设置）”命令，再选择分类汇总为“无”，单击“确定”按钮。

（8）选定透视表的任意单元格，右击选择“表格选项”（或数据透视表工具栏下拉菜单的“表选项”）命令；在弹出的对话框中勾选“合并标志”。选定显示内容为“（空白）”字样的单元格，在编辑框中将其修改为“-”并回车。

本例在Excel 2007—Excel 2019中的操作方法是：

（1）选定会计分录表的A1至J112单元格区域，创建空白透视表，将同时显示“数据透视字段列表”。修改表标签为“多字段透视”。

（2）将“数据透视字段列表”的“年、月”拖至“报表筛选”或“筛选器”框中；将“一级科目、二级科目、三级科目”依序拖至“行标签”或“行”框中；将“借方发生额、贷方发生额”依序拖至“数值”或“值”框中。

此时，数据透视表将显示如图8-10左部所示。

图8-10　待修改透视表（Excel 2019数据透视分析选项卡）

（3）在Excel 2007—Excel 2016中，右击“计数项”，在弹出的快捷菜单选择“值字段设置”，将其汇总方式改为“求和”。Excel 2019自动生成求和项，不必修改。

（4）分别在表内某行的一级科目、二级科目、三级科目上右击，选择并进入“字段设置”对话框；在“分类汇总和筛选”卡片中，选择分类汇总为“无”，如图8-10右部所示；在“布局和打印”卡片中，将原有的“以大纲形式显示项目标签”改为“以表格形式显示项目标签”。

（5）双击“行标签”，将其修改为“一级科目”。

（6）选定显示内容为“（空白）”字样的单元格，在编辑框中将其修改为“-”并回车。

以上介绍的是新建数据透视表的方法。若需要将数据透视表显示在现有工作表中（嵌套透视表），应按以下方法设置。

（1）Excel 2003及其以前版本将透视表插入当前工作表的方法：在透视表向导中，连续两次单击“下一步”按钮，并选择数据透视表显示在现有工作表，指定显示的起始单元格，单击“完成”按钮。

（2）Excel 2007—Excel 2019直接在“创建数据透视表”对话框中，选择数据透视表显示在现有工作表，再指定显示的起始单元格。

根据星科制造公司年初余额表的年初借方余额、年初贷方余额，会计分录表的借方发生额、贷方发生额这4个字段的值，编制2022年1月的科目汇总表。

根据该公司两个工作表、4个字段的值编制的科目汇总表如图8-11所示。

图8-11　数据透视表源自多表（Excel 2013数据视透表分析选项卡）

（1）调用数据透视表向导。Excel 2003及其以前版本的数据透视表向导为“数据/数据透视表和数据透视图”命令，可以直接使用。

Excel 2007—Excel 2019调用方法：选择“文件/选项”（或“Office按钮/Excel选项”）命令进入“Excel选项”对话框；Excel 2010—Excel 2019选择左部的“快速访问工具栏”，Excel 2007选择左部的“自定义”项；选择中部的“不在功能区的命令”，找到“数据透视表向导”项，单击“添加”按钮；再单击Excel选项对话框的“确定”按钮后，快速访问工具栏将显示数据透视表向导按钮。

（2）插入多表来源的数据透视表，方法如下：

a.Excel 2003及其以前版本选择“数据/数据透视表和数据透视图”菜单命令，Excel 2007—Excel 2019单击快速访问工具栏的“数据透视表向导按钮”，进入向导（指南）对话框。

b.在向导对话框选择“多重合并计算数据区域”或“多重汇总数据区域”项，单击“下一步”按钮。

c.选择“创建单页字段”项，单击“下一步”按钮，进入向导步骤2b对话框，如图8-12所示。

图8-12　选择多表数据区域

d.单击选定区域下部的录入框，选定“年初余额”表的B1至F39单元区域，单击“添加”按钮；再选定“会计分录”表的F1至J112单元区域，单击“添加”按钮。

e.按默认设置两次单击“下一步”按钮，选择“新工作表”项；单击“完成”按钮，工作簿中将新建1张工作表，将其表标签修改为“多表透视”。

此时，数据透视表将显示如图8-13所示。

图8-13　插入源自多表的数据透视表

（3）修改为科目汇总表，方法如下：

a.在Excel XP—Excel 2019中，单击B3单元格的“列”（或“列标签”）下拉箭头，取消“二级科目、三级科目”项。在Excel 2000及其以前版本中，右击C4单元格选择“删除”命令，再右击G4单元格选择“删除”命令。

b.在Excel XP—Excel 2019中，右击“列”或“列标签”（B3单元格），选择“数据透视表选项”命令，在“汇总和筛选”卡片中取消“显示行总计”。在Excel 2000及其以前版本中，右击B3单元格选择“选项”命令，在格式选项中取消“行总计”项，单击“确定”按钮。

c.在Excel XP—Excel 2019中，在A3单元格的“计数项：值”上右击，选择汇总依据为“求和”。

d.选定“年初借方余额”单元格，鼠标指针指向该单元格的边框线上再按下左键将其拖动到A4与B4单元格间的竖线处（有虚线提示）并释放鼠标；类似地，调整其他各字段的顺序，调整B4至E4各单元格的文字顺序。

e.在Excel XP—Excel 2019中，单击A4单元格的“行”（或“行标签”）筛选箭头，从下拉列表中选择“降序”选项。在Excel 2000及其以前版本中，点击A4单元格，单击常用工具栏的“降序”按钮。