Excel数据透视表与数据透视图

Excel数据透视表与数据透视图

一、什么是数据透视表？

数据透视表是一种可以快速汇总、分析大量数据表格的交互式分析工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并简历交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。

使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并作出有关企业中关键数据的决策。

二、数据源

1、什么是数据源？

数据源是指：用于创建数据透视表的数据来源。可以是Excel的数据列表、其他数据的透视表，也可以是外部的数据源。

2、数据源的原则：

• 每列数据的第一行包含该列的标题
• 数据源中不能包含空行和空列
• 数据源中不能包含空单元格
• 数据源中不能包含合并单元格
• 数据源中不能包含同类字段（即既能当标题也能当数据数据内容）

三、创建数据透视表

1、创建数据源

2、点击菜单栏上的“插入”，选择创建数据透视表

点击确定后，创建数据透视表成功

创建数据透视表后，想要查看数据则点击右侧字段列表，先勾选哪个字段，哪个字段排在前面。

想要字段出现在列，则点击字段名，把字段名拖进“数据透视表区域”里的列区域里，字段就在列中展示。

四、数据透视表的基本术语和4大区域

1、基本术语

数据源：用于创建数据透视表的数据源，可以是单元区域、定义的名称、另一个数据透视表数据或其他外部数据来源。

字段：数据源中各列的列标题，每个字段代表一类数据。字段可分为：报表筛选字段、行字段、列字段、值字段。

项：项是每个字段中包含的数据，表示数据源中字段的唯一条目。

2、4大区域

行区域、列区域、值区域、报表筛选区域

五、数据透视表的基本操作

1、选择数据透视表及表中单元格、字段、项

2、重命名数据透视表

3、复制、移动和删除数据透视表

以上操作很简单，略

六、数据透视表字段列表窗格

1、打开/关闭透视表字段列表窗格

2、重命名字段

七、数据源更新

数据源修改了，数据透视表没有修改，这时候就需要刷新。

1、手动刷新

数据源区域未改变，如只是修改了字段的某个值。

当前sku-01的金额为59

将sku-01的金额修改为50

没刷新之前的数据透视表的sku-01还是59

刷新后的数据透视表的sku-01为50

数据源区域改变，如删除了数据源的某些行或列、添加了某些行或列，数据源的区域大小发生改变。

只需要将数据的前10条做数据透视表：

修改后的透视表

2、自动刷新

打开工作簿时，自动刷新

刷新后如何保持调整好的列宽？

选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 布局和格式 -> 取消勾选更新时自动调整列宽

3、如何清理垃圾条目？

当前数据源的类目为干果蜜饯、酒水饮料、母婴用品

数据透视表的类目也为干果蜜饯、酒水饮料、母婴用品

现将数据源中母婴用品类目删除

在数据透视表中刷新，母婴用品类目不见了。

但在类目筛选里面还有母婴用品类目，称之为垃圾条目。

清理垃圾条目，选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 数据 -> 在每个字段保留的项数里选择“无”即可。

八、数据透视表的三种布局形式

• 压缩布局形式
• 大纲布局形式
• 表格布局形式

九、改变数据透视表的外观

1、修改数据透视表的样式

选中数据透视表 -> 设计 ,红色方框里就是数据透视表的的样式

还可以自定义样式

2、修改数据透视表的主题

3、修改数据透视表样式三

在每组数据中插入空白行

合并数据透视表中的字段项（当数据报表布局为表格形式时）

十、设置分类汇总的显示

1、关闭分类汇总显示

2、自定义分类汇总

选中数据透视表 -> 右键 -> 字段设置

十一、设置总计的显示

十二、设置数字格式显示

1、设置数字格式显示

右键 -> 数字格式

2、设置空值和错误值的显示方式

选择数据透视表 -> 右键 -> 数据透视表选项 -> 布局与格式 -> 格式

十三、条件格式的使用

1、突出显示单元格规则

2、项目选取规则

3、数据条

结果：

4、色阶

5、图标集

6、新建规则

7、清除规则

8、管理规则

