エクセルで平均から0を除く！離れたセルを指定してAVERAGEIFSで計算する

たくさんのセルを扱うシートで、「0」が混ざって平均値が下がってしまうのを避けたいと感じたことはありませんか。「離れたセル」から値を取り出して、「0」を自動で除外して平均を出したいというニーズに応える方法を、手順や応用例を含めてくわしく解説します。基本関数から応用数式、最新機能まで含む内容ですので、エクセルでの作業効率がぐっと向上します。

エクセル 平均 0を除く 離れたセル をAVERAGEIFSで実現する方法

この見出しでは、「エクセル 平均 0を除く 離れたセル」の全単語を使って、離れた複数のセルを対象にゼロを除外してAVERAGEIFS関数で平均を計算する基本的な方法を紹介します。AVERAGEIFSを使って条件を設定する基本原則と、離れたセルという状況にどう対応できるかを理解できるようになります。

AVERAGEIFSの基本構成と0を除く条件の指定

AVERAGEIFS関数は、指定した範囲に対して複数の条件を設定して平均を求める関数です。通常、平均対象範囲（average_range）と、条件となる範囲（criteria_range）およびその基準（criteria）を指定します。その中で「0を除く」には、criteriaとして"<>0" を用います。これにより対象となるセルの中で値が0でないものだけが計算対象になります。

離れたセルをAVERAGEIFSで扱うときの注意点

AVERAGEIFS関数は、average_rangeとcriteria_rangeの範囲は同じサイズ・形状である必要があります。つまり離れた複数のセルを単体で指定する方法は制限されます。非連続のセルを扱う場合は、まずそれらを含む連続範囲を選び、その中で条件を指定して処理するか、または複数の範囲を合成する工夫が必要です。

複数の範囲を合成して離れたセルを対象にする方法

離れたセル群を対象にするには、以下のようなアプローチがあります。SUMとCOUNTIFを組み合わせて手動で平均を計算するか、LET関数やIF関数を使って間のセルを無視する数式を作る方法です。例として、SUMで非ゼロセルの合計を出し、COUNTIFで非ゼロセルの数を出して、それを割るという式を使います。これによりAVERAGEIFSが直接扱えない離れたセルでも平均が取れます。

AVERAGEIFSを使って非連続セルの平均を求める応用例

ここでは具体的な応用ケースを取り上げ、離れたセルから0を除いた平均値をどう計算するかをステップごとに示します。実務で使いやすい形式なので、実際に設定する際の参考になります。

例1：数個の非連続セルを手動で指定する

例えばセルA1、C1、E1の3つを対象に、「0でないセルだけ平均を求めたい」という場合には、次のような数式が使えます。SUM関数でこれらのセルの合計を出し、条件演算子を使って0でないもののみを数えて割ります。
式例：
SUM(A1,C1,E1)/((A10)+(C10)+(E10))
こうすることで、0のセルを除いて正確な平均が得られます。この方法は対象セルが少数で、セル参照の入力が可能なときに便利です。

例2：一定の間隔で離れたセルを対象にする配列数式

データが規則的に離れており、例えば毎列ごとにチェックするパターンがある場合には、配列数式を使うと便利です。MOD関数とCOLUMNまたはROW関数を用いて、「列番号あるいは行番号の余りが特定値」のセルだけを対象にし、さらに0除外の条件を掛け合わせます。
式例：
AVERAGE(IF(MOD(COLUMN(A1:G1)-COLUMN(A1),2)=0,IF(A1:G10,A1:G1)))
このように入力し、最新のExcelでは通常のEnterで計算でき、古いバージョンではCtrl+Shift+Enter要です。

例3：AVERAGEIFSで複数基準＋0除外を組み合わせる実践例

AVERAGEIFSを使って「離れたセル」が属する範囲を条件でフィルターしながら、0も除外する方法があります。具体的には、平均対象範囲と、その範囲と同じ範囲に0でないという条件を加えることです。
例えば、列D2:D100が数値、列C2:C100がカテゴリを持ち、特定のカテゴリのみ、かつ数値が0でないデータの平均を求めたい場合：
AVERAGEIFS(D2:D100, C2:C100, “カテゴリA”, D2:D100, “0”)
このように複数条件で対象を絞りつつ、0を除外できます。

非連続セル選択とAVERAGEIFSの限界と代替案

離れたセル（非連続範囲）をAVERAGEIFSだけで扱うのは制約が多いため、場合によっては代替の式や手段を用いる方が便利です。ここでは主な限界点と、それに対する最新の対応策を紹介します。

AVERAGEIFSで非連続セルを直接扱えない制約

AVERAGEIFSはaverage_rangeとcriteria_rangeが連続したセル範囲であることを前提としています。そのため、直接複数の異なる場所にあるセルを一つずつ指定することはできません。そうした指定をするとエラーになったり正しく計算されないことがあります。複数範囲にまたがるケースは、通常SUM・COUNTIF・IFなどを組み合わせた式か、範囲を含む連続セルを使って操作します。

