数据透视表的23个技巧

如何调整“数据透视表”字段顺序｜数据透视表技巧如图是某系列电商品种的销售数据，列出了每一类商品的销售额，以及对应商品的“排名段” 。我们希望统计每个排名段的销售情况。

但是在使用数据透视表统计每个排名段的销售额的时候，排名段的顺序并没有按照理想的从小到大的顺序进行排序，即“1-100”的数据项没有排到第一位。

有部分操作者可能会将这部分数据，一行行地将数据复制，粘贴到新的表格。但是这种方法费时费力，且容易出错。

在数据透视表中，可利用【拖曳数据项】，对字段进行手动排序。

如图为由【数据透视表】创建的商品销售汇总表。现在希望调整“排名段”字段的显示顺序，将“1-100”放在最上方显示，方法如下。

1、选中“排名段”字段下的“1-100”数据项的单元格，将鼠标指针悬停在其边框线上。

2、当出现4个方向箭头形的鼠标指针时，按下鼠标左键不放，并将其拖曳到“排名段”的最上边的边框线上，松开鼠标即可完成对“1-100”数据项的排序。

3、其他的数据项，如“1001-2000”可按照同样的方法，将其拖曳到“排名段”的最下边的边框线上。

最终的显示效果如图所示。

在使用数据透视表时，遇到字段排序不符合预期，可利用【拖曳数据项】，对字段进行手动排序，调整到理想的顺序。

如果想获取本文的案例数据，可以在评论区回复【数据透视表排序】获取。
excel数据透视表布局怎么修改，数据透视表实用技巧创建数据透视表并添加要分析的字段后，你可能需要增强报表布局和格式以使数据更易于阅读和扫描，以便获取详细信息。若要更改数据透视表的布局，你可以更改数据透视表窗体以及字段、列、行、分类汇总、空单元格和空行的显示方式。若要更改数据透视表的格式，你可以应用预定义样式、镶边行和条件格式。

重要: 功能区上的“ 数据透视表工具 ”选项卡带有两个选项卡 -“ 分析 ”（在 Excel 2013 及更高版本中）或“ 选项 ”（Excel 2010 和 Excel 2010）和“ 设计 ” 。请注意，本主题中的步骤在适用情况下同时涉及“ 分析 ”和“ 选项 ”选项卡。

更改数据透视表的布局形式

若要对数据透视表或其各个字段进行大量布局更改，你可以使用以下三种形式之一：

压缩形式在一列中显示来自不同行区域字段的项目，并使用缩进来区分不同字段中的项目。采用压缩形式的行标签占用的空间较少，这为数字数据留出了更多空间。显示“ 展开 ”和“ 折叠 ”按钮，以便你能够以压缩形式显示或隐藏详细信息。压缩形式可节省空间并使数据透视表更具可读性，因此被指定为数据透视表的默认布局形式。

表格形式为每个字段显示一列，并为字段标题提供空间。



大纲形式类似于表格形式，但它可以在每个组的顶部显示分类汇总，因为下一列中的项目显示在当前项目下方的一行内。


将数据透视表更改为压缩、大纲或表格形式

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 设计 ”选项卡的“ 布局 ”组中，单击“ 报表布局 ” ，然后执行下列操作之一：

若要使相关数据不沿屏幕水平方向分散并帮助最大限度地减少滚动，请单击“ 以压缩形式显示 ” 。

在压缩形式中，字段包含在一列中并缩进以显示嵌套的列关系。

若要以经典数据透视表样式分级显示数据，请单击“ 以大纲形式显示 ” 。

若要以传统表格格式查看所有数据并轻松将单元格复制到另一个工作表，请单击“ 以表格形式显示 ” 。



更改项目标签在布局形式中的显示方式

在数据透视表中，选择一个行字段。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

你还可以双击采用大纲或表格形式的行字段，然后继续执行步骤 3 。

在“ 分析 ”或“ 选项 ”选项卡的“ 活动字段 ”组中，单击“ 字段设置 ” 。


