1、 首先在excel中任意添加一个列状图
2、 编辑Excel—》》宏
3、 在对应的sheet上进行处理
//定义几个变量
Dim rowCount As Integer
Dim endRowIndex As Integer
Dim currentYear As String
Dim lastYear As String
//获取指定单元格的信息,这里Me就是当前的sheet
currentYear = Me.Range("C5").Value
Dim rowCount As Integer
lastYear = Me.Range("D5").Value Dim endRowIndex As Integer
Dim currentYear As String
startRowIndex = 5 Dim lastYear As String
endRowIndex = Me.Range("AO1").Value currentYear = Me.Range("C5").Value
endRowIndex = 7 lastYear = Me.Range("D5").Value
Dim columnChart1 As Chart startRowIndex = 5
//拿到第一个图吧对象
With ActiveSheet.ChartObjects(1).Chart endRowIndex = Me.Range("AO1").Value
//对标题进行处理
.ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年投资完成率对比" endRowIndex = 7
//绑定数据源,name,x,y
.SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumns Dim columnChart1 As Chart
With .SeriesCollection(1)
.HasDataLabels = True With ActiveSheet.ChartObjects(1).Chart
.Name = Me.Range("C5").Value
End With .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年投资完成率对比" + lastYear + "年投资完成率对比"
With .SeriesCollection(2)
.HasDataLabels = True .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumnstartRowIndex, "B" & endRowIndex), Sheets("test").Range("C" & startRowIndex, "C" & endRowIndex), Sheets("test").Range("D" & startRowIndex, "D" & endRowIndex)), Excel.XlRowCol.xlColumns
.Name = Me.Range("D5").Value With .SeriesCollection(1)
End With .HasDataLabels = True
.Name = Me.Range("C5").Value
End With End With
With .SeriesCollection(2)
.HasDataLabels = True
With ActiveSheet.ChartObjects(2).Chart .Name = Me.Range("D5").Value
End With
.ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年资金完成率对比"
End With
.SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumns
With .SeriesCollection(1)
.HasDataLabels = True With ActiveSheet.ChartObjects(2).Chart
.Name = Me.Range("C5").Value
End With .ChartTitle.Characters.Text = currentYear + "年及" + lastYear + "年资金完成率对比" + lastYear + "年资金完成率对比"
With .SeriesCollection(2)
.HasDataLabels = True .SetSourceData Union(Sheets("test").Range("B" & startRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumnstartRowIndex, "B" & endRowIndex), Sheets("test").Range("F" & startRowIndex, "F" & endRowIndex), Sheets("test").Range("G" & startRowIndex, "G" & endRowIndex)), Excel.XlRowCol.xlColumns
.Name = Me.Range("D5").Value With .SeriesCollection(1)
End With .HasDataLabels = True
.Name = Me.Range("C5").Value
End With End With
With .SeriesCollection(2)
.HasDataLabels = True
.Name = Me.Range("D5").Value
End With
End With
End Sub