基于VBA生成折线图：展示动态表格3列最后5条数据及限值

一、定位动态表格的最后5行数据 #

如果你的表格是普通数据区域（非结构化表），可以通过以下代码精准获取最后5行数据（假设样本标识列是A列，表头在第1行，数据从第2行开始）：

Sub GetLast5Rows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") '替换为你的工作表名称
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row '找到A列最后一个非空行
    
    Dim startRow As Long
    '如果总行数不足5，就从第2行（数据起始行）开始；否则取倒数第5行
    startRow = IIf(lastRow < 6, 2, lastRow - 4)
    
    '定义包含样本列+3个测试点的范围
    Dim dataRange As Range
    Set dataRange = ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "D"))
End Sub

如果用的是Excel结构化表（ListObject），代码更简洁稳定（自动适配新增行）：

Sub GetLast5RowsFromTable()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("TestData") '替换为你的结构化表名称
    
    Dim last5Rows As Range
    If tbl.ListRows.Count >= 5 Then
        '取最后5行数据区域
        Set last5Rows = tbl.ListRows(tbl.ListRows.Count - 4).Range.Resize(5)
    Else
        '如果不足5行，取全部数据行
        Set last5Rows = tbl.DataBodyRange
    End If
End Sub

二、生成带测试点和限值的折线图 #

结合上面的范围定位，直接生成包含3个测试点折线+2条限值线的图表：

Sub GenerateTestChart()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    '===== 1. 定位最后5行数据 =====
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim startRow As Long
    startRow = IIf(lastRow < 6, 2, lastRow - 4)
    Dim dataRange As Range
    Set dataRange = ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "D"))
    
    '===== 2. 创建折线图 =====
    Dim chtObj As ChartObject
    '在工作表指定位置创建图表（Left/Top/Width/Height可自行调整）
    Set chtObj = ws.ChartObjects.Add(Left:=150, Top:=50, Width:=700, Height:=400)
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    '设置图表类型为折线图，添加测试点数据
    cht.ChartType = xlLine
    cht.SetSourceData Source:=dataRange
    
    '===== 3. 添加LIMIT1和LIMIT2水平线 =====
    '假设LIMIT1和LIMIT2的值存在G1和G2单元格，可自行替换
    Dim limit1 As Double, limit2 As Double
    limit1 = ws.Range("G1").Value
    limit2 = ws.Range("G2").Value
    
    '添加LIMIT1系列（红色虚线样式区分）
    With cht.SeriesCollection.NewSeries
        .Name = "LIMIT1"
        .XValues = ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "A"))
        '生成与样本数匹配的限值数组
        .Values = Application.WorksheetFunction.Rept(limit1 & ",", lastRow - startRow + 1)
        .Values = Split(Left(.Values, Len(.Values) - 1), ",")
        .ChartType = xlLine
        .Format.Line.DashStyle = msoLineDash
        .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    End With
    
    '添加LIMIT2系列（绿色点划线样式）
    With cht.SeriesCollection.NewSeries
        .Name = "LIMIT2"
        .XValues = ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "A"))
        .Values = Application.WorksheetFunction.Rept(limit2 & ",", lastRow - startRow + 1)
        .Values = Split(Left(.Values, Len(.Values) - 1), ",")
        .ChartType = xlLine
        .Format.Line.DashStyle = msoLineDashDot
        .Format.Line.ForeColor.RGB = RGB(0, 176, 80)
    End With
    
    '===== 4. 图表优化（可选） =====
    cht.HasTitle = True
    cht.ChartTitle.Text = "最近5个样本测试结果"
    cht.Axes(xlCategory).HasTitle = True
    cht.Axes(xlCategory).AxisTitle.Text = "测试样本"
    cht.Axes(xlValue).HasTitle = True
    cht.Axes(xlValue).AxisTitle.Text = "测试值"
    cht.Legend.Position = xlLegendPositionBottom
End Sub

三、关于OFFSET函数无效的说明 #

你之前用OFFSET没生效，大概率是因为：

• 未正确结合COUNTA计算动态范围的起始行，导致范围偏移
• 引用表头单元格时未锁定，新增行后范围自动错位
• 表格存在空行，COUNTA统计行数不准确

相比函数，用VBA直接定位最后一行的方式更可靠，不受空行或引用偏移的影响。

内容的提问来源于stack exchange，提问作者Tomás