我试图从一组测试数据创建一个数据透视表 . 它几乎正常工作，但要显示图表，数据总和必须在行标签框中 .
我可以手动轻松完成，但这应该可以使用VBA . 表和图表的其余部分是使用VBA生成的，它运行良好，但我不知道如何将SUM字段移动到行标签
将数据总和从列标签移动到行标签：
sourceSheet.Select
Set dataRange = sourceSheet.Range(sourceSheet.Cells(startRow, 1), sourceSheet.Cells(currentRow - 1, startColumnVals + columnArraySize - 1))
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, dataRange)
Set pTable = pCache.createPivotTable(pivotSheet.Range("A42"))
' Page fields are the filters
Set pField = pTable.PivotFields("Result")
pField.Orientation = xlPageField
' Column fields will apprear in the Legend
Set pField = pTable.PivotFields("TestName")
pField.Orientation = xlColumnField
' Row Fields - don't know what to do here !
' Data field
For i = 0 To (columnArraySize - 1)
cellText = sourceSheet.Cells(startRow, startColumnVals + i).Text
Set pField = pTable.PivotFields(cellText)
pField.Orientation = xlDataField
pField.Name = "S" & cellText & "-" & i
pField.Function = xlSum
Next
Set dataRange = pTable.TableRange1
Set pChaObj = pivotSheet.ChartObjects.Add(Left:=10, Width:=1000, Top:=15, Height:=500)
Set pChart = pChaObj.Chart
pChart.SetSourceData dataRange
pChart.ChartType = xlLine
1 回答
这是你想要的？