中央絶対偏差を算出するエクセルユーザー定義関数

データ解析を行う際、中央絶対偏差を使うことが多いのですが、エクセルで簡便に算出する方法がなかったので、ユーザー定義関数で作成をしてみました。

従来の算出方法

通常は以下のステップで求めるかと思います。

1. データの中央値を算出
2. データの中央値からの乖離を算出
3. 上記乖離の絶対値を算出
4. 上記絶対値の中央値を算出

A列に生データがあるとして、B列に1〜3の計算、別のセルに4を最終的に算出するのが一般的でしょうか。

配列数式を使用する手法

配列数式を用いることで、1セルでの算出も一応可能です。

ただし、空欄セルがあると、0と認識されるためにズレが生じます。また、文字列などがあるとエラーとなってしまいます。

これを回避するためにIF関数などで数値のみを計算に使用するようにする方法もありますが、式の作成がかなり手間です。(参考 stackoverflow.com Median Absolute Deviation calculation with a condition)

ユーザー定義関数での算出

従来方法が手間なのでユーザー定義関数を作成しました。

=MEDDEV(”生データの範囲”)　と入力するだけで算出可能です。平均絶対偏差の関数がAVGDEVなので、MEDDEV関数という名前にしています。

もちろん、空白セルや文字列がある場合も想定しており、値のズレやエラーが発生しないようにしました。

MEDIAN関数と同様に、データ数が偶数個の場合は中央に近い2データの平均値を返します。

VBAコード

Function MEDDEV(allData As Range) As Double
    Dim median As Double
    Dim data_list() As Double
    Dim i As Integer: i = 0
    Dim d As Range

    ' データの中央値を取得
    median = Application.Evaluate("MEDIAN(" & allData.Address & ")")

    '中央絶対偏差を算出するための数値配列を作成
    ' 空欄セル or 数値以外のセルは除外する
    ReDim data_list(allData.Count - 1)
    For Each d In allData
        If d.Value = "" Or IsNumeric(d.Value) = False Then
            ReDim Preserve data_list(UBound(data_list) - 1)
        Else
            data_list(i) = Abs(d.Value - median)
            i = i + 1
        End If
    Next d
    Call QuickSort(data_list, LBound(data_list), UBound(data_list))

    '中央絶対偏差を算出。データ数が偶数この場合はセンター付近の2値の平均を算出する。
    If UBound(data_list) Mod 2 = 0 Then
        MEDDEV = data_list(UBound(data_list) / 2)
    Else
        MEDDEV = (data_list(UBound(data_list) / 2 - 0.5) + data_list(UBound(data_list) / 2 + 0.5)) / 2
    End If
End Function

'クイックソート。Tipsfound様より
'https://www.tipsfound.com/vba/02020
Function QuickSort(ByRef data As Variant, ByVal low As Long, ByVal high As Long)
    Dim l As Long
    Dim r As Long
    l = low
    r = high

    Dim pivot As Variant
    pivot = data((low + high) / 2)

    Dim temp As Variant

    Do While (l <= r)
        Do While (data(l) < pivot And l < high)
            l = l + 1
        Loop
        Do While (pivot < data(r) And r > low)
            r = r - 1
        Loop

        If (l <= r) Then
            temp = data(l)
            data(l) = data(r)
            data(r) = temp
            l = l + 1
            r = r - 1
        End If
    Loop

    If (low < r) Then
        Call QuickSort(data, low, r)
    End If
    If (l < high) Then
        Call QuickSort(data, l, high)
    End If
End Function