数据透视表的格式设置.doc

数据透视表的格式设置
数据透视表最大的功能在于分析。但我们在用的时候往往不是分析给我们自己看的，而是分析给我们的同事、老板、客户供应商或给一些相关的人员看的，这时候就要注意一下透视表的仪表形象了。“爱美之心，人皆有之”，如何美化数据透视表，让透视表成为内在美与外在美兼备的“美人”，这也就是本节课的学习内容——打造精美的数据透视表。
☆ 快速为数据透视表添加应用格式（套用格式有两种方法）：
方法一：数据透视表工具栏，第二个按钮“设置报告格式”，选择想要套用的格式，确定。

方法二：菜单栏格式，自动套用格式，确定。

☆ 四类自动格式的区别与特点
第一类：报表     共十种格式。它的显著特征是一维表格，相当于一个列表，而且不同优先级的字段呈缩进形态。通过演示文件可以看到，套用报表格式后，列字段就插入到了行字段的最前面，也就是说列字段优先于行字段。—— 建议大家谨慎使用，因为它会把你的布局都给改了。
选择自动格式时，双击报表格式就可以，不用选中后再确定。
第二类：表     共十种格式。特征是二维表格，有行字段、列字段和数据区。数据区域不带缩进。格式套用后仍保留透视表的布局。
第三类：传统数据透视表     只有一种格式。这是数据透视表的默认格式，使用这种格式可以把套有格式的透视表“打回原形”。
第四类：无     只有一种格式。可以把数据透视表变得不像透视表。演示文件中发现，它能去掉透视表数据区域上方的“数据”，将数据透视表伪装成普通表格，这就是无格式的用处。
 可以看到，在“自动套用格式”对话框中，有很多的选择，从预览图中大概的可以看出，具体的为“报表1—10”、“表1—10”、“传统数据透视表”、“无”，即在“自动套用格式”对话框中提供了22件“衣服”。
2、四类自动格式的区别与特点
（1）报表
在英文版的Excel里，“报表”显示为“Report”，从技术的角度讲，其显著特征体现为一维的列表，不同级别的字段呈现缩进形态，如图
可以看出，自动套用“报表”格式之后，列字段插入到行字段的最前面，即列字段优先于行字段，由此可得，“报表”格式会改变数据透视表的布局，而且原有列字段优先于行字段。但也因为如此，在一般情况下不推荐使用。
（2）表
在英文版的Excel里，“表”显示为“Table”，所呈现的就是一个二维形态的表格，与生成的数据透视表很相像，有行字段、列字段、数据区域，
发现数据透视表的布局与最开始生成的一样，有行字段、列字段，同时给整个数据透视表应用了一些格式，包括不同颜色和底纹的标题区、汇总行等，即不会让数据透视表看起来像一张白纸。可以看出，“表”不会改变原有数据透视表的布局，只是把相应单元格的字体样式、单元格背景、边框等做了一些设置。
（3）传统数据透视表
如果应用了“报表”、“表”等格式后，不想使用了，想回到最初生成的数据透视表，不需要删了重新生成或手动调整，只需应用此项，即可恢复到最初的状态。
（4）无
传统数据透视表已经非常朴素，但应用“无”的格式，会去掉所有的边框。这种格式并不是在所有的情况使用都有效果，如图
如果想让数据透视表呈现为普通的表格，可使用“无”的格式。如图
如果继续对上表做一些修饰，就可以将数据透视表变得不像。

☆ 自动格式的优点与缺点
优点：
1、快：点开菜单，选择喜欢的报表形式。
2、自动保鲜：当对透视表套用了一个自动格式以后，这个格式其实已经不是衣服这么简单了，它更像透视表的皮肤。无论你的透视表发生什么样的布局改变，刷新、筛选或内容增减，都不影响套用的格式的显示。如果不是自动套用格式，很难达到与透视表这种无缝隙结合的这个特点。
缺点：
1、样式太少了：才22个，容易引起审美疲劳且缺少个性。
2、不支持自定义：只有应用、确定的按钮，没有自定义。
个性鲜明DIY

除了使用自动套用格式以外，接着学怎样对透视表进行个性化的格式DIY。摩登套装虽然好但数量有限，如果满足不了你的需求，那么可以自己动手做个性化的套装，打上自己的性格烙印，自己的品牌烙印。

☆ 显示或隐藏透视表的行、列总计
还有一种方法就是直接在“表格选项”里修改
隐藏方法一：把鼠标移到行/列总计栏上，鼠标变成向下或向右的实心小黑箭头，单击鼠标右键，选择隐藏。
隐藏方法二：单击数据透视表任意单元格，右键，选择“表格选项”，取消勾选“列总计”和“行总计”。
显示方法：单击数据透视表任意单元格，右键，在“表格选项”下，勾选“列总计”或 “行总计”。
启用选定内容特性，可批量选择。
 注意：鼠标应停在单元格的字段上，不能停在行号或列号上，这在透视表中是不允许的。鼠标形状相同，位置不同，则意义不同。
