Excel图表处理：忽略/分离数据集两个极大值的方案（支持VBA）

针对10万+行、存在两个远高于其他数据的极大值的数据集，以下是三种无需辅助列的实现方案，均支持VBA自动化处理：

方案一：忽略极大值绘制图表（对应Chart 2） #

通过VBA直接筛选出排除极大值的数据范围，生成图表：

Sub CreateChartWithoutOutliers()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject
    Dim rng As Range
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 定义筛选条件：排除Amount列大于1000的极大值（可根据实际调整阈值）
    ws.Range("A1:B" & lastRow).AutoFilter Field:=1, Criteria1:="<1000"
    
    ' 获取筛选后的可见数据范围
    Set rng = ws.Range("A1:B" & lastRow).SpecialCells(xlCellTypeVisible)
    
    ' 创建图表
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    With chartObj.Chart
        .SetSourceData Source:=rng
        .ChartType = xlLine ' 可根据需求更换图表类型，如xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "不含极大值的趋势图"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "时间"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "数值"
    End With
    
    ' 取消筛选
    ws.AutoFilterMode = False
End Sub

执行后会生成仅展示正常数据的图表，避免极大值导致的失真。

方案二：将极大值单独绘制（对应Chart 3） #

用VBA生成两个独立图表，分别展示正常数据和极大值：

Sub CreateSeparateCharts()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartNormal As ChartObject
    Dim chartOutliers As ChartObject
    Dim rngNormal As Range
    Dim rngOutliers As Range
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 筛选正常数据（阈值根据实际调整）
    ws.Range("A1:B" & lastRow).AutoFilter Field:=1, Criteria1:="<1000"
    Set rngNormal = ws.Range("A1:B" & lastRow).SpecialCells(xlCellTypeVisible)
    
    ' 筛选极大值
    ws.Range("A1:B" & lastRow).AutoFilter Field:=1, Criteria1:=">=1000"
    Set rngOutliers = ws.Range("A1:B" & lastRow).SpecialCells(xlCellTypeVisible)
    
    ' 创建正常数据图表
    Set chartNormal = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    With chartNormal.Chart
        .SetSourceData Source:=rngNormal
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = "正常数据趋势图"
        .Axes(xlCategory).AxisTitle.Text = "时间"
        .Axes(xlValue).AxisTitle.Text = "数值"
    End With
    
    ' 创建极大值图表
    Set chartOutliers = ws.ChartObjects.Add(Left:=550, Width:=300, Top:=100, Height:=300)
    With chartOutliers.Chart
        .SetSourceData Source:=rngOutliers
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "极大值数据展示"
        .Axes(xlCategory).AxisTitle.Text = "时间"
        .Axes(xlValue).AxisTitle.Text = "数值"
    End With
    
    ' 取消筛选
    ws.AutoFilterMode = False
End Sub

两个图表可分别展示不同维度的数据，便于对比查看。

方案三：同一图表用次坐标轴展示极大值 #

将极大值数据系列设置到次坐标轴，在同一图表中兼顾正常数据和极大值：

Sub CreateChartWithSecondaryAxis()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject
    Dim rngAll As Range
    Dim seriesNormal As Series
    Dim seriesOutliers As Series
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rngAll = ws.Range("A1:B" & lastRow)
    
    ' 创建基础图表
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=100, Height:=300)
    With chartObj.Chart
        .SetSourceData Source:=rngAll
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = "含极大值的双坐标轴图表"
        .Axes(xlCategory).AxisTitle.Text = "时间"
        .Axes(xlValue).AxisTitle.Text = "正常数值"
    End With
    
    ' 添加极大值系列
    Set seriesOutliers = chartObj.Chart.SeriesCollection.NewSeries
    seriesOutliers.Name = "极大值"
    ' 遍历数据，提取极大值点
    For i = 2 To lastRow
        If ws.Cells(i, "A").Value >= 1000 Then ' 阈值根据实际调整
            seriesOutliers.XValues = seriesOutliers.XValues & ",""" & ws.Cells(i, "B").Value & """"
            seriesOutliers.Values = seriesOutliers.Values & "," & ws.Cells(i, "A").Value
        End If
    Next i
    
    ' 将极大值系列设置为柱状图并移至次坐标轴
    With seriesOutliers
        .ChartType = xlColumnClustered
        .AxisGroup = xlSecondary
    End With
    
    ' 设置次坐标轴标题
    chartObj.Chart.Axes(xlValue, xlSecondary).HasTitle = True
    chartObj.Chart.Axes(xlValue, xlSecondary).AxisTitle.Text = "极大值"
End Sub

此方案在同一图表中同时展示两类数据，避免空间浪费，也能清晰区分量级差异。

内容的提问来源于stack exchange，提问作者mjac