AVERAGEIF関数で「0を除く」や特定条件に一致するデータだけの平均値を出す

Excel関数で特定条件に一致するデータの平均を求める方法をお探しですね。

広告

Excelで「0を除いた平均」を出す方法|AVERAGEIF関数の使い方を分かりやすく解説

Excelで平均値を出すとき、普通にAVERAGE関数を使うと「0」も計算に入ってしまいます。

でも、売上がまだ発生していない行や、未入力の代わりに0を入れている行、無料サンプルの行なども含めて平均を出すと、実際より低い数値になってしまうことがあるんです。

そんなときに便利なのが、条件に合うデータだけで平均を出せる**AVERAGEIF関数**です。

この記事では、「0を除いた平均」の出し方から、特定の条件に合うデータだけを集計する方法、失敗しないための注意点まで、分かりやすく説明していきます。

AVERAGEIF関数って何？普通のAVERAGE関数と何が違うの?

AVERAGEIF関数は、**指定した条件に合うセルだけを使って平均値を計算できる**Excel関数です。

普通のAVERAGE関数は、指定した範囲にある数値を全部まとめて平均します。

だから、範囲の中に「0」が入っていれば、その0も正しい数値として平均に含まれてしまいます。

一方、AVERAGEIF関数なら「0以外」「100以上」「営業部だけ」「商品Aだけ」みたいな条件を付けられるので、本当に集計したいデータだけに絞って平均を出せるんです。

基本の書き方

“`
=AVERAGEIF(範囲, 条件, [平均範囲])
“`

– **範囲**：条件をチェックするセルの範囲
– **条件**：平均対象を選ぶためのルール
– **平均範囲**：実際に平均したい数値が入っている範囲

条件をチェックする範囲と平均したい範囲が同じ場合は、3つ目の「平均範囲」を省略できます。

例えば、B2からB20までの数値のうち、0以外だけを平均するなら：

“`
=AVERAGEIF(B2:B20,”<>0″)
“`

これだけでOKです。

空白セルや文字列と「0」は違う

ここで大事なポイントがあります。

**空白セルや文字列と「0」は扱いが違う**んです。

AVERAGE関数やAVERAGEIF関数では、空白セルや文字列は基本的に平均の対象から外されます。

でも、0は数値なので対象に含まれます。

だから、「未入力のつもりで0を入れている」「該当なしを0で表している」といった表では、AVERAGE関数だけでは思った通りの平均になりません。

0をちゃんとしたデータとして扱うのか、それとも除外すべき値として扱うのか、先に決めておくことが正しい集計への第一歩です。

「0を除いた平均」の出し方

AVERAGEIF関数で「0を除いた平均」を出したいときは、条件に**「”<>0″」**を指定します。

「<>」はExcelで「等しくない」という意味の記号です。

つまり「”<>0″」は「0じゃない値」という意味になります。

実際の使い方

例えば、C2からC30までに売上金額が入っていて、0円の行を除いて平均売上を出したいときは：

“`
=AVERAGEIF(C2:C30,”<>0″)
“`

これで、C2からC30のうち0以外の数値だけで平均が計算されます。

「0を除く」と「0より大きい」は違う

注意したいのは、**「0を除く」と「0より大きい」は同じじゃない**ということです。

「<>0」は、0以外ならマイナスの数値も対象に含めます。

返品額や差額などでマイナス値が入る表では、マイナスも平均に含めるかどうかを確認する必要があります。

もし「プラスの数だけを平均したい」なら：

“`
=AVERAGEIF(C2:C30,”>0″)
“`

こう書きます。

売上や点数のように普通はマイナスが出ないデータなら結果は同じになりやすいですが、会計や在庫管理では意味が変わってくるので注意してください。

見た目は0でも実は違う場合がある

また、画面では0に見えても、実際には小数点以下にすごく小さい値が残っている場合があります。

表示の設定で小数点以下を隠していると、セルには0.0001みたいな値が入っていても画面上は0に見えることがあるんです。

この場合、「<>0」では除外されず、平均の対象に含まれてしまいます。

思った通りの結果にならないときは、表示する桁数を増やしてみたり、ROUND関数で丸めた補助列を作ったり、データの入力ルールを見直したりすると、原因が見つけやすくなります。

エラーを防ぐ工夫

0を除外した平均をレポートに表示する場合は、**該当するデータが1件もないケース**も考えておくと安心です。

条件に合う数値が1つもないと、AVERAGEIF関数は「#DIV/0!」というエラーを返します。

エラー表示を避けたいときは：

“`
=IFERROR(AVERAGEIF(C2:C30,”<>0″),”該当データなし”)
“`

このようにIFERROR関数で囲みます。

数値として0を返したいときは、最後の部分を「0」に変えればOKです。

ただし、該当データがないことと平均値が0であることは意味が違うので、報告資料では「該当データなし」と表示したほうが誤解されにくいです。

いろんな条件で平均を出す実用例

AVERAGEIF関数は、0を除く平均だけじゃなく、部署名・商品名・地域・ステータスなど、**特定の条件に合うデータだけの平均**を出すときにも便利です。

