エクセルで離れたセルの平均から0を除く方法！AVERAGE関数でゼロを無視して計算するテクニック

エクセルで平均を出したいのに、データの中に0が混ざっていて正しい値にならない、離れたセルを選んで平均を出したいがうまくいかない、と悩む方はとても多いです。
特に集計表や業務報告では、未入力を0として管理しているケースも多く、そのままAVERAGE関数を使うと実態よりも小さい平均値になってしまいます。
この記事では、離れたセルを対象にしながら、0を除いて平均を計算する方法を、関数の組み合わせと実務的なテクニックを交えて、分かりやすく解説します。

エクセル 平均 0を除く 離れたセル を実現する基本的な考え方

エクセルで離れたセルの平均から0を除く計算を行うには、まず「0をどう扱うか」と「離れたセルをどう指定するか」の2点を整理しておく必要があります。
通常のAVERAGE関数は、指定した範囲やセルの数値をすべて対象にして平均を計算するため、0が含まれていると必ず平均に影響します。離れたセルもカンマ区切りで複数指定できますが、この場合も0はそのまま平均に含まれます。

これを回避するには、AVERAGEIFやAVERAGEIFS、あるいはIF関数との組み合わせによる配列数式や、最近のバージョンで利用できるFILTER関数などを使って、「条件に合うセルだけを平均する」という考え方に切り替えるのがポイントです。
ここを理解しておくと、離れたセルでも0を除外した平均を柔軟に計算できるようになります。

0を除く平均が必要になるシーンとは

0を除く平均が必要になる場面として多いのは、未入力や対象外を0で管理しているケースです。例えば、売上が発生していない日を0と入力している場合、本来は売上があった日だけで平均を取りたいのに、0を含めると平均が低くなりすぎてしまいます。
同様に、テストの欠席者を0点として管理している成績表や、センサーが未取得のデータを0で表現している測定データなども、0を含めると実態を表さない平均になってしまいます。

また、0は単に「値がゼロ」であるのか、「値が存在しない」のかを区別しづらい点も重要です。実務では、0を「カウントに含めない値」とみなして平均を取ることが多く、そのためにAVERAGE関数だけではなく、AVERAGEIF関数など条件付き平均の手法が頻繁に使われます。
このような背景を理解しておくと、なぜ0を除く設定が必要なのかが明確になります。

離れたセルの平均を取るときの基本仕様

離れたセルの平均を取る場合、AVERAGE関数に複数のセルや範囲をカンマで区切って指定します。例えば、セルA1、C3、E5の3つだけを平均したい場合は、AVERAGE(A1,C3,E5)と指定します。
また、A1:A3とC1:C3の2つの範囲を同時に指定することもでき、その場合はAVERAGE(A1:A3,C1:C3)のような書き方になります。このように、離れたセルや範囲を柔軟に組み合わせて平均計算が可能です。

ただし、この基本仕様では0が含まれているセルもすべて計算対象になります。0を除外するロジックは関数側で用意されていないため、条件付きの平均関数や、0を無視するための補助列、配列数式などを組み合わせる必要があります。
以降の章で、離れたセルを指定しつつ0を除く具体的な実装例を詳しく見ていきます。

AVERAGEとAVERAGEIFの役割の違い

AVERAGE関数は、指定したセルまたは範囲内の数値の合計を件数で割る、最も基本的な平均計算用の関数です。一方、AVERAGEIF関数は、「条件を満たしたセルだけを対象に平均する」という目的で用意されている関数で、0を除きたい場合などに非常に有効です。
例えば、「0以外の数値だけを平均したい」場合は、AVERAGEIF(範囲,”<>0″) のように指定することで、0ではないセルだけが平均対象になります。

ただし、AVERAGEIFは1つの条件にしか対応できず、複数条件が必要な場合にはAVERAGEIFSを使います。また、AVERAGEIFは原則として連続した範囲を対象にする設計であるため、離れたセルを多く含む場合には、関数を工夫して書く、または別の手法を検討する必要があります。
この役割の違いを理解しておくと、どのケースでどの関数を選ぶべきか判断しやすくなります。

離れたセルの平均から0を除くための具体的な関数テクニック

離れたセルを対象にしながら0を除いて平均を計算するには、基本のAVERAGE関数だけでは不十分です。ここでは、AVERAGEIF、AVERAGEIFS、IFとの組み合わせなど、複数の関数テクニックを組み合わせて、実務で使いやすい形に落とし込む方法を順番に解説します。
それぞれの方法には長所と短所がありますので、自分のファイル構成や運用ルールに合った方法を選ぶことが重要です。

