数据透视表教程1-打造精美的数据透视表

本课主题：打造精美的数据透视表

数据透视表最大的功能在于分析，但是我们在用数据透视表进行分析的时候往往是要把做的

分析给同事、老板、客户供应商以及相关人员看的。那末，在这个时候数据透视表就要注重其仪

表形象了。否则数据透视表的可读性就比较差，吸引人眼球的能力也就比较差，所以i张养眼的

数据，透视表首先在外表上也许就能打动阅读者的心。下面就我们就来探讨一下怎么样让数据透

视表更美。

一、试穿摩登套装：自动格式

1、快速为数据透视表应用格式

套用格式的两种方法：

A、数据透视表工具栏里第二人按钮，设置报告格式，激活自动套用格式。如图1

1数据透视表口，为幽i要w!■,J1.3

B、EXCEL工具栏菜单，格式，自动套用格式。如图2

珞式瓯L工具一L数据啤•唐单元格Clrl”

行⑧>

列©*

工作表冬）

自劫再用格

条件格式如...

2、四类自动格式的区别与特点

在自动套用格式里提供了22种格式，其中有10种报表类型，10种表类型，1种传统数据透视表，1

种无格式。

A、报表：报表格式的特征是它是一维表格，而且报表的优先级成缩进形态显示。在应用报表格式之

后，它会改变数据透视表的布局。如图3

北京

白素^Z66212-800

苏璃110400

%L33ZOO

北京汇忌&33

华春常1

许三多63如皿

锁州W忌154)

配若销售蹈区悟售金瓣

按摩背

B、表：是一个二维表格，没有缩进。应用表的这种格式只是改变了字体颜色、大小、样式、边框等

等，但是会保留数据透视表的原来的布局。如图4

8JS—i/

细句按摩符学皴妒

捎告蛔区销传人MA数垦梢替金脉赤兴糖若金闸数量崛伟金通数最摘甘盅翔

白素贞25G.,28003451四凡

5&5lA396flO7343(XX)

赤珊13911W叩96-754

3S7O

22&inSMO«*M4606W

1

北京汇总8曼5(K4no洒3L约瞒09舟TZG?

11

广州何1R42192蹊0034499E

拓光151&I440SI957JS

广抵IL忌4219262419131由■和353625911

毕杏抽8TC96<)01&9545(H)3029500C

许兰多as504001.M紫§加£10.30性4

外州汇忘

150lAOWO

C、传统：传统格式也就是EXCEL默认的格式，数据透视表创建好之后就是这种格式。如果想让数据透

视表的格式回到原来样子，就应用这个格式。如图5

品夕R初如H

按摩椅能步机微波炉

箱售地区^销售人员，数量销售金额数量箱售金朗数量销售金丽建

白素贞266212WK)3451793&0

率兵通lA3X60(>

北京73430(H)

苏珊1II040CJ

知56,55700

北京前1总633'506400癖123860(]474258060

何庆421926200

扬光131614=40

嘛•.汇总

42-i926200131B1440

D、无：隐藏数据透视表的某些特征，比如去掉透视表数据区域上方的“数据”按钮和边框，让表格看

起来更象普通表格，这就是无格式的用处。如图6

%1名*数据1

莅，摩椅跑引艮机m支炉

销售地区-L销售数量精告金?i?i数量馅告金额数星稻售金额

白索贞:湘'I2I28OC345179360

李互

北京553T3F43.10()

苏珊I3B110400

引185320056J35TOO

北京汇总633506400563).1力8600474•258060

何庆

广州42192&2OO

杨光13161440

广州汇总421S2&20013161440

3.自动格式的优点与缺点

优点：A、快：只要选择一个你认为适合现在数透视表的格式就可以很快地应用到表格中。

B、自动、鲜：当套用了一个自动格式以后，无论透视表发生布局改变、刷新、内容H勺筛选，

套用的格式是不会受到影响。如果不是自动套用格式，那末无法达到与透视表这种无缝结

合的这个特点。

缺点：A、样式太少了惟独22个，容易应生审美疲劳。

B、不支持自定义。不能将自己设置好的格式保存下来供以后使用。

