小計行と合計行を含む表で大活躍

Excelで数値を合計する関数といえば、多くの人がSUM（サム）関数を思い浮かべると思います。しかし「SUM関数を使うべきではない」ケースもあるのをご存じでしょうか？

例えば、以下の表を見てください。

左側の表には「小計」と「合計」の行があります。右側は、その表に入力された関数を表示した状態です。小計行と合計行には、SUM関数が入力されていることがわかります。

上記の表は、この状態では正しく集計されています。しかし、今後「製品」のデータを追加することになったら、どうでしょうか？ 合計の対象となる行が増えるため、SUM関数のセル参照を修正していく手間が生じます。うっかり修正し忘れるミスにも注意しなければなりません。

また、以下の表のように、何らかの原因で参照するセルにエラー値が表示されている場合、SUM関数では正しく小計・合計を求めることができません。

SUM関数ではエラー値が含まれるセルを参照した場合、正しく集計できません。

このような小計行と合計行を含む表では、SUM関数ではなくSUBTOTAL（サブトータル）関数、またはAGGREGATE（アグリゲート）関数を利用するのがおすすめです。どちらも「さまざまな集計値を求める」という機能を持ち、引数［集計方法］によって集計方法を切り替えられるという特徴があります。

本記事では「小計」と「合計」の行を含む表を効率よく、ミスなく計算できるようにしたい人のために、SUBTOTAL関数とAGGREGATE関数の使い方を解説します。

SUBTOTAL関数の構文

SUBTOTAL関数は、Excel 2007でも動作する対応バージョンの幅広い関数です。構文は以下の通りとなっています。

---

=SUBTOTAL（集計方法, 参照1, 参照2, ... ,参照254）

---

• ［集計方法］ ...... 集計方法を1～11の値で指定します。101～111の値は非表示の行を集計対象から除外します。
• ［参照］ ...... 集計する数値が入力されているセル範囲を指定します。

SUBTOTAL関数の機能は、1つめの引数［集計方法］の値によって切り替わります。例えば、数値を合計する場合は「9」を指定します。引数［集計方法］の値と対応する機能、同等の働きをする関数をまとめると、以下の表のようになります。

SUBTOTAL関数の集計方法

SUBTOTAL関数で集計する

先ほどの表の小計行と合計行の数式を、SUM関数からSUBTOTAL関数に書き換えてみましょう。合計を求めるので、引数［集計方法］は「9」です。2つめの引数［参照］には、合計する対象のセル範囲を指定します。

ここでポイントとなるのが、引数［参照］に指定したセル範囲にSUBTOTAL関数の数式が含まれている場合、自動的に集計から除外されるという点です。今後にデータを追加したとしても、合計するセル範囲を拡大するだけで済みます。数式を修正する手間が省けるとともに、ミスも減らせるメリットがあります

1SUBTOTAL関数を入力する

「=SUBTOTAL(」に続けて、引数［集計方法］に「9」と入力します。

2合計するセル範囲を指定する

合計するセル範囲（セルD2～D4）を指定し、「)」を入力して［Enter］キーを押します。セルD9、D13も同様にSUBTOTAL関数を入力しておきます。

3小計行を含むセル範囲を指定する

セルD14に「=SUBTOTAL(9,D2:D13)」と入力します。合計するセル範囲は「D2:D13」で構いません。セルD4、D9、D13に入力されたSUBTOTAL関数は無視されるため、セル範囲をまとめて指定できます。

4小計行を無視して合計できた

SUBTOTAL関数が入力された小計行を無視して、正しく合計を求めることができました。

AGGREGATE関数の構文

AGGREGATE関数は、Excel 2010以降のバージョンで利用できるSUBTOTAL関数の上位互換の関数です。構文は以下の通りとなります。

---

=AGGREGATE（集計方法, オプション, 参照1, 参照2, ... ,参照253）

---

• ［集計方法］ ...... 集計方法を1～19の値で指定します。
• ［オプション］ ...... 集計の対象を0～7の値で指定します。
• ［参照］ ...... 集計する数値が入力されているセル範囲を指定します。

SUBTOTAL関数と同じく、1つめの引数［集計方法］の値によって動作が切り替わります。数値の合計を求めるなら「9」です。引数［集計方法］の値と対応する機能、同等の働きをする関数をまとめると、以下の表のようになります。

AGGREGATE関数の集計方法

AGGREGATE関数では、2つめの引数［オプション］により、SUBTOTAL関数よりも幅広い集計が行えます。例えば「3」を指定すると、エラー値と非表示の行を無視することができるため、この値を指定することがほとんどでしょう。引数［オプション］に指定できる値と集計対象をまとめると、以下の表のようになります。

引数［オプション］の値と集計対象

AGGREGATE関数でエラー値を無視して集計する

今度は、先ほどの表の小計行と合計行の数式を、AGGREGATE関数で書き換えてみましょう。

AGGREGATE関数の2つめの引数［オプション］に「3」と指定することで、3つめの引数［参照］に指定したセル範囲にAGGREGATE関数やSUBTOTAL関数の数式が含まれていても、集計から除外されます。合計する場合は「=AGGREGATE(」に続けて、定型句として「9,3」と覚えてしまってもいいでしょう。

1AGGREGATE関数を入力する

「=AGGREGATE(」に続けて、引数［集計方法］に「9」と入力します。合計するセル範囲にエラー値が表示されていますが、AGGREGATE関数は無視できます。

2集計の対象を指定する

続けて「,3」と入力します。

3合計するセル範囲を指定する

合計するセル範囲（セルD2～D4）を指定し、「)」を入力して［Enter］キーを押します。

4エラー値を無視して合計できた

エラー値を無視して合計できました。セルD9、D13も同様にAGGREGATE関数を入力しておきます。

5小計行を含むセル範囲を指定する

セルD14に「=AGGREGATE(9,3,D2:D13)」と入力します。合計するセル範囲は「D2:D13」で構いません。セルD4、D9、D13に入力されたAGGREGATE関数は無視されるため、セル範囲をまとめて指定できます。

6小計行を無視して合計できた

AGGREGATE関数が入力された小計行とエラー値を無視して、正しく合計を求めることができました。

ちなみに、IFERROR関数などを利用してエラー値を無視することもできますが、数式が長くなります。AGGREGATE関数で処理したほうがスマートです。

SUM関数との併用は厳禁

SUBTOTAL関数とAGGREGATE関数を利用する場合、SUM関数との併用は厳禁だと覚えてください。その理由は、小計行を無視して集計できるのはSUBTOTAL関数やAGGREGATE関数が入力されているセルだけだからです。

例えば、以下のように数式を入力すると、誤った合計が求められてしまいます。小計行にSUM関数が入力されていると、正しく合計を求められないことに注意してください。

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

関連記事