また、関数の書き方だけでなく、「セル選択の仕方」もポイントになります。同じ結果を得るにも式を短くできる書き方や、後から範囲を拡張しやすい設定方法なども存在します。
これらを押さえておくと、単なる一時しのぎの式ではなく、再利用性が高く、他のメンバーとも共有しやすい関数テクニックとして活用できます。

単発の離れたセルで0を除くシンプルな書き方

離れたセルが数個だけであれば、IF関数とAVERAGEを組み合わせたシンプルな配列数式で対応できます。例えば、A1、C3、E5の3セルのうち、0を除く平均を取りたい場合、次のような発想になります。
IFで「0なら空欄、それ以外ならその値」を返し、その結果をAVERAGEで平均する形にします。概念としては AVERAGE(IF({A1,C3,E5}<>0,{A1,C3,E5})) のようなイメージです。

このような配列的な指定は、最新のエクセルではそのまま確定できることが多いですが、古いバージョンではCtrl＋Shift＋Enterで確定する必要があります。使用しているエクセルのバージョンによって挙動が異なる点には注意が必要です。
離れたセルの数が少ない場合には、視認性も高く、式のメンテナンスもしやすい方法として有効です。

複数範囲に対してAVERAGEIFを応用する方法

AVERAGEIFは通常「1つの範囲」に対する条件付き平均を計算しますが、工夫次第で複数範囲や離れたセルに対応させることも可能です。例えば、A1:A5とC1:C5の2つの範囲から0を除いた平均を取りたい場合、単純にAVERAGEIF(A1:A5,”<>0″)とAVERAGEIF(C1:C5,”<>0″)をそれぞれ計算し、重み付き平均を行うというアプローチがあります。
つまり、「合計÷件数」を自分で組み立てるイメージです。

具体的には、合計値をSUMIF(範囲,”<>0″)で求め、件数をCOUNTIF(範囲,”<>0″)で求め、複数範囲分を足し合わせてから、全体の合計を全体の件数で割ります。こうすると、離れた複数の範囲でも「0を除外した平均」を一貫したロジックで求めることができます。
少し式は長くなりますが、AVERAGEIFだけでは対応しにくいケースを柔軟に処理できる点がメリットです。

IF関数とAVERAGE関数を組み合わせる配列数式

0を除外する条件を柔軟に書きたい場合には、IF関数とAVERAGE関数を組み合わせた配列数式が非常に強力です。例えば、「0または空白は無視して平均を取りたい」というような条件は、AVERAGEIFだけでは書きにくいですが、IFを使えば詳細にコントロールできます。
概念的な形としては、AVERAGE(IF(条件,値)) という構造を取ります。

例えば、A1:A5とC1:C5の両方から、0と空白を除いた平均を取りたい場合、IF((A1:A5<>0)*(A1:A5<>””),A1:A5) のように、複合条件で有効な値だけを抽出し、それをAVERAGEに渡す形をとります。最新のエクセルでは動的配列に対応しており、配列数式の確定方法もシンプルになってきています。
この方法は少し高度ですが、一度マスターすると複雑な条件付き平均に広く応用できます。

最新のエクセルで使える関数を使った0を除く平均の応用

最新のエクセルでは、従来の関数に加えて、FILTER関数やLET関数、動的配列機能などが利用できます。これらを活用すると、離れたセルをまとめて一つの配列として扱い、その中から0を除いた値だけを抽出してAVERAGEを計算する、といったスマートな記述が可能になります。
従来の配列数式に比べて、可読性が高く、修正や拡張も行いやすいのが特徴です。

特にFILTER関数は、条件に合致した値だけを動的に取り出せるため、「0以外」「空白でない」などの条件を自然な形で表現できます。離れたセル群を一旦配列として結合し、それをFILTERで絞り込んでからAVERAGEに渡す、という流れを構築すれば、メンテナンス性の高いシート設計が実現できます。

FILTER関数とAVERAGEの組み合わせ

FILTER関数は、範囲と条件を指定して一致するデータだけを抽出する関数です。この性質を利用すると、「0を除いた値だけを取り出してから平均する」という処理を、非常に直感的な式で書くことができます。
イメージとしては、AVERAGE(FILTER(範囲,範囲<>0)) のような形になります。