、个性鲜明DIY1、显示或者隐.藏透视表的行、列总计

方法1：将光标停在汇总项的行（列）的靠左边（上边）位置，光标变为黑色向右（向下）筋头时

点右键，在弹出的对话框中选择隐藏（不要将光标停在行标签和列标签上）

方法2：定位鼠标到任一单元格，单击右键，表格选项，去掉列总计和行总计前的勾即可隙藏。

（反之即可显示）如图7

名称牌;

格式选项

H列总计毛页画布局（1J：垂直并排

回行总计粉1

每列字段数更〉一：

0自动奁用格式僚）

隐贼的子汇总项

ri对于错误值，显示（V-

II合并标志曜

回保留格式蛛一叵对「空单元裕」显示⑥:

叵屋一打印页上重第项目（QJL1设置打印标题（V

2、显示或者隐藏透视表的分类汇总项

隐藏分类汇总项的方法：

A：把光标停在分类汇总项左边，当光标变成向右的箭头，单击鼠标左键，所有的分类汇总项

就都会被选中（要先启用选定内容功能），单击鼠标右键，弹出的菜单中选择“隐藏”。

B：双击要隐藏分类汇总的行字段名称，弹出数据透视表字段对话框，在分类汇总选项下选择

“无”，确定c如图8

数据通视表字段

字随名（£）鼐定

职消

分类汇总-

宜

求r

■>::l

计

U直隐藏

匈腕黑）包）平

•直

最V

0无但）%

和

最也），高

\]

乘

一

/匝：

心

显示分类汇总项的方法:

双击要显示分类汇总的行字段名称，弹出数据透视表字段对话框，分类汇总选项下选择“自

动”或者“自定义”，确定。（自动就是求和，自定义可以改变当前的汇总方式，如计数等

等）

3、显示或者隐藏透视表的明细项

隐藏数据明细的方法：

A,单击想隐藏明细项的数据项，鼠标右键，弹出的菜单选项选择“组及显示明细数据下的隐

藏明细项目。如图9

其俊祀茶融衅据地J

＞率旃也合团）

株州

laws

43ZOO

arcKWBO

E、双击想隐藏明细项的数据项，即可隐藏下一级的明细项。如图10

俏售地区一销售人员数星:肖售金额

北京4-------眺S0B40O

J州■-

.华春鲍ST6v6（川

171*11一

许三盘50400

杭州汇总150120000

范跑抱

南京5443200

高伟ETO2I6OOC.

-南屈汇总324.2592OC

显示数据明细的方法：

双击已经隐藏明细项的数据项，即可显示隐藏的数据明细。

4、合并居中显示标题内容

合并居中力法：选中透视表的任一单元格，单击鼠标右键，表格选项，勾选合并标志。如图

数据透舞表选项

名祢1cL数据医视表］

格式选项

斯列总计⑥页面布局1）：垂直并排

E行总计啰

拇列字段数（I）.：

0自动茸用格式也）

口隐藏的子汇总项电）口答

笄原蔽!□对于错误值，显示cyi

叵保留格式。叵对于空单元格，昱示也

反每一打印页上市豆项目而）口设詈打印标题⑴

注意：在普通表格里最好不使用合并居中，因为在进行数据分析或者创建数据透视表时可能遇

到不少问题。数据透视表的合并居中是对整个数据透视表起作用的，该功能跟菜单里

的合并居中不同。

5、每项后面插入空行

双击字段名称，布局，勾选“每项后面插入空行”，确定。如图12

字段名也）：梢目地区确定

分类汇总-

兽

，自动理）天

0计

均

宜

平

自走紧如大

最f

（）无®小f21

最

积i

乘

一

-

显一示空数据项&逸捷透视表字段布局

显示选顼

令孤冲

X以报表形式显示项目（!）

尾汇总

»以大纲形式显示项吕。

白素贞

26&I）在组的市部显了分于汇£5）

林诗音

国湾项箱的迓画

一小

367打印选试

通_，许

口每顶岛:■.摩“浦野J（5i/yuncjlirrfjcj

注意数据透视表不允许直接在表内插入空行，否则会影响它的完整性。

