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

1、本课主题：打造精美的数据透视表数据透视表最大的功能在于分析，但是我们在用数据透视表进行分析的时候往往是要把 做的分析给同事、老板、客户供应商以及相关人员看的。那么，在这个时候数据透视表就要 注重其仪表形象了。否则数据透视表的可读性就比较差，吸引人眼球的能力也就比较差，所 以一张养眼的数据，透视表首先在外表上也许就能打动阅读者的心。下面就我们就来探讨一 下怎么样让数据透视表更美。一、试穿摩登套装：自动格式1、快速为数据透视表应用格式套用格式的两种方法：A、数据透视表工具栏里第二个按钮，设置报告格式，激活自动套用格式。如图1勤据逵视表型4幻311 . o a IB、EXCEL工具栏菜单，格式，自动

2、套用格式。如图 22、四类自动格式的区别与特点在自动套用格式里提供了 22种格式，其中有10种报表类型，10种表类型，1种传统数据 透视表，1种无格式。A、报表：报表格式的特征是它是一维表格，而且报表的优先级成缩进形态显示。在应用报 表格式之后，它会改变数据透视表的布局。如图 3品名Q愴售世区O销售人扇Q E弟售金救北京白寮贞ZIZ&OO130110400赵琦E29L93ZOO北廉汇总B3350B400毕春他otI59500&0400杭州宝总15Q120000B表：是一个二维表格，没有缩进。应用表的这种格式只是改变了字体颜色、大小、样式、 边框等等，但是会保留数据透视表的原来的布局。如图4品名

3、FF1報步机县示矮雷售A匸-敷量柏售金醸M怙善金Ml數星愴肯金緩戡量愴售金白索穴2B&01280034S179360北京5631239600T34300013&11D4002325S7S1B3200詔SS7D0&06CC汇J&635W4QD瞒DO474eo&o?E61173541r42192&200344Q9EU1ai440319570K广州i盘471?67D0131fiin3S3C259TST69600M3X23500CS3S0400J.55&094&2:10._307M汇J5150Dlog.sinacorTjcfi/yongjinC传统：传统格式也就是EXCEL默认的格式，数据透视表创建好

4、之后就是这种格式。如果想让数据透视表的格式回到原来样子，就应用这个格式。如图5品名F1 数轄F按摩椅微淤炉销書比区-销售人员匸销售金额销售金做白隶贞2662123001345H&O563堆 38 8007343000133LUJ4U0I229583570C北京汇总&3350&40U5&312385004T42530&0厂州02B20D15161440厂州汇启4219262C01316UWD无：隐藏数据透视表的某些特征，比如去掉透视表数据区域上方的“数据”按钮和边框, 让表格看起来更象普通表格，这就是无格式的用处。如图6广州白素贞266212800136L101ODW193200&3350640

