・きっかけ
取引先の担当者様からのこのような相談がありました。「エクセルで作成している仕入台帳を月別(Book別)に作成しているが月別のファイルを一つに管理する方法はない？」とのことでした。なぜ月別にファイルを作成しているのか確認したところ、仕入金額を集計(SUM関数)したときに入力した全部の行の金額全部が集計されてしまって、日付や取引先でフィルターをしても思ったような結果が表示されなかったことが理由で月毎にファイルを分けているとのことでした。それを解決するための関数としてSUBTOTALと表をテーブルにした際のメリット説明した時のことを記事にさせていただきました。
※この記事は5分で読めます。
・こんな方におすすめです
・集計にSUM関数を使用しておりフィルタをかけたときに非表示の値は集計対象から外したい方。
・集計したいセルの範囲を毎回手作業で指定してSUM関数集計している方。
・集計区分によって別セルや別表などを使ってSUM関数集計している方。
・SUBTOTAL関数とは
指定された集計方法で、合計や件数、個数他を集計してくれます。
=SUBTOTAL（集計方法,対象範囲）例としてセルC1～C5に入力されている数字の合計を算出してくれたり、件数の集計をしてくれます。
※合計や件数など集計方法の指定については下記、SUBTOTALの集計方法(参考資料)にありますので、お手数をお掛けいたしますが、そちらを参照いただけますと幸いです。
・使用手順
下記サンプルファイルの仕入台帳では集計結果を表示したいセル(サンプルファイルではセルのC2に入力)に関数「SUBTOTAL(9,C4:C15)」を入力し集計方法を指定し範囲(セルC4～C15)を選択します。
セルに関数入力後は下記画面のように指定した範囲(セルC4～C15)の合計が表示されます。
これではSUM関数と変わらないのではないかと疑問が湧きます…
・SUM関数(他 AVERAGE、COUNT)とSUBTOTAL関数の違い
ここまでの説明を見ていただいた方は、よく集計で使用するSUM関数とSUBTOTAL関数は何が違うのか疑問に思います。
違いはフィルターで絞り込みしたとき、表示内容(表示されている行のみ)の集計値になります。
SUM関数は非表示(表示されていない行)になっている行も集計対象に含まれるので、画面の見た目で明細と合計に差がでてしまいます。
行の件数が多ければその集計対象の間違いにも気付かない状況になってしまうと思います。
※SUM関数同様に集計でよく使われるAVERAGE関数やCOUNT関数も非表示になっている行を含んだ合計になります。
・実行例(指定条件でフィルターをかけたとき)
下記のようにサンプルファイルの支払日セルの条件を1～3月で指定条件でフィルターをかけてみます(支払日列の▼をクリックし該当月をチェック)
条件を指定しフィルターをかけてみると、表示されている内容だけの集計になります。(今回は支払日でフィルターをかけております)
・指定範囲がテーブルの場合。(自動で範囲が広がる)
管理しているデータ(列、行)をテーブルとして最初に範囲を指定してしまえば行が追加されても自動で書式の範囲が広がるので、集計の範囲漏れを防ぐことができますので私はこちらの方法をおすすめいたします。
※エクセルのテーブルについてですが、表に新しく行や列を挿入や追加しても書式が引き継がれる機能で、テーブル化していないと追加した行に数式の入力を忘れてしまうと集計漏れの原因となってしまいますが、テーブル化すると自動で範囲を広げてくれるので数式の入力忘れの心配がなくなるのと、あくまで個人の意見ですが見た目がキレイがになることがメリットではないでしょうか。
対象範囲をテーブルにする場合。対象範囲を選択し「挿入」→「テーブル」から作成できます。
テーブル作成後は自動で書式の範囲が広がるので関数の有効範囲を変更する手間が省けます。新規に行を追加しても自動で範囲(SUBTOTAL関数が有効)が自動で広がるのでとても便利です。
テーブルになると水色と白の交互に行の色が自動で色分けされてキレイになりますね♪
また、テーブルを選択中に「テーブルツール」→「テーブルデザイン」で自分好みにカスタムできます。
・SUBTOTALの集計方法(参考資料)
SUBTOTAL関数を使用するときにセルに「=SUBTOTAL（集計方法,対象範囲）」を入力しますが集計方法は今回、記事にした合計以外にも件数や平均などもあります。私は下記、「件数」、「平均」をよく使いますが
分析したい内容によって指定できます。
・件数の場合 → =SUBTOTAL（3,対象範囲）
・平均の場合=SUBTOTAL（1,対象範囲）
集計方法
| 集計方法 | 集計方法名 | 集計の種類 |
| 1 | AVERAGE | 平均値 |
| 2 | COUNT | 数値の個数 |
| 3 | COUNTA | データの個数 |
| 4 | MAX | 最大値 |
| 5 | MIN | 最小値 |
| 6 | PRODUCT | 積 |
| 7 | STDEV.S | 不偏標準偏差 |
| 8 | STDEV.P | 標本標準偏差 |
| 9 | SUM | 合計値 |
| 10 | VAR.S | 不偏分散 |
| 11 | VAR.P | 標準分散 |
・まとめ
SUMやCOUNT関数よく使うのですが、SUBTOTAL関数は聞いたことはあったようなないようなと言うくらいに、過去に使った際もなんとなくネットで調べて使ったことがありましたが、こんな便利な機能なのに覚えていませんでした。そんな背景もあり今回の相談を受けて自分自身、理解していなかったので再度使用方法について確認し、この関数のメリットと使用方法を理解するいい機会であったのと、取引先の担当者様にはSUBTOTAL関数とテーブル機能の使い勝手が今の業務の環境にとても合っていることをお伝えできたので、これまで複数に増えていたシートやブックを一つのブックにまとめることができました。ご依頼いただいた担当者の管理工数や作業時間も少し減らせるのではないかと期待しております。私としては何か他のことでも使えるのではないかと、頭の片隅に置いておきます。
最後にですが、この記事は私の実体験をもとに書いております。VBAを使いエクセルの自動化をすることで同じことの繰り返し作業やケアレスミスを減らすことができると思います。また、視点を変えて違うことへの利用を検討したり、この記事を通して新しいアイディアが生まれたり、VBAに興味を持っていただけたら幸いです。
ここまでご覧いただき、誠にありがとうございます。
・ご注意
・掲載されている内容には細心の注意をしてるつもりですが、
間違いやご指摘等がありましたら、「お問い合わせ」からご連絡をいただけますと幸いです。
・掲載されている入力VBAコード等は動作を保証するものではなく、あくまでサンプルとして掲載しております。
・掲載されている入力VBAコード等は自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
・対応バージョン
Excel2021、2019、2016
コメント