VBA-突出异常点

最新推荐文章于 2023-03-19 19:05:23 发布

Coding的巴扎黑

最新推荐文章于 2023-03-19 19:05:23 发布

阅读量686

点赞数

分类专栏： VBA-Excel 文章标签： Excel vba 异常点折线图

本文链接：https://blog.csdn.net/zishendianxia/article/details/77199085

版权

VBA-Excel 专栏收录该内容

39 篇文章 7 订阅

订阅专栏

系统：Windows 7
软件：Excel 2010

今天讲讲如何在图表中特别显示异常点
涉及的图表为折线图
示例数据表示有几个人的体重和正常体重范围，将异常点在折线图上表示出来（数字只是示意）
关于何为异常，可以多样化，读者可以发散思维

方法1：手动实现

效果图
这里写图片描述

核心要点是设置辅助行，辅助行中只显示异常值
=IF(OR(E6>E7,E6<E8),E6,#N/A),这句的公式的含义如下
- 如果E6单元格的数值大于E7单元格或者E6单元格的数值小于E8，则取值E6单元格数值，否则取值#N/A
- 人话：实际体重大于120kg或者小于40kg时，则该体重为异常值，显示在辅助行
图表设置，辅助行的线条设置为无线条，数据标记选项设置成圆形，填充红色
其实是将辅助行挡住实际体重的曲线，因为辅助行只有异常值，所以曲线上看起来只有异常值点显示红色

公式设置
这里写图片描述

数据标记设置
这里写图片描述

方法2：代码式实现

效果图
这里写图片描述

方法1显示异常值主要采用辅助行覆盖原体重曲线的方法，缺点是：
- 对于异常值的点不能单独设置数据标记类型
- 异常值数据标记大小也不能特别化，要不就无法覆盖正常的体重曲线
代码方法无需设置辅助行，逻辑过程如下
- 初始化体重曲线，所有点设置成一样的
- 根据判断条件识别异常点，对异常点进行单独设置
关键方法
- Set point1 = line1.Points(k)获取折线上从左至右的第k个点
- point1.MarkerForegroundColor = RGB(0, 255, 0) 设置标记线颜色
- point1.MarkerBackgroundColor = RGB(255, 0, 0) 设置数据标记填充色
在这里说一个Excel2010可能的bug，通过录制宏无法获取对数据标记进行设置的VBA代码，或者说获取的代码不对，大家可以试一下
整个代码由两个Sub构成，第一个Sub获取异常点位置及线条初始化，第二个完成异常点的设置

Sub 异常点识别()
    Set sht = ThisWorkbook.Worksheets("示例")
    Set ch1 = sht.ChartObjects("体重图")
    Set line1 = ch1.Chart.SeriesCollection("体重")

    '线条初始化
    With line1
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 7
    End With

    line1.MarkerForegroundColor = RGB(0, 0, 0) '标记线颜色
    line1.MarkerBackgroundColor = RGB(0, 0, 0)  '填充色

    For j = 5 To 10 Step 1
        ti_zhong = sht.Cells(6, j)
        zui_zhong = sht.Cells(7, j)
        zui_qing = sht.Cells(8, j)
        If ti_zhong > zui_zhong Or ti_zhong < zui_qing Then
            di_ji = j - 4
            Call 设置异常点(di_ji)
        End If
    Next j

End Sub

Sub 设置异常点(k)
    Set sht = ThisWorkbook.Worksheets("示例")
    Set ch1 = sht.ChartObjects("体重图")
    Set line1 = ch1.Chart.SeriesCollection("体重")
    Set point1 = line1.Points(k)

    point1.MarkerStyle = xlMarkerStyleTriangle
    point1.MarkerSize = 15
    point1.MarkerForegroundColor = RGB(0, 255, 0)  '标记线颜色
    point1.MarkerBackgroundColor = RGB(255, 0, 0)  '填充色

End Sub