数据透视表常见问题诊断&解答

mn860429 · 发表于 2010-12-12 10:26

一、我总是遇到错误"数据透视表"字段名无效”

问题：
创建数据透视表时，出现以下错误信息：数据透视表字段名无效。在创建透视表时，必须使用组合为带有标志列列表的数据．如果要更改数据透视表字段的名称，必须健入字段的新名称。
字段名无效.jpg

解决方法：
该信息表示数据源中的一个或多个列没有标题名称。要修正该问题，找到用来创建数据透视表的数据集，确保所有的列都有一个标题名。

实例文件：

字段名无效.rar (3.34 KB, 下载次数: 918)

=============================================================================================

二、我的数据透视表总是使用“计数”而不使用“求和”
问题：
在数据源中有一个包含数字的列。并且格式为真正的数字,可以求和。但是，在每次试图将其添加到数据透视表时，Excel总是会自动对字段使用“计数”，而不是“求和”。因此只能手动将计算方法更改为“求和”。
计数vs求和.jpg

解决方法：
如果在源数据列存在任何文本值，Excel都会自动对该列的数据字段应用“计数”。类似的，即使有一个空单元格也会导致Excel应用“计数”。很有可能在源数据列中包含文本值或空白单元格。要解决该问题，只需从源数据列中删除文本值或空白单元格，然后刷新数据透视表。

实例文件：

计数vs求和.rar (4.13 KB, 下载次数: 504)

=============================================================================================

三、透视表的列宽无法保留，我的数据透视表总是将工作薄中的列调整为最适合标题的列宽
问题：
刷新数据透视表或者选择刷选字段中的一个新项时，包含标题的列自动调整为列宽适合列标题。这在不希望更改已经做好的报表格式时会很麻烦。
保留列宽.jpg

解决方法：
使用一个‘数据透视表选项”可以很容易地解决此问题。
1．右键单击数据透视表，选择“数据透视表选项”。
2．在出现的对话框中，选择“布局和格式”选项卡。
3．取消选中“更新时自动调整列宽”。

实例文件：

保留列宽.rar (32.67 KB, 下载次数: 334)

=============================================================================================

四、字段分组时得到一个错误消息
问题：
试图在数据透视表中给字段分组时，出现以下错误消息:“选定区域不能分组”。
组合.jpg

解决方法：
以下情况之一将触发该错误消息：

试图分组的字段是一个文本字段．
试图分组的字段是一个数据字段，但它包含文本
试图分组的字段是一个数据字段，但Excel将其识别为文本．
试图分组的字段是数据透视表的报表筛选区域。

采取下列步骤可以解决该问题

查找源数据,确保试图分组的字段是数据格式，并且不包含文本。删除所有的文本，将单元格的格式设置为数据格式，使用 0 填充所有空单元格。
选中数据源中试图分组的字段的那个列。在功能区选择“数据”选项卡，然后选择“分列”。这将激活‘文本分列向导”。将数据更新为正确的形式（数字或者日期）。回到数据透视表．右键单击，选择“刷新”。
如果试图分组的字段在数据透视表的筛选字段中，可将该字段移动到行字段或列字段。现在可以对字段中的数据项进行分组了。在分组字段以后，可以将其移回筛选区域

实例文件：

组合.rar (4.9 KB, 下载次数: 397)

=============================================================================================

五、我的透视表将同一个数据项显示两次

问题：
数据透视表将同一个数据项显示两次，并将每个数据项当作一个单独的实体。比如实例中的甲出现了两次，这是不对的，如果这两个数据相同，那么他们应该合并为一个结果，而不是两个。
重复的项.jpg

解决方法：
大多数情况下是数据输入的不规范，查看数据源是否有不可见的字符或者空格，或者数据类型不统一的问题（文本型的数字或日期）。

实例文件：

重复的项目.rar (4.12 KB, 下载次数: 379)

=============================================================================================

六、删除的数据项仍然显示在筛选区域中

问题：
从源数据删除了一个数据项，并刷新了数据透视表。但是该数据项仍然在数据透视表中显示，数据透视表还是在其透视表缓冲中保存该数据项。
删除的项目.jpg

