2007数据透视表如何删除_删除数据透视表标题中的总和

2007数据透视表如何删除

When you're building a pivot table, if you add fields to the Values area, Excel automatically adds "Sum of" or "Count of" to the start of the field name. You can manually remove that text, or use macros to quickly change the headings. There is one macro example here, and more on my Contextures website.

在构建数据透视表时，如果将字段添加到“值”区域，则Excel会自动在字段名称的开头添加“总和”或“计数”。 您可以手动删除该文本，或使用宏快速更改标题。 这里有一个宏示例，我的Contextures网站上有更多示例。

自动标题文字 (Automatic Heading Text)

This screen shot shows a couple of fields in the Values area of a pivot table.

此屏幕快照显示了数据透视表的“值”区域中的几个字段。

• The Quantity heading has be revised, to remove the "Sum of" text
  数量标题已修改，以删除“总和”文本
• The TotalPrice heading has not be changed, and still shows the "Sum of" text.
  TotalPrice标题未更改，仍然显示“ Sum of”文本。

手动更改标题 (Manually Change a Heading)

To remove the "Sum of" or "Count of" from a single value heading, just type over the existing heading, to fix it.

要从单个值标题中删除“总和”或“计数”，只需在现有标题上键入即可进行修复。

NOTE: Excel won't allow you to use the exact name of the field, such as "Quantity". Add a space character at the beginning or end of the text, and Excel with accept that as a valid heading.

注意：Excel不允许您使用字段的确切名称，例如“ Quantity”。 在文本的开头或末尾添加一个空格字符，然后使用Excel将其接受为有效的标题。

请参阅手动更改步骤 (See the Steps for Manual Change)

To see the steps for renaming the headings manually, watch this short video.

要查看手动重命名标题的步骤，请观看此简短视频。

演示地址

用宏删除和 (Remove Sum Of With a Macro)

If you want to quickly change multiple headings, to remove "Sum of", or other automatic text, use a macro.

如果要快速更改多个标题，要删除“总和”或其他自动文本，请使用宏。

Previously, I've posted macros to fix the headings on Normal pivot tables. However, those macros might not work correctly in newer versions of Excel – depending on how you build your pivot tables.

以前，我发布了宏以修复Normal透视表上的标题 。 但是，这些宏在Excel的较新版本中可能无法正常工作-取决于您如何构建数据透视表。

数据透视表类型 (Pivot Table Types)

if you create a pivot table in newer versions of Excel, there is a check box for "Add this data to the Data Model".

如果在较新版本的Excel中创建数据透视表，则存在一个复选框“将数据添加到数据模型”。

• If you check that box, Excel creates an OLAP-based pivot table, instead of a Normal pivot table.
  如果选中此框，则Excel将创建基于OLAP的数据透视表，而不是“普通”数据透视表。
• If you DO NOT check that box, Excel creates an Normal pivot table
  如果您不选中该框，Excel将创建一个普通数据透视表

SourceName属性 (SourceName Property)

In the original macros, which work nicely with Normal pivot tables, the code uses the SourceName property for each Value field, and adds a space character at the end.

在可以与Normal数据透视表配合使用的原始宏中，代码对每个Value字段使用SourceName属性，并在末尾添加一个空格字符。

If the column heading in the source data is "QtySold", Excel returns "QtySold" for the SourceName property.

如果源数据中的列标题为“ QtySold”，则Excel为SourceName属性返回“ QtySold”。

For OLAP-based pivot tables, the SourceName property returns a structured name for the Value field, similar to this:

对于基于OLAP的数据透视表，SourceName属性返回Value字段的结构化名称，类似于以下内容：

• [Measures].[Sum of QtySold]
  [度量]。[QtySold的总和]

That would take extra work to clean up, when running a macro.

运行宏时，这将需要额外的工作来清理。

SourceCaption属性 (SourceCaption Property)

For OLAP-based pivot tables, I've use the SourceCaption property. It returns the heading from the Value field in the pivot table layout. For example, "Sum of QtySold"

对于基于OLAP的数据透视表，我使用了SourceCaption属性。 它从数据透视表布局的“值”字段返回标题。 例如，“ QtySold之和”

That's easier to clean up, because it doesn't have the brackets and periods that are in the SourceName property.

这很容易清理，因为它没有SourceName属性中的方括号和句点。

删除总和的宏 (Macro to Remove Sum Of)

Here is one of my new macros to remove Sum Of from the Value Headings. It is designed for OLAP-based pivot tables, and fixes the pivot table for the selected cell on the worksheet.

这是我将从值标题中删除“总和”的新宏之一。 它是为基于OLAP的数据透视表而设计的，并修复了工作表上所选单元格的数据透视表。

Before you run the macro:

在运行宏之前：

• make a backup copy of your workbook, just to be safe.
  为了安全起见，请制作工作簿的备份副本。
• then, select a pivot table cell, and run the macro.
  然后，选择数据透视表单元格，然后运行宏。

宏代码– OLAP (The Macro Code – OLAP)

There are two procedures listed below. The first macro, ValueCaptionsSelPT_OLAP, calls the second one, ChangeHead_OLAP.

下面列出了两个过程。 第一个宏ValueCaptionsSelPT_OLAP调用第二个宏ChangeHead_OLAP。

Be sure to copy both procedures into your workbook.

确保将两个过程都复制到工作簿中。

Sub ValueCaptionsSelPT_OLAP()
'for normal pivot tables
'removes the "Sum of"
'   from Value field headings
'   in selected pivot table only
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo exitHandler

If pt Is Nothing Then
  MsgBox "Select a pivot table cell" _
      & vbCrLf & "and try again"
  GoTo exitHandler
End If
  
Application.EnableEvents = False
Application.ScreenUpdating = False
  
  ChangeHead_OLAP pt

exitHandler:
  Set pt = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

errHandler:
  Resume exitHandler

End Sub

Copy this procedure too.

也复制此过程。

Sub ChangeHead_OLAP _
  (ByRef myPT As PivotTable)
''Called in ValueCaptions macros

Dim pf As PivotField
Dim lNum As Long
Dim lFind As Long
Dim strFind As String
Dim strHead01 As String
Dim strHead02 As String
strFind = " of "

myPT.ManualUpdate = True

For Each pf In myPT.DataFields
  strHead01 = pf.SourceCaption
  lFind = InStrRev(strHead01, strFind) _
          + Len(strFind) - 1
  strHead02 = Replace(strHead01, _
    Left(strHead01, lFind), "")
  pf.Caption = strHead02 & " "
Next pf

myPT.RefreshTable
myPT.ManualUpdate = False

Set pf = Nothing

End Sub

获取所有删除的宏总数 (Get All the Remove Sum Of Macros)

To get the rest of the macros that remove Sum Of from the Value headings, go to the Pivot Table Value Heading Macros page.

要获取从“值”标题中删除“总和”的其余宏，请转到“ 数据透视表值”标题页面 。

You can copy the code from that page, and add it to your workbook. Or, download the sample files, to get the code, and pivot tables for testing.

您可以从该页面复制代码，并将其添加到您的工作簿中。 或者，下载示例文件以获取代码，并透视表进行测试。

翻译自: https://contexturesblog.com/archives/2018/07/26/remove-sum-of-in-pivot-table-headings/

2007数据透视表如何删除