Excel宏来修复折线图中重叠的数据标签 [英] Excel macro to fix overlapping data labels in line chart
问题描述
我正在搜索/尝试使用宏来修复带有一个或多个系列集合的折线图中数据标签的位置,以便它们不会重叠。
I am searching/trying to make a macro to fix the position of data labels in a line chart with one or multiple series collections so that they will not overlap each other.
我在想我的宏的一些方法,但当我试图让它我明白这对我来说太难了,我很头疼。
I was thinking of some ways for my macro but when I try to make it I understand that this is way too hard for me and I get headache.
有什么我错过了吗?
下面是一个带有重叠数据标签的示例图表:
Here's an example chart with overlapped data labels:
这里是一个示例图表,其中我手动修复数据标签:
Here's an example chart where I manually fixed the data labels:
/ p>
推荐答案
此任务基本上分为两个步骤:访问 / code>对象以获取
标签
和操作标签位置以避免重叠。
This task basically breaks down to two steps: access the Chart
object to get the Labels
, and manipulate the label positions to avoid overlap.
对于给定的样本,所有系列都绘制在公共X轴上,X值充分展开,标签在此维度中不重叠。因此,提供的解决方案只处理每个X点的标签组。
For the sample given all series are plotted on a common X-axis and the X values are sufficiently spread that labels don't overlap in this dimension. Therefore the solution offered only deals with groups of labels for each X point in turn.
Sub
解析图表,并为每个X点依次创建标签
数组
This Sub
parses the chart and creates an array of Labels
for each X point in turn
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
AdjustLabels dLabels ' This Sub is to deal with the overlaps
Next
End Sub
检测重叠
这会使用 Labels
的数组调用 AdjustLables
。这些标签需要检查重叠
Detect Overlaps
This calls AdjustLables
with an array of Labels
. These labels need to be checked for overlap
Sub AdjustLabels(ByRef v() As DataLabel)
Dim i As Long, j As Long
For i = LBound(v) To UBound(v) - 1
For j = LBound(v) + 1 To UBound(v)
If v(i).Left <= v(j).Left Then
If v(i).Top <= v(j).Top Then
If (v(j).Top - v(i).Top) < v(i).Height _
And (v(j).Left - v(i).Left) < v(i).Width Then
' Overlap!
End If
Else
If (v(i).Top - v(j).Top) < v(j).Height _
And (v(j).Left - v(i).Left) < v(i).Width Then
' Overlap!
End If
End If
Else
If v(i).Top <= v(j).Top Then
If (v(j).Top - v(i).Top) < v(i).Height _
And (v(i).Left - v(j).Left) < v(j).Width Then
' Overlap!
End If
Else
If (v(i).Top - v(j).Top) < v(j).Height _
And (v(i).Left - v(j).Left) < v(j).Width Then
' Overlap!
End If
End If
End If
Next j, i
End Sub
移动标签
当检测到重叠时,您需要一个移动一个或两个标签而不创建另一个标签的策略
这里有许多可能性,您没有给予足够的细节来判断您的要求。
Moving Labels
When an overlap is detected you need a strategy that move one or both labels without creating another overlap.
There are many possibilities here, you havn'e given sufficient details to judge your requirements.
对于这种方法,你需要一个版本的Excel,它有DataLabel.Width和DataLabel.Height属性。版本2003 SP2(和,大概,早期)不。
For this approach to work you need a version of Excel that has DataLabel.Width and DataLabel.Height properties. Version 2003 SP2 (and, presumably, earlier) does not.
这篇关于Excel宏来修复折线图中重叠的数据标签的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!