在图表中添加水平线

Question

>我正在从表格中生成图表。

我想在图表中有一条水平线。我将其称为设定值，数据可在表的 E 列中找到。

另外，我想更改m轴刻度。我想要带有百分号的轴值从 0 - 10。

任何线索都会有所帮助。我已经尝试过用我的图表来解决这个问题。

Sub chartRedResultPer()
Dim Rng, rng1, rng2  As Range
Dim cht1 As Object
Set rng1 = ActiveSheet.Range("A2:A53")
Set rng2 = ActiveSheet.Range("D2:D53")
Set Rng = Union(rng1, rng2)
Set Sh = ActiveSheet.ChartObjects.Add(Left:=320, _
Width:=600, _
Top:=70, _
Height:=250)
Sh.Select
Set cht1 = ActiveChart
With cht1
.SetSourceData Source:=Rng
.ChartType = xlColumnClustered
cht1.Axes(xlSecondary).TickLabels.NumberFormat = "0%"
cht1.SeriesCollection(1).delete
End With
cht1.SeriesCollection(1).Name = "Red "
cht1.SeriesCollection(1).HasDataLabels = True
cht1.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
cht1.HasTitle = True
cht1.ChartTitle.Text = "Result 2017"
End Sub

Answer 1

添加另一个范围

Dim rng3 as Range
Set rng3 = ActiveSheet.Range("E2:E53")
With cht1.SeriesCollection.NewSeries
.Name = "SetPoint"
.Values = rng3
.Type = xlLine
.MarkerStyle = None
.Format.Line.ForeColor.RGB = RGB(0, 0, 0)
End With
With cht1.Axes(xlValue)
.MaximumScale = 10
.MinimumScale = 0
End With

Answer 2

代码是这样的。

Sub ColumnChart()
Dim i As Integer, j As Integer, n As Integer
Dim rng1 As Range, rng2   As Range, rng3 As Range
Dim cht1 As Chart, Sh As ChartObject
Set rng1 = ActiveSheet.Range("A2:A53")
Set rng2 = ActiveSheet.Range("D2:D53")
Set rng3 = ActiveSheet.Range("e2:e53")
Set Sh = ActiveSheet.ChartObjects.Add(Left:=320, _
Width:=600, _
Top:=70, _
Height:=250)
Set cht1 = Sh.Chart
With cht1
n = .SeriesCollection.Count
If n > 0 Then
For j = n To 1 Step -1
.SeriesCollection(j).Delete
Next j
End If
.HasTitle = True
.ChartType = xlColumnClustered
.Legend.Position = xlLegendPositionRight
With .ChartTitle
.Characters.Text = "Result 2017"
.Characters.Font.Size = 12
End With
i = i + 1
.SeriesCollection.NewSeries
With .SeriesCollection(i)
.Name = "Red "
.XValues = rng1
.Values = rng2
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
.ApplyDataLabels
End With
With .SeriesCollection(i).DataLabels
.ShowValue = True
.Font.Size = 7
End With
i = i + 1
.SeriesCollection.NewSeries
With .SeriesCollection(i)
.Name = "Pecent "
.XValues = rng1
.Values = rng3
.AxisGroup = 2
.ChartType = xlLine
.Format.Line.ForeColor.RGB = RGB(0, 255, 0)
End With

.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Red "
.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "#,###"
.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0%"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x Axes"
End With

End Sub