1. ホーム
  2. ブログ
  3. SUBTOTAL関数の使い方|フィルタで絞った分だけを合計する

SUBTOTAL関数の使い方|フィルタで絞った分だけを合計する

#

この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。

フィルタでA社だけに絞ったのに、一番下の合計が全社の金額のまま動かない。 SUM は画面に出ていない行も足しているからです。

SUBTOTAL(サブトータル) は、いま見えている行だけを集計します。

何が起きているのか

取引先をA社だけにフィルタしたのに、合計の =SUM(D2:D6) が全社の 220,000 のままの画面
画面にはA社の3行しか出ていないのに、合計は 220,000 のまま。見えているのは 129,000 分。
!

この状態で数字を転記すると事故になる

「A社の売上を調べて」と言われて絞り込み、一番下の合計をコピーする。絞ったつもりで全社の数字を報告してしまう。

SUBTOTALの形

=SUBTOTAL(集計番号, 範囲)
  1. 集計番号何をするかを数字で指定。合計なら 9、件数なら 3
  2. 範囲集計したい範囲。SUMと同じ

STEP1:SUMをSUBTOTALに書き換える

合計セルを =SUBTOTAL(9,D2:D6) に書き換え、フィルタで見えている3行分の 129,000 になった画面
=SUM(D2:D6) → =SUBTOTAL(9,D2:D6)。関数名と先頭の 9 を足すだけ。見えている3行分 129,000 になる。
B7に =SUBTOTAL(3,B2:B6) を入れ、絞り込んだ件数 3 が出た画面
番号を 3 にすると件数。合計の横に並べておくと、絞った結果の中身が一目で分かる。
フィルタを解除すると、SUBTOTALの合計が自動で 220,000 に戻った画面
フィルタを外せば 220,000 に戻る。掛け替えるたびに合計が追いかけてくる。

集計番号

番号動き同じ関数
9合計SUM
3空白でない件数COUNTA
1平均AVERAGE
4 / 5最大 / 最小MAX / MIN
!

9 と 109 の違い

フィルタで隠れた行は、どちらも除く。違うのは行番号を右クリックして「非表示」にした行で、9 は含め、109 は除く。手で行を隠すことがあるなら 109 が安全。

小計を並べても、総合計が二重にならない

A社小計・B社小計をSUBTOTALで出し、総合計 =SUBTOTAL(9,C2:C6) が小計を二重に足さず 163,000 になっている画面
総合計は 163,000。SUBTOTALは範囲の中の他のSUBTOTALを無視する。 SUMで書くと小計まで足して 326,000 になる。

使い分け

やりたいこと使うもの
その場で条件を変えながら見たいフィルタ+SUBTOTAL
決まった条件で毎回同じ数字を出したいSUMIFS

調べるときはSUBTOTAL、報告書に載せるならSUMIFS。 誰かがフィルタを掛け替えると数字が変わるので、毎月決まった集計はSUMIFSで。

よくあるつまずき

×

フィルタを掛けても数字が変わらない

=SUM(…) のまま。合計セルの数式バーが SUBTOTAL で始まっているか見る。

×

別シートを参照したら動かない

SUBTOTALは「その表のフィルタ状態」を見ます。シートをまたぐ集計はSUMIFSで。

!

SUBTOTALの結果を、そのまま貼り付けない

値で貼り付けると、そのときの絞り込み結果が固定されます。「A社・9月分」のように条件を書き添える。

次に読むとよい記事


「絞り込んで報告した数字が、あとで違っていた」という経験があれば、表の作り方から見直せます。現状のヒアリングと課題整理は無料です。

#Excel#SUBTOTAL#集計#フィルタ

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

記事一覧へ戻る