在“ 字段设置 ”对话框中，单击“ 布局和打印 ”选项卡，然后在“ 布局 ”下执行下列操作之一：

若要以大纲形式显示字段项，请单击“ 以大纲形式显示项目标签 ” 。

若要以压缩形式在同一列中显示或隐藏下一字段的标签，请单击“ 以大纲形式显示项目标签 ” ，然后选择“ 在同一列中显示下一字段的标签(压缩表单) ” 。

若要以表格形式显示字段项，请单击“ 以表格形式显示项目标签 ” 。


更改数据透视表中的字段排列

若要获取所需的最终布局结果，可以使用数据透视表字段列表添加、重新排列和删除字段。

如果你未看到数据透视表字段列表，请确保选中数据透视表。如果你仍未看到数据透视表字段列表，请在“ 选项 ”选项卡的“ 显示/隐藏 ”组中，单击“ 字段列表 ” 。

如果你在数据透视表字段列表中未看到要使用的字段，则可能需要刷新数据透视表以显示自上次操作以来添加的任何新字段、计算字段、度量值、计算度量值或维度。在“ 选项 ”选项卡的“ 数据 ”组中，单击“ 刷新 ” 。

有关使用数据透视表字段列表的详细信息，请参阅使用字段列表排列数据透视表中的字段。


向数据透视表添加字段

执行下列一项或多项操作：

选中字段部分中每个字段名称旁边的复选框。字段将放置在布局部分的默认区域中，但您可以在需要时重新排列。

默认情况下，文本字段将添加到“ 行标签 ”区域，数值字段将添加到“ 值 ”区域，而联机分析处理 (OLAP) 日期和时间层次结构将添加到“ 列标签 ”区域。

右键单击字段名称，然后选择以下相应命令将字段放置到布局部分的特定区域中：“ 添加到报表筛选器 ”、“ 添加到列标签 ”、“ 添加到行标签 ”或“ 添加到值 ” 。

单击并按住字段名称，然后在字段部分和布局部分中的某个区域之间拖动字段。


复制数据透视表中的字段

在基于 Excel 工作表数据或来自非 OLAP 源数据的外部数据的数据透视表中，你可能希望多次向“ 值 ”区域添加同一字段，以便能够使用“ 值显示方式 ”功能来显示不同的计算。例如，你可能希望并排比较计算，例如毛利率和净利润率、最小和最大销售额、客户数量和客户总数百分比。有关详细信息，请参阅在数据透视表的值字段中显示不同的计算。

单击并按住字段部分中的字段名称，然后将该字段拖动到布局部分中的“ 值 ”区域。

根据需要重复步骤 1 ，以多次复制该字段。

在每个复制的字段中，按照你希望的方式更改汇总函数或自定义计算。


注意:

向“值”区域添加两个或多个字段时，无论它们是相同字段还是不同字段的副本，字段列表都会自动将“值列”标签添加到“ 值 ”区域。你可以使用此字段在“ 值 ”区域内上下移动字段位置。你甚至可以将“值列”标签移动到“ 列标签 ”区域或“ 行标签 ”区域。但是，你无法将“值列”标签移动到“报表筛选器”区域。

无论数据类型是数值还是非数值，你都只能向“ 报表筛选器 ”、“ 行标签 ”或“ 列标签 ”区域添加一次字段。如果你尝试多次添加同一字段 — 例如添加到布局部分的“ 行标签 ”和“ 列标签 ”区域，则该字段将自动从原始区域中删除，并进入新区域。

将同一字段添加到“ 值 ”区域的另一种方法是使用公式（也称为计算列），它在公式中使用相同的字段。

你不能在基于 OLAP 数据源的数据透视表中多次添加同一字段。


重新排列数据透视表中的字段

文章插图

你可以使用布局部分底部的四个区域之一重新排列现有字段或重新定位这些字段：