離れたセルの場合は、まずそれらを1つの配列としてまとめてからFILTERに渡します。例えば、{A1,A3,C5,E2} のように波括弧で並べた配列を使い、FILTER({A1,A3,C5,E2},{A1,A3,C5,E2}<>0) と書きます。こうすることで、0以外の値だけが抽出され、その集合に対してAVERAGEで平均を計算できます。
動的配列に対応したエクセル環境であれば、特に難しい設定をしなくても、この仕組みを自然に利用できます。

動的配列で離れたセルをひとまとめに扱うコツ

動的配列機能により、エクセルでは波括弧を用いた配列リテラルや、CHOOSEなどを使って複数の範囲を結合した配列を容易に扱えるようになりました。離れたセルや範囲を一つの論理的な配列としてまとめて、その上で条件フィルタや平均計算を行うというスタイルが取りやすくなっています。
例えば、CHOOSE({1,2},{A1:A3},{C1:C3}) で2つの範囲をまとめるなど、柔軟な設計が可能です。

これを応用して、FILTERやAVERAGEと組み合わせることで、「複数の離れた範囲から0を除いた平均」を簡潔な1本の式で表現できます。また、範囲をテーブル化しておくと、行の追加に応じて配列も自動で拡張されるため、メンテナンス性がさらに向上します。
このような動的配列の考え方は、これからのエクセル設計の標準とも言えるため、平均計算以外の場面でも役立ちます。

LET関数で複雑な式を読みやすく整理する

0を除いて平均を取るロジックが複雑になると、1本の式が長くなり、保守性が低下します。LET関数を使うと、中間計算を変数として定義できるため、式全体を段階的に整理しながら記述できます。
例えば、「離れたセルをまとめた配列」「0を除いたフィルタリング結果」「最終的な平均値」といった各ステップに名前をつけて管理することが可能です。

概念的には、LET(配列, {A1,A3,C5,E2}, 有効値, FILTER(配列,配列<>0), AVERAGE(有効値)) のような構造を取ります。このように書くことで、後から式を読む人にも意図が伝わりやすくなり、誤修正のリスクも減らせます。
関数のネストが深くなりがちな0除外ロジックを分かりやすく表現できるため、チームでシートを共有している場合にも特に有効です。

AVERAGE関数とAVERAGEIF関数の違いと正しい使い分け方

0を除いて平均を取りたいと考えたとき、多くの方がまず検討するのがAVERAGE関数とAVERAGEIF関数です。しかし、この2つの関数は役割と挙動が異なり、場面に応じた使い分けが必要です。
ここでは、両者の違いを整理し、どのような条件のときにどちらを選べばよいかを、具体例を交えて説明します。

特に重要なのは、「対象範囲が連続しているか」「条件が単一か複数か」「0以外にも除外したい値があるか」の3点です。これらを整理しておくと、関数の選択ミスによる誤集計や、後からの修正作業を大幅に減らすことができます。
業務でのエクセル運用においては、こうした基本的な関数の設計思想を理解しておくことが、信頼性の高いシートを作るうえで重要です。

AVERAGE関数が適しているケース

AVERAGE関数は、条件が不要な純粋な平均計算、すなわち「指定した値をすべて均等に扱いたい」ケースに向いています。例えば、既に0が存在しないように前処理されているデータや、0も含めて平均値として意味を持たせたいデータなどです。
また、離れたセルを少数だけ指定してざっと平均を確認したいような簡易チェックにも向いています。

さらに、他の関数や配列処理で条件付きの値だけを抽出済みの場合も、最終的な集約関数としてAVERAGEを使う形が適しています。このように、AVERAGEはあくまでも「条件付き抽出が済んだ後の最後の集約担当」と位置付けると分かりやすいです。
0を除外する処理自体は、IFやFILTERなど他の関数側に任せる、という設計思想で使うのが効果的です。

AVERAGEIF・AVERAGEIFSが力を発揮するケース

AVERAGEIFやAVERAGEIFSは、「条件に合致したデータだけを平均する」という目的に最適化された関数です。特に、「0を除外したい」「特定の文字列やカテゴリに該当するデータだけ平均したい」など、単純ではあるが明確な条件を持つケースで威力を発揮します。
0を除く平均であれば、AVERAGEIF(範囲,”<>0″) という非常にシンプルな式で実現できます。