6、设置错误值的显示方式

选中透视表任一单元格，单击鼠标右犍，表格选项，格式选项，勾选对于错误值，然后在显示的

框内自定义内容（可以配合ALT+数字小键盘进行特殊字符的输入），确定。如图13

名称国工数摧透视展4格式选项

回列总计。回行总计.页面布局中垂直并排

。自动登用格式霆）口隐^的

仔列字段数庄i：x。c

子汇总项on

□台井标志瞠

匠对于错误值，显示⑦：咯叵对于

回保留格式位）

回母一打印页上重发项目空单亓.格.显示'I!中罟打印标题

（Q：）（［）

口川星号豺标跆匚总茧」

7、处理空白

处理数据项有空白的方法：选中透视表任一单元格，单击鼠标右键，表格选项，格式选项，勾

选“对于空白选项”，显示（即可填入内容进行十分明替换）

处理行字段内有空白的方法：先复制一个“（空白）”然后按CTRL+H查找替换，查找内容粘

贴（空白），替换为输入一个空格，点击全部替换。

8、设置单元格格式

A、行标题：可以换一个填充色进行填充、加粗。

B、汇总项：启用选定内容，换填充色进行填充，字体加粗，变换字体颜色。

C、数据区域：设置数字格式，选中表格，单击“千分位分隔符的数字”快捷键。如果想持久

保鲜数字格式，则用数据透视表的字段设置里的单元格格式来设置。如图14

裁据透视表字愤

源字段：数量

啪定

名祢耻）”吗跖

汇总方式伍”:

除藏凹

八

=

•均——A[数字⑹…]

尢

订

小

平[选项Q))>]

毅

鬃

、智能外观1、改变数值的显示方式（自定义数字格式）

A、全部选中，设置单元格格式。

B、双击计数项，数据透视表字段，数字，自定义，类型中输入，确定，确定。如果想让J变

成红色，则在自定义类型下面输入［红色］”即可。如图15

单元格格式

数字

始类您片

第规数计数项:理程

值

专

径

用

登类型5

期

甘

时

间

为3

百

遇

用

比G

0.礼0oo

科学记政mj

的ffljC

特殊

WG*一律，料0：+V-ttj"ooZ：*-t.S#c-iJ0w

如果想在纯数字后面加之某字，比如102班，即在自定义类型下面输入G/通用格式“班”

确定，确定。注意：自定义数字格式只能在有数字的字段用。因为文本字段菜单中没有此

项显示。如图16

单兀格概式

数字

桃

示例

币

评班^

期

类型。:

向

册

数

卖

比.叫通用格V]

本

巨式

味

数n

常花,敞

规

数

值札眦W

货

*：花踊一：痛一：施”

I—匕从•to・，・r

2、隐藏零值

A、工具菜单,选项，视图，勾选掉“零值”。这种设置法整个表格将不会显示零值。如图

17

选项

H启动任务箧格如回前辗栏旧工状态栏⑤0任第栏中的资口CD

批注

无迎〃只显示标识符（I）0批拄和标识符&

，全部显示如

0.显示占也符卫）V全部隐藏（1）

曲口选项口自动为■页符虹口公式⑪0

阿格线-（g）网格线颜色旧）：：

[Z行号列标但）回分0水平蹬动条（!）

级显云符号（W口长值叵垂直S1动条（V）.

您"一叵工作表标第

县动

窿表卸色园际保存诺误检查拼町检查安全性

丽重新计算貌辑1-S-3的匡助自定义序列

C、只希翼零值不在透视表中显示，并且浮现负数的需要标注出来：双击字段名，数据透视表

字段选择数字，数值，选择负数用红字显示格式，点击自定义，然后在代码的后面输入

0.00;[红色]-0.00—，确定，确定。如图18

L元格格式

褥

分类示例

求和项:数量

管安渐类型⑴:

睛比0.00一【红色】-0.0J;|

馥就

。

O

.

#

，

构

-•一«m_：_t”

-00；_♦oo_▼

自定义格式说明：

1、如果只定义一个段，则该代码用于所有的数字上。

2、如果只定义两个段，第一段用于正数和0,第二段用于负数。

3、如果只定义三个段，第一段用于正数，第二段用于负数，第三段用于0值。

4、如果定义四段，第一段用于正数，第二段用于负数，第三段用于0文本，第四段用于文本。

有空还得补补这方面的知识。

3、突出显示符合条件的数值

限按数值大小：500以上用红颜色表示，500以下用蓝颜色表示。

方法1：自定义格式设置，双击字段名，数字，自定义类型下面输入[红色][>500]G/

通用格式；[蓝色][<500]G/通用格式。如图19

务施格式

数字

分类C)：示例

