跨不同图表组更改系列绘图顺序或图例输入顺序 [英] Change Series plotorder or legend entry order across different ChartGroups
问题描述
Office 365
我在 powerpoint 中有一个组合图表.一个系列是列式，其他三个系列是线.它们都在主 Y 轴上.X 轴为类别类型(文本标签)
I have a combination chart in powerpoint. One series is column type, and the other three are lines. They are all in the primary Y axis. The X-axis is of category type (text labels)
这是一个关于 Powerpoint 如何构建图表的示例
Here is an example on how Powerpoint is structuring the chart
ChartGroup | Chart Type | PlotOrder | Legend | Correct Order
1 | Column | 1 | B | 2
2 | Line | 1 | A | 1
2 | Line | 2 | C | 3
2 | Line | 3 | D | 4
目前，图例是这样显示的
Currently, the legend is being displayed like this
B A C D
我需要图例显示为
A B C D
这意味着图例 A 将显示在 B 之前，即使它位于索引高于 B 的图表组的另一个图表组中.
This means that the legend A would be displayed before B even if it's in another chartgroup with a higher index than B's chartgroup.
我正在添加数据快照和正在发生的事情的可视化示例:这是我正在使用的虚拟数据:
I'm adding data snapshots and visual examples of what is happening: Here is the dummy data I'm using:
这是系列的初始顺序，当它们都是相同的类型并且在同一个ChartGroup中时，系列图例的顺序是需要的.
This is the initial order of series, when all of them are the same type and in the same ChartGroup, the order of the Series Legend are as required.
但是当我将 Series 1 Value 类型从 Line 更改为 Column 时，就会发生这种情况，一个新的图表组被创建，Series1 值放在开头:
But then when I change the Series 1 Value type from Line to Column, this is what happens, a new chartgroup is created, and the Series1 Value is put at the beginning:
即使是数据源窗口中的图表顺序也和开始时一样:
Even the Chart Order in the Data Source Window is as it was in the beginning:
Series1 值的公式仍然将其顺序设为 2:=SERIES(Sheet1!$B$1,Sheet1!$A$1:$A$17,Sheet1!$B$2:$B$17,**2**)
And the formula for the Series1 Value still puts it's order as 2:
=SERIES(Sheet1!$B$1,Sheet1!$A$1:$A$17,Sheet1!$B$2:$B$17,**2**)
当它在不同的图表组中时，是否可以将系列 1 目标图例从第二个位置移动到第一个位置?
Is it possible to move Series1 Goal Legend from the second position to the first position when it's in different ChartGroups?
我正在考虑一些想法:
- 在折线图ChartGroup中添加一个虚拟系列以在第二个位置显示Series1 Value图例，然后从柱形图中删除图例
- 是否可以在折线图下方添加填充以使其看起来像柱形图?
欢迎任何建议，
谢谢，
推荐答案
我认识到您的问题.由于图表被分组，图例的显示会发生变化，因此不太可能将图表和图例链接起来.
I recognized your problem. As the charts are grouped, the display of the legend changes, so it is unlikely that the chart and the legend will be linked.
所以我决定使用快捷方式.我用正确的图例和修改后的图表制作了两个宏图表，只复制了第一个图表的图例部分，并制作了一个宏以粘贴为第二个图表的图例部分的图片.分辨率有问题.
So I decided to use shortcuts. I made two macro charts with the correct legend and a modified chart, and copied only the legend part of the first chart, and made a macro to paste as a picture in the legend part of the second chart. There is a flaw in the resolution.
Sub ModifyCahrtLegend()
Dim Cht1 As Chart, Cht2 As Chart, Cht3 As Chart
Dim Shp As Shape, Shp1 As Shape, Shp2 As Shape, Shp3 As Shape
Dim Ws As Worksheet
Dim rngX As Range
Dim rngHeader As Range
Dim Srs As Series
Dim fn As String
Dim obj As ChartObject
Dim l, t, w, h
Dim cl, tb, ct
Dim i As Integer
Dim a As Variant
Set Ws = ActiveSheet
For Each obj In Ws.ChartObjects
obj.Delete
Next obj
With Ws
Set rngX = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
Set rngHeader = .Range("b1")
End With
a = Array(1, 0, 2, 3)
r = rngX.Rows.Count
Set Shp1 = Ws.Shapes.AddChart(, Range("g1").Left, 100, 500, 300)
Set Cht1 = Shp1.Chart
With Cht1
For Each Srs In .SeriesCollection
Srs.Delete
Next Srs
.ChartType = xlLine
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
For i = 0 To 3
Set Srs = .SeriesCollection.NewSeries
With Srs
.XValues = rngX
.Values = rngHeader.Offset(0, a(i)).Offset(1).Resize(r)
.Name = rngHeader.Offset(0, a(i))
End With
Next i
End With
Set Shp2 = Ws.Shapes.AddChart(, Range("g1").Left, 100, 500, 300)
Set Cht2 = Shp2.Chart
With Cht2
For Each Srs In .SeriesCollection
Srs.Delete
Next Srs
.ChartType = xlLine
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
For i = 0 To 3
Set Srs = .SeriesCollection.NewSeries
With Srs
.XValues = rngX
.Values = rngHeader.Offset(0, a(i)).Offset(1).Resize(r)
.Name = rngHeader.Offset(0, a(i))
End With
Next i
Set Srs = .SeriesCollection(2)
With Srs
.ChartType = xlColumnClustered
End With
End With
With Cht1
t = .Legend.Top
l = .Legend.Left
h = .Legend.Height
w = .Legend.Width
' .CopyPicture
End With
'** picture editing
Cht1.CopyPicture
Range("C23").Select
Ws.Pictures.Paste
n = Ws.Shapes.Count
Set Shp = Ws.Shapes(n)
With Shp1
cl = (.Width - w) / 2
cb = .Height - t - h
ct = .Height - h - cb
End With
With Shp
.PictureFormat.CropLeft = cl
.PictureFormat.CropRight = cl
.PictureFormat.CropTop = ct
.PictureFormat.CropBottom = cb
End With
Set Cht3 = Ws.Shapes.AddChart.Chart
Set obj = Cht3.Parent
With obj
.Top = t
.Left = l
.Height = h
.Width = w
.ShapeRange.Line.ForeColor.RGB = RGB(255, 255, 255)
End With
Shp.CopyPicture
Cht3.Paste
fn = "legend.png"
Cht3.Export fn, "PNG"
Shp.Delete
obj.Delete
Shp1.Delete
Set Shp = Cht2.Shapes.AddPicture(fn, msoFalse, msoCTrue, l, t, w, h)
Kill fn
End Sub
但是这种方式创建的图表在调整大小时会变形，所以最好通过设置宽度和高度来制作图表.
However, the chart created in this way is deformed when resizing, so it would be better to make a chart by setting the width and height.
Sub testChart()
ModifyLegend 500, 200 '<~~ set width, Height
End Sub
Sub ModifyLegend(myW, myH)
Dim Cht1 As Chart, Cht2 As Chart, Cht3 As Chart
Dim Shp As Shape, Shp1 As Shape, Shp2 As Shape, Shp3 As Shape
Dim Ws As Worksheet
Dim rngX As Range
Dim rngHeader As Range
Dim Srs As Series
Dim fn As String
Dim obj As ChartObject
Dim l, t, w, h
Dim cl, tb, ct
Dim i As Integer
Dim a As Variant
Set Ws = ActiveSheet
For Each obj In Ws.ChartObjects
obj.Delete
Next obj
With Ws
Set rngX = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
Set rngHeader = .Range("b1")
End With
a = Array(1, 0, 2, 3)
r = rngX.Rows.Count
Set Shp1 = Ws.Shapes.AddChart(, Range("g1").Left, 100, myW, myH)
Set Cht1 = Shp1.Chart
With Cht1
For Each Srs In .SeriesCollection
Srs.Delete
Next Srs
.ChartType = xlLine
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
For i = 0 To 3
Set Srs = .SeriesCollection.NewSeries
With Srs
.XValues = rngX
.Values = rngHeader.Offset(0, a(i)).Offset(1).Resize(r)
.Name = rngHeader.Offset(0, a(i))
End With
Next i
End With
Set Shp2 = Ws.Shapes.AddChart(, Range("g1").Left, 100, myW, myH)
Set Cht2 = Shp2.Chart
With Cht2
For Each Srs In .SeriesCollection
Srs.Delete
Next Srs
.ChartType = xlLine
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
For i = 0 To 3
Set Srs = .SeriesCollection.NewSeries
With Srs
.XValues = rngX
.Values = rngHeader.Offset(0, a(i)).Offset(1).Resize(r)
.Name = rngHeader.Offset(0, a(i))
End With
Next i
Set Srs = .SeriesCollection(2)
With Srs
.ChartType = xlColumnClustered
End With
End With
With Cht1
t = .Legend.Top
l = .Legend.Left
h = .Legend.Height
w = .Legend.Width
' .CopyPicture
End With
'** picture editing
Cht1.CopyPicture
Range("C23").Select
Ws.Pictures.Paste
n = Ws.Shapes.Count
Set Shp = Ws.Shapes(n)
With Shp1
cl = (.Width - w) / 2
cb = .Height - t - h
ct = .Height - h - cb
End With
With Shp
.PictureFormat.CropLeft = cl
.PictureFormat.CropRight = cl
.PictureFormat.CropTop = ct
.PictureFormat.CropBottom = cb
End With
Set Cht3 = Ws.Shapes.AddChart.Chart
Set obj = Cht3.Parent
With obj
.Top = t
.Left = l
.Height = h
.Width = w
.ShapeRange.Line.ForeColor.RGB = RGB(255, 255, 255)
End With
Shp.CopyPicture
Cht3.Paste
fn = "legend.png"
Cht3.Export fn, "PNG"
Shp.Delete
obj.Delete
Shp1.Delete
Set Shp = Cht2.Shapes.AddPicture(fn, msoFalse, msoCTrue, l, t, w, h)
Kill fn
End Sub
这篇关于跨不同图表组更改系列绘图顺序或图例输入顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
