「小計」を省いて「合計」を算出できる関数SUBTOTALの使い方

今回は、途中に「小計」がある表から「全体の合計」を求める場合などに活用できる関数SUBTOTALの使い方を紹介する。同様の計算を関数SUMで行うと、「通常の数値データ」と「小計の数値データ」が重複して計算されるため、正しい計算結果を得られない。ほかにも、関数SUBTOTALはさまざまな場面に応用できるので、ぜひ使い方を覚えておくとよいだろう。

関数SUBTOTALの使い方

まずは、今回の例で使用するデータ表から紹介していく。以下の表は、とある施設の「入場料」「館内飲食」「グッズ販売」について各日の売上をまとめた表だ。各日の売上(小計)は関数SUMにより算出されている。

• 分類別に売上をまとめたデータ

この表から「全データの売上の合計」を求める場合、普通に関数SUMを入力すると、正しくない計算結果が表示されてしまう。

• 関数SUMの入力

• 関数SUMの計算結果

上記の場合、関数SUMの計算結果に「小計」の数値も含まれてしまうため、正しい「合計」を得ることはできない。正しい合計を算出するには、「=E8+E12+E16+E20+E24+E28+E32」のように足し算で計算するか、もしくは「=SUM(E5:E32)/2」のように数式・関数を記述する必要がある。いずれもの方法でも正しい「合計」を求められるが、正直な話、決してスマートな解決方法とはいえないだろう。このような場合に活用できるのが、関数SUBTOTALだ。

それでは、関数SUBTOTALの使い方を紹介していこう。関数SUBTOTALは以下のような書式で記述する。合計を求める場合は、第1引数に「9」を指定し、第2引数に「合計を求めるセル範囲」を指定すればよい。

◆関数DSUMの書式 =DSUM(計算方法, セル範囲)

• 第1引数…計算方法を以下に示した数値で指定
• 第2引数…計算の対象となるセル範囲

◆第1引数に指定できる値

• 1 or 101：平均(AVERAGE)
• 2 or 102：数値データの個数(COUNT)
• 3 or 103：データの個数(COUNTA)
• 4 or 104：最大値(MAX)
• 5 or 105：最小値(MIN)
• 6 or 106：掛け算(PRODUCT)
• 7 or 107：不偏標準偏差(STDEV.S)
• 8 or 108：標準偏差(STDEV.P)
• 9 or 109：合計(SUM)
• 10 or 110：不偏分散(VAR.S)
• 11 or 111：分散(VAR.P)

関数SUBTOTALを使用するときは、「小計」にあたる部分の計算も関数SUBTOTALで行う必要がある。たとえば、「11/01(水)の小計」を求めるときは、以下のように関数SUBTOTALを入力する。

• 関数SUBTOTALで小計を算出

• 関数SUBTOTALの計算結果

同様の手順で全ての「小計」を算出したら、次は関数SUBTOTALで「合計」を算出する。今回の例では「E5:E32」が合計するセル範囲となるので、関数SUBTOTALの記述は以下のようになる。このセル範囲には「小計」の数値も含まれているが、この数値は関数SUBTOTALにより算出されているため、計算の対象外として処理される。

• 関数SUBTOTALで合計を算出

• 関数SUBTOTALの計算結果

このように、関数SUMの代わりに関数SUBTOTALを利用すると、重複する項目を自動的に除外して「合計」を求められるようになる。もちろん、第1引数の値を変更すれば「平均」を求める場合などにも応用することが可能だ。

ちなみに、今回の例で示した問題は、「小計」や「合計」を1つ右の列(F列)に配置するなど、表の形式を工夫することでも解決できる。

• 「小計」と「合計」をF列に配置した表

しかし、状況によっては表の形式を変更するのが困難な場合もあるだろう。こういった場合に備えて、関数SUBTOTALの使い方も覚えておくと、様々な形式の表に対応できるようになる。

フィルター使用時の合計の算出

関数SUBTOTALは、フィルターを使ってデータを抽出する際にも活用できる。以下の表は、先ほどの例と同じデータについて、「小計」の行を削除し、フィルターを適用した表となる。また、この表の末尾には「売上」の合計を算出する関数SUMが入力されている。

• フィルターを適用した表

この状態で「分類」が「入場料」のデータだけを抽出すると、以下に示した図のようになる。「入場料」のデータだけが抽出されるものの、関数SUMにより算出される値は「全データの合計」のまま変化しない。

• フィルターによるデータの抽出

• 関数SUMにより算出される値は「全データの合計」

このような場合に、関数SUMの代わりに関数SUBTOTALを入力しておくと、データの抽出状況に応じて「合計」を再計算することが可能となる。

• 関数SUBTOTALの入力

たとえば、「入場料」のデータだけを抽出すると、関数SUBTOTALを入力したセルには「入場料の合計」が表示される。同様に、「館内飲食」のデータだけを抽出すると「館内飲食の合計」が表示される。

• 「入場料」のデータだけを抽出した場合

• 「館内飲食」のデータだけを抽出した場合

このように、関数SUBTOTALを利用すると、「抽出したデータに応じた合計」を算出できるようになる。フィルターを使ってデータを抽出し、そのつど合計を確認していきたい場合などに非常に重宝する存在となるだろう。もちろん、第1引数を「1」に変更すれば、抽出したデータの「平均」を求める場合にも応用できる。

関数SUBTOTALはフィルターと非常に相性がいい。こちらの使い方も合わせて覚えておくと、データを分析する際に役立つはずだ。気になる人は、簡単な表で動作を試しておくとよいだろう。