AVERAGEIFSを使えば、「0以外かつ特定の部署」「0以外かつ日付が指定期間内」といった複数条件の平均も容易に書けます。ただし、基本的に連続した範囲を前提とするため、離れたセルが多い場合にはやや扱いづらくなります。
そのようなときは、まず対象範囲をテーブル化して連続範囲にまとめる、もしくは補助列を用いて「有効データの集合」をあらかじめ用意しておくと、AVERAGEIF系の関数をスムーズに使えます。

0と空白の扱いの違いに注意

AVERAGE、AVERAGEIF、AVERAGEIFSはいずれも空白セルを自動的に平均対象から除外しますが、0は「有効な値」として扱われる点が重要です。この挙動を理解していないと、「空白は無視されるのに0は入ってしまうのはなぜか」と混乱しがちです。
実務上、「未入力」を0で表現しているケースでは、この仕様が平均値の歪みを生む原因になります。

0と空白を明確に区別したい場合は、IF関数や、条件で空白を指定したAVERAGEIFなどを組み合わせて制御します。例えば、「0と空白の両方を除外したい」なら、AVERAGEIFS(範囲,範囲,”<>0″,範囲,”<>”) のように2条件を設定します。
このように、0と空白の扱いを意識して式を組み立てることで、思わぬ集計ミスを防ぐことができます。

実務でよくあるパターン別：0を除く離れたセルの平均計算レシピ

ここでは、実務で頻出する具体的なパターンごとに、0を除いて離れたセルの平均を計算するレシピをまとめます。同じ0除外といっても、入力のルールや表の構造によって、最適な関数や設計が変わってきます。
自分の利用シーンに近いパターンを見つけて、それぞれの書き方を参考にしてみてください。

また、パターン別にメリット・デメリットも整理しておくと、「小規模シートならこの方法」「部署全体で共有する大規模ファイルなら別の方法」といったように、用途に応じた選択がしやすくなります。
以下の表は、代表的な方法を比較したものです。

月次売上のうち売上ゼロ日を除いて平均したい

月次売上表で、売上が発生していない日を0として管理している場合、「売上があった日だけの平均売上」を求めたいことがよくあります。この場合、売上範囲が連続していれば、AVERAGEIF(範囲,”<>0″) で簡単に実現可能です。
日付列と売上列がある場合には、日付で期間を絞りつつ、0を除くAVERAGEIFSを使う方法もあります。

一方で、「特定の曜日だけ」「特定のキャンペーン期間だけ」といった条件が加わる場合には、IF関数やFILTER関数との組み合わせで柔軟な条件指定を行います。売上0日の扱いを、平均に含めるかどうかをあらかじめ定義しておくことで、集計の一貫性を保つことができます。
このような売上平均の設計は、経営判断にも直結するため、0除外のロジックをシート上にコメントなどで明示しておくことも有効です。

テストの欠席者や未受験者を0としている成績表

成績表で欠席者や未受験者を0点で管理している場合、そのままAVERAGEを使うと、クラス平均が実際よりも大きく下がってしまいます。多くの教育現場では、「受験者のみを対象とした平均点」が必要になるため、0を除いた平均計算が必須です。
このとき、連続範囲であればAVERAGEIF(範囲,”<>0″)が有効ですが、特定の科目だけ離れた列にあるなど、構造が複雑なこともあります。

そのような場合には、対象科目のセルを配列としてまとめ、IFまたはFILTERを用いて0以外を抽出してから平均する方法が有効です。また、欠席を0ではなく空白で管理するルールに変更できるのであれば、AVERAGEの標準挙動で「空白は無視」されるため、ロジックがシンプルになります。
いずれにせよ、0と欠席の意味付けをどうするかを関係者間で共有しておくことが重要です。

センサー計測値の異常値や0値を除外する

センサーや計測装置から取得したデータでは、異常時に0が記録される、あるいは一時的に値が取得できず0になることがあります。このような0を含めて平均すると、実際の測定値を大きく歪めてしまうため、異常値や0値を除いた平均がよく求められます。
ここでは、0以外にも明らかにあり得ない値を除外したいケースも多いため、条件がやや複雑になります。

例えば、「0以外かつ、理論上の範囲内」の値だけを対象とするために、AVERAGEIFS(範囲,範囲,”<>0″,範囲,”>=下限”,範囲,”<=上限”) のような式を組みます。離れたセンサー値をまとめたい場合は、まず補助列に条件を満たした値を抽出し、その列を連続範囲としてAVERAGEIFSやFILTER＋AVERAGEの対象にすると、管理しやすくなります。
このような測定値処理では、ログの保全と計算ロジックの明示が特に重要になるため、関数だけでなく、コメントや説明セルも併せて整備すると安心です。

