エクセルで離れたセルの0を除く平均の出し方！正しい数値を求める！

エクセルでデータ分析をしていると、「平均を出したいけれど、ゼロを含めたくない」「離れたセルだけを指定したい」というニーズが出てきます。ゼロ値が混ざると平均が歪んでしまい、判断を誤る原因になります。この記事では、離れたセルを対象にしつつ、ゼロを除いて平均を計算する方法をわかりやすく解説します。関数の組み合わせや配列数式、IF文やLET関数などを使いこなして、正確な平均値を取得できる手法を順を追って説明していきます。

エクセル 平均 0を除く 離れたセル を対象にする基本の考え方

離れたセル（非連続セル）から平均を取りたい場合、そのセルをどのように指定するかが第一歩になります。通常のAVERAGE関数では連続セルの範囲指定がメインですが、関数の引数として複数のセルをカンマで区切って指定することも可能です。ただしその場合、ゼロを除外する条件を入れる工夫が必要になります。ゼロを除くには AVERAGEIF や AVERAGEIFS を使ったり、配列数式を組み合わせたりします。

まず対象となるセルを明確にし、それらの値が数値であること、ゼロと空白かどうかの区分を理解することが重要です。空白はAVERAGE関数で無視されますが、ゼロは無視されません。そのためゼロを除外する基準を明示することが必要です。

AVERAGE 関数ではゼロが含まれる

AVERAGE 関数は、指定された範囲内にある数値をすべて足してセルの数で割ります。空白セルや文字列セルは無視されますが、ゼロ値はカウントされます。そのため、ゼロを含めた平均になるという特性があります。ゼロを含めたくない場合、この仕様が障害になります。

AVERAGEIF/AVERAGEIFS を使ってゼロを除外する

AVERAGEIF 関数は、「条件」を指定してその範囲の中で条件を満たす数値だけを平均計算の対象とします。ゼロを除外するには条件に “0” を設定します。複数条件がある場合は AVERAGEIFS を使うことでゼロ以外に複数の条件を組み込むことができます。これが一般的な手法です。

離れたセルを平均対象とする指定の仕方

非連続セル（離れたセル）を平均対象とするには、関数の引数に複数のセルをカンマで区切って指定する方法があります。また、選択範囲を名前定義しておき、その名前を使って範囲をまとめるという方法もあります。ただし、この場合ゼロを除外する方法と組み合わせないと平均値が期待と異なります。

離れたセルでゼロを除く平均を取得する具体的な方法

ここでは、離れたセルを対象にゼロを除いて平均を出す具体的手順をいくつか紹介します。状況に応じて使い分けられるよう、標準関数を使う方法、配列を使う方法、LET関数を使う方法など、多様な手法を説明します。どれも最新の Excel 機能で使えるので安心してください。

SUM と COUNT を組み合わせて手動で計算する方法

離れた複数セルを指定し、それらの合計を SUM で求め、ゼロ以外のセル数を COUNTIF（条件付きで数える関数）で求め、それらで割るという手法です。例えば A1, C1, E1, G1 の四つのセルでゼロを除きたい場合、式としては

(A1+C1+E1+G1)/((A10)+(C10)+(E10)+(G10))

のようにそれぞれセルがゼロかどうかを TRUE/FALSE にし、それを数値として扱うことでゼロ以外のセル数を分母にする方法です。この方法は汎用性が高く、離れたセルを明確に指定する場合に有効です。

AVERAGE と IF を組み合わせた配列数式（動的配列対応版）

最新の Excel では動的配列が使えるので、IF 関数の中に条件を入れてゼロを除いた値のみを対象にした平均を得ることができます。例えば、非連続セルを配列リテラルで指定し、ゼロ以外の値をフィルタするような式を使います。こうした配列数式は Enter キーのみで入力できるバージョンもあります。

例として、A1, C1, E1, G1 のセルを対象にゼロを除く平均を計算する式は次のようになります。
AVERAGE( IF( {A1,C1,E1,G1} 0, {A1,C1,E1,G1} ) )

この式では IF でゼロ以外のものだけを返し、それを AVERAGE で平均しています。すべてがゼロまたは空白の場合はエラーになるため、その場合のハンドリングも検討します。

LET 関数を使って可読性と再利用性を高める方法

LET 関数は変数を定義して式内で再利用できるため、複雑な式を整理するのに非常に便利です。離れたセルの平均をゼロ除外で求める式を LET を使って書くと、読みやすく保守しやすくなります。

たとえば、対象セルを rng という変数にして、ゼロを除いた合計を SUMIF(rng,”0″,rng)、ゼロを除いたセル数を COUNTIF(rng,”0″)、平均＝合計÷件数 とする構成が典型です。セルの指定方法次第で rng に配列指定が入ります。

離れたセルで複雑な条件を追加する応用テクニック

単純にゼロを除外するだけでなく、複数条件を満たすセルだけを対象にしたいときや、セルの間隔が一定の離れたセルを条件付きで選びたい場合など、より応用的なテクニックがあります。IF 関数、MOD 関数、ROW や COLUMN 関数を組み合わせて、パターン的に離れたセルを対象にする方法もあります。

列や行の間隔が一定なセルの平均取得