文字列の条件で絞り込む

例えば、A列に部署名、C列に売上金額が入っている表で、営業部の平均売上だけを求めるなら：

“`
=AVERAGEIF(A2:A100,”営業部”,C2:C100)
“`

この式では、A2からA100の中から「営業部」と一致する行を探して、その行に対応するC2からC100の数値だけで平均を計算します。

数値の条件で絞り込む

数値条件もよく使います。

例えば、C列の点数から80点以上の平均を出したいときは：

“`
=AVERAGEIF(C2:C100,”>=80″)
“`

比較演算子を使う条件は、**必ずダブルクォーテーション(“)で囲む**のが基本です。

「>=80」を「”>=80″」と書くことで、Excelが条件として正しく理解してくれます。

基準値をセルで指定したいときは：

“`
=AVERAGEIF(C2:C100,”>=”&E1)
“`

このように、比較演算子とセル参照を「&」でつなぎます。

これで、E1の数値を変更するだけで条件を変えられます。

あいまい検索も使える

文字列の条件では、完全一致だけじゃなく**ワイルドカード**も使えます。

ワイルドカードは、文字列の一部をあいまいに指定する記号です：

– **「*」**：何文字でもOK
– **「?」**：1文字だけ

例えば、A列の商品名が「東京A」「東京B」「東京限定」みたいに入っていて、東京で始まる商品の平均単価を出すなら：

“`
=AVERAGEIF(A2:A100,”東京*”,C2:C100)
“`

特定の言葉を含むデータを対象にしたいときは：

“`
=AVERAGEIF(A2:A100,”*東京*”,C2:C100)
“`

前後にアスタリスクを付けます。

複数の条件を使いたいときは?

AVERAGEIF関数は基本的に**1つの条件だけ**を指定する関数です。

「営業部かつ関東」「商品Aかつ0以外」「2024年1月のデータだけ」みたいに複数の条件で平均を出したいときは、**AVERAGEIFS関数**を使います。

例えば、B列が部署、C列が商品、D列が売上の表で、営業部の商品Aの平均売上を求めるなら：

“`
=AVERAGEIFS(D2:D100,B2:B100,”営業部”,C2:C100,”商品A”)
“`

AVERAGEIFとAVERAGEIFSでは**引数の順番が違う**ので注意してください。

AVERAGEIFSは最初に平均対象範囲を書きます。

よくある失敗と正確に使うコツ

範囲のサイズをそろえる

AVERAGEIF関数でよくある失敗の1つが、**条件範囲と平均範囲のサイズがずれている**ケースです。

例えば、条件範囲をA2:A100、平均範囲をC2:C90みたいに指定すると、行の対応関係が崩れて正しい集計になりません。

条件を判定する範囲と平均する範囲は、**同じ行数・同じ位置関係で指定する**のが基本です。

表にデータを追加する可能性があるなら、Excelの**テーブル機能**を使うと範囲が自動で広がって、数式の修正漏れを防げます。

エラー値が入っている場合

もう1つの注意点は、**平均対象範囲にエラー値が含まれている場合**です。

空白や文字列は平均対象から無視されることが多いですが、「#N/A」「#VALUE!」みたいなエラーが含まれていると、AVERAGEIF関数の結果もエラーになることがあります。

元データにエラーが混ざる可能性があるときは、補助列でIFERROR関数を使ってエラーを空白に変えてから平均を出すのが現実的です。

例えば、D列に：

“`
=IFERROR(C2,””)
“`

こんな補助列を作って、その補助列を平均対象にすると集計が安定します。

0を除外する理由を明確に

「0を除く」平均を使うときは、**なぜ0を除外するのか理由をはっきりさせておく**ことが大切です。

未入力や対象外を0で表しているなら除外するのが自然ですが、テストの0点、売上0円、稼働時間0時間などが実際の結果を表す場合は、0も大事なデータです。

こういう0を除外すると、平均が実際より高く見えてしまいます。

集計する前に「0は欠損値なのか、意味のある実績値なのか」を確認して、必要なら表の入力ルールを次のように分けると、後から分析しやすくなります：

– 未入力は空白
– 対象外は「N/A」
– 実績ゼロは0

件数も一緒に確認しよう

実務では、**平均値だけで判断せず、件数も一緒に確認する**のがおすすめです。

0を除いた平均が高くても、対象データが2件しかなければ信頼性は低くなります。

例えば：

“`
=COUNTIF(C2:C100,”<>0″)
“`

これで0以外の件数を確認して、平均値と並べて表示すると、集計結果が妥当かどうか判断しやすくなります。

さらに、条件付き平均をよく使う表では、SUMIF・COUNTIF・AVERAGEIFSを組み合わせることで、合計・件数・平均を同時に把握できます。

AVERAGEIF関数は単体でも便利ですが、**条件の意味とデータの状態を確認しながら使う**ことで、より正確で説得力のある集計ができるようになります。

まとめ

この記事を参考に、Excelで「0を除いた平均」をうまく活用してみてください。

最初は少し難しく感じるかもしれませんが、慣れればとても便利な機能です!

広告