【Excel】SUM関数で特定の行だけ除外したい!Excelの条件付き合計とSUMIF使い分け

Excelで集計を行う際、SUM関数は非常に便利です。しかし、特定の行を除外して合計したい場面に遭遇することがあります。例えば、合計値から除外すべき「合計行」や「見出し行」が存在する場合などが該当します。SUM関数だけではこれらの行を直接除外して集計することはできません。この記事では、SUM関数で特定の行を除外して合計する方法として、Excelの条件付き合計機能とSUMIF関数を使い分ける方法を解説します。これにより、意図した範囲のみを正確に集計できるようになります。

SUM関数は指定したセル範囲の数値をすべて合計する基本的な関数です。しかし、集計対象のデータに「合計」や「小計」といった特定の行が含まれている場合、それらの行まで合計に含めてしまうと、本来の意図とは異なる結果になってしまいます。このような状況で、特定の条件に合致する行を除外して合計を計算するには、SUM関数の使い方を工夫するか、SUMIF関数のような条件付き集計関数を利用する必要があります。どちらの方法が適しているかは、除外したい行の条件やデータの状況によって異なります。

SUM関数で除外したい行がある場合の根本原因

SUM関数で特定の行を除外できない主な原因は、SUM関数が単純な数値の合計機能に特化しているためです。SUM関数は、引数として与えられたセル範囲に含まれるすべての数値を対象とします。そのため、集計対象のデータ範囲内に、合計から除外したい「合計行」や「見出し行」が含まれている場合、それらの行の数値もそのまま合計に加算されてしまいます。SUM関数自体には、特定の条件に基づいて集計対象を絞り込む機能は備わっていません。

この問題を解決するには、SUM関数が対象とするセル範囲を、除外したい行を含まないように指定する必要があります。しかし、データ範囲が動的に変化する場合や、除外したい行の条件が複雑な場合には、単純なセル範囲の指定だけでは対応が難しくなります。そのような状況では、後述する条件付き合計機能やSUMIF関数のような、より高度な集計機能の利用が不可欠となります。

特定の行を除外して合計する操作手順

特定の行を除外して合計を計算するには、主に2つの方法があります。1つはSUM関数とIF関数を組み合わせた配列数式を使う方法、もう1つはSUMIF関数を利用する方法です。どちらの方法も、除外したい行の条件を指定して、それ以外の行の数値を合計します。

1. SUM関数とIF関数を組み合わせた配列数式による除外
   この方法は、SUM関数とIF関数を組み合わせて使用し、特定の条件を満たすセルのみを合計します。除外したい行に特定の文字列(例:「合計」)が含まれている場合に有効です。配列数式として入力する必要があるため、入力後にCtrl + Shift + Enterキーで確定します。(Excel 2019以前のバージョンでは配列数式として確定する必要があります。Microsoft 365では動的配列機能によりEnterキーのみで確定できる場合もあります。)
   数式例:=SUM(IF(A1:A10<>“合計”,B1:B10,0))
   この数式は、A1からA10の範囲に「合計」という文字列が含まれていない場合に、B1からB10の範囲の対応するセルの値を合計します。
2. SUMIF関数による除外
   SUMIF関数は、指定した条件に合致するセルの値を合計する関数です。この関数を利用して、合計から除外したい行の条件を指定し、それ以外の行の値を合計します。SUMIF関数は、単純な条件での合計に非常に適しています。
   数式例:=SUMIF(A1:A10,”<>合計”,B1:B10)
   この数式は、A1からA10の範囲に「合計」という文字列が含まれていない場合に、B1からB10の範囲の対応するセルの値を合計します。SUMIF関数は、SUM関数とIF関数を組み合わせた配列数式よりも簡潔に記述できるため、多くの場合で推奨されます。

SUMIF関数と条件付き合計機能の使い分け

Excelで特定の条件に基づいて合計を計算する場合、SUMIF関数と条件付き合計機能(テーブル機能やSUBTOTAL関数など)がよく利用されます。どちらを使用するかは、データの構造や集計の目的に応じて選択します。

SUMIF関数で合計を計算する場合

SUMIF関数は、単一の条件に基づいてセル範囲の数値を合計する際に最も適しています。例えば、特定のカテゴリに属する商品の売上合計を計算したい場合や、特定の月の日付を除外して集計したい場合などに役立ちます。

SUMIF関数の基本的な構文

SUMIF(範囲, 検索条件, [合計範囲])

