AVERAGEIFで条件に基づいて平均する

SUMIF で合計し、COUNTIF で数えてきました。この仲間の3つ目が AVERAGEIF です。条件を満たす値だけの平均を求めます。

すべての売上を平均する代わりに、East 地域の売上だけ、または50を超えるスコアだけを平均できます。フィルターと平均の計算を、1つのわかりやすい手順にまとめられます。

AVERAGEIF は SUMIF と同じ3つの部分で構成されます。

• range 検査するセル
• criteria 条件
• average_range 平均するセル

range を調べて一致するセルを見つけ、その対応する average_range のセルを平均します。

A2:A10 に地域、B2:B10 に金額が入っている場合、East 地域の平均売上を求めるには、列Aで East を検索し、一致する列Bの値を平均します。

内部では SUMIF を COUNTIF で割る処理を行っていますが、AVERAGEIF を使えば、それを1つの読みやすい数式にまとめられます。

検査する列と平均したい列が同じ場合は、3番目の引数を省略できます。B2:B10 のうち70を超えるスコアだけを平均するには、次のようにします。

この場合、列Bが検査対象と平均対象の両方になります。average_range を省略すると、スプレッドシートは range を両方に使用します。

ほかの IF 関数と同じように、条件をセルに保存すると柔軟に使えます。D1 に地域名が入っている場合は、条件として D1 を指定します。

これで、1つのセルから平均値を操作できます。D1 を East から West に変更すると、平均もすぐに再計算されます。集計表やダッシュボードの選択欄に最適です。

AVERAGEIF は、ほかの IF 関数と同じように、引用符内の演算子を受け付けます。100以上の売上だけを平均するには、次のようにします。

• ">=100" 100以上
• "<50" 50未満
• "<>0" 0を除外

最後の条件は、平均を下げてしまう0の値を無視しながら平均できるため、特に便利です。

セルのしきい値を使うには、アンパサンドで演算子とセルを連結します。D1 に基準値が入っている場合は、それより大きい値をすべて平均します。

& によって、">" と D1 の値から条件テキストが作られます。これは SUMIF や COUNTIF で使ったものと同じ連結方法です。

AVERAGEIF には、ほかの関数にはない注意点があります。一致する行がない場合は、割る対象がないため #DIV/0! エラーになります。

たとえば、データに存在しない地域の平均を求めると、このエラーが返されます。これは数式が壊れているのではなく、フィルターに一致するセルが0個だったことをスプレッドシートが知らせているのです。

一致するものがない場合は、数式を IFERROR で囲み、#DIV/0! の代わりにわかりやすいメッセージを表示します。

これで、存在しない地域には目立つエラーではなく「データなし」と表示されます。カテゴリが一部欠けている場合でも、レポートを整った状態に保てます。

AVERAGEIF は、一致したセルのうち数値が入っているセルだけを平均します。average_range 内の完全に空白のセルは無視されるため、0として扱われません。

これは重要な点です。値が欠けていても、文字どおりの0のように平均を0へ近づけることはありません。0も除外したい場合は、値の列に "<>0" という条件を追加してください。

見やすい集計表を作ってみましょう。D2、D3、D4 に各カテゴリを入力し、ラベルのセルを参照する AVERAGEIF を1つ作って下方向にコピーします。

これで各行に、そのカテゴリの平均値が表示されます。前のレッスンで作った SUMIF の合計や COUNTIF の件数と並べれば、数式だけで動くコンパクトなレポートになります。

これで、AVERAGEIF を使って条件付きで平均を求められるようになりました。重要な点は次のとおりです。

• 順序は range、criteria、average_range です。最後の引数を省略すると、検査範囲自体を平均します。
• テキスト、数値、">=100" のような演算子を使用できます。
• 一致するものがないと #DIV/0! エラーになるため、IFERROR で対処します。
• 空白セルは無視され、0として扱われません。

次は、ワイルドカードを使って部分的なテキストを検索します。