事務作業の省力化や資料作成に役立つ、Excelの使い方を紹介

Excelの機能を最大限に生かして、業務の省力化や経営状況を分析できる資料の作成方法を解説します。

「平均値」は必ずしも「平均的な値」ではありません

「平均値」必ずしも「平均的な値」にならない場合があります

 複数のデータの「平均的な値」を求めるには通常対象データの合計をその個数で除する「単純平均」を用いて「平均値」を求めます。

 しかし、対象のデータに「差」がある場合、「単純平均」ではそれらの複数のデータの「平均的な値」になっていない場合があります。

 さらに、データの中に極端に差があるものがある場合は「平均値」では状況を正しく把握することができない場合があります。

 

複数のデータの「平均的な値」を求める方法には複数あります

  1. 単純平均(AVERAGE)   平均点を「グループの数」で除する
  2. 加重平均(SUMPRODUCT)   グループの「人数」も考慮に入れて計算
  3. 中央値    (MEDIAN)      データの「中間値」を求める

 

「個体数」に差がある場合は、加重平均(SUMPRODUCT関数)」を用います

 人数が25人の「グループA」40人の「グループB」では、平均点の重みが異なるので、「平均点」だけではなく「人数」も考慮に入れる必要があります。

 このような場合は、「グループごとに「平均点」と「人数」を掛けたものを合計し、それを人数の合計で除す」「加重平均」を使います。

 

   f:id:k-ohmori9616:20190903120042p:plain

 

1.単純平均(AVERAGE)加重平均(SUMPRODUCT)では、がある

 加重平均(SUMPRODUCT)「平均点」だけではなく、「人数」も考慮に入れて計算します。

 

データに極端な「差」がない場合

 「単純平均」「加重平均」差はそれほど大きくなりません。

    単純平均 (68+60+67)÷ 3 = 65

    加重平均 (68×25+60×40+67×35)÷(25+40+35)= 64.45

 

   f:id:k-ohmori9616:20190903110547p:plain

 

「グループB」の平均点が、極端に低い場合

 各グループの平均点に極端な差がある場合は、「単純平均」「加重平均」差が大きくなります。

    単純平均 (68+30+67)÷3 = 55

    加重平均 (68×25+30×40+67×35)÷(25+40+35)= 52.45

 

   f:id:k-ohmori9616:20190903110707p:plain

 

2.加重平均「SUMPRODUCT関数」を使います

 SUMPRODUCT関数を使えば、グループごとに掛け算をした合計を簡単に求めることができます。

 数式「SUMPRODUCT ( "平均点の合計" , "人数の合計")  / SUM ( "人数の範囲" ) 」

  • SUMPRODUCT関数は、掛け算(PRODUCT)の結果を合計(SUM)する関数です。
  • 「範囲同士」の掛け算ができます。
  • 複数の行を、それぞれ1行ずつ掛け算した値の総合計を得られます

 

   f:id:k-ohmori9616:20190405122712p:plain

 

SUMPRODUCT関数で、「要素の積」を合計します

 「平均点」「人数」のフィールド(項目)を範囲指定します。

 

   f:id:k-ohmori9616:20190903105137p:plain

 

SUMPRODUCT関数で求めた「値」SUMで求めた「値」で除します

 SUM関数を使って「人数」の合計を求めます。

 

   f:id:k-ohmori9616:20190903105423p:plain

 

  加重平均を求める数式は

=" SUMPRODUCT関数で求めた値 " ÷ " グループの総人数 "


 このように、SUMPRODUCT関数を使って「加重平均」とすることで、より正確な平均値を求めることができます。

 

 「配列数式」を用いても結果は同じですが、後で数式を見た時に分かりにくいのでこのような場合にはSUMPRODUCT関数を使います

 

「配列数式」を使って表の総合計を簡単に計算する - Excelの機能を活用して、事務作業の省力化や経営分析をする

 

 

 3.データの中に他と極端に「差」がある「値」がある場合は、中央値(MEDIAN関数)

 下の表の様にグループBの平均点が他より極端に低い場合、「加重平均」では状況を正しく反映できません

 そのような場合は、「平均値」を求めるのではなくMEDIAN関数を使って「中央値」を求めます。

 

各グループに極端な差がある場合は中央値(MEDIAN)を使う

 各グループに極端な差がある場合は、「中央値」は他の方法との「差」が大きくなる。

 

   f:id:k-ohmori9616:20190903111730p:plain

 

MEDIAN関数」は、選択された範囲内の中央値を求める関数

 中央値は「平均」を取る場合と比較して、極端なデータがあった場合でも比較的正しい値を求めることが出来ます

 数式「 =MEDIAN ( "平均点の範囲" )

 

   f:id:k-ohmori9616:20190405125647p:plain
 

 このように複数のデータの「平均」を求める際には、単純平均(AVERAGE)だけでなく、加重平均(SUMPRODUCT)中央値(MEDIAN)を状況に応じて使い分けてください。

 

 

 

k-ohmori9616.hatenablog.com