直線上に配置

集計

◎加える


Q53 オートSUMより便利なSUBTOTAL関数

集計項目が多い表があり、特に間に小計を入れた場合、オートSUMを用いて合計をとると小計まで合計されてしまいます。小計項目が多い場合に何かと不便です。何かよい方法はありませんか?

フィルターで抽出を行ったとき、その合計をオートSUMで求めると抽出されたデータ以外のデータも合計されています
どうやって合計を求めればよいのでしょうか?


両質問ともSUBTOTAL関数を用いることで解決できます。また、SUBTOTAL関数は使い慣れると集計分析の時に抜群の威力を発揮する関数なので、ぜひ覚えて欲しい関数です。

次のような表があるとします。


通常は小計や総計の欄にオートSUMなどを使って合計を出している場合がほとんどだと思います。しかしこれだとご質問にあるように総計を出す場合各小計欄を避けて合計するかあるいは、各小計項目だけを全て加えるかのいずれかをしなければ正しい総計が求められません。
小計項目がいくつもある場合での集計を行うにはSUBTOTAL関数を使用します。

SUBTOTAL関数で小計を求め、SUBTOTAL関数は集計範囲にあるSUBTOTAL関数は無視するという性質を利用して全範囲の総計にも同じようにSUBTOTAL関数を用いることで、オートSUMを用いた場合による小計の重複を避ける事が出来ます。

8月の地区名の小計セルを選択して関数ダイアログからSUBTOTAL関数を呼び出します。


SUBTOTALを選択すると下図の数式パレットが表示されます。

SUBTOTALはいろいろな集計が出来るようになっていますので、集計方法を選択します。
選択は各集計の方法を番号で指定する方式となっています。
一見面倒くさそうに見えますが、この方式が後で述べる集計・分析に抜群の威力を発揮するのです。

ここでは、地区名の集計をするのに集計方式の指定に3を指定します。
(文字列の)個数を数えるのはCONNTA関数です。(Q.データ数のカウントを参照)




次に集計の範囲を指定します。指定範囲は一度に30個まで指定出来ますが、ここでは範囲2以降は無視します。
8月1日の地区名Aにセルをおき8月10日のDまでドラッグして範囲を指定します。

計算結果が下図のように示されます。

次に肥満教室以降も同様に行いますが、セルの内容は数字ですので、SUBTOTAL関数の集計の引数は合計(SUM)を表す9を指定します。
集計範囲を同じように指定します。

これで、肥満教室の8月分が集計されました。これを右方向に親子教室までオートフィルでコピーすれば8月分の小計は完了です。

9月分以降を同様にして集計します。

最後に総計のセルにも同じように行いますが、集計範囲は一気に総計の手前まで指定します。ここで得られる合計は途中の小計を全て無視された合計数になっています。
また、SUBTOTAL関数には非表示になっている行を無視する性質もあるので、質問2のようにフィルターで抽出したデータの集計を行うとき抽出されたデータのみで計算を行うことができます。

最初の質問のように単に小計と総計を表示させるだけなら、メニューから
「データ(D)」→「集計(B)」を選んで集計行を挿入させる方法もあります。

(実はここの裏方で用いられている関数がSUBTOTAL関数なのですが・・・)
SUBTOTAL関数の一見めんどくさそうに見える集計方式が威力を出すと述べましたが、それは次のようなデータ表で集計・分析を行う場合です。
先ほどと同じようにSUBTOTAL関数を用いて集計を行います。年齢の平均値を求めたいので、引数は1となりますが、直接数式パレットに1を入力するのではなく、先に隣のセルに1と入力し、パレットではそのセル番号を絶対参照(指定)させます。(オートフィルを行うので$マーク付きの絶対参照にします)

横にオートフィルを行い各項目についても同じようにし、全項目の平均値を出します。

ここで、引数を指定したセルの数値を変えるとそれに対応した集計項目に全て変わります。最大値を出したければ4、標準偏差を出したければ8と入れ替えるだけで全項目の数値が変更されます。
また、次のように1つの関数でレポート表を一括表示させることも可能です。

このように引数を切り替える事で、多角的な見方が簡単に行えるのがSUBTOTAL関数の特徴です。

このような使い方でピボットテーブルを思い浮かべた方はエクセルを使いこなしている人ですね。「集計(B)」でも使われていることは述べましたが、実はピボットテーブルの中で一番活躍しているのがこのSUBTOTAL関数です。

SUBTOTAL関数
  書式:SUBTOTAL (集計方法,範囲1,範囲2,・・・)
  意味:リストまたはデータベースの集計値を返します
      作成した集計リストを修正するときは、SUBTOTAL 関数を編集します。
  集計方法:リストの集計に使用する関数を、1 〜 11 の番号で指定します。

集計方法          関数
  1       AVERAGE 関数  
  2       COUNT 関数

  3       COUNTA 関数
  4       MAX 関数
  5       MIN 関数
  6       PRODUCT 関数
  7       STDEV 関数
  8       STDEVP 関数
  9       SUM 関数
  10       VAR 関数
  11      VARP 関数
  範囲:集計するリストの範囲を 1 〜 29 個まで指定します。
      範囲 内に他の集計値が挿入されている場合、ネストされている集計値は、
      計算の重複を防ぐために無視されます。
      リストを抽出した結果として非表示になっている行は無視されます。
      この
ため、抽出されたリストに表示されているデータだけを集計することができます。
     参照先に 3-D 参照が含まれている場合は、エラー値 #VALUE! が返されます。


◎ワンポイント オートSUM 例
下のような縦横方向に合計欄のある表で各項目を合計するとき、セル内にひとつ一つオートSUMを行っていませんか?

このような表は全て選択してオートSUMを行うと一括で答えが得られて便利です。

直線上に配置


     目次へ   次のページへ