【Excel】「たかが平均値計算」の落とし穴 その原因と対策：Tech TIPS

「Microsoft Excel」を使ってデータを集計、分析する際、平均値を計算するAVERAGE関数もよく使うのではないだろうか。しかし、平均は注意しないと、意図した計算結果と異なる値になることがある。そこで、本Tech TIPSでは、平均を計算する場合の注意点と対策、さまざまな平均値の求め方について紹介する。

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2016／2019／2021／365

Excelで平均値の計算結果が意図と異なる原因は？
「Microsoft Excel」で平均値を計算する場合、求めたいセル範囲に文字列が含まれていると、意図と異なる計算結果になる可能性がある。そこで、本Tech TIPSでは、平均を計算する場合の注意点と対策、さまざまな平均値の求め方について紹介する。画面で使用しているデータは、「テストデータ・ジェネレータ」で生成したテストデータである。

　「Microsoft Excel」を使ってデータを集計、分析する際、合計を計算するSUM関数とともに、平均値を計算するAVERAGE関数もよく使うのではないだろうか。しかし、平均値の計算は注意しないと、意図した計算結果と異なる値になることがある。

　そこで、本Tech TIPSでは、平均を計算する場合の注意点と対策、平均値のさまざまな求め方について紹介しよう。

　以下では、最も広く使われている「算術平均」のことを単に「平均」と表している。調和平均など他の平均については扱っていないので注意していただきたい。

セル範囲に文字列が含まれている場合の平均値に注意

　平均値は、「数値の合計値÷数値の数」で求められる。SUM関数などを使って計算もできるが、一般的にExcelで平均値を求める場合はAVERAGE関数を用いることが多いだろう。ここで注意が必要なのが、セルに数値以外の「N/A」や「−」などの文字列が入力されていた場合だ。

　AVERAGE関数では、これらの文字列が入った項目は無視される。例えば、5人でテストを受けた結果を集計する際、4人が100点、1人が試験を受けず「−」となっていた場合、AVERAGE関数で平均値を求めると、100点になる。受験した人（4人）の平均であれば問題ないが、試験を受けなかった場合は0点とするという決まりで5人の平均を求めたい場合は80点でなければならない。

AVERAGE関数を使った平均値の計算
AVERAGE関数では、求めるセル範囲に文字列が入っていると、そのセルを無視して平均値を計算する。上記のように「−」が入っていると、数値の入ったセルのみを対象に平均値が計算される。

　このように場合によっては、意図とは異なる計算結果になってしまうことがある。

　では、「−」「N/A」などの文字列については「0」として計算する場合はどうすればいいのだろうか。「−」「N/A」などを事前に「0」に置き換えなくても、AVERAGEA関数を使うことで解決できる。AVERAGEA関数では、数値以外を「0」として「数値の数」に含めて計算するため、上記の例だと平均が80点となる。

AVERAGEA関数を使った平均値の計算
AVERAGEA関数では、求めるセル範囲に文字列が入っていると、そのセルの値を「0」として平均値を計算する。上記のように「−」が入っていても、平均値の計算対象になる。

　計算の用途によっては、AVERAGE関数では誤りが生じるということを覚えておいた方がよい。

ステータスバーを見れば平均値はすぐに分かる？

　単純にAVERAGE関数で求められる平均値のみを確認したいというだけならば、AVERAGE関数を使わなくても、Excelのステータスバー（ウィンドウの最下部にある部分）を見るだけで分かる。平均値を求めたいセル範囲を選択すると、ステータスバーに「平均：」と平均値が表示される。

　また、その右側には、セル範囲に含まれるデータの個数が、「データの個数：」として示される。ただし、ここで示されているデータの個数は、「−」や「N/A」といった文字列も含むので、合計値をこのデータの個数で割り算してしまうと、AVERAGE関数で求めた平均値とは異なる値になることがある点に注意してほしい。

ステータスバーで平均値を確認する
平均値を求めたいセル範囲を選択すると、ステータスバーの「平均：」に平均値が表示される。このときの平均値は、AVERAGE関数で求めた値と同じものとなる。

　ステータスバーに「平均：」や「データの個数：」が表示されない場合は、ステータスバーを右クリックして、表示されたメニューで「平均」や「データの個数」をクリックして、チェックが入った状態にすればよい。

　
ステータスバーに「平均：」などが表示されていない場合は、ステータスバーを右クリックして、表示されたメニューで「平均」や「データの個数」をクリックして、チェックが入った状態にすればよい。

［数式］タブの［オートSUM］を使ってAVERAGE関数を一発入力

　単純に選択したセル範囲の平均値を求めたい場合、わざわざAVERAGE関数を入力しなくても、［数式］タブの［関数ライブラリ］グループにある［オートSUM］を利用すると簡単に計算できる（［ホーム］タブの［編集］−［オートSUM］でも構わない）。［数式］タブの［オートSUM］のプルダウンリストで［平均」を選択すると、自動的に選択したセル範囲の下に「=AVERAGE（＜セル範囲＞）」が挿入され、平均値が求められる。

