数据透视表

创建数据透视表以分析工作表数据

数据透视表是计算、汇总和分析数据的强大工具,可助你了解数据中的对比情况、模式和趋势。 数据透视表的工作方式略有不同,具体取决于用于运行 Excel 的平台。

如果你有正确的 许可证要求,可以 要求 Copilot 帮助你创建数据透视表

你的浏览器不支持视频。 请安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

  1. 选择要据其创建数据透视表的单元格。

    注意:  数据应按具有单个标题行的列进行组织。 有关更多详细信息,请参阅数据格式提示和技巧部分。

  2. 选择“插入”>“数据透视表”。

    数据透视表按钮

  3. 这会基于现有表或区域创建数据透视表。 范围或表中的数据透视表

    注意: 选择“ 将此数据添加到数据模型”会将用于此数据透视表的表或区域添加到工作簿的数据模型中。 了解详细信息

  4. 选择要放置数据透视表的位置。 选择“ 新建工作表”,将数据透视表置于新工作表或“现有工作表”中,然后选择新数据透视表的显示位置。 

  5. 选择“确定”。

通过单击按钮上的向下箭头,可以从数据透视表的其他可能源中进行选择。  除了使用现有表或区域外,还可以选择其他三个源来填充数据透视表。 选择数据透视表源

注意: 根据组织的 IT 设置,你可能会在列表中看到组织的名称。 例如,“从 Power BI (Microsoft) ”。

从外部数据源获取

来自外部源的数据透视表

从数据模型获取

如果您的工作簿包含 数据模型,并且您希望从多个表创建数据透视表、使用自定义度量值增强数据透视表,或者正在使用非常大的数据集,请使用此选项。  数据表中的数据透视表

从 Power BI 获取

如果你的组织使用 Power BI,并且你想要发现并连接到你有权访问的认可的云数据集,请使用此选项。 

Power BI 中的数据透视表

  1. 若要向数据透视表中添加字段,请在“数据透视表字段”窗格中选中字段名称复选框。

    注意: 所选字段将添加到其默认区域:将非数值字段添加到,日期和时间层次结构将添加到,并将数字字段添加到

    数据透视表

  2. 若要将字段从一个区域移到另一个区域,请将该字段拖到目标区域。

如果向数据透视表数据源添加新数据,在该数据源基础上构建的所有数据透视表都需要刷新。 若要仅刷新一个数据透视表,可以右键单击数据透视表范围中的任何位置,然后选择“ 刷新”。 如果有多个数据透视表,请先选择任何数据透视表中的任意单元格,然后在功能区上转到“ 数据透视表分析 ”> 选择“ 刷新 ”按钮下的箭头,然后选择“ 全部刷新”。

刷新数据透视表

默认情况下,放置在“ ”区域中的数据透视表字段显示为 SUM。 如果 Excel 将数据解释为文本,则数据将显示为 COUNT。 这就是为何需要确保不混合使用值字段的数据类型。 可以更改默认计算,方法是先选择字段名称右侧的箭头,然后选择“ 值字段设置” 选项。

Excel“值字段设置”对话框

接着,更改“汇总值依据”部分的计算。 请注意,更改计算方法时,Excel 会自动将其追加到 “自定义名称 ”部分,如“FieldName 的总和”,但你可以对其进行更改。 如果选择“ 数字格式”,则可以更改整个字段的数字格式。

提示: 由于更改 “汇总值依据 ”部分中的计算会更改数据透视表字段名称,因此最好不要重命名数据透视表字段,直到设置完数据透视表。 一个技巧是使用 “查找 & 替换 ” (Ctrl+H) >查找 >“总和”的内容,然后将 “替换为 ”> 留空以一次性替换所有内容,而不是手动重排。

“按值汇总“选项的 ​​Excel“值字段设置”对话框

值显示方式

