データを集計する作業は、職種を問わず多くの業務で必要とされるものです。
中でもよく使われるのが「平均値」による評価ではないでしょうか。
平均値は「合計 ÷ データ数」で求められますが、手作業や電卓では少し手間がかかります。
その点、Excelを使えば、セル範囲を選ぶだけで自動的に計算してくれるため、とても便利です。
さらにExcelには、状況に応じたさまざまな平均の求め方が用意されているので、今回は特に役立つ6つの方法を取り上げて解説します。
もくじ
この記事を読むと、次のような効果が期待できます
- 時間短縮につながる効率的な計算方法を習得できる
- 平均に関する関数の理解が深まり、他の関数への応用もスムーズになる
- より正確な平均値の算出方法を身につけられる
エクセルで平均計算1-AVERAGE関数の入力の基本
平均を計算する AVERAGE関数 は、オートSUMアイコンからも挿入できます。
では、1年1組のテスト計算の結果が入力された一覧表を使い、平均を計算してみましょう。
計算結果を表示したいセルを選択してオートSUMボタンの横にある『ｖ』をクリックすると、合計・平均・個数などが表示されます。
ここでAVERAGE関数を設定する場合は「平均」を選びましょう。
引数を選択してEnterキーを押すと、平均値が自動で計算されました。
エクセルで平均計算2-AVERAGEA関数で空白を無視
ここでは、生徒の一人がテストを欠席した場合の平均の計算方法について見ていきます。
以下の3通りの方法で比較してみましょう。
- 「0」を入力して、AVERAGE関数で計算
- 「欠席」と入力して、AVERAGE関数で計算
- 「欠席」と入力して、AVERAGEA関数で計算
まず、「0」を入力して AVERAGE 関数を使うと、平均は「60.8」と表示されました。
この場合、0 も数値データとして計算に含まれています。
次に、「欠席」と入力して AVERAGE 関数を使うと、平均は「66.9」となりました。
欠席の欄を空白にしても、同じ結果になります。
AVERAGE 関数は、文字列や空白を計算対象に含めないためです。
一方、「欠席」と入力して AVERAGEA 関数で計算すると、平均は「60.8」になりました。
これは、「欠席」という文字が数値「0」として扱われたためで、1つ目のケースと同じ結果です。
このように、AVERAGE関数 は文字列を無視しますが、AVERAGEA関数 は文字列もデータとして処理します（文字列は「0」として扱われます）。
ただし、数値を入力すべきセルに文字を入れるのは、表の設計上あまりおすすめできません。
それでも、AVERAGEA 関数を使えば、こうした入力にも柔軟に対応できます。
エクセルで平均計算3-AVERAGEIF関数を使って条件を設定
ある条件に当てはまる項目だけを集計出来るようにするのがAVERAGEIF関数です。
引数の使い方は以下のとおり。
- 第一引数・・・検索範囲
- 第二引数・・・条件指定
- 第三引数・・・平均対象範囲
下の表のような各教科の点数を入力した表から、AVERAGEIF関数を使って国語のみの平均値を計算してみましょう。
たとえば、下のような表に各教科の点数が入力されている場合、国語のみの平均点を求めてみましょう。
数式バーに次のように入力します。
=AVERAGEIF(D:D,G2,E:E)
これで、指定どおり国語の平均が計算できました。
他の教科についても、国語の数式をコピーしてセルを調整すれば、すぐに集計が完了します。
SUMIF関数 と同様に、AVERAGEIF でも「>=80」のような不等号を使った条件指定や、「*」を使ったあいまい検索も可能です。
たとえば、「80点以上の平均」や、「名前に『木』が含まれる生徒の平均」なども計算できます。
エクセル平均計算4-AVERAGEIFS関数を使って複数の条件設定
AVERAGEIFS関数 は、AVERAGEIF関数の上位互換です。
AVERAGEIFでは条件を1つしか指定できませんが、AVERAGEIFSなら複数の条件を設定できます。
引数の順序はAVERAGEIFと異なり、条件は第4引数以降に追加していきます。
- 第一引数・・・平均対象範囲
- 第二引数・・・検索条件1
- 第三引数・・・検索範囲1
AVERAGEIF関数の紹介では「教科」を条件にしましたが、ここではさらに「学級」という条件を加えてみます。
たとえば、「1年1組の国語の平均点」を求めるといったケースです。
数式は以下のようになります。
=AVERAGEIFS($E:$E, $A:$A, H$1, $D:$D, $G2)
ここでは、「教科」と「学級」の2つの条件を指定しており、引数は5つになります。
この数式を作ることで、「1年1組の国語」という条件に合致するデータの平均値を正確に算出できました。
残りの範囲についても、コピーやオートフィルを使えば効率よく反映できます。
ただし、数式を他のセルに展開する際には、絶対参照を適切に使うことが重要です。
絶対参照についてはこちらの記事をごらん下さい。
エクセル平均計算5-SUBTOTAL関数でオートフィルタに対応
フィルタ機能でよく使われるSUBTOTAL関数の第一引数に「1」を設定することでも、平均値の計算も可能です。
SUBTOTAL関数については、詳細をこちらの記事にも記載しておりますので、よかったらご覧ください。
エクセル平均計算6-DAVERAGE関数でデータベース関数を使いこなす
AVERAGE IF関数やSUBTOTAL関数とは異なる方法で、よりデータベース的な方法で集計するDAVERAGE関数というのがあります。
データベース関数については、こちらにも記事を掲載しておりますので、よかったらご覧ください。
注意点-平均の平均は間違った結果を導く
平均の計算方法を正しく理解しておかないと、間違った結果を導くことがあります。
ここに、各クラスの生徒の身長データとクラス毎の平均値が表示されています。
全ての生徒の平均値を計算してみます。
F17セル(上)には、対象のデータをすべて選択して平均値を算出しました。
F18セル(下)は、各クラスの平均値から更に平均値を算出しました。
この場合は、上と下が同じ結果になりました。
次は、１クラスの生徒数が異なる場合です。
同じように計算したところ、上と下の結果に誤差が発生しました。
このように、平均値をサンプル全体から計算する場合と、各グループの平均値から計算する場合とでは、結果が異なる場合があるので特に注意しましょう。
まとめ
ITC関連の技術の発達が更に速くなってきています。
ビッグデータなどが注目されていますが、正しくデータを見て判断するためには、統計をある程度知っていなければなりません。
平均の計算は、統計には必須です。
エクセルで正しく平均を計算出来るようにしておきましょう。
本日もありがとうございました。