解决方法：
设计上Excel在筛选字段中保留数据项。这确保那些暂时被删除的数据不会离开筛选字段。例如，假设筛选字段有两个值YES和NO。有时数据集中可能没有包含值No的记录。默认情况卜，Excel将保留No值作为筛选字段的选项，等待NO仇重新出现。
要从数据透视表中清除这些数据项，必须从数据透视表中暂时删除这个字段，然后刷新透视表，再把字段拖回它原来的位置。
尽管这种做法在有些情况下很有用，但在大多数情况下并不需要这种功能。使用下列步骤可以让Excel禁用该行为。

删除的数据项.jpg

右键单击数据透视表，然后选择“数据透视表选项”。
在出现的对话框中，选择“数据”选项卡。
将“每个字段保留的项数”属性更改为“无”。

实例文件：

删除的项.rar (4.71 KB, 下载次数: 250)

=============================================================================================

七、刷新数据透视表时数据消失了

问题：
刷新数据透视表后，放入值区域的字段消失了，并删除了数据透视表中的数据．
数据丢失.gif

解决方法：
出现这种情况是因为更改了放入值区域的字段的名称。例如，创建一个数据透视表，并拖入一个名为工资的字段放到数据透视表的值区域。这时，如果在源数据中将工资列标题名称更改为基本工资并刷新数据透视表，那么值区域的数据将消失。原因在于，在刷新时数据表将刷新源数据的缓存．并发现工资字段已经不存在了．数据透视表当然无法计算不存在的字段。要解决这个问题，打开‘数据透视表列表”，将新字段拖入值区域中。

实例文件：

数据丢失.rar (4.46 KB, 下载次数: 213)

=============================================================================================

八、“推迟布局更新”选项锁住了排序、刷选、分组等功能

问题：
在创建数据透视表时使用了“推迟布局更新”选项，结果几乎所有的功能都被禁用了。
推迟更新.jpg

解决方法：
取消选中“推迟布局更新”。选中该复选框将使数据透视表处于手动更新状态，以至于无法使用数据透视表的其他功能（比如排序、筛选和分组）。

实例文件：

推迟.rar (4.44 KB, 下载次数: 192)

=============================================================================================

九、老版本的Excel无法正常地打开数据透视表

问题：
使用Excel2007创建了一个数据透视表，结果现在每次使用以前版本的excel时，所有的数据透视表功能都不存在了。
九.jpg

解决方法：
该问题通常是由不同Excel版本间的兼容性问题导致的。Excel2007在数据透视表处理数据的能力上有了一次飞跃。这就允许创建远远超出以前版本限制的数据透视表。例如．假设构建了一个数据透视，该表包含32500个以上的数据项（在Excel2002和Excel2003中已经是极限了）。此外还使用了Exccl2007独有的功能。那么如果有谁使用以前版本的excel打开excel2007文件，那么他只能看到一些硬编码的值而已.也就是说，数据透视表中的值被转换为硬编码的数据，并且数据透视表缓冲、透视表对象以及格式也都将丢失。
要解决该问题，可以使用兼容模式构建数据透视表报表。兼容模式允许以Excel2003工作薄的方式使用Excel2007。也就是说，它会遵守Excel2003的限制，避免了在无意中创建与以前Excel版本不兼容的数据透视表。可按照以下步骤以兼容模式构建数据透视表：

新建一个工作薄。
将空工作薄另存为“Exce.97一2003工作薄”。选择该选项将工作薄另存为．xls文件。
打开新创建的．xls文件。

打开空的．xls文件时，excel自动进人兼容模式。这样就可以按照Excd2003的方式创建数据透视表。在兼容模式下，可以确保不会超出任何Excel2003数据透视表的限制。

实例文件：无

=============================================================================================

十、刷新了数据透视表，然后计算字段显示为错误值

问题：
数据透视表中有一个计算字段，之前一直运行良好，但突然不正常了。该字段现在显示一个错误消息。
计算字段错误.jpg

解决方法：
重命名或者删除了数据源的数据。要修补该问题，只需编辑计算字段来反映源数据的更改即可。

实例文件：

计算字段错误.rar (5.65 KB, 下载次数: 275)

=============================================================================================

[ 本帖最后由 mn860429 于 2010-12-14 15:04 编辑 ]