显示或隐藏透视表的分类汇总项
当行字段和列字段有多个时，会默认出现分类汇总的项目，相当于分类汇总小计。
隐藏方法一：把光标停在其中一个分类汇总项上，当光标变成向右的实心小黑箭头时，单击鼠标左键，所有的分类汇总项就都会被选中（启用选定内容特性），单击鼠标右键，弹出的菜单中选择“隐藏”。
隐藏方法二：双击行字段名称，弹出数据透视表字段对话框，分类汇总选项下选择“无”。
显示方法：隐藏了哪一列的分类汇总，就双击哪一列的行字段名称，在弹出数据透视表字段对话框，分类汇总选项下选择“自动”或“自定义”，确定。（自动就是表示有，默认是求和；自定义可以改变当前的汇总方式，如计数等）。

☆ 显示或隐藏透视表的明细项
透视表里面不允许隐藏单个的单元格，因为透视表是一个整体区域，不能去删除、插入，而此时又不希望出现某一些明细项，这就需要按照自己的希望显示或隐藏。
隐藏或显示明细数据之一：右键，“组及显示明细数据”下“隐藏明细数据”。
隐藏或显示明细数据之二：双击某一个数据项即可显示或隐藏，比较简便。

☆     合并居中显示标题内容
在透视表任意单元格，单击鼠标右键，“表格选项”，勾选“合并标志”。
数据透视表的居中是对整个表生效的，它跟菜单的合并居中是不一样的。
——建议：合并居中能不用就不用。

☆ 每项后面插入空行
透视表中是不允许插入空行的，那样会影响数据透视表的完整性。
方法：双击字段名称，打开“数据透视表字段”对话框，选择“布局”，勾选“每项后面插入空行”，确定。

☆ 设置错误值的显示方式（让错误值显示的更友好或不显示）
在透视表任意单元格，单击鼠标右键，表格选项，“格式选项”下勾选“对于错误值，显示（V）：”，确定。内可以为空、 “错误”、 “×”等，可以根据你自己的需求来决定。
录入特殊字符的快捷方式：
“×”快捷键：alt＋41409（小键盘）
“√”快捷键：alt＋41420（小键盘）

☆ 处理“空白”
对于数据项有“空白” 单元格：在透视表任一单元格，单击鼠标右键，表格选项，格式选项，勾选“对于空白选项，可以在空框处输入你想显示的内容，如wrong等。
对于行字段有“空白”字符：选中任何一个包含空白的内容，复制，查找替换，查找内容粘贴刚才复制的内容，替换为  （空格）。

☆ 设置单元格格式
行标题：选中，可以换一个背景，进行填充、加粗。
汇总项：启用选定内容，进行填充背景，字体加粗，变换字体颜色。
数据区域：设置数据格式区域的时候，除了手动设置的方式，最好还是进入数据字段的字段设置里面去做设置。如：做求和项的金额，双击字段，进入“数据透视表字段”，数字下进行相应的设置。这样设置的格式，不会因刷新、筛选等发生格式的丢失，它的格式是持久保鲜的。
智能外观
通过自定义数字格式、条件格式，让你的数据透视表有一个智能的外观，在DIY的基础上更近一步，可以像变色龙一样，随着环境的变化、需求的变化、数据源的更新而去做一个变动。称之为智能外观。

☆ 改变数值的显示方式（自定义数字格式）
1、将单元格中不太人性化的字符显示为“√”
方法一：全部选中，设置单元格格式。此方法简单，不能永久保存。
方法二：双击计数项，数据透视表字段，数字，自定义，类型中按ALT键输入“41420”显示“√”，确定。
如果想让√变成红色，则在自定义类型直接输入[红色]“√”即可。
注意：[ ]必须在英文输入状态下。
2、如班级下面是纯数字想在数字后面加一个“班”字
双击班级，数字，自定义类型，输入“G/通用格”班””，确定。0.00”元”
自定义数字格式什么时候能用？
只能对有数字的字段去设置。因为文本字段菜单中没有此项显示。
☆ 隐藏零值
方法一：工具，选项，视图，勾选掉“零值”。 此公共选项适合整个工作表里只有一个透视表。
方法二：如果工作表中还有其他数据，只希望零值不在透视表中显示，就只能用自定义数字格式去做。
标注负数：双击字段名，数据透视表字段选择数字，数值，选择负数用红字显示格式；
隐藏零值：双击字段名，数据透视表字段选择数字，数值，点击自定义，你会发现一个自定义代码，无论在哪个代码的后面输入；；确定。这样就同时达到了不显示零值及负数为零的效果。
代码分成四段：正数；负数；零；文本。

