如何将Pivot Table粘贴为值后保留小计/总计公式并可编辑数据？

针对你需要将透视表转为可编辑数据+保留小计求和公式的需求，提供两种实用方法，适配不同数据量场景：

一、手动批量处理（适合小规模透视表） #

1. 先将透视表粘贴为值和格式到新工作表或空白区域，确保所有数据和格式保留，但原透视表公式被清除。
2. 选中所有小计/总计单元格：
   • 利用透视表小计的格式特征（通常为加粗），按Ctrl+F打开查找窗口，点击「格式」按钮，选择小计单元格的格式（比如加粗字体），点击「查找全部」，再按Ctrl+A选中所有匹配的单元格。
3. 批量插入求和公式：
   • 对于行小计，假设当前小计单元格在B5，对应数据行是B3:B4，直接输入=SUM(B3:B4)，然后按Ctrl+Enter批量填充所有选中的小计单元格。若透视表结构规整，这个方法效率尚可。

二、VBA宏批量生成（适合大量行列小计的场景） #

手动处理大规模透视表过于繁琐，用VBA可自动识别透视表的小计/总计行，批量插入正确的求和公式：

宏代码示例 #

Sub KeepSubtotalFormulas()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim rng As Range
    Dim cell As Range
    
    ' 绑定当前选中单元格所在的透视表
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0
    If pt Is Nothing Then
        MsgBox "请先选中透视表中的任意单元格", vbExclamation
        Exit Sub
    End If
    
    ' 将透视表复制为值到右侧空白区域
    pt.TableRange2.Copy
    With pt.TableRange2.Offset(0, pt.TableRange2.Columns.Count + 2)
        .PasteSpecial xlPasteValuesAndNumberFormats
        .PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    
    ' 遍历所有行字段，给行小计添加求和公式
    For Each pf In pt.RowFields
        For Each pi In pf.PivotItems
            If Not pi.DataRange Is Nothing And pi.ShowDetail = False Then
                Set rng = pi.DataRange.Resize(1, pt.DataFields.Count)
                For Each cell In rng
                    ' 自动计算当前小计对应的数据源行数，生成求和公式
                    cell.Formula = "=SUM(" & cell.Offset(-pi.RecordCount, 0).Resize(pi.RecordCount, 1).Address(False, False) & ")"
                Next cell
            End If
        Next pi
    Next pf
    
    ' 处理列总计的求和公式
    If pt.ColumnGrand Then
        Set rng = pt.ColumnGrandRange
        For Each cell In rng
            cell.Formula = "=SUM(" & cell.Offset(0, -rng.Columns.Count + 1).Resize(1, rng.Columns.Count - 1).Address(False, False) & ")"
        Next cell
    End If
    
    MsgBox "处理完成：数据为可编辑值，小计/总计已添加自动求和公式", vbInformation
End Sub

使用步骤 #

1. 打开Excel文件，按Alt+F11打开VBA编辑器。
2. 右键点击左侧的工作簿名称，选择「插入」→「模块」，将上述代码粘贴到模块窗口中。
3. 回到Excel界面，选中透视表内的任意单元格。
4. 按Alt+F8打开宏窗口，选择KeepSubtotalFormulas并点击「执行」。
5. 宏会在原透视表右侧生成一个独立副本：数据区域是可编辑的纯值（可修改T0、T1等数据），所有小计/总计单元格自动插入对应数据源范围的求和公式，修改数据后小计会自动更新。

注意事项 #

• 确保透视表已开启「行小计」和「列总计」功能（在透视表选项中可设置）。
• 宏会识别折叠状态的行组作为小计项，若需要展开组的小计，需先手动折叠对应组再运行宏。

内容的提问来源于stack exchange，提问作者Luqman Hakim