SUBTOTALは範囲内にSUBTOTALがあるときに無視する

このサイトではSUBTOTAL関数について何度かご紹介しています。

Excelの講習会でSUBTOTAL関数についてご案内すると、
「SUBTOTAL関数があればSUM関数とかCOUNT関数とかはいらないんじゃないですか？」
といったご質問をいただくことがあります。

SUBTOTAL関数を単体で使っている場合に、このような疑問になるのも頷けます。しかしそんなことはありません。SUBTOTAL関数があるからといって、SUM関数やCOUNT関数が不要にはなりません。

SUBTOTAL関数はSUM関数やCOUNT関数の機能をあわせ持った関数、というわけではないのです。

SUBTOTAL関数の特長的な部分は、組み合わせて使うところにあります。

▼サンプルファイル（003580.xls 47KByte）ダウンロード

サンプルファイルの「SUBTOTAL関数 OK」シートがSUBTOTAL関数の特長がよく見える使い方です。

複数の店舗を持っている会社の売上管理表のようなものだとイメージしてください。

B2:B4セルには関東地区にある各店舗の1月の売上データ、B6:B7セルには関西地区にある各店舗の1月の売上データが入力されています。

B5セルには関東地区の売上を合計する
　「=SUBTOTAL(9,B2:B4)」
B8セルには関西地区の売上を合計する
　「=SUBTOTAL(9,B6:B7)」
B9セルには全店舗の売上の総合計を計算する
　「=SUBTOTAL(9,B2:B8)」
という数式が、それぞれ入力してあります。

B9セルに入力されている総合計を計算する数式は「=SUBTOTAL(9,B2:B8)」ですから、範囲内に関東地区の合計を計算しているB5セルと、関西地区の合計を計算しているB8セルを含んでいます。

しかし、B9セルには正しい計算が行われています。もし総合計を計算する数式を「=SUM(B2:B8)」としていたら、小計まで合計に含まれた間違った値が計算されてしまいます。

これがSUBTOTAL関数の特長的な部分です。

これがヘルプの「範囲内に他の集計値が挿入されている場合、ネストされている集計値は、計算の重複を防ぐために無視されます。」の意味しているところです。

SUBTOTAL関数の範囲内にSUBTOTAL関数が含まれているときには、SUBTOTAL関数の値を無視して計算するのです。

まずはこのことをご理解ください。

ただ、ここまでの説明ではSUM関数が不要でないことの説明にはなっていません。

SUM関数が不要にならない例として「SUBTOTAL関数 NG」シートをご覧ください。

E2セルに1月から3月の合計を求めるために「=SUBTOTAL(9,B2:D2)」という数式を入力して、E2セルをE9セルまでオートフィルしたものです。

E5・E8・E9セルの値が「0」になってしまっています。

ヘルプの「範囲内に他の集計値が挿入されている場合、ネストされている集計値は、計算の重複を防ぐために無視されます。」という効果が働いた結果です。

E5セルには「=SUBTOTAL(9,B5:D5)」という数式が入力されていますから、B5:D5セルの合計が計算されてよさそうです。

しかし、B5:D5セルにはSUBTOTAL関数が使われています。

SUBTOTAL関数の範囲内にSUBTOTAL関数が含まれているときには、SUBTOTAL関数の値を無視して計算するの仕様です。

B5:D5セルにはSUBTOTAL関数が入力されていますから、E5セルでSUBTOTAL関数を使って計算しようとしても無視されて「0」が計算されてしまうのです。

「SUBTOTAL関数 NG」シートのような場合、どこかでSUM関数を使わざるを得ません。例えば「SUBTOTAL関数 NG改」シートのように。

ここでは合計を計算する例をご紹介しましたが、他の計算の場合でも同じことです。

SUBTOTAL関数があればSUM関数やCOUNT関数が不要というわけにはいかないのです。