非表示の行を数えないSUBTOTAL関数の使い方

SUBTOTAL関数の使い方

SUBTOTAL関数は、表の中で「見えている行だけ」を集計できます。見えている行は、一般に可視セルと呼ばれます。オートフィルターで条件を絞ると、条件に合わない行は非表示になります。このとき、SUBTOTALは非表示行を外して計算します。合計、平均、件数などの基本的な集計に対応しており、フィルターで一覧を切り替えながら、すぐに集計値を確認したい場面で役立ちます。

まずは代表的な書き方を確認します。

• 合計を求める例（9を使う）：`=SUBTOTAL(9, B2:B100)`
• 合計を求める例（109を使う）：`=SUBTOTAL(109, B2:B100)`

どちらも見た目は似ていますが、9と109には違いがあります。9は「フィルターで非表示になった行だけを除外」します。109は「フィルター非表示に加えて、手動で非表示にした行も除外」します。日々の作業で、行を右クリックして非表示にすることがあるなら、109を選ぶと安心です。

SUMとの違いも確認しておきます。SUMはセルの値を無条件で合計します。フィルターで行が隠れていても、手動で非表示にしていても、SUMは見えない行も数えます。表示中だけの合計が欲しいなら、SUMではなくSUBTOTALを使います。

フィルターとSUBTOTALの組み合わせはとても相性が良いです。条件を変えるたびに、SUBTOTALの結果が連動して変わるため、試行錯誤しながら傾向を見ることができます。例えば、担当者や月でフィルターすれば、その条件に合うレコードだけの売上合計がすぐに分かります。

実務では、SUBTOTALを表の最下行や列の右端に置くことが多いです。集計セルがフィルターで隠れない位置にあると、どの条件でも集計結果を参照しやすくなります。表をテーブル（Ctrl+T）にすると、データ範囲が伸びてもSUBTOTALの範囲が自動で追従しやすくなり、メンテナンスが楽になります。

SUBTOTAL関数の基本（可視セルの集計）

SUBTOTAL関数は、表の中で「見えている行だけ」を集計できます。見えている行は、一般に可視セルと呼ばれます。オートフィルターで条件を絞ると、条件に合わない行は非表示になります。このとき、SUBTOTALは非表示行を外して計算します。合計、平均、件数などの基本的な集計に対応しており、フィルターで一覧を切り替えながら、すぐに集計値を確認したい場面で役立ちます。

まずは代表的な書き方を確認します。

• 合計を求める例（9を使う）：`=SUBTOTAL(9, B2:B100)`
• 合計を求める例（109を使う）：`=SUBTOTAL(109, B2:B100)`

どちらも見た目は似ていますが、9と109には違いがあります。9は「フィルターで非表示になった行だけを除外」します。109は「フィルター非表示に加えて、手動で非表示にした行も除外」します。日々の作業で、行を右クリックして非表示にすることがあるなら、109を選ぶと安心です。

SUMとの違いも確認しておきます。SUMはセルの値を無条件で合計します。フィルターで行が隠れていても、手動で非表示にしていても、SUMは見えない行も数えます。表示中だけの合計が欲しいなら、SUMではなくSUBTOTALを使います。

フィルターとSUBTOTALの組み合わせはとても相性が良いです。条件を変えるたびに、SUBTOTALの結果が連動して変わるため、試行錯誤しながら傾向を見ることができます。例えば、担当者や月でフィルターすれば、その条件に合うレコードだけの売上合計がすぐに分かります。

実務では、SUBTOTALを表の最下行や列の右端に置くことが多いです。集計セルがフィルターで隠れない位置にあると、どの条件でも集計結果を参照しやすくなります。表をテーブル（Ctrl+T）にすると、データ範囲が伸びてもSUBTOTALの範囲が自動で追従しやすくなり、メンテナンスが楽になります。

二重集計の回避（小計を除いた集計）

明細行の中に小計行を入れている表では、合計を取ると「小計の小計」になってしまうことがあります。SUBTOTALは、関数の対象範囲の中に別のSUBTOTALがあると、それを自動的に無視します。つまり、小計行の値が二重に数えられることを避けられます。

二重集計を避ける基本の考え方は次の通りです。

• 明細行の列に対して、範囲全体をSUBTOTALで集計する。
• グループごとの小計は、同じくSUBTOTALで作る。
• 最後に全体合計もSUBTOTALで作れば、内部の小計は除外される。

例として、B列に金額があり、各カテゴリごとに小計行を設けるケースを考えます。

• 各カテゴリの小計行：`=SUBTOTAL(109, B開始:B終了)`
• 全体の合計：`=SUBTOTAL(109, B2:B100)`

この構造なら、全体合計は明細だけを集計し、カテゴリ小計は二重に含まれません。小計をSUMで作ると二重計上の原因になります。小計行でもSUBTOTALを使うことが大切です。

また、集計セルの置き場所もポイントです。フィルターで非表示になる領域の中に集計セルを置くと、意図せず数式自体が見えなくなることがあります。集計セルは基本的に表の外側や最後尾に置き、常に参照できるようにしましょう。

FAQ：フィルターの後に行を手動で隠すと結果はどう変わる？

