テック・ガジェット・ショッピングなど

Excel AVERAGE関数で平均を求めるやり方

AD

エクセルで平均を求めるには、AVERAGE関数を使うが、それに関連したAVERAGEIF関数、及びAVERAGEIFS関数についての紹介。

概要

書式

Copy
AVERAGE(数値1, 数値2, ...)

指定した範囲のセルの平均を求める関数だが、セルの中に文字列、論理値、空白が含まれている場合、それらは無視される。

また、0のセルも平均値として計算されるので、それを除きたい場合には、後述の「0」のセル以外で平均を求める方法を参照してほしい。

同様に引数の数値2は、直接数値やセル参照で指定して平均を割り出すこともできる。（下記のサンプル書式④を参照）

サンプル書式

基本的な数値のみを扱った書式。

A1B1C1D1サンプル書式①表示結果
2003200720102013=AVERAGE(A1:D1)2008.25

文字列、論理値、空白がセルに含まれている場合には、それらは無視される。

A1B1C1D1サンプル書式②表示結果
USBメモリ(空白)20102013=AVERAGE(A1:D1)2010

セルに0が含まれていた場合、その0も平均値として計算される。

A1B1C1D1サンプル書式③表示結果
0200720102013=AVERAGE(A1:D1)1507.5

A1からD1まで複数選択しつつ、例えばさらに個別に値を合計したい場合の平均値を求めるための書式の書き方。

A1B1C1D1サンプル書式④表示結果
2003200720102013=AVERAGE(A1:D1,2016)2009.8

「0」のセル以外で平均を求める方法

数値データに0が含まれている場合もあり、それによって平均値がかなり異なってくる場合もあるかと思う。

そんな時は、0を除いて計算してあげる。

下記のデータを使ってサンプルを3つ紹介。

A1B1C1D1
2003200720102013

①配列数式を使う方法

まずは、下記のように式を入力する。

Copy
=AVERAGE(IF(A1:D1<>0,A1:D1))

そしたら、キーボードを入力する。

Copy
SHIFT + CTRL + ENTER

すると「{}（中カッコ）」が追加されて0を除いた平均を求めることができる。

Copy
{=AVERAGE(IF(A1:D1<>0,A1:D1))}

ただし、この配列数式はデータ量が多いと重くなる。

②AVERAGEIF関数を使う方法

AVERAGE関数ではないだが、Excel2007から導入されたAVERAGEIF関数を使うことで条件付きで0を除いた平均を求めることができる。

Copy
=AVERAGEIF(A1:D1,"<>0")

「”<>0″」は、0以外を意味する。

③COUNTIF関数を使う方法

これも、AVERAGE関数ではないだが、指定の条件のセル数をカウントしてくれるCOUNTIF関数を使う方法。

平均を求めるには、数値全体の合計からセル個数を割ることで求めることができる。

●数値全体の合計/セル個数

数式にすると、数値の合計を求めるSUM関数で数値の合計を求め、COUNTIF関数で0以外のセルをカウントしたものを割ることで求められる。

Copy
=SUM(A1:D1)/COUNTIF(A1:D1,"<>0")

条件に一致したセルの平均を求める

エクセル2007から導入されたAVERAGEIF関数は、特定の条件に一致したセルの平均値を求めることができる。

書式

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

解説

特定の条件に一致したセルの平均値を求めることができるAVERAGEIF関数は、例えば、「カメラアプリ」と「ゲームアプリ」の値段表があったとして、特定の条件として「カメラアプリ」のみの平均値を求めたい場合に使用する。

また、不等号を使って、例えば300円以下のアプリのみの平均値を求めることもできる。（サンプル書式②参照）

そして、結構役に立つのが、「0」を含まないセルの平均値を求めることもできるので、下記のサンプル書式③は覚えておくと便利。

サンプル書式

下記のアプリの値段に関するサンプル表を参考にサンプルの書式を見てみよう。

ABCDE
1カメラアプリゲームアプリカメラアプリゲームアプリカメラアプリ
209002801200350
サンプル書式①=AVERAGEIF(A1:E1,”ゲームアプリ”,A2:E2)
結果表示1050
概要「ゲームアプリ」の平均の値段。「範囲」の「A1:E1」は、2行目にまたがって「A1:E2」と指定してもOK。
サンプル書式②=AVERAGEIF(A2:E2,”<300")
結果表示140
概要300円以下のアプリの平均値。表の例では、0円も平均値の中に含む。この書式の場合には、「平均対象範囲」の省略が可。
サンプル書式③=AVERAGEIF(A2:E2,”<>0″)
結果表示140
概要0円を含まないアプリの平均値。「”<>0″」は、0を含まないという意味。

複数の条件に一致したセルの平均を求める

複数の条件に一致したセルの平均値を求めるには、最後尾に「S」を付けたAVERAGEIFS関数を使用する。

書式

Copy
AVERAGEIFS(平均範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)

解説

AVERAGEIFS関数は、AVERAGEIF関数にSがついて、複数の条件での平均値を求められる関数。

なので、例えば「□□の文字列を含み、○○以上、●●以下のセルの平均値」を求めたい時に活躍する。

特に、下記のサンプル書式⑤の「○○を含まない」セルの平均値を求めるやり方は、覚えておくといいだろう。

また、サンプル⑥のように、検索条件をセル参照にしておくことで、条件の変更があった場合の修正が簡単に手直しができてお薦め。

サンプル書式

下記の表をサンプルに、様々な書式を見ていきましょう。

ABCDE
1エクセルパワーポイントパワーポイントエクセルエクセル
220032007201020102013

○○より大きく、●●より小さいセルの平均値

サンプル書式①=AVERAGEIFS(A2:E2,A2:E2,”>2005″,A2:E2,”<2013")
表示結果2009
概要2行目で、2005よりも大きく、2013よりも小さいセル

文字列○○に該当するセルの平均値

サンプル書式②=AVERAGEIFS(A2:E2,A1:E1,”パワーポイント”)
表示結果2008.5
概要1行目で「パワーポイント」に該当するセルの平均値

文字列○○に該当するセルで、○○よりも大きいセルの平均値

サンプル書式③=AVERAGEIFS(A2:E2,A1:E1,”エクセル”,A2:E2,”>2003″)
表示結果2011.5
概要1行目で「エクセル」に該当し、2003よりも大きいセルの平均値

文字列○○に該当するセルの平均値を四捨五入

サンプル書式④=ROUND(AVERAGEIFS(A2:E2,A1:E1,”エクセル”),0)
表示結果2009
概要1行目で「エクセル」に該当するセルの平均値を、小数点第一位で四捨五入

文字列○○に該当するセルで、数値○○を含まないセルの平均値

サンプル書式⑤=AVERAGEIFS(A2:E2,A1:E1,”エクセル”,A2:E2,”<>2003″)
表示結果2011.5
概要1行目で「エクセル」に該当し、2003を含まないセルの平均値。検索条件の「”<>2003″」は2003を含まないという意味。特に、0を含めたくない場合の使用にお薦め

検索条件をセル参照で指定

サンプル書式⑥=AVERAGEIFS(A2:E2,A1:E1,B1)
表示結果2011.5
概要「検索条件範囲」をセル参照で指定。この例では「B1」の列で「エクセル」に該当し、2003を含まないセルの平均値