Excel：在XY图表中为数据点添加标签-Excel:AddlabelstodatapointsinXYchart

2  

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.

我有同样的问题。您需要做的就是用'InStr(xVals,“,”)'替换硬编码的'9',它将在第一个逗号之前的字段中接受任何长度的SERIES名称。

2  

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

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.

因此,为了构建此图表,我将我的数据放入嵌入电子表格中,然后由于Json文件,我可以在我的Javascript代码中使用它。

I can create a scatter plot like so :

我可以像这样创建一个散点图:

var trace1 = {
  x: firstX,
  y: firstY,
  text: firstLabel,
  mode: 'markers+text',
  textposition:'top right'
};

The firstX and firstY variable are the X and Y values.

firstX和firstY变量是X和Y值。

To add a label to each point I added a label to text and changed the mode to marker+textinstead of just marker.

要为每个点添加标签,我在文本中添加了标签,并将模式更改为标记+ text而不仅仅是标记。

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的开发者

2  

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下载。它由前微软Excel MVP Rob Bovey编写。

1  

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