Excel VBA检查数据透视字段的格式是数字还是百分比

Question

我不知道这是否可能，但我正在尝试设计一个宏，将所有数据透视字段以数字或百分比的形式添加到数据透视表的值部分。

以下是我目前所拥有的：

If MsgBox("Do you want to add all pivot table fields as values?", vbOKCancel + vbQuestion, "Accept/Reject") = vbCancel Then
Exit Sub
End If
Dim pt As PivotTable
Dim pf As PivotField
Dim pivotTableName As String
On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then
pivotTableName = InputBox("Enter the pivot table name.")
Set pt = ActiveSheet.PivotTables(pivotTableName)
End If
For Each pf In pt.PivotFields
pt.AddDataField pt.PivotFields(pf.Name), pf.Name & " ", xlSum
pt.PivotFields(pf.Name & " ").NumberFormat = "#,##0"
Next pf

但接下来我想添加一个if语句，看看字段是否应该格式化为百分比。我的想法是：

1. 检查字段中的单个记录值，看看它是否小于100%(或1)(这在我所处的95%情况下是理想的)

2. 检查字段的和是否小于任意数，例如<100(不完美)

3. 检查字段名称中是否有%(我可能不得不使用)

   对于pt.PivotFields 中的每个fld

   如果InStr(fld.Value，"%")，则

   下一个

4. 检查字段是否为计算字段(不完美)

   对于pt.PivotFields 中的每个fld

   如果fld。IsCalculated然后

   下一个

前两种可能吗？有人有更好的主意吗？

谢谢！

p.S。我感兴趣的另一件事是检查字段是否包含文本，或者字段是否无法求和。

Answer 1

如果有人感兴趣，我会这么做：

子自动添加字段()

If MsgBox("Do you want to add all pivot table fields as values?", vbOKCancel + vbQuestion, "Accept/Reject") = vbCancel Then
Exit Sub
End If
Dim pt As PivotTable
Dim pf As PivotField
Dim pivotTableName As String
On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then
pivotTableName = InputBox("Enter the pivot table name.")
Set pt = ActiveSheet.PivotTables(pivotTableName)
End If
For Each pf In pt.PivotFields
If InStr(pf.Value, "%") > 0 Then
pt.AddDataField pt.PivotFields(pf.Name), pf.Name & " ", xlSum
pt.PivotFields(pf.Name & " ").NumberFormat = "0.0%"
Else
If pf.IsCalculated Then
If InStr(pf.Formula, "/") > 0 Then
pt.AddDataField pt.PivotFields(pf.Name), pf.Name & " ", xlSum
pt.PivotFields(pf.Name & " ").NumberFormat = "0.0%"
Else
pt.AddDataField pt.PivotFields(pf.Name), pf.Name & " ", xlSum
pt.PivotFields(pf.Name & " ").NumberFormat = "#,##0"
End If
Else
pt.AddDataField pt.PivotFields(pf.Name), pf.Name & " ", xlSum
pt.PivotFields(pf.Name & " ").NumberFormat = "#,##0"
End If
End If
Next pf
For Each pf In pt.PivotFields
If (pt.GetPivotData(pf.Value & " ") = 0) Then
pt.PivotFields(pf.Value & " ").Orientation = xlHidden
End If
Next pf

结束子