【Excel】AVERAGEIFで条件付き平均を求める|0・空欄・複数条件の違い

Excelで「A支店だけ」「80点以上だけ」の平均を出すには、AVERAGEIF関数を使います。条件に合う行を探す範囲と、実際に平均する数値の範囲を分けて指定できるのが特徴です。

ただし、0を除くか、空欄をどう扱うかによって平均は変わります。平均が高くなる式を選ぶのではなく、何を一件と数える集計なのかを先に決めましょう。ここでは実際の数値で計算を確かめ、条件の書き方とエラー時の確認順を説明します。

1. AVERAGEIFの三つの引数を確認する

基本形は=AVERAGEIF(範囲,条件,平均対象範囲)です。第1引数で条件を調べ、第2引数で一致条件を指定し、第3引数から対応する行の数値を平均します。第3引数を省略すると、第1引数の範囲自体が平均対象になります。

=AVERAGEIF(A2:A7,"A支店",B2:B7)

この例ではA列に支店名、B列に金額が入っている想定です。A列がA支店になっている行のB列だけを使います。文字列の条件は半角のダブルクォーテーションで囲みます。支店名をD2に入力して切り替えたい場合は、条件をD2にします。セル参照そのものを"D2"と囲まないでください。

条件を同じ範囲の数値で判定するなら、=AVERAGEIF(B2:B7,">=80")のように二つの引数で書けます。文字の支店名を調べる式で第3引数を省略すると、金額列を指定したことにはならないので注意します。

2. 0と空欄を含む例で平均を検算する

次の六行をA2:B7へ入れたとします。空欄の行のBセルは、本当に何も入力しない状態です。「空欄」という文字を入れる例ではありません。

行・支店	金額
2:A支店	100
3:B支店	300
4:A支店	0
5:A支店	200
6:B支店	500
7:A支店	空欄

=AVERAGEIF(A2:A7,"A支店",B2:B7)の結果は100です。A支店の数値は100、0、200の三つなので、合計300を3で割ります。空のB7は分母に含めません。支店名が一致する行は四行でも、平均の対象となる数値は三つです。

B7へ数値0を入力すると、合計は300のまま、分母が4になって平均は75へ変わります。未入力と0を同じにすると集計の意味が変わる理由です。0が「販売なし」を意味する実績なら、除外すると実態より高い平均になります。一方、未入力の代用として0が入っているなら、集計前に記録方法を見直す必要があります。

3. 数値条件とセル参照を組み合わせる

数値条件には、等しい値のほか、>、>=、<、<=、<>を使えます。たとえばB2:B7から0より大きい数値だけを平均するなら、次の式です。

=AVERAGEIF(B2:B7,">0")

上の例では100、300、200、500を対象にし、1100÷4で275になります。この式には支店の条件がないので、A支店だけの平均ではありません。また、">0"は負の値も除外します。0だけを除く"<>0"と同じ条件ではないことに注意してください。

基準値をD2に置き、「D2以上」を平均したいときは、比較記号とセル参照を&でつなぎます。

=AVERAGEIF(B2:B7,">="&D2)

D2が200なら、対象は300、200、500で平均は1000÷3、約333.33です。">=D2"と書いてもD2の内容を参照する式にはなりません。条件セルの未入力を許す場合は、空欄のときの表示方針も決め、意図せず0を条件に使わないようにします。

4. 「含む」と「で終わる」の違いを確認する

文字列の条件にはワイルドカードを使えます。"*支店"は「支店」で終わる文字列、"*支店*"は途中も含めて「支店」を含む文字列です。「東京支店」はどちらにも合いますが、「東京支店臨時」は後者だけに合います。

?は任意の一文字です。星印や疑問符そのものを条件に含めるときは、~*、~?のようにチルダを前に付けます。たとえば商品区分「A*」だけを調べるのと、Aで始まる区分をまとめるのでは、別の集計になります。

文字条件を広げる前に、実際に対象になる名称を数件確認してください。前後の空白などで一致しない場合、何でも部分一致に変えて済ませると別部署まで含むおそれがあります。条件の構文と空欄の扱いはMicrosoftのAVERAGEIF関数の説明を確認できます。

5. 条件範囲と平均範囲の行をそろえる

支店名がA2:A7、金額がB2:B7なら、両方の開始行・終了行をそろえて指定します。A2とB2、A3とB3という対応で計算するため、金額範囲だけB3から始めると別の行の金額と結び付いてしまいます。

AVERAGEIFでは、平均対象範囲の大きさが違っても、必ずエラーになるわけではありません。Microsoftの説明では、平均対象範囲の左上セルを起点に、条件範囲と同じ大きさのセルが実際の対象になります。そのため、A2:A7に対してB2:B5と短く指定しても、「B5までしか計算しない」という意味にはなりません。

この仕様を頼りに短く書くより、同じ行数で明示した方が確認しやすくなります。複数の集計セルへコピーする場合は、範囲を固定します。D2に支店名があるなら、次の式をコピーして条件セルだけ変える設計ができます。

=AVERAGEIF($A$2:$A$7,D2,$B$2:$B$7)

新しい明細を追加したときは、両範囲を同時に広げます。一方だけを広げて計算が通ったとしても、正しい対応が維持されているとは限りません。

6. 二つ以上の条件にはAVERAGEIFSを使う

「A支店、かつ金額が0より大きい」のように複数の条件を満たす平均なら、AVERAGEIFSを使います。こちらは平均する範囲が最初です。AVERAGEIFから名前だけ変えて引数をそのまま残すと、意図しない式になります。

=AVERAGEIFS(B2:B7,A2:A7,"A支店",B2:B7,">0")

例では100と200が対象なので、結果は150です。先ほどのA支店全体の100とは異なります。どちらが正しいかは、0を含める業務上のルールで決まります。「ゼロを除いた方が見やすい」という理由で切り替えないでください。

AVERAGEIFSの条件は、対応する行ですべて成立する必要があります。また、条件範囲と平均対象範囲は同じ大きさ・形にそろえる必要があります。AVERAGEIFSの公式説明で、引数の順番と範囲の要件を確認できます。

7. #DIV/0!をすぐ0へ置き換えない

条件に合う数値がなければ、平均を計算できず#DIV/0!になることがあります。条件に合う行がない場合だけでなく、該当行の平均対象が空欄や文字列だけの場合も確認します。数字に見える文字列は、表示形式を数値へ変えるだけでは数値にならない場合があります。

まず条件の綴り、範囲の位置、対象金額が数値かどうかを調べます。COUNTIFで支店名の一致件数を数えても、それは平均の分母である数値件数とは限りません。例のA支店が四行、平均対象が三件だった違いを思い出してください。

レポート上でエラーを見せたくない場合も、原因を確認してから「対象なし」「要確認」などの表示を設計します。すべてをIFERROR(...,0)で0へ置き換えると、本当に平均0の集団と計算できなかった集団を区別できません。元データに別のエラーがある場合も先に修正します。

8. 平均の意味を確認して集計を仕上げる

完成後は、対象となった数値を書き出し、合計を数値の件数で割って照合します。0を入力した場合、空欄にした場合、条件に合う行がない場合を小さな表で試すと、期待する扱いを確認できます。表示桁数を減らしても元の計算値まで丸めたとは限らないため、端数処理の必要な報告では別途ルールを決めます。

この平均は各行を一件として扱います。数量が異なる商品の平均単価など、重みを付ける集計とは別です。対象件数や対象期間も併記すると、平均だけを比較する誤解を減らせます。式を戻すときは集計セルだけを修正し、条件に合わせるために元データを削除しないようにしてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】