意外に便利です(Excel SUBTOTAL関数)

関数はあまり使いませんが、このSUBTOTAL関数は、けっこう便利です。

SUBTOAL関数

ExcelにはSUBTOTAL関数というものがあります。
一定範囲の値の集計などをしてくれます。

=SUBTOTAL(集計方法,集計範囲) と設定します。

「集計方法」とありますが、単純な集計(SUM)のほかに、平均や最大値、カウントなどもしてくれます。
関数設定をしていく際に、こんなふうに集計方法が表示されます。

集計で使うので、9のSUMをセットします。
範囲はテーブルの「金額」部分です。
テーブルになっていれば、行が増えても全部を集計範囲としてくれます。

同じように集計する関数としては、SUM関数もあります。
ただ、SUM関数は決めた範囲の「すべて」を集計します。

しかしSUBTOTAL関数は、「希望する範囲のみ」集計することができます。
なので、フィルター機能と組み合わせることで、抽出した範囲だけ集計するなど、集計の幅がひろがるのです。

こんなふうに私は使っています

日々の経費処理を、私はExcelで行っています。
その書式が冒頭の表です。

このシートを見ていただくと、昨日までで私は420,324円、小口で経費を使っています。
シートにはフィルターがかかっていませんから、全額を集計しています。

それでは、5月はいくら使っているのか?
これを知りたいときには、日付でフィルターをかけます。

そうすると、5月分だけが抽出され、その抽出された分の金額が集計されます。

使ったのは、11,291円です。

次に、年初から昨日まで、タクシー代はいくら使っているかな?
これを集計しようと思います。

日付のフィルターを解除し、今度は摘要で「タクシー代」のフィルターをかけます。


タクシー代だけが抽出され、その金額は23,060円と出ました。

さらには「4月のタクシー代」とか、「1万円以上の買い物」なんていうのも集計することができます。

けっこう便利だと思いませんか?

集計のためにはテーブルに、合計は常に上にがおすすめ

ただ入力するだけならテーブルにする必要はないかもしれませんが、ただ入力するだけでは、もったいなさすぎます。
こういった入力をする場合には、必ず「テーブル」にしておきましょう。

この表でいうと、日付〜摘要の部分です。
データ行を含め範囲指定して、挿入 → テーブルとすれば、テーブル化できます。
テーブル化してあれば、ピボットテーブルにも利用できます。

それからもう1つ、意外にやっている方は少ないのですが、合計を「上に」表示させること。
これけっこうオススメです。

上にあれば、常に合計(集計値)を見ることができます。
もし合計を下においてしまうと、そこまで見に行かなければなりません。

「でもスクロールすると、上にあっても見えなくなっちゃわないですか?」

はい、そうです。
なのでもう1つ、私は表示の固定を使って、合計と見出しが見えなくならないようにしています。

表示 → ウインドウ枠の固定 → ウインドウ枠の固定として、この表の場合は3行目より上を固定表示させています。

これで、その下に3万行あっても、集計値は常に見ることができます。

さらに、このテーブルから会計ソフトにインポートするためのセットも、このExcelファイルにしてあります。
できるだけ入力しないで、効率よく作業は終わらせたいものです。

ーーー