我正在處理一個包含大約 17k 個值的大型資料集，我需要為每 29 個值制作一個圖表。例如，如果資料集有 58 個值，我需要為每 29 個值制作一個圖表，從而制作兩個圖表。
我撰寫了以下代碼來制作圖表：
'''
Sub Charts()
'
' Charts Macro
'
'
Range("D3:D32").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$D$3:$D$32")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$3:$C$32"
Range("D32:D61").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$D$32:$D$61")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$32:$C$61"
End Sub
'''
然而，正如您想象的那樣，對于 17k 值來說，這是一個相當乏味的步驟。
有人可以建議一種更簡單的方法來制作資料圖表嗎？我會非常感激同樣的！
uj5u.com熱心網友回復：
嵌套 for 回圈是我過去處理類似問題的方式。就像是
For i=1 to 17k/29
create chart
for k=1 to 29
range = i*k
data = range
假設你的資料是連續的并且在相同的范圍內，每次你只需要弄清楚單個集合的實際步驟然后放手。
uj5u.com熱心網友回復：
使用步長為 29的For/Next 回圈。
Option Explicit
Sub ChartsMacro()
Const SIZE = 29
Dim ws As Worksheet, cht As Chart, c, sTitle As String
Dim lastrow As Long, r As Long, n As Long, L As Long, T As Long
Dim t0 As Single: t0 = Timer
Application.ScreenUpdating = False
Set ws = ActiveSheet
With ws
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For r = 3 To lastrow Step SIZE
n = n 1
Application.StatusBar = "Chart " & n
' position and title chart
sTitle = "Chart " & n & " (" & r & " to " & r SIZE & ")"
L = .Range("F" & r 1).Left
T = .Range("F" & r 1).Top
.Range("C" & r).Resize(, 10).Borders(xlEdgeTop).Color = vbBlack
' create chart
Set cht = .Shapes.AddChart2(227, xlLine, Left:=L, Top:=T).Chart
cht.SetSourceData Source:=.Range("D" & r).Resize(SIZE 1)
cht.ChartTitle.Text = sTitle
cht.FullSeriesCollection(1).Name = "=""Marginal Costs"""
cht.FullSeriesCollection(1).XValues = .Range("C" & r).Resize(SIZE 1)
Next
End With
Application.ScreenUpdating = True
Application.StatusBar = "Done"
MsgBox n & " Charts created in " & Format(Timer - t0, "0.0") & " secs"
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/342810.html
下一篇：在陣列VBA中重復偶數