用线在XY散点上居中放置数据标签 [英] Center Data Label over XY scatter with lines

问题描述

我正在创建以下图表：

但是我需要每个数据标签位于要创建的直线上而不是直线终点的中心。我有以下内容：

 设置mypts = mysrs.Points 
 mypts（mypts.Count）.ApplyDataLabels 
 
与mypts（mypts.Count）.DataLabel 
 .ShowSeriesName = True 
 .ShowCategoryName = False 
 .ShowValue = False 
'可选参数
。方向= 0 
。位置= xlLabelPosition高于
 .Font.Size = 10 
 .Font.Bold =真
以
结尾 

是否有人知道如何将数据标签居中放置？我通过给每个系列起一个名字来给它命名？

解决方案

在每行上方放置标签居中将涉及创建另一个计算该行中心的数据系列。在无法访问您的数据的情况下，我转向了@JonPeltier出色的

现在，设置中点数据并不困难。中间点是快速公式 =（（D2-A2）/ 2）+ A2 ，向下复制到列中。

添加到图表中，选择 MidPoint和 Value列，然后 cntl + c 复制数据，然后选择图表并选择性粘贴作为新的数据系列。

最后，格式化标签以仅显示来自单元格的值（取消选中 Y数据标签），并将标记设置为无。然后，您便获得了行的居中标签。

I am in the process of creating the following chart:

But I need each data label to be centered over the line that gets created instead of the end point of the line. I have the following:

    Set mypts = mysrs.Points
    mypts(mypts.Count).ApplyDataLabels

        With mypts(mypts.Count).DataLabel
            .ShowSeriesName = True
            .ShowCategoryName = False
            .ShowValue = False
            ' optional parameters
            .Orientation = 0
            .Position = xlLabelPositionAbove
            .Font.Size = 10
            .Font.Bold = True
        End With

Does any body know how to center the data labels, which I gave it by giving each series a name?

解决方案

Centering a label above each line will involve creating another data series that calculates the line's center. Without having access to your data, I turned to @JonPeltier 's excellent post on connecting two XY data series.

Your question involves your custom VBA code. My example here should be taken as the steps needed to set up the data. At your choice you can either set up your data on the worksheet, or have your VBA code create this "extra" data series to add to the chart (again, because I don't know what your data looks like I can't recommend a solution specific to your problem).

I won't repeat all the details of creating the chart, but here's an example of the data and the resulting chart:

Now, setting up the mid-point data isn't difficult. The mid-point is a quick formula =((D2-A2)/2)+A2, copied down the column.

Added to the chart, select the "MidPoint" and "Value" columns and cntl+c copy the data, then select the chart and "Paste Special" as a new data series.

A Quick Note: DO NOT put your mid-point data on the same rows as the A and B data. I did this initially and Excel (in its own private wisdom) associated the data with column A. The results on the chart aren't pretty.

Next to last step is to select the new data series and select "Add Data Labels", then check the box for "Value From Cells". Choose the data range for the labels, e.g. cells C17:C20.

Finally, format the labels to only show the "Value From Cells" (uncheck the "Y" data label) and set the marker to "None". Then you have the centered label for your line.

这篇关于用线在XY散点上居中放置数据标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！