还可将其显示为字段的百分比,而不是使用计算进行数据汇总。 下例中,更改了家庭开支金额,使其显示为“总计 %”,而不是值的总和。

数据透视表示例,其中各值显示为总计的百分比 数据透视表示例,其中各值显示为总计的百分比

打开“ 值字段设置 ”对话框后,可以从“ 显示值” 选项卡进行选择。

将值同时显示为计算和百分比。

只需将项目拖动到 “值” 部分两次,然后为每个选项设置 “汇总值” 和“ 显示值” 选项。

你的浏览器不支持视频。 请安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

  1. 选择工作表中的表或数据区域,然后选择“ 插入 > 数据透视表 ”以打开“ 插入数据透视表 ”窗格。

  2. 可以手动创建自己的 数据透视表 ,也可以选择要创建的建议 数据透视表 。 执行下列操作之一:

  • “创建自己的数据透视表” 卡上,选择“ 新建工作表 ”或“ 现有工作表 ”以选择数据透视表的目标。

  • 在建议的数据透视表上,选择“ 新建工作表 ”或“ 现有工作表”以选择数据透视表的目标。

注意: 建议 的数据透视表 仅适用于Microsoft 365 个订阅者。

使用“插入数据透视表”窗格可以设置数据透视表的源、目标和其他方面。

可以在创建数据透视表数据时更改数据源。

  1. 在“ 插入数据透视表 ”窗格中,选择“ ”下的文本框。 更改 时,窗格中的卡片将不可用。

  2. 在网格上选择数据或在文本框中输入区域。 

  3. 按键盘或按钮上的 Enter 以确认选择。 窗格会根据新的数据源使用新的建议 的数据透视表 进行更新。

“插入数据透视表”窗格,要求将表或区域用作源,并允许你更改目标。

从 Power BI 获取

如果你的组织使用 Power BI,并且你想要发现并连接到你有权访问的认可的云数据集,请使用此选项。 

Power BI 中的数据透视表

在“ 数据透视表字段”窗格中,选中要添加到数据透视表的任何字段的复选框。

默认情况下,非数值字段添加到“”区域,日期和时间字段添加到“”区域,数值字段添加到“”区域。

还可以手动将任何可用项拖放到任何 数据透视表中 ,或者如果不再需要数据透视表中的某个项,请将其从列表中拖出或取消选中。

Excel 网页版中的数据透视表字段

默认情况下,“值”区域中的数据透视表字段显示为 SUM。 如果 Excel 将数据解释为文本,则会将其显示为 COUNT。 这就是为何需要确保不混合使用值字段的数据类型。通过右键单击行中的任何值并选择“ 汇总值依据 ”选项来更改默认计算。

Excel 网页版中的汇总依据

值显示方式

还可将其显示为字段的百分比,而不是使用计算进行数据汇总。 下例中,更改了家庭开支金额,使其显示为“总计 %”,而不是值的总和。

数据透视表示例,其中各值显示为总计的百分比

右键单击要显示其值的列中的任何值。 在菜单中选择“ 将值显示为 ”。 将显示可用值的列表。

从列表中进行选择。

若要显示为 父总计的百分比,请将鼠标悬停在列表中的该项上,然后选择要用作计算基础的父字段。 

显示为

如果将新数据添加到 数据透视表 数据源,则必须刷新基于该数据源构建的任何 数据透视表。 右键单击 数据透视表 区域中的任意位置,然后选择“ 刷新”。

刷新数据透视表

如果创建了 数据透视表 并决定不再需要它,请选择整个 数据透视表 范围,然后按 Delete。 这不会对它周围的其他数据、 数据透视表 或图表产生任何影响。 如果数据透视表位于没有要保留的其他数据的单独工作表上,则删除工作表是删除数据透视表的一种快速方法。

  • 应将数据整理为表格格式,且没有任何空白行或空白列。 理想情况下,可以使用 Excel 表

  • 表是一个很好的数据透视表数据源,因为在刷新数据时,添加到表的行会自动包含在数据透视表中,并且任何新列都包含在 “数据透视表字段”列表中。 否则,需要 更改数据透视表的源数据,或使用 动态命名区域公式

  • 列中的数据类型应相同。 例如,同一列中不得混合使用日期和文本。

  • 数据透视表处理数据的快照(称为缓存),因此不会以任何方式更改实际数据。

