・小計と総計の計算ミスを防ぐ方法
・SUBTOTAL関数の使い方
練習用サンプルデータ（ダウンロード）
※シートは練習用と成果物（解答）の２種類あります。閲覧したい方はダウンロードして下さい。
合計を求める際に、切りの良い所で、小計を入れ、最後に全体の合計を計算することがあると思います。そういった場合、一番に思い浮かぶ関数は、SUM関数だと思います。
SUM関数を使うと、全体の合計を計算する時に、各小計セルを１つ１つ選択しないといけません。また、過剰にセルを選択するミスをしてしまう場合もあり、手間がかかる割にコスパがよくありません。そういった時に、手間の少ないSUBTOTAL関数の使い方をご紹介します。
（練習で実施する小計と合計の自動計算の完成図）
※beforeの状態を、afterの状態まで仕上げる過程を説明します。
例題は、小計と上期合計の計算を、SUBTOTAL関数を使って計算する問題です。
練習サンプルデータは、ダウンロードファイルのシート「練習用」となります。
‘【１】SUM関数の現状確認（ご参考）
※サンプルは、シート「ご参考（SUM）」を参照ください。
上記の、この手間を省略することができる関数が、SUBTOTAL関数です。
【２】SUBTOTAL関数の公式解説
SUBTOTAL関数の公式
＝SUBTOTAL（集計方法,範囲１,範囲2・・・・）
‘（１）公式の「集計方法」は、以下の表より選択し入力
合計以外に様々の集計が可能です。SUM関数の場合は、非表示したセルも計算対象となります。SUBTOTAL関数を使うと、フィルター機能を使って、表示しているものだけを計算させ、表示させることが可能です。
（例）合計を計算したい場合は、「集計方法」に９か109を入力
９は、非表示したセルも合計して計算する時に入力
109は、非表示したセルは合計に含めない時に入力
|
計算したい事 |
集計方法 |
|
|
非表示セルの値 |
非表示セルの値 |
|
|
平均 |
1 |
101 |
|
数値の個数 |
2 |
102 |
|
データの個数 |
3 |
103 |
|
最大値 |
4 |
104 |
|
最小値 |
5 |
105 |
|
積算 |
6 |
106 |
|
不偏標準偏差 |
7 |
107 |
|
標本標準偏差 |
8 |
108 |
|
合計 |
9 |
109 |
|
不偏分散 |
10 |
110 |
|
標本分散 |
11 |
111 |
‘（２）公式の「範囲」指定
SUBTOTAL関数を使って合計を計算した場合、合計を求めるセル範囲を指定します。
範囲内にSUBTOTAL関数を含むセルがある場合は、無視して計算されます。
その為、2重計算される心配がなくなります。
【２】SUBTOTAL関数の使い方
‘①セルC6の小計に「=SUBTOTAL(9,B3:B5)」を入力します。
集計方法は、非表示セルの値も含めて合計を指定する「９」を入力
範囲は、合計する範囲を指定します。「B３：B5」を入力
‘②セルB10も同様に小計を求めます。
‘③セルB11の上期合計を求めます。
（POINT）
上期合計は、全体範囲「B３からB10」を指定しても、その中にある小計は合計されません。SUBTOTALを含むセルは、合計に含まれないのが、SUBTOTAL関数の最大の特徴です。
SUBTOTAL関数の注意点
集計するデータを、あらかじめ、集計したい単位で、並び替えしておくことです。支店の塊で集計したい。また、上期の塊で集計したい。など用途は様々あると思いますが、集計したい単位で、まず、並び替えを意識しておく必要があります。
‘【３】集計の便利機能の紹介（小計欄を作るのが面倒な方向け）
支店単位に小計を計算したい場合は、「支店」列の昇順で並び替えましょう。
※サンプルデータはシート「ご参考（機能）」となります。
●もし並び順の指定がある場合は、ユーザー設定リストを使って指定しましょう。合わせて、ユーザー設定リスト並び替え
‘①支店の列のどこかのでもよいので、支店名の入ったセルを選択します。
「昇順」ボタンをクリックします。
‘②どこかの支店名をクリックし、データタブの小計をクリックしてください。
‘③すると「集計の設定」のダイアログボックスが起動されます。
以下の設定を選択して、ＯＫをクリック。
〇グループ基準
支店単位で集計したいので「支店」を選択します。
〇集計の方法
合計の集計をしたいので「合計」を選択します。
〇集計するフィールド
売上の合計を計算したいので「売上」にチェックを入れます。
‘④すると、支店単位で、集計行が追加されました。
そして、最終行は総計が追加されています。
‘⑤アウトラインの切り替えをしてみましょう。
左側に表示されたアウトラインは、「―」をクリックすると、グループが折りたたみ、「＋」をクリックすると、展開されます。
〇「―」と「＋」を使って表示したい項目を自由に設定できます。
〇「１」「２」「３」のボタンについては、
‘「１」をクリックすると総計のみ
‘「２」をクリックすると小計と総計
‘「３」をクリックすると全部展開
ピボットテーブルの機能も使い勝手が良いで、合わせて検討してみてはいかがでしょう。
【４】まとめ
SUBTOTAL関数を使う時は、事前に集計したいグループで並び替えておきましょう。
また、小計欄を求めたい箇所がたくさんあり、時間もない場合は、アウトラインの機能を検討してみましょう。ピボットテーブルも集計する際、あっという間に集計できますので、試してみてください。
