トップ > 関数 > 条件集計 > AVERAGEIFS

複数条件に合うものだけの平均を出す

AVERAGEIFS(アベレージイフス)関数

「佐藤さんが売ったモニタの平均単価は?」のように、絞り込んだ上での平均は、全体の平均と混ぜると数字の意味が変わります。AVERAGEIFS は SUMIFS の平均版で、書き方もほぼ同じです。合計と件数が分かっているなら平均は割り算でも出せますが、1つの数式で完結するほうが、表が壊れにくくなります。

✅ これができます:「誰の・何の・平均いくら」が、条件を変えるだけで切り替わるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

数式を入れる前(左)は集計欄が空欄。入れた後(右)は、佐藤のモニタ2件(8,500 と 29,800)の平均 19,150 が出ます

これをコピペすれば動きます

「担当者=佐藤 かつ 商品=モニタ」の平均金額(集計欄の G2 に入れる)

=AVERAGEIFS($D$2:$D$11,$B$2:$B$11,$F$2,$C$2:$C$11,$F$3)

条件に金額の下限も足した版(10,000以上のものだけの平均)

=AVERAGEIFS($D$2:$D$11,$B$2:$B$11,$F$2,$C$2:$C$11,$F$3,$D$2:$D$11,">=10000")

変えるのはここだけ

変える場所を示した図
数式の「変える場所」を番号で示した図。番号は下の表と対応しています
番号変える場所意味書き方・例
1平均する範囲
$D$2:$D$11
平均したい数値の列。SUMIFS の「合計範囲」と同じ位置(先頭)に書く数量の平均が欲しいなら、数量の列に変える
2条件範囲1
$B$2:$B$11
1つ目の条件と照らし合わせる列。ここでは担当者の列自分の表の「判定したい列」に読み替える
3条件1
$F$2
1つ目の条件が入ったセル。ここでは担当者名(佐藤)"佐藤" のように直接書くこともできる。セル参照にしておくと条件変更が楽
4条件範囲2
$C$2:$C$11
2つ目の条件と照らし合わせる列。ここでは商品名の列条件が増えるたびに「範囲・条件」のペアを後ろに足していく
5条件2
$F$3
2つ目の条件が入ったセル。ここでは商品名(モニタ)金額の条件なら ">=10000" のような書き方もできる

組み込み手順

  1. サンプル表を用意します。A〜D列に売上リスト(日付/担当者/商品名/金額)を並べます
  2. F2 に1つ目の条件(佐藤)、F3 に2つ目の条件(モニタ)を書きます
  3. G2 に1つ目の数式を貼り付けると、両方の条件に合う行の平均が出ます
  4. 条件に合う行が1件も無いと #DIV/0! エラーになります(対処は「つまずきどころ」参照)
  5. 自分の表で使うときは、「変えるのはここだけ」の①〜⑤を自分の表の場所に差し替えます

このページのサンプル表は、A列に日付、B列に担当者、C列に商品名、D列に金額が入った売上リスト(10件)です。 佐藤のモニタは 8,500(11/11)と 29,800(11/13)の2件で、平均は 19,150 です。

Excelとスプレッドシートの違い

Excel 2007 以降で使えます。条件に合う行が無いと #DIV/0! エラーになるので、実務では IFERROR と組み合わせることが多い関数です。

Googleスプレッドシートでも AVERAGEIFS がそのまま使えます。数式・引数の順番は Excel と同じです。条件に合う行が無い場合も、同じく #DIV/0! になります。

カスタマイズ例

条件に合う行が無いときに「—」と表示したい

数式全体を IFERROR で囲みます。=IFERROR(AVERAGEIFS(...),"—") の形にすると、対象が無い月や担当者でもエラー表示にならず、表の見た目が崩れません。

平均とあわせて件数も並べたい

平均だけでは「何件の平均か」が分かりません。隣のセルに COUNTIFS で件数も出しておくと、1件だけの平均に振り回されずに済みます。

条件が1つだけなら AVERAGEIF でもよい

条件が1つだけのときは =AVERAGEIF($B$2:$B$11,$F$2,$D$2:$D$11) でも同じ結果になります。ただし引数の順番が AVERAGEIFS と違うので、混在させると読み間違えのもとになります。このサイトでは AVERAGEIFS に統一する書き方をおすすめしています。

つまずきどころ

関連する関数・レシピ

次はこれ:MAXIFS/MINIFS(条件付きの最大・最小)
条件集計の仕上げは最大・最小です。「田中の最高額・最低額」を出します

よくある質問

Q. SUMIFS との違いは?

A. 集計の仕方が違うだけで、書き方は同じです。SUMIFS は合計、AVERAGEIFS は平均を返します。

Q. 空白のセルは平均に入る?

A. 空白は数えません。ただし「0」と入力されたセルは平均の対象になります。未入力と 0 円を区別したいときは注意してください。

Q. Mac でも使える?

A. 使えます。Mac版 Excel と、ブラウザ版の Googleスプレッドシートで動作します。

PR(広告)

もっと体系的に学びたい人へ
独学で体系化するなら、講座で一気に学ぶのが近道です。

Udemy:Excelショートカット講座を見る Udemy:Googleスプレッドシート入門講座を見る