今回は、ExcelのSUBTOTAL関数を使い、フィルター後の表を集計しやすくする方法を紹介します。
SUBTOTALが向いている集計
Excelで一覧表を扱うとき、合計や平均をSUM関数やAVERAGE関数で求めることはよくあります。ただし、フィルターで表示する行を絞った後も、通常の合計は非表示の行を含めて計算されます。画面で見えている行だけを確認したいときは、結果が感覚と合わなくなることがあります。
SUBTOTAL関数は、フィルターと組み合わせて使いやすい集計関数です。表示されている行を対象に合計、平均、件数、最大、最小などを計算できます。絞り込みながら状況を確認する一覧表では、通常の合計欄よりSUBTOTALのほうが扱いやすい場面があります。
たとえば、案件一覧で担当者を絞る、経費一覧で費目を絞る、在庫表で分類を絞るといった場面で、表示中の行だけの合計を確認できます。別シートに集計表を作るほどではないが、一覧を見ながら確認したい場合に向いています。
基本の書き方
SUBTOTAL関数は、集計方法を示す番号と、集計したい範囲を指定します。
合計を出す例
金額がD列に入っている場合、次のように指定します。
=SUBTOTAL(9,D2:D100)この式では、9が合計を表します。D2からD100までを対象に、フィルターで表示されている行を中心に合計します。
件数を数える例
入力済みの件数を数えたい場合は、COUNTAに相当する番号を使います。
=SUBTOTAL(3,A2:A100)この式では、A列に何か入力されている行を数えます。担当者や区分でフィルターをかけると、表示中の件数を確認できます。
よく使う集計番号
SUBTOTALでは、集計方法ごとに番号が決まっています。すべてを覚える必要はありません。日常業務では、よく使うものだけ押さえておくと十分です。
- 1: 平均
- 2: 数値の個数
- 3: 空白でないセルの個数
- 4: 最大値
- 5: 最小値
- 9: 合計
合計だけでなく、平均や件数も同じ考え方で使えます。売上や金額なら合計、対応日数なら平均、申請一覧なら件数のように、表の目的に合わせて選びます。
集計行を置く位置
SUBTOTALの式は、表の上か下に置きます。おすすめは、フィルター操作をしながら結果を見やすい位置に置くことです。
表の上に置く場合
表の上に集計欄を置くと、行数が多い表でもスクロールせずに結果を確認しやすくなります。見出しの近くに「表示中の合計」「表示中の件数」といったラベルを置くと、通常の総合計との違いが伝わりやすくなります。
ただし、表の見出しと近すぎる位置に式を置くと、フィルター範囲に含まれることがあります。集計欄と表の間に空行を入れる、またはテーブル機能の集計行を使うなど、範囲の切り分けを意識します。
表の下に置く場合
表の下に集計欄を置くと、従来の合計欄に近い感覚で使えます。印刷する帳票では下部に合計があるほうが自然なこともあります。
行を追加する可能性がある表では、集計式の範囲が不足しないようにします。Excelのテーブル機能を使うと、行を追加したときに範囲が広がりやすくなります。
テーブル機能と組み合わせる
Excelのテーブル機能には、集計行を表示する機能があります。テーブルとして設定した範囲で集計行をオンにすると、列ごとに合計や平均などを選べます。この集計行でもSUBTOTALが使われます。
テーブルとSUBTOTALを組み合わせる利点は、範囲管理がしやすいことです。通常のセル範囲では、行を追加したときに式の範囲を直す必要が出る場合があります。テーブルなら、追加行が同じ表として扱われやすくなります。
- 見出し行を分かりやすくする
- 表の途中に空行を入れない
- 集計したい列のデータ形式をそろえる
- 集計行のラベルを用途に合わせて変える
テーブルの集計行は、フィルターで絞った結果に応じて変わります。普段からフィルターで確認する一覧なら、手作業で式を入れるより管理しやすくなります。
非表示行との違いに注意する
SUBTOTALは、フィルターで非表示になった行を除外して集計できます。一方で、手動で行を非表示にした場合の扱いは、指定する番号によって変わります。
集計番号には、1から11の番号と、101から111の番号があります。101以降を使うと、手動で非表示にした行も除外できます。たとえば、手動非表示も除いて合計したい場合は次のようにします。
=SUBTOTAL(109,D2:D100)フィルターだけを使う表なら9で足りることもありますが、行の非表示を併用する表では109を使うほうが意図に合う場合があります。表を共有するなら、どちらの考え方で集計しているかをラベルやメモで示しておくと誤解を減らせます。
運用しやすい表にするコツ
SUBTOTALを使う表では、集計欄の意味が伝わるようにします。単に「合計」と書くと、全体の合計なのか表示中の合計なのか分かりにくくなります。
- ラベルは「表示中の合計」「フィルター後の件数」のように書く
- 通常の総合計が必要なら別欄に分ける
- 集計式のセルには色や罫線で目印を付ける
- 集計対象の列に文字と数値を混在させない
- フィルター条件を変えた後に結果を見る習慣を作る
共有ファイルでは、集計式を誤って消さないように、集計欄だけ保護する方法もあります。入力する列と集計する列を分けておくと、表を使う人が迷いにくくなります。
確認ミスを減らす工夫
SUBTOTALを入れた表では、フィルター条件を変えた後に、条件そのものも確認します。合計値だけを見ていると、前の条件が残ったまま判断してしまうことがあります。
- フィルターがかかっている列を見出しで確認する
- 集計欄の近くに確認したい条件名を置く
- 総合計と表示中の合計を同じ位置に並べない
- 確認後はフィルターを解除して元の表に戻す
会議や共有作業で使う場合は、画面を見ている人全員が「今は絞り込み後の数値を見ている」と分かる状態にします。ラベルやセルの色を使い、通常の合計欄と混同しない見た目にしておくと扱いやすくなります。
まとめ
SUBTOTAL関数は、Excelでフィルター後の表示行を集計したいときに役立ちます。合計、平均、件数、最大、最小などを、一覧を絞り込みながら確認できます。
テーブル機能と組み合わせ、集計欄のラベルを分かりやすくしておくと、共有ファイルでも扱いやすくなります。通常の合計と表示中の合計を分けて考えることが、SUBTOTALを使ううえでの大切なポイントです。