数据透视表
说明
数据透视图
说明
值
用于显示汇总数值数据。
值
用于显示汇总数值数据。
行标签
用于将字段显示为报表一侧的行。位置较低的行嵌套在其正上方的另一行中。
坐标轴字段(类别)
用于将字段显示为图表中的坐标轴。
列标签
用于将字段显示为报表顶部的列。位置较低的列嵌套在其正上方的另一列中。
图例字段(序列)标签
用于显示图表图例中的字段。
报表筛选器
用于根据报表筛选器中的选定项筛选整个报表。
报表筛选器
用于根据报表筛选器中的选定项筛选整个报表。

若要重新排列字段，请单击其中一个区域中的字段名称，然后选择以下命令之一：

选择此命令
可以
上移
在区域中将字段上移一个位置。
下移
在区域中将字段下移一个位置。
移至开头
将字段移动到区域的开头。
移至末尾
将字段移动到区域的末尾。
移到报表筛选器
将字段移动到“报表筛选器”区域。
移到行标签
将字段移动到“行标签”区域。
移到列标签
将字段移动到“列标签”区域。
移到值
将字段移动到“值”区域。
值字段设置、字段设置
显示“ 字段设置 ”或“ 值字段设置 ”对话框。有关每个设置的详细信息，请单击对话框顶部的“帮助”按钮。

你还可以单击并按住字段名称，然后在字段和布局部分之间以及不同区域之间拖动字段。


从数据透视表中删除字段

单击数据透视表。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

若要显示数据透视表字段列表（如有必要），请在“ 分析 ”或“ 选项 ”选项卡的“ 显示 ”组中，单击“ 字段列表 ” 。


若要删除字段，请在“数据透视表字段列表”中执行下列操作之一：

在“数据透视表字段列表”中，清除字段名称旁边的复选框。

注意: 清除“字段列表”中的复选框将从报表中删除该字段的所有实例。

在“布局”区域中，单击字段名称，然后单击“ 删除字段 ” 。

在布局部分中单击并按住字段名称，然后将其拖到“数据透视表字段列表”之外。


更改列、行和分类汇总的布局

若要进一步优化数据透视表的布局，可以执行影响列、行和分类汇总布局的更改，例如在行上方显示分类汇总或关闭列标题。你还可以重新排列行或列中的单个项目。


打开或关闭列和行字段标题

单击数据透视表。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

若要在显示和隐藏字段标题之间切换，请在“ 分析 ”或“ 选项 ”选项卡的“ 显示 ”组中，单击“ 字段标题 ” 。



在其行上方或下方显示分类汇总

在数据透视表中，选择要显示分类汇总的行字段。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

提示: 在大纲或表格形式中，你还可以双击行字段，然后继续执行步骤 3 。

在“ 分析 ”或“ 选项 ”选项卡的“ 活动字段 ”组中，单击“ 字段设置 ” 。


在“ 字段设置 ”对话框的“ 分类汇总和筛选器” 选项卡的“ 分类汇总 ”下，单击“ 自动 ”或“ 自定义 ” 。

注意: 如果选择“ 无 ” ，则会关闭分类汇总。

在“ 布局和打印 ”选项卡的“ 布局 ”下，单击“ 以大纲形式显示项目标签 ” ，然后执行下列操作之一：

若要在分类汇总行上方显示分类汇总，请选中“ 在每个组顶端显示分类汇总 ”复选框。默认情况下，此选项处于选中状态。

若要在分类汇总行下方显示分类汇总，请清除“ 在每个组顶端显示分类汇总 ”复选框。


更改行或列项的顺序

请执行以下任一操作：

在数据透视表中，右键单击行或列标签或标签中的项目，指向“ 移动 ” ，然后使用“ 移动 ”菜单上的某个命令将项目移到其他位置。

选择要移动的行或列标签项，然后指向单元格的底部边框。当指针变为四向指针时，将项目拖动到新位置。下图显示了如何通过拖动来移动行项目。



刷新时调整列宽

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 数据透视表 ”组中，单击“ 选项 ” 。


