EXCEL 用VBA產生圖表

大家好阿！玩遊戲開外掛，當然上班也要開外掛。

這篇VBA的目的是將整理過的原始資料，批次製作成圖表，並且完成圖表格式的修改；要注意的重點是正確的指向目前處理到的列號。

Sub 圖表繪圖()
' 定義 myChart 為圖表物件
Dim myChart As ChartObject
' 設定圖表的大小
CHART_X = 400
CHART_Y = 250
 
 
' 初始化，並以 LOAD_SEQ_COUNT 做為處理到哪行的指標(PC;Process Count)
LOAD_SEQ_COUNT = 1
 
For j = 0 To 29
    For i = 0 To 3
' 建立一個 i x j 的矩陣 4 x 30
' 1 □□□□□□□□□□□□□□□□□□□□□□
' 2 □□□□□□□□□□□□□□□□□□□□□□
' 3 □□□□□□□□□□□□□□□□□□□□□□
' 4 □□□□□□□□□□□□□□□□□□□□□□
Set myChart = ActiveSheet.ChartObjects.Add(1 + j * CHART_X, 1 + i * CHART_Y, CHART_X, CHART_Y)
' 上行計算目前處理到哪一個圖表的計數器

With myChart.Chart
    ' ↓ 定義圖表為帶線的x-y散佈圖
    .ChartType = xlXYScatterLines
    ' ↓ 定義資料範圍
    .SetSourceData Source:=Range("F2:CG3")
    ' ↓ 每一步驟將row遞增
    LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
 
    ' ↓ 設定圖表標題
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = "='raw data'!$B$" & LOAD_SEQ_COUNT & ":$B$" & LOAD_SEQ_COUNT & ""
    ' ↑ B欄位放著我的圖表標題
    ' ↓ 設定圖表標題要加附的文字，所以我的例子圖表標題為B欄+C欄
    .ChartTitle.Text = .ChartTitle.Text & " - " & Range("'raw data'!$C$" & LOAD_SEQ_COUNT & ":$C$" & LOAD_SEQ_COUNT & "")
 
    ' ↓ 匯入定義的範圍，這邊我的圖表有六個類型的資料
    .SeriesCollection(1).Name = "=""Max"""
    .SeriesCollection(1).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(1).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
     LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = "=""0 hr"""
    .SeriesCollection(2).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(2).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
    LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
    .SeriesCollection.NewSeries
    .SeriesCollection(3).Name = "=""168 hrs"""
    .SeriesCollection(3).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(3).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
    LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
    .SeriesCollection.NewSeries
    .SeriesCollection(4).Name = "=""500 hrs"""
    .SeriesCollection(4).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(4).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
    LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
    .SeriesCollection.NewSeries
    .SeriesCollection(5).Name = "=""1000 hrs"""
    .SeriesCollection(5).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(5).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
    LOAD_SEQ_COUNT = LOAD_SEQ_COUNT + 1
    .SeriesCollection.NewSeries
    .SeriesCollection(6).Name = "=""Min"""
    .SeriesCollection(6).XValues = "='raw data'!$F$1:$DA$1"
    .SeriesCollection(6).Values = "='raw data'!$F$" & LOAD_SEQ_COUNT & ":$DA$" & LOAD_SEQ_COUNT & ""
 
    ' ↓ 定義圖表標記格式
    .SeriesCollection(1).MarkerStyle = 2
    .SeriesCollection(1).MarkerSize = 7
    .SeriesCollection(2).MarkerStyle = 1
    .SeriesCollection(2).MarkerSize = 5
    .SeriesCollection(3).MarkerStyle = 2
    .SeriesCollection(3).MarkerSize = 8
    .SeriesCollection(4).MarkerStyle = 3
    .SeriesCollection(4).MarkerSize = 7
    .SeriesCollection(5).MarkerStyle = 2
    .SeriesCollection(5).MarkerSize = 8
    .SeriesCollection(6).MarkerStyle = 2
    .SeriesCollection(6).MarkerSize = 7
    .Axes(xlValue).CrossesAt = .Axes(xlValue).MinimumScale
    .Axes(xlCategory).MaximumScale = 100
    .Axes(xlCategory).MajorUnit = 10
End With
    Next i
Next j
End Sub

發佈留言取消回覆