小計を除いて合計する

下記表のように小計が複数ある表で合計を求めたい場合、小計のみを指定してSUM関数を利用して合計を求めることもできますが、実は、SUBTOTAL（サブトータル）関数で求めた方が便利だったりもします。

今回は、このSUBTOTAL関数の設定をご紹介します。

ポイント（下記表を参考）
SUM関数とSUBTOTAL関数の違いはというと、SUM関数は、比較的簡単に設定できますが、C5セルとC8セルにそれぞれ計算をし、さらに小計の合計を求めます。

ですので、設定後に３ケ所の式が当たっているかどうかを常に確認する必要があります。

それに比べ、SUBTOTAL関数にすると、C5セル・C8セルにも数式は入力しますが、C9セルの合計値は小計を除いて合計を求める為、SUM関数よりもより確実（シンプル）に合計値を求めることができます。

①　C5セルにカーソルを合わせます。

②　関数の挿入ボタン「fx」をクリックします。

　　すると、「関数の挿入」のダイアログボックスが表示されます。

③　関数の分類の枠から「数学/三角」を選択します。

④　関数名の枠から「SUBTOTAL」を選択します。

⑤　「OK」をクリックします。

すると、「関数の引数」のダイアログボックスが表示されます。

①　集計方法の枠に「9」と入力します。
　　※SUBTOTAL関数には下記のように、集計の種類があります。
　　　１・・・AVERAGE（平均）
　　　２・・・COUNT（数値の件数）
　　　３・・・CONTA（空白以外の件数）
　　　４・・・MAX（最大値）
　　　５・・・MIN（最小値）
　　　６・・・PRODUCT（積）
　　　７・・・STDEV（標本に基づいた標準偏差）
　　　８・・・STDEVP（標準偏差）
　　　９・・・SUM（合計）
　　　１０・・VAR（範囲内の不偏分散）
　　　１１・・VARP（分散）
この１１種類の中で、今回は合計値を求めたいので、「9」と入力します。

②　参照1の枠に「C3:C4」と入力します。

③　「OK」をクリックします。

C8セルも同様の数式を設定します。

C9セルには、「＝SUBTOTAL（9,C3:C8）」と入力します。
※この数式は、小計は含まずに合計しますという意味になります。

①　C5セルの数式をD5セル～E5セルへコピーします。

②　C8セルの数式をD8セル～E8セルへコピーします。

③　C9セルの数式をD9セル～E9セルへコピーします。

これで、全ての数式の設定が完了しました。

最後に、SUM関数よりもより確実に合計が求められるということをはじめにお話ししましたが、その例をご紹介します。

完成した後に、表に行を追加したとします。（６行目）

C5セルとC9セルがSUM関数の場合、C9セルの小計で追加行を見逃してしまうと、C10セルの合計も変更されず、「1750」という数値になります。

ですが、C5セル、C9セル、C10セルにSUBTOTAL関数を設定しておけば、C9セルの小計で追加行を見逃してしまっても、合計値のC10セルは追加行を含めた正しい数値「1800」が表示されます。

スポンサーリンク