使用 VBA 创建数据透视图-解网

问：

我编写了以下代码来从数据透视表创建数据透视图

代码运行良好，但是当它到达 .ApplyDataLabels 行

当我单击“结束”但没有出现数据标签时，图形仍然会创建：

Sub Graph_Test_3()

Dim chtObj As ChartObject
Dim PvtSht As Worksheet
Dim PvtTbl As PivotTable
Dim ser As Series

'set the Pivot sheet
Set PvtSht = Worksheets("Pivots And Graphs")

' set the Pivot Table object
Set PvtTbl = PvtSht.PivotTables("PivotTable5")

' set the Chart Object
Set chtObj = PvtSht.ChartObjects.Add(300, 200, 550, 200)

' modify ChartObject properties
With chtObj
    .Chart.SetSourceData PvtTbl.TableRange2 'set the chart’s data range to the Pivot-Table’s TableRange2
    .Chart.ChartType = xlColumnStacked
    .Name = "Performance Spread 24" 'change the name of the chart
    .Chart.HasTitle = True 'add a chart title
    .Chart.ChartTitle.Text = "Performance Spread 24" 'set the chart title text
    .Chart.Axes(xlCategory).HasTitle = True 'add an x-axis title
    .Chart.Axes(xlCategory).AxisTitle.Text = "Performance Range" 'set the x-axis title text
    .Chart.Axes(xlValue).HasTitle = True 'add a y-axis title
    .Chart.Axes(xlValue).AxisTitle.Text = "Headcount" 'set the y-axis title text
    .Chart.Axes(xlCategory).MajorGridlines.Delete 'remove the x-axis gridlines
    .Chart.Axes(xlValue).MajorGridlines.Delete 'remove the y-axis gridlines
    For Each ser In .Chart.SeriesCollection
       .ApplyDataLabels xlDataLabelsShowValue
        With ser.DataLabels
            .Position = xlLabelPositionOutsideEnd
            .Font.Size = 12
            .Font.Colour = vbBlack
            'loop through each series in the chart ser
            'position the data labels outside end Next ser End With
        End With
    Next ser
End With
End Sub

我尝试将该行更改为但是，这会给出相同的错误消息和相同的结果.ApplyDataLabels xlDataLabelsShowValue.ApplyDataLabels.Type = xlDataLabelsShowValue

如果这是基本的，很抱歉，我对 VBA 很陌生！

Excel VBA 图形透视表

将循环更改为常规循环，并将索引添加到 .For EachFor.SeriesCollection
删除或更改为有效位置。对堆积柱形图无效。可用选项包括：.Position = xlLabelPositionOutsideEndxlLabelPositionOutsideEnd
1. xlLabelPositionCenter
2. xlLabelPositionInsideEnd
3. xlLabelPositionInsideBase
更改为 ..Colour.Color

    .Chart.Axes(xlValue).MajorGridlines.Delete 'remove the y-axis gridlines
    
    Dim i As Long
    For i = 1 To .Chart.SeriesCollection.Count
        Set ser = .Chart.SeriesCollection(i)
        ser.ApplyDataLabels
        
        With ser.DataLabels
            .Position = xlLabelPositionCenter
            .Font.Size = 12
            .Font.Color = vbBlack
        End With
    Next

上一个：如何透视数据

下一个：如何解析 Merge Colume 到 DataFrame [已关闭]

使用 VBA 创建数据透视图

Using VBA to create a pivot Graph

评论