求解中位數、25、75分位數

在交易策略裡，總是希望可以掌握波動度，評估波動度的方法除了標準差之外，用分位數的距離來評估也是不錯的方法，這裡提供Excel VBA程式碼參考

'Output = 1 25分位、Output = 2 中位數、Output = 3 75分位
'使用泡沫排序方式，對原始數據做大小排序
'對全體數據和剩餘數據集，找尋三次中位數，即可獲得四分位數

Public Function sQuartile(ByVal InputData As Range, ByVal Output As Variant) As Variant

Dim ArrDataX() As Variant
Dim DataCount As Variant
Dim DataTemp As Variant
Dim ii As Variant
Dim jj As Variant

ArrDataX = InputData
DataCount = InputData.Count
For jj = 1 To DataCount - 1
For ii = 1 To DataCount - jj

If ArrDataX(ii, 1) > ArrDataX(ii + 1, 1) Then
DataTemp = ArrDataX(ii + 1, 1)
ArrDataX(ii + 1, 1) = ArrDataX(ii, 1)
ArrDataX(ii, 1) = DataTemp
End If 'If ArrDataX(ii, 1) > ArrDataX(ii + 1,1) Then
Next 'For ii = 1 To DataCount-jj
Next 'For jj = 1 To DataCount-1

'Debug.Print "ArrDataX(1)", ArrDataX(1, 1), "ArrDataX(2)", ArrDataX(2, 1), "ArrDataX(3)", ArrDataX(3, 1)

'Debug.Print "ArrDataX(4)", ArrDataX(4, 1), "ArrDataX(5)", ArrDataX(5, 1), "ArrDataX(6)", ArrDataX(6, 1)

If Output = 1 Then
If DataCount \ 2 Mod 2 = 0 Then sQuartile = (ArrDataX(DataCount \ 2 \ 2, 1) + ArrDataX(DataCount \ 2 \ 2 + 1, 1)) * 0.5
If DataCount \ 2 Mod 2 = 1 Then sQuartile = ArrDataX(DataCount \ 2 \ 2 + 1, 1)
End If 'If Output = 1 Then

If Output = 2 Then
If DataCount Mod 2 = 0 Then sQuartile = (ArrDataX(DataCount \ 2, 1) + ArrDataX(DataCount \ 2 + 1, 1)) * 0.5
If DataCount Mod 2 = 1 Then sQuartile = ArrDataX(DataCount \ 2 + 1, 1)
End If 'If Output = 2 Then

If Output = 3 Then
If DataCount \ 2 Mod 2 = 0 Then sQuartile = (ArrDataX(DataCount - DataCount \ 2 \ 2, 1) + ArrDataX(DataCount \ 2 \ 2 - 1, 1)) * 0.5
If DataCount \ 2 Mod 2 = 1 Then sQuartile = ArrDataX(DataCount - DataCount \ 2 \ 2, 1)
End If 'If Output = 3 Then
End Function