この構文では、「範囲」は条件をチェックするセル範囲、「検索条件」は合計するセルを決定する条件、「合計範囲」は実際に合計される値が含まれるセル範囲です。合計範囲は省略可能で、省略された場合は範囲と同じセル範囲が使用されます。

SUMIF関数で特定の行を除外する具体例

例えば、A列に品目名、B列に数量が入っており、A列に「合計」と記載された行を除外してB列の数量を合計したい場合、以下の数式を使用します。

=SUMIF(A1:A10,”<>合計”,B1:B10)

この数式は、A1からA10の範囲で「合計」以外の値を持つ行に対応するB1からB10の値を合計します。このように、SUMIF関数は除外したい条件を「<>(～でない)」で指定することで、特定の行を除外した集計を容易に行えます。

条件付き合計機能(SUBTOTAL関数)で合計を計算する場合

条件付き合計機能として、SUBTOTAL関数は非常に強力です。SUBTOTAL関数は、指定した条件に基づいて、範囲内の数値を合計、平均、個数などを計算できます。その最大の特徴は、フィルター機能と連携して、表示されているデータのみを対象に計算できる点です。これにより、非表示の行やフィルターで除外された行は計算対象から自動的に除外されます。

SUBTOTAL関数の基本的な構文

SUBTOTAL(関数番号, 参照1, [参照2], …)

「関数番号」は、実行したい計算の種類を指定する数値です。例えば、合計を計算する場合は「9」、平均を計算する場合は「1」を指定します。重要なのは、関数番号の「1～11」はフィルターで非表示になっている行を計算に含めるのに対し、「101～111」は非表示になっている行を計算から除外する点です。SUM関数のように、フィルターで除外したい行(例:合計行)がある場合、SUBTOTAL関数で「109」(合計、非表示行は除外)を指定することで、表示されているデータのみを合計できます。

SUBTOTAL関数で特定の行を除外する具体例

例えば、A列に品目名、B列に数量が入っており、フィルター機能を使って特定の品目のみを表示させた上で、表示されている数量の合計を計算したい場合、以下の数式を使用します。

=SUBTOTAL(109,B1:B10)

この数式は、B1からB10の範囲で、現在フィルターによって表示されているセルのみを合計します。もし、A列の「合計」行をフィルターで非表示にした場合、このSUBTOTAL関数は「合計」行を除いた値のみを計算します。SUMIF関数と異なり、SUBTOTAL関数はフィルター機能との連携が前提となるため、動的に表示・非表示を切り替えながら集計したい場合に非常に有効です。

SUMIF関数とSUBTOTAL関数の使い分け

SUMIF関数は、特定の「条件」を満たすセルを対象に集計したい場合に適しています。条件が固定されており、フィルター操作を行わない場合や、数式内で直接条件を指定したい場合に便利です。例えば、「東京」の売上だけを合計したい、といったケースです。

SUBTOTAL関数は、フィルター機能と組み合わせて、現在「表示されている」セルを対象に集計したい場合に最適です。データの表示・非表示を切り替えながら、その都度集計結果を更新したい場合に強力な機能を発揮します。例えば、商品カテゴリでフィルターをかけ、その都度表示されている商品の合計数量を把握したい、といったケースです。

どちらの関数も、SUM関数だけでは実現できない「条件付きの合計」を可能にしますが、そのアプローチと得意とする場面が異なります。データの状況や、どのような操作で集計結果を更新したいかに応じて、適切な関数を選択することが重要です。

SUMIF関数でよくある誤解と対処法

SUMIF関数は非常に便利ですが、いくつかの誤解や注意点があります。これらを理解しておくことで、より効果的にSUMIF関数を活用できます。

検索条件にワイルドカードを使わない場合

SUMIF関数の検索条件では、ワイルドカード文字(*、?、~)を使用できます。しかし、これらのワイルドカードを意図せず使用してしまうと、期待通りの結果が得られないことがあります。例えば、検索条件を「りんご」とした場合、「ふじりんご」や「りんごジュース」といったセルも条件に合致してしまいます。これは、SUMIF関数が「りんご」という文字列を部分的に含むセルも検索条件に合致すると解釈するためです。

対処法

