如何实现Excel数据透视表计算字段先向上取整再求和？

问题核心 #

Excel透视表的计算字段会先对数据源进行聚合（求和），再执行计算逻辑，所以你用=ROUNDUP([Field 1], 0)得到的是求和后再取整的结果，而非预期的先取整再求和。

可行解决方案 #

方案1：用Power Query生成带取整列的虚拟数据源（无需修改原数据） #

这种方法不需要改动原始数据源，通过Power Query创建一个包含取整后值的新查询表，再以此为基础做透视：

• 选中原始数据源区域，点击「数据」选项卡 →「从表格/区域」（Excel 2016及以后版本可用，旧版本需安装Power Query插件）
• 在Power Query编辑器中，点击「添加列」→「自定义列」，输入公式：

  = Number.RoundUp([Field 1], 0)
  

  给新列命名（比如「Field 2 取整后」）
• 点击「关闭并上载」→ 选择「仅创建连接」，再右键连接 →「加载到」→「表」→ 选择新工作表作为存放位置
• 用这个新生成的表作为透视表数据源，将「Field 2 取整后」拖入值区域选择「求和」，即可得到先取整再求和的结果

方案2：VBA动态计算并更新透视表结果 #

如果需要和原透视表联动，每次刷新透视表自动计算正确结果，可以用VBA实现：

1. 按Alt+F11打开VBA编辑器，插入一个标准模块，粘贴以下代码：

Sub RoundUpThenSumInPivot()
    Dim pvt As PivotTable
    Dim pvtRow As PivotItem
    Dim wsData As Worksheet
    Dim lastRow As Long
    Dim sumVal As Double
    Dim i As Long
    
    ' 请根据实际情况修改以下参数
    Const PVT_SHEET_NAME As String = "透视表工作表" ' 透视表所在工作表名称
    Const PVT_NAME As String = "数据透视表1" ' 透视表名称
    Const DATA_SHEET_NAME As String = "数据源" ' 原始数据源所在工作表名称
    Const ROW_FIELD_COL As String = "A" ' 行文本字段在数据源中的列号
    Const FIELD1_COL As String = "B" ' Field 1在数据源中的列号
    Const RESULT_FIELD_NAME As String = "Field 2" ' 透视表中存放结果的字段名称
    
    ' 绑定对象
    Set pvt = ThisWorkbook.Worksheets(PVT_SHEET_NAME).PivotTables(PVT_NAME)
    Set wsData = ThisWorkbook.Worksheets(DATA_SHEET_NAME)
    lastRow = wsData.Cells(wsData.Rows.Count, ROW_FIELD_COL).End(xlUp).Row
    
    ' 遍历每个行项目，计算取整后求和值
    For Each pvtRow In pvt.PivotFields(wsData.Cells(1, ROW_FIELD_COL).Value).PivotItems
        sumVal = 0
        ' 遍历数据源匹配行项目，累加取整后的值
        For i = 2 To lastRow
            If wsData.Cells(i, ROW_FIELD_COL).Value = pvtRow.Name Then
                sumVal = sumVal + WorksheetFunction.RoundUp(wsData.Cells(i, FIELD1_COL).Value, 0)
            End If
        Next i
        ' 将结果写入透视表对应单元格
        pvt.DataBodyRange.Cells(pvtRow.Position, pvt.PivotFields(RESULT_FIELD_NAME).Position).Value = sumVal
    Next pvtRow
End Sub

2. 如果需要每次刷新透视表自动运行计算，在透视表所在的工作表模块中粘贴以下代码：

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ' 匹配透视表名称，仅指定透视表刷新时触发
    If Target.Name = "数据透视表1" Then
        RoundUpThenSumInPivot
    End If
End Sub

3. 修改代码中的常量参数（工作表名称、列号等），保存并运行RoundUpThenSumInPivot宏即可。

注意事项 #

• 方案1生成的查询表会和原数据源联动，修改原数据后刷新查询表即可同步更新
• 方案2的VBA代码需要启用宏，保存文件时选择.xlsm格式

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