数据透视表教程打造精美的数据透视表.docx
《数据透视表教程打造精美的数据透视表.docx》由会员分享，可在线阅读，更多相关《数据透视表教程打造精美的数据透视表.docx（17页珍藏版）》请在冰豆网上搜索。
数据透视表教程打造精美的数据透视表
本课主题：
打造精美的数据透视表
数据透视表最大的功能在于分析，但是我们在用数据透视表进行分析的时候往往是要把做的分析给同事、老板、客户供应商以及相关人员看的。
那么，在这个时候数据透视表就要注重其仪表形象了。
否则数据透视表的可读性就比较差，吸引人眼球的能力也就比较差，所以一张养眼的数据，透视表首先在外表上也许就能打动阅读者的心。
下面就我们就来探讨一下怎么样让数据透视表更美。
一、试穿摩登套装：
自动格式
1、快速为数据透视表应用格式
套用格式的两种方法：
A、数据透视表工具栏里第二个按钮，设置报告格式，激活自动套用格式。
如图1
｛勤据逵视表型4幻^311.oaI
B、EXCEL工具栏菜单，格式，自动套用格式。
如图2
2、四类自动格式的区别与特点
在自动套用格式里提供了22种格式，其中有10种报表类型，10种表类型，1种传统数据透视表，1种无格式。
A、报表：
报表格式的特征是它是一维表格，而且报表的优先级成缩进形态显示。
在应用报表格式之后，它会改变数据透视表的布局。
如图3
品名
Q'愴售世区O销售人扇Q£E£
弟售金救
北京
白寮贞
ZIZ&OO
130
110400
赵琦
E29
L93ZOO
北廉汇总
B33
50B400
毕春他
]otI
59500
&0400
杭州宝总
15Q
120000
B表：
是一个二维表格，没有缩进。
应用表的这种格式只是改变了字体颜色、大小、样式、边框等等，但是会保留数据透视表的原来的布局。
如图4
品名FF1
報步机
县示矮
雷售A匸
-
敷量柏售金醸
M
怙善金Ml
數星愴肯金緩
戡量愴售金■
白索穴
2B&
012800
34S
179360
北京
563
1239600
T3
43000
13&
11D400
232
5S7S^
1B3200
詔
SS7D0
&06CC
汇J&
635
W4QD
⑵瞒DO
474
»eo&o
?
E6
117354
1
r«
421
92&200
34
4Q9E
U1
ai440
319
570K
广州i［盘
471
□?
67D0
131
fii«n
3S3
C259T
ST
69600
M3»
X2
3500C
S3
S0400
J.55
&094&
2:
10.
_307M
汇J5
150
Dlog.sina^corTj^cfi/yongjin
C传统：
传统格式也就是EXCEL默认的格式，数据透视表创建好之后就是这种格式。
如果
想让数据透视表的格式回到原来样子，就应用这个格式。
如图5
品名F1数轄F
按摩椅
微淤炉
销書比区-
销售人员匸
销售金额
销售金做
白隶贞
266
2123001
345
H^&O
563
堆38800
73
43000
133
LUJ4U0I
229
58
3570C
北京汇总
&33
50&40U
5&3
1238500
4T4
2530&0
厂州
⑵
02B20D
151
61440
厂州汇启
421
9262C0
131
6UW
广州
白素贞
266
212800
136
L101OD
W
19^3200
&33
50640（}|
何庚
畅光
广州汇总
Sfi步机
数臺
123M0
微诚炉
345
73
179360
^3000
35TOO
253060
331
131
61440
61440
销售全做
寸向汇总
按摩荷销售地区□销售人员口数壘
92&200
1E33&00
92&200
D无：
隐藏数据透视表的某些特征，比如去掉透视表数据区域上方的“数据”按钮和边框,让表格看起来更象普通表格，这就是无格式的用处。
如图6
3•自动格式的优点与缺点
优点：
A、快：
只要选择一个你认为适合现在数透视表的格式就可以很快地应用到表格中。
B、自动、鲜：
当套用了一个自动格式以后，无论透视表发生布局改变、刷新、内容的筛选，套用的格式是不会受到影响。
如果不是自动套用格式，那么无法达到与透视表这种无缝结合的这个特点。
缺点：
A、样式太少了只有22个，容易应生审美疲劳。
、不支持自定义。
不能将自已设置好的格式保存下来供以后使用
、个性鲜明DIY1、显示或隐藏透视表的行、列总计
方法1:
将光标停在汇总项的行（列）的靠左边（上边）位置，光标变为黑色向右（向下）箭头时点右键，在弹出的对话框中选择隐藏（不要将光标停在行标签和列标签上）
方法2:
定位鼠标到任一单元格，单击右键，表格选项，去掉列总计和行总计前的勾即可隐藏。
（反之即可显示）如图7
2、显示或隐藏透视表的分类汇总项
隐藏分类汇总项的方法：
A:
把光标停在分类汇总项左边，当光标变成向右的箭头，单击鼠标左键，所有的分类汇总项就都会被选中（要先启用选定内容功能），单击鼠标右键，弹出的菜单中选择“隐藏”。
值佰佰和數均*小积求计平最最乖
B:
双击要隐藏分类汇总的行字段名称，弹出数据透视表字段对话框，在分类汇总选项下选择“无”，确定。
如图8
牙类汇总
（?
■自动©C'目走殳鮒O无⑧
显示分类汇总项的方法:
双击要显示分类汇总的行字段名称，弹出数据透视表字段对话框，分类汇总选项下选择“自动”或“自定义”，确定。
（自动就是求和，自定义可以改变当前的汇总方式，如计数等等）
3、显示或隐藏透视表的明细项
隐藏数据明细的方法：
A、单击想隐藏明细项的数据项，鼠标右键，弹出的菜单选项选择“组及显示明细数据下的隐藏明细项目。
如图9
B、双击想隐藏明细项的数据项，即可隐藏下一级的明细项。
如图10
销售地宜&
销售金锁
北京
833
506400
广州
炖+1J
——
许三翳
&3
50400
杭州汇总
190
120000
54
43200
咼启
肓伟
负
216000
324
259?
00|
显示数据明细的方法：
双击已经隐藏明细项的数据项，即可显示隐藏的数据明细
4、合并居中显示标题内容
合并居中方法：
选中透视表的任一单元格，单击鼠标右键，表格选项，勾选合并标志。
如图11
名称迈）；：
数据透视表i
顶质布局0J:
垂直并排
每列字段對a）:
□对于错误值j显希迪巨对手空单元槁，昱示a
□设置打印标题CL
格式选项
0列总计⑹
0行总计（P
0自动奁用格式⑥
□隐議的子汇总项憶）
□靜赢恙“⑪一^
叼毎一打印页上重复项目0）
注意：
在普通表格里最好不使用合并居中，因为在进行数据分析或创建数据透视表时可能遇到很多问题。
数据透视表的合并居中是对整个数据透视表起作用的，该功能跟菜
单里的合并居中不同。
5、每项后面插入空行
总动走遲匸自自元漠SOO
=冏
»a
［确定］
33600
4?
取消丨
匸
IE
［隐蕊0）
IE
（高级迪…
32
七戸m
邂00
7
:
布局屯）….
gojnn
丄丄
1
令汎冲
»汇总
白素贞
EQG
林请B
路小雨
36T
许他
□显示空数据项
双击字段名称，布局，勾选“每项后面插入空行”，确定。
如图12
数据请观表字段布层
显示选顶
■S以报養形式显示顶目①
<:
:
■归栩戒式显示项目①花细朗顶部整示分类汇总©）
打EP3JW
口矽臨蜀V『鉅跚I翻“g|j「门旳
注意数据透视表不允许直接在表内插入空行，否则会影响它的完整性。
6设置错误值的显示方式
选中透视表任一单元格，单击鼠标右键，表格选项，格式选项，勾选对于错误值，然后在显示的框内自定义内容（可以配合ALT+数字小键盘进行特殊字符的输入），确定。
女口图
13
处理行字段内有空白的方法：
先复制一个“（空白）”然后按容粘贴（空白），替换为输入一个空格，点击全部替换。
CTRL+F查找替换，查找内
7、处理空白
8、设置单元格格式
A、行标题：
可以换一个填充色进行填充、加粗
B、汇总项：
启用选定内容，换填充色进行填充，字体加粗，变换字体颜色
C、数据区域：
设置数字格式，选中表格，单击“千分位分隔符的数字”快捷键。
如果想持久保鲜数字格式，则用数据透视表的字段设置里的单元格格式来设置。
如图14
值值值.订平星杲乘
=•
选项（Q）»
三、智能外观1、改变数值的显示方式（自定义数字格式）
A、全部选中，设置单元格格式。
B、双击计数项，数据透视表字段，数字，自定义，类型中输入“V”，确定，确定。
女口果想让V变成红色，则在自定义类型下面输入［红色］“V”即可。
如图15
单元格格式
如果想在纯数字后面加上某字，比如102班，即在自定义类型下面输入G/通用格式“班”，确定，确定。
注意：
自定义数字格式只能在有数字的字段用。
因为文本字段菜单中没有
用数
专比记
规值币1+期间分数学本辣
常数货樂日时百分科立特
许數顼:
谍程
类型⑪
卽適用稱式
0
0.00
花WO
花##□.00
*彩W；*"
:
*也oo^ooZ:
*-屯®fo"oo_
此项显示。
如图16
数字
分类◎
g/通用格艺"班r
规值币计期间分數学44躱貝-吊數在罚会目时百分科文特国
■.
00
.00
+W.
空
*■戳#S0_:
_**；*M.00
类据②;
示例
班级
单元格■格式
2、隐藏零值
A、工具菜单，选项，视图，勾选掉“零值”。
这种设置法整个表格将不会显示零值。
如图17
mE
国表_|1
「麺色—II—鬲―i—廊~误戲厂i「拼髓盍—『安全性］
视團
F=|j—*
重新计宣編辑窜规1-2-3的帮助自定义序列
i
回启动任务窗格⑪叵I漏辎世迥回枚态栏⑤回任务栏中的窗口迪
批注
G无⑧身艮雄示标识碍（X）C）批注和标识痔⑩
对象
■>.全韻显示如n显示占位符（Vo全部隐藏①
窗口遼顼
匚:
自动分贡符⑧0和号列标（E）0水呼嚴动条IX）
□公式®回分级显示苻号Q）0垂直滾动条边
回网格统⑥□聲詹②10工作表标签叨
岡格线颤邑C）:
自窃
C、只希望零值不在透视表中显示，并且出现负数的需要标注出来：
双击字段名，数据透视表字段选择数字，数值，选择负数用红字显示格式，点击自定义，然后在代码的后面输入0.00;［红色卜0.00，确定，确定。
如图18
数宇
求和顼:
数量
自定义格式说明：
1、如果只定义一个段，则该代码用于所有的数字上。
2、如果只定义两个段，第一段用于正数和0,第二段用于负数。
3、如果只定义三个段，第一段用于正数，第二段用于负数，第三段用于0值。
4、如果定义四段，第一段用于正数，第二段用于负数，第三段用于0文本，第四段用于文本。
有空还得补补这方面的知识。
3、突出显示符合条件的数值
A、按数值大小：
500以上用红颜色表示，500以下用蓝颜色表示。
方法1:
自定义格式设置，双击字段名，数字，自定义类型下面输入[红色][>500]G/
通用格式；[蓝色][<500]G/通用格式。
如图19
方法2:
条件格式设置，选中数据区域，格式，条件格式，单元格数值，大于，500,
格式，颜色，蓝颜色，确定。
添加条件，单元格数值，小于等于，500,格式,
颜色，红颜色，确定。
如图20
AaBbCcYyZs
条件L①，
条件为克吋，持用悔式如右阁所示：
小于或尊于T
權式［辽二马
家舟格式
_删除远Li［陳定］
B、按目标值差距
数据透视表中的某一列数值与某一个目标值比对的结果，根据这个结果去做相应的突出显示。
选中合同结束列，格式，条件格式，公式，=E5<$H$1格式，图案绿色。
女口
图21。
选中合同结束列，格式，条件格式，公式，=E5-“2007-9-30”<0,格式，图案绿色。
4、永恒的边框
传统透视表格式的风格里，通过手动添加边框线，刷新后就没有了。
可以通过条件格式设置自动地为表格加上边框线。
A、寻找每行都有数据的列与每列都有数据的行
B、设置条件格式
方法：
选中透视表，格式，条件格式，条件1,公式后面输入=and（$A5<>””，A$5<>””）当前面两个条件成立时就应用后面设置的格式确定。
如图22
使用条件格式，简单易行无需代码，但是如果行字段或者列字段中有“空项目”，那么数据透视表的公式就不像下图中那么简单了，或是改变透视表布局，比如添加字段后透视表区域将“扩大”，那么新增加的单元格区域无法自动添加条件格式，因此没有“永恒边框”，也就是说这种实现方法无法自适应扩展。
D问题：
如何创建通用的条件？
这个下去再想。
活动单元格一定要是K22
四、格式保鲜
1、注意透视表美化后的格式丢失问题
A、尽量使用字段属性
B、借助条件格式
重点在于变化中边框的设置
2、解决列宽问题
A、设置表格选项
透视表创建，设置列宽后，刷新数据后设置的列宽就没有了，要让它刷新数据列宽不变可以去掉自动套用格式勾选，再刷新的时候，列宽就不会变化了。
这是最简便的一种设置列宽的方式，但是从此以后就不能再使用透视表的自动格式了
如图23
B、利用空格
选中自动套用格式选项，可以在列字段名称两侧加空格来达到列宽的固定，它是按列字
北京
销莒人员1二
白素贞
李兵
北京汇总
数居一
数星「
在讴两停殳养后加空相空迪。
79Z2&91140
7S514431D0
梢萝金輙*
1015810
3071
72^1&5P
段的名称长度来调整列宽的。
如图24
C、借助宏（终极武器）
录制一段宏设置列宽，然后在工作表标签上按鼠标右键，查看代码，在工作簿名下双击模块，复制右边的代码，然后点击左边工作表的名称，点击Worksheet，
PivotTableUpdate（为每次刷新数据透视表执行），然后把代码复制到里面。
如图25
q対町
jj
」《＜■j*'/.」专：
行s列日
jQ
*
J3
俅恒的我E^tcel对彗（ShatUlHftswa
-YTAfroject
-r■】:
rasoft
Ti訂Shtttlftsbattsjt57histfcrkboak
:
-吁檯按徴模块1
□®B
（名称〕
SheUl
EiSiplayPa^eBze-F^Lse
DiEpla>*K.LfhtTftFiLstEnfcbleAu+4-?
iltF«Xi»Enfabl*rtlculttTruiIn^bleCruillninFalse^£n^blel'iyqLTbb.f4L.se
:
En^ble^-xHKoRtslr
•.永恒的表4&找亠x"-Sheet1《代码）
!
艾件®鳴错世I视国电）骷入①務式⑪谓直担）运行®工具（I）外接程序⑥宙口如翳助班）
3、解除数据透视表的列数限制
数据透视表创建多个字段的时候，行字段达到6个或以上的时候，只能加入一个数据区域，否则就会报错。
创建方法：
先拖数据区域的字段，再拖行字段。
五、美化实例
1、设置页字段
2、自动套用“无”格式
3、将数据区域设置为自动筛选
4、设置汇总项5、设置数值的显示方式
6、标题行的设置
7、为表格设置永恒的边框线
8、设置整个表格的字体
9、取消表格中默认的网格线
10、为整个工作表添加一个背景
虽然这节课看起来是美化数据透视表的工作，但是要掌握的知识还是很多，所以感觉收获还真不少。
另外在自定义数字格式和VBA方面的东西接触的很少，以后还要抓紧学习呀！
升级会员 