pivot sql 多字段_删除带有宏的Excel Pivot字段

pivot sql 多字段

If you’re rearranging a complex pivot table, it can take a while to manually remove each field. To make the job easier, you can remove Excel pivot fields with macros. There’s a sample macro below that remove all the column fields, and you’ll find more examples, and a free workbook, on my website.

如果要重新排列复杂的数据透视表，则可能需要一段时间才能手动删除每个字段。 为了简化工作，您可以使用宏删除Excel数据透视表字段。 下面有一个示例宏，该宏删除了所有列字段，您可以在我的网站上找到更多示例和免费工作簿。

删除带有宏-列的Excel Pivot字段 (Remove Excel Pivot Fields With Macros - Columns)

This macro will remove all the column fields from the pivot table layout.

此宏将从透视表布局中删除所有列字段 。

If the pivot table has more than one Value field, a Values button is created – you’ll see it in the PivotTable Field List.

如果数据透视表具有多个“值”字段，则会创建一个“值”按钮-您将在“数据透视表字段列表”中看到它。

By default, that button is in the Column area, so if you haven’t moved it, the macro will show a message, asking if you want to remove the Values field.

默认情况下，该按钮在“列”区域中，因此，如果您尚未移动它，宏将显示一条消息，询问您是否要删除“值”字段。

Click Yes if you want to remove the Values field, and click No to keep the values.

如果要删除“值”字段，请单击“是”，然后单击“否”以保留值。

宏代码 (The Macro Code)

To use the macro, copy the following code to a regular code module, then run the macro. See the next section for more details.

若要使用宏，请将以下代码复制到常规代码模块中，然后运行宏。 有关更多详细信息，请参见下一部分。

Sub RemoveAllFieldsCol()
Dim pt As PivotTable
Dim pf As PivotField
Dim lRmv As Long

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.ColumnFields
  lRmv = vbYes
  If pf.Name = "Data" Or pf.Name = "Values" Then
    lRmv = MsgBox("Click Yes to remove Values field from Column area." _
      & vbCrLf _
      & "Click No to keep the Values field.", _
        vbQuestion + vbYesNo, "Remove Values?")
  End If
  If lRmv = vbYes Then
    pf.Orientation = xlHidden
  End If
Next pf

End Sub

如何使用宏 (How to Use the Macro)

To use this macro, copy the code to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.

若要使用此宏， 请将代码复制到工作簿中的常规代码模块 。 然后，选择一个包含数据透视表的工作表，然后运行宏。

NOTE: If there are two or more pivot tables on the sheet, the macro will affect the first pivot table only.

注意：如果工作表上有两个或多个数据透视表，则该宏将仅影响第一个数据透视表。

Set pt = ActiveSheet.PivotTables(1)

If you want to select a pivot table, and then run the macro, change the Set pt line to the following:

如果要选择数据透视表，然后运行宏，请将“ 设置点”行更改为以下内容：

Set pt = Activecell.PivotTable

更多宏以删除数据透视表字段 (More Macros to Remove Pivot Fields)

You can find more macros to remove pivot fields, on the Remove Pivot Fields page on my Contextures website. There is code to remove Row fields, Filter fields, Value fields and All Fields.

在我的Contextures网站上的“删除数据透视字段”页面上，您可以找到更多的宏来删除数据透视字段 。 有代码删除行字段，筛选器字段，值字段和所有字段。

You can also download the free workbook on that page, with all the macros, and some test data. Scroll down to the Download section, and click the link, to get the workbook.

您还可以在该页面上下载免费的工作簿，其中包含所有宏和一些测试数据。 向下滚动到“下载”部分，然后单击链接以获取工作簿。

翻译自: https://contexturesblog.com/archives/2016/12/15/remove-excel-pivot-fields-with-macros/

pivot sql 多字段