正確に一致する文字列のみを対象としたい場合は、検索条件を完全一致にする必要があります。例えば、A列の品目名が「りんご」と完全に一致する場合のみ合計したい場合は、検索条件を「”りんご”」のようにダブルクォーテーションで囲みます。また、「りんご」という文字列を部分的に含むものを除外したい場合は、SUMIF関数で「<>りんご」のように指定し、さらに「りんご*」や「*りんご」といった条件で別途集計して、それらを差し引くといった複雑な処理が必要になることもあります。多くの場合、SUMIFS関数(複数条件)や、より高度な条件設定が可能なPower Queryの利用が推奨されます。

合計範囲と検索範囲のサイズが異なる場合

SUMIF関数では、検索条件をチェックする「範囲」と、実際に合計する「合計範囲」のサイズ(行数または列数)が一致している必要があります。もし、これらのサイズが異なると、Excelはエラーを返したり、意図しないセルを合計したりする可能性があります。

対処法

SUMIF関数を入力する際は、必ず検索範囲と合計範囲のセル数が同じになるように指定します。例えば、A1からA10までを検索範囲とする場合、合計範囲もB1からB10までのように、同じ行数・列数になるように指定してください。数式を入力する際に、Excelの自動補完機能やマウスでの範囲選択を正確に行うことが重要です。もし、データ範囲が変動する場合は、Excelテーブル機能を利用するか、OFFSET関数やINDIRECT関数を組み合わせた動的な範囲指定を検討する必要がありますが、これは数式の複雑さを増します。

集計対象のデータに空白セルやエラー値が含まれる場合

SUMIF関数は、検索条件に合致するセルの値を合計しますが、合計範囲に空白セルや数値以外のデータ(文字列、エラー値)が含まれている場合、それらは無視されます。これはSUM関数と同様の挙動ですが、意図しない結果になることがあります。

対処法

合計範囲に空白セルやエラー値が含まれている場合、それらが集計結果に影響を与えないか確認してください。もし、空白セルを「0」として扱いたい場合は、SUMIF関数とIF関数を組み合わせた配列数式で、空白セルを「0」に置き換えてから合計するなどの工夫が必要です。エラー値が含まれている場合は、SUMIF関数でエラー値を無視するのではなく、SUMIFS関数でエラー値でないことを条件に追加するか、事前にエラー値を修正しておく必要があります。例えば、IFERROR関数を使ってエラー値を「0」に置き換えておくなどの前処理が有効です。

SUM関数とSUMIF関数、SUBTOTAL関数の比較

Excelで数値を合計する際に利用される代表的な関数であるSUM関数、SUMIF関数、SUBTOTAL関数は、それぞれ異なる特徴と用途を持っています。以下に、それらの違いを比較表にまとめます。

項目	SUM関数	SUMIF関数	SUBTOTAL関数
基本的な機能	指定したセル範囲の数値をすべて合計する	単一の条件に合致するセル範囲の数値を合計する	指定した関数番号に基づき、セル範囲の数値を計算する(合計、平均、個数など)
条件指定	なし	あり(単一条件)	あり(関数番号による計算種類指定、フィルター連動)
フィルターとの連携	非表示の行も計算対象に含める	非表示の行も計算対象に含める	関数番号「101～111」を使用すると、非表示の行を除外できる
除外したい行がある場合	手動で範囲指定を調整するか、配列数式などが必要	「<>条件」で除外条件を指定できる	フィルターで対象行を非表示にして、関数番号「109」などで合計できる
得意な場面	単純な数値の合計	特定の条件を満たすデータの集計	フィルターと連動した動的な集計、複数の計算種類の実行
複数条件	なし	不可(SUMIFS関数を使用)	不可(フィルターで複数条件を設定する必要がある)

まとめ

SUM関数で特定の行を除外して合計したい場合、SUMIF関数やSUBTOTAL関数を使い分けることで、柔軟な集計が可能になります。SUMIF関数は、特定の条件に基づいて除外したい場合に有効であり、「<>」演算子を用いて除外条件を指定することで、意図した範囲のみを正確に合計できます。一方、SUBTOTAL関数は、フィルター機能と連携し、表示されているデータのみを対象に計算したい場合に強力なツールとなります。

この記事で解説したSUMIF関数の「<>」演算子による除外、およびSUBTOTAL関数とフィルターの組み合わせを実践することで、合計行や不要な行を除外した正確な集計が実現できるようになります。次に、実際のデータでこれらの関数を試してみて、ご自身の業務に合った方法を見つけてください。さらに高度な条件設定が必要な場合は、SUMIFS関数やPower Queryの利用も検討すると良いでしょう。

📊

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

🏆

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