在“ 数据透视表选项 ”对话框的“ 布局和格式 ”选项卡的“ 格式 ”下，执行下列操作之一：

若要将数据透视表列自动调整为最宽文本或数字值的大小，请选中“ 更新时自动调整列宽 ”复选框。

若要保持当前数据透视表列宽，请清除“ 更新时自动调整列宽 ”复选框。


将列移动到行标签区域或将行移动到列标签区域

你可能希望将列字段移动到行标签区域或将行字段移动到列标签区域，以优化数据透视表的布局和可读性。将列移动到行或将行移动到列时，将转置字段的垂直或水平方向。此操作也称为“透视”行或列。

请执行以下任一操作：

右键单击行字段，指向“ 移动<字段名称>” ，然后单击“ 将<字段名称>移至列 ” 。

右键单击列字段，然后单击“ 将<字段名称>移至行 ” 。

将行或列字段拖到其他区域。下图显示了如何将列字段移动到行标签区域。

1.单击列字段

2.将其拖至行区域

3.“体育”将成为行字段，例如“地区”


合并或取消合并外部行和列项的单元格

你可以合并行和列项的单元格，以便水平和垂直居中项目；或者取消合并单元格，以便左对齐项目组顶部的外部行和列字段中的项目。

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 选项 ”选项卡的“ 数据透视表 ”组中，单击“ 选项 ” 。


在“ 数据透视表选项 ”对话框中，单击“ 布局和格式 ”选项卡，然后在“ 布局 ”下，选中或清除“ 合并且居中排列带标签的单元格 ”复选框。

注意: 你不能使用数据透视表中的“ 对齐方式 ”选项卡下的“ 合并单元格 ”复选框。


更改空白单元格、空白行和错误的显示

你的数据透视表数据有时可能包含空白单元格、空白行或错误，并且你希望更改它们的显示方式。


更改错误和空单元格的显示方式

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 数据透视表 ”组中，单击“ 选项 ” 。


在“ 数据透视表选项 ”对话框的“ 布局和格式 ”选项卡的“ 格式 ”下，执行下列一项或多项操作：

若要更改错误显示，请选中“ 对于错误值，显示 ”复选框。然后在框中，键入要替代错误值的值。若要将错误值显示为空单元格，请删除框中的所有字符。

若要更改空单元格的显示，请选中“ 对于空单元格，显示 ”复选框，然后在文本框中键入要在空单元格中显示的值。

提示: 若要显示空白单元格，请删除框中的所有字符。若要显示零，请清除该复选框。


在行或项目之后显示或隐藏空白行

对于行，请执行下列操作：

在数据透视表中，选择一个行字段。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

提示: 在大纲或表格形式中，你还可以双击行字段，然后继续执行步骤 3 。

在“ 分析 ”或“ 选项 ”选项卡的“ 活动字段 ”组中，单击“ 字段设置 ” 。


在“ 字段设置 ”对话框的“ 布局和打印 ”选项卡的“ 布局 ”下，选中或清除“ 在每个项目标签后插入空行 ”复选框。

对于项目，请执行下列操作：

在数据透视表中，选择所需的项目。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 设计 ”选项卡的“ 布局 ”组中，单击“ 空白行 ” ，然后选中“ 在每个项目标签后插入空行 ”或“ 删除每个项目标签后的空行 ”复选框。


注意: 你可以将字符和单元格格式应用于空白行，但不能在其中输入数据。


更改不含数据的项目和标签的显示方式

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 数据透视表 ”组中，单击“ 选项 ” 。


在“ 显示 ”选项卡的“ 显示 ”下，执行下列一项或多项操作：

若要显示行中的空数据项，请选中或清除“ 显示行中的空数据项 ”复选框以显示或隐藏不含值的行项目。

注意: 此设置仅适用于联机分析处理 (OLAP) 数据源。

若要显示列中的空数据项，请选中或清除“ 显示列中的空数据项 ”复选框以显示或隐藏不含值的列项目。

注意: 此设置仅适用于 OLAP 数据源。

