エクセルで平均を計算する際に0を除くには？離れたセルでも正しく平均を求める方法

「離れたセルでも0を平均から除きたい」「AVERAGE関数では0を除けないの？」と悩んでいませんか。普通のAVERAGEでは空白は無視されますが、0は含まれてしまうため、正確な平均値が得られません。本記事では、離れたセル（非連続セル）に対しても0を除外して平均を計算する方法を、最新情報に基づいてわかりやすく解説します。操作方法や互換性にも触れますので、初心者から上級者まで参考になる内容になっています。

エクセル 平均 0を除く 離れたセル を実現する基本的な関数とその特徴

エクセルで平均値を出す際、0を除く処理や離れたセル（非連続セル）への対応には、いくつか代表的な関数があります。まずはその基本を押さえ、どの状況にどの関数が適しているかを理解することが重要です。

AVERAGE、AVERAGEIF／AVERAGEIFS、IF と組み合わせた式、SUM／COUNTIF 等、それぞれ長所と短所があります。特に離れたセルを扱う場合、範囲指定や配列処理がポイントになりますので、その特徴を明確に把握しましょう。最新情報に基づいて、それらの違いを比較します。

AVERAGE とその限界

AVERAGE関数は指定した範囲の中の数値の平均を返しますが、空白や文字列は無視されても、値が0のセルは平均値の分母に含まれるため、結果が小さくなる原因になります。離れたセルをひとまとめに範囲指定できないときには、この関数だけでは目的を達成しにくいです。最新バージョンでもこの仕様は変わっておらず、0を除外したいときには別の関数が必要です。

AVERAGEIF／AVERAGEIFS の活用

AVERAGEIF 関数は「条件」を付けて平均を計算することができ、「0」を条件とすれば0を除くことが可能です。たとえば、A1～A10の中の0でない値だけ平均を出すなら =AVERAGEIF(A1:A10,”0″) のように書きます。AVERAGEIFS を使えば、0を除くことに加えて「特定の区分のみ」「日付・文字列条件あり」など複数の条件が使えます。

離れたセル（非連続範囲）への対応方法

離れたセルを指定したい場合、AVERAGEIF などでは非連続範囲の条件指定が難しいことがあります。そんなときは SUM と COUNTIF を組み合わせたり、IF 関数や配列数式を使用することで個別セルを足し合わせ、0でないセルを数える方法が有効です。最新の Excel では配列数式が簡単になっており、処理の幅が広がっています。

具体的な方法：離れたセルにおける0除外平均の書き方と応用

離れたセルを対象に0を除外して平均を計算する具体的な式を、操作手順付きで紹介します。非連続セルの一覧指定、配列数式、IF 条件、SUM／COUNTIF の組み合わせなど、複数のやり方を比較します。またバージョン違い（古い Excel と最新 Excel）の注意点も含めています。

非連続セルを列挙して SUM／COUNTIF を使う式

非連続セル（例：A1、C1、E1、G1）の値から0以外の平均を求める代表的な式は次の通りです。SUM で合計、COUNTIF で値が0でないセルの数を数えます。

=IFERROR( (SUM(A1,C1,E1,G1)) / ((A10)+(C10)+(E10)+(G10)) , 0 )

この式では、A10 のような論理値が TRUE/FALSE で 1/0 に評価され、それらを加えることで0でないセルの数を得られます。古いバージョンのエクセルでも動作する汎用性の高い方法です。

配列数式を使って複数の離れたセルを条件付きで平均

最新の Excel（365 や最新バージョン）では FILTER 関数や LET 関数などを使って、離れたセル集合を配列として処理し、0を除いた平均を求められます。例えば FILTER で 0 を排除し、AVERAGE で求める方法です。こうした式を使うことで見た目にも分かりやすく、メンテナンス性も高くなります。

AVERAGEIF／AVERAGEIFS を使った範囲条件付きの平均計算

たとえば、複数の離れた範囲を一度に条件付きで平均したい場合、AVERAGEIFS を使って値が「0」であることと対象範囲を結びつけます。ただし、AVERAGEIFS では複数範囲を同時に列挙することは原則できないので、複数の範囲をひとまとめにする補助列を作る、あるいは配列またはヘルパーセルを使う工夫が必要です。

実践例：ケース別の活用シーンと式の応用

実務の状況に応じて、離れたセルかつ0除外平均を使いたい場面はいくつかあります。営業データ、製造データ、特定の日付だけの平均など。ここでは４つの典型ケースを取り上げ、実際の式とスクリーン操作の流れを紹介します。

ケース1：営業部署の日別売上で特定日のみ平均（非連続セル）

たとえば、1日目、10日目、20日目、30日目の売上をセル A1、A10、A20、A30 に入力してあり、それらの平均を出したいが、「0」は無視したいケース。この場合、先ほどの SUM／COUNTIF の組み合わせ式が便利です。古い Excel でも動くため、手軽に結果を得られます。SUM(A1,A10,A20,A30) / ((A10)+(A100)+(A200)+(A300)) の形にすることで、0 でないデータのみを対象に平均が計算されます。

ケース2：FILTER 関数で動的に離れたセルを選ぶ方法

Excel の最新バージョンでは FILTER を使って、ある列でフラグが立っている行のみ、また値が0でない行のみを対象にして離れたセル平均を取ることができます。例えば「Flag 列が TRUE」の行のうち「売上」が 0 でないものの平均を求めるような式です。こうすれば対象セルが増減しても柔軟に対応できます。

ケース3：旧バージョンで配列数式を使う方法

