【Excel】AVERAGEIFS関数でゼロを除外して平均する!Excelの条件付き平均で正確な値を出す方法

Excelでデータの平均を計算する際、ゼロ値を除外したい場面は多いです。特に、集計データにゼロが含まれていると、平均値が実態よりも低く算出されてしまうことがあります。AVERAGEIFS関数を使えば、条件を指定して平均値を計算できますが、ゼロを除外するには少し工夫が必要です。この記事では、AVERAGEIFS関数を使ってゼロ値を除外し、正確な平均値を算出する具体的な方法を解説します。これにより、データの傾向をより正確に把握できるようになります。

Excelの標準機能では、単純なAVERAGE関数ではゼロ値も計算に含まれてしまいます。条件付き平均を計算できるAVERAGEIFS関数は強力ですが、ゼロを除外する設定が直感的ではないと感じる方もいるかもしれません。この記事を読めば、AVERAGEIFS関数を自在に使いこなし、データの精度を高めることができるでしょう。

AVERAGEIFS関数でゼロを除外する仕組み

Excelでデータの平均値を計算する際、ゼロ値を除外して集計したいケースは頻繁に発生します。例えば、売上データでゼロは「売上なし」を意味する場合、これを含めて平均すると実態とかけ離れた値になります。AVERAGEIFS関数は、このような場合に特定の条件を満たすデータのみを対象として平均を計算できる便利な機能です。

AVERAGEIFS関数は、複数の条件を指定して対象範囲の平均を算出します。この関数を利用することで、「ゼロでない」という条件を付加し、正確な平均値を求めることが可能になります。ゼロを除外する仕組みは、平均を計算する対象範囲に対して、「0より大きい」という条件を指定することにあります。

AVERAGEIFS関数でゼロを除外して平均を計算する手順

AVERAGEIFS関数を用いて、特定の範囲からゼロ値を除外して平均を計算する手順を説明します。ここでは、A1からA10の範囲にある数値の中から、ゼロより大きい値のみを対象に平均を計算する例を挙げます。

1. 平均を計算したいセルを選択する
   結果を表示したいセルをクリックして選択します。
2. AVERAGEIFS関数を入力する
   選択したセルに、以下の数式を入力します。

   =AVERAGEIFS(A1:A10, A1:A10, ">0")

   この数式の各要素は以下の通りです。

   • A1:A10 (1つ目): 平均を計算する対象となる数値が入っている範囲を指定します。
   • A1:A10 (2つ目): 条件を適用する範囲を指定します。ここでは、平均計算と同じ範囲を指定しています。
   • ">0": 条件を指定します。ここでは、「0より大きい」という条件を設定しています。
3. Enterキーを押して確定する
   数式を入力したら、Enterキーを押して計算結果を表示します。

これで、A1からA10の範囲にある数値のうち、ゼロより大きい値のみを対象とした平均値が算出されます。例えば、A1:A10の範囲に{10, 0, 20, -5, 30, 0, 15, 25, 0, 5}というデータがあった場合、ゼロを除外した平均は (10+20-5+30+15+25+5) / 7 = 105 / 7 = 15 となります。

複数の条件を指定して平均を計算する場合

AVERAGEIFS関数は、ゼロを除外する条件だけでなく、他の条件も同時に指定して平均を計算できます。例えば、「特定のカテゴリに属し、かつゼロでない売上」といった集計が可能です。

ここでは、B列に商品カテゴリ、A列に売上が入力されている表で、「カテゴリA」に属する商品の「ゼロでない売上」の平均を計算する例を説明します。

1. 結果を表示したいセルを選択する
   計算結果を表示したいセルをクリックします。
2. AVERAGEIFS関数を入力する
   以下の数式を入力します。

   =AVERAGEIFS(A1:A10, B1:B10, "カテゴリA", A1:A10, ">0")

   この数式では、以下の3つの要素を指定しています。

   • A1:A10 (1つ目): 平均を計算する対象の売上データ範囲です。
   • B1:B10: 1つ目の条件(カテゴリ)を適用する範囲です。
   • "カテゴリA": 1つ目の条件です。「B列がカテゴリAである」という条件を指定しています。
   • A1:A10 (2つ目): 2つ目の条件(ゼロでない)を適用する範囲です。
   • ">0": 2つ目の条件です。「A列の売上が0より大きい」という条件を指定しています。