5、(|何庚销售全做寸向汇总 按摩荷 销售地区销售人员口 数壘畅光广州汇总Sfi步机数臺123M0微诚炉34573179360300092&2001E33&0092&20035TOO25306033113161440614403 自动格式的优点与缺点优点：A、快：只要选择一个你认为适合现在数透视表的格式就可以很快地应用到表格中。B 、自动、鲜：当套用了一个自动格式以后，无论透视表发生布局改变、刷新、内容 的筛选，套用的格式是不会受到影响。如果不是自动套用格式，那么无法达到与 透视表这种无缝结合的这个特点。缺点：A、样式太少了只有22个，容易应生审美疲劳。、不支持自定义。不能将自已设置好的格式保存下

6、来供以后使用、个性鲜明DIY 1、显示或隐藏透视表的行、列总计方法1:将光标停在汇总项的行（列）的靠左边（上边）位置，光标变为黑色向右（向下） 箭头时点右键，在弹出的对话框中选择隐藏（不要将光标停在行标签和列标签上）方法2:定位鼠标到任一单元格，单击右键，表格选项，去掉列总计和行总计前的勾即可 隐藏。（反之即可显示）如图7名称； 逛趣鼠格式选顶回列总计乎L0行总计40自动套用格式嚏藏的子汇总项00g合笄标恚加叼擁罟格式巴0斑一打ED页上重宦顶目页面布局QJ：重直并擀每列孚粽数便X对于常误俏，显示迪;叼对于空单元格显示农）； 设置打印标题值佰佰 和數均*小积 求计平最最乖2、显示或隐藏透视表的分

7、类汇总项隐藏分类汇总项的方法：A :把光标停在分类汇总项左边，当光标变成向右的箭头，单击鼠标左键，所有的分类汇 总项就都会被选中（要先启用选定内容功能），单击鼠标右键，弹出的菜单中选择“隐 藏”。B :双击要隐藏分类汇总的行字段名称，弹出数据透视表字段对话框，在分类汇总选项下 选择“无”，确定。如图8牙类汇总（?自动 C目走殳鮒 O无显示分类汇总项的方法:双击要显示分类汇总的行字段名称，弹出数据透视表字段对话框，分类汇总选项下选择“自 动”或“自定义”，确定。（自动就是求和，自定义可以改变当前的汇总方式，如计数等 等）3、显示或隐藏透视表的明细项隐藏数据明细的方法：A、单击想隐藏明细项的数据项

8、，鼠标右键，弹出的菜单选项选择“组及显示明细数据下 的隐藏明细项目。如图9B、双击想隐藏明细项的数据项，即可隐藏下一级的明细项。如图10销售地宜&销售金锁北京833506400广州炖+1J许三翳&350400杭州汇总1901200005443200咼启肓伟负216000324259?00|显示数据明细的方法：双击已经隐藏明细项的数据项，即可显示隐藏的数据明细4、合并居中显示标题内容合并居中方法：选中透视表的任一单元格，单击鼠标右键，表格选项，勾选合并标志。如 图11名称迈）；：数据透视表i顶质布局0J:垂直并排每列字段對a）:对于错误值j显希迪 巨对手空单元槁，昱示a设置打印标题CL格式选项0

9、列总计0行总计（P0自动奁用格式隐議的子汇总项憶）靜赢恙“十一叼毎一打印页上重复项目0）注意：在普通表格里最好不使用合并居中， 因为在进行数据分析或创建数据透视表时可能 遇到很多问题。数据透视表的合并居中是对整个数据透视表起作用的，该功能跟菜单里的合并居中不同。5、每项后面插入空行双击字段名称，布局，勾选“每项后面插入空行”，确定。如图12总动走遲 匸自自元 漠SOO=冏a确定336004?取消丨匸IE隐蕊0）IE（高级迪32七戸m邂007:布局屯）.gojnn丄丄1令汎冲汇总白素贞EQG林请B路小雨36T许他显示空数据项数据请观表字段布层显示选顶S以报養形式显示顶目.全韻显示如n显示占位符(

10、Vo全部隐藏窗口遼顼匚:自动分贡符0和号列标(E)0水呼嚴动条IX)公式回分级显示苻号Q)0垂直滾动条边回网格统聲詹1 4 0工作表标签叨岡格线颤邑C):自窃C、只希望零值不在透视表中显示，并且出现负数的需要标注出来：双击字段名，数据透 视表字段选择数字，数值，选择负数用红字显示格式，点击自定义，然后在代码的后 面输入0.00 ;红色卜0.00 ，确定，确定。如图18数宇求和顼:数量自定义格式说明：1、如果只定义一个段，则该代码用于所有的数字上。2、如果只定义两个段，第一段用于正数和 0,第二段用于负数。3、 如果只定义三个段，第一段用于正数，第二段用于负数，第三段用于0值。4、 如果定义四段

11、，第一段用于正数，第二段用于负数，第三段用于0文本，第四段用于 文本。有空还得补补这方面的知识。3、突出显示符合条件的数值A、按数值大小：500以上用红颜色表示，500以下用蓝颜色表示。方法1:自定义格式设置，双击字段名，数字，自定义类型下面输入红色500G/通用格式；蓝色500G/通用格式。如图19疣格格式规直币期间邠数抚秦 Z 常数益会日时百分科袴=示例方法2:条件格式设置，选中数据区域，格式，条件格式，单元格数值，大于，500,格式，颜色，蓝颜色，确定。添加条件，单元格数值，小于等于，500,格式,颜色，红颜色，确定。如图20家舟格式条件L，条件为克吋，持用 悔式如右阁所示：小于或尊于