例えば毎 2 列または毎 3 行ずつ離れたセルを対象にする場合、MOD 関数と COLUMN 関数または ROW 関数を応用して、対象セルかどうかを判定することが可能です。IF の中で MOD を使って、列番号または行番号の差が特定の数で割り切れるセルのみを対象にします。ゼロ除外も同時に組み込まれるように工夫します。

複数条件（例：ゼロ除外＋特定以上の値だけを含める）

データにノイズがあったり、特定の閾値以上の値だけを平均したいときは AVERAGEIFS 関数を使います。複数の条件を AND 条件で組み込むことができます。非連続セルを範囲で指定できない場合は名前定義したセルの集合を使うか、配列形式で条件を与える方法を使います。

ゼロと空白が混在している場合の注意点

一見空白に見えても実際にはゼロ値が入力されているケースや、文字列としてゼロが入っていることがあります。空白だけを除きたい場合は条件を “” にするなどの対策が必要です。ゼロを除外するのか空白を除外するのか、また両方除外するのかを明確にし、式に反映させておくべきです。

実践例：具体的なシナリオでの式と使い分け

ここでは具体的な場面を想定し、どの式を使うか、どのように設定するかを比較しながら説明します。表形式で異なる方法の長所と短所を整理し、どのようなケースでどの方法がベストかを明示します。

シナリオ 1：特定セル 5 個を指定してゼロを除く平均を取りたい

対象セルが A10, B13, D6, K18, L12 のように離れており、これらの中でゼロを除きたいという場合、公算的に手動指定＋ IF 組み合わせの方法が使えます。具体的には次の式：

=(A10 + B13 + D6 + K18 + L12) / ((A100)+(B130)+(D60)+(K180)+(L120))

この方法は対象セルが少ない時に実用的です。ただし、対象セルが増えると式が長くなりミスが起こりやすくなります。また、すべてのセルがゼロまたは空白のときにゼロ除算エラーになる可能性がありますので IFERROR 等で囲むと安全です。

シナリオ 2：大きなデータの中からパターンで離れたセルを対象にしたい

例えば列が散らばっていたり、規則的な間隔で配置されている離れたセルを対象に平均を出したいとき、MOD 関数と配列数式を使うとよいでしょう。対象となる列番号や行番号の差を判定し、IF 内で条件を組み合わせてゼロ除外します。範囲指定を一つにまとめられるので管理が容易です。

シナリオ 3：複数条件付きでの平均取得（たとえばゼロを除きつつ 10 以上の値だけ）

AVERAGEIFS 関数を使えば、ゼロ以外という条件に加えて「10 以上」「空白でない」「特定の文字列を含む」などを AND 条件で付け加えることができます。値が離れたセルの場合、配列形式や名前定義を使って対象セル集合を範囲として指定することと条件設定を両立させることがポイントです。

関数ごとの比較と使い分けのポイント

ここでは、AVERAGE・AVERAGEIF・AVERAGEIFS・SUM+COUNT・配列数式・LET 関数など複数の方法を表で比較し、それぞれどのような場面で向いているかを整理します。見やすい比較で、自分のケースに合う方法がすぐ分かります。

よくあるトラブルと解決策

平均をゼロ除外で出そうとしてもうまくいかないケースもあります。ここではその典型例と原因、解決方法を紹介します。エラー表示や予期せぬ平均値のズレに悩まされている人に役立ちます。

#DIV/0! エラーが出る場合

対象の離れたセルすべてがゼロまたは空白の場合、分母がゼロになるため #DIV/0! というエラーが出ます。これを防ぐには式を IF または IFERROR で包んで、分母がゼロのときに空白を返す、あるいは特定のメッセージを表示するようにします。例：

=IF( (セル条件) , (計算式) , “” ) または IFERROR(… , “”)

空白セルとゼロが混ざっていて除外が意図通りでない場合

空白に見えても実際はゼロ値が入力されていたり、さらに文字列として「0」が入っている場合があります。また、表示上ゼロを隠しているだけでセルの値としてはゼロ扱いされているケースもあります。これらが原因で除外が不十分になることがありますので、セルの内容をクリア、数値書式の見直し、条件の指定を “0” や “” などで厳格に行うことが大切です。

離れたセル指定のカンマ引数数が多すぎてエラーになる場合

対象セルを手動で指定してカンマ区切りで多数列挙すると、関数の引数制限や読みづらさの問題が出ることがあります。こうした場合は、名前定義を使ってセル集合をまとめるか、配列演算でまとめて処理する方法を検討します。LET 関数も併用できると整理しやすくなります。

まとめ

離れたセルを対象に平均を取得し、ゼロを除外するための手法は複数あります。基本的には AVERAGEIF／AVERAGEIFS を使ってゼロ以外の条件を指定する方法が手軽です。非連続のセルを指定するなら、SUM＋COUNT 手動指定や配列数式を使った方法が有効です。LET 関数を使えば式が整理され、読みやすくメンテナンスしやすくなります。

まずは自分のデータ構造（離れたセルが規則的かランダムか、ゼロと空白の混在具合、複条件の必要性など）を把握し、適した方法を選ぶことが最も重要です。その上で条件を適切に設定し、エラー対策を施すことできちんとした平均値を得られます。