平均の求め方(複数条件、空白・ゼロの扱い)/AVERAGE系関数

average-1 基礎コース

平均の求め方(AVERAGE関数)

エクセルにてデータの平均値を求めるにはAVERAGE関数を使用します。

=AVERAGE(数値1, [数値2]・・・)

[数値1] = 平均を求める1つ目の数値、又は範囲を指定します。
[数値2] = 省略可。[数値1]の他に対象となる数値、又は範囲がある場合に指定します。

関数の説明 範囲内の平均値を求める関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)AVERAGE関数

下の表は、ある保育園の身体測定を記録したものです。この表を使って、「平均身長」を求めてみましょう。

50058010601

① 全体の身長から「平均身長」を求める。

(1)[数値1] :平均を求めるセル範囲[E4:E11]を指定します。
=AVERAGE(E4:E11)

 

条件に合うデータを平均(AVERAGEIF関数)

平均値を求める際に、ある条件に沿って求めたい場合はAVERAGEIF関数を使用します。

=AVERAGEIF(範囲, 条件, 平均対象範囲)

[範囲] = 条件を検索するセル範囲を指定します。
[条件] = [範囲]の中からセルを検索する為の条件を数値や式等によって指定します。
[平均対象範囲] = 実際に平均する範囲を指定します。

関数の説明 条件に合うセル範囲の平均値を求める関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)AVERAGEIF関数

今回は同じ身体測定表にて、「クラスがひまわりクラス」という条件をつけて平均を求めてみましょう。

20200821_01

① 全体の身長から「ひまわりクラスの平均身長」を求める。

(1)[範囲] :条件の検索範囲となるセル範囲[C4:C11]を指定します。
(2)[条件] :セル内容の条件となる「ひまわり」を「”」で囲んで指定します。 →“ひまわり”
(3)[平均対象範囲] :実際に平均するセル範囲[E4:E11]を指定します。
=AVERAGEIF(C4:C11,”ひまわり”,E4:E11)

 

POINT!

平均値を数式等によって求めた場合、その答えが割り切れない等の理由で「123.4567」のように長く表示される場合があります。

そういった時は、対象のセルを選択しておき、下の図にある[ホーム]リボン右側の[小数点]ボタンを押すだけで、簡単に小数点以下の桁数を増やしたり、減らしたりすることが出来るので便利です。

efa-averageifs-01

 

複数条件に合うデータを平均(AVERAGEIFS関数)

エクセルでデータを管理する場合には、「○○で、かつ××の時の平均」といった具合に、条件が1つとは限りません。そういった、条件が複数ある場合には、次のAVERAGEIFS関数を使いましょう。

=AVERAGEIFS(平均範囲, 検索条件範囲1, 検索条件1, [検索条件範囲2], [検索条件2]・・・)

[平均範囲] = 実際に平均する範囲を指定します。
[検索条件範囲1] = 条件を検索する1つ目の範囲を指定します。
[検索条件1] = [検索条件範囲1]にて検索する条件を数値や式等によって指定します。
[検索条件範囲2] = 省略可。条件を検索する2つ目の範囲を指定します。
[検索条件2] = 省略可。[検索条件範囲2]にて検索する条件を数値や式等によって指定します。

関数の説明 複数の条件に合うセルの平均値を求める関数
EXCELバージョン Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)AVERAGEIFS関数

上と同じ表を使い、「クラスがひまわりクラス」で、かつ、「性別が男子」の平均身長を求めてみましょう。

50058010801

① 全体の身長から「ひまわりクラスの男子の平均身長」を求める。

(1)[平均範囲] :実際に平均するセル範囲[E4:E11]を指定します。
(2)[検索条件範囲1] :1つ目の条件の検索範囲となるセル範囲[C4:C11]を指定します。
(3)[検索条件1] :セル内容の条件となる「ひまわり」を「”」で囲んで指定します。 →“ひまわり”
(4)[検索条件範囲2] :2つ目の条件の検索範囲となるセル範囲[D4:D11]を指定します。
(5)[検索条件2] :セル内容の条件となる「男」を「”」で囲んで指定します。 →“男”
=AVERAGEIFS(E4:E11,C4:C11,”ひまわり”,D4:D11,”男”)

 

