Microsoft Accessでは、Excelの「Trimmean」関数のように外れ値を排除した平均を求める標準の関数はありません。しかし、SQLクエリを使って似たような処理を行うことができます。この記事では、Access 2013で外れ値を排除した平均を計算する方法を解説します。
Accessで「Trimmean」関数に相当する計算を行う方法
Excelの「Trimmean」関数は、データセットから一定割合の最大値と最小値を除外し、その残りの値を使って平均を計算します。Accessでこれを実現するためには、まずデータの上位と下位の数値を除外する必要があります。
Accessでは、これをSQLクエリを利用して手動で計算します。以下では、その方法を順を追って説明します。
必要なデータセットを取得する
まず最初に、計算したいデータを取得するためのクエリを作成します。例えば、あるテーブルに「SalesAmount」というフィールドがあると仮定します。このフィールドのデータから外れ値を除外して平均を計算することが目的です。
基本的なSELECTクエリで全てのデータを取得します。
SELECT SalesAmount FROM Sales;ここから、外れ値を除外するために、上位と下位の一定のパーセンテージを除いたデータセットを取得します。
外れ値を除外するためのクエリの作成
次に、最も高い値と最も低い値を除外するためのロジックを作成します。ここでは、データセットの上位と下位の10％を除外する例を示します。
まず、データを昇順で並べ替え、上位10％と下位10％を除外します。以下のようにクエリを構築します。
SELECT SalesAmount FROM (SELECT SalesAmount, Rank() OVER (ORDER BY SalesAmount ASC) AS RankAsc, Rank() OVER (ORDER BY SalesAmount DESC) AS RankDesc FROM Sales) AS RankedSales WHERE RankAsc > (SELECT COUNT(*) * 0.1 FROM Sales) AND RankDesc > (SELECT COUNT(*) * 0.1 FROM Sales);このクエリでは、全データを昇順および降順でランク付けし、上位10％と下位10％を除外したデータセットを抽出しています。
残りのデータを使って平均を計算する
最後に、除外したデータを使って平均を計算します。クエリの結果から「SalesAmount」の平均を求めるためには、以下のように「AVG()」関数を使用します。
SELECT AVG(SalesAmount) AS TrimmedMean FROM (SELECT SalesAmount FROM (SELECT SalesAmount, Rank() OVER (ORDER BY SalesAmount ASC) AS RankAsc, Rank() OVER (ORDER BY SalesAmount DESC) AS RankDesc FROM Sales) AS RankedSales WHERE RankAsc > (SELECT COUNT(*) * 0.1 FROM Sales) AND RankDesc > (SELECT COUNT(*) * 0.1 FROM Sales));これにより、外れ値を除外した「SalesAmount」の平均値が計算されます。
その他の工夫：パーセンテージを調整する
上記の例では10％を除外する方法を紹介しましたが、外れ値を除外する割合は任意で調整できます。例えば、上位5％と下位5％を除外する場合は、クエリ内の「0.1」を「0.05」に変更するだけで簡単に調整可能です。
このように、除外するデータの割合を変更することで、より精度の高い平均を計算できます。
まとめ
Microsoft Accessには、Excelの「Trimmean」関数のように直接外れ値を排除した平均を求める関数はありませんが、SQLクエリを活用することで同様の処理を行うことができます。上位および下位の一定割合を除外することで、外れ値を排除した平均を計算できます。状況に応じて、除外するデータの割合を調整することも可能です。
コメント