「平均値」は必ずしも「平均的な値」にならない場合があります
複数のデータの「平均的な値」を求めるには通常対象データの合計をその個数で除する「単純平均」を用いて「平均値」を求めます。
しかし、対象のデータに「差」がある場合、「単純平均」ではそれらの複数のデータの「平均的な値」になっていない場合があります。
さらに、データの中に極端に差があるものがある場合は「平均値」では状況を正しく把握することができない場合があります。
複数のデータの「平均的な値」を求める方法には複数あります
- 単純平均(AVERAGE) 平均点を「グループの数」で除する
- 加重平均(SUMPRODUCT) グループの「人数」も考慮に入れて計算
- 中央値 (MEDIAN) データの「中間値」を求める
「個体数」に差がある場合は、「加重平均(SUMPRODUCT関数)」を用います
人数が25人の「グループA」と40人の「グループB」では、平均点の重みが異なるので、「平均点」だけではなく「人数」も考慮に入れる必要があります。
このような場合は、「グループごとに「平均点」と「人数」を掛けたものを合計し、それを人数の合計で除す」、「加重平均」を使います。
1.単純平均(AVERAGE)と加重平均(SUMPRODUCT)では、差がある
加重平均(SUMPRODUCT)は「平均点」だけではなく、「人数」も考慮に入れて計算します。
データに極端な「差」がない場合
「単純平均」と「加重平均」の差はそれほど大きくなりません。
単純平均 (68+60+67)÷ 3 = 65
加重平均 (68×25+60×40+67×35)÷(25+40+35)= 64.45
「グループB」の平均点が、極端に低い場合
各グループの平均点に極端な差がある場合は、「単純平均」と「加重平均」の差が大きくなります。
単純平均 (68+30+67)÷3 = 55
加重平均 (68×25+30×40+67×35)÷(25+40+35)= 52.45
2.加重平均は「SUMPRODUCT関数」を使います
SUMPRODUCT関数を使えば、グループごとに掛け算をした合計を簡単に求めることができます。
数式「=SUMPRODUCT ( "平均点の合計" , "人数の合計") / SUM ( "人数の範囲" ) 」
- SUMPRODUCT関数は、掛け算(PRODUCT)の結果を合計(SUM)する関数です。
- 「範囲同士」の掛け算ができます。
- 複数の行を、それぞれ1行ずつ掛け算した値の総合計を得られます
SUMPRODUCT関数で、「要素の積」を合計します
「平均点」と「人数」のフィールド(項目)を範囲指定します。
SUMPRODUCT関数で求めた「値」をSUMで求めた「値」で除します
SUM関数を使って「人数」の合計を求めます。
加重平均を求める数式は
「 =" SUMPRODUCT関数で求めた値 " ÷ " グループの総人数 " 」
このように、SUMPRODUCT関数を使って「加重平均」とすることで、より正確な平均値を求めることができます。
※ 「配列数式」を用いても結果は同じですが、後で数式を見た時に分かりにくいのでこのような場合にはSUMPRODUCT関数を使います
「配列数式」を使って表の総合計を簡単に計算する - Excelの機能を活用して、事務作業の省力化や経営分析をする
3.データの中に他と極端に「差」がある「値」がある場合は、中央値(MEDIAN関数)
下の表の様にグループBの平均点が他より極端に低い場合、「加重平均」では状況を正しく反映できません。
そのような場合は、「平均値」を求めるのではなくMEDIAN関数を使って「中央値」を求めます。
各グループに極端な差がある場合は中央値(MEDIAN)を使う
各グループに極端な差がある場合は、「中央値」は他の方法との「差」が大きくなる。
「MEDIAN関数」は、選択された範囲内の中央値を求める関数
中央値は「平均」を取る場合と比較して、極端なデータがあった場合でも比較的正しい値を求めることが出来ます。
数式「 =MEDIAN ( "平均点の範囲" ) 」
このように複数のデータの「平均」を求める際には、単純平均(AVERAGE)だけでなく、加重平均(SUMPRODUCT)や中央値(MEDIAN)を状況に応じて使い分けてください。
- 「平均値」は必ずしも「平均的な値」にならない場合があります