この記事は 事務でよく使うExcel関数まとめ｜目的から探せる一覧 の1本です。他の関数もまとめて見られます。
フィルタでA社だけに絞ったのに、一番下の合計が全社の金額のまま動かない。
明細表を使っていれば、必ず一度は出会います。原因は操作ミスではありません。SUM は、画面に出ていない行も数えているからです。
この記事で扱う SUBTOTAL（サブトータル） は、いま画面に見えている行だけを集計する関数です。フィルタを掛け替えるたびに、合計が自動で追いかけてきます。
この記事で使うデータ
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 日付 | 取引先 | 商品 | 金額 |
| 2 | 2026-09-01 | A社 | 杉板 | 60,000 |
| 3 | 2026-09-03 | B社 | 檜柱 | 76,000 |
| 4 | 2026-09-05 | A社 | 合板 | 27,000 |
| 5 | 2026-09-08 | C社 | 杉板 | 15,000 |
| 6 | 2026-09-12 | A社 | 集成材 | 42,000 |
まず、何が起きているのか
一番下に =SUM(D2:D6) を入れて、取引先を A社だけに絞ります。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 日付 | 取引先 | 商品 | 金額 |
| 2 | 2026-09-01 | A社 | 杉板 | 60,000 |
| 3 | 2026-09-05 | A社 | 合板 | 27,000 |
| 4 | 2026-09-12 | A社 | 集成材 | 42,000 |
| 5 | 合計 | 220,000 |
見えているのは129,000円分なのに、表示は220,000円。
SUM は、フィルタで隠れた行も、変わらず足し続けます。「非表示になっただけで、データは残っている」ためです。Excelとしては正しい動きですが、実務では困ります。
この状態で数字を資料に転記すると、事故になります
「A社の売上を調べて」と言われて絞り込み、
一番下の合計をそのままコピーする。よくある流れです。
絞ったつもりで、全社の数字を報告してしまいます。
しかも、数字自体は間違っていないので、見直しても気づけません。
SUBTOTALの形
- 集計番号何をするかを数字で指定する。合計なら 9
- 範囲集計したい範囲。合計する列をそのまま指定する
ここだけが他の関数と違います。「合計する」「数える」「平均する」を、関数名ではなく 番号で指定します。
STEP1：SUMをSUBTOTALに置き換える
さきほどの =SUM(D2:D6) を、こう書き換えます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 日付 | 取引先 | 商品 | 金額 |
| 2 | 2026-09-01 | A社 | 杉板 | 60,000 |
| 3 | 2026-09-05 | A社 | 合板 | 27,000 |
| 4 | 2026-09-12 | A社 | 集成材 | 42,000 |
| 5 | 合計 | 129,000 |
B社とC社を表示に戻せば、すぐ220,000円へ戻ります。フィルタを掛け替えるたびに、合計が追いかけてきます。
書き換えるのは、関数名と先頭の 9 だけです
=SUM(D2:D6)
↓=SUBTOTAL(9, D2:D6)
範囲はそのままで構いません。既存のファイルでも、合計セルを直すだけで済みます。
集計番号は、よく使う6つだけ覚えれば足ります
番号は11種類ありますが、事務で使うのはこのあたりです。
| 番号 | 動き | 同じことをする関数 |
|---|---|---|
| 9 | 合計する | SUM |
| 1 | 平均を出す | AVERAGE |
| 2 | 数値の件数を数える | COUNT |
| 3 | 空白でない件数を数える | COUNTA |
| 4 | 最大値 | MAX |
| 5 | 最小値 | MIN |
まず 9（合計）と 3（件数）の2つです。この2つで、実務のほとんどは足ります。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 日付 | 取引先 | 商品 | 金額 |
| 2 | 2026-09-01 | A社 | 杉板 | 60,000 |
| 3 | 2026-09-05 | A社 | 合板 | 27,000 |
| 4 | 2026-09-12 | A社 | 集成材 | 42,000 |
| 5 | 合計 | 3件 | 129,000 |
合計と件数を並べておく。これだけで、絞り込んだ結果がどういう中身なのか、一目で分かるようになります。
9 と 109 の違い
番号には、100を足した版があります。9 に対して 109、3 に対して 103 です。
違いは1点だけです。
| フィルタで隠れた行 | 右クリックで「非表示」にした行 | |
|---|---|---|
| 9 | 除く | 含める |
| 109 | 除く | 除く |
手で行を隠すことがあるなら、109を使ってください
フィルタで絞るだけなら、9 でも 109 でも結果は同じです。
違いが出るのは、行番号を右クリックして「非表示」を選んだときだけ。
このとき 9 は、隠した行も足し続けます。
判断に迷うなら 109 を使っておけば安全です。
「見えている行だけ」という意味では、109のほうが素直です。
小計を並べても、総合計が二重にならない
SUBTOTALの本当に便利なところは、ここです。
取引先ごとに小計を入れ、一番下に総合計を置く。この形を SUM で作ると、小計の分まで足してしまい、金額が倍になります。
SUBTOTALは、範囲の中にある他のSUBTOTALを無視します。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 取引先 | 商品 | 金額 | 小計 |
| 2 | A社 | 杉板 | 60,000 | |
| 3 | A社 | 合板 | 27,000 | |
| 4 | A社小計 | 87,000 | ||
| 5 | B社 | 檜柱 | 76,000 | |
| 6 | B社小計 | 76,000 | ||
| 7 | 総合計 | 163,000 |
もし =SUM(D2:D9) で書いていたら、326,000円（正しい金額の2倍）になっていました。
小計行を手で除外する必要がありません
=SUM(D2:D3, D5:D5) のように範囲を飛ばして書く方法もありますが、
行が増えるたびに式を直すことになります。
SUBTOTALなら、範囲をまとめて指定するだけ。
行が増えても、小計を足しても、式は触らなくて済みます。
テーブルなら、自動で入ります
範囲を選んで Ctrl+T でテーブルにしておくと、SUBTOTALを手で書く必要すらありません。
- 表のどこかをクリックして Ctrl+T
「先頭行をテーブルの見出しとして使用する」にチェックを入れてOK。
- リボンの「テーブルデザイン」タブを開く
テーブル内をクリックしているときだけ出るタブです。
- 「集計行」にチェックを入れる
表の一番下に集計行が追加されます。
- 集計行のセルをクリックし、▼から「合計」を選ぶ
自動で
=SUBTOTAL(109, ...)が入ります。
入るのは109のほうです。Excel自身が「見えている行だけ」を標準としている、ということでもあります。
SUBTOTALを使わないほうがよい場面
条件が最初から決まっているなら、SUBTOTALは向きません。
「A社の合計」を毎月出すなら、フィルタを使わない
SUBTOTALは「いま画面に出ている分」を集計します。
つまり、誰かがフィルタを掛け替えると数字が変わります。
毎月決まった集計をするなら、条件を式の中に書いてしまうべきです。
フィルタ操作が要らなくなり、人によって結果が変わることもなくなります。
使い分けはこうなります。
| やりたいこと | 使うもの |
|---|---|
| その場で条件を変えながら見たい | フィルタ + SUBTOTAL |
| 決まった条件で、毎回同じ数字を出したい | SUMIFS |
調べるときはSUBTOTAL、報告書に載せるならSUMIFS。この分け方で考えると迷いません。
よくあるつまずき
フィルタを掛けても数字が変わらない
=SUM(...) のままになっています。合計セルを選んで、
数式バーが SUBTOTAL で始まっているか確認してください。
なお、合計セルが範囲の中に入っていると循環参照になります。=SUBTOTAL(9, D2:D7) の D7 が合計セル自身、という書き方はできません。
別シートを参照したら、うまく動かない
SUBTOTALは「その表のフィルタ状態」を見て動きます。
他のシートの行が隠れているかどうかは判断できません。
シートをまたぐ集計は、SUMIFSなど条件で絞る関数を使ってください。
SUBTOTALの結果を、そのまま貼り付けないでください
コピーして別の場所に値貼り付けすると、そのときの絞り込み結果が固定されます。
あとから見た人には、何で絞った数字なのか分かりません。
貼り付けるときは、「A社・9月分」のように条件を必ず書き添えてください。
今日やってみること
- フィルタを使っている明細表を1つ開く
売上明細、作業記録、在庫表。どれでも構いません。
- 一番下の合計セルを選んで、数式バーを見る
=SUM(で始まっていたら、この記事の対象です。 SUMをSUBTOTAL(109,に書き換える範囲はそのままです。閉じカッコの数に注意してください。
- フィルタを掛けて、合計が動くか確かめる
動けば完了です。
- できれば、横に件数も並べる
=SUBTOTAL(103, 取引先の列)で件数が出ます。
フィルタを掛けたときに数字が追いかけてくるだけで、確認の手間がはっきり減ります。Excelを「見るための道具」として使うなら、まず入れておきたい関数です。
次に読むとよい記事
- そもそもの合計のしかたを確認する → SUM関数の使い方
- 決まった条件で毎回同じ数字を出す → SUMIFS関数の使い方
- 条件に合う件数を数える → COUNTIFS関数の使い方
- 範囲を固定してコピーできる式にする → 絶対参照（$）の使い方
- 関数の一覧から探す → 事務でよく使うExcel関数まとめ
「毎月フィルタを掛け直して、数字を拾って、資料に打ち込んでいる」という作業は、仕組みごと減らせることがほとんどです。現状のヒアリングと課題整理は無料です。
この記事について
SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。
あわせて読みたい
SUM関数の使い方｜行を足しても合計が狂わない書き方
Excelの合計は、Σ（オートSUM）ボタンを押せば入ります。ほとんどの方がそうしているはずです。 にもかかわらず、「合計が合わない」という相談は今でも…
SUMIFS関数の使い方｜条件に合う行だけを合計する
毎月の売上を取引先ごとに集計するとき、フィルタをかけて、選択して、画面右下の合計を見て、電卓で足して……という作業をしていませんか。 この記事で扱う S…
TEXT関数と日付の処理｜「2026/9/1」を「9月」にまとめる
「月ごとの売上を出してほしい」と言われて、明細表を開く。日付は1日単位で入っているので、そのままでは月でまとめられません。 手で「9月」と打っていくと、…