EXCEL第6章数据透视表和透视图

Excel数据处理与分析第6章数据透视表和透视图引言我们在工作当中经常会碰到：将目标数据从大到小的排序排序可以得出某种趋势显示我们想要的结果，并且隐藏不需要的结果筛选就是显示想要的隐藏不想要的汇总出我们需要的数据分类汇总就是按类别统计计算引言以上方法的小总结：你的要求总是在不断的变化之中总是结合excel提供的不同的功能实现运算总是需要反复的撤销恢复源数据最终被你整理的“面目全非”工作效率低下如何在不破坏源数据的情况下又能实时的比较随意的获取某些结果？-----数据透视表初识透视表数据透视表是集排序、筛选、组合、汇总等多个功能于一体的集合体它是一种交互式报表它有强大、灵活的处理数据的功能熟练掌握该项功能能给你的工作效率显著的提高透视表---可以透过放置在特定表（特定区域）上的数据，拨开它们看似无关的组合得到某些内在的联系，从而得到某些可供研究的结果，这就是透视表概念的由来。内容提要一、概述二、数据透视表创建数据透视表数据透视表的操作三、数据透视图创建数据透视图数据透视图的操作什么是数据透视表什么是数据透视图6.1概述数据透视表是Excel2007提供的一种交互式报表，是一种动态数据分析工具。可以根据用户不同的目的进行汇总、分析、浏览数据，得到想要的分析结果。在数据透视表中可以交换行和列来查看原数据的不同汇总结果。数据透视表数据透视图则是将数据透视表中的数据图形化，能方便地查看比较、分析数据的模式和趋势。数据透视图创建数据透视表的步骤更改数据来源刷新数据清除和删除数据透视表6.2创建数据透视表数据透视表的结构创建数据透视表的步骤这4个区域都可以包容一个或多个源数据表中的字段信息，但是由于它们的位置不同，所以它们的名称和作用则完全不同。页区域行区域列区域值区域在数据透视表的空框架中，一共有4个不同的区域:6.2创建数据透视表-数据透视表的结构作用是分类作用是汇总作用是分类筛选6.2创建数据透视表-创建透视表的步骤准备数据源创建数据透视表6.2.1创建数据透视表-准备数据源透视表数据源若要创建数据透视表，您必须连接到一个数据源，可以从下3种数据源创建数据透视表的方法：从Excel工作表中创建数据透视表从外部数据源创建数据透视表从合并计算多个数据区域创建数据透视表6.2.1创建数据透视表-准备数据源透视表数据源要求透视表对数据源规模的规定类别Excel2000Excel2003Excel2007记录数空间限制空间限制1048576列字段数25625616384透视表记录数80003250010485766.2.1创建数据透视表-准备数据源透视表数据源要求透视表数据源的格式要求标准数据库数据表要求规范的行列构成的二维表原始数据表必须有列标题设置不存在合并单元格不存在空格、空行、空列6.2.1创建数据透视表-创建数据透视表创建数据透视表的步骤：准备好数据后，请单击数据中的任意位置在“插入”选项卡上的“表”组中，单击“数据透视表”，然后再次单击“数据透视表”，打开“创建数据透视表”对话框：选择数据“表/区域”及放置数据透视表的位置单击“确定”。Excel将空的数据透视表添加至指定位置并显示数据透视表字段列表指定数据透视表框架中四个区域内的数据内容6.2.1创建数据透视表-创建数据透视表选择数据区域中的一个单元格。6.2.1创建数据透视表-创建数据透视表进入“创建数据透视表”对话框选择数据“表/区域”及放置数据透视表的位置单击“确定”，Excel会将空的数据透视表添加至指定位置并显示数据透视表字段列表，以便添加字段、创建布局以及自定义数据透视表。6.2.1创建数据透视表-创建数据透视表指定数据透视表框架中四个区域内的数据内容6.2.1创建数据透视表-创建数据透视表无论是哪个区域，操作都是相同的，都是将字段列表中的“字段名”拖拽至相应的位置即可。在拖拽这些字段至透视表时，是没有一个定式的，要根据数据分析的目的，选择需要的字段拖拽。但是由于各个区域的作用是不同的，在数据分析时，只有将数据拖到了合适的位置时，才能得到正确的结果，否则不会得到正确的结果，这确实需要些经验。指定数据透视表框架中四个区域内的数据内容每个字段都可以构成筛选，具体使用哪个字段需要考虑实际问题的需求：例如地区筛选，目的是需要提供在不同地区的客户订单状况说明行标签决定数据表需要显示那些明细数据内容，例如：应用客户字段作为行标签是为了说明每个客户的订单状况。选择作为列标签的字段，决定了每一行数据对象的信息需求，例如选择“客户”作为行标签，“订单交期”作为列标签，是为了得到每个“订单交期”每个“客户”的情况。在数值区域内指定对由“行”“列”标签所决定的全部数据所需要进行的数据计算6.2.1创建数据透视表-创建数据透视表6.2.2更改数据来源创建完数据透视表后，还可改变其数据来源，步骤：单击数据透视表在“数据透视表工具”→“选项”→“数据”组中，单击“更改数据源”按钮打开命令列表，选择“更改数据源”命令，将打开“更改数据透视表数据源”对话框要使用其他的Excel表或单元格区域，单击“折叠”按钮，选取表或区域6.2.3刷新数据更改源数据将导致用于分析的数据发生变化。用户可以通过刷新报表，使用与原始数据连接信息类似的新数据来更新数据透视表或数据透视图。操作步骤：在“数据透视表工具”→“选项”→“数据”组中单击“刷新”按钮，弹出下拉命令列表，选择“刷新”或“全部刷新”命令来刷新数据，如图所示。6.2.4清除数据透视表如果要将数据透视表框架中四个区域内的数据内容清除，然后重新开始设计布局，请使用“全部清除”命令。该命令可有效地重新设置数据透视表，但数据透视表的数据连接、位置和缓存仍保持不变。具体操作步骤如下：单击数据透视表。在“数据透视表工具”→“选项”→“操作”组中，单击“清除”，然后单击“全部清除”。6.2.5删除数据透视表数据透视表作为一个整体，要删除整个数据透视表，具体操作如下：单击数据透视表在“数据透视表工具”→“选项”→“操作”组中，单击“选择”按钮打开命令列表，选择“整个数据透视表”命令，即选择了整个数据透视表按Delete键删除即可数据透视表的基本操作数据透视表的筛选和排序数据透视表的计算多重合并计算数据区域创建数据透视表6.3数据透视表的基本操作数据透视表的字段操作移动数据透视表及显示明细数据筛选和排序数据透视表的项组合6.3数据透视表的基本操作-6.3.1字段的操作字段设置添加字段重新排列字段删除字段数据透视表字段的操作6.3数据透视表的基本操作-6.3.1字段的操作创建数据透视表之后，可以使用数据透视表字段列表对字段进行操作(如添加字段、删除字段、重新排列等)。默认情况下，数据透视表字段列表显示分为两部分：上方的字段部分：用于添加和删除报表字段，下方的布局部分：用于重新排列和重新定位字段。数据透视表字段的操作数据透视表字段的操作字段设置使用“字段设置”对话框可以控制数据透视表中字段的各种格式、分类汇总和筛选设置。字段设置在“数据透视表工具”→“选项”→“活动字段”组中，如图所示。6.3数据透视表的基本操作-6.3.1字段的操作例如：按学院统计各类不同职称的论文篇数的平均值。6.3数据透视表的基本操作-6.3.1字段的操作要将字段添加到报表，主要有以下几种方法：在“数据透视表字段列表”中的字段部分选中各字段名称旁边的复选框。字段放置在布局部分的默认区域。用鼠标右键单击字段名称，在“添加到报表筛选”、“添加到列标签”、“添加到行标签”和“添加到值”中进行选择，将该字段放置在布局部分的特定区域中。用户还可以单击并按住某个字段名，在字段部分与布局部分中的某个区域之间拖动该字段。数据透视表字段的操作添加字段6.3数据透视表的基本操作-6.3.1字段的操作例如：统计某个“客户”订购数量占总订购数量的百分比。6.3数据透视表的基本操作-6.3.1字段的操作6.3数据透视表的基本操作-6.3.1字段的操作用户可以通过使用“数据透视表字段列表”或利用“报表筛选”、“列标签”、“行标签”、“数值”4个区域之一来重新排列现有字段或重新放置这些字段，如图所示。用户还可以单击并按住字段名，在字段与布局部分之间以及不同的区域之间拖动该字段。6.3数据透视表的基本操作-6.3.1字段的操作数据透视表字段的操作重新排列字段要删除字段，可以在数据透视表中右键单击，使用快捷菜单中的删除命令删除字段。也可以在布局区域之一中单击字段名称，从打开的菜单中选择“删除字段”命令即可；或者清除字段部分中各个字段名称旁边的复选框。6.3数据透视表的基本操作-6.3.1字段的操作数据透视表字段的操作删除字段移动数据透视表用户如果要移动数据透视表，其操作步骤如下:单击数据透视表在“数据透视表工具”→“选项”→“操作”组中，单击“移动数据透视表”按钮,将显示“移动数据透视表”对话框在“选择放置数据透视表的位置”对话框中，如果选择“新工作表”，则将数据透视表放入一个新的工作表。如果选择“现有工作表”，则将数据透视表放入现有工作表6.3.2移动数据透视表数据及显示明细数据6.3.2移动数据透视表数据及显示明细数据显示或隐藏明细数据为了便于查看，可以在一个操作中显示或隐藏到数据明细的任意级别，甚至数据明细的所有级别。具体操作方法:单击数据透视表在“数据透视表工具”→“选项”→“显示/隐藏”组中，单击相关按钮即可显示或隐藏相关项目6.4数据透视表的排序和筛选-6.4.1筛选数据通过筛选数据可以筛选出符合用户指定条件的数据，并隐藏不想显示的数据。在数据透视表或数据透视图中，筛选是累加式的，也就是说，每次增加筛选都是基于当前筛选并进一步减小显示的数据。筛选标签值筛选按选定内容筛选删除筛选6.4数据透视表的排序和筛选-6.4.2排序数据对数据进行排序是数据分析中不可或缺的组成部分。对列或行标签中的数据进行排序对值区域中的数据进行排序6.4.3数据透视表中的组合项在数据透视表中,可以组合数字项、组合日期或时间和组合选定项。6.5数据透视表的计算数据透视表和数据透视图提供了多种计算方式。如果汇总函数和自定义计算不能满足自己的需要，则可利用Excel提供的计算字段和计算项中创建自己的公式。添加计算字段添加计算项显示公式列表编辑公式删除公式6.5数据透视表的计算计算字段与计算项简介：计算字段—通过对数据透视表内现有字段进行计算创建的一个字段,可以将计算字段看作是添加一个虚拟列到数据集中。计算项—通过对数据字段内现有的数据项进行计算所创建的一个数据项,可以将计算项看作是添加一个虚拟行到数据集中。有了计算字段和计算项,可以向数据透视表中插入公式，创建自己的自定义字段或者数据项。6.6多重合并计算数据区域创建数据透视表要汇总各个单独工作表区域中的结果，可将单独工作表区域中的数据合并到主工作表的数据透视表中。单独工作表区域可与主工作表位于相同工作簿中，也可位于不同工作簿中。合并数据时，将对其进行组合，以便更加轻松地聚合数据。6.6多重合并计算数据区域创建数据透视表不使用任何页字段合并数据使用单页字段合并数据6.6多重合并计算数据区域创建数据透视表单击任意一个单元格。启动“数据透视表和数据透视图向导”，按“Alt+D+P”组合键。在向导的“步骤1”页面上单击“多重合并计算数据区域”，然后单击“下一步”。不使用任何页字段合并数据6.6多重合并计算数据区域创建数据透视表在向导的“步骤2a”页面上单击“自定义页字段”，然后单击“下一步”。6.6多重合并计算数据区域创建数据透视表在向导的“步骤2b”页面上执行下列操作：选定包含汇总数据的工作表数据区域，然后单击“添加”。如果单元格区域在另一个工作簿中，请先打开工作簿以便选择数据。所有汇总数据区域依次添加完后，在“请先指定要建立在数据透视表中的页字段数目”下，选择“0”，然后单击“下一步