Excel使いこなしの基礎と応用

SUBTOTAL関数で合計|9と109の使い分けやフィルターでの使用

使用関数:SUBTOTAL

表の途中に小計行を設けている場合、SUM関数ではなく、SUBTOTAL関数を使います。小計を含むデータ表でスムーズな集計を実行する関数です。非表示の行を除外した集計やフィルターで抽出後の集計にも応用できます。

目次


小計を除いて合計する

SUBTOTAL(サブトータル)は「小計」のこと。その名の通り、小計行のあるデータ表で力を発揮します。

スポンサーリンク

Webデザインのサービスごとに小計を設けた売上表があります。
小計を設けた売上表
表の最終行を「総計」として「オートSUM」を実行すると、小計まで含めて合計してしまい、金額が倍になっていますね。
表にオートSUMを実行すると数値が倍
そこで、「数式」タブ「関数ライブラリ」の「数学/三角」から一覧をスクロールして「SUBTOTAL」をクリックします。
「関数ライブラリ」の「数学/三角」からSUBTOTALをクリック
最初の引数「集計方法」は番号で指定します。以下に番号リストを掲載しましたが、このページで使うのは「SUM=合計」に対応する「9」と「109」です。

集計方法の番号リスト
番号 集計方法 対応する関数
1 101 平均値 AVERAGE
2 102 数値が入ったセルの個数 COUNT
3 103 データが入ったセルの個数 COUNTA
4 104 最大値 MAX
5 105 最小値 MIN
6 106 全ての数値の積 PRODUCT
7 107 不偏標準偏差 STDEV.S
8 108 標本標準偏差 STDEV.P
9 109 合計 SUM
10 110 不偏分散 VAR.S
11 111 標本分散 VAR.P

関数の引数ダイアログで、「集計方法」に「9」を入力、「参照1」に小計も含めた金額の範囲を指定します。
SUBTOTALの関数の引数ダイアログ
「OK」すると、「総計」が小計を除外して集計されました。
総計が小計を除外して集計
数式は「=SUBTOTAL(9,B1:B12)」となります。「B列の範囲内で、小計を除外して合計を求める」

必須! 小計もSUBTOTALで集計

ここで、注意点が1つ。

スポンサーリンク

SUBTOTAL関数で小計を除いて集計するためには、小計自体もSUBTOTALで集計されている必要があります。

小計がオートSUMで集計されていたりすると除外されません。

集計方法9と109の使い分け

前章の「集計方法の番号リスト」で、合計に対応する番号が「9」と「109」の2種類ありました。上記例では「9」を指定して実行しましたが、「109」の出番は?

小計除外の応用として、非表示のデータを除外して集計を行いたい場合があります。「109」を指定すると、非表示になった行を無視して集計してくれるのです。

下図の表ではキャンペーンの格安サービス(4行目と7行目)を非表示にして、トータルを計算し直そうとしていますが、集計方法が「9」になっているので「小計」「総計」の数値は変わりません。
行を非表示にした表
「総計」「小計」の数式の「集計方法」を「109」に変更します。
「集計方法」を109に変更
金額が非表示のデータを除外して計算し直されました。
非表示のデータを除外して計算

フィルターとSUBTOTAL

非表示データの除外と同じことで、フィルターで抽出したデータの集計にもSUBTOTAL関数が使われます。

テーブル化した表でフィルターを実行してみましょう。
下図の売上表で「木曜日」だけをフィルターで抽出すると、
売上表から売上表で「木曜日」だけをフィルターで抽出
集計行に合計金額が表示されますが、このセルに自動で挿入されている数式は「=SUBTOTAL(109,[合計])」です。
集計行に合計金額が自動表示
テーブルの集計行にはあらかじめSUBTOTALが組み込まれているわけで、右横の▼からプルダウンリストを引き出して「平均値」や「データの個数」などを集計できるようになっています。まさに手間要らず。
▶データ表にはテーブルを使うべき! その理由と設定方法
テーブルの集計行に組み込まれたSUBTOTAL関数
▶Excel2010からは、引数のオプション指定で非表示データ・エラー値を除外できる強化版の関数AGGREGATE(アグリゲート)が登場。降順・昇順などの集計にも追加対応。

まとめ

  • SUBTOTAL関数は小計や非表示データを除外して集計する
  • 上記を実行するには、小計もSUBTOTAL関数で集計されていることが絶対条件
  • 引数「集計方法」には対応する番号を指定する
  • 1桁の番号(例:9)は小計のみを除外する
  • 3桁の番号(例:109)は非表示データ及びフィルターで隠されたデータも除外する
  • テーブルでは既にSUBTOTALが組込まれた集計行で合計や平均値が自動表示される


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。