若要在值区域中没有字段时显示项目标签，请选中或清除“ 值区域中没有字段时显示项目标签 ”复选框，以在值区域中没有字段时显示或隐藏项目标签。

注意: 此复选框仅适用于使用早于 Office Excel 2007 的 Excel 版本创建的数据透视表。


更改或删除格式

你可以在库中选择各种数据透视表样式。此外，你还可以控制报表的条带行为。更改字段的数字格式是在整个报表中应用一致格式的快速方法。你还可以添加或删除行和列的条带（交替使用较暗和较浅的背景）。条带可以使数据更容易阅读和扫描。


应用样式以格式化数据透视表

你可以使用众多预定义的数据透视表样式（或快速样式）之一快速更改数据透视表的外观和格式。

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 设计 ”选项卡的“ 数据透视表样式 ”组中，执行以下任一操作：

单击可见的数据透视表样式或滚动库以查看其他样式。

若要查看所有可用样式，请单击滚动条底部的“ 更多 ”按钮。

如果要创建自己的自定义数据透视表样式，请单击库底部的“ 新建数据透视表样式 ”以显示“ 新建数据透视表样式 ”对话框。


应用条带以更改数据透视表的格式

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 设计 ”选项卡的“ 数据透视表样式选项 ”组中，执行下列操作之一：

若要以较浅和较暗的颜色格式交替显示每一行，请单击“ 镶边行 ” 。

若要以较浅和较暗的颜色格式交替显示每一列，请单击“ 镶边列 ” 。

若要在条带样式中包含行标题，请单击“ 行标题 ” 。

若要在条带样式中包含列标题，请单击“ 列标题 ” 。



从数据透视表中删除样式或条带格式

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 设计 ”选项卡的“ 数据透视表样式 ”组中，单击滚动条底部的“ 更多 ”按钮以查看所有可用样式，然后单击库底部的“ 清除 ” 。



为数据透视表中的数据设置条件格式

使用条件格式可帮助你直观地浏览和分析数据，检测关键问题以及识别模式和趋势。条件格式可帮助你回答有关数据的特定问题。在数据透视表上使用条件格式时，需要了解一些重要的区别：

如果通过筛选、隐藏级别、折叠和展开级别或移动字段来更改数据透视表的布局，则只要不删除基础数据中的字段，就会保留条件格式。

“ 值 ”区域中字段的条件格式范围可以基于数据层次结构，由一列或多列的行或一行或多行的列上的父级（层次中下一个较高级别）的所有可见子级（层次中下一个较低级别）决定。

注意: 在数据层次结构中，子级不继承父级的条件格式，父级也不继承子级的条件格式。

设置“ 值 ”区域中字段的条件格式范围的方法有三种：按选定内容、按相应字段和按值字段。

有关详细信息，请参阅“应用条件格式” 。


更改字段的数字格式

在数据透视表中，选择所需的字段。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 活动字段 ”组中，单击“ 字段设置 ” 。

“ 字段设置 ”对话框显示标签和报表筛选器；“ 值字段设置 ”对话框显示值。

在对话框的底部单击“ 数字格式 ” 。

在“ 设置单元格格式 ”对话框的“ 类别 ”列表中，单击要使用的数字格式。

选择所需的选项，然后单击“ 确定 ”两次。

你也可以右键单击值字段，然后单击“ 数字格式 ” 。


包含 OLAP 服务器格式设置

如果已连接到 Microsoft SQL Server Analysis Services 联机分析处理 (OLAP) 数据库，则可以指定要检索和显示数据的 OLAP 服务器格式。

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 数据 ”组中，单击“ 更改数据源 ” ，然后单击“ 连接属性 ” 。


在“ 连接属性 ”对话框的“ 用法 ”选项卡的“ OLAP 服务器格式设置 ”部分下，执行下列操作之一：

若要启用或禁用数字格式（如货币、日期和时间），请选中或清除“ 数字格式 ”复选框。

