vba Excel：为 XY 图表中的数据点添加标签
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26117035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Add labels to data points in XY chart
提问by Dr. Manuel Kuehner
I want to have labels next to data points in an Excel chart. There is a VBA code from Microsoft for this purpose:
我想在 Excel 图表中的数据点旁边有标签。为此，Microsoft 提供了一个 VBA 代码：
http://support2.microsoft.com/kb/914813/en-us
http://support2.microsoft.com/kb/914813/en-us
Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub
It works so far. But only if the collection has no name:
到目前为止它有效。但仅当集合没有名称时：
When I name the collection then the macro returns an error:
当我命名集合时，宏返回一个错误：
Does anyone know how to use the code provided by Mircosoft and still be able to name the data collection?
有谁知道如何使用 Mircosoft 提供的代码并且仍然能够命名数据集合？
回答by Elizabeth
I had the same problem. All you need to do is replace the hardcoded '9' with 'InStr(xVals, ",")' and it will accept any length SERIES name in the field before the first comma.
我有同样的问题。您需要做的就是将硬编码的 '9' 替换为 'InStr(xVals, ",")'，它会在第一个逗号之前的字段中接受任何长度的 SERIES 名称。
回答by nicolas dejean
There already are some good answers like ZAT's one, explaining how to add labels to a data point in native Excel with VBA language.
已经有一些很好的答案，比如 ZAT 的答案，解释了如何使用 VBA 语言在本机 Excel 中向数据点添加标签。
But if you don't know anything about VBA it might be difficult to understand. For complex charts like this one I prefer to use Javascript which I think is more "readable" than VBA. And if you want to make a dynamic and interactive chart, javascript comes with a lot of powerful libraries.
但是，如果您对 VBA 一无所知，则可能难以理解。对于像这样的复杂图表，我更喜欢使用 Javascript，我认为它比 VBA 更“可读”。如果你想制作一个动态的交互式图表，javascript 自带了很多强大的库。
Here is a working code I have written for you, with plotly.js(the documentation is very good for js beginners) :
这是我为您编写的工作代码，使用plotly.js（该文档非常适合 js 初学者）：
https://www.funfun.io/1/#/edit/5a60bbe7404f66229bda3e39
https://www.funfun.io/1/#/edit/5a60bbe7404f66229bda3e39
So to build this chart I put my data in the embedded spreadsheet, which I can then use in my javascript code thanks to a Json file.
所以为了构建这个图表，我将我的数据放在嵌入的电子表格中，然后我可以在我的 javascript 代码中使用它，这要归功于 Json 文件。
I can create a scatter plot like so :
我可以像这样创建散点图：
var trace1 = {
x: firstX,
y: firstY,
text: firstLabel,
mode: 'markers+text',
textposition:'top right'
};
The firstXand firstYvariable are the X and Y values.
的firstX和firstY可变的是X和Y值。
To add a label to each point I added a label to textand changed the mode to marker+textinstead of just marker.
将标签添加到每个点我加了一个标签，text并改变了模式，marker+text而不是只marker。
Once you've made your chart you can load it in Excel by passing the URL in an Excel add-in called Funfun.
制作图表后，您可以通过在名为Funfun的 Excel 加载项中传递 URL 将其加载到 Excel 中。
Here is how it looks like:
这是它的样子：
Disclosure : I'm a developer of funfun
披露：我是 funfun 的开发者
回答by Jon Peltier
Excel 2013 introduced the capability to label a chart series with data from cells, after many years of users begging for it. Select the series, and add data labels. Select the data labels and format them. Under Label Options in the task pane, look for Label Contains, select the Value From Cells option, and select the range containing the label text.
经过多年用户的请求，Excel 2013 引入了使用单元格数据标记图表系列的功能。选择系列，并添加数据标签。选择数据标签并格式化它们。在任务窗格中的标签选项下，查找标签包含，选择来自单元格的值选项，然后选择包含标签文本的范围。
And even before this, you could use a free add-in called the XY Chart Labeler (which works on all charts that support data labels, not just XY charts), which you can download from Applications Professionals. It's written by Rob Bovey, a former Microsoft Excel MVP.
甚至在此之前，您还可以使用名为 XY Chart Labeler（它适用于支持数据标签的所有图表，而不仅仅是 XY 图表）的免费插件，您可以从Applications Professionals下载该插件。它由前 Microsoft Excel MVP Rob Bovey 编写。
回答by ZAT
Try this after chart generation (assuming chart in the same sheet): (modify this according to your need)
在图表生成后试试这个（假设图表在同一张表中）：（根据你的需要修改）
Option Explicit
Sub RenameChartDataLabel()
Dim rngDLabel As Range
Dim iii as integer, pp as integer, dlcount as integer
Set rngDLabel = ActiveSheet.Range("A2:A6") 'change range for datalabels text
ActiveSheet.ChartObjects("Chart 2").Activate 'change chart name
dlcount = ActiveChart.SeriesCollection(1).DataLabels.Count
iii = 1
pp = 1
For iii = dlcount To 1 Step -1
ActiveChart.SeriesCollection(1).DataLabels(iii).Select
Selection.Text = rngDLabel(pp).Value
Selection.Font.Bold = True
Selection.Position = xlLabelPositionAbove
pp = pp + 1
Next
Set rngDLabel = Nothing
End Sub
