125
数据透视表和透视图CHAPTER7
·
第7章数据透视表和透视图
数据透视表是一种交互式的报表方式，它具备极强的分析能力，可
以帮助用户分析、组织数据，利用它可以从不同的角度对数据进行分析汇
总（小计），尤其对记录多、结构复杂的工作表更为方便。如果要分析相
关的汇总值，尤其是要合计较大的数字列表对每个数字进行多种比较的时
候，通常使用数据透视图。
使用数据透视图可以汇总、分析、浏览和提供摘要数据。使用数据
透视图可以可视化数据透视图中的摘要数据，并且可以方便地查看比较结
果、模式和趋势。
本章要点：1.数据透视表
2.数据透视表图
销售业绩透视表效果图
销售报表透视图
将单元格格式自定义0;-0;;@，或者依次进入Excel选项→高级→勾选“在具有零值的单元格中显示零”。
126
市场与营销管理必会Excel应用100例办公室之
·
7.1
创建销售业绩数据透视表
总结销售业绩时，由于记录众多、结构复杂，采用分类汇总、合并计算都不能动态
查看到数据，这时就要用到数据透视表。
数据透视表是一种可以快速汇总大量数据的交互式方法，使用数据透视表可以深
入分析数值并且可以回答一些预计不到的数据问题。数据透视是针对下面用途特别设
计的。
（1）以多种用户方式查询大量数据。
（2）对数值数据进行分类汇总和合并，按分类和子分类对数据进行汇总，创建自定
义计算和公式。
（3）展开或折叠要关注结果的数据级别，查看感兴趣的区域摘要数据的明细。
（4）将行移动到列或将列移到行或“透视”以查看源数据的不同汇总。
（5）对最有用和最关注的数据子集进行筛选排序，分组和有条件地设置格式，使用
户能够关注所需的信息。
（6）提供简明，有吸引力并且带有批注的联机报表或打印报表。
（7）在数据透视表中，源数据中的每一个列或每一个字段都能成为汇总多行信息的
数据透视表字段。
步骤01新建工作表
新建工作表录入产品订单信息，计算出各类产品、各客户的订购情况。打开EXCEL
程序，新建的工作簿并将文件名命名为“销售业绩数据透视表”。并录入如图数据。
如何将多个工作表的单元格合并计算？
127
数据透视表和透视图CHAPTER7
·
步骤02输入公式计算
在H2单元格中输入公式“=F2G2C2”计算每笔订单的金额，并利用拖动填充柄的
办法填充H列的其他单元格。
步骤03插入数据透视图
打开“插入”选项卡，单击数据透视图，在下拉菜单中选择“数据透视表”，会弹
出创建“数据透视表”窗口，并在“选择一个表或区域”中输入刚刚创建的表的范围，
单击确定插入。
步骤04设置字段
在“数据透视表字段列表”任务窗格中拖拽字段至相应的区域，在字段设置选项
下，可以看到“订单号”、“订购单位”、“折扣”、“产品”、“产品名”、“产品
采用公式=Sheet1!D4+Sheet2!D4+Sheet3!D4；或者=SUM(Sheet1:Sheet3!D4)
128
市场与营销管理必会Excel应用100例办公室之
·
编号”、“订购数量”、“合计”等多个选项，具体设置根据使用者要求来设定。
步骤05生成报表
例如在本地中选择“订购单位”、“产品名”、“合计”字段添加到报表，勾选这3
个字段后，将会自动生成报表。
步骤06改变数据透视表字段
完成数据透视表的创建后，用户可以真接在工作表中使
用数据透视表字段列表任务窗格来规划透视表，操作步骤如
下：在数据透视表字段列表任务窗格移动字段，例如将“订
购单位”拖动到列标签、将“产品名”拖动到行标签，将得
到如下透视表。
如何获取一个月的最大天数？
129
数据透视表和透视图CHAPTER7
·
步骤07设置排序
打开“数据透视表工具”的“选项”选项卡，选择数列中有数据的任一单元格，例
如总计单元格，单击“排序”组中的降序排列，将按产品订单总额降序排列。
7.2
创建销售业绩数据透视图
数据透视图以图形形式表示数据透视表中的数据，正如在数据透视表中那样，可以
更改数据透视图的布局和数据。数据透视图通常有一个使用相应布局的相关联的数据透
视表。两个报表中的字段相互对应。如果改变了某一报表某个字段的位置，则另一报表
中的相应字段的位置也会改变。
:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01
130
市场与营销管理必会Excel应用100例办公室之
·
7.2.1
了解数据透视表字段列表
报表筛选字段
用来根据特定项筛选“数据”的字段。
使用报表演筛选字段是在不修改系列和分类信息情况下，汇总并快速集中处量数据
子集的捷径。另外，由于只能允许每次检索大数据集中的一个报表筛选页，因此在图表
使用外部源数据时，报表筛选字段可节省内存。
值字段
来自基本源数据的字段，提供进行比较或计算的数据。根据报表使用的源数据，除
了可以使用汇总函数外还可使用Average、Count和Product等计算公式。
系列字段
数据透视图中为系列方向指定的字段，字段中的项提供单个数据系列，在图表中，
系列由图例表示。
项
项代表一个列或行字段中的唯一条目，且出现在报表筛选字段、分类字段和系列字
段的下拉列表中。分类字段中的项在图表的分类轴上显示为标签，系列字段的中项列于
图例中，并提供各个数据系列的名称。
分类字段
分配到数据透视图分类方向上的源数据中的字段，分类字段为那些用来绘图的数据
点提供一分类。
自定义报表
可以更改图表类型和其他选项，如标题、图例位置、数据标签、图表位置等。
7.2.2
具体步骤
步骤01创建数据透视图
首次创建数据透视表时，可以自动创建数据透视
图，也可以根据现有的数据透视表创建数据透视图。
下面我们直接打开前面创建的工作表，打开“数据透
如何快速定位到单元格？
131
数据透视表和透视图CHAPTER7
·
视表工具”的“选项”选项卡，再单击“工具”组中的“数据透视图”按钮，打开插入
图表窗口。
步骤02选择数据透视图
选择“簇状柱型图”，单击确定即可。
步骤03直接创建数据透视图
在对图表使用十分熟的情况下，还可以直接创建数据透视图，打开上一节的产品订
单工作表。选择“插入”选项卡，单击“表”组中的“数据透视表”下拉箭头，在其下
拉列表中选择“数据透视图”选项，即可打开“创建数据透视表及数据透视图”对话
框，选择表格区域范围，单击确定。
在“数据透视表字段列表”任务窗格中拖动字段到相应的区域。
两种方法：按F5键，出现“定位”对话框，在引用栏中输入欲跳到的单元格地址，单击“确定”按钮即可；
或者单击编辑栏左侧单元格地址框，输入单元格地址即可。
132
市场与营销管理必会Excel应用100例办公室之
·
即可得到新的数据透视图。
步骤04修改数据透视图字段
完成数据透视图的创建后，用户可以直接在工作表中
使用“数据透视表字段列表任务”窗格来规划数据透视
图，可以改变其布局等。
布局对数据透视图的设置非常重要，它可以修改图表
的不同类型，以求达到最理想的效果。
首先改动字段，可以在数据透视表字段列表，任务窗
格中移动字段。
步骤05更改图标类型
接下来打开“图表工具”的“设计”选项卡，在“类型”组中单击“更改图表类
型”按钮，选择“堆积柱形图”单击“确定”按钮更新图表类型。
“Ctrl＋”的特殊功用
133
数据透视表和透视图CHAPTER7
·
7.3
用产品订单数据透视图分析数据
在“数据透视图工具”中包含一个“分析”选项卡，利用它可以对数据透视图进行
数据分析。
步骤01打开工作表
打开前面介绍的工作表。激活数据透视图，再打开“分析”选项卡。
步骤02选择分析项
在图例区中单击“遥控四驱车”。
步骤03查看选择项
此时“活动字段”组中出现“产品名”活动字段，单击“活动字段”组中的“展开
整个字段”按钮，打开“显示明细数据”对话框。
一般来说，当处理一个工作表中有很多数据的表格时，通过选定表格中某个单元格，然后按下Ctrl＋键可选定
整个表格。Ct?＋选定的区域是这样决定的：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。
134
市场与营销管理必会Excel应用100例办公室之
·
步骤04显示明细数据
选择“订购单位”，单击“确定”按钮，即可显示明细数据。
7.4
制作销售报表透视图
对销售数据行处理是企业管理工作中的一个重要环节。通过分析销售报表企业决策
者可以获得部门、时期、业务、产品的销售等信息，并将其作为考核、调整的依据。
步骤01新建工作表
打开EXCEL程序，新建的工作簿并将文件名命名为“销售报表透视图”。然后按照
下图录入数据。
如何快速选取工作表中所有包含公式的单元格？
135
数据透视表和透视图CHAPTER7
·
步骤02输入公式计算
在F2单元格中输入公式“D2E2”，并真充F列的其他单元格。
步骤03创建数据透视图
打开插入选项卡，选择数据透视表，单击“表”组中“数据透视表”下拉箭头，选
择创建数据透视图。
步骤04选择表格范围
在“创建数据透视表及数据透视图”中选择表格的范围，并选择在新工作表中放置
数据透视表及数据透视图，单击确定完成创建。
步骤05修改字段
在“数据透视表字段列表”任务窗格中拖动字段，选择需要的字段，并将透视图拖
到合适的位置。
单击“定位条件”按钮，在“定位条件”对话框中选择“公式”项，按“确定”按钮即可。
136
市场与营销管理必会Excel应用100例办公室之
·
步骤06修改业务员姓名
单击“业务员姓名”后的下拉箭头，选中选择“多项”复选框，选择“冯小丽”、
“刘力均”两个业务员，在单击确定按钮。数据透视表会随之而变。
步骤07修改排序
打开“选项”选项卡，在F列的单元格中单击鼠标，再单元“排序”组中按的降序排
序按钮，按总金额降序排列。
如何在不同单元格中快速输入同一数内容？
137
数据透视表和透视图CHAPTER7
·
步骤08修改样式
打开“设计”选项卡在“数据透视表样式”组中选择“数据透视表浅色15”样式。
步骤09设置镶边行
在“数据透视表样式选项”组中选中“镶边列”复选框。
步骤10更改透视图
接下来选中激活数据透视图，再单击“更改图表型”组
中“更改图表类型”按钮，选择“堆积柱形图”单击“确
定”按钮，图表显示如图所示。通过以上的一些调整，即可
设计出自己需要的各种透视图、表格来。
选定单元格区域，输入值，然后按Ctrl+Ener键，即可实现在选定的单元格区域中一次性输入相同的值。
138
市场与营销管理必会Excel应用100例办公室之
·
|
|
