求解中位數、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