excel - 将两种不同类型的图表添加到 Excel 并设置它们的格式
问题描述
我有一个由两个主要内容组成的图表。
第一个是一个循环,它根据值创建一堆系列。这些系列中的每一个都是带有线条的 XY 散点图。这些行中的每一个都根据使用Vlookup
Excel 中的函数的条件进行着色。我需要纠正的第一件事是该Case
部分,因为它不喜欢 G 的第一个实例。这仅在我添加第二个图表时发生。
接下来我想要创建一个带有另一个范围的 XY 散点图,然后仅将自定义数据标签应用于这些点。我可以使用以下已更新的答案来更改 Chart the Series 绘图的类型。
Dim age1 As Variant
Dim age2 As Variant
Dim per1 As Variant
Dim per2 As Variant
Dim id as Variant
Dim mp as Range
Dim yd as Range
id = Range(Range("A2"), Range("A2").End(xlDown)).Value2
age1 = Range(Range("C2"), Range("C2").End(xlDown)).Value2
age2 = Range(Range("D2"), Range("D2").End(xlDown)).Value2
per1 = Range(Range("E2"), Range("E2").End(xlDown)).Value2
per2 = Range(Range("E2"), Range("E2").End(xlDown)).Value2
Set mp = Range(Range("J2"), Range("J2").End(xlDown))
Set yd= Range(Range("E2"), Range("E2").End(xlDown))
ln = UBound(id) - LBound(id) + 1
Set cht = ws.ChartObjects(1).Chart
With cht
.ChartArea.ClearContents 'Clears the chart so a new one can be created
.ChartType = xlXYScatterLines 'Defines the Chart as a Scatter with Lines
For i = 1 To ln 'First Thing that creates many series
xdata = Array(age1(i, 1), age2(i, 1))
ydata = Array(per1(i, 1), per2(i, 1))
.SeriesCollection.NewSeries
.SeriesCollection(i).XValues = xdata
.SeriesCollection(i).Values = ydata
.SeriesCollection(i).Name = id(i, 1)
Next i
'Orginal method: .ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(.SeriesCollection.Count).XValues = mp
.SeriesCollection(.SeriesCollection.Count).Values = yd
.SeriesCollection(.SeriesCollection.Count).Name = "Series"
'New Method
.SeriesCollection(.SeriesCollection.Count).ChartType = xlXYScatter
End With
'end of creating charts
Set drng = Range(Range("A2"), Range("B2").End(xlDown) 'For the Vlookup
With ActiveSheet
For Each xycht In .ChartObjects
For Each mysrs In xycht.Chart.SeriesCollection
mysrs.MarkerStyle = xlMarkerStyleCircle
lnum = Application.VLookup(mysrs.Name, drng, 2, 0) 'This fails the first instance with G as a Type Mismatch Error.
' Select Case lnum
' Case "G"
' lColor = RGB(255, 0, 0)
' Case "D"
' lColor = RGB(0, 255, 0)
' Case "M"
' lColor = RGB(0, 0, 255)
' Case "A"
' lColor = RGB(0, 0, 0)
' Case Else
' lColor = RGB(255, 255, 255)
' End Select
' mysrs.MarkerBackgroundColor = lColor
' mysrs.Format.Line.Visible = msoFalse
' mysrs.Format.Line.Visible = msoTrue
' mysrs.Format.Line.ForeColor.RGB = lColor
Next
Set mypts = ws.ChartObjects(1).SeriesCollection(SeriesCollection.Count).Points(1).Apply 'This fails cause it needs an Object
mypts(mypts.Count).ApplyDataLabels
With mypts(mypts.Count).DataLabel
.ShowSeriesName = False
.ShowCategoryName = False
.ShowValue = False 'I need this tonot show Values, but my own Values.
' optional parameters
.Position = xlLabelPositionAbove
.Font.Name = "Helvetica"
.Font.Size = 10
.Font.Bold = False
End With
Next
End With
解决方案
使用 Series 对象的 ChartType 属性...
.SeriesCollection(.SeriesCollection.Count).ChartType = xlXYScatter
推荐阅读
- html - 即使隐藏,HTML 选取框标签也会在后台运行吗?
- javascript - 基于数组对象属性值的 joi 验证
- batch-file - 重命名没有模式的多个文件
- android - 我无法在实时设备上运行演示颤振应用程序
- ios - 当我在我的应用程序中使用 Mapbox sdk 时,无法从文件中读取属性列表,Info.plist 问题
- arrays - 问题:引用数组值,但返回零
- javascript - 按钮 onclick 的奇怪行为
- r - 从 igraph 中的特定集群中检索节点和边列表
- encryption - RSA加密/解密方法
- windows - 无法在 Windows 10 上更改注册表