【VBAリファレンス|実務向け】実務で差がつく!AVERAGE関数の限界を超えた「統計的平均」の算出テクニック

スポンサーリンク

なぜ「単純な平均」ではダメなのか?

業務で売上推移や作業時間を分析する際、迷わずAVERAGE関数を使っていませんか?しかし、実務のデータには「セールによる爆発的な売上」や「システム障害による極端な遅延」といった、いわゆる「特異値(外れ値)」が混じることがあります。これらをそのまま平均に含めると、実態を反映しない数値となり、誤った経営判断や目標設定に繋がります。本記事では、Excel 2016以降で活用できる、より実務的な平均算出手法を解説します。

基礎知識:平均を制御する関数の仕組み

AVERAGE関数は「指定範囲の合計÷データ個数」を計算する単純な関数です。一方、実務で役立つ以下の関数は「データの性質」を考慮します。

TRIMMEAN関数:データの上下から一定の割合(%)を除外して平均を算出します。極端な値の影響を排除したい場合に有効です。
PERCENTILE.INC関数:指定した百分位(パーセンタイル)の値を返します。上位・下位の境界値を知るのに最適です。
DAVERAGE関数:データベース関数の一つで、条件に一致するレコードだけを対象に平均を算出します。

実装・解決策:VBAで外れ値を除外した平均を算出する

VBAを使用すれば、特定の条件に合致するデータだけを抽出し、動的に平均を算出することが可能です。以下は、指定した範囲から外れ値(平均値から大きく乖離した値)を自動判定して除外するコード例です。

サンプルプログラム

以下のコードは、選択した範囲内の数値から、平均値の±1.5倍の範囲外にある値を除外して、真の平均を計算する実務的なマクロです。

Sub CalculateTrimmedAverage()
Dim rng As Range
Dim cell As Range
Dim total As Double
Dim count As Long
Dim avg As Double
Dim stdev As Double

‘ 現在選択している範囲を対象にする
Set rng = Selection

‘ 単純平均と標準偏差を算出
avg = Application.WorksheetFunction.Average(rng)
stdev = Application.WorksheetFunction.StDev_P(rng)

‘ 平均から大きく乖離したデータ(外れ値)を除外して計算
For Each cell In rng
‘ 平均値±(標準偏差×1.5)の範囲内の数値のみを合計
If Abs(cell.Value – avg) <= (stdev 1.5) Then total = total + cell.Value count = count + 1 End If Next cell ' 結果を出力 If count > 0 Then
MsgBox “外れ値を除外した平均値は: ” & Round(total / count, 2)
Else
MsgBox “有効なデータがありません。”
End If
End Sub

応用・注意点:現場で陥りやすい罠

1. データの分布を確認する:TRIMMEAN関数などを使う際は、必ず元のデータが正規分布に近いかを確認してください。極端に偏ったデータの場合、平均値そのものが指標として適切でない可能性があります。
2. ゼロ値の扱い:AVERAGE系関数は「0」を数値としてカウントしますが、COUNTA関数などは異なります。売上実績などで「0」を「未発生」と捉えるか「数値の0」と捉えるかで、集計範囲が変わる点に注意しましょう。
3. メンテナンス性:VBAで複雑な計算を組むのも良いですが、まずはTRIMMEAN関数のような標準機能で対応できないか検討してください。標準関数で済むものは、数式の方が後の修正が容易で、引き継ぎもしやすいためです。

まずは、お手元のデータで「平均が実態と乖離していないか」を再確認することから始めてみてください。

タイトルとURLをコピーしました