• 系統：Windows 7
• 軟件：Excel 2010

今天講講如何在圖表中特别顯示異常點

涉及的圖表為折線圖，示例數據表示有幾個人的體重和正常體重範圍，将異常點在折線圖上表示出來（數字隻是示意） 關于何為異常，可以多樣化，讀者可以發散思維

1. 核心要點是設置輔助行，輔助行中隻顯示異常值
2. =IF(OR(E6>E7,E6<E8),E6,#N/A),這句的公式的含義如下：如果E6單元格的數值大于E7單元格或者E6單元格的數值小于E8，則取值E6單元格數值，否則取值#N/A
3. 人話：實際體重大于120kg或者小于40kg時，則該體重為異常值，顯示在輔助行
4. 圖表設置，輔助行的線條設置為無線條，數據标記選項設置成圓形，填充紅色
5. 其實是将輔助行擋住實際體重的曲線，因為輔助行隻有異常值，所以曲線上看起來隻有異常值點顯示紅色

圖1 動态效果圖

圖2 公式設置

圖3 數據标記設置

1. 方法1顯示異常值主要采用輔助行覆蓋原體重曲線的方法，缺點是：

• 對于異常值的點不能單獨設置數據标記類型
• 異常值數據标記大小也不能特别化，要不就無法覆蓋正常的體重曲線

1. 代碼方法無需設置輔助行，邏輯過程如下

• 初始化體重曲線，所有點設置成一樣的
• 根據判斷條件識别異常點，對異常點進行單獨設置

1. 關鍵方法

• Set point1 = line1.Points(k)獲取折線上從左至右的第k個點
• point1.MarkerForegroundColor = RGB(0, 255, 0) 設置标記線顔色
• point1.MarkerBackgroundColor = RGB(255, 0, 0) 設置數據标記填充色

1. 整個代碼由兩個Sub構成，第一個Sub獲取異常點位置及線條初始化，第二個完成異常點的設置

圖4 效果圖

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

圖5 代碼截圖

以上，為本次的介紹内容，下回見。

本文首發于Excel高效辦公之VBA。排版和細節略作修改，發于頭條