エクセルで0を除く平均の出し方！離れたセルを指定して正確に計算する

エクセルで「0を含めずに平均を出したい」「対象が離れたセルである」こうした悩みを抱えている方は多いでしょう。一般的なAVERAGE関数では0や空白、範囲以外のセルの影響で平均値が歪むことがあります。専門的なプロでもこれに対応する複数の方法を使い分けています。この記事では、最新情報を元に、0を除外しつつ離れたセルを指定して平均を算出する具体的な方法を、実践形式で丁寧に解説します。

エクセル 平均 0を除く 離れたセル を指定する方法

離れたセルを指定しながら、0を除いて平均を計算するには「複数のセル・範囲を列挙する」「条件を設定する」といった工夫が必要になります。ここではその基本的な考え方と構成要素をまず押さえます。

離れたセルを指定する構文の基本

エクセルで離れたセル（非連続セル）を扱うには、セル参照をカンマで区切って列挙します。例として「A1」「C3」「E5」など複数のセルを対象とする場合、AVERAGE関数内で「=AVERAGE(A1,C3,E5)」のように記述します。これにより、連続でないセルをまとめて処理することが可能です。

0を除く条件の設定の方法

0を含めない平均値を求めるには、AVERAGEIF関数を使って条件「0」（ゼロ以外）を指定する方法が一般的です。たとえば範囲がA1:A10なら「=AVERAGEIF(A1:A10,”0″)」。この式により、空白や文字列を無視しつつ、ゼロのセルも計算対象から外せます。

非連続セルと0除外を組み合わせるポイント

非連続セルかつ0を除外するには、単にAVERAGEとAVERAGEIFだけでは対応し切れないケースがあります。その場合は配列数式やSUM／COUNT／SUMPRODUCTなどを組み合わせて総和を求め、ゼロ以外のセル数で割る、という方式が選択肢に入ります。この構成により柔軟かつ正確に対応できます。

非連続セルを含むデータでの具体的な数式パターン

ここでは、実際に離れたセルを指定して0を除いた平均を出すための具体例をあげます。条件やエクセルのバージョンに応じて使い分けられます。

AVERAGE関数にセルを列挙して指定する例

たとえばセル A1、C3、E5 の３つだけを対象とし、ゼロを含めず平均を出したい時は次のような構文を使います。
=SUM(A1,C3,E5)/((A10)+(C30)+(E50))
この式では、SUMで総和を取り、各セルがゼロでないかを比較して TRUE/FALSE を数値化し、ゼロ以外セルの個数で割ることで平均値を正しく計算できます。

配列数式で非連続範囲と条件を指定する方法

Excelのバージョンにより、配列数式を利用できるものがあります。この場合、VSTACKやTOCOLといった関数で複数の離れたセルを一つの配列にまとめ、IF関数で0でないセルだけ取り出してAVERAGEで計算します。たとえば
=AVERAGE(IF(VSTACK(A1,C3,E5)0, VSTACK(A1,C3,E5)))
のような式です。最新型の Excel では Enter キーだけで動作することもあります。

AVERAGEIF や AVERAGEIFS を使って範囲をまとめる方法

AVERAGEIF や AVERAGEIFS は範囲内で条件に合うセルの平均を出す用途に適しており、範囲が離れていてもそれぞれに条件を設定できることがあります。たとえば A1:C1 と E1:G1 といった２つの離れた範囲で 0 を除く平均を取りたい場合、それぞれに AVERAGEIF を使い、結果を組み合わせる式を作ることが可能です。

最新の Excel 機能を活用した便利な方法

最新の Excel には、配列の扱いや動的な関数が進化しており、より簡潔で直感的に目的を達成できる方法が登場しています。

FILTER 関数を使って条件に合うデータを抽出する

FILTER 関数を使って「0 を含まない」データのみ抽出し、それを AVERAGE に渡す方法があります。例えば範囲 A1:A10 で 0 を除外するなら、
=AVERAGE(FILTER(A1:A10, A1:A10 0))
と記述します。FILTER が TRUE 条件のセルだけ配列で返し、AVERAGE がそれを平均計算するという構造です。

SUMPRODUCT 関数で非連続セルもまとめて扱う例

SUMPRODUCT を使うと離れたセルを利用しながらゼロ除外・個数カウントが簡単になります。複数のセルを足し、ゼロでないセルの数を計算して平均を出します。たとえば
=SUM(A1,C3,E5)/((A10)+(C30)+(E50))
の形式が該当し、非連続セルを対象にしつつ 0 を除外できます。

最新の Excel バージョンでの動的配列と VSTACK/TOCOL の活用

新しい Excel では VSTACK や TOCOL といった動的配列関数が利用可能で、離れたセルをまとめて一つの列または行に変換できます。そして IF 条件で 0 を取り除いて AVERAGE を使えば、従来よりも簡潔な式で完結します。例として
=AVERAGE(IF(TOCOL((A1,C3,E5),1)0, TOCOL((A1,C3,E5),1)))
のような記述です。

