【Excel VBA】長條圖加上水平線

10 min readAug 29, 2022

為了讓圖表呈現地更直觀，很常在圖表中加入水平線做為參考。
這條線可以是一個達標門檻、最低下限、甚至是平均值，同時也可以從中比較多筆數據間的差異。
本文會介紹長條圖加上水平線常用的幾種方式，並且附上VBA程式碼。

方法：
從網路資料中，大約能整理出3種主要方法。
方法1：組合圖中，水平線數據使用折線圖(Line chart)。
方法2：組合圖中，水平線數據使用散佈圖(Scatter chart)。
方法3：組合圖中，水平線數據使用散佈圖但會隱藏，而是使用誤差線(Error bar)功能來加上水平線。

方法1：組合圖中，水平線數據使用折線圖(Line chart)
優點：
使用上最直覺，步驟簡單。
缺點：
1.需給定一組水平線所需的數據。(儲存格參照數量 = 樣本數)
2.水平線無法畫滿整個圖表。(兩側會缺一點)

方法2：組合圖中，水平線數據使用散佈圖(Scatter chart)
優點：
改善方法1水平線兩側會缺一點的問題。
缺點：
需給定水平線 X, Y二維數值。(儲存格參照數量 = 4)
(X值為水平線位置的起終點，要涵蓋整個圖表時數值需補上0.5；Y值為水平線的高度，兩個數值分別為最左與最右)

Draw horizontal line with scatter chart.

方法3：組合圖中，水平線數據使用散佈圖但會隱藏，而是使用誤差線(Error bar)功能來加上水平線。
優點：
儲存格參照數量最少。(儲存格參照數量 = 1)
缺點：
1.步驟較複雜，需額外做誤差線設定。
2.誤差線本來不是這樣用的啊。

操作步驟如下：
1.選取水平線的資料來源(數列Y值)。
2.點擊圖表→新增誤差線→選擇「其他誤差線選項」→選擇水平線的數列名稱。(可參考下圖)
3.圖表會出現水平與垂直的誤差線，將垂直的誤差線Delete刪除。
4.點擊水平的誤差線，點擊右鍵→「誤差線格式」
5.設定終點模式為「無端點」；設定誤差量為自訂，點擊指定值按鈕。
6.設定正錯誤值為「樣本數-0.5」、負錯誤值為「0.5」。(注意要保留括弧，可參考上圖誤差線設定)
7.設定誤差線格式與水平線相同。

程式碼：
1.透過執行VBA把範例數值加進去吧，不需要一個一個打。
以下是基礎的一維矩陣資料寫入方式。

Sub example_data()
    row1 = Array("", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    row2 = Array("A", 2.12, 2.63, 0.7, 1.55, 2.84, 2.83, 1.77, 2.11, 0.6, 1.45)
    row3 = Array("B", 1.6, 1.99, 1.07, 4.04, 2.24, 4.41, 4.25, 3.91, 1.56, 2.29)
    row4 = Array("Line", 3, 3, 3, 3, 3, 3, 3, 3, 3, 3)
    row5 = Array("XY", 0.5, 10.5, "", "", "", "", "", "", "", "")
    Range("A1:K1").Value = row1
    Range("A2:K2").Value = row2
    Range("A3:K3").Value = row3
    Range("A4:K4").Value = row4
    Range("A5:K5").Value = row5
End Sub

2.上述介紹的3種方法，皆可用VBA產生。程式碼大同小異。
(執行巨集前請先建立好數據)

Sub method1() '方法1
    Range("A8").Select  'Select blank cells to avoid adding data automatically when creating a chart
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    With ActiveChart
      .Parent.Top = Range("L1").Top  'Set chart position
      .Parent.Left = Range("L1").Left
      .SeriesCollection.NewSeries 'Add series
      .FullSeriesCollection(1).Name = Range("A2").Value
      .FullSeriesCollection(1).Values = Range("B2:K2").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(2).Name = Range("A3").Value
      .FullSeriesCollection(2).Values = Range("B3:K3").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(3).Name = Range("A4").Value
      .FullSeriesCollection(3).Values = Range("B4:K4").Value
      .FullSeriesCollection(3).ChartType = xlLine 'Change chart type to line chart
      .SetElement (msoElementChartTitleNone) 'Delete chart title
      .SetElement (msoElementLegendBottom) 'Set legend position at the bottom
    End With
    
    With ActiveChart.FullSeriesCollection(3).Format.Line 'Horizontal line format
      .Weight = 2.25
      .ForeColor.RGB = RGB(255, 0, 0)
    End With
End Sub
Sub method2() '方法2
    Range("A8").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    With ActiveChart
      .Parent.Top = Range("L15").Top
      .Parent.Left = Range("L15").Left
      .SeriesCollection.NewSeries
      .FullSeriesCollection(1).Name = Range("A2").Value
      .FullSeriesCollection(1).Values = Range("B2:K2").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(2).Name = Range("A3").Value
      .FullSeriesCollection(2).Values = Range("B3:K3").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(3).ChartType = xlXYScatterLinesNoMarkers 'Change chart type to scatter chart
      .FullSeriesCollection(3).Name = Range("A4").Value
      .FullSeriesCollection(3).Values = Range("B4:C4").Value  'Y value
      .FullSeriesCollection(3).XValues = Range("B5:C5").Value  'X value
      .SetElement (msoElementChartTitleNone)
      .SetElement (msoElementLegendBottom)
    End With
    
    With ActiveChart.FullSeriesCollection(3).Format.Line
      .Weight = 2.25
      .ForeColor.RGB = RGB(255, 0, 0)
    End With
End Sub
Sub method3() '方法3
    Range("A8").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    With ActiveChart
      .Parent.Top = Range("L29").Top
      .Parent.Left = Range("L29").Left
      .SeriesCollection.NewSeries
      .FullSeriesCollection(1).Name = Range("A2").Value
      .FullSeriesCollection(1).Values = Range("B2:K2").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(2).Name = Range("A3").Value
      .FullSeriesCollection(2).Values = Range("B3:K3").Value
      .SeriesCollection.NewSeries
      .FullSeriesCollection(3).Name = Range("A4").Value
      .FullSeriesCollection(3).Values = Range("B4").Value
      .FullSeriesCollection(3).ChartType = xlXYScatterLinesNoMarkers
      .FullSeriesCollection(3).ErrorBar Direction:=xlX, Include:=xlBoth, Type:=xlCustom, Amount:=9.5, MinusValues:=0.5 'Add error bar (X-axis)
      .FullSeriesCollection(3).ErrorBars.EndStyle = xlNoCap  'Error bar without cap
      .SetElement (msoElementChartTitleNone)
      .SetElement (msoElementLegendBottom)
    End With
    
    With ActiveChart.FullSeriesCollection(3).ErrorBars.Format.Line  'Error bar format
      .Weight = 2.25
      .ForeColor.RGB = RGB(255, 0, 0)
    End With
    
    With ActiveChart.FullSeriesCollection(3).Format.Line
      .Weight = 2.25
      .ForeColor.RGB = RGB(255, 0, 0)
    End With
End Sub

執行巨集，完成，繼續工作。
晚點來吃一碗日清的咖哩泡麵好了。

【Excel VBA】長條圖加上水平線

Written by Kangkai