SUM関数では全体の合計でミスが発生しがち

Excelで数値データを合計するときにおなじみの「SUM」関数。しかし、複数の小計が含まれる表でSUM関数を使うと、全体の合計を求めるために、いちいち数式を修正する手間が発生します。

SUM関数を使った売上一覧表の例を見てみましょう。7行目、12行目、17行目に部門ごとの小計を求めるSUM関数があり、18行目にそれぞれの小計を足す数式を入力することで、全体の合計を求めています。

SUM関数を使って集計している売上一覧表の例。セル右側の赤い文字は入力されている数式です。

これでも正しい集計が行えますが、4月以降のデータを追加するときには、合計を求める数式で足し合わせるセルを、間違えないように修正する必要があります。手間がかかりますし、うっかり修正し忘れるミスも起こりがちです。

こうした表での集計には、「SUBTOTAL」（サブトータル）関数を使いましょう。SUBTOTAL関数は、引数［集計方法］によってさまざまな計算が行える多機能な関数ですが、ここでは「合計」するときに便利な関数と割り切って話を進めます。

小計と合計をミスなく求められる

SUBTOTAL関数を使って、上記と同じ売上一覧表を作成しました。小計行と合計行のすべてにSUBTOTAL関数が入力されています。

SUBTOTAL関数を使った例。1つ目の引数「9」は、集計方法として「合計」を指定したという意味になります。

18行目に入力されているSUBTOTAL関数は、合計するセル範囲としてC3〜C17を参照していることがわかります。このセル範囲には小計行も含まれていますが、これらは自動的に集計から除外されるため、正しい合計が求められます。

4月以降のデータを追加するときにも、合計するセル範囲を拡大するだけで済みます。数式を修正する手間が省けるうえ、ミスも減るでしょう。

SUBTOTAL関数とSUM関数の併用はNG

ただし、1つの表内でSUBTOTAL関数とSUM関数を併用するのは厳禁です。SUBTOTAL関数で小計を除外して集計できるのは、SUBTOTAL関数が入力されているセルだけ。小計行にSUM関数が入力されていると、正しく集計されない点には注意が必要です。

小計にSUM関数、合計にSUBTOTAL関数を使った例。SUBTOTAL関数での集計に小計行の値まで含まれてしまい、誤った合計が求められています。