Excel 2016 以前では、配列数式（Ctrl＋Shift＋Enter）を使って IF と AVERAGE を組み合わせて 0 を除く方法があります。たとえば =AVERAGE(IF((A1,A3,A5)0, (A1,A3,A5))) のような形です。非連続セルをカンマ区切りで列挙し、0でないものだけを IF で選別することで平均を得られます。

ケース4：複数条件ありでの平均計算

「0 を除く」だけでなく「地域」「商品カテゴリ」「日付」など複数の条件も一緒に絞りたい場合は AVERAGEIFS の使用が有効です。ただし離れたセルに複数範囲を指定する場合、ヘルパー列や補助範囲を使って一つの範囲として扱えるようにする必要があります。最新 Excel では新しい配列関数を活用してそれを解決できます。

注意点とよくあるトラブル対策

式を組む際の注意点や、初心者がはまってしまいがちなポイントを整理します。特に離れたセル指定によるエラー、0を除外したら値が何もないときの#DIV/0! エラー、Excel のバージョン差による動作の違いなど、実際に使っている中で気をつけるべきことを具体的に挙げます。

#DIV/0! エラーが出る原因と回避方法

すべてのセルが 0 もしくは空白の場合、0除外の条件付き式では「非0セルの数」が 0 になり、分母が 0 になってしまいます。この結果 #DIV/0! エラーが返されます。これを避けるには IFERROR 関数や IF を併用し、「分母 が 0 のときは空白を返す」などの処理を入れることが有効です。

離れたセル指定での構文エラーや誤った範囲指定の見落とし

非連続セルを指定する際、AVERAGEIF／AVERAGEIFS は範囲指定を一本で指定する必要がありますが、カンマ区切りの複数セルを直接条件範囲に使えないことがあります。複数範囲をまとめたい場合は、SUM／COUNTIF 組み合わせか配列式を使うか、または補助列で一つの連続範囲に整理しておく工夫が必要です。

Excel のバージョンによる機能の違い

最新の Excel では FILTER、LET、動的配列など強力な機能が使え、より簡潔に式を書けるようになっています。一方で旧バージョン（Excel 2010～2016 等）では配列数式が必要だったり、機能制限によって工夫が多く求められることがあります。導入する環境に応じて式を選びましょう。

実際の操作手順：セル選択と式入力の流れ

ここでは、離れたセルを対象に0を除外して平均値を出すまでの操作手順を、画面操作を意識した流れで説明します。初めての方にもイメージしやすいように、メニューや入力方法も含めて解説します。

ステップ1：対象セルの確認と整理

まず対象とする離れたセルをリストアップし、どのセルに値が入っているか、0 が入っているかを確認します。セルの場所が散らばっているとミスが起きやすいため、補助列を使って対象セルをまとめることも検討しましょう。たとえば対象セルだけまとめた列にコピーまたは参照を挿入しておくと、関数が扱いやすくなります。

ステップ2：式の入力と確認

対象セルが決まったら、先ほど紹介した SUM／COUNTIF や FILTER／AVERAGE 組み合わせの式を入力します。ERROR 対策として IFERROR を使い、ユーザが見たときに「平均値なし」状態でも変にエラーが残らないように工夫します。式を入力したあと、値が変わるセルに 0 を入れたり消したりして動作確認をすることが大事です。

ステップ3：書式や表示の整え方

平均値を表示するセルについて、小数点以下、桁区切り、色などの書式を整えて見やすくします。特に「0 を除いた平均」が空白になっているときには、グレーなどで目立たせると誤解を防げます。また、セル範囲の名前定義を活用すると式が読みやすく保守性も高まります。

他の関数やツールを使った応用アイデア

エクセルには他にも強力な機能があります。ピボットテーブル、Power Query、マクロなどを併用すれば、大量データや複雑な条件でも0 を除いて離れたセルの平均を効率よく求められます。これらを使うことで「手作業で非連続セルをすべて式に書く」という負荷を減らせます。

ピボットテーブルで集計時に0除外

ピボットテーブルの集計軸に売上などを設定し、フィルターで 0 を除く条件を入れることで、0 を含まない平均を算出できます。元データにフラグを設けておけばフィルター設定が簡単になり、離れたセルという概念をデータテーブル全体で扱えます。

Power Query を使って予処理する方法

Power Query を使えばデータを読み込む段階で 0 の値を削除したり、対象セルを抽出しておくことができます。クエリの中で条件を設定して「0 を含まない」データだけを残すことで、後段の Excel 関数で離れたセルの平均を取る操作が非常にシンプルになります。

マクロ（VBA）で自動化する方法

定期的に同じような平均計算を繰り返す必要がある場合、VBA マクロで非連続セルを対象に 0 を除く平均計算を自動化できます。スクリプトで対象セルを列挙し、0 以外の値のみ集計する処理を記述するだけです。ボタンひとつで処理できるため、ミス防止にもなります。

比較表：各方法の特徴一覧

まとめ

離れたセル（非連続セル）でも 0 を平均から除外したい場合、まずは目的と Excel のバージョンを確認することが重要です。簡単に済ませたいなら、AVERAGEIF／AVERAGEIFS で「0」を使う方法がありますが、非連続セルを複数範囲で指定する場面では SUM／COUNTIF や配列数式を組み合わせる方法がより確実です。

最新の Excel では動的配列や FILTER 関数が使えるため、式がよりシンプルで柔軟になります。エラーの回避や見やすい表示にも配慮しながら、自分のデータ構造に合わせた方法を選んでください。こうすることで「エクセル 平均 0を除く 離れたセル」のニーズに正しく応えることができるはずです。