十四、排序

1、排序规则

类型：排序规则（升序）

数字：数字按从小到大排序

日期：越早的日期越小

文本：仅包含文字（中文或英文）可按字母或笔画排序；若包含数字、字母、和各种符号，将按以下顺序排列： 空格 0~9 ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ' { | } ~ + < = > A~Z;

逻辑值：false < true

错误值：优先级相同

空单元格：无论是升序还是降序排列，空格单元格总在最后

2、手动升序：

直接选择要移动的单元格进行拖拽，或移动

3、自动升序：

4、其他方式排序：

（1）按笔画排序

• 按第一个字的笔画多少来排列。
• 若笔画相同则按起笔的顺序排序：横、竖、撇、捺、折。
• 若前两项都相同，则按字形结构排序：左右、上下、整体。

（2）使用排序对话框进行排序

（3）、自定义排序

（4）对筛选字段进行排序

十五、筛选

1、通过筛选字段进行筛选

2、对标签进行筛选

3、值筛选

4、日期筛选

5、根据所选内容进行筛选

十六、切片器

切片器：用于选择的选取器。

1、创建切片器

2、删除切片器

在切片器上右键 ->

3、使用切片器

（1）在多个数据透视表中共享切片器

条件：同一个数据源，同一缓存

在任意一个数据透视表中创建切片器

在切片器上，右键 -> 报表连接

选择要连接的报表

（2）更改切片器的显示顺序

当两个切片器重叠的时候，会遮住另一个切片器

（3）对切片器中的字段进行排序

在切片器上，右键 ->

十七、分组

数据必须在“行区” 或 “列区”里

1、对数值数据分组

或

分组后的结果：

2、对文本数据分组

以第一个汉字相同的为一组

可以修改名称

修改组名

3、对日期数据分组

注意事项：日期必须是日期数据类型，否则会出现“选定区域不能分组”！

十八、设置数据计算方式

1、在不该变数据源的情况下，修改数据透视表的数据计算方式

在值区域的任意单元格上，右键，选择“值显示方式”

2、数据计算：指数

公式：（（单元格的值）（总体汇总之和））/（（行汇总）（列汇总））

十九、计算字段

计算字段也称为虚拟字段，在数据透视表中创建，不会出现在数据源中。 计算字段只能出现在值区域。

1、计算字段的创建

2、修改和删除计算字段

二十、计算项

计算项是临时添加的数据，不会改变数据源。 计算项只能出现在列区域 或 行区域。 计算项不会出现在列表窗格中。

1、创建计算项

2、计算求解次序

3、列出公式

二十一、数据透视图的创建与使用

1、数据透视图与普通图表的区别

（1）普通图标的数据源就是单元格，数据透视图的数据源即可以是数据工作表的数据、也可以是外部的数据库。

（2）普通图表可创建“簇状柱形图”（默认），按照分类来比较值；数据透视图默认创建类型为“堆积柱形图”，主要是用来比较各个值在整个分类总计当中，所占的比例。对于数据透视图而言，它不可以使用X、Y散点图、气泡图、以及股价图。

（3）普通图标不具备交互性，属于静态图表；数据透视图具备良好的交互性，是一种动态图表。

（4）普通图标的格式，除非你手动删除掉，否则永久的存在，不会丢失；数据透视图可能刷新之后就会丢失数据标签、趋势线、误差线、以及对数据系列的一些更改。

2、数据透视图与数据透视表的关系

如果数据透视图是建立在数据透视表的基础上，那么数据透视图的默认样式由数据透视表决定。

数据透视表上的四大区域（筛选区、列区、行区、值区域）与数据透视图一一对应，行区域对应数据透视图的X轴，列区域对应数据透视表的Y轴，值区域对应数据透视图当中的数据系列。

3、数据透视图的限制

（1）不创建散点图、气泡图、以及股价图。

（2）数据透视图当中无法切换数据源的位置。

（3）无法调整数据标签的大小。

（4）如果在数据透视图当中，添加了趋势线 ，而这个趋势线所基于的数据透视表当中，添加或删除这个字段的时候，这些趋势线就会消失。

