VBA将堆积柱形图上的数据标签从“值"更改为“系列名称" [英] VBA Change Data Labels on a Stacked Column chart from 'Value' to 'Series name'

问题描述

我想将堆积柱形图上的数据标签从值"更改为系列名称".

I would like to change the data labels on a stacked column chart from 'value' to 'series name'.

我要编辑的图表:

我已经尽我所能在互联网上搜索了最好的东西，而我认为可以做到这一点的唯一方法是使用VBA.我仍然是VBA新手，因此将不胜感激

I have searched the best I could on the internet and the only way I think this can be done is by using VBA. I am still a VBA noob so any help would be appreciated

推荐答案

进入Excel，我选择了要编辑的图表，然后通过转到图表工具">添加图表元素">数据标签">更多数据标签"来选择所有标签.选项

Going into Excel, I select the chart I want to edit and then select all labels by going to Chart Tools > Add Chart Element > Data Labels > More Data Label Options

接下来，我取消选中标签中不需要的任何选项，并在我刚刚打开的对话框的标签选项"下选中所需的选项.

Next I uncheck whatever options I don't want in my labels, and check those I do want, under the "Label Options" in the dialog I just opened.

这对我来说很好用，但是如果不是您想要的，或者您出于某些其他原因想要通过VBA编辑标签，则此脚本代码段会将给定图表的标签设置为系列名称:

This works fine for me, but if it doesn't for you, or you for some other reason want to edit the labels via VBA, this script snippet sets the labels of a given chart to the series name:

Option Explicit

Sub Macro1()
  With ThisWorkbook.Worksheets("Grafar, utvikling").ChartObjects("Graf3").Chart.FullSeriesCollection(2)
    If Not .HasDataLabels Then
      .ApplyDataLabels
    End If
    If Not .DataLabels.ShowSeriesName Then
      .DataLabels.ShowSeriesName = True
    End If
  End With
End Sub

很显然，您需要将工作表名称，图表名称等切换为图表中的任何内容-很难弄清楚，但是如果有任何麻烦，请随时对以下问题进行评论:您确实有任何问题.

Clearly you need to switch the worksheetnames, chart-name, etc. to whatever you have in your chart - it shouldn't be very hard to figure out, but if you have any trouble feel free to comment on the question if you do have any problems.

我通过使用宏记录器创建了宏，然后编辑结果，它通常可以为您尝试执行的任何任务提供良好的起点.以我的经验，用这种方法在Excel-VBA中找出对象名称/结构要比从头开始找出要容易.

I created the macro by using the macro recorder, and then editing the results, it can often give a good starting point for whatever task you attempt to do. In my experience it can be easier to figure out the object names / structures in Excel-VBA this way, than trying to figure it out from scratch.

考虑到上述情况并没有达到您的期望，我怀疑图表中的每一列都是一个序列.要在图表中快速显示每个数据系列的SeriesName，请尝试遍历图表中的所有系列:

Considering the above doesn't do what you want, I suspect that each column in your chart is one series. To quickly display the SeriesName for each dataseries in the chart, try looping over all the series in the chart:

Sub Macro1()
  Dim se As Series

  For Each se In ThisWorkbook.Worksheets("Grafar, utvikling").ChartObjects("Graf3").Chart.SeriesCollection
    If Not se.HasDataLabels Then
      se.ApplyDataLabels
    End If
    If Not se.DataLabels.ShowSeriesName Then
      se.DataLabels.ShowSeriesName = True
    End If
    If se.DataLabels.ShowValue Then
      se.DataLabels.ShowValue = False
    End If
  Next se

End Sub

这篇关于VBA将堆积柱形图上的数据标签从“值"更改为“系列名称"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！