空白・0の扱い/平均関数(AVERAGE系関数)

エクセルにて平均の関数を使った計算は便利ですが、次の点に注意が必要となります。それは、『平均する範囲内に「0」があった場合にその数字も含めて平均してしまう』ということです。

これは、エクセルで作業しているとよくありがちなことですが、下の表を見てみましょう。

 

平均する範囲内に「0」値が含まれている場合

下の表では、上から2人目の夏目さんは、身体測定の当日に欠席だったため、測定が出来ず、仮に「0」を入力していました。
そこで、セルE13にて平均身長を求めてみたところ、実際には身長が110cmより大きい場合が多いのに対し、明らかにそれより小さい「101.8」となりました。
これは、仮に入力しただけの「0」値も計算された結果となります。

average

 

平均する範囲内に空白が含まれている場合

対称的に、もし「0」値を入力するのではなく、「空白」としておけば、下の表のように問題なく平均値が表示されます。

また、その他にも「-」等の文字を入力しておけば同様に正常な結果となります。平均の関数を使用した表でもし『入力できない』部分が発生した場合には、通常はこれらの方法で誤った結果が表示されないようにしておきましょう。

average-1

 

平均するデータから0を除く

表内に「0」があればその数値も含めて平均してしまうAVERAGE関数ですが、それでも「0」値を入力しておきたい等の場合が発生した時には次のような方法で平均を求めるようにしましょう。

 

使い方例)「0」値が含まれている範囲の平均を計算する方法

下の表にて、同じように1名欠席の為、仮に「0」値が入力されています。この際の平均身長を求めてみましょう。

38-3-3

① 全体の身長から「平均身長」を求める。

(1)セル範囲[E4:E11]の合計を求める。

SUM(E4:E11)

(2)セル範囲[E4:E11]に「0」ではないセルの数がいくつあるかカウントする。

COUNTIF(E4:E11,”<>0″)

(3)(1)で求めた合計を(2)で求めた「0」値以外のセルの個数で割る。

= SUM(E4:E11) / COUNTIF(E4:E11,”<>0″)

以上で、平均が求められます。AVERAGE関数にこだわらずに柔軟に対応していきましょう。

 

DAVERAGE関数の使い方

複数の条件等による平均値の求め方には、DAVERAGE関数を使用する方法もあります。
これは、表全体をタイトル行(見出し)も含めた「データベース」として認識させ、『「性別」のタイトルが書かれた列から条件に合うデータを抽出する・・・』などが出来るようになるものです。

=DAVERAGE(データベース, フィールド, 条件)

[データベース] = 列の見出し部分も含めた表の範囲を指定します。
[フィールド] = 平均を求めたい見出しを指定します。
[条件] = 検索条件が入力された範囲を指定します。

関数の説明 条件に合う[フィールド]の平均値を求める関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)DAVERAGE関数

同じく下の身体測定表にて、今回は右側に条件を入力する欄を追加し、条件に合う平均値を求めることにしました。

unnamed-5-2

① 全体の身長から、右側で入力された条件(ひまわりクラスの男子)に沿って平均身長を求める。

(1)[データベース] :表の見出しも含めたセル範囲[B3:E11]を指定します。
(2)[フィールド] :平均を求めたい表の見出しを「”」で囲んで指定します。 →“身長(cm)”
(3)[条件] :条件を入力しているセル範囲[G3:H4]を指定します。(※見出しも含めることに注意)
=DAVERAGE(B3:E11,”身長(cm)”,G3:H4)

 

以上で、「ひまわりクラスの男子」の平均身長を求めることが出来ました。
見出しも含めて認識してくれるDAVERAGE関数では、あらかじめ条件となる見出しや条件内容を入力しておくことで式に反映させることができます。
つまり、今回の場合「ひまわり」を「ばら」に変更するなど、式の条件を変更しやすくなる特徴がこの関数にはあるのです。

 

 

 

 

Rate article