如果你在使用数据透视表方面没有什么经验或不确定如何使用,“推荐的数据透视表”会是一个不错的选择。 当你使用此功能时,Excel 将通过在数据透视表中使数据与最合适的区域相匹配来确定有意义的布局。 这可以为你进行其他实验提供一个起点。 创建建议的数据透视表后,可以探索不同的方向并重新排列字段以实现所需的结果。 你也可以下载交互式制作第一个数据透视表教程。

  1. 选择源数据或表区域中的单元格。

  2. 转到“插入”>“推荐的数据透视表”。 转到“插入”>“推荐的数据透视表”,让 Excel 为你创建数据透视表

  3. Excel 会分析你的数据,并提供多个选项,如此示例中使用家庭费用数据。 Excel​​“推荐的数据透视表”对话框

  4. 选择最适合你的数据透视表,然后按“确定”。 Excel 在新工作表上创建数据透视表并显示 “数据透视表字段” 列表。

  1. 选择源数据或表区域中的单元格。

  2. 转到“插入”>“数据透视表”。 "数据"选项卡,"分析"组

  3. Excel 将显示“ 创建数据透视表 ”对话框,其中选择了区域或表名称。 此情况下,将使用名为“tbl_HouseholdExpenses”的表。

  4. 在“选择放置数据透视表的位置”部分,选择“新工作表”或“现有工作表”。 对于“现有工作表”,选择希望放置数据透视表的单元格。

  5. 选择“确定”,Excel 将创建一个空白的数据透视表并显示 “数据透视表字段” 列表。

数据透视表字段列表

在顶部的“字段名称”区域,选择要添加到数据透视表的任何字段的复选框。 默认情况下,非数值字段添加到“”区域,日期和时间字段添加到“”区域,数值字段添加到“”区域。 也可将任意可用项手动拖放到任意数据透视表字段,或者,如果不再需要数据透视表中的某个项,只需将其拖出字段列表或取消选中该项。 能够重新排列字段项是数据透视表功能之一,它使更改其外观变得如此简单。

数据透视表字段列表

Excel​​“数据透视表字段列表”对话框示例

  • 汇总方式

    默认情况下,放置在“ ”区域中的数据透视表字段显示为 SUM。 如果 Excel 将数据解释为文本,则数据将显示为 COUNT。 这就是为何需要确保不混合使用值字段的数据类型。 可以通过先选择字段名称右侧的箭头,然后选择“ 字段设置” 选项来更改默认计算。

    接着,更改“汇总依据”部分的计算。 请注意,更改计算方法时,Excel 会自动将其追加到 “自定义名称 ”部分,如“FieldName 的总和”,但你可以对其进行更改。 如果选择“ 数字...”,则可以更改整个字段的数字格式。

    提示: 由于更改 汇总依据 部分中的计算会更改数据透视表字段名称,因此最好不要重命名数据透视表字段,直到设置完数据透视表。 一个技巧是选择“编辑”菜单上的“替换 (”) >查找 >“总和”的内容,然后将“替换为”> 留空以一次性替换所有内容,而不是手动重新填充。

  • 数据显示方式

    还可将其显示为字段的百分比,而不是使用计算进行数据汇总。 下例中,更改了家庭开支金额,使其显示为“总计 %”,而不是值的总和。

    数据透视表示例,其中各值显示为总计的百分比

    打开“ 字段设置” 对话框后,可以从“ 将数据显示为 ”选项卡进行选择。

  • 将值同时显示为计算和百分比。

    只需将项目拖动到 “值” 部分两次,右键单击该值并选择“ 字段设置”,然后为每个选项设置 “汇总依据 ”和“ 将数据显示为 ”选项。