4、创建数据透视图

（1）根据数据透视表创建数据透视图

（2）根据数据源创建数据透视图

（3）使用向导创建数据透视图

5、移动数据透视图

（1）通过复制、粘贴或剪切的方式移动

（2）通过鼠标移动

（3）通过菜单移动

6、使用数据透视图

（1）从不同布局来查看数据透视图（像数据透视表一样操作）

（2）显示和关闭数据透视图的字段列表

或

（3）字段按钮的显示与关闭

（4）添加趋势线

7、刷新数据透视图

8、删除数据透视图

二十二、编辑透视图

1、更改数据透视图的类型

2、更改数据透视图的样式

3、设置数据透视图的标题

4、设置数据透视图的图例

5、设置数据透视图的标签

6、设置数据透视图的数据系列

更改数据系列图表类型：

7、设置坐标轴及其标题

将坐标轴的单位修改方向：

8、设置数据透视图的数据表

数据表是数据透视图自带的。有时候数据透视表与数据透视图分开，这个时候可以自己添加一个数据表。

9、设置数据透视图的误差线

10、设置数据透视图的网格线

二十三、美化数据透视图

1、设置数据透视图的绘图区域和图表

2、设置数据透视图的背景墙和基底

注：背景墙和基底只针对三维图形。

设置基底样式：

设置背景墙样式：和基底的操作一样。

结果：

3、设置数据透视图的文字样式

4、使用数据透视图模板

使用数据透视图模板：

5、使用主题美化数据透视图

二十四、将数据透视图转换为静态数据图

1、将数据透视表转换为静态数据

通过静态数据创建数据图：

2、将数据透视图转换为图片

3、直接删除数据透视表

删除数据透视表后，数据透视图就是一个普通的图。

4、断开数据透视图与数据透视表的连接

复制一份数据透视表到另一个工作表中，再把原来的数据透视表删除。

二十五、切片器和迷你图的使用

1、切片器

2、迷你图

二十六、数据透视表的打印一、什么是数据透视表？

数据透视表是一种可以快速汇总、分析大量数据表格的交互式分析工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并简历交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。

使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并作出有关企业中关键数据的决策。

二、数据源

1、什么是数据源？

数据源是指：用于创建数据透视表的数据来源。可以是Excel的数据列表、其他数据的透视表，也可以是外部的数据源。

2、数据源的原则：

• 每列数据的第一行包含该列的标题
• 数据源中不能包含空行和空列
• 数据源中不能包含空单元格
• 数据源中不能包含合并单元格
• 数据源中不能包含同类字段（即既能当标题也能当数据数据内容）

三、创建数据透视表

1、创建数据源

2、点击菜单栏上的“插入”，选择创建数据透视表

点击确定后，创建数据透视表成功

创建数据透视表后，想要查看数据则点击右侧字段列表，先勾选哪个字段，哪个字段排在前面。

想要字段出现在列，则点击字段名，把字段名拖进“数据透视表区域”里的列区域里，字段就在列中展示。

四、数据透视表的基本术语和4大区域

1、基本术语

数据源：用于创建数据透视表的数据源，可以是单元区域、定义的名称、另一个数据透视表数据或其他外部数据来源。

字段：数据源中各列的列标题，每个字段代表一类数据。字段可分为：报表筛选字段、行字段、列字段、值字段。

项：项是每个字段中包含的数据，表示数据源中字段的唯一条目。

2、4大区域

行区域、列区域、值区域、报表筛选区域

五、数据透视表的基本操作

1、选择数据透视表及表中单元格、字段、项

2、重命名数据透视表

3、复制、移动和删除数据透视表

以上操作很简单，略

六、数据透视表字段列表窗格

1、打开/关闭透视表字段列表窗格

2、重命名字段

七、数据源更新

数据源修改了，数据透视表没有修改，这时候就需要刷新。

1、手动刷新

数据源区域未改变，如只是修改了字段的某个值。

当前sku-01的金额为59

