数据透视表使用.docx
- 文档编号:2135487
- 上传时间:2022-10-27
- 格式:DOCX
- 页数:24
- 大小:1.11MB
数据透视表使用.docx
《数据透视表使用.docx》由会员分享,可在线阅读,更多相关《数据透视表使用.docx(24页珍藏版)》请在冰豆网上搜索。
数据透视表使用
源数据:
1)报表字段的名称来自于源数据的列标题,因此,首先确保具有源数据中工作表第一行各列的名称;
2)标题下的其余各行应该包含同一列中的类似项。
例如:
文本应该位于一列,数字在另一列,而日期又在另一列;即包含数字的列不应该包含文本,诸如此类等等;
3)用于数据透视表的数据应该不包含空列;最好不要包含空行。
例如:
应该删除用于将数据块相互隔开的空白行。
注意:
各列中单元格格式,文字为文本格式,数字为数值格式或货币格式(在开始选项卡中部位置可调整格式)等等。
否则在创建数据透视表过程中会出现错误。
创建数据透视表:
1)准备好数据后,单击数据中的任意位置;
2)在“插入”选项卡上的“表”组中,单击“数据透视表”,选择其中的数据透视表,打开“创建数据透视表”对话框;
3)根据需要调整“表/区域”框中用于建立透视表的数据范围及工作表位置,点击确定即可。
如图所示,即为关闭“创建数据透视表”对话框后,在新工作表中看到的内容。
其中,左侧为数据透视表准备的布局区域,右侧为“数据透视表字段列表”,该列表显示来自源数据的列标题,如前所述,每一个标题都是一个字段:
如“销售人员”等。
4)通过将“数据透视表字段列表”中显示的任何字段移动到布局区域,即可创建数据透视表。
其中,字段移动方式有三种:
①选中字段名称左边的复选框;②右键单击字段名称,选择要将该字段移动到的位置;③直接将所需字段用鼠标左键拖放入“数据透视表字段列表”底部的框中,指定字段在报表中使用的方式;④使用区域节下拉菜单同样可以实现,如下图:
5)注意:
数据透视表会根据所选字段自动默认为:
不含数字的字段位于左侧显示为一列,而含有数字的字段将位于右侧的区域中;不管选择字段的顺序如何。
※注意字段中的值。
如果是在值字段中有一个空单元格,Excel将把该字段读作文本字段。
※如果某列中的所有单元格都含有数值数据,会默认选择求和,但如果只有1个单元格为空格单元格或者含有文本,Excel就会默认选择计数。
若需改变值汇总方式,只需在数值区域右键选择“值字段设置”选项,在其对话框中选择所需的“值字段汇总方式”即可。
※9种汇总计算函数:
1)求和
2)计数:
对所有单元格进行计数,包括数值、文本和错误的单元格,等价于counta函数
3)最大值
4)最小值
5)平均值
6)剩积
7)数值计数:
只计算数值单元格的个数,等价于count函数
8)标准偏差和总体标准偏差——计算标准偏差
9)方差和总体标准方差——计算统计方差
6)“报表筛选区域”常用来重点关注报表数据的子集,通常是地理区域、时间跨度或产品线等。
添加到报表筛选框之后,可以单独查看某个地区、某个季度或某个产品线的相关数据汇总情况,也可查看总体数据汇总情况。
7)根据实际需要,选中订购日期的复选框(将订购日期添加到行标签),单击日期的任一位置,点击“选项”—“分组”—“将字段分组”,或在日期单元格处右键选择“创建组”进行分组,可将日期按月、季、年等分组以便按固定月份、季度或年度统计数据。
注:
将订购日期添加到列表前更方便查看。
(可以直接在右侧框中将订购日期从行标签框拖至列标签框;也可以在日期处单击右键选择“移动”—“将订购日期移至列”选项)
(注:
后一种方法可实现多种形式的移动,包括“左移”、“移至开头”、“移至末尾”等等,方法均为右键选择“移动”选择其子选项即可。
)
其他:
1)关于行总计和列总计的显示与否问题
1透视表任意位置右键选择“数据透视表选项”—“汇总和筛选”选项(即TotalandFitters选项)—“显示行汇总”“显示列汇总”选项根据实际需要选择即可。
2选中“总计”所在的一行或一列,右键单击“删除总计”选项可删除总计的一行或一列。
3在“数据透视表工具”选项卡中,“设计”一栏的“布局”部分的“总计”选项下拉选项中有多种行总计列总计的显示方式,可以根据需要进行选择。
2)另外一种创建数据透视表的方式:
ALT+N+V+T(其中,N、V、T是依次按的,而不是一起按的)
3)数据透视表的内容要与数据源保持一致
随着时间的推移,数据也可能发生变化,并且增加新的行和列,然而,数据透视表报表所依赖的数据透视表缓存与数据源没有连接,因而不能反应对数据源所进行的修改,直到再一次更新数据源。
通过再次更新数据源更新透视表缓存的行为称为刷新数据。
在两种情况下,我们必须更新数据透视表报表:
对数据透视表中的现有数据已经进行修改
1当数据源仅仅是某个数据发生改变时,更新透视表数据只需在数据透视表中单击右键,选择“刷新”;或在上方“数据透视表工具”选项卡的“选项”一栏选择更新;
2当数据源的范围随着行或列的增加而扩大时,更新方法为:
在数据透视表内任何地方单击鼠标右键,然后,在上方“数据透视表工具”选项卡的“选项”一栏中选择“更新(更改)数据源”。
这一选择会引发如下图所示对话框,重新选择更改表/区域即可。
4)“数据透视表工具”选项卡—“选项”部分(清除功能):
①“清除筛选”选项——当透视表显示的是某筛选条件下的汇总数据时,该选项清楚当前筛选,将汇总变为全部汇总数据。
②“全部清除”选项——清除当前透视表中所有已选字段,以便在不删除透视表缓存的情况下从头开始重新对透视表进行布局。
5)重新定位数据透视表的位置:
选择“数据透视表工具”—“选项”—“移动数据透视表”,将出现如下对话框:
可以对数据透视表的位置重新进行定位。
6)工作表的数值部分一般要求不能有空单元格,对于数据透视表中的空格一般用“0”来填充。
具体操作为:
①在数据透视表内任选一个单元格;
②选择“数据透视表工具”选项卡—“选项”栏—“选项”—“选项”图标,出现如下对话框:
3在对话框的“布局和格式”选项卡上,“格式”部分,选择“对于空单元格,显示”并且在其后输入0,确定即可。
4完成后,在数据透视表中,空格部分均显示为0.
7)修改字段名称:
数据表中每个字段都有一个名称,列和筛选区域中的字段从源数据的标题继承,而数据部分的字段会被默认赋予“求和项:
××”类似的名称,有时候我们会用“总计”或其他名称来代替默认的名称。
方法为:
右键单击透视表的数值区中某一数值选择“值字段设置”,在“自定义名称”栏中输入一个新名称即可。
注意:
如果输入的名称在数据透视表中已经存在,则由于重复命名会失败,此时只需在命名字段的结尾处增加一个空格即可命名。
Excel认为有空格和没有空格是两个不同的名称,但是读者并不会注意到该名称后面的空格。
8)改变透视表的布局样式:
在“数据透视表工具”选项卡中,“设计”一栏的“布局”部分下有多种布局样式,可以根据需要进行选择。
注:
如果希望在后继分析中使用得到的汇总数据,表格形式的布局可能是最好的布局。
9)利用样式和主题自定义数据透视表的外观
1自定义样式
方法:
1在“数据透视表样式”样式库中,找到现有的一种样式,右击选择“复制”,Excel将显示“修改数据透视表快速样式”对话框
2为该样式选择一个新名称。
3在“元素”列表中,单击“第一行条纹”。
一个新的、称为“条纹尺寸”的部分会出现在对话框中。
4重复第二步,完成后,单击确定。
2右键单击“数据透视表样式”样式库中某一样式,选择“设为默认样式”可将某一样式设为未来数据透视表的默认样式;
3在“页面布局”选项卡中(鼠标可在sheet表中任意位置),打开“主题”下拉列表,可以选择更改透视表主题样式,如果只要求改变颜色或只要求改变字体,可使用“页面布局”—“主题”组中的“颜色”或“字体”下拉菜单对透视表的颜色或字体单独进行更改。
10)禁止分类汇总即不显示分类汇总的方法:
选中文字“××汇总”所在的单元格,在“数据透视表工具”选项卡中选择“选项”—“活动字段”—“字段设置”或右键单击选择“字段设置”,在其对话框的“分类汇总和筛选”选项下的“分类汇总”部分选择“无”,确定即可;反之同样过程选择“自动”即可显示。
最好的方法:
在“数据透视表字段列表”下区域节的某个字段,左键单击选择“字段设置”。
用同种方法可以通过自定义方法为某个字段添加多种分类汇总。
11)如果要创建累积总计或者将一项与另一项对比,就需要使用“值字段设置”对话框的第2个选项卡“值显示方式”。
方法是:
右击数据透视表的数值区中的某个数值并选择“值字段设置”,单击“值显示方式”下拉列表时,除“普通”选项卡外,还有8个选项可供选项:
占同行数据总和的百分比――将数据透视表一行的总计的百分比显示为100%
占列行数据总和的百分比――将数据透视表一列的总计的百分比显示为100%
点总和的百分比―――所有明细数据的总计的百分比显示为100%
差异―――显示一项与另一项对比或者前一项对比,二者之间的差异
百分比――将某一项的数值表示为另一项的数值的百分比
差异百分比――表示某一项相对于另一项的变化的百分比
按某一字段汇总―――计算连续项的重要性
指数――计算各项的相对重要性
12)利用差异选项显示年与年之间的不同,在“值显示方式”下拉列表中,选择“差异”。
因为要将一年与另一年的情况进行对比,所以要在“基本字段”中选择“Years”。
在“基本项”字段中有几个可实施的选项。
如果总是要将当年和上一年的情况进行对比,可以选择“上一个”选项,如果有若干年(月)的有价值的数据,并且都要和某一基准年(月),如2007年进行对比,则可以选择2007。
如下图同时给出了“值字段设置”对话框的设置和根据该设置所得到的报表。
该报表显示2008年1月的收入比2007年1月多5万5。
13)按“某一字段汇总”跟踪YTD总和
如果需要按月比较开始至今的总额,可以使用“按某一字段汇总”选项来完成;如果需要百分比,可使用“按某一字段汇总的百分比”选项;
注:
诸如此类等等,还可根据实际需要选择其他显示方式。
“行汇总的百分比”——确定每一行/列业务对总计的贡献大小;
“列汇总的百分比”——(季节性报表)特别适合查看业务的季节性;
“指数”——可以进行相当模糊的计算,Microsoft声称这种计算可以描述一列某个单元格的相对重要性。
14)将日期字段分组,可选多个,如:
同时选择“年”“季度”“月”显示如下
注:
1、“季度”和“年”已经添加到数据透视表字段列表中。
但我们不要被这种现象所欺骗,我们的数据源并没有发生变化而包括这些新的字段,这些字段现在仅仅在内存中数据透视表缓存的一部分;
2、默认情况下“年”和“季度”会自动添加到与数据透视表布局中的原日期字段相同的区域,如上图所示。
3、按月组合时包含年:
如果我们按月组合字段,总是应该在“分组”对话框中包含“年”,否则,就会将原始数据中每年的相同月算在一起,这不是我们要的结果。
4、按周组合日期字段:
在“分组”对话框中没有步长“周”,如果要按组合,方法是:
在数据透视表中任选一个标题,然后从“选项”选项卡选择“将字段分组”,在“分组”对和话框中,取消选中所有“步长”选项,而只选选择“日”字段,这样可以使“天数”旋转按钮可用,为了生成周报表,将天数的值由1增加到7,最后必须设置“起始于”日期。
在一个报表中组合两个日期字段
如果需要生成有两个日期字段的报表,并试图对两个日期字段按月和年分组,Excel将随机地把第1个组合字段命名为“年”,第2个组合字段命名为“年2”。
这样的命名约定不可避免地会带来混淆。
在这种情况下重要的是用有意义的名称重新命名这些字段。
15)重新排列数据透视表字段列表——在靠近“数据透视表字段列表”的右上角有一个小的下拉菜单,选择此下拉菜单可选择“数据透视表字段列表”的5种可能的排列方式:
使用字段下拉菜单:
16)对数据透视表中的数据进行排序,有三种方法:
①使用“选项”选项卡上的排序图标排序:
在“数据透
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据 透视 使用