集計
SUBTOTAL関数の使い方・読み方
読み方：SUBTOTAL は「サブトータル」と読みます。（一般的な読み方です。公式に読み仮名の定めはありません）
SUBTOTALは、フィルタで絞り込んだ結果だけを集計する関数です。
このページの中身（24項目）
この記事の書式と対応環境の出どころ（2026-08-26 確認）
- Google スプレッドシート ヘルプ：公式の書式は
SUBTOTAL(関数コード, 範囲1, 範囲2)
https://support.google.com/docs/answer/3093649 - Microsoft サポート：SUBTOTAL 関数
https://support.microsoft.com/ja-jp/excel/functions/subtotal-function
関数の仕様は追加・変更されることがあります。書式が合わないときは、上の公式ページで最新をご確認ください。
SUM は隠れている行も足してしまいます。「フィルタで東京だけ表示したのに、合計が全社のまま」——これを解決するのがSUBTOTALです。
書式
=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)第1引数に番号で集計方法を指定するのが特徴です。
集計方法の番号
| 1桁 | 3桁 | 集計内容 |
|---|---|---|
| 1 | 101 | 平均（AVERAGE） |
| 2 | 102 | 数値の個数（COUNT） |
| 3 | 103 | 空欄でない個数（COUNTA） |
| 4 | 104 | 最大値（MAX） |
| 5 | 105 | 最小値（MIN） |
| 6 | 106 | 積（PRODUCT） |
| 7 | 107 | 標本標準偏差（STDEV） |
| 8 | 108 | 母標準偏差（STDEVP） |
| 9 | 109 | 合計（SUM） |
| 10 | 110 | 標本分散（VAR） |
| 11 | 111 | 母分散（VARP） |
実務で使うのは 9 と 109（合計）、次いで 3 と 103（件数）がほとんどです。
1桁と3桁の違い
ここが最も重要です。
| フィルタで隠れた行 | 手動で非表示にした行 | |
|---|---|---|
| 1桁（9など） | 除外する | 含める |
| 3桁（109など） | 除外する | 除外する |
つまり、
- フィルタだけ使うなら、どちらでも同じ結果
- 行を右クリックで非表示にすることがあるなら 3桁
迷ったら 109（3桁）を使ってください。 「見えている行だけ」という直感に一致します。
基本の使い方
| A | B | |
|---|---|---|
| 1 | 支店 | 売上 |
| 2 | 東京 | 1200 |
| 3 | 大阪 | 800 |
| 4 | 東京 | 1500 |
=SUBTOTAL(109, B2:B4)フィルタ無しなら 3500。フィルタで「東京」だけ表示すると 2700 に変わります。
数式を書き換えていないのに結果が変わる——これがSUBTOTALの働きです。
件数を数えるなら次の形です。
=SUBTOTAL(103, A2:A4)SUMとの使い分け
フィルタを使う表では、集計行は必ずSUBTOTALにしてください
SUMのままだと、絞り込んでも数字が変わらず、見る人が誤解します。これは実務で頻繁に起きるミスです。
小計が二重計上されない仕組み
SUBTOTALには、もうひとつ重要な性質があります。
範囲内に他のSUBTOTALがあると、それを無視します。
小計行をいくつも挟んだ表で、最後に総合計を出す場面を考えてください。
A列の途中に =SUBTOTAL(109, B2:B10) ← 小計
=SUBTOTAL(109, B12:B20) ← 小計
最終行に =SUBTOTAL(109, B2:B21) ← 総合計普通の SUM で総合計を出すと、小計まで足してしまい2倍になります。 SUBTOTALなら小計を自動で除外するので、正しい総合計が出ます。
小計を含む表では、すべてSUBTOTALで統一するのが定石です。
フィルタで隠れた行だけを数える
「絞り込みで何件除外されたか」を出したいなら、全件から表示件数を引きます。
=COUNTA(A2:A100) - SUBTOTAL(103, A2:A100)表の一番上に集計を置く
集計行を表の上に置くと、フィルタをかけたときにスクロールせず確認できます。
ただし、集計行が範囲に含まれないよう注意してください。表が2行目から始まるなら、集計は1行目に置き、範囲は A2:A1000 とします。
なお、集計行を表の上に置くと、フィルタの範囲に含まれてしまうことがあります。フィルタは表の見出し行から設定してください。
Excelとの違い
SUBTOTALは共通です
番号の意味も1桁/3桁の違いも同じです。
違いは代替手段です。
| Excel | スプレッドシート | |
|---|---|---|
AGGREGATE（エラー無視＋非表示除外） | ある | ない |
| QUERY | ない | ある |
Excelの AGGREGATE は、SUBTOTALの機能に加えてエラー値を無視できます。範囲に #N/A が混ざっていてもエラーになりません。スプレッドシートにはこれがないので、元のエラーを IFERROR で潰しておく必要があります。
一方スプレッドシートでは、そもそもフィルタを使わずに QUERY や FILTER で絞り込む書き方が使えます。
=SUM(FILTER(B2:B100, A2:A100="東京"))画面のフィルタ状態に依存しないぶん、こちらのほうが再現性は高いです。人に渡すファイルではこちらを検討する価値があります。
フィルタと連動するという性質
小計を求める関数の最大の特徴は、フィルタで非表示になった行を集計から除外することです。通常の合計関数は、隠れている行も含めて計算します。この違いが、実務では決定的な意味を持ちます。
たとえば、取引一覧に対して特定の顧客だけを絞り込んだとき、画面に見えている行の合計が知りたくなります。通常の合計関数を表の下に置いていると、絞り込んでも数字が変わりません。全体の合計が表示され続けます。小計の関数を使えば、絞り込むたびに合計が変わり、その時点で見えている行だけの数字になります。
この動きは、データを眺めながら条件を変えて確認する作業と非常に相性が良いものです。条件付き集計の数式を書かなくても、フィルタの操作だけで様々な切り口の集計が見られます。
ただし、集計方法を番号で指定する必要があります。合計、平均、件数、最大、最小など、それぞれに番号が割り当てられています。この番号を覚えるのが最初のハードルですが、よく使うのは合計と件数と平均の三つなので、実質的にはそれだけ覚えれば足ります。
手動で非表示にした行の扱い
集計方法の番号には二系統あります。一桁の番号と、百番台の番号です。この違いは、手動で非表示にした行を含めるかどうかです。
一桁の番号は、フィルタで非表示になった行は除外しますが、手動で行を非表示にした場合は含めます。百番台の番号は、フィルタでも手動でも、非表示の行はすべて除外します。
実務では、この違いが混乱の元になります。フィルタだけを使っているなら差は出ませんが、行を右クリックして非表示にする操作を併用していると、結果が変わります。
方針としては、常に百番台を使うと決めてしまうのが分かりやすくなります。見えていない行は数えない、という一貫した動きになるためです。他人が触るファイルでも、説明が単純になります。
ただし、意図的に一時的な非表示を使い、それでも集計には含めたい場面もあります。その場合は一桁の番号を選びます。どちらを使っているかを、集計行の近くに書き添えておくと親切です。
入れ子にしたときの挙動
小計の関数には、もうひとつ便利な性質があります。範囲の中に別の小計の関数が入っている場合、それを二重に数えません。
これが役に立つのは、階層のある集計表です。部門ごとの小計行を挟みながらデータが並んでいて、最後に全体の合計を出したい場合。通常の合計関数で全体を合計すると、小計行の数字も足されてしまい、実際の倍になります。小計の関数なら、範囲の中の小計行を自動的に無視するので、正しい合計になります。
同じ理由で、途中に小計行を追加しても全体の合計は壊れません。行を挿入するたびに数式を直す必要がなくなります。
この性質を知らないと、階層のある集計表を作るたびに範囲を細かく指定して足し合わせることになります。数式が複雑になり、行を追加するたびに直す必要が出ます。
なお、通常の合計関数と混在させると、この自動的な除外は働きません。階層のある表では、すべての集計行を小計の関数で統一してください。
連番を振るという応用
やや変わった使い道として、フィルタで絞り込んだ後も連番が飛ばない番号を振る、という使い方があります。
通常、行番号から連番を作ると、絞り込んだときに番号が飛びます。三番の次が七番になる、といった見え方です。印刷して配る資料では不自然に見えます。
小計の関数で件数を数える方法を使い、自分の行までの範囲を対象にすると、見えている行だけを数えた番号になります。絞り込むたびに番号が振り直され、常に一から連続します。
範囲の指定は、開始行を固定し、終了行を自分の行にします。下にコピーすると範囲が広がっていくので、その行までに見えている件数が返ります。
印刷を前提とした一覧表では、この一手間が仕上がりの印象を変えます。手作業で番号を振り直す必要もなくなります。
フィルタを前提とした表の作り方
フィルタと連動する集計を活かすには、表そのものをフィルタ前提で設計する必要があります。
見出しは一行だけにします。二段の見出しがあると、フィルタが正しく認識されません。
データの範囲に空白行を作りません。空白行があると、フィルタの範囲がそこで途切れます。
集計行は、データの範囲から離して置きます。表のすぐ下に置くと、フィルタの対象に含まれてしまい、絞り込んだときに一緒に隠れることがあります。表の上部に置くのが安全です。上部にあれば、スクロールしても見え続けるという利点もあります。
セルの結合は使いません。結合されたセルがあると、フィルタも並べ替えも正しく動きません。
列見出しは重複させません。同じ名前の列が複数あると、どれを絞り込んでいるか分からなくなります。
この五点を守った表であれば、フィルタで自由に切り口を変えながら、常に正しい集計が表示されます。条件付きの集計関数を大量に書く必要がなくなります。
集計方法を切り替える仕組み
集計方法を番号で指定する性質を利用すると、表示する集計を切り替える仕組みが作れます。
集計方法の番号をセルに置き、その番号を参照する形で数式を書きます。番号を変えれば、合計にも平均にも件数にも切り替わります。
さらに、プルダウンで集計方法を選べるようにすれば、番号を覚える必要もなくなります。表示名と番号の対応表を作り、選んだ表示名から番号を引く形にします。
この仕組みは、同じ表を複数の見方で確認したい場合に有効です。合計で全体を把握し、平均で水準を見て、件数で規模を確認する。表を作り直さずに切り替えられます。
注意点として、集計方法によって意味が変わる列があります。金額の列は合計も平均も意味がありますが、単価の列は合計に意味がありません。切り替えられるようにしても、意味のある組み合わせだけを使ってください。
見出しにも集計方法を表示するようにしておくと、今どの集計を見ているかが分かります。選んだ表示名をそのまま見出しに反映すれば済みます。
「集計」の関数を全体から見る
数値をまとめる関数群です。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに48本あります。
このカテゴリで共通するつまずき
空欄と0の扱いが結果を左右します。未入力を0で埋めると平均が下がります。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
条件の数で使う関数が変わります。将来増えるなら複数条件に対応する形にしてください。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
先に決めておけば、書くのは数分で終わります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
空欄と0の扱いが結果を左右します。未入力を0で埋めると平均が下がります。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
条件の数で使う関数が変わります。将来増えるなら複数条件に対応する形にしてください。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
うまくいかないときに見る場所は、ほぼ決まっています。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
収録は201本です。カテゴリごとの内訳を出しておきます。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 |
| 集計 | 47本 ←この記事 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. フィルタをかけても数字が変わりません
通常の合計関数を使っています。小計の関数に置き換えてください。
Q. 集計方法の番号が覚えられません
よく使うのは合計、件数、平均の三つです。数式を入力するときに候補が表示されるので、その場で選べます。
Q. 手動で隠した行も除きたいのですが
百番台の番号を使ってください。一桁の番号では含まれます。
Q. 小計行が二重に数えられます
範囲の中に通常の合計関数が混ざっています。すべて小計の関数に統一してください。
Q. 条件を付けて集計できますか
この関数には条件を指定する機能がありません。条件付きの集計が必要なら、条件付きの集計関数を使うか、フィルタで絞ってからこの関数を使ってください。
Q. 空白行があっても大丈夫ですか
問題ありません。空白は無視されます。
Q. 別のシートの範囲を集計できますか
できますが、別シートの表示状態には連動しません。同じシート内で使うのが本来の用途です。
Q. より柔軟な集計をしたいのですが
集計方法をより細かく指定できる関数もあります。ただし覚えることが増えるので、フィルタとの連動が目的ならこの関数で足ります。
この分野の他の記事
同じ「集計」の記事です。目的が近いので、こちらで解決しない場合はこちらも見てください。
まとめ
- 書式は
=SUBTOTAL(集計方法, 範囲) - 合計は 109、件数は 103 を覚えておけば足りる
- 1桁は手動非表示を含み、3桁は除外する。迷ったら3桁
- フィルタを使う表の集計行は、必ずSUBTOTALにする
- 範囲内の他のSUBTOTALを無視するので、小計と総合計が二重にならない