数据透视图的使用方法.docx

数据透视图的使用方法.docx

《数据透视图的使用方法.docx》由会员分享，可在线阅读，更多相关《数据透视图的使用方法.docx（26页珍藏版）》请在冰豆网上搜索。

数据透视图的使用方法

数据透视表的用法

1：

什么叫数据数据透视表？

   在学习excel数据透视表之前，我们有必要了解它的概念。

什么是数据透视表呢？

   理论上讲，数据透视表是excel提供的一种交互式的强大数据分析和汇总工具。

兰色把它总结为：

数据透视表是可以把一个明细表进行分类汇总，而且可以随意改变汇总模式的一种工具。

   明细表?

分类汇总？

随意改变模式？

工具？

神马东东？

听起来还是有点抽象，还是看图比较真观。

   销售明细表：

    根据地区汇总

   根据地区和销售员汇总

    根据地区、销售员、商品名称汇总

   根据月份、地区、销售员、商品名称汇总

好吧！

因为篇幅关系只展示这几种汇总方式。

也许你认为我函数水平高超，我会说我一个函数和公式也没用；也许你会说你是不是用了传说中的宏，对不起，我没写一句代码。

以上。

。

。

全是excel数据透视表实现的，而且展示的也只是数据透视表最最最。

。

基础的汇总功能！

！

2：

制作数据透视表

做数据透视表之前，我们需要了解以下两个问题：

   1什么样的表格需要做数据透视表

     数据透视表的用途如果细数会有很多，但最基本、最常用的是“分类汇总”，所以当我们需要对一个流水账式的明细表进行分类汇总时，就需要使用数据透视表。

数据透视表的源数据即是“明细表”。

如第1讲中的销售明细表。

    2什么样的格式才能做出数据透视表

      在制作数据透视表，需要注意以下几点：

1空字段名包括空列，无法制作数据透视表   

2相同的字段名，会自动添加序号，以示区别   

3字段所在行有合并单元格，等同于空字段，也无法创建数据透视表   

4如果有空行，会当成空值处理 

    如果数据源表不规范，需要处理一下才能制作数据透视表，数据表的整理我们将在第3讲介绍。

   下面我们就要亲手制作数据透视表了。

   原材料：

销售明细表（点微信右下角阅读原文下载示例表格）

   制作步骤：

     第1步选取销售明细表，excel2003版 ：

数据菜单-数据透视表（Excel2010版：

插入选项卡-数据透视表 ）

     excel2003版

    excel2010版

     第2步执行“数据透视表”命令后，会进入数据透视表创建向导。

（excel2003和excel2010稍有差异，但大体选项是一样的）在向导的第一步一般是默认选项，不需要设置，直接点确定即可（excel2003版在第一步点“完成”按钮）。

     第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体的房间位置。

    添加数据透视表项目

       A把地区拖动到行标签

       B把销售量和销售金额拖动到数值区域。

    注意：

excel2003版和excel2010添加方式不同，是选取字段，直接拖到数据透视表中的相对位置。

如下图所示。

   拖动完成后，一个数据透视表的雏形已展现在我们的面前，如下图所示。

3：

源表的整理

当你制作成数据透视表之前，你需要看看你的明细表是否存在下面的问题：

    1存在空列或没有行标题。

     影响：

明细表如果有标题为空，将无法制作数据透视表。

     整理:

把标题补充完整。

    2存在相同的标题。

     影响：

数据透视表会自动添加序号以区分

     整理：

尽量不要存在相同的列标题。

   3存在合并单元格

     影响：

合并单元格除第1个格外，其他均作为空值处理。

      整理：

取消合并单元格，填充完整。

   4存在非法日期。

     影响：

生成的数据透视表中，无法按日期格式进行年、月、日格式的筛选和组合。

     整理：

转换成excel认可的日期格式。

方法：

选取列-分列-第3步选日期

   5存在文本型数字

     影响：

文本型数字将无法在数据透视表中正确求和。

      整理：

转换成数值型数字。

方法:

选取列-分列-完成

综上所述，一个无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据表，才是一个标准的数据透视表的数据源。

4：

透视表的结构

操作数据透视表，我们需要先了解一下数据透视表的结构。

  1字段列表。

明细表的所有第一行列标题都会显示在“字段列表”中。

相当于数据透视表的原材料基地。

  2字段设置区域。

把要显示的字段拖放在相应区域内。

相当于数据透视表的加工厂。

加工的方式把相应的字段拖入。

   3数据透视表的显示区域。

组合成的数据透视表会在这儿显示。

     A报表筛选区域（excel2003版称为页字段）。

如上图所示，可以按“月”筛选数据透视表要显示的内容。

     B行标签（2003版称为行字段）。

该区域的字段会上下排列显示，如上图中“销售地区”和“销售员”字段。

     C列标签（2003版称为列字段）。

该区域的字段会左右排列显示。

如下图中的“地区”。

     D数值。

要统计的数据一定要放在该栏内才会进行求和等统计。

如上图中的“销量”。

   字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果。

而且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。

同学们可以拖动转换一下各个区域的字段内容，同时观察数据透视表的变化。

5：

值的计算方式

上讲学习了数据透视表的基本结构，其中一个重要的组成部分是“数值”区域。

我们需要把要求和的数值拖放到“数值”框内进行求和，如下图所示。

可有时结果却显示“计数”。

     原因：

如果把含文本格式内容的列拖放到值区域，统计方式默认“计数”，显示为“计数项：

字段名”。

本例中，一是误把销售员放在了值区域，二是在销售量列可能含有文本型内容。

    处理方法：

把销售员从值框中拖出去（删除），然后双击“数量”列标题，在弹出的“值字段设置”窗口中，把“计数”修改为“求和”，如下图所示。

