excel - 带有#N/A 值的数据透视表？

Question

我正在创建一个自动创建数据透视表的宏。我能够获得数据透视表的输出，但所有数据值都是#N/A。这是由于我在数据透视表中使用的列包含 #N/A 值。

如何获得所有行的实际总和，而不必调整数据集？这是我目前使用的代码。

Sub pivottable()

Dim PSheet As Worksheet, DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As pivottable
Dim PField As PivotField
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim PvtTable As pivottable
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True

Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets(1)
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(4, 1), _
TableName:="PivotTable4")

Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="PivotTable")

Sheets("PivotTable").Select
Set PvtTable = ActiveSheet.PivotTables("PivotTable")

'Rows
    With PvtTable.PivotFields("Office")
        .Orientation = xlRowField
        .Position = 1
    End With

    With PvtTable.PivotFields("Type")
        .Orientation = xlRowField
        .Position = 2
    End With
'Columns
    With PvtTable.PivotFields("Category")
        .Orientation = xlColumnField
        .Position = 1
    End With

PvtTable.AddDataField PvtTable.PivotFields("Receipts"), "receipts", xlSum

End Sub

我读到有可能使用 AutoFilter .会 AutoFilter能够忽略具有#N/A 值或“0”的数据并继续对其他值求和，因此我们避免在数据透视表中出现#N/A。

Answer 1

包裹您的VLOOKUP IFERROR 中的函数函数，所以 #N/A值不会在源数据中返回。