知らなかった！subtotal関数の２つの使い方を完全マスター
subtotal関数は、指定したセルを指定した方法で集計して、その結果（例えば、合計・平均等）を求める関数です。
この記事では、subtotal関数の使い方・書式を説明するとともに
- subtotal関数をオートフィルタと組み合わせる方法
- subtotal関数を集計に使う方法
- subtotal関数を使うときの注意点
- subtotal関数を使う代わりにピボットテーブルで集計したほうがいい場合とは？
なども説明していきます。
この記事の目次
subtotal関数の書式
subtotal関数は、次のように使います。
- 集計方法
-
集計方法を番号で指定します。
この中では、「9番のsum関数」を使う機会が一番多いです。 - セル１,セル２, ...
-
集計したいセルを指定します。
sum関数の代わりに使う方法
例えば、C2セル～C12セルまでの「合計」を計算する場合。
sum関数であれば、次のように入力しますよね？
subtotal関数を使う場合には、次のように入力します。
sum関数で合計を取る場合と比べると、違うところは、最初に「9,」とつけるところだけです。
あとは、sum関数と同じように使えます。
subtotal関数の便利な使い方
subtotal関数は、他の関数と大きく違う「特徴」が２つありますので、
subtotal関数は、その特徴を生かした使い方をするのが基本となります。
大きな表を作るときの「小計」を取る
subtotal関数で集計をする場合、subtotal関数が入力されているセルは無視されます。
この性質を使ってsubtotal関数で小計を取ると、sum関数やcount関数などで小計を取るのに比べて、セルの指定が簡単にできます。
subtotal関数が特に威力を発揮するのが、部署が増えたり減ったりした場合です。
sum関数を使うと小計行が増えるたびに合計行の計算式の「足し算」を増やしていかないといけません。
手間がかかりますし、ミスも増えます。
一方で、subtotal関数であれば、合計行の計算式はわかりやすいので、ミスをする確率を下げられます。
subtotal関数とsum関数を組み合わせたらダメ
subtotal関数を使って小計を出す場合、１つだけ注意があります。
それは、subtotal関数を使うのであれば、すべての集計についてsubtotal関数を使わないといけないということです。
混在させてしまうと、次のように集計が正しく行われません。
subtotal関数は、subtotal関数が入っているセルは無視しますが、sum関数など別の関数で合計を取っているセルは無視してくれません。
その結果、集計結果がおかしくなります。
オートフィルタで表示されているセルのみ合計する
sum関数などの普通の関数は、オートフィルタを使用して表示・非表示を切り替えても、計算結果は変わりません。
ところがsubtotal関数を使うと、オートフィルタを使用したときに、表示されているセルだけを集計することができます。
オートフィルタで表示されているセルだけを集計したいという場合。
エクセルで使える関数は「subtotal関数」と「aggregate関数」の２つしかありませんので、このどちらかの関数を使うことになります。
subtotal関数を使うときの注意点
subtotal関数は、使いどころによっては便利なのですが、使わないほうがいい場面もあります。
オートフィルタではない単なる非表示セルは合算されてしまう
先ほど見たとおり、subtotal関数を使うとオートフィルタの結果を反映して、表示セルのみの集計ができます。
でも、オートフィルタを使わずに、単に非表示にしただけのセルは集計対象となります。
例えば、下の図では2行目～4行目、8行目～10行目を非表示にしているのですが、subtotal関数を使っても計算結果は変化していません。
単なる非表示の場合でも、集計から除外したい場合には、aggregate関数を使いましょう。
次のように、表示されている5行目～7行目だけの合計を計算することができます。
データ処理用の表では使わない
小計行を入れてしまうと、ピボットテーブル・オートフィルタなど、エクセルのデータ処理用の機能が使いにくくなります。
そのため、エクセルでデータ処理をする場合には、次のような小計行を入れないデータを作るのが基本です。
当然、subtotal関数も使うべきではありません。
subtotal関数は、エクセルで資料を作る最後の最後にだけ使いましょう。
subtotal関数の代わりに使える機能
subtotal関数を使って表を作りたいという場合のほとんどは、ピボットテーブルを使うと、同じ集計を簡単に行うことができます。
ピボットテーブルのほうが、レイアウトの自由度は低いのですが、集計作業にかかる手間は少なくて済みます。
可能であれば、ピボットテーブルを使って集計をしてみてください。
集計方法は、次のページで紹介していますので、あわせてご覧ください！
→ 意外と簡単！？エクセルのピボットテーブルでデータ集計