エラーを防ぐための注意点と効率的なシート設計のポイント

0を除いた平均計算は、一見すると単純な要件に見えますが、条件設定やセル指定を少し間違えるだけで、結果が大きくずれてしまうことがあります。ここでは、よくあるエラーや設計上の落とし穴を避けるためのポイントを解説します。
特に、将来の追加データに対しても正しく動き続けるかどうか、他のメンバーが式を見て理解できるかどうか、といった視点が重要です。

また、効率的なシート設計のためには、単に1つのセルで答えが出ればよいという発想ではなく、「再利用しやすさ」「変更に強い構造」「ロジックの透明性」といった観点から設計を見直すことも大切です。
以下で紹介するポイントを押さえておけば、0を除く平均に限らず、他の統計処理や集計関数を組み合わせる際にも役立ちます。

0と空白、文字列が混在している場合のチェック

実務のデータでは、0と空白に加えて、ハイフンや文字列が混在しているケースがよくあります。AVERAGEやAVERAGEIFは文字列を無視しますが、意図せず文字が混ざっていると、平均の分母となる件数が想定より少なくなり、結果が偏ってしまうことがあります。
そのため、0除外ロジックを組む前に、データの型や表現ルールを確認することが重要です。

具体的には、ISNUMBER関数などを使って数値かどうかを判定し、数値でないものが一定数以上見つかる場合は、入力ルールの見直しやデータクリーニングを行います。また、数値のように見えるが文字列として入力されているケースもあるため、VALUE関数やセルの書式設定を用いて統一を図ることも検討します。
こうした前処理を行っておくことで、0を除いた平均計算の信頼性が大きく向上します。

関数が長くなりすぎる場合の分割方法

0を除いて平均する条件が増えるにつれ、1つのセルに書く関数が長くなり、可読性が低下します。長い式は誤記や修正ミスの原因になるため、適切に分割して補助列を活用することが大切です。
例えば、「有効データかどうかの判定」を補助列で行い、その列がTRUEの行だけを平均する、といった設計も有効です。

また、最新のエクセルではLET関数を使って中間計算を変数化すれば、1セル内でも論理構造を明示しながら式を整理できます。いずれの場合も、「何をしているのか」が式から読み取れる状態を保つことが重要です。
特にチームで利用するファイルでは、自分以外の人が式を読むことを前提に、分かりやすさを優先した設計を心掛けると、運用上のトラブルを大幅に減らせます。

表形式やテーブル機能を使って将来の拡張に備える

平均を計算する範囲が今後も増えていく場合、普通のセル範囲参照だけでシートを組んでいると、データが増えるたびに範囲を修正する必要が出てきます。これを避けるために有効なのが、エクセルのテーブル機能を使ってデータ範囲を構造化する方法です。
テーブルとして設定すると、行の追加に応じて関数の参照範囲も自動で拡張されます。

さらに、構造化参照を使えば、「売上列」「得点列」といった列名で式を記述できるため、セル番地だけの式よりも読みやすくなります。0を除く平均のロジックも、テーブル上の特定列に対してAVERAGEIFSやFILTERを適用するだけで済むようになり、拡張性と保守性が向上します。
将来的なデータ増加や仕様変更を見越して、早い段階からテーブル化を検討することをおすすめします。

まとめ

エクセルで離れたセルの平均から0を除くには、単にAVERAGE関数を使うだけでは不十分で、AVERAGEIFやAVERAGEIFS、IF関数との組み合わせ、さらにはFILTERやLETといった最新の関数を状況に応じて使い分けることが重要です。
0を「未入力」や「対象外」と見なすのか、「本当に0の値」と見なすのかという設計思想をはっきりさせておくことで、平均値の解釈が明確になり、集計結果の信頼性も高まります。

また、離れたセルを扱う場合には、配列や動的配列の考え方を取り入れることで、複雑な範囲指定をシンプルな1本の式にまとめることもできます。テーブル機能や補助列を積極的に活用し、将来の拡張にも耐えられるシート設計を意識することが、業務効率化とミス防止の鍵です。
ここで紹介したテクニックを組み合わせれば、多くの実務シーンで「0を除いた本当に知りたい平均」を的確に算出できるようになります。