図書館員くろすけの備忘録

「=Subtotal(集計方法,対象1,対象2......)」

「リストまたはデータベースの集計値を返します。」

しばらく間隔があいてしまいましたが、今回取り上げるのは、統計作業に便利なSUBTOTAL関数です。
一見難しそうに見えるこの関数ですが、全然そんなことはなく、使いやすく強力な統計のお友達です。

今回は、下のような学部ごとの図書費の合計と、その総計を出すような表を作ってみようと思います。図書館業務で必ず訪れる統計の季節には、こんな感じの表は珍しくないかと思います。



単純に考えたら、下のようにSUM関数で処理をするのが多いかなと思います。



SUM関数の場合、特に、一番下の総計の部分が面倒ですよね。学部の合計が計算されているセルを指定してあげないといけないのですが、指定を間違えることもしばしばありますし、そもそも大きくなったり、入り組んだりすると、どこに学部の合計があるかわかりづらいものもあります。そもそもそんな表を作るな、というのが答えの一つなのですが、それでも作らなければいけない時に力を発揮するのが、このSUBTOTAL関数です。



SUBTOTAL関数の入力画面を呼び出すと、まず「集計方法」に「？」となってしまう方も多いかもしれません。説明も「リストまたはデータベースの集計値を返します。」とそっけないものです。
実はSUBTOTALは合計だけでなく、平均やカウント、最大、最小などを算出することもできるのです。なので、まずは何をしたいのか、を宣言する必要があります。



計算方法は、以下の1〜11の11通りです。101〜111もありますが、ひとまずは1〜11で十分でしょう。その差異は今回は割愛させていただきます。これを数字で指定します。見慣れない関数も混ざっていますが、ひとまず、今回は合計ですので、「9」を使います。さっそく先程の表に使ってみましょう。



これだけだと、ただのSUM関数と変わりませんね。ひと手間増えたSUM関数といった感じになってしまいますが、SUBTOTAL関数が威力を発揮するのはここからで、説明にない特徴として、「SUBTOTAL関数はSUBTOTAL関数を無視する」という特徴があります。さっそく下の図を見てみましょう。



このような形でSUM関数を使った場合、学部計欄のSUM関数を再度計算してしまい、結果が本来の2倍になってしまいます。ですが、SUBTOTAL関数の場合は、



すべての学部計をSUBTOTAL関数で計算したうえで、総計にSUBTOTAL関数を使用した場合、



このように、学部計欄のSUBTOTAL関数を無視して、計算結果を返してくれます。つまり、小計が入り組んでいるような構造の表であっても、簡単に総計を算出することができるのです。また、このような使い方ができるということは、メンテナンス性も上がります。是非SUBTOTAL関数を使いこなして、厄介な業務を短縮させてください。