データ解析を行う際、中央絶対偏差を使うことが多いのですが、エクセルで簡便に算出する方法がなかったので、ユーザー定義関数で作成をしてみました。
従来の算出方法
通常は以下のステップで求めるかと思います。
- データの中央値を算出
- データの中央値からの乖離を算出
- 上記乖離の絶対値を算出
- 上記絶対値の中央値を算出
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