通过VBA在Excel数据透视表中显示具有重复数量的重复行
通过VBA在Excel数据透视表中显示具有重复数量的重复行
提问于 2017-03-07 02:24:04
我有一个材料清单(BOM)表，其中有重复的项目，为不同的功能。我有一个VBA代码来自动创建这个表的枢轴表，以查看函数列的总价值。
A B C D
Description Quantity Cost($) Function
1 Pr. Vessel_A 1 320 Pre Treat
2 Pr. Vessel_A 1 320 Post Treat
3 Pr. Vessel_A 1 320 Primary RO
4 Pr. Vessel_A 1 320 Pre Treat
5 Pr. Vessel_A 20 6400 Secondary RO
6 Membrane_00B 5 505 Pre Treat
7 Membrane_00B 5 505 Primary RO
.
.
.所以我想看到的是：
Function Quantity Cost($) Description
1 Pre Treat 1 320 Pr. Vessel_A
2 Pre Treat 1 320 Pr. Vessel_A
3 Pre Treat 5 505 Membrane_00B
4 Total Pre Treat 1145
5 Primary RO 5 505 Membrane_00B
6 Primary RO 20 6400 Pr. Vessel_A
7 Total Primary RO 6905
8 Post Treat 1 320 Pr. Vessel_A
9 Total Post Treat 320
10 Secondary RO 1 320 Pr. Vessel_A
11 Total Secondary RO 320
.
.
.我使用这个代码已经很长时间了，但是今天我意识到，如果项目描述和数量在相同的函数中是相同的，那么表只向我显示其中一个.。
在本例中，我只看到了1 quantity Pr。预处理功能的Vessel_A而2量的成本。所以好的，总成本是正确的，但数量不是。而不是这样，我需要看到两条不同的行，也可以正确地看到数量。
我现在看到的是：
Function Quantity Cost($) Description
1 Pre Treat 1 640 Pr. Vessel_A
2 Pre Treat 5 505 Membrane_00B
3 Total Pre Treat 1145 我一直试图自己纠正这个问题，但没能做到这一点。也没有在网上找到确切的解决方案。那么，我应该如何编辑我的代码，以同时看到重复的描述行与重复的数量在我的枢轴表？这是我的密码：
Sub CreateProcesSectList()
'Create worksheet "ProcessSectionsList" if it doesn't exist. And then Create a Process Sections Pivot Table
Dim Baslik1, Baslik2, Baslik3, Baslik4, Baslik5 As String
Baslik1 = Sheet5.Range("F2").Value 'Items' Descriptions
Baslik2 = Sheet5.Range("H2").Value 'Process Sections
Baslik3 = Sheet5.Range("I2").Value 'Quantity
Baslik4 = Sheet5.Range("K2").Value 'Unit cost
Baslik5 = Sheet5.Range("M2").Value 'Total Cost
Application.ScreenUpdating = False
CreateSheetIf ("ProcessSectionsList")
Sheets("ProcessSectionsList").Select
Columns("A:AK").Select
Range("A1").Activate
Selection.Delete Shift:=xlToLeft
Sheet5.Select
Application.Goto Reference:="R100000C6"
Selection.End(xlUp).Select
ActiveCell.CurrentRegion.Select
Set tblo = ActiveCell.CurrentRegion
tblo.Offset(1, 0).Resize(tblo.Rows.Count - 1, _
tblo.Columns.Count).Select
'Sheet5.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"R2C2:R388C22", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="ProcessSectionsList!R1:R1048576", TableName:="ProcessSectionsPivotTable", _
DefaultVersion:=xlPivotTableVersion14
Range("a1").Select
Sheets("ProcessSectionsList").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1)
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik3)
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik4)
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("ProcessSectionsPivotTable").AddDataField ActiveSheet.PivotTables( _
"ProcessSectionsPivotTable").PivotFields(Baslik5), "Count of Total Cost", xlCount
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik1)
.LayoutForm = xlTabular
.RepeatLabels = True
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik3)
.LayoutForm = xlTabular
.RepeatLabels = True
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik4)
.LayoutForm = xlTabular
.RepeatLabels = True
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
End With
Range("C223").Select
ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields("Count of Total Cost"). _
Function = xlSum
Range("A1").Select
With ActiveSheet.PivotTables("ProcessSectionsPivotTable").PivotFields(Baslik2)
.PivotFilters.Add Type:=xlCaptionDoesNotBeginWith, Value1:="0"
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("ProcessSectionsPivotTable")
.TableStyle2 = "PivotStyleLight20"
.ShowDrillIndicators = False
End With
Range("a1").Select
ActiveSheet.PivotTables("ProcessSectionsPivotTable").CompactLayoutRowHeader = Baslik2
Columns("d:e").Select
Selection.Style = "Currency"
Columns("B:B").ColumnWidth = 54.14
Range("a1").Select
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub只要我有".RepeatLabels = True“来表示Baslik1和Baslik3 (数量和描述)，那么缺少什么呢？
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-05-18 17:51:33
我通过向表中添加另一个数据列来解决这个问题。
在这种情况下，只要新添加的列中的值不同，我的表就需要重复所有的项。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42639040
复制相关文章
相似问题
腾讯云开发者
