Excel气泡图重叠数据标签 [英] excel bubble chart overlapping data label

问题描述

当下表中的criteria1和criteria2具有相同的值时，气泡图存在问题.数据标签和数据系列相互重叠.在这种情况下，很难阅读它们.该如何解决?

I am facing an issue with a bubble chart when criteria1 and criteria2 in the below table have the same values. The data label and data series overlap each other. In such cases making it difficult to read them. How can this be fixed?

+------------+-----------+-----------+
|    City    | criteria1 | criteria2 |
+------------+-----------+-----------+
| Thane      |         4 |         3 |
| Mumbai     |         3 |         2 |
| Pune       |         5 |         1 |
| Goa        |         2 |         3 |
| Chandigarh |         3 |         1 |
+------------+-----------+-----------+

重叠问题

推荐答案

在图表旁边添加了刷新按钮，用于调整数据标签.下面是按钮后面的代码.

Added a refresh button next to chart which adjust the data labels. Below is the code behind the button.

 Sub MoveLabels()

    Dim sh As Worksheet
    Dim ch As Chart
    Dim sers As SeriesCollection
    Dim ser As Series
    Dim i As Long, pt As Long
    Dim dLabels() As DataLabel

    Set sh = ActiveSheet
    Set ch = sh.ChartObjects("Chart 1").Chart
    Set sers = ch.SeriesCollection

    ReDim dLabels(1 To sers.Count)
    For pt = 1 To sers(1).Points.Count
        For i = 1 To sers.Count
            Set dLabels(i) = sers(i).Points(pt).DataLabel
        Next

        resetLabels dLabels
        AdjustLabels dLabels  ' This Sub is to deal with the overlaps
    Next
End Sub


Private Sub AdjustLabels(ByRef v() As DataLabel)

    Application.ScreenUpdating = False

    Dim i As Long, j As Long, adj As Long
    Dim temp_a As String, temp_b As String

    For i = LBound(v) To UBound(v) - 1
    For j = LBound(v) + 1 To UBound(v)

        temp_a = v(i).Caption
        temp_b = v(j).Caption

        Debug.Print temp_a & " - | - " & temp_b


        v(i).Caption = "a"
        v(j).Caption = IIf(temp_a = temp_b, "a", "b")
        ActiveSheet.ChartObjects("Chart 1").Activate


        If ((v(j).Top = v(i).Top) And (v(i).Caption <> v(j).Caption) And (v(j).Left = v(i).Left)) Then

            Select Case v(j).Position
            Case xlLabelPositionAbove
                    v(j).Position = xlLabelPositionRight
            Case xlLabelPositionRight
                    v(j).Position = xlLabelPositionBelow
            Case xlLabelPositionBelow
                    v(j).Position = xlLabelPositionLeft
            Case xlLabelPositionLeft
                    v(j).Position = xlLabelPositionAbove
            End Select

        End If


        v(i).Caption = temp_a
        v(j).Caption = temp_b

       temp_a = vbNullString
       temp_b = vbNullString


    Next j, i

     Application.ScreenUpdating = True

End Sub



Sub resetLabels(ByRef v() As DataLabel)

    For i = LBound(v) To UBound(v) - 1
        v(i).Position = xlLabelPositionAbove
    Next

End Sub

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