将sku-01的金额修改为50

没刷新之前的数据透视表的sku-01还是59

刷新后的数据透视表的sku-01为50

数据源区域改变，如删除了数据源的某些行或列、添加了某些行或列，数据源的区域大小发生改变。

只需要将数据的前10条做数据透视表：

修改后的透视表

2、自动刷新

打开工作簿时，自动刷新

刷新后如何保持调整好的列宽？

选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 布局和格式 -> 取消勾选更新时自动调整列宽

3、如何清理垃圾条目？

当前数据源的类目为干果蜜饯、酒水饮料、母婴用品

数据透视表的类目也为干果蜜饯、酒水饮料、母婴用品

现将数据源中母婴用品类目删除

在数据透视表中刷新，母婴用品类目不见了。

但在类目筛选里面还有母婴用品类目，称之为垃圾条目。

清理垃圾条目，选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 数据 -> 在每个字段保留的项数里选择“无”即可。

八、数据透视表的三种布局形式

• 压缩布局形式
• 大纲布局形式
• 表格布局形式

九、改变数据透视表的外观

1、修改数据透视表的样式

选中数据透视表 -> 设计 ,红色方框里就是数据透视表的的样式

还可以自定义样式

2、修改数据透视表的主题

3、修改数据透视表样式三

在每组数据中插入空白行

合并数据透视表中的字段项（当数据报表布局为表格形式时）

十、设置分类汇总的显示

1、关闭分类汇总显示

2、自定义分类汇总

选中数据透视表 -> 右键 -> 字段设置

十一、设置总计的显示

十二、设置数字格式显示

1、设置数字格式显示

右键 -> 数字格式

2、设置空值和错误值的显示方式

选择数据透视表 -> 右键 -> 数据透视表选项 -> 布局与格式 -> 格式

十三、条件格式的使用

1、突出显示单元格规则

2、项目选取规则

3、数据条

结果：

4、色阶

5、图标集

6、新建规则

7、清除规则

8、管理规则

十四、排序

1、排序规则

类型：排序规则（升序）

数字：数字按从小到大排序

日期：越早的日期越小

文本：仅包含文字（中文或英文）可按字母或笔画排序；若包含数字、字母、和各种符号，将按以下顺序排列： 空格 0~9 ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ' { | } ~ + < = > A~Z;

逻辑值：false < true

错误值：优先级相同

空单元格：无论是升序还是降序排列，空格单元格总在最后

2、手动升序：

直接选择要移动的单元格进行拖拽，或移动

3、自动升序：

4、其他方式排序：

（1）按笔画排序

• 按第一个字的笔画多少来排列。
• 若笔画相同则按起笔的顺序排序：横、竖、撇、捺、折。
• 若前两项都相同，则按字形结构排序：左右、上下、整体。

（2）使用排序对话框进行排序

（3）、自定义排序

（4）对筛选字段进行排序

十五、筛选

1、通过筛选字段进行筛选

2、对标签进行筛选

3、值筛选

4、日期筛选

5、根据所选内容进行筛选

十六、切片器

切片器：用于选择的选取器。

1、创建切片器

2、删除切片器

在切片器上右键 ->

3、使用切片器

（1）在多个数据透视表中共享切片器

条件：同一个数据源，同一缓存

在任意一个数据透视表中创建切片器

在切片器上，右键 -> 报表连接

选择要连接的报表

（2）更改切片器的显示顺序

当两个切片器重叠的时候，会遮住另一个切片器

（3）对切片器中的字段进行排序

在切片器上，右键 ->

十七、分组

数据必须在“行区” 或 “列区”里

1、对数值数据分组

或

分组后的结果：

2、对文本数据分组

以第一个汉字相同的为一组

可以修改名称

修改组名

3、对日期数据分组

注意事项：日期必须是日期数据类型，否则会出现“选定区域不能分组”！

十八、设置数据计算方式

1、在不该变数据源的情况下，修改数据透视表的数据计算方式

在值区域的任意单元格上，右键，选择“值显示方式”

2、数据计算：指数