结果如下图

    从上图也可以看到，数据透视表值的统计方式，不仅有求和、计数，还可以进行最大值、最小值、标准偏差等计算。

    本讲最后介绍一个小技巧，上图中，如果你想把“求和项：

数量”改成“数量”会提示无法更改，解决方法是可以在字段名后添加一个空格。

即“数量”。

6：

报表布局

 生成数据透视表后，如果你是excel2003以上版本，你会看到多列的内容会显在一列中，如下图所示。

   如果你想让列分开，就需要调整数据透视表的报表布局。

选取数据透视表-在顶部找到“设计”选项卡-再打开报表布局下拉菜单，会看到5种不同的布局方式：

  1压缩式布局。

如本文开始所示。

  2大纲式视图。

特点标题分列显示。

汇总显示在每项的上方

  3以表格显示形式。

特点：

标题分列显示。

汇总显示在每项的下方

  4重复所有项目标签。

可以填充相同的内容。

大家注意下图和上图的区别。

   5不重复所有项目标答。

取消重复。

是4的逆操作。

7：

添加和删除汇总项

 默认情况下，在数据透视表中添加行或列都会自动添加汇总项，如何调整隐藏或显示汇总行/列呢？

   1总计行

    总计行分为行总计和列总计，如下图所示。

    控制它隐藏和显示的命令在数据透视表右键-选项-如下图所示。

（2003版在数据透视表上右键-表格选项-行和列总计选中）

  2分类汇总行

     分类汇总主要是指行或列标签的分类汇总，如下图所示。

     想隐藏汇总，在数据透视表相应的列上右键，取消“分汇汇总..”勾选，如果要恢复操作相同。

8：

穿上漂亮的外衣

新创建的数据透视表，就是一个无任何美化效果的表格。

今天学习快速给数据透视表穿上美丽的外衣。

   原始表格。

   操作步骤：

       1打开选取数据透视表-顶端数据透视表选项-设计-如下图所示。

       2点设计-其他-打开更多数据透视表模块，点击后即套用该模板。

   设置后的效果如下图所示。

9：

修改引用数据源

如果明细表中行或列数发生了变化，数据透视表一般是不会自动调整数据源的，而需要手工去调整数据源的数据范围。

方法是：

    excel2010版选取数据透视表-最顶端数据透视表选项-更改数据源-重新选取范围。

    excel2003版在数据透视表的右键菜单中，有“数据透视表向导”，打开数据透视表向导后再点“上一步”就可以重新选取数据源了。

   开始兰色说“一般”不会自动更改数据源，难道有“二般”的情况？

？

   还真有，方法就是把明细表设置为“数据表”（2003版称为列表），设置步骤是：

选取明细表区域，excel2010版插入-表格。

（excel2003右键菜单中“创建列表”）

   设置完成后，再次增加新数据或新列，数据透视表就会自动更新数据源了。

10：

数据透视表的刷新

当明细表更新数据后，以该明细表制的数据透视表也需要刷新才可以显示新的汇总数据。

   在数据透视表上的左键菜单上，点“刷新”即可完成数据刷新，如下图所示。

   如果excel文件中有很多数据透视表，可以添加“全部刷新”按钮。

方法是

   文件-Excel选项-快捷访问工具栏-左侧栏中找到“全部刷新”-点击“添加”按钮即可添加到“快捷访问工具栏”如下图所示

   点击“全部刷新”命令可以刷新本工作薄所有数据透视表，如下图所示。

11：

数据透视表的移动和删除

一移动和复制

    选取包含数据透视表的区域，把光标放在选取区域边缘后，直接拖动到指定的区域放下即可。

如果在拖动时按ctrl键。

移动变为复制数据透视表。

  二删除

    选取包括所有数据透视表元素的区域，包括透视表筛选区域和行列及数值标签区域，按del键即可删除。

12：

筛选数值项

数据透视表中如果需要对值进行筛选，我们无法在值上面找到筛选按钮，如下图所示。

   要筛选值，我们借助行标筛选的筛选值功能。

比如筛选数量大于30的行。

  步骤：

      打开最右一个行标签的按钮，筛选值-大于-输入30，点确定后即可筛选出我们需要的结果。

设置筛选条件

   筛选后的结果如下图所示。

  另：

选取“求和项：

单价”右边单元格-数据-筛选，即可使用excel的自带筛选功能。

如下图所示。

13：

固定数字和表格格式

在数据透视表中，好不容易设置的数字格式，以及列宽，在刷新后全部恢复了原状，设置的格式全部消失。

怎么才能固定格式呢？

  解决方法：

       在数据透视表上右键-数据透视表选项-去掉“更新时自动调整列宽”，勾选“更新时保留数字格式”

     可能大家发现，即使进行了上面的设置，更新时设置的数字格式还是会变。

比如下图中，设置了广州的数字是“千位分隔符”样式，但地区换成“苏州”时，还会有的数字非千位分隔符样式。

    解决上述问题的方法：

把地区放在行标签，然后统一设置数字的格式为千位分隔符样式。

然后再筛选所有地区，数字格式均为千位分隔符样式。

14：

选取

数据透视表有值、行标签等元素构成，怎么能快速选取它们呢？

  1全选数据透视表

     光标放在数据透视表上，最上面数据透视表工具-布局-选择-整个数据透视表。

（excel2003版在右键菜单中就有选择的子菜单）

  2选取值或行列标签部门。

    在第1步中我们发现除了全选外，其他的都是灰色。

这是因为当你选取的范围内包括值等元素时，这些才能用，所以要选取这些内容，先全选，然后再选值或行列标签。

  3选取某行、某列。

    当你把光标放在数据透视表的某行或某列前面时，你会发现光标变成了黑箭头。

这时你只需要单击就可以选取所有和本行列有关的行或列了。