知らなかった!subtotal関数の2つの使い方を完全マスター

subtotal関数は、指定したセルを指定した方法で集計して、その結果(例えば、合計・平均等)を求める関数です。

この記事では、subtotal関数の使い方・書式を説明するとともに

  • subtotal関数をオートフィルタと組み合わせる方法
  • subtotal関数を集計に使う方法
  • subtotal関数を使うときの注意点
  • subtotal関数を使う代わりにピボットテーブルで集計したほうがいい場合とは?

なども説明していきます。

subtotal関数の書式

subtotal関数は、次のように使います。

=subtotal(集計方法 , セル1 , セル2 , … )
集計方法

集計方法を番号で指定します。
この中では、「9番のsum関数」を使う機会が一番多いです。

番号 集計方法
1 average関数
2 count関数
3 counta関数
4 max関数
5 min関数
6 product関数
番号 集計方法
7 stdev関数
8 stdevp関数
9 sum関数
10 var関数
11 varp関数
セル1,セル2, …

集計したいセルを指定します。

sum関数の代わりに使う方法

例えば、C2セル~C12セルまでの「合計」を計算する場合。

sum関数であれば、次のように入力しますよね?

=sum(C2:C12)

subtotal関数を使う場合には、次のように入力します。

=subtotal(9, C2:C12)

sum関数で合計を取る場合と比べると、違うところは、最初に「9,」とつけるところだけです。
あとは、sum関数と同じように使えます。

subtotal関数の便利な使い方

subtotal関数は、他の関数と大きく違う「特徴」が2つありますので、
subtotal関数は、その特徴を生かした使い方をするのが基本となります。

大きな表を作るときの「小計」を取る

subtotal関数で集計をする場合、subtotal関数が入力されているセルは無視されます。

この性質を使ってsubtotal関数で小計を取ると、sum関数やcount関数などで小計を取るのに比べて、セルの指定が簡単にできます。

subtotal_1_1

subtotal関数が特に威力を発揮するのが、部署が増えたり減ったりした場合です。

sum関数を使うと小計行が増えるたびに合計行の計算式の「足し算」を増やしていかないといけません。

手間がかかりますし、ミスも増えます。
一方で、subtotal関数であれば、合計行の計算式はわかりやすいので、ミスをする確率を下げられます。

subtotal_1_2

subtotal関数とsum関数を組み合わせたらダメ

subtotal関数を使って小計を出す場合、1つだけ注意があります。

それは、subtotal関数を使うのであれば、すべての集計についてsubtotal関数を使わないといけないということです

混在させてしまうと、次のように集計が正しく行われません。

subtotal_1_7

subtotal関数は、subtotal関数が入っているセルは無視しますが、sum関数など別の関数で合計を取っているセルは無視してくれません。

その結果、集計結果がおかしくなります。

オートフィルタで表示されているセルのみ合計する

sum関数などの普通の関数は、オートフィルタを使用して表示・非表示を切り替えても、計算結果は変わりません。

ところがsubtotal関数を使うと、オートフィルタを使用したときに、表示されているセルだけを集計することができます。

subtotal_1_3

オートフィルタで表示されているセルだけを集計したいという場合。

エクセルで使える関数は「subtotal関数」と「aggregate関数」の2つしかありませんので、このどちらかの関数を使うことになります。

subtotal関数を使うときの注意点

subtotal関数は、使いどころによっては便利なのですが、使わないほうがいい場面もあります。

オートフィルタではない単なる非表示セルは合算されてしまう

先ほど見たとおり、subtotal関数を使うとオートフィルタの結果を反映して、表示セルのみの集計ができます。

でも、オートフィルタを使わずに、単に非表示にしただけのセルは集計対象となります。
例えば、下の図では2行目~4行目、8行目~10行目を非表示にしているのですが、subtotal関数を使っても計算結果は変化していません。

subtotal_1_4

単なる非表示の場合でも、集計から除外したい場合には、aggregate関数を使いましょう。
次のように、表示されている5行目~7行目だけの合計を計算することができます。

subtotal_1_5

データ処理用の表では使わない

小計行を入れてしまうと、ピボットテーブル・オートフィルタなど、エクセルのデータ処理用の機能が使いにくくなります。

そのため、エクセルでデータ処理をする場合には、次のような小計行を入れないデータを作るのが基本です。

subtotal_1_6

当然、subtotal関数も使うべきではありません。

subtotal関数は、エクセルで資料を作る最後の最後にだけ使いましょう。

subtotal関数の代わりに使える機能

subtotal関数を使って表を作りたいという場合のほとんどは、ピボットテーブルを使うと、同じ集計を簡単に行うことができます。

ピボットテーブルのほうが、レイアウトの自由度は低いのですが、集計作業にかかる手間は少なくて済みます。

可能であれば、ピボットテーブルを使って集計をしてみてください。

集計方法は、次のページで紹介していますので、あわせてご覧ください!
 → 意外と簡単!?エクセルのピボットテーブルでデータ集計

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。