r语言 数据透视表功能_从工作表下拉列表中选择数据透视表功能

r语言 数据透视表功能

Last week, Chandoo interviewed me for his Online Excel VBA School, and we talked about using VBA with Excel Pivot Tables.

上周，Chandoo为其在线Excel VBA School采访了我，我们讨论了将VBA与Excel Pivot Tables一起使用的问题。

If you drop a field into the Values area, and that field contains blank cells or text, it's added as "Count of", instead of "Sum of".

如果将字段放入“值”区域，并且该字段包含空白单元格或文本，则会将其添加为“计数”，而不是“总和”。

With a macro, you can quickly change all the fields from Count to Sum, instead of fixing each pivot table summary function manually.

使用宏，您可以快速将所有字段从Count更改为Sum ，而无需手动修复每个数据透视表汇总功能 。

选择摘要功能 (Select a Summary Function)

One way to let users change the function is to create a drop down list of functions on the worksheet. Then, event code runs when the cell changes, and the selected function is shown in the pivot table.

让用户更改功能的一种方法是在工作表上创建功能的下拉列表。 然后，事件代码在单元格更改时运行，并且所选功能显示在数据透视表中。

The cell with the drop down list is named FuncSel, as you can see in the NameBox in the screen shot above.

带有下拉列表的单元格名为FuncSel，如您在上方屏幕截图中的NameBox中所见。

On another sheet, that could be hidden from the users, there is a list of functions, and a formula that looks up the numeric value for each function. The cell with the formula is named FuncSelCode.

在另一张可以向用户隐藏的工作表上，有一个函数列表和一个公式，用于查找每个函数的数值。 具有公式的单元格名为FuncSelCode。

这个怎么运作 (How It Works)

When the FuncSel cell is changed, the Worksheet_Change code on that sheet runs.

更改FuncSel单元格后，该工作表上的Worksheet_Change代码将运行。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("FuncSel").Address Then
    ChangeAllData (wksLists.Range("FuncSelCode").Value)
End If
End Sub

The ChangeAllData procedure runs, using the numeric value in the FuncSelCode cell, and changes all the data fields in the pivot table.

ChangeAllData过程使用FuncSelCode单元中的数值运行，并更改数据透视表中的所有数据字段。

Sub ChangeAllData(lFn As Long)
'changes data fields to selected function
On Error GoTo errHandler
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Application.ScreenUpdating = False
Set pt = wksPTSales.PivotTables(1)
On Error GoTo errHandler
pt.ManualUpdate = True
For Each pf In pt.DataFields
  pf.Function = lFn
Next pf
pt.ManualUpdate = False
exitHandler:
Set pf = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

下载样本文件 (Download the Sample File)

To see the code, and test it with the sample data, you can download the Pivot Table Summary Function Change workbook.

要查看代码并使用示例数据进行测试，可以下载“ 数据透视表摘要功能更改”工作簿 。

The file is in Excel 2007 format, and zipped. Enable macros when you open the file. ______________

该文件为Excel 2007格式，并已压缩。 打开文件时启用宏。 ______________

翻译自: https://contexturesblog.com/archives/2011/06/22/select-pivot-table-function-from-worksheet-drop-down/

r语言 数据透视表功能