afdjepa · 发表于 2010-12-12 11:11

要注意透视表“字段设置”中的选项“选定”与“确定”

htqccw · 发表于 2010-12-12 11:29

很好经验，学习了

afdjepa · 发表于 2010-12-12 11:34

所说情况与Office2003有别

mn860429 · 发表于 2010-12-12 11:39

欢迎补充，环境为07，因为论坛用03的用户好像比较多才将附件另存为了兼容格式，欢迎大家升级excel，毕竟是大势所趋。

mn860429 · 发表于 2010-12-12 14:39

透视表相关选项

一、自动生成透视表公式

QQ截图未命名.jpg

==================================

透视表公式.gif

实例文件：

透视表公式.rar (5.83 KB, 下载次数: 284)

=============================================================================================

二、页字段横版排放

页字段横版.gif

0默认的是全部，个数可以自己设置
实例文件：无

=============================================================================================

三、启用选定内容（快速选择透视表数据）

启用选定.gif

实例文件：无

=============================================================================================

四、透视表打印相关设置

1.每项目后面插入分页符

每项插入分页复.jpg

效果图：

意义：配合自动编号的辅助列和组合可以实现每页小计和最后总计

实例文件：无

2.透视表选项打印选项卡设置

在数据透视表上显示时打印展开／折叠按钮
在每一打印页上重复行标签
设置打印标题匀

（1）在数据透视表上显示时打印展开／折叠按钮

效果图：

（2）在每一打印页上重复行标签

效果图：

（3）设置打印标题

效果图：

打印标题.jpg

说明：此选项会自动添加顶端标题行和左边标题列

=============================================================================================

[ 本帖最后由 mn860429 于 2010-12-14 23:12 编辑 ]

zxw011401 · 发表于 2010-12-12 14:45

非常感谢，又学会了一招，哈哈

水星钓鱼 · 发表于 2010-12-12 14:55

MN这么好的帖子。不。。可惜了。哈哈

mn860429 · 发表于 2010-12-12 15:35

技巧一、在透视表中添加一个排名字段（03版&07版）

排名次.gif

=============================================================================================

技巧二、在透视表中添加一个排名字段（10版）

实例文件：

名次.rar (4.2 KB, 下载次数: 146)

=============================================================================================

技巧三、透视表美化（去除难看的"求和项："、"空白"、错误值）

美化.gif

实例文件：

美化.rar (4.39 KB, 下载次数: 171)

=============================================================================================

技巧四、二维数据转一维

维数.gif

实例文件：

二维数据转一维数据.rar (5.64 KB, 下载次数: 203)

[ 本帖最后由 mn860429 于 2010-12-14 15:24 编辑 ]

mn860429 · 发表于 2010-12-12 16:36

技巧五、公司个人年度工资汇总（sql+分页显示）

年度工资.gif

select *,1 as 月份 from [1月$] union all
select *,2 from [2月$] union all
select *,3 from [3月$] union all
select *,4 from [4月$] union all
select *,5 from [5月$] union all
select *,6 from [6月$] union all
select *,7 from [7月$] union all
select *,8 from [8月$] union all
select *,9 from [9月$] union all
select *,10 from [10月$] union all
select *,11 from [11月$] union all
select *,12 from [12月$]

复制代码

实例文件：

年度工资.rar (15.62 KB, 下载次数: 312)

=============================================================================================

技巧六、差异分析（多重合并计算区域页字段的应用）

差异.gif

实例文件：

差异分析.rar (5.25 KB, 下载次数: 191)

=============================================================================================

技巧七、强制显示无数据的月份项目（组合&显示无数据的项目）

强制显示无数据的月份项目.gif

实例文件：

强制显示无数据的月份项目.rar (4.19 KB, 下载次数: 138)

=============================================================================================

技巧八、创建动态的数据透视表（数据库表的应用）

数据库表.gif

实例文件：

利用数据库表创建动态的数据透视表.rar (4.3 KB, 下载次数: 217)

[ 本帖最后由 mn860429 于 2010-12-14 15:25 编辑 ]

		自动登录	找回密码
密码			免费注册

[分享] 数据透视表常见问题诊断&解答

评分

关于透视表中计数与求和

注意软件版本