Excel2007数据透视表从入门到精通

Excel2007数据透视表精讲本节主要讲述以下内容：数据透视表功能解析透视表的功能透视表的概念使用透视表的步骤透视表的相关编辑数据透视图……由于本人水平有限，在讲解过程中难免有误，希望广大网友抱着批判的态度阅读，更希望能及时的指正引言我们在工作当中经常会碰到：将目标数据从大到小的排序显示我们想要的结果，并且隐藏不需要的结果汇总出我们需要的数据你的要求可能如下所示（一）：将销售额从大到小或从小到大的排序找到所有已经付款或未付款的数据将所有的相同的省份或城市集中在一起将名字相同的销售员工集中在一起你的要求可能如下所示（二）：只显示河北省的数据其它数据隐藏只显示09年的销售记录其它数据隐藏只显示春兰空调的销售记录其它数据隐藏只显示于宝富的销售记录其它数据隐藏只显示河北邯郸的春兰品牌的销售记录你的要求可能如下所示（三）：统计2年中的总销售额只统计2010年的总销售额统计辽宁的两年之中的总销售额统计不同省份的两年之中的总销售额统计每个员工的两年之中的总销售额统计付款和未付款的个数你的要求可能如下所示（四）：同时处理不同工作表或不同工作簿中的数据同时统计08、09、10年的三年的销售额同时统计不在同一个工作表四个季度的最大值……以上方法的小总结你的要求总是在不断的变化之中总是结合excel提供的不同的功能实现运算总是需要反复的撤销恢复源数据最终被你整理的“面目全非”工作效率低下如何在处理数据时不破坏源数据方法如下：备份源数据设置明显的数据还原辅助列（比如有序的编号列）关闭不保存如何在不破坏源数据的情况下又能实时的比较随意的获取某些结果？数据透视表初识透视表数据透视表是excel的一个重点、难点是衡量Excel掌握熟练程度的一个指标数据透视表是集排序、筛选、组合、汇总等多个功能于一体的集合体它是一种交互式报表它有强大、灵活的处理数据的功能熟练掌握该项功能能给你的工作效率显著的提高透视表概念的由来一说到透视，我们经常会联想到医生通过仪器给不管穿着什么样的衣服的求医者透视病理，总能看到人的五脏六腑，从而得出某些症状；如果说衣服是假象，那得出的症状则是真相同样透视表功能可以将看似随意组合的数据得出某些结果出来，这些随意组合的数据我们可以看做是假象，得出的结果可以看做为本质。透视表功能就是可以透过放置在特定表（特定区域）上的数据，拨开它们看似无关的组合得到某些内在的联系，从而得到某些可供研究的结果，这就是透视表概念的由来。一般刚接触这个功能的对它的概念理解起来比较困难，这个就像office中的“宏”概念一样，“宏”概念不明确、不直观、难理解，不适合中国人称呼习惯同样透视表的概念不很直观、不形象、不明确采用透视表汇总以下数据要求汇总出2年内的总销售额、最大、最小、平均销售额总共卖出空调的数量（总台数）单价空调卖出最高值汇总出：每个省份的总销售额并进行相关的排序、显示和隐藏操作每个城市的总销售额并进行相关的排序、显示和隐藏每个人的2年内的总销售额、销售数量（销售的台数）、销售数量+销售额只显示2009年的每笔销售额显示明细数据：每个省+每个人的销售额每个省+每个城市+每个人的销售额每个省+每个城市+不同日期+每个人的销售额总结：透视表的主要特性保护源数据上的安全性操作上的灵活性（随意组合不同的字段）处理不同源数据上的强大性（暂时不讲）功能上的综合性透视表概念的另种称呼虽然透视表集“万千宠爱”于一身，但是它更多的功能侧重于分类汇总不严格来说：数据透视表就是一个不破坏源数据的、操作灵活的、功能强大的分类汇总，透视表就是分类汇总功能的进一步的延伸如果让我给透视表再起另一个概念的话我会称呼它为：自由汇总表自由：突出了它的灵活汇总：突出了它的功能初学者会感觉该功能高深莫测、学起来时无从下手快速驾驭透视表功能的前提条件要想快速的掌握好数据透视表的功能需要首先掌握以下方面的知识：排序的知识筛选的知识组合的知识分类汇总的知识透视表适用的场合不能破坏源数据（维持数据的原貌）你的要求总是处于不断的改变之中对数据的处理结果要求并不精细数据量很大的情况下数据跨表、跨簿……使用透视表功能的必要性探究透视表不是万能的并不是任何时候都必须使用透视表数据量很少的情况下对数据处理要求很简单的情况下只进行简单的排序、简单的筛选……这个时候就没必要用该功能大材小用杀鸡焉用牛刀用它反而使问题复杂化数据透视表功能的局限性虽然该功能可以随意的、实时的、不破坏源数据的汇总出我们想要的某些数据，但是这些汇总出来的结果往往仅限于：最值、均值、计数……对数据的要求进一步的多样化上：数据名次的精确排位上数据的智能化判断上数据的频率分布上……这些需要借用Excel函数才能来完成数据的来源及规范性探究要想使用透视表功能，必须要有数据，我们首先应掌握如下：确定数据的来源数据源的规范性探究确定数据的来源数据表中数据的来源内部外部内部同表同一个工作表异表同簿但不同表内部同表异表外部AccessSqlserver记事本（文本文件）其它的工作簿…外部accessSqlserver记事本工作簿数据源标准化要求虽然excel中可作为透视表的数据来源多样，理论上来说excel中的数据均可用透视表功能加以分析处理，但是excel对数据源也有一定的要求，这些要求主要体现在数据的：规范性多样性单纯性重复性数据源的规范性有明确的字段（数据列标题、列标）尽量不要缺少、多余、重复必要的字段有明确的字段（数据列标题）一个规范、完整的数据区域是由字段+（一条或多条）记录构成的这个时候我们可以将该数据区域称之为数据清单字段每一列的最顶端一般都有一个标题，称之为列标或列标题，专业术语称之为字段通过在该列设置字段表明了该列数据的大致内容（明确的列名）每个字段反映目标对象的某个属性，字段设置的越多则描述对象越完整数据源的多样性（数据列的多样性）一个数据源可能是有多列多行构成的，但所有的数据列尽量不要使用格式上相同的数据比如说：全部使用数字列、全部使用文本列，如果是那样虽然也能汇总出结果出来，但汇总方式的种类有限，不能很好的体现出透视表的功能数据列的格式最好使用文本列（日期列）+数字列数据源的单纯性同列中的数据格式要尽量统一比如：文本列中尽量不要包含数字、日期数字列中尽量不要包含文本、日期数据源的重复性作为数据源的数据列（某列、某些列）中的数据要有必要的重复性比如：文本列中含有多个重复的数据日期列中含有多个重复的日期尽量避免列中的数据全部相同或全部不相同的情况（尤其在文本、日期列中的数据）数据透视表其实就是分类汇总功能的延伸，不严格来说它就是高级的分类汇总分类汇总功能就是将重复的数据汇总在一起，如果你的数据全部相同或全部不相同则不能很好体现出它的意义，一般：文本列、日期列是主管分类的列数字列是主管汇总的列总结：没有重复就没有分类，没有恰当合适的分类就无法生成想要的汇总结果数据透视表讲解之（四）选定数据源生成透视表界面概述数据源的来源我们已经掌握，数据源的规范性已经熟悉，接下来使用透视表功能使用透视表功能先期一般分两大步骤选定数据源生成透视表选定数据源先选中需要的全部或部分数据源单击定位某点（Excel会自动识别和该点相邻的区域则全被选中）选定数据源时注意以下情况：尽量包含字段名称尽量不要包含合并单元格区域不能全部使用空白区域选中的行数不能太少（2行以上）可以选中任意一列（2行以上）不能选中不相邻的列（不能挑选）选中多个相邻的且行数相同的列可以选中区域内的连续的区域（两行以上）不能使用空白单元格选中的信息必须超过≥2行以上生成透视表透视表位置上的选择同表（源数据和透视表处在同一个工作表内）异表（源数据和透视表不在同一个工作表内）生成透视表数量上的设置相同的数据源区域可以生成多个透视表同一个数据源的不同区域也可以生成多个透视表透视表状态下的界面认识界面总览透视区的认识选项卡的认识字段浮动窗口的认识界面总览一旦生成透视表，则默认会自动的进入透视表编辑状态（模式），在这种状态或这说模式下会出现如下“症状”：数据透视表区域选项卡数据透视表字段列表数据透视表区域（透视区）的认识（一）默认：会出现一个矩形的区域（透视区）该区域是建立在普通工作表上的一个带有特殊功能的区域（类似中国的经济特区）它是个占位符（或者说是个占位界面）默认该界面无法打印出来只能打印一个空白矩形框小提示：如何更改透视表名称区域顶端自动出现透视表的名称+编号它会随着新建透视表数量的变化而变化可以用以下方法更改透视表的名称透视表选项对话框数据透视表区域（透视区）的认识（二）该区域是用来存放数据、处理数据的无法直接输入信息，只能将字段列表中的数据置入其中一旦置入字段，则透视表默认界面消失，删除数据则界面恢复数据透视表区域（透视区）的认识（三）位置：默认左侧紧贴工作表行标签，顶部和上方列标签空出两行空白区域框选住所有的透视区可移动该区域的位置在透视区的最左端或顶端插入适当的空列或空行，迫使其改变位置不能只移动部分区域透视区认识认识（四）选中该透视区，通过剪切、复制等方法，可以将选中的透视区粘贴一个或多个名称不同的透视区可以在同一个工作表内粘贴多个也可以跨表粘贴多个还可以跨簿粘贴数据透视表区域（透视区）的认识（五）大小：默认界面不能强行拖动改变表区域的大小，但是它的大小会随着行高、列宽的变化而变化拖入到其中的字段的数量、种类、位置的不同而随之变化有的人说透视区的形状是个：变形金刚橡皮泥万花筒魔方小技巧：推迟布局更新的使用默认情况下，透视区会实时的随着字段置入的位置和数量变化而变化如何删除该项区域（一）？如何删除该项区域？刚生成的时候按撤销键即可清除（ctrl+z）拖拉选中全部区域（可多选其它非透视区域），然后按del键删除，会留下空白矩形框此时该区域为普通区域，可以直接输入信息字段列表框会消失如何删除该项区域（二）？通过全部清除按钮可以全部清除不能只删除部分区域（不管使用键删除）删除透视表所在的工作表亦可选项卡的认识主要用来编辑、修饰、美化透视表字段浮动窗口的认识窗口概览窗口的相关编辑字段列表框的认识控制窗口的认识双击窗口窗口概览上方为字段列表下方为字段控制窗口窗口中字段多少和位置的设置字段列表框中所显示的字段的数量和位置源数据的字段的多少、位置有关选中的生成的透视表的字段有关不能手动更改默认字段的有无和顺序字段位置的调整窗口的相关编辑窗口的外观改变大小位置显示和隐藏窗口的外观改变窗口大小的改变手工拖拉边框不能无限制的变大和变小窗口位置的改变拖拉窗口的顶端小提示：双击窗口的顶端拖拉窗口的顶端到工作表编辑区右侧时会将字段列表框嵌入到工作表内窗口的显示和隐藏临时（暂时、自动）隐藏单击非透视区时会自动隐藏，反之会自动显示永久（彻底）隐藏数据透视表解析之（五）上一节我们讲到选定数据源生成透视表的基本操作我们知道所谓的透视表只不过是建立在一个普通工作表上的一块含有特殊功能的（表）区域要想使用该区域提供的特殊功能处理数据，必须先将数据置入其中，这就需要掌握以下几方面的内容：必须使用字段列表框中的数据不接受手工直接输入的数据给谁添加？当含有多个透视表区域时目标要明确如何添加？有必要先熟悉一下字段窗口字段窗口的认识（07版本的称呼）行标签列标签报表筛选数值四个字段窗口的特点共性异性窗口的共性数据控制功能附加数据控制数据传送数据删除数据调控如何传送？有三种方法复选框添加法拖拉法右键选择法复选框添加法单击选中一个或多个需要的数据即可将选中的数据置入到透视区的相应位置字段