LET関数やFILTER関数を使った動的範囲の取得

Excelの最新バージョンでは、LET関数やFILTER関数などが使えるため、より柔軟に離れたセル・動的な条件での平均計算が可能です。例えばFILTERで「セルが非ゼロかつ指定するカテゴリに属するもの」を抽出し、それをAVERAGEで計算する方法です。またLETを使って中間の範囲を変数とし、整理された数式を書くことができます。

IFERRORとエラーハンドリングの工夫

対象となるセルすべてが0であったり、条件を満たすデータが存在しない場合にはAVERAGEやAVERAGEIFSは#DIV/0というエラーを返します。このとき、IFERRORを使って空白や特定の表示を返したり、条件で事前チェックしてから平均を出すようにすることで表の見た目や運用の品質を高めることができます。

実務で使える具体シナリオとパターン集

ここでは、実務でよく出てくるパターンを取り上げ、離れたセルから0を除いて平均を取る典型例と、それぞれに最適な数式を提案します。複数パターンを使い分けることで、どの現場でも応用がききます。

パターン1：売上データの月別平均で0は欠損データとみなしたい場合

売上が入力されてない月が「0」となってしまい、平均が低く出るのを避けたいケースがあります。この場合、月ごとのセルが連続であればAVERAGEIFで範囲指定＋<>0が使えます。月データが離れた場所に散らばっていればSUM／COUNTIF／IFで非連続のセル群を処理するか、FILTER関数で条件付きで抽出してAVERAGEを使う方法が便利です。

パターン2：カテゴリ別・部門別など複数条件＋0除外

部門別の数字が複数列に分かれていたりカテゴリ別に散在しているケースでは、AVERAGEIFSを使ってカテゴリ条件と数値非ゼロ条件を組み合わせます。たとえば、部門列が「営業」、数値列が「売上」の場合：AVERAGEIFS(売上範囲, 部門範囲, “営業”, 売上範囲, “0”) という形式で使うことで、営業部門の売上データのうち0でないものだけを平均できます。

パターン3：レポートやダッシュボードで動的に非連続セルを選ぶ場合

報告書作成時にユーザーが「任意の離れたセルを選んで平均を出したい」という要求がある場合、VBAマクロやユーザー定義関数を使うのが効果的です。選択セルを取得して、値が数値かつ0でないものを集めて平均を返す関数などを設定しておくと、自由度が高くなります。操作の繰り返し性も高めません。

比較表：各手法の長所と短所

離れたセル・0除外の平均を出す手法を比較しやすく、選択しやすくするために、代表的な方法を表にまとめます。どの方法が状況に適しているかが一目でわかります。

数式エラー対策とパフォーマンスを上げるポイント

正しい数式を作っても、運用していく中でエラーが出たり動作が遅くなったりすることがあります。そのようなトラブルを予防・軽減するためのコツを紹介します。

DIV/0エラーを避ける

条件を満たすセルがすべて0であったり存在しない場合、AVERAGE・AVERAGEIFSは#DIV/0!エラーを返します。これを防ぐにはIF関数やIFERROR関数を使って、事前にCOUNTIFやSUMPRODUCTで対象セル数をチェックし、0でないかを確認する仕組みを入れることが重要です。空白を返す、または説明文を返すなど見せ方を工夫するとよいです。

計算速度とスプレッドシートの軽量化

SUM・COUNTIF・IF・配列数式・FILTERなどが大量セルを対象にすると、計算が遅くなることがあります。不要な範囲を指定しない、動的範囲をできるだけ限定する、必要のない配列数式を避けるなどの工夫が役立ちます。可能ならデータモデルやピボットテーブルを使うことも検討して下さい。

Excelバージョンによる関数対応の違い

Excelのバージョンによって、FILTERやLETが使えないものがあります。また、古いバージョンでは配列数式の入力時にCtrl＋Shift＋Enterが必要です。使用環境を確認して、互換性のある方法を採用することが長く使う上で重要です。

まとめ

離れたセルから0を除いて平均を出すニーズには、AVERAGEIFSを中心とした複数の手法があり、それぞれにメリット・デメリットがあります。対象セルが連続しておりカテゴリ条件なども明確ならAVERAGEIFS＋<>0が最もシンプルです。非連続セルが多い場合にはSUM／COUNTIF／IFまたは動的抽出系の機能を使うと柔軟性が高まります。マクロを使うのも選択肢のひとつです。

また、エラー対策、Excelのバージョン互換性、計算速度などを意識することで、実践的で信頼性のあるシートを作成できます。ぜひこの記事で学んだ方法を試して、あなたのExcel作業をスムーズにしてください。