Excel（エクセル）関数の1つ、SUBTOTAL（サブトータル）関数は、指定した範囲を、指定した方法（合計・平均・個数等）で集計する関数で、SUM関数やAVERAGE関数、COUNT関数、COUNTA関数などのような機能を持っています。
この記事では、実務での使用頻度が高く、ぜひ知っておいてほしい、小計が複数個ある表や、小計と合計がある表に、SUBTOTAL関数をおすすめする理由を、SUM関数にはない特徴とともに紹介します。
関連記事フィルタと組み合わせるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう！
関連記事手動で非表示にした行を合計したくないならSUBTOTAL( 109、合計範囲 )を使おう！
| サブトータル | 書式 |
| SUBTOTAL関数 | （ 集計方法 , セル範囲 ） |
SUBTOTAL関数の書式
SUBTOTAL（サブトータル）関数は、指定した範囲を、指定した方法（合計・平均・個数等）で集計する関数です。
［=SUBTOTAL( 集計方法 , セル範囲 )］という書式で表します。
集計方法
1～11、101～111のいずれかの数値（番号）で指定します。
セル範囲
セル範囲は 1つだけでなく、2つ以上も指定できます。
集計方法
SUBTOTAL関数は、集計方法を1～11、101～111のいずれかの数値（番号）で指定します。
まずは、数値（番号）は、9 を使えるようになりましょう。
なお、どの数値（何番）が、どの集計方法に対応しているのかを覚える必要はありません。
以下のように、支援機能でわかりやすく表示してくれます。
SUBTOTAL関数、集計方法［9］
SUBTOTAL関数の代表的な使い方である、集計方法を9とする場合を解説します。
SUM関数ではなく、SUBTOTAL関数を使うのには大事な意味が込められています。
SUM関数との書式の違い
今、C5セルに、C2セル～C4セルの合計を求めることにします。
SUM関数を使うのならば、以下のようになりますよね。
| C5セル | =SUM(C2:C4） |
一方、SUBTOTAL関数を使うのならば、以下のようにします。
SUM関数よりは複雑に見えますが、9を入れるだけなのでたいしたことはありません。
| C5セル | =SUBTOTAL(9,C2:C4） |
SUBTOTAL関数の特徴を活かす
| SUBTOTAL関数で集計をする場合、SUBTOTAL関数が入力されているセルは無視される |
(adsbygoogle = window.adsbygoogle || []).push({});
どういうことかを、具体例を用いて説明します。
さきほどの表のC5セル、C10セル、C11セルにSUBTOTAL関数を入力します。
| C5セル | =SUBTOTAL(9,C2:C4） |
| C10セル | =SUBTOTAL(9,C6:C9） |
| C11セル | =SUBTOTAL(9,C2:C10） |
C11セルの範囲を、C2セル～C10セルとしていることに注目してください。
SUBTOTAL関数で小計を求めている、C5セルとC10セルが含まれていますが、［SUBTOTAL関数で集計をする場合、SUBTOTAL関数が入力されているセルは無視される］という特徴から、二重に計上されることなく、以下のようにうまくいきます。
組織改編などに強い
今度は、営業2課が組織改編で、営業2課と営業3課に分かれた場合を例にして説明します。
営業2課の渡辺さん（B8セル）と山本さん（B9セル）が、営業3課になったとしましょう。
8行目に行を挿入し、営業2課の小計を求めます。
C8セルに、SUBTOTAL関数を入力します。
| C8セル | =SUBTOTAL(9,C6:C7） |
※C11セルのSUBTOTAL関数を［=SUBTOTAL(9,C9:C10）］と適正化しています。
SUM関数の場合、途中に小計を加えると、合計欄（ここでは、C12セル）のセル範囲を修正する必要がありますが、SUBTOTAL関数の場合はその必要がありません。
手間が省けるとともに、修正漏れを防ぐことができ、ミスする確率を下げることになります。
以上の理由から、小計を求める、このような表を作成する際は、SUM関数ではなく、SUBTOTAL関数を使用することをオススメします。
関連記事フィルタと組み合わせるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう！
SUBTOTAL関数を使用する際の注意点
SUBTOTAL関数で集計する場合、SUBTOTAL関数は無視されますが、SUM関数などが無視されるわけではありません。
このことに注意をしてください。
つまり、SUBTOTAL関数で集計する場合は、すべての集計をSUBTOTAL関数で行うようにしてください。
SUM関数とSUBTOTAL関数を混在させると、集計結果が正確ではなくなります。
おすすめ・関連記事
- Excel関数の基本的な使い方と応用テクニック辞典（関数一覧）
- Excel 厳選ショートカットキー・印刷用・早見表PDF付き
- Windows10 厳選ショートカットキー・早見表・印刷用PDF付き
- Word 厳選ショートカットキー・印刷用・早見表PDF付き
- PowerPoint 厳選ショートカットキー・早見表・印刷用PDF付き