［オートSUM］を使ってAVERAGE関数を入力する（1）
平均値を求めたいセル範囲を選択して、［数式］タブの［オートSUM］をクリック、表示されたメニューで［平均］を選択する。

▼

［オートSUM］を使ってAVERAGE関数を入力する（2）
選択したセル範囲の下にAVERAGE関数を使った数式が入力され、平均値が計算される。

　ただし、必ずAVERAGE関数による平均値となるので、前述の通り、「−」や「N/A」などの文字列が含まれたセルも含めて平均を求めたい場合は、［オートSUM］−［平均」で入力された「=AVERAGE（＜セル範囲＞）」を「=AVERAGEA（＜セル範囲＞）」に書き換える必要がある点に注意してほしい。

［オートSUM］を使って入力したAVERAGE関数を編集する
「−」も平均値に含めたい場合は、［オートSUM］を使って入力したAVERAGE関数を編集し、AVERAGEA関数とすればよい。

平均を求める他の関数

　平均値を求める関数には、AVERAGE関数とAVERAGEA関数だけでなく、AVERAGEIF関数やAVERAGEIFS関数もある。

　AVERAGEIF関数とAVERAGEIFS関数は、条件を指定し、合致したセルのみを計算対象とするものだ。例えば、顧客リストの「女性」を対象に購入金額の平均を計算したいような場合、AVERAGEIF関数を使えばよい。「性別」列でフィルターを使って「女性」に絞り込まなくても、平均値の計算ができる。

=AVERAGEIF（＜検索条件のセル範囲＞,＜検索条件＞,＜平均対象のセル範囲＞）

AVERAGEIF関数の書式

AVERAGEIF関数を使った平均値の計算（1）
AVERAGEIF関数を使うと、他の列で条件を指定して、合致した行の平均値が計算できる。

▼

AVERAGEIF関数を使った平均値の計算（2）
条件で絞り込みたい列でフィルターを使って絞り込まなくても、平均値の計算ができる。

　また、顧客リストの「女性」で6回よりも購入回数が多い人のみを対象としたいといった、複数の条件で絞り込んで平均値を求めたい場合はAVERAGEIFS関数が使える。条件は127個まで指定可能だ。

=AVERAGEIFS（＜平均対象のセル範囲＞,＜検索条件1のセル範囲＞,＜検索条件1＞［,＜検索条件2のセル範囲＞,＜検索条件2＞,……,＜検索条件127のセル範囲＞,＜検索条件127＞］）

AVERAGEIFS関数の書式

　条件を指定する場合、「男性」や「女性」といった文字列や、「6より大きい」といった比較演算子を指定する場合、「"（ダブルクォーテーション）」で囲むのを忘れないようにしよう。

AVERAGEIFS関数を使った平均値の計算（1）
AVERAGEIFS関数では複数の条件で絞り込みができる。

▼

AVERAGEIFS関数を使った平均値の計算（2）
複数の条件で絞込んだ平均値が1つの数式で計算できる。

　また、注意が必要なのは、AVERAGEIF関数とAVERAGEIFS関数で＜平均対象のセル範囲＞を指定する位置が異なる点だ。AVERAGEIF関数は最後、AVERAGEIFS関数では最初に指定する点を間違えないようにすること。

　AVERAGEIF関数とAVERAGEIFS関数は、引数の指定が少々面倒なので、［数式］タブの［関数ライブラリ］グループにある［関数の挿入］を選択し、表示された［関数の挿入］ダイアログを使うとよい。［関数の挿入］ダイアログの「関数の検索」に「平均」と入力して検索を実行すると、「関数名」に平均に関する関数が表示されるので、［AVERAGEIF］もしくは［AVERAGEIFS］を選択、［OK］ボタンをクリックすると、［関数の引数］ダイアログが表示され、各項目を指定するだけで関数の引数を埋めることができる。

「関数の挿入」を使って条件付きの平均を入力する（1）
［数式］タブの［関数の挿入］をクリックし、［関数の挿入］ダイアログを開く。［関数の挿入］ダイアログの「関数の検索」に「平均」と入力して検索を実行すると、「関数名」に平均に関する関数が表示されるので、使いたい平均に関する関数を選択する。ここでは「AVERAGEIFS」を選択したものとする。

▼

「関数の挿入」を使って条件付きの平均を入力する（2）
［関数の引数］ダイアログが開くので、「平均対象範囲」に平均値を計算したいセル範囲を選択する。

▼

「関数の挿入」を使って条件付きの平均を入力する（3）
「条件範囲1」「条件1」などに、条件を検索したいセル範囲、そのセル範囲で絞り込みたい文字列などを入力する。

▼

「関数の挿入」を使って条件付きの平均を入力する（4）
さらに条件を追加したい場合は、ダイアログの右側にあるスクロールバーを使ってスクロールすればよい（この点が少し分かりにくいので注意）。条件を全て入力したら［OK］ボタンをクリックする。

▼

「関数の挿入」を使って条件付きの平均を入力する（5）
AVERAGEIFS関数が入力され、複数の条件で絞り込んだ平均値が計算できる。