公式：（（单元格的值）（总体汇总之和））/（（行汇总）（列汇总））

十九、计算字段

计算字段也称为虚拟字段，在数据透视表中创建，不会出现在数据源中。 计算字段只能出现在值区域。

1、计算字段的创建

2、修改和删除计算字段

二十、计算项

计算项是临时添加的数据，不会改变数据源。 计算项只能出现在列区域 或 行区域。 计算项不会出现在列表窗格中。

1、创建计算项

2、计算求解次序

3、列出公式

二十一、数据透视图的创建与使用

1、数据透视图与普通图表的区别

（1）普通图标的数据源就是单元格，数据透视图的数据源即可以是数据工作表的数据、也可以是外部的数据库。

（2）普通图表可创建“簇状柱形图”（默认），按照分类来比较值；数据透视图默认创建类型为“堆积柱形图”，主要是用来比较各个值在整个分类总计当中，所占的比例。对于数据透视图而言，它不可以使用X、Y散点图、气泡图、以及股价图。

（3）普通图标不具备交互性，属于静态图表；数据透视图具备良好的交互性，是一种动态图表。

（4）普通图标的格式，除非你手动删除掉，否则永久的存在，不会丢失；数据透视图可能刷新之后就会丢失数据标签、趋势线、误差线、以及对数据系列的一些更改。

2、数据透视图与数据透视表的关系

如果数据透视图是建立在数据透视表的基础上，那么数据透视图的默认样式由数据透视表决定。

数据透视表上的四大区域（筛选区、列区、行区、值区域）与数据透视图一一对应，行区域对应数据透视图的X轴，列区域对应数据透视表的Y轴，值区域对应数据透视图当中的数据系列。

3、数据透视图的限制

（1）不创建散点图、气泡图、以及股价图。

（2）数据透视图当中无法切换数据源的位置。

（3）无法调整数据标签的大小。

（4）如果在数据透视图当中，添加了趋势线 ，而这个趋势线所基于的数据透视表当中，添加或删除这个字段的时候，这些趋势线就会消失。

4、创建数据透视图

（1）根据数据透视表创建数据透视图

（2）根据数据源创建数据透视图

（3）使用向导创建数据透视图

5、移动数据透视图

（1）通过复制、粘贴或剪切的方式移动

（2）通过鼠标移动

（3）通过菜单移动

6、使用数据透视图

（1）从不同布局来查看数据透视图（像数据透视表一样操作）

（2）显示和关闭数据透视图的字段列表

或

（3）字段按钮的显示与关闭

（4）添加趋势线

7、刷新数据透视图

8、删除数据透视图

二十二、编辑透视图

1、更改数据透视图的类型

2、更改数据透视图的样式

3、设置数据透视图的标题

4、设置数据透视图的图例

5、设置数据透视图的标签

6、设置数据透视图的数据系列

更改数据系列图表类型：

7、设置坐标轴及其标题

将坐标轴的单位修改方向：

8、设置数据透视图的数据表

数据表是数据透视图自带的。有时候数据透视表与数据透视图分开，这个时候可以自己添加一个数据表。

9、设置数据透视图的误差线

10、设置数据透视图的网格线

二十三、美化数据透视图

1、设置数据透视图的绘图区域和图表

2、设置数据透视图的背景墙和基底

注：背景墙和基底只针对三维图形。

设置基底样式：

设置背景墙样式：和基底的操作一样。

结果：

3、设置数据透视图的文字样式

4、使用数据透视图模板

使用数据透视图模板：

5、使用主题美化数据透视图

二十四、将数据透视图转换为静态数据图

1、将数据透视表转换为静态数据

通过静态数据创建数据图：

2、将数据透视图转换为图片

3、直接删除数据透视表

删除数据透视表后，数据透视图就是一个普通的图。

4、断开数据透视图与数据透视表的连接

复制一份数据透视表到另一个工作表中，再把原来的数据透视表删除。

二十五、切片器和迷你图的使用

1、切片器

2、迷你图

二十六、数据透视表的打印