12、T權式辽二马AaBbCcYyZs_删除远Li 陳定B、按目标值差距数据透视表中的某一列数值与某一个目标值比对的结果，根据这个结果去做相应的突 出显示。选中合同结束列，格式，条件格式，公式，=E5$H$1格式，图案绿色。女口图21。选中合同结束列，格式，条件格式，公式，=E5-“2007-9-30 ” 0,格式，图案绿色。4、永恒的边框传统透视表格式的风格里，通过手动添加边框线，刷新后就没有了。可以通过条件格 式设置自动地为表格加上边框线。A、寻找每行都有数据的列与每列都有数据的行B、设置条件格式方法：选中透视表，格式，条件格式，条件1,公式后面输入=and($A5” ”，A$5” ”) 当前面

13、两个条件成立时就应用后面设置的格式确定。如图22使用条件格式，简单易行无需代码，但是如果行字段或者列字段中有“空项目”，那么数 据透视表的公式就不像下图中那么简单了，或是改变透视表布局，比如添加字段后透视表区 域将“扩大”，那么新增加的单元格区域无法自动添加条件格式，因此没有“永恒边框”， 也就是说这种实现方法无法自适应扩展。D 问题：如何创建通用的条件？这个下去再想。活动单元格一定要是K22四、格式保鲜1、注意透视表美化后的格式丢失问题A、尽量使用字段属性B、借助条件格式重点在于变化中边框的设置2、解决列宽问题A、设置表格选项透视表创建，设置列宽后，刷新数据后设置的列宽就没有了，要让它刷新数

14、据列宽不变 可以去掉自动套用格式勾选，再刷新的时候，列宽就不会变化了。这是最简便的一种设置列宽的方式，但是从此以后就不能再使用透视表的自动格式了如图23回列总计 回行腔计CI）瞬瞬 hi i_i:.-iiia_：Bii-iii iiji!隱藏的务匚总顶 0合井标志（M） 回俣騁格式世）叵IflfTTEMI上重复顶目页甬祈局；垂直并排 *每列爭段数:a C时于错误值显示醴, 回对于空单元格昱示口： |设畳打E晦题B、利用空格选中自动套用格式选项，可以在列字段名称两侧加空格来达到列宽的固定，它是按列字段的名称长度来调整列宽的。如图 24北京销莒人员1二白素贞李兵北京汇总数居一数星在讴两停殳养后加空

15、相空迪。79Z2&911407S514431D0梢萝金輙*10158103071721&5PC、借助宏（终极武器）录制一段宏设置列宽，然后在工作表标签上按鼠标右键，查看代码，在工作簿名下双 击模块，复制右边的代码，然后点击左边工作表的名称，点击Worksheet，PivotTableUpdate （为每次刷新数据透视表执行），然后把代码复制到里面。如图25Tlsiial Basic - 永恒的表!艾件 鳴错世I视国电）骷入 務式 谓直担）运行 工具（I）外接程序 宙口如 翳助班）q対町jj j */.专：行s列日jQ*J3-YTAfroject-r 】:rasoftTi訂 Shtttl ftsbatts jt5 7 hi stfcrkboak:-吁檯按 徴模块1俅恒的我 Etcel对彗 (ShatUl HftswaB（名称SheUlEi Sipl ayP aeBz e -FLseDiEpla*K.LfhtTft FiLst EnfcbleAu + 4-?ilt FXi Enfabl*rtlcul tt Trui InbleCruill nin False nbleliyqLT bb.f4L.se:Enble- xHKoRtslr.永恒的表4&找亠x - Sheet 1代码）3、解除数据透视表的列数限制数据透视表创建多个字段的时候，行字段达