AVERAGEIFSで複数条件に基づいて平均する

AVERAGEIFSは、複数の条件を同時に満たす値の平均を計算します。すべての売上を平均する代わりに、1月のEast地域の売上の平均はいくらだったでしょうか。という問いに答えられます。

これで3つの関数がそろいます。SUMIFSは合計し、COUNTIFSはカウントし、AVERAGEIFSは平均を求めます。すべて同じ複数条件の形式を使うため、1つわかればほかの関数もほぼ理解できます。

AVERAGEIFS は SUMIFS とまったく同じ構造です。平均する値を最初に指定し、その後に条件のペアを続けます。

• average_range — 平均する数値
• criteria_range1、criteria1
• criteria_range2、criteria2

内部では、一致する値を合計し、一致した件数で割っています。つまり、1つのすっきりした関数で SUMIFS を COUNTIFS で割るのと本質的に同じです。

列Aが地域、列Bが月、列Cが金額だとすると、これは1月の東部地域の売上の平均を求める例です。

C:C の金額を平均します。2つの条件ペアによって、平均を計算する前に対象の行が東部かつ1月のものに絞り込まれます。

平均する値を、その値の大きさで絞り込むこともできます。たとえば、100を超える高額な東部地域の売上だけを平均するとします。

ここでは、列Cを average_range と criteria_range の両方に使用しています。AVERAGEIFS では、同じ列を両方の役割に使えます。

ユーザーが操作できるレポートを作成するには、値を直接入力する代わりにセルを参照します。地域を F1 に、最低金額を F2 に入力します。

比較演算では、演算子とセルを結合します。">"&F2 は、F2 に入っている値より大きいという意味です。地域のような単純な文字列の一致には演算子は必要なく、セル参照だけを指定します。

AVERAGEIFS で最も注意が必要なのは、すべての条件に一致する行がない場合です。平均するものがないため、#DIV/0! エラーが返されます。

これは SUMIFS（0を返します）や COUNTIFS（0を返します）とは異なります。0個の値の平均は定義できないため、Excel は推測で値を返さず、エラーを発生させます。

数式を IFERROR で囲むと、一致するものがない場合にわかりやすい値を表示できます。見づらい #DIV/0! の代わりに、ダッシュやメッセージを表示できます。

これにより、フィルターの組み合わせに該当するデータがない場合でも、ダッシュボードをすっきり見せられます。絞り込んだデータを平均するときは、空の結果になる場合を必ず考慮してください。

重要な点として、AVERAGEIFS は average_range 内の空白セルを無視します。空白セルは合計にも除数にも含まれません。一方、0 が入力されたセルは実際の数値として含まれます。

0が欠損データの代わりに使われている場合、その値によって平均が下がります。必要に応じて、C:C, "<>0" のような条件を追加して除外することを検討してください。

必要なだけ条件を追加できます。列Dが担当者だとすると、東部地域で1月に、Maria が担当した売上の平均を求められます。

ペアを1つ追加するたびに、絞り込みの条件が厳しくなります。AVERAGEIFS はAND ロジックを使用するため、3つすべての条件を満たす行だけが平均の対象になります。

2つの引数の順序を正しく使い分けてください。

• AVERAGEIF: range, criteria, [average_range] — 判定する範囲が先で、average_range は省略可能な最後の引数です。
• AVERAGEIFS: average_range, range1, crit1, ... — average_range が常に最初です。

ほかの関数と同様に、複数形の ...IFS 版を基本にすると、SUM、COUNT、AVERAGE で一貫したパターンを使えます。

AVERAGEIFS は、複数の項目を横に並べて比較する集計に適しています。列Fに地域名を縦に並べ、条件列を固定したコピー可能な数式1つで、地域ごとの平均売上を計算します。

$C:$C を average_range、$A:$A を criteria_range、$F2 を相対参照する地域セルにすると、数式を下方向にコピーするだけで、地域ごとの平均をすぐに求められます。

複数の条件に基づいて値を平均できるようになりました。

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range が最初です。
• 一致するデータがない場合は #DIV/0! になるため、IFERROR で対処します。
• 空白セルはスキップされますが、0は含まれます。必要に応じて "<>0" で除外します。
• 動的な数値の上限や下限には ">"&F1 を使用します。

次は、これらの条件関数で日付の範囲を扱う方法を学びます。