如果向数据透视表数据源添加新数据,则必须刷新基于该数据源构建的任何数据透视表。 若要仅刷新一个数据透视表,可以右键单击数据透视表中的任意位置,然后选择“ 刷新”。 如果有多个数据透视表,请先选择任何数据透视表中的任意单元格,然后在功能区上转到 “数据透视表分析 ”> 选择“ 刷新 ”按钮下的箭头,然后选择“ 全部刷新”。

如果创建了数据透视表并决定不再需要它,只需选择整个数据透视表范围,然后按 Delete。 这不会影响任何其他数据、数据透视表或周围的图表。 如果数据透视表位于单独工作表上,里面没有你想要保留的数据,则快速删除数据透视表的方法是删除该工作表。 

iPad 上的数据透视表可供在 iPad 版本 2.82.205.0 及更高版本上运行 Excel 的客户使用。 若要访问此功能,请确保你的应用已通过 App Store 更新到最新版本。

  1. 选择源数据或表区域中的单元格。 

  2. 转到 “插入 > 数据透视表”。 

    在 iPad 上插入数据透视表。

  3. 选择要放置数据透视表的位置。 在新 工作表上选择“插入”,将数据透视表置于新工作表中,或选择要将新数据透视表放置在“目标”字段中的单元格。 

  4. 选择“插入”。 

通常,非数值字段添加到 “行” 区域,日期和时间字段添加到 “列” 区域,并将数值字段添加到“ ”区域。 也可将任意可用项手动拖放到任意数据透视表字段,或者,如果不再需要数据透视表中的某个项,只需将其拖出字段列表或取消选中该项。 能够重新排列字段项是数据透视表功能之一,它使更改其外观变得如此简单。 

注意: 如果字段列表不再可见,请转到“ 数据透视表 ”选项卡,向左轻扫,然后选择“ 字段列表 ”以显示字段列表。 

iPad 上的数据透视表字段列表

如果向数据透视表数据源添加新数据,则必须刷新基于该数据源构建的任何数据透视表。 若要仅刷新一个数据透视表,可以选择并按住数据透视表区域中任意位置的单元格,然后选择“ 刷新”。 如果有多个,请转到功能区上的 “数据透视表”选项卡 ,然后选择“ 刷新”。 

在 Excel for iPad 上刷新数据透视表的屏幕截图

如果创建了数据透视表并决定不再需要它,则可以选择跨越整个数据透视表范围的行和列,然后按 Delete。   

数据格式提示和技巧

  • 使用干净的表格数据以获得最佳结果。

  • 按列而不是行组织数据。

  • 确保所有列都有标题,每列都有一行唯一的非空白标签。 避免使用双行标题或合并单元格。 Excel 列 2

  • 将数据格式化为 Excel 表格 (选择数据中的任何位置,然后从功能区) 中选择“插入 > 表格 ”。

  • 如果你有复杂或嵌套的数据,请使用 Power Query 对其进行转换 (例如,以取消数据) 以便将数据组织成具有单个标题行的列。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家或在社区中获取支持。

数据透视表建议是 Microsoft 365 中连接体验的一部分,它使用人工智能服务分析数据。 如果选择退出 Microsoft 365 中的连接体验,则不会将数据发送到人工智能服务,并且无法使用数据透视表建议。 有关更多详细信息,请阅读Microsoft隐私声明

相关文章

创建数据透视图

使用切片器筛选数据透视表数据 

创建数据透视表日程表以筛选日期

使用数据模型创建数据透视表以分析多个表中的数据

创建连接到 Power BI 数据集的数据透视表

使用字段列表排列数据透视表中的字段

更改数据透视表的源数据

在数据透视表中计算值

删除数据透视表

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。