VBA气泡图

Question

我有四根由几行组成的柱子。有些是充满信息的，有些是空的，如下所示。

 w     x     y   z
Blue   23   74   120
White  50   25   34
Grey   11   45   
Yellow 25   12   12
Black  11   22 

我想要做的是让每一行代表一个‘泡泡’在一个泡泡excel图。气泡的大小将等于z。多亏了汤姆·霍兰德( Tom Hollander )，我找到了一种管理它的方法，我调整了代码，使我正在寻找的东西，在下面发现汤姆的one.Here每个气泡代表一个意甲，所以有自己的标签。我现在唯一的问题是，当Z轴为空时，我想告诉我的代码不要创建气泡，也就是不要创建标签。

有什么想法吗？

我还试图找到一种方式，标签顺序遵循气泡顺序，即最高的气泡将首先有标签，等等。

Public Sub CreateMultiSeriesBubbleChart()
    If (selection.Columns.Count <> 4 Or selection.Rows.Count < 3) Then
        MsgBox "Selection must have 4 columns and at least 2 rows"
        Exit Sub
    End If

    Dim bubbleChart As ChartObject
    Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=selection.Left, 
Width:=600, Top:=selection.Top, Height:=400)
    bubbleChart.chart.ChartType = xlBubble
    Dim r As Integer
    For r = 2 To selection.Rows.Count
        With bubbleChart.chart.SeriesCollection.NewSeries
            .Name = "=" & selection.Cells(r, 1).Address(External:=True)
            .XValues = selection.Cells(r, 2).Address(External:=True)
            .Values = selection.Cells(r, 3).Address(External:=True)
            .BubbleSizes = selection.Cells(r, 4).Address(External:=True)
        End With

    Next

    bubbleChart.chart.SetElement 
(msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    bubbleChart.chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & 
selection.Cells(1, 2).Address(External:=True)

    bubbleChart.chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    bubbleChart.chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & 
selection.Cells(1, 3).Address(External:=True)

    bubbleChart.chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    bubbleChart.chart.Axes(xlCategory).MinimumScale = 0
End Sub

非常感谢您的光临或帮助。

Answer 1

只需添加if语句即可。

Public Sub CreateMultiSeriesBubbleChart()
    If (Selection.Columns.Count <> 4 Or Selection.Rows.Count < 3) Then
        MsgBox "Selection must have 4 columns and at least 2 rows"
        Exit Sub
    End If

    Dim bubbleChart As ChartObject
    Set bubbleChart = ActiveSheet.ChartObjects.Add(Left:=Selection.Left, Width:=600, Top:=Selection.Top, Height:=400)
    bubbleChart.Chart.ChartType = xlBubble
    Dim r As Integer
    For r = 2 To Selection.Rows.Count
        If Selection.Cells(r, 4) <> "" Then '<~~ z is not empty
            With bubbleChart.Chart.SeriesCollection.NewSeries
                .Name = "=" & Selection.Cells(r, 1).Address(External:=True)
                .XValues = Selection.Cells(r, 2).Address(External:=True)
                .Values = Selection.Cells(r, 3).Address(External:=True)
                .BubbleSizes = Selection.Cells(r, 4).Address(External:=True)
            End With
        End If
    Next

    bubbleChart.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    bubbleChart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 2).Address(External:=True)

    bubbleChart.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    bubbleChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=" & Selection.Cells(1, 3).Address(External:=True)

    bubbleChart.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    bubbleChart.Chart.Axes(xlCategory).MinimumScale = 0
End Sub