【Excel】エラー値を含む範囲でSUMが#VALUE!になる!Excelの集計でエラーを無視する方法

ExcelでSUM関数を使って合計を計算する際、集計対象の範囲にエラー値が含まれていると、結果が#VALUE!エラーになってしまうことがあります。

これは、SUM関数がエラー値を数値として認識できないために発生する問題です。業務で集計を行う際にこのエラーに遭遇すると、原因の特定や解決に時間を取られ、作業が滞る原因となります。

この記事では、ExcelのSUM関数でエラー値を無視して数値だけを集計する方法を解説します。SUM関数で#VALUE!エラーが発生する原因と、その解決策となる関数や機能の使い方を具体的に説明します。

SUM関数が#VALUE!エラーを返す根本的な原因

ExcelのSUM関数は、指定された範囲内の数値を合計する関数です。しかし、この関数はエラー値を数値として認識できないため、集計対象のセルに#VALUE!、#DIV/0!、#N/Aなどのエラー値が1つでも含まれていると、SUM関数全体が#VALUE!エラーを返してしまいます。

これは、SUM関数が内部的にエラー値を処理できない仕様になっているためです。例えば、A1セルに100、B1セルに#N/Aエラー、C1セルに200が入っている場合、=SUM(A1:C1)と入力しても、B1セルの#N/Aエラーが原因で結果は#VALUE!となります。意図した合計値が得られないため、データ分析やレポート作成において支障をきたします。

エラー値を無視してSUM関数で合計を計算する手順

SUM関数で#VALUE!エラーが発生する場合、エラー値を無視して数値のみを集計するには、いくつかの方法があります。ここでは、代表的な3つの方法を解説します。

1. IFERROR関数とSUM関数を組み合わせる方法

IFERROR関数は、数式の結果がエラー値だった場合に、指定した値を返す関数です。この関数とSUM関数を組み合わせることで、エラー値を0に置き換えてから合計を計算できます。この方法は、Excel 2007以降で利用可能です。

1. 集計結果を表示したいセルを選択する
   合計を表示したい任意のセルを選択してください。
2. IFERROR関数とSUM関数を入力する
   選択したセルに以下の数式を入力します。
   =IFERROR(SUM(範囲), 0)
   「範囲」は合計したいセル範囲に置き換えてください。例えば、A1からA10の範囲を合計したい場合は、「A1:A10」と入力します。
   この数式は、まずSUM関数で範囲の合計を計算します。もしSUM関数がエラーを返した場合(範囲内にエラー値がある場合)、IFERROR関数がそのエラーを検知し、代わりに「0」を返します。
3. Enterキーを押して確定する
   数式を入力したら、Enterキーを押して計算結果を表示させてください。

この方法では、エラー値が含まれているセルは計算上0として扱われるため、SUM関数の結果が#VALUE!エラーになるのを防げます。ただし、エラー値そのものを無視するのではなく、エラー値を0として集計する点に注意が必要です。

2. SUMPRODUCT関数を使う方法

SUMPRODUCT関数は、配列内の対応する要素を乗算し、その合計を返す関数です。配列数式として機能するため、エラー値を無視して計算を実行できます。この方法は、Excelのほとんどのバージョンで利用可能です。

1. 集計結果を表示したいセルを選択する
   合計を表示したい任意のセルを選択してください。
2. SUMPRODUCT関数を入力する
   選択したセルに以下の数式を入力します。
   =SUMPRODUCT(–(ISERROR(範囲)=FALSE), 範囲)
   「範囲」は合計したいセル範囲に置き換えてください。例えば、A1からA10の範囲を合計したい場合は、「A1:A10」と入力します。
   この数式は、まずISERROR関数で範囲内の各セルがエラーかどうかを判定します(エラーならTRUE、数値ならFALSE)。次に「–( )」でTRUE/FALSEを1/0に変換します。最後に、エラーではない(0でない)要素のみを合計します。
3. Enterキーを押して確定する
   数式を入力したら、Enterキーを押して計算結果を表示させてください。

SUMPRODUCT関数は、配列を直接扱えるため、通常のSUM関数のようにCtrl+Shift+Enterで確定する必要がありません。エラー値を無視して、純粋に数値データのみを集計したい場合に有効な方法です。

3. AGGREGATE関数を使う方法

AGGREGATE関数は、Excel 2010以降で利用できる強力な関数です。この関数は、SUM、AVERAGE、COUNTなどの様々な集計機能に加え、エラー値の無視や非表示行の除外といったオプションを指定できます。

1. 集計結果を表示したいセルを選択する
   合計を表示したい任意のセルを選択してください。
2. AGGREGATE関数を入力する
   選択したセルに以下の数式を入力します。
   =AGGREGATE(9, 2, 範囲)
   「9」はSUM関数と同等の集計を指定する番号です。「2」はエラー値を無視するオプションです。「範囲」は合計したいセル範囲に置き換えてください。例えば、A1からA10の範囲を合計したい場合は、「A1:A10」と入力します。
3. Enterキーを押して確定する
   数式を入力したら、Enterキーを押して計算結果を表示させてください。

AGGREGATE関数は、SUM関数よりも柔軟な集計が可能です。オプションを指定することで、エラー値だけでなく、非表示になっている行や手動で非表示にした行のデータも計算から除外できます。エラー値の無視を明確に指定できるため、意図しない計算結果になるリスクを低減できます。

AGGREGATE関数でエラー値を無視するオプション

AGGREGATE関数は、集計方法や除外するデータの種類を指定するための引数を持っています。特に、エラー値を無視するために重要なのは、2番目の引数です。

AGGREGATE関数の構文は以下の通りです。

=AGGREGATE(関数番号, オプション, 参照1, [参照2], …)

ここで、2番目の引数「オプション」には、以下の数値が指定できます。

オプション番号	内容
0	無視しない
1	非表示の行
2	エラー値
3	非表示の行とエラー値
4	すべて無視
5	非表示の行のみ
6	エラー値のみ
7	すべて無視(非表示の行、エラー値)

SUM関数と同等の集計を行う場合、1番目の引数「関数番号」には「9」を指定します。エラー値のみを無視したい場合は、2番目の引数「オプション」に「2」を指定します。これにより、範囲内のエラー値を無視して、数値データのみを合計できます。

SUMPRODUCT関数とAGGREGATE関数の使い分け

SUMPRODUCT関数とAGGREGATE関数は、どちらもエラー値を無視して集計できますが、それぞれ特徴があります。どちらを使用するかは、Excelのバージョンや求める機能によって判断すると良いでしょう。

項目	SUMPRODUCT関数	AGGREGATE関数
Excelバージョン	ほとんどのバージョン	Excel 2010以降
エラー無視の指定	ISERROR関数と組み合わせる	オプションで指定(番号2)
その他の機能	配列計算(乗算など)	非表示行の除外、COUNT、AVERAGEなど複合的
数式入力	Ctrl+Shift+Enter不要	Ctrl+Shift+Enter不要

SUMPRODUCT関数は、古いバージョンでも使える汎用性の高さが魅力です。一方、AGGREGATE関数は、Excel 2010以降であれば、より直感的かつ多機能にエラー値や非表示行を扱えます。SUM関数以外にも様々な集計をしたい場合や、非表示行のデータを除外したい場合には、AGGREGATE関数が適しています。

SUM関数で#VALUE!エラーになる場合のよくある失敗パターン

SUM関数で#VALUE!エラーが発生する原因は、エラー値の混入だけではありません。よくある失敗パターンと、その対処法を解説します。

「数値」として入力されていないテキストデータ

セルに数字が入力されていても、実際には文字列として認識されている場合があります。例えば、セルの左上に緑色の三角マークが表示されている場合や、セルの書式設定が「文字列」になっている場合などが該当します。

対処法:

1. 緑色の三角マークの対処
   該当セルを選択し、表示される感嘆符(!マーク)をクリックして「エラーを無視する」を選択します。または、「数値に変換」を選択して数値データに統一します。
2. セルの書式設定の変更
   対象範囲を選択し、右クリックメニューから「セルの書式設定」を選び、「表示形式」タブで「標準」または「数値」を選択してOKをクリックします。その後、数式バーで値を再入力するか、F2キーを押してEnterキーを押して数式を再計算させる必要があります。
3. 「数値」列をコピーして「値」として貼り付け
   正常な数値データが入っている列をコピーし、エラー値が含まれる列に「値」として貼り付けることで、書式設定や文字列の誤りを修正できます。

計算結果として発生するエラー値

別の数式の結果として、#DIV/0!(0で除算)、#REF!(参照が無効)などのエラー値が発生している場合も、SUM関数は#VALUE!エラーを返します。

対処法:

1. エラーの原因となっている数式を特定・修正する
   エラー値が表示されているセルを選択し、数式バーで原因となっている数式を確認します。例えば、0で除算されている場合は、除数に0が入らないように条件分岐(IF関数など)を追加します。
2. AGGREGATE関数やIFERROR関数でエラーを処理する
   根本的な数式の修正が難しい場合は、エラーが発生しているセル自体にIFERROR関数を適用するか、SUM関数ではなくAGGREGATE関数を使用します。

数式内の参照セルが空欄またはエラー

SUM関数が参照しているセルが空欄の場合、SUM関数は0として扱いますが、その空欄セルが別の数式によってエラー値を返している場合、SUM関数もエラーになります。

対処法:

1. 参照先のセルを確認する
   SUM関数でエラーになる範囲内のセルを一つずつ確認し、空欄になっているセルや、エラー値を返しているセルがないか確認します。
2. 空欄セルを0にする、またはエラーを処理する
   空欄セルを0として扱いたい場合は、セルの書式設定で「空の文字列を表示する」のチェックを外すか、IF関数などで条件分岐させます。エラー値を返している場合は、前述の方法でエラーを処理します。

これらの失敗パターンを理解しておくことで、SUM関数で#VALUE!エラーが発生した際に、より迅速に原因を特定し、適切な対処を行うことができます。

まとめ

ExcelでSUM関数を使用する際に、集計範囲にエラー値が含まれていると#VALUE!エラーが発生してしまう問題に対し、IFERROR関数、SUMPRODUCT関数、AGGREGATE関数といった代替手段を用いて解決できることがわかりました。

IFERROR関数を使えばエラー値を0に置き換えて計算でき、SUMPRODUCT関数やAGGREGATE関数を使えばエラー値を無視して数値のみを集計できます。特にAGGREGATE関数は、Excel 2010以降であれば、エラー値の無視だけでなく非表示行の除外など、より柔軟な集計が可能です。

これらの関数を使い分けることで、データ集計時のエラーによる作業中断を防ぎ、より正確で効率的なデータ分析を実現できます。ぜひ、これらの方法を業務で活用してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】