如何在Excel中将数据标签添加到气泡图 [英] how to add data label to bubble chart in excel

问题描述

我想向气泡图添加自定义数据标签.我的代码如下.目前，数据标签是指XValues.我想用气泡大小填充数据标签.您能帮我如何自定义以下代码吗?

Hi I want to add customize data label to my bubble chart. my code is below. at the moment data label refer to XValues. I would like my data label fill with bubble size. would u mind help me how I can customize below code?

我尝试添加.DataLabel.Text ="txt"，但是我在下面收到了 错误: 运行时错误"438":对象不支持此属性或方法

I tried to add .DataLabel.Text = "txt" but I received below error: Run-time error '438':Object doesn't support this property or method

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 red, green, blue As Integer


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)
        .Format.Fill.Solid
        .Format.Fill.ForeColor.RGB = RGB(61, 161, 161)
     '   .DataLabel.Text = "txt"
    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

结束子

我的输入示例:

Label          Hour Day count
01-SUNDAY       14  1   1
01-SUNDAY       19  1   1
02-MONDAY       12  2   1
02-MONDAY       13  2   1
02-MONDAY       14  2   2
02-MONDAY       16  2   2

推荐答案

DataLabel.Text是Point而不是NewSeries

此代码:

For r = 2 To Selection.Rows.Count
    With bubbleChart.Chart.SeriesCollection.NewSeries
        [...]
        .DataLabel.Text = "txt"
    End With 
Next

...试图标记系列，但失败.

...attempts to label the series, and fails.

将这段代码识别为多系列气泡图"的另一个著名示例，这是一个逻辑假设，我们每个序列只需要处理1个数据点，这使得以下代码成为解决方案:

Recognizing this code as being from another famous example of "multi-series Bubble Charts", it is a logical assumption that we only need to handle 1 data point per series, which makes the following code the solution:

For r = 2 To Selection.Rows.Count
    With bubbleChart.Chart.SeriesCollection.NewSeries
        [...]
        .Points(1).HasDataLabel = True
        .Points(1).DataLabel.Text = "txt"
    End With 
Next

这篇关于如何在Excel中将数据标签添加到气泡图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！