=AVERAGEIF(A2:A7,"North",C2:C7)は、A列がNorthの行について、C2:C7の売上の平均を出します。SUMIFと同じように動きますが、合計を一致した行の数で割る点が違います。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
F2はNorthの3行、120、110、40を平均して90を表示します。F3はNorthとAppleの2つの条件が必要なのでAVERAGEIFSを使います：(120 + 40) / 2 = 80。F4には平均する範囲が別にないので、一致した売上そのものを平均します。
AVERAGEIF関数とAVERAGEIFS関数の構文
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
引数の順番には、SUMIFとSUMIFSと同じ落とし穴があります。AVERAGEIFは平均する範囲を最後に置き(省略もできます)、AVERAGEIFSは最初に置きます。条件はどちらも同じ書き方です："North"、">50"、"<>0"、"*apple*"、または演算子をセルにつないだ">"&F5。平均する範囲の空のセルと文字列は飛ばされます。
0を除いて平均する
AVERAGEは0を値として数えるので、欠席して0点の生徒が2人いるとクラスの平均が下がります。空のセルは違い、AVERAGEはそれを飛ばします。=AVERAGEIF(B2:B7,"<>0")は0も飛ばします。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
AVERAGEは240を5で割り、48を表示します。Danの空のセルは除かれますが、2つの0は数えられるからです。"<>0"を指定したAVERAGEIFは240を3で割り、80を表示します。B5に60を入力すると両方が変わり、B5に0を入力するとAVERAGEだけが動きます。0とマイナスの数値も除くには">0"を使います。
AVERAGEIFが#DIV/0!を返す理由
何も一致しないと、AVERAGEIFは割る数がなく#DIV/0!を返します。IFERRORで囲めば、代わりにダッシュ記号、メッセージ、空のセルを表示できます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! 0 または空のセルで割っています。Westの行はないので、F2は#DIV/0!を、F3はメッセージを表示します。A3をWestに変えると、どちらも45を表示します。
MAXIFS関数とMINIFS関数
上のシートのF4からF6は、条件付きで最大値と最小値を求めています。AVERAGEIFSと同じ順番で、探す範囲が最初です。=MAXIFS(C2:C7,A2:A7,"North")は120を、=MINIFS(C2:C7,A2:A7,"North")は40を返します。AVERAGEIFと違い、何も一致しないときはエラーではなく0を返します。
MAXIFSとMINIFSにはExcel 2019以降かMicrosoft 365が必要です。Excel 2016以前では=MAX(IF(A2:A7="North",C2:C7))で同じことができます。これらのバージョンではCtrl+Shift+Enter(MacではCmd+Shift+Enter)で入力してください。
練習: 2つの条件で平均する
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
やってみよう: クラスAの点数を、0(欠席した生徒)を除いて平均してください。数式はF2に書きます。
平均の平均: よくある間違い
大きさの違うグループの平均をさらに平均すると、全体の平均が間違います。Northは3行、Southは2行なので、2つの平均の平均では、Southの各行が本来より大きな重みで数えられます。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
E4は105を、E5は5行の本当の平均102を表示します。グループの大きさが違うときは、1つのAVERAGEIFSで行そのものを平均するか、SUMIFSを同じ条件のCOUNTIFSで割ります：
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
単位数や数量で重みを付けた点数は、また別の計算です。それは加重平均です。
よくある質問
AVERAGEIFとAVERAGEIFSの違いは何ですか？
AVERAGEIFは条件が1つで、平均する範囲を最後に置きます：=AVERAGEIF(A2:A7,"North",C2:C7)。AVERAGEIFSは複数の条件を受け取り、平均する範囲を最初に置きます：=AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple")。
Excelで0を除いて平均するには？
=AVERAGEIF(B2:B7,"<>0")を使います。0でないセルだけを平均します。空のセルはAVERAGEやAVERAGEIFがもともと除くので、条件が必要なのは本当の0だけです。
AVERAGEIFが#DIV/0!を返すのはなぜですか？
条件に一致したセルがないので、Excelが0の合計を0の件数で割っているからです。別のものを表示するには囲みます：=IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data")。
条件付きで最大値を求めるには？
MAXIFSを使い、探す範囲を最初に置きます。=MAXIFS(C2:C7,A2:A7,"North")はNorthの最大値を返します。最小値はMINIFSで同じように求めます。どちらもExcel 2019以降かMicrosoft 365が必要です。
