今回は、ExcelのSUBTOTAL関数を使い、フィルターで表示されている行を中心に集計する方法を紹介します。
SUBTOTAL関数とは
SUBTOTAL関数は、合計、平均、件数、最大値、最小値などの集計を行う関数です。SUM関数などとの違いは、フィルターで非表示になった行を集計から除外できる点にあります。
売上一覧を部署や担当者で絞り込み、表示中のデータだけの合計を確認したい場合、SUM関数では非表示の行も含まれます。SUBTOTAL関数なら、フィルター条件を変えるたびに表示行へ合わせて結果が更新されます。
また、参照範囲内に別のSUBTOTAL関数がある場合、その小計を重ねて集計しない仕組みがあります。一覧の途中と全体に小計を置いても二重集計を避けやすいことが特徴です。
SUBTOTAL関数の基本構文
SUBTOTAL関数では、最初の引数に集計方法を示す番号を指定し、その後に集計対象の範囲を指定します。集計方法の番号によって、平均、件数、最大、最小、合計などを切り替えます。
同じ合計でも、手動で非表示にした行を含める番号と、除外する番号があります。フィルターで非表示になった行はどちらの系統でも除外されますが、行の非表示操作への扱いが異なります。
番号だけでは意味を覚えにくいため、数式を作る際は関数の入力候補を確認します。共有する表では、集計セルの見出しへ「表示行合計」などと記載し、何を集計しているか分かるようにします。
表示行の合計を求める
売上金額などを合計する場合は、合計に対応する集計番号と金額列の範囲を指定します。表へフィルターを設定し、条件を変えると集計結果も切り替わります。
集計対象へ見出しセルを含めても、文字列なので通常は合計へ影響しません。ただし、範囲の意味を明確にするため、データ部分だけを参照するか、Excelテーブルの列を構造化参照で指定すると管理しやすくなります。
新しい行を追加する運用では、固定されたセル範囲だと追加分が集計から漏れることがあります。Excelテーブルへ変換し、対象列を参照すると、行の追加へ対応しやすくなります。
フィルター非表示と手動非表示の違い
フィルターを使って条件外の行を隠した場合、SUBTOTAL関数はその行を集計から除外します。これは通常の小さい番号と、手動非表示も除外する大きい番号のどちらでも同様です。
行番号を右クリックして非表示にした場合は、選ぶ集計番号によって結果が変わります。手動非表示の行を含める集計と、除外する集計があるため、用途に合わせて選びます。
一時的に詳細行を隠しても全体合計を維持したい場合と、画面に見える行だけを集計したい場合では要件が異なります。非表示の方法と集計へ含める基準を先に決めることが重要です。
件数を数えるときの使い分け
件数を求める集計方法には、数値が入ったセルを数えるものと、空白以外のセルを数えるものがあります。
受注金額列の入力件数を数えるなら数値セルの件数、案件名や担当者名の登録件数を数えるなら空白以外の件数が適しています。数字に見えても文字列として保存されている場合は、数値の件数へ含まれません。
件数が想定より少ないときは、セルの表示形式だけでなく実際のデータ型を確認します。空白に見える数式が入ったセルは、集計方法によって件数へ含まれる場合があるため、元データの式も確認します。
平均、最大値、最小値を確認する
SUBTOTAL関数は合計だけでなく、表示中データの平均、最大値、最小値も求められます。フィルターで特定の店舗や期間を選び、平均単価や最大処理時間を確認するといった使い方ができます。
平均を使う場合は、空白と0の扱いに注意します。空白セルは通常、平均の計算対象から外れますが、0は値として含まれます。未入力を0で埋めている表では、平均値の意味が変わるため、データ入力ルールを確認します。
複数の指標を並べる場合は、合計、件数、平均などのラベルを明記します。同じフィルター条件へ連動する集計を表の上部へまとめると、条件変更後の結果を確認しやすくなります。
Excelテーブルの集計行で使う
Excelテーブルには、最下部へ集計行を表示する機能があります。各列のドロップダウンから合計、平均、件数などを選ぶと、内部でSUBTOTAL関数が設定されます。
数式を直接入力しなくても利用でき、フィルターで表示中の行へ連動します。列の追加や行の追加にも対応しやすいため、継続的に更新する一覧に向いています。
集計行へ独自の数式を入れることもできますが、テーブルの構造化参照とフィルター時の動作を確認します。通常のデータ行と集計行が混ざらないため、印刷範囲やグラフ範囲を設定する際にも区別しやすくなります。
小計機能との関係
「データ」タブの小計機能を使うと、並べ替えたデータのグループごとに小計行を挿入できます。このときもSUBTOTAL関数が使用されます。
たとえば部署ごとに並べ替え、部署が変わる位置へ売上合計を挿入できます。アウトラインも作成され、詳細行を折りたたんで小計だけを表示できます。
ただし、Excelテーブルのままでは小計機能を利用できない場合があります。また、元データへ行を追加してもグループ構造が自動で適切に更新されるとは限りません。定期的に更新する集計なら、ピボットテーブルのほうが扱いやすいこともあります。
参照範囲内のSUBTOTALを除外する仕組み
SUBTOTAL関数は、参照範囲内にある別のSUBTOTAL関数の結果を原則として集計から除外します。グループごとの小計と最終合計を同じ列へ置いたときに、小計を再度足して二重になることを防ぎます。
一方、SUM関数で作った途中合計は自動では除外されません。SUBTOTAL関数とSUM関数が混在する表では、最終合計が意図どおりか確認します。
AGGREGATE関数など別の集計関数が範囲内にある場合も、除外の挙動が同じとは限りません。複雑な集計表では、元データ領域と集計領域を分け、参照関係を明確にします。
集計結果が合わないときの確認項目
結果が予想と異なる場合は、次の順で確認します。
- 集計方法の番号が合計、件数、平均など目的に合っているか
- 手動非表示の行を含める番号か、除外する番号か
- 参照範囲に追加したデータが含まれているか
- 数値が文字列として保存されていないか
- 参照範囲内にSUM関数などの途中集計が混在していないか
フィルターを解除した状態と条件を設定した状態で結果を比較すると、非表示行の扱いを確認しやすくなります。テスト用に数行だけの表を作り、行の非表示とフィルターを別々に試す方法も有効です。
SUBTOTAL関数が向く場面と向かない場面
SUBTOTAL関数は、一つの一覧をフィルターで切り替えながら、表示中の合計や件数を確認する場面に向いています。入力一覧の上部へ確認用の指標を置く、テーブルの集計行を使うといった用途です。
複数条件を数式だけで指定して集計するならSUMIFS関数やCOUNTIFS関数、項目を縦横に組み替えて分析するならピボットテーブルが適しています。SUBTOTAL関数は、フィルター操作と連動する点に価値があります。
印刷する集計表では、フィルター条件が分からないと数字の意味を判断できません。タイトルや条件表示セルを用意し、どの範囲を絞り込んだ結果か分かるようにします。
実務での使い方のコツ
- 継続的に行を追加する一覧はExcelテーブルへ変換する
- 集計セルへ「表示行合計」など結果の意味を記載する
- 手動非表示を使う運用では、集計番号の種類を統一する
- 数値列に文字列や空白文字が混ざっていないか確認する
- 条件付きの結果を共有するときはフィルター条件も示す
シートを保護する場合は、利用者がフィルターを操作できる設定か確認します。集計結果だけ見えても条件を変更できなければ、SUBTOTAL関数の利点を生かせません。操作する人と編集する人の役割に合わせて保護範囲を決めます。
まとめ
ExcelのSUBTOTAL関数は、フィルターで非表示になった行を除外し、表示中のデータを合計、平均、件数などで集計できます。参照範囲内の別のSUBTOTAL関数を重ねて集計しないため、小計を含む一覧にも利用できます。
使用時は、集計方法の番号、手動非表示行の扱い、参照範囲、データ型を確認します。継続的に更新する表では、Excelテーブルと集計行を組み合わせると管理しやすくなります。画面に表示されている行と集計対象の関係を明確にすることが、SUBTOTAL関数を正しく使うポイントです。