常

规合同金额

直

数

币类型C1J：

货

计

专

用

期红色］［瓯Q］也通用格用_匚蓝.

育-Ug).

比

间故/下午h•时苗怎F秒"’A

数

时

分

记■7orn*］(ih：mm

教

百Him«g

学nrii:0

分

W本

科［h］：ITim：55

.费MO.

方法2：条件格式设置，选中数据区域，格式，条件格式，单元格数值，大于，500,格

式，颜色，蓝颜色，确定。添加条件，单元格数值，小于等于，500,格式，颜

色,红颜色，确定,如图20

条件格式

条件］①_____________

单元格数值v大于7500M

替驻器信用AsBbCcYyZz精式⑭..

条件维)

单元格数值也小于或者等于丁|5叩*-尽

条世我真时>待用.......［谜瑚,布...：

格式加走:四祈示：AaBbCcYyZz迎.一・.展

••

添加画21】，册1除但匚〜1L确定取销

3、按目标值差距

数据透视表中的某一列数值与某一个目标值比对的结果，根据这个结果去做相应的突出

显示。选中合同结束列，格式，条件格式，公式，=E5GH$1,格式，图案绿色。如图21。

恭加(A)»:删除负(•••确定职消

选中合同结束列，格式，条件格式，公式，=E5-“2022-9-30”<0,格式，图案绿色。

4、永恒的边框

传统透视表格式的风格里，通过手动添加边框线，刷新后就没有了。可以通过条件格式

发置自动地为表格加之边框线。

A、寻觅每行都有数据的列与每列都有数据的行

B、设置条件格式

方法：选中透视表,格式，条件格式,条件1,公式后面输入=and（$透0"”，

A$50"”）当前面两个条件成立时就应用后面设置的格式确定。如图22

条件格式

条件1（D一，

公式.=and&K5"，出

买件为其时,待用

格式力阻图所示：AaBbCcYyZz

[添加力）»]（册赊④…][~~S

使用条件格式，简单易行无需代码，但是如果行字段或者列字段中有“空项目”，那末数据

透视表的公式就不像下图中那末简单了，或者是改变透视表布局，比如添加字段后透视表区域将

“扩大”，那末新增加的单元格区域无法自动添加条件格式，因此没有“永恒边框”，也就是说

这和实现方法无法自适应扩展。

D、问题：如何创建通用的条件？这个下去再想。

活动单元格一定要是K22

四、格式保鲜

1、注意透视表美化后的格式丢失问题

A、尽量使用字段属性

B、借助条件格式

重点在于变化中边框的设置。

2、解决列宽问题

A、设置表格选项

透视表创建，设置列宽后，刷新数据后设置的列宽就没有了，要让它刷新数据列宽不变打

以去掉自动套用格式勾选，再刷新的时候，列宽就不会变化了。

这是最简便的一种设置列宽的方式，但是从此以后就不能再使用透视表的自动格式了。如

图23

数据透视表选项

名称如：数据透视表।

旧列总计⑥页面布局〃、）：睡宜并排

回行总计①..每41字段数

口担劫月用糖式低2・

口囹藏的子汇总项也）WI合并标

□对于错误佰I显示①;

志（M）

回佻留格式虫）回对于空单无格.显示也

间等打ER页上重更邛I目（Q，口谩置打印标题ci）

B、利用空格

选中自动套用格式选项,可以在列字段名称两侧加空格来达到列宽的固定，它是按列字段的

名称长度来调整列宽的。如图24

数掘1-1

禁售地区T销售人员，