It was fun. I don’t know if there is a good way to extend the functionality of existing box and mustache charts in Excel, but fortunately I was able to come up with a quick and dirty way to fake it using a scatter chart. Basically, we're just going to iterate over the intervals and choose enough points to display on a scatter chart that looks indistinguishable from boxes and mustaches. Since it makes each interval a different series, you can also format things like coloring to make them easier to distinguish.
( , , , ):
min/max . , :
, , , :
' Build and display an "Interval Chart"
Public Sub MakeIntervalChart()
Dim inRow As Long, outRow As Long, lastRow As Long, startRow As Long
Dim interX As Double, interY As Double, intervalAmt As Double
intervalAmt = 0.01 ' CHANGE ME
' Source / Destination Worksheet Parameters (CHANGE ME)
Dim wsSource As Worksheet, wsDest As Worksheet
Set wsSource = Worksheets("Data")
Set wsDest = Worksheets.Add()
wsDest.Name = "IntervalChart"
' Create output chart
Dim boxChart As Chart
Set boxChart = wsDest.Shapes.AddChart2(240, xlXYScatter).Chart
boxChart.HasLegend = True
outRow = 1
lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
For inRow = 2 To lastRow
' Retrieve current interval (CHANGE ME)
Dim seriesName As String
Dim minX As Double, maxX As Double, minY As Double, maxY As Double
seriesName = wsSource.Cells(inRow, 1)
minX = wsSource.Cells(inRow, 2)
maxX = wsSource.Cells(inRow, 3)
minY = wsSource.Cells(inRow, 4)
maxY = wsSource.Cells(inRow, 5)
startRow = outRow
'intervalAmt = (maxX - minX) / 50.0
' Top and Bottom of box
For interX = minX To maxX Step intervalAmt
wsDest.Cells(outRow, 1) = seriesName
wsDest.Cells(outRow, 2) = interX
wsDest.Cells(outRow, 3) = minY
outRow = outRow + 1
wsDest.Cells(outRow, 1) = seriesName
wsDest.Cells(outRow, 2) = interX
wsDest.Cells(outRow, 3) = maxY
outRow = outRow + 1
Next
'intervalAmt = (maxY - minY) / 50.0
' Left and Right of box
For interY = minY To maxY Step intervalAmt
wsDest.Cells(outRow, 1) = seriesName
wsDest.Cells(outRow, 2) = minX
wsDest.Cells(outRow, 3) = interY
outRow = outRow + 1
wsDest.Cells(outRow, 1) = seriesName
wsDest.Cells(outRow, 2) = maxX
wsDest.Cells(outRow, 3) = interY
outRow = outRow + 1
Next
' Add new series (box)
With boxChart.SeriesCollection.newSeries()
.Name = seriesName
.XValues = wsDest.Range("B" & startRow & ":B" & outRow - 1)
.Values = wsDest.Range("C" & startRow & ":C" & outRow - 1)
End With
Next
End Sub
, / ( , ). 0.01 . , . - , 0,01 , intervalAmt = (maxX - minX) / 50.0 50.0 ( ). - , , , .