Excel。平均値。今回は文字列をゼロとして計算したい。
平均アレコレその3
AVERAGEA関数
最近、データ分析に付随するご質問が多くなってきているように感じておりまして、
企業研修さんや、マンツーマン、
そして、集中講座などで平均の出し方も様々あるんですよとご紹介しております。
そこで、今回は、平均アレコレの第3弾として、欠席などの文字がデータに入っているときに、
ゼロとして認識したい時に使う、
AVERAGEA関数
をご紹介しましょう。
下記のデータをご覧ください。
前回のAVERAGEIFの表と同じです。
前回は、品川さんが欠席したのですが0点となっています。
通常のオートSUMボタンの平均。
つまり、AVERAGE関数を使うと品川さんの0も含んでしまったため、
平均値が下がってしまいました。平均は、総和を総和の個数で除算しますので、
平均値が下がったので、AVERAGEIF関数を使ってゼロを除外して、
平均値を算出することが出来ることをご紹介しました。
ちなみに、数式は、=AVERAGEIF(C3:C8,"<>0",C3:C8)
今回は、品川さんをゼロではなくて、欠席と入力してあったらどうなるのでしょうか？
そうすると、AVERAGE関数は範囲内の数値のみで算出しますので、先程のゼロと異なり、
54.6と算出されるわけです。AVERAGEIF関数も変わらずに、54.6を算出しております。
ところが、今回は、逆で、欠席をゼロとして算出したい場合どうしたらいいのか？
ということなのです。
現場は、ケースバイケース。
色んな状況にマッチした方法を知っているのは損になりません。
そこで、このようなケースの場合はどうしたらいいのかというと、
AVERAGEA関数を使うと、問題は解決することが出来ます。
それでは、数式を作っていくことにしましょう。
C11をクリックして、AVERAGEA関数のダイアログボックスを表示していきましょう。
値1には、今回平均を算出したい範囲になりますので、C3:C8ということになりますね。
では、確認したら、OKボタンをクリックしてみましょう。
AVERAGEA関数を使って算出されてC11には、
AVERAGE関数の時にC6がゼロというデータだった時と同じ、45.5が算出されましたね。
AVERAGEA関数という、あまり使用頻度が少ない関数ですが、様々なケースがありますので、ちょっと知っておくのもいいでしょう。
といっても、マイナーな関数を思い出すよりも、
AVERAGE関数だとゼロなら同じ結果を算出してくれますので、
ここからは、アイディアなのですが、AVERAGE関数で欠席という文字でも、
AVERAGEA関数と同じように算出させる方法があります。
それは、ユーザー定義書式を使います。
C3:C8を範囲選択して、
セルの書式設定ダイアログボックスの表示形式にあるユーザー定義を選択します。
種類には、
#,##0;[赤]-#,##0;"欠席";
と入力します。これでOKボタンをクリックしましょう。
すると、ゼロは欠席という表示に替わりますので、AVERAGE関数でも、算出することが出来ます。関数ばかりではなく、他の手法もあるかもしれませんので、
Excel力は、トータルでスキルアップしていきたいものですね。
