跳至主要内容

Kutools for Office — 一套工具,五种功能。事半功倍。

如何在Excel数据透视表中添加总计/小计列的百分比?

Author Kelly Last modified

在Excel中处理大型数据集并通过数据透视表进行分析时,工具通常会自动生成汇总列或行以聚合数值数据。然而,在许多实际场景中(例如绩效评估或销售对比),您不仅需要查看总计,还需要了解每个项目相对于总计或其子组小计所占的比例(百分比)。通过直接在数值旁边显示这些百分比,您可以快速识别关键贡献者、发现趋势并更有效地传达洞察。本指南逐步演示了如何在数据透视表中添加一个额外列,用于计算每个值占总体总计或子组小计的百分比,从而简化Excel中的数据分析和报告任务。


在Excel数据透视表中添加总计/小计列的百分比

为了显示每个项目对总体总计或数据子组的贡献百分比,您可以通过在Excel数据透视表中添加一个百分比计算列来增强功能。这种方法在您希望执行数据比较或呈现超出原始数字的汇总统计信息时特别有用。以下扩展步骤概述了如何设置此功能,并在每个阶段提供实用提示和注意事项。

1. 首先选择要在数据透视表中分析的数据范围,然后转到Excel功能区,点击 插入 > 数据透视表。这将为您的分析创建数据透视表的基础。从一开始就选择正确的源范围可以确保计算准确;请仔细检查您的选择是否覆盖所有相关数据且没有空白行或列。

2。在 创建数据透视表 对话框中,指定是要将数据透视表放置在新工作表还是现有工作表中。选择新工作表通常会使表格更易于查看,并保持原始数据完整。设置好首选项后,点击 确定 按钮继续。
set options in the Create PivotTable dialog box

3。在 数据透视表字段 窗格中,拖动 商店 字段和 项目 字段到 区域。接下来,将 销售额 字段拖到 区域 两次。这样做可以让您在结果表中并排显示原始销售额和百分比计算。如果您只想显示百分比列,可以稍后删除或隐藏原始值字段。
drag items into Value field

4。在 区域下方,单击第二个 销售额 字段旁边的下拉箭头(默认情况下通常显示为“销售额2的总和”)。从上下文菜单中选择 值字段设置 。此步骤打开一个对话框,您可以在其中定义字段数据在表格中的汇总和显示方式。
select Value Field Settings from the drop-down list

5。在 值字段设置 对话框中,转到 显示值为 选项卡。从 显示值为 下拉菜单中,选择 占总计的百分比 以计算每个值占总计的比例。可选地,在 自定义名称 字段中输入清晰、描述性的列名,例如“总销售额的百分比”,以便于解释。点击 确定.
set options in the Value Field Settings dialog box

注意:如果您想显示每个值占其父级小计的百分比(而不是整体总计),请从“显示值为”下拉菜单中选择“占父行总计的百分比”。当您的数据集包含分组行(例如商店下的类别)时,此选项特别有价值,因此您可以分析对类别级别总计的贡献。

返回数据透视表后,您现在会看到一个新增列,显示“占总计的百分比”以及原始值。这允许立即进行比较,使您更容易解释哪些项目或类别对总结果贡献最大。
the percent of Grand Total column is in the pivot table

注意:当您在第5步中选择 占父行总计的百分比 时,百分比反映了每个项目对其各自小计的贡献(例如,每个产品在商店中的份额),从而更细致地分析您的数据。
get the percent of the Subtotal column

💡 提示与注意事项:

  • 如果您的原始数据包含过滤器或空白,请在设置百分比后仔细检查数据透视表的准确性。
  • 格式可能默认以小数形式显示数字;右键单击百分比列,选择 数字格式,然后选择 百分比 格式。
  • 在某些Excel版本中,字段名称或界面可能略有不同——如果屏幕不完全匹配,请关注一般步骤。
  • 如果“显示值为”选项显示为灰色,请确保数值字段位于 区域并且选择了数据透视表。

以这种方式添加百分比列对于仪表板、快速性能分析以及为演示或管理报告汇总详细数据非常有用。但是,如果需要进一步自定义(例如条件格式或更高级的计算),请考虑使用计算字段或补充Excel公式以获得更大的灵活性。

如果您对替代方法感兴趣,或者需要在标准数据透视表选项之外实现自定义的百分比计算,可以尝试使用Excel公式补充您的报告,甚至使用简单的VBA自动化您的工作流程。这些方法提供了更多控制,尤其是在内置的“显示值为”设置无法满足特殊要求时。