☆ 突出显示符合条件的数值
1、按数值大小（500以上用蓝色表示，500以下用红色表示）
方法一：自定义设置格式：双击字段名，数字，自定义类型下面输入“[红色][>500]G/通用格式;[蓝色][<500]G/通用格式”确定。
方法二：选中数据区域，格式，条件格式，单元格数值，大于，500，格式，颜色，蓝颜色，确定。在添加条件下，可同样的设置小于500的格式。
2、按目标值差距
数据透视表中的某一列数值与某一个目标值比对的结果，根据这个结果去做相应的突出显示。
方法一：选中合同结束列，格式，条件格式，公式，=E5<$H$1，格式，图案绿色，确定
方法二：选中合同结束列，格式，条件格式，公式，=E5-“2007-9-30”<0，格式，图案绿色，确定。
这两个公式条件方法是等效的，那么根据这样的条件格式，就可以得到数据透视表中的某一个数值或某一列数值与某个目标值去比对的结果，根据这个结果去做相应的突出显示。

☆ 永恒的边框
传统透视表格式的风格里，只有标题行、标题列、总计稍微有一些边框线，通过边框添加表格线，刷新后就没有了。
首先，寻找每行都有数据的列与每列都有数据的行，判断当前这个单元格是不是应该有边框线。然后，设置条件格式：定位到最右下角的这个单元格，按住shift键，再去选左上角的这个单元格。条件格式为公式=AND($A5<>” ”,A$5<>” ”)，设置边框。
视频之 四      格式保鲜
如果你的透视表格式设置好以后，当你的某些情况、外在环境或者是你的布局发生了某些变化的时候，格式可能会丢失。那么针对不同的透视表的变化情况，格式会产生不同的影响，这时候我们可以选择Excel一些相应的设置选项或者一些讨巧技术去帮助大家能够以不变适应万变，不会受到数据透视表的影响，也就是保鲜。

☆ 注意透视表美化后的格式丢失问题
方法一：尽量使用字段属性
方法二：借助条件格式，重点在于变化中边框的设置。

☆ 解决列宽问题
方法一：设置表格选项     
创建一个透视表，它会自动套用格式（传统数据透视表格式），设置列宽后，在表格选项下，取消勾选自动套用格式，确定。再刷新的时候，列宽就不会变化了。
优点：最简便的一种方式。
缺点：从此以后不能再使用透视表的自动格式了。
方法二：利用空格   
这是一种讨巧的办法，在列字段的标题两侧加空格来达到你想要的列宽，加空格后刷新，就可以看到列的变化。
方法三：借助宏（终极武器）    
录制一段宏（重新设置列宽的宏），在菜单栏上右击，选择Visual Basic, 调出Visual Basic对话框，选择第二个按钮，录制宏，录制新宏，直接点确定，就OK了。
下面你做的每一步都会被Excel记录成宏代码，这个宏代码会在将来进一步设定之后，可以在每次透视表刷新后进行运行。
现在需要做的是重新设置列宽，选中要调整列宽的所有列，拖动列宽至满意的宽度，调整完了，这个操作就行了。下面点击Visual Basic对话框第2个按钮，停止录制，这样代码就出来了。
查看运用代码：在所录制宏的工作表sheet3标签上按鼠标右键，选择查看代码，会出现一个界面，在工程窗口的下方有一个模块，双击模块，在右边会出现一段代码：”columns（“C:H”）。select   selection.columnwidth=10.5”复制代码，然后在工程窗口双击次工作簿中sheet3的名称，上面通用处点击Worksheet，右边选择PivotTableUpdate，把代码粘贴到end  sub之前，然后将end sub之后的删除。回到透视表后，刷新，会发现宏代码已经生效。
VBA的大名早已如雷贯耳，但从没接触过，总觉得那是非常复杂、非常深奥的DD，对它敬而远之。在看过周老师的操作后，觉得用VBA更简单、更方便，今后有时间得学学关于VBA的知识了。

☆  解除数据透视表的列数限制？
在一个表格创建多个字段的时候，如果行字段达到6个或6个以上，就只能加入一个数据字段，加第二个会报错。
正确的操作方法是先将字段拖到数据区域，再把数据拖到列字段上面（爱拖多少拖多少）让它并排显示，再拖行字段区域即可。
视频之五      美化实例

把前面的功能串起来，来介绍从头到尾介绍如何美化数据透视表，综合的将技巧融会贯通一下。
☆ 页字段的美化：表格选项，页面布局。
☆ 自动套用格式：选择自动套用格式中的“无”，表格选项，勾选合并标志
☆ 设置自动筛选：选中列标题最后一个字段旁边的空白单元格，数据，筛选，自动筛选。
☆ 批量设置汇总项：批量选中汇总项，填充颜色并加粗字体等。
☆ 设置数值字段的显示方式：双击相应的字段，数字，数值，设置。
☆ 标题行的设置：选中标题行，设置背景颜色，字体加粗，字体颜色。
☆ 永恒的边框线：选中整个透视表，格式，条件格式，输入公式“=$E28<>”””。
☆ 重新设置整个表格的字体。
☆ 取消表格中默认的网格线：工具，选项，视图，勾选掉网格线，确定。
☆ 为整个工作表添加一个背景：格式，工作表，背景，从电脑中选择一张图片（尽量简单一些的、色彩稍微明亮一点的、接近于白颜色或亮色系的图片），插入。
☆ 在A列前插入一列，这样可以让透视表居中且可以看到左边的边框线。