系统: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
代码截图
以上为本次的学习内容,下回见
如发现有错误,欢迎留言指出。文章定位:“字典查询类”
更多原创请关注微信公众号
扫描二维码,关注公众号
让重复工作一键化,让时间更有意义