トラブル対策と注意点

これらの方法を使う際にありがちなつまずきや誤用を未然に防ぐための注意点を解説します。精度が落ちたりエラーになる原因を理解しておけばスムーズに使えます。

#DIV/0! エラーの原因と対処法

平均を取る対象にゼロ以外のデータが一つもない場合、除算対象がゼロになり #DIV/0! エラーになります。IF や IFERROR を使い、対象セルに非ゼロデータがあるか COUNTIF 等で確認する処理を加えると防げます。例えば
=IF(COUNTIF(A1:C1,"0")=0,"",SUM(A1,C3,E5)/((A10)+(C30)+(E50)))
のように記述します。

混在データ：空白・文字列・エラー値の存在

平均を計算する際、0以外でも空白や文字列、あるいはエラー値が混在していると想定以上の結果になることがあります。AVERAGE や AVERAGEIF は空白を無視することが多いですが、文字列やエラーは扱いに注意が必要です。IFERROR 等でエラーを空白または無視できるようにする工夫が有効です。

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

Excel のバージョンによっては動的配列関数や VSTACK・TOCOL が使えない場合があります。これらが使えない場合は、従来型の配列数式（Ctrl＋Shift＋Enter）や SUMPRODUCT を活用する方法に戻す必要があります。対象の環境を確認してから式を選んでください。

具体例で比較して使い分けよう

ここでは異なる状況でどの方法が適しているかを比較します。離れたセルの数やデータの分布、Excel のバージョンを基準に判断できます。

よくある質問（FAQ）

このような疑問を持つ方が少なくないため、事前に回答を用意します。

AVERAGEIF で非連続セルを直接指定できないのはなぜか

AVERAGEIF 関数は基本的に「一つの連続した範囲」を対象にするため、非連続セルをカンマで列挙してそのまま指定するとエラーや誤動作の原因になります。非連続セルを扱うにはセルを列挙して SUM とカウントを組み合わせるか、最新機能で配列化して処理する方法が必要です。

SUMPRODUCT を使った式の理解ポイント

SUMPRODUCT を使う場合、セルの論理値 TRUE／FALSE を数値 1／0 に変換して条件を表現します。例えば (A10) はゼロ以外なら TRUE＝1 を返します。SUM で合計を取り、これを条件件数で割ることで平均が出せます。この論理的な処理が理解できると式のカスタマイズがしやすくなります。

動的配列を使うときに注意すべきメモリや処理速度

VSTACK や TOCOL といった動的配列関数は非常に便利ですが、対象セル数が膨大だったり複雑な条件が重なると計算が重くなることがあります。必要以上に大きな参照範囲を指定しない、不要な配列処理を避けるなどの工夫をすると操作がスムーズになります。

実践ワークフロー：ステップバイステップで作る平均算出式

離れたセルを指定し 0 を除いて平均を出す作業を、実際の操作手順として追っていきます。このワークフローを参考に自分のデータに応用してみてください。

ステップ1：対象セルを把握する

まず平均を出したい非連続セルを選定します。例として A1、C3、E5 の三つ。どのセルが「0」「空白」「数値」かを一度確認しておきます。これによって後で条件式を作る手間が減ります。

ステップ2：バージョンと機能の確認

Excel のバージョンを確認し、FILTER、VSTACK、TOCOL、動的配列、配列数式（Ctrl＋Shift＋Enter）等の機能が使えるかどうかを確かめます。最新のExcelならば動的配列が使える場合が多いため、より簡潔な構文が可能です。

ステップ3：数式を作成する

バージョンに応じて次の式のいずれかを作成します：

• SUM + 条件カウント方式：=SUM(A1,C3,E5)/((A10)+(C30)+(E50))
• FILTER と AVERAGE の組み合わせ：=AVERAGE(FILTER(A1:C10, A1:C100))（複数範囲を配列化する場合）
• 配列数式方式：=AVERAGE(IF(VSTACK(A1,C3,E5)0, VSTACK(A1,C3,E5)))

ステップ4：エラー処理と検証する

式を入力したら実際に結果が意図通りか確認します。特にすべての対象セルが 0 または空白のときに #DIV/0! が出るかどうかを試し、IF や IFERROR で代替値（空白あるいは 0）を表示するように補うと良いです。

まとめ

離れたセルを指定して 0 を除外して平均を出すには、まず対象セルを明確にし、Excel のバージョンで使える関数を確認することが重要です。一般的には SUM と論理式（0）を組み合わせた方式、または FILTER／VSTACK／TOCOL といった動的配列を使う方式が便利で強力です。AVERAGEIF や AVERAGEIFS は範囲指定が主要用途ですが、非連続セルを扱う際には他の方式と組み合わせて使うことになります。

この記事で紹介した方法を使えば、0 値による影響を排除した正確な平均が得られます。自分のデータの構成や使用環境に応じて最適な式を選び、平均値の信頼性を高めてください。