使用Excel公式在数据透视表外部计算总计的百分比

在某些情况下,您可能希望在数据透视表旁边直接显示总计的百分比,或者您需要比内置的“显示值为”功能提供的更多格式化选项。在这种情况下,您可以在数据透视表外部使用Excel公式来进行计算。

1. 找到数据透视表中的数值列(例如,假设您的销售值在单元格范围D5:D10中)。接下来,找到包含总计的单元格(例如D11)。或者,您可以使用GETPIVOTDATA函数更可靠地引用总计。

2. 在相邻列中(例如单元格E5),输入以下公式以计算每个项目的总计百分比:

=D5/$D$11

或者使用更强大的GETPIVOTDATA版本(假设总值字段为“销售额”,数据透视表从单元格D4开始):

=D5/GETPIVOTDATA("Sales", $D$4)

这些公式将每个值除以总计,确保为每一行计算相对百分比。根据实际情况调整字段名称和单元格引用以匹配您的数据透视表布局。

3. 将公式复制到整个数值范围的旁边。为了获得最佳效果,通过选择范围、右键单击、选择 设置单元格格式 并选择 百分比 来将新列格式化为百分比。

实用提示:这种方法为进一步自定义提供了灵活性(例如附加条件或使用条件格式进行颜色编码)。但是,当数据透视表更新时,请仔细检查公式引用是否仍然准确——尤其是当项目或行动态变化时。在这种情况下,使用GETPIVOTDATA有助于防止引用失效。


使用VBA代码将总计的百分比添加到数据透视表

对于需要自动添加总计百分比度量的用户——特别是在为报告创建多个数据透视表时——VBA提供了一种可定制的方法。这种实用解决方案非常适合重复性任务或模板。请按照以下步骤操作:

1. 点击 开发工具 > Visual Basic 打开Microsoft Visual Basic for Applications窗口。在VBA窗口中,点击 插入 > 模块,然后将以下代码复制并粘贴到模块中:

Sub AddPercentOfGrandTotal()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pfNew As PivotField
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    
    If ActiveSheet.PivotTables.Count = 0 Then
        MsgBox "No PivotTable found on this sheet.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    Set pt = ActiveSheet.PivotTables(1)
    
    If pt.DataFields.Count = 0 Then
        MsgBox "No data field found in the PivotTable.", vbExclamation, xTitleId
        Exit Sub
    End If
    
    Set pf = pt.DataFields(1)
    
    ' Check if the field already exists
    Dim fldName As String
    fldName = "Percent of Grand Total"
    On Error Resume Next
    Set pfNew = pt.PivotFields(fldName)
    On Error GoTo 0
    If Not pfNew Is Nothing Then
        MsgBox "Field '" & fldName & "' already exists.", vbInformation, xTitleId
        Exit Sub
    End If

    ' Add new field and apply percentage calculation
    Set pfNew = pt.AddDataField(pt.PivotFields(pf.SourceName), fldName, xlSum)
    With pfNew
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
    End With
End Sub

2。插入代码后,点击 Run button “运行”按钮或按 F5 执行。宏将自动在当前工作表中的现有数据透视表内添加一个显示总计百分比的新字段。

注意和故障排除:此代码假定您的数据透视表已经至少包含一个数据字段。如果您要通过名称定位特定的数据透视表,则可以将ActiveSheet.PivotTables(1)替换为类似ActiveSheet.PivotTables("PivotTable1")的内容。运行新宏之前始终保存您的工作簿,并确保启用了宏(如果代码未运行,请检查信任中心设置)。


相关文章:

最佳Office办公效率工具

🤖 Kutools AI 助手:以智能执行为基础,彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能:查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/倾斜/删除线等)……
15大工具集12项 文本工具添加文本删除特定字符等)|50+种 图表 类型甘特图等)|40+实用 公式基于生日计算年龄等)|19项 插入工具插入二维码从路径插入图片等)|12项 转换工具小写金额转大写汇率转换等)|7项 合并与分割工具高级合并行分割单元格等)| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言!

通过Kutools for Excel提升您的Excel技能,体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能,助您提升效率并节省时间。 点击此处获取您最需要的功能……


Office Tab为Office带来多标签界面,让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读
  • 在同一个窗口的新标签页中打开和创建多个文档,而不是分多个窗口。
  • 可提升50%的工作效率,每天为您减少数百次鼠标点击!

所有Kutools加载项,一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件,以及Office Tab Pro,非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置(支持MSI)
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册,无需信用卡
  • 超高性价比——比单独购买更实惠