3. Enterキーを押して確定する
   数式を入力後、Enterキーで計算結果を表示させます。

このように、AVERAGEIFS関数を使えば、複数の条件を組み合わせて、より複雑な条件での平均計算が可能です。ゼロ値の除外も、他の条件と組み合わせて容易に実現できます。

AVERAGEIFS関数でゼロを除外する際の注意点

AVERAGEIFS関数でゼロを除外して平均を計算する際に、いくつか注意すべき点があります。

AVERAGEIFS関数でゼロを除外する条件を指定する際、「>0」と「<>0」の違いに注意が必要です。前者は「0より大きい」、後者は「0と等しくない」という条件になります。

もし、負の数も平均に含めたい場合は、「<>0」を使用します。例えば、売上データでゼロは「売上なし」として除外したいが、赤字(マイナス値)は集計に含めたい場合です。その場合の数式は以下のようになります。

=AVERAGEIFS(A1:A10, A1:A10, "<>0")

「>0」を指定すると、ゼロだけでなく負の数も除外されるため、意図しない結果になる可能性があります。目的に応じて適切な条件演算子を選択することが重要です。

AVERAGEIFS関数で条件を指定する際、範囲の指定方法に誤りがあると、期待通りの結果が得られません。特に、条件を指定する範囲(criteria_range)の行数や列数は、平均を計算する範囲(average_range)と一致している必要があります。

例えば、平均対象範囲がA1:A10で、条件範囲がB1:B9だった場合、Excelはエラーを返したり、意図しない計算結果になったりします。これは、Excelが各条件に対応するデータを正しく紐付けられないためです。

数式を作成する際は、必ず各範囲のサイズが揃っているか確認しましょう。データ範囲のサイズが異なる場合は、必要に応じて範囲を調整してください。

AVERAGEIFS関数は、数値データに対して機能します。もし、対象範囲に文字列やエラー値が含まれている場合、それらは計算対象外となるか、エラーの原因となることがあります。

特に、ゼロを除外する条件「>0」を指定した場合、文字列やエラー値は「0より大きい」という条件を満たさないため、自動的に除外されます。しかし、すべてのデータが数値であるとは限らないため、事前にデータのクリーニングを行うことが推奨されます。

もし、文字列やエラー値が含まれている可能性がある場合は、IFERROR関数などを組み合わせて、エラー値をゼロや空白に置き換えてからAVERAGEIFS関数を適用するなどの対策を検討すると良いでしょう。

AVERAGEIFS関数とAVERAGE関数の違い

Excelで平均を計算する際に主に使用される関数に、AVERAGE関数とAVERAGEIFS関数があります。両者の主な違いは、条件を指定できるかどうかです。

機能	AVERAGE関数	AVERAGEIFS関数
条件指定	不可	複数可能
ゼロ値の扱い	計算に含める	条件で除外可能
用途	単純な平均計算	条件付き平均計算(ゼロ除外含む)

AVERAGE関数は、指定した範囲内のすべての数値を単純に合計し、その個数で割った平均値を返します。ゼロ値や負の値もすべて計算に含まれます。一方、AVERAGEIFS関数は、指定した条件に合致する数値のみを対象として平均を計算します。この条件に「>0」などを指定することで、ゼロ値を除外した平均を求めることができます。

まとめ

この記事では、ExcelのAVERAGEIFS関数を用いて、データからゼロ値を除外して正確な平均値を計算する方法を解説しました。AVERAGEIFS関数に「>0」という条件を追加することで、ゼロを含まない平均値を簡単に算出できます。さらに、他の条件と組み合わせることで、より目的に即した複雑な集計も可能です。

今回学んだAVERAGEIFS関数によるゼロ除外のテクニックは、売上データや在庫データなど、ゼロが特別な意味を持つデータ分析において非常に役立ちます。この知識を活用し、データの精度を高め、より的確な意思決定を行えるようになるでしょう。

今後、さらに高度な条件分岐や集計が必要になった際には、SUMIFS関数やCOUNTIFS関数なども合わせて学習すると、Excelでのデータ分析能力がさらに向上します。

📊

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

🏆

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