百变数据透视表

OFFICE能力提升课程百变数据透视表制作：蔡曙东2014年4月根据卢景德《EXCEL2007数据透视表应用大全》改编制作初识数据透视表什么是数据透视表呢？数据透视表其实就是基于数据源记录表根据不同需要生成的不同报表。记录表是用来记录原始数据的，而数据透视表则是用来分类汇总的分析报表。在开始介绍数据透视表之前，先来仔细看一看后面的二张销售记录表，请大家分析思考一下，这两张表你认为哪一张表做得更好、更合理？如果是你做记录表，你会做成哪一种形式？数据透视表的用途—记录表A数据透视表的用途—记录表B数据透视表的用途数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并做出有关企业中关键数据的决策。数据透视表的用途以友好的方式，查看大量的数据表格。对数值数据快速分类汇总，按分类和子分类查看数据信息。展开或折叠所关注的数据，快速查看摘要数据的明细信息。建立交叉表格（将行移动到列或将列移动到行），以查看源数据的不同汇总。快速的计算数值数据的汇总信息、差异、个体占总体的百分比信息等。对数据源（记录表）的要求若要创建数据透视表，要求数据源必须是比较规则的数据，也只有比较大量的数据才能体现数据透视表的优势。如：表格的第一行是字段名称，字段名称不能为空；数据记录中最好不要有空白单元格或合并单元格；每个字段中数据的数据类型必须一致（如，“订单日期”字段的值即有日期型数据又有文本型数据，则无法按照“订单日期”字段进行组合）。数据越规则，数据透视表使用起来越方便。正确的数据源记录表数据透视表的作用前面这张表大家觉得怎么样，看起是不是很累？我们怎样才能方便地找出表中的下列信息呢？1.每种产品销售金额的总计是多少？2.每个地区的销售金额总计是多少？3.每个城市的销售金额总计是多少？4.每个雇员的销售金额总计是多少？5.每个城市中每种产品的销售金额合计是多少？……数据透视表能帮你解决这些问题！选择数据源中任意有内容的单元格或整个数据区域。选择“插入”选项卡。单击“数据透视表”命令。创建数据透视表132创建数据透视表选择要分析的数据—自动选中，也可人为选择区域。选择放置数据透视表位置。单击确定。54选择“新工作表”时系统会自动建立1张新表；选择“现有工作表”时，需要在当前工作表中选择放置数据透视表的位置。6创建数据透视表OK！一张空的数据透视表就这样建好了！可这是空的呀，怎么往里添加数据呢？往下看！数据透视表字段的使用将字段拖动到“行标签”区域，则此字段中的每类项目会成为一行；我们可以将希望按行显示的字段拖动到这个区域。将字段拖动到“列字段”区域，则此字段种的每类项目会成为列；我们可以将希望按列显示的字段拖动到此区域。将字段拖动到“数值”区域，则会自动计算此字段的汇总信息（如求和、计数、平均值、方差等等）；我们可以将任何希望汇总的字段拖动到此区域。将字段拖动到“报表筛选”区域，则可以根据此字段对报表实现筛选，可以显示每类项目相关的报表。我们可以将较大范围的分类拖动到此区域，以实现报表筛选。仅使用行标签的简单数据透视表数值金额求和行标签产品名称交叉数据透视表（行标签+列标签）行标签列标签如果将不同字段分别拖动到行标签区域和列标签区域，就可以很方便的创建交叉表格。筛选字段的使用将地区作为筛选字段，产品名称作为行标签、城市作为列标签，生成的数据透视表如下图筛选字段选择不同的地区看看结果如何？使用数据透视表查看摘要与明细信息要查看每种产品由不同雇员的销售情况。其方法：将“雇员”字段拖到行标签区域的“产品名称”的下面。通过点击“+”/“-”来展开/折叠明细信息。使用数据透视表查看摘要与明细信息如果要想查看下表中白奶酪在各城市的销售信息，只需要双击白奶酪在各城市销售的总计数（Z14单元格）1双击使用数据透视表查看摘要与明细信息系统将生成一张新的工作表，此工作表详细地列明了白奶酪在各个城市销售的信息，这张表类似于筛选操作，这种透视表的优点在于可以单独保存并不改源数据表的显示。2生成明细表组合数据—组合日期数据下图是按日期列出的销售信息，这样列出的数据太多，看着很累，如果想要按年汇总列出，应该怎么做呢？1.单击订单日期任一单元格2.选择数据透视表工具的选项卡3.单击将字段分组工具4.选中月、季度、年5.单击确定组合数据—组合日期数据此时数据透视表就按年、季度、月分组汇总（如下图）。注意：如果仅想按月组合时，在分组对话框的步长选项中必须同时选中年，否则数据透视表将会把不同年份的相同月份汇总到同一个组！数值数据的分段组合再试一下按单笔销售金额大小组合数据。先以金额同时作为行标签和求和项建立一个数据透视表。然后按下图步骤进行组合。1.单击行标签字段中的任一单元格2.选择数据透视表工具的选项卡3.单击将字段分组工具4.设置起始为0，步长为10005.单击确定数值数据的分段组合下图即是按单笔销售金额的大小组合汇总的数据。注意图中0—1000中的1000实际意义是<1000，而1000—2000中的1000是=1000！对文本字段的分类组合除了可以对日期字段、数值字段的组合，还可以对文本类字段进行组合分类。如下图，将产品名称进行分类。1.按住Ctrl键，同时选择多个需要组合为同一类的产品名称2.选择数据透视表工具的选项卡3.单击将所选内容分组工具对文本字段的分类组合按前图所述操作后，数据透视表显示为下图所示。此时的组合结果命名为数据组1，数据组2……，可以直接在单元格内将名字改为产品分类名字即可，如谷类、点心等……数据透视表的数据汇总方式数据透视表的优势在于，我们可以很方便的从不同的角度，对数据进行不同方式的汇总统计。前面我们创建的数据透视表都是以求和的方式计算金额合计。那么当我们希望汇总的信息不是求和，而是计算平均值或者计数那么该如何处理呢？改变数据汇总方式比如，我们希望统计每种产品被销售的次数。可以将“产品名称”字段分别拖拽到“行标签”区域和“数值”区域，由于“产品名称”字段的内容是文本型数据，当把其拖拽到“数值”区域中时，汇总方式自动变为计数。如图所示：可直接在单元格中改为“销售次数”改变数据汇总方式如果我们希望查看每种产品的平均单价呢？这时照样还是将“产品名称”字段拖拽到“行标签”区域，而将“单价”字段拖拽到“数值”区域，由于“单价”字段的数据类型是数值型，汇总方式会自动为求和。而我们所希望的是计算每种产品的平均单价，那么该如何将汇总方式从求和改为平均值呢？2.点击数据汇总依据1.右键单击字段名单价单元格3.选择平均值改变数据汇总方式在汇总方式中有一共有11种函数，包括：求和、计数、数值计数、平均值、最大值、最小值、乘积、标准偏差、总体标准偏差、方差、总体方差。具体功能如下表：汇总方式功能求和对数值求和。是数字数据的默认函数。相当于Sum函数计数数据值的个数。作用与COUNTA工作表函数相同。“计数”汇总是数据（数字除外）的默认函数平均值求数值平均值。相当于AVERAGE函数最大值求最大值。相当于MAX函数最小值求最小值。相当于MIN函数乘积求数值的乘积。相当于PRODUCT函数数值计数数字数据值的个数。用与COUNT工作表函数相同。标准偏差估算总体的标准偏差，样本为总体的子集。相当于STDEV函数总体标准偏差计算总体的标准偏差。汇总的所有数据为总体。相当于STDEVP函数方差估计总体方差，样本为总体的子集。相当于VAR函数总体方差计算总体的方差。汇总的所有数据为总体。相当于VARP函数改变数据透视表的值显示方式通过改变数据透视表的值显示方式，可以帮助我们对数据按照不同显示方式进行比较。值显示方式具体含义如下表：值显示方式结果差异显示的值为与“基本字段”中“基本项”值的差。百分比显示的值为“基本字段”中“基本项”值的百分比。差异百分比显示的值为与“基本字段”中“基本项”值的百分比差值。按某一字段汇总显示的值为按照“基本字段”中的值连续累加的汇总。占同行数据总和的百分比显示每一行或每个类别的值相对于该行或该类别总计的百分比。占同列数据总和的百分比显示每一列或每个系列的所有值为该列或该系列总计的百分比。占总和的百分比显示的值为报表中所有值或所有数据点的总计的百分比。指数按下式计算值：((单元格中值)x(总计))/((行总计)x(列总计))改变数据透视表的值显示方式例如，要将求和项：金额数值显示为百分比数据的操作步骤如下图所示：2.点击数据透视表工具中的选项卡1.选中求和项：金额数据中的任一单元格3.点击字段设置5.选择6.单击确定4.点值显示方式选项卡改变数据透视表的值显示方式如下图，即是将金额的显示方式改变为占同行数据总和（本产品在各地区销售总额）的百分比。改变数据透视表的值显示方式假如我们突然改变主意，要想把销售金额显示为占同列数据总和（本地区各种产品销售总额）的百分比时，只需要按下图改变“值显示方式”的选项即可，其他操作与前面相同。改变选项在数据透视表中显示多个计算字段如果我们希望同时查看计算字段的百分比和金额合计能不能做到呢？答案很简单，只需要在字段列表中将“金额”字段再次拖到数值区域就可以了。此时数值区域添加了一个“金额2”，列标签区自动添加了一个“数值”字段。在数据透视表中显示多个计算字段前面的操作结果百分比和合计数是横向排列的；如果将数值字段从列标签区域拖动到行标签区域，就可以将百分比和合计数变成上下纵向排列了。注意：将数值字段拖放到产品名称上方和下方显示的结果是不一样的请自行拖放查看效果。不同汇总方式的数据比较用前面学过的方法，以订单日期作为行标签、以雇员作为列标签，建立一张透视表。并将订单日期按年、月进行组合。不同汇总方式的数据比较再次把金额字段拖到数值区域，并将字段名称分别改为当月发生额和金额累计。将汇总方式改为按某一字段汇总，基本字段选择订单日期。创建数据透视表公式在使用数据透视报表的时候，如果汇总函数和自定义计算（值显示方式）没有提供所需的结果，则可在计算字段或计算项中创建自己的公式。先了解什么是字段、项、计算字段和计算项：字段：数据表中的每一列，我们称之为字段。如：地区、城市、产品名称、金额等..项：字段里面的每个值，我们称之为项。如：地区字段中的东北、华北、西北；城市字段中的北京、上海、广州都称之为项。计算字段：数据透视表中的字段，该字段使用用户创建的公式。计算字段可使用数据透视表中其他字段中的内容执行计算。计算项：数据透视表字段中的项，该项使用用户创建的公式。计算项使用数据透视表中相同字段的其他项的内容进行计算。在数据透视表中使用计算字段先建立每月金额合计的透视表，然后按下图步骤操作：2.点击数据透视表工具中的选项卡1.选中数据透视表中的任一单元格3.点击公式4.点击计算字段5.输入“税金”6.输入公式8.确定7.添加在数据透视表中使用计算字段将计算字段“税金”添加到数值区域，数据透视表中就增加了一列“求和项:税金”，如下图：在数据透视表中使用计算项下图中显示的是每个雇员销售每种产品的金额汇总表。那么能否通过数据透视表计算雇员销量的平均水平是多少呢？我们可以通过数据透视表计算项来实现。先在“雇员”字段中添加一项名为“雇员平均水平”的项。具体操作步骤如下图：1.先将光标定位在雇员字段2.点击数据透视表工具中的选项卡3.点击公式4.点击计算项在数据透视表中使用计算项在对话框的名称位置输入“雇员平均水平”。在公式位置输入：“=AVERAGE(张颖,王伟,李芳,郑建杰,赵军,孙林,金士鹏,刘英玫,张雪眉)”。单击添加并确定。5.输入“雇员平均水平”6.输入公式8.确定7.添加在数据透视表中使用计算项“雇员平均水平”项即添加到数据透视表的“雇员”字段中。删除添加的计算字段或计算项若要删除添加的计算字段或计算项，在计算字段或计算字段对话框的名称中，选择要删除的字段，单击对话框右侧的删除按钮即可。2.选择需删除的计算字段3.点删除1.点击右侧三角4.点确认2.选择需删除的计算项3.点删除1.点击右侧三角4.点确认删除计算字段删除计算项利用计算项进行比较想要查看每个雇员与雇员平均水平的差异，可以通过更改值显示方式来实现。2.点击数据透视表工具中的选项卡1.把光标定位在值字段3.点击字段设置4.点击值显示方式5.基本字段选择“雇员”6.基本项选择“雇员平均水平”7.确定利用计算项进行比较下图即是每个雇员与雇员平均水平差异比较的透视表。数据透视表的数据源控制与刷新我们可以使用数据透视表功能，在数据源的基础上生成各种不同的报表，以满足不同层次的需要。但当我们使用数据透视表生成报告后，如果数据源变化了，数据透视表报告的信息会不会自动跟着变化呢？当更改数据源后，默认情况下数据透视表并不会自动更新。当数据源的值更改过以后，我们可以通过数据透视表的“选项”工具面板里的“数据”组的“刷新”命令来刷新数据透视表。这样数据透视表报告就变成最新的了。但是“刷新”命令只是刷新数据透视表所引用的数据源的值。当数据源的范围变化了以后（比如数据源增加了新的纪录）则使用刷新命令就不能将数据范围一起更新了。我们可以通过数据透视表的“选项”工具面板里的“数据”组的“更改数据源”命令来重新选择数据透视表所引用的数据范围。数据源数值改变后的更新2.点击数据透视表工具中的选项卡1.把光标定位在值字段3.点击刷新下拉箭头注意：用刷新更新的数据透视表只能用在源数据表只更改了数据的值，而未增加新的记录时用不用，当数据表增加新的记录后需要更新时的方法按下一页操作。4.点击刷新工具数据源增加新的记录的更新2.点击数据透视表工具中的选项卡1.把光标定位在值字段3.点击更改数据源右侧箭头4.点击更改数据源5.选中包含新增加记录的源数据表区域6.点确认