• 9（1〜11のグループ）の場合：フィルターで非表示になった行は除外されるが、手動で非表示にした行は含まれる。
• 109（101〜111のグループ）の場合：フィルター非表示に加えて、手動で非表示にした行も除外される。
• よって、フィルターと手動非表示を併用する運用なら、109など100番台を選ぶと挙動が一貫しやすい。

SUBTOTAL関数の集計コードまとめ

SUBTOTALの第1引数は、どの種類の集計を行うかを示す番号です。1〜11と101〜111の2セットがあります。1〜11はフィルターで非表示になった行だけを除外します。101〜111はそれに加えて、手動の非表示（行を隠す操作）も除外します。

代表的なコードをまとめた表を示します。用途に応じて選んでください。

よく使うのは、合計なら9/109、個数なら3/103、平均なら1/101です。手動で行を隠す運用があるかどうかで100番台を選ぶかを決めると迷いません。

実際の数式例をいくつか示します。

• 可視セルだけの合計：`=SUBTOTAL(109, 売上[金額])`
• 可視セルだけの件数（空白以外）：`=SUBTOTAL(103, 売上[受注番号])`
• 可視セルだけの平均：`=SUBTOTAL(101, 売上[単価])`

上の例は、テーブル（Ctrl+T）で作成した表を想定しています。テーブル名が「売上」で、列名が角括弧で示されています。テーブル化の利点は、行が増減しても範囲が自動で広がる点です。通常のセル範囲でも同じように使えます。

注意点も整理します。

• 文字列が混ざる列で合計を取ると、文字列は無視されます。集計対象の列は数値化しておきます。
• エラー値が含まれていると、結果が期待と異なることがあります。必要に応じてIFERRORなどで前処理すると安定します。
• フィルター条件が複数でも、SUBTOTALは「見えている行だけ」を対象にします。条件の複雑さは関係ありません。
• 小計のセルそのものは除外されます（同じ範囲内にあるSUBTOTALは数えない仕様）。

FAQ：9と109はどちらを使えばよい？

• フィルターしか使わない運用なら、9で十分です。
• フィルター後に行を手動で非表示にすることがある、あるいはグループの折りたたみを使うなど「見た目に合わせて隠す」操作が多いなら、109を選ぶと安全です。
• チームでブックを共有し、操作が人によって異なる場合も、109を標準にしておくと齟齬が起きにくくなります。

よくある質問

Q. テーブル（Ctrl+T）でもSUBTOTALは正しく動きますか？

A. はい。テーブル化しても、SUBTOTALは可視セルだけを対象に集計します。構造化参照を使うと範囲が自動調整され、数式が読みやすくなります。例えば、テーブル名が売上で金額列の合計は `=SUBTOTAL(109, 売上[金額])` です。

Q. 可視セルだけをコピーしたいのですが、SUBTOTALと関係ありますか？

A. 関数ではなく操作になりますが、選択範囲の可視セルだけをコピーする機能があります。検索ボックスに「可視セルの選択」と入力してコマンドを使うか、Alt+; のショートカットが役立ちます。SUBTOTALの結果と合わせて、絞り込んだ明細だけを別シートに持って行くときに便利です。

Q. フィルター条件が複数の場合、正しく集計されますか？

A. はい。表示されている行だけが対象なので、複数条件でどれだけ絞っても、結果は常に現在表示中の行に一致します。条件設定に関係なく、考え方は同じです。

Q. エラー値が混ざる列はどう扱えばよいですか？

A. 事前にIFERRORで0や空白に置き換えておくと、合計や平均が安定します。エラーを含むセルが可視でも、SUBTOTALはそのセルの値を扱えないため、結果が望ましくない場合があります。

Q. ピボットテーブルとSUBTOTALはどう使い分けますか？

A. 細かいグループ別の集計やクロス集計を素早く行うならピボットテーブルが向いています。明細表をそのまま使い、条件で表示を切り替えながら合計や件数を見たい場合はSUBTOTALが向いています。両方を併用し、探索はSUBTOTAL、確定した集計はピボットという分担もよく使われます。

Q. 行をグループ化して折りたたんだ場合の挙動は？

A. 100番台（101〜111）を使うと、グループの折りたたみや手動の非表示も除外されます。1〜11の番号では、グループの折りたたみは除外対象にならないことがあります。見た目の表示に完全に合わせたい場合は、基本的に100番台を選びます。

Q. SUBTOTALで空白以外の件数を数えるには？

A. データの個数を表す3または103を使います。範囲に数値以外が含まれていても、空白だけを除外して数えたいときに便利です。例えば `=SUBTOTAL(103, C2:C100)` です。

Q. SUMと結果が合わないのはなぜ？

A. SUMは非表示かどうかに関係なく全てを合計します。SUBTOTALは可視セルだけを合計します。フィルターや手動非表示の有無で結果が違うのは仕様通りです。どちらの値が欲しいかで関数を選んでください。

Q. SUBTOTALが入っているセルをさらに集計しても大丈夫？

A. 同じ範囲内のSUBTOTALは自動的に除外されます。小計を積み上げても、最終のSUBTOTALは二重計上を避けます。SUMで重ねると二重計上になるので注意してください。