若要启用或禁用字体样式（如粗体、斜体、下划线和删除线），请选中或清除“ 字体样式 ”复选框。

若要启用或禁用填充颜色，请选中或清除“ 填充颜色 ”复选框。

若要启用或禁用文本颜色，请选中或清除“ 文本颜色 ”复选框。


保留或放弃格式设置

单击数据透视表中的任意位置。

此操作将在功能区上显示“ 数据透视表工具 ”选项卡。

在“ 分析 ”或“ 选项 ”选项卡的“ 数据透视表 ”组中，单击“ 选项 ” 。


在 “布局和格式” 选项卡的“ 格式 ”下，执行下列操作之一：

若要保存数据透视表布局和格式，以便在每次对数据透视表执行操作时使用它，请选中“ 更新时保留单元格格式 ”复选框。

若要在每次对数据透视表执行操作时放弃数据透视表布局和格式并恢复为默认布局和格式，请清除“ 更新时保留单元格格式 ”复选框。

注意: 虽然此选项也会影响数据透视图格式，但不会保留趋势线、数据标签、误差线以及对特定数据系列的其他更改。
excel中的数据透视表怎么用1打开你需要汇总的EXCEL表格，点击插入里面的数据透视表
2出现数据透视表的数据源选择项，选择你所需要的数据确定，就会出现数据透视表的报表选项
3将你所需要的报表字段，用鼠标左键点住不放，拖入右边的行坐标和数值区域
一般我们做数据汇总时都是将数据求和，或者求平均数等等，所以这时就要对数值区域的字段进行修改。
4选择字段后边的小三角行，点击“值字段设置” ，将计算类型修改成你所需要的“求和”或者其它。
5确定后关闭报表，汇总结果就出来了。
6如果说还想要每个业务的每个产品的销售汇总，还可以增加一个行标签。
鼠标放在行列标处，点击右键，显示字段列表，将客户名称拉到行标签处即可
7这样的表格看起来还不是很习惯，可以将鼠标放在“客户名称”处，单击右健，更改“字段设置” ，在布局和打印处，选择“以表格形式显示项目标签”即可
8如果数据有更改的，更改后，在数据透视表上点击鼠标右健，点击刷新即可
excel做数据透视表的方法Excel中经常需要使用到数据透视表进行数据分析，数据透视表具体该如何制作呢?下面是我带来的关于excel做数据透视表的方法，希望阅读过后对你有所启发!
excel做数据透视表的方法：
做数据透视表步骤1：首先，在Excel中打开准备好了的电子表格，单击菜单栏--“插入”--“数据透视表” 。
做数据透视表步骤2：弹出创建数据透视表对话框，我们选择表区域，以及放置数据透视表的位置，单击确定。
做数据透视表步骤3：右侧窗格出现数据透视表字段列表，我们将里面的四个字段全部勾选，然后单击“选项”按钮。
做数据透视表步骤4：在书籍透视表选项界面中，切换到汇总和筛选标签，只勾选“排序中的排序时使用自定义列表” ，单击确定。
做数据透视表步骤5：切换到显示标签，勾选“显示字段标题和筛选下拉列表” ，以及“经典数据透视表布局选项” 。
做数据透视表步骤6：确定之后相应的数据透视表发生变化，右击单元格，选择“字段设置” 。
做数据透视表步骤7：弹出字段设置界面，在“分类汇总和筛选”标签中，勾选“无” ，单击确定。
做数据透视表步骤8：选中数据透视表区域，按下Ctrl+C复制，然后点击下面的新建工作簿按钮。
做数据透视表步骤9：在新建的表格中，将复制的内容粘贴进来，然后按下F5键，打开定位对话框，点击“定位条件” ，勾选“空值” ，确定。
做数据透视表步骤10：此时会选中表中的空值单元格，注意不是空单元格，输入=B2 ，回车。
【数据透视表的23个技巧】做数据透视表步骤11：返回到sheet1 ，选中数据区域，单击开始选项卡下的“格式刷”按钮。