PR

エクセル(Excel)SUMIF関数でエラーを除いて合計する方法

この記事は約5分で読めます。
【PR】

Excelで日常的に集計作業をしていると、

エラー値が含まれていてSUM関数で合計できない!

という経験はありませんか?

特に#DIV/0!や#VALUE!といったエラーが合計したい範囲に存在していると、SUM関数ではうまく合計できず、資料作成にも支障をきたすことがあります。

安心してください!

この記事では、SUMIF関数を使ってエラー値を除外して合計する方法を解説します。

さらに、条件付き集計や他の関数による対応方法も紹介しますので、実務で使える力がしっかり身につきますよ。

この記事のゴール

  • エラーを含むデータでも正しく合計できるようになる
  • 集計作業でのエラーに強くなり、報告資料の信頼性が向上する
【PR】

エラー値を除いて集計する方法

SUMIF関数の基本の使い方(構文)

SUMIF関数は、「ある条件に合うデータだけを合計したい」時に便利な関数です。
これを活用すれば、エラーを含まないセルだけを指定して合計することが可能になります。

=SUMIF(範囲, 条件, 合計範囲)

SUMIF関数

=SUMIF(範囲, 条件, 合計範囲)

範囲  :条件をチェックするセル範囲を指定します。
検索条件どのようなセルを合計の対象とするかを指定します。
数値、文字列、または数式で指定できます。
合計範囲合計したいセルの範囲を指定します。
※範囲と同じ場所を指定する場合は省略可

エラー値を除く方法(実例)

例えば、以下のようなデータがあるとします。

C5セルの販売数量に単位(個)を入れてしまったので、
合計値がエラーになっていますね。

特定のエラーを除いた合計を出したい場合は、以下のような工夫が必要です。

=SUMIF(D2:D7,”<>#VALUE!”)

数式の解説

#VALUE!」のエラーが出ているので、このエラーを除く(”<>”)値を合計します。

また、チェック範囲と合計範囲が同一のため、合計範囲を省略しています。

ただし、この方法では1つの条件に当てはまるデータしか除外できません。

実務では様々なエラーが混在するため、後述するISERROR関数やIF関数との組み合わせがより実用的です。

応用:一定販売数量以上のデータを合計

たとえば、販売するようが20個以上のデータだけを合計したい場合は以下の通りです。

=SUMIF(C2:C7,”>20″,D2:D7)

数式の解説

C列の販売数量が20以上の行(”>20″)値を合計します。

チェック範囲と合計範囲が異なるため、それぞれの範囲を設定しています。

条件指定を工夫することで、柔軟な集計ができますね

SUMIF関数以外の関数でエラーを回避する

エラーの回避は、SUMIFだけでは難しいケースもあります。

たとえば、エラーかどうかの判定が複雑だったり、合計範囲と条件範囲が一致しない場合などです。

その場合は、IFERRORやISERROR、AGGREGATE関数などの出番です。

IFERROR関数を使う

IFERROR関数を使えば、エラーを任意の値(たとえば0)に置き換えて合計することができます。
数式がエラーにならない場合は、数式の結果がそのまま表示されます。

IFERROR関数

=IFERROR(, エラーの場合の値)

       :条件をチェックするセル範囲を指定します。
エラーの場合の値エラーの場合に変換する値を指定します。
対応するエラー: #DIV/0!#N/A#VALUE!#REF!#NAME?#NUM!など

IFERROR関数を使用して合計する場合は以下の数式になります。

=SUM(IFERROR(D2:D7,0))

この数式は「配列数式」のため、Excel2019以前のバージョンで用いる場合はCSE入力(Ctrl+Shift+Enterで数式を確定)する必要があります。

AGGREGATE関数を使う

AGGREGATE関数は、複数の集計関数(SUM、AVERAGE、MAXなど)を様々なオプション(エラー値の無視、非表示行の無視など)と共に使用できる、非常に柔軟性の高い関数です。

AGGREGATE関数

=AGGREGATE(集計方法, オプション, 配列)

集計方法  :実行する集計方法を指定する番号を指定します。
例:9=SUM、1=AVERAGE、4=MAXなど
オプションエラー値や非表示セルを無視するかどうかを指定します。
例:6=エラー値を無視、1=非表示の行を無視、
  7=エラー値と非表示の行の両方を無視、など
配列計算したいセルの範囲を指定します。

AGGREGATE関数を使用して合計する場合は以下の数式になります。

=AGGREGATE(9,6,D2:D7)

数式の解説

9は「合計」を意味する関数番号
6は「エラー値を無視する」オプションです

まとめ

エラー値が原因で合計がうまく出せない…。という悩みも、関数を正しく使えば解決できます。今回紹介した方法を活用すれば、エラーがある場合でも落ち着いて対処できるはすです。

ぜひ実務でも試してみてくださいね。

タイトルとURLをコピーしました