SUBTOTAL関数とは？フィルター後の合計に使える理由
Excelで売上表や経費一覧を扱っていると、「フィルターで絞り込んだ行だけ合計したい」と思う場面はよくあります。たとえば、営業担当者ごとの売上だけを表示したり、特定の月の経費だけを抽出したりするケースです。
このとき、通常のSUM関数を使っていると注意が必要です。SUM関数は、フィルターで非表示になっている行も含めて合計してしまうため、画面上に表示されている金額と合計値が一致しないことがあります。
そこで活躍するのがSUBTOTAL関数です。SUBTOTAL関数は、フィルターで表示されている行だけを対象にして、合計や平均、件数などを計算できる関数です。つまり、表を絞り込むたびに、表示中のデータだけを自動で集計してくれます。
SUBTOTAL関数の基本的な考え方は、「集計方法を指定して、対象範囲を計算する」というものです。合計を出したい場合は、集計方法として9を指定します。
=SUBTOTAL(9, 範囲)たとえば、B2からB100までの金額をフィルター後の表示行だけで合計したい場合は、次のように入力します。
=SUBTOTAL(9, B2:B100)この式を使うと、フィルターで一部の行を非表示にしたとき、その非表示行は合計から除外されます。営業資料や月次レポートを作るときに、条件を変えるたびに合計値も自動で変わるため、手作業で計算し直す必要がありません。
20代のビジネスパーソンにとって、Excel作業の効率化はそのまま仕事のスピードアップにつながります。特に、上司への報告資料やチームの数値管理では、「今表示しているデータだけの合計」を正確に出せることが重要です。
SUBTOTAL関数は、難しい関数に見えるかもしれませんが、合計だけなら覚える形はシンプルです。まずは「フィルター後の合計にはSUBTOTAL(9, 範囲)を使う」と覚えておくと、日々のExcel作業でかなり役立ちます。
フィルターで表示された行だけを合計する基本式
フィルターで表示された行だけを合計したい場合は、SUBTOTAL関数を使って次のように入力します。
=SUBTOTAL(9, 合計したい範囲)ポイントは、最初の引数に9を指定することです。9は「合計」を意味する番号で、フィルターによって非表示になった行を除外して計算してくれます。
たとえば、次のような売上表があるとします。
| 日付 | 担当者 | 商品 | 売上金額 |
|---|---|---|---|
| 4/1 | 田中 | A商品 | 50,000 |
| 4/2 | 佐藤 | B商品 | 30,000 |
| 4/3 | 田中 | C商品 | 40,000 |
売上金額がD2:D100に入力されている場合、フィルター後に表示されている売上だけを合計する式は次のとおりです。
=SUBTOTAL(9, D2:D100)この数式は、表の下や集計用のセルに入力しておくのがおすすめです。たとえばD101に入力しておけば、担当者で「田中」だけに絞り込んだときは田中さんの売上合計、商品で「A商品」だけに絞り込んだときはA商品の売上合計が自動で表示されます。
実際の操作手順はシンプルです。
- 合計したい表を用意する
- 表にフィルターを設定する
- 合計を表示したいセルに
=SUBTOTAL(9, 範囲)を入力する - フィルター条件を変更して、合計値が変わるか確認する
フィルターは、Excelの上部メニューにある「データ」タブから「フィルター」を選択すると設定できます。見出し行に▼マークが表示されたら、担当者名や日付、商品名などで条件を絞り込めます。
注意点として、数式を入力するセルは、できるだけ集計対象の範囲外に置きましょう。合計したい範囲の中にSUBTOTALの式まで含めてしまうと、意図しない計算になる場合があります。売上金額がD2:D100なら、合計セルはD101や表の上部など、データと分けて配置すると見やすくなります。
また、範囲に空白セルが含まれていても基本的には問題ありません。売上表のように後から行が追加される可能性がある場合は、少し広めにD2:D1000のように指定しておくと、都度数式を修正する手間を減らせます。
つまり、フィルター後の合計を出したいときは、まず=SUBTOTAL(9, 金額の範囲)を覚えておけばOKです。条件を変えるたびに合計も自動で切り替わるため、確認作業や報告資料の作成がかなりラクになります。
SUM関数との違い｜SUBTOTALを使うべき場面
SUM関数とSUBTOTAL関数の大きな違いは、「フィルターで非表示になった行を合計に含めるかどうか」です。
SUM関数は、指定した範囲内の数値をそのまま合計します。たとえば、=SUM(D2:D100)と入力した場合、フィルターで一部の行を非表示にしても、D2:D100に入っている金額はすべて合計対象になります。
一方、SUBTOTAL関数で=SUBTOTAL(9, D2:D100)と入力しておけば、フィルターで表示されている行だけを合計します。つまり、画面上で見えているデータと合計値が連動するのが特徴です。
| 関数 | フィルターで非表示の行 | 向いている場面 |
|---|---|---|
SUM |
合計に含める | 全データの合計を出したいとき |
SUBTOTAL |
合計から除外する | 絞り込み後の合計を出したいとき |
たとえば、月間売上の全体合計を見たいだけならSUM関数で問題ありません。フィルター条件に関係なく、表全体の合計を固定で確認したい場合には、むしろSUM関数のほうがシンプルです。
しかし、担当者別・商品別・エリア別など、フィルターで条件を切り替えながら合計を確認したい場合は、SUBTOTAL関数を使うべきです。表示内容を変えるたびに合計値も自動で変わるため、毎回範囲を選び直したり、別の表を作ったりする必要がありません。
特に仕事でよくあるのが、上司から「この商品の売上だけ見せて」「関東エリアだけの合計は？」と聞かれる場面です。このときSUM関数のままだと、フィルターで絞っても全体合計が表示され続けるため、誤った数字を伝えてしまうリスクがあります。
また、SUBTOTAL関数には、範囲内に別のSUBTOTAL関数があった場合、その結果を二重に集計しにくいというメリットもあります。小計を含む表で集計する場合にも使いやすく、売上管理表や経費一覧との相性が良い関数です。
使い分けの目安としては、「全体を固定で合計したいならSUM」「フィルター後の見えている行だけ合計したいならSUBTOTAL」と覚えておきましょう。
Excelで集計ミスを減らすには、関数の使い分けが重要です。フィルターを使う表では、最初からSUBTOTAL関数を入れておくと、確認作業がスムーズになり、報告する数字の信頼性も高まります。
非表示行も除外したいときの指定番号の選び方
SUBTOTAL関数で合計を出すときは、基本的に9を使えばフィルター後の表示行だけを合計できます。ただし、ここで注意したいのが「手動で非表示にした行」の扱いです。
Excelでは、フィルターで行が非表示になる場合と、行番号を右クリックして「非表示」を選ぶ場合があります。どちらも画面上は見えなくなりますが、SUBTOTAL関数では指定番号によって集計対象が変わります。
| 指定番号 | 計算内容 | 手動で非表示にした行 |
|---|---|---|
9 |
合計 | 含める |
109 |
合計 | 除外する |
つまり、フィルターで絞り込んだ行だけを合計したい場合は、これまで紹介したように次の式で問題ありません。
=SUBTOTAL(9, D2:D100)一方で、フィルターだけでなく、手動で非表示にした行も合計から外したい場合は、指定番号を109にします。
=SUBTOTAL(109, D2:D100)たとえば、売上表の中に「確認中のデータ」や「一時的に除外したいデータ」があり、その行を手動で非表示にしている場合、9ではその金額が合計に含まれてしまいます。画面上では見えていないのに、合計には入っているため、「表示されている金額を足した合計」とズレて見えることがあります。
このようなズレを防ぎたいなら、合計には109を使うと覚えておくと安心です。特に、複数人で共有しているExcelファイルでは、誰かが手動で行を非表示にしているケースもあります。正確に「今見えている行だけ」の合計を出したいなら、109を選ぶほうが安全です。
なお、合計以外にもSUBTOTAL関数には平均や件数などの指定番号があります。よく使うものは次のとおりです。
| 計算内容 | 通常の指定番号 | 手動非表示も除外 |
|---|---|---|
| 平均 | 1 |
101 |
| 数値の個数 | 2 |
102 |
| データの個数 | 3 |
103 |
| 合計 | 9 |
109 |
使い分けの目安はシンプルです。フィルターだけを考えるなら9、手動で非表示にした行も除外したいなら109です。
仕事で使う表では、後から行を非表示にしたり、他の人が加工したりすることも少なくありません。集計ミスを防ぐためにも、見えている行だけを確実に合計したい場面では、最初から=SUBTOTAL(109, 範囲)を使っておくと安心です。
仕事で使える実例｜売上表・経費表での活用方法
ここからは、実際の仕事でSUBTOTAL関数をどう使うかを具体例で見ていきましょう。特に使いやすいのが、売上表と経費表です。どちらもフィルターで条件を切り替えながら合計を見る場面が多く、SUBTOTALとの相性が抜群です。
売上表で担当者別・商品別の合計を確認する
たとえば、次のような売上管理表があるとします。
| 日付 | 担当者 | 商品 | エリア | 売上金額 |
|---|---|---|---|---|
| 4/1 | 田中 | A商品 | 東京 | 50,000 |
| 4/2 | 佐藤 | B商品 | 大阪 | 30,000 |
| 4/3 | 田中 | A商品 | 東京 | 40,000 |
売上金額がE2:E100に入っている場合、表の下に次の式を入れておきます。
=SUBTOTAL(109, E2:E100)この状態で「担当者」を田中さんに絞れば、田中さんの売上合計だけが表示されます。さらに「商品」をA商品に絞れば、田中さんが販売したA商品の売上だけを確認できます。会議前に「担当者別の実績をすぐ見たい」「特定商品の売れ行きを確認したい」というときに便利です。
経費表で部署別・費目別の合計を出す
経費表でもSUBTOTALはよく使えます。たとえば、交通費・接待交際費・備品費などが混在している一覧で、費目ごとの合計を確認したい場合です。
=SUBTOTAL(109, F2:F200)経費金額がF列にあるなら、上記の式を入れておきます。そのうえで、フィルターを使って「部署：営業部」「費目：交通費」のように絞り込めば、営業部の交通費だけをすぐに合計できます。
月末の経費チェックでは、「今月の広告費はいくらか」「部署ごとの出張費が増えていないか」など、条件を変えながら数字を見ることが多いはずです。SUBTOTALを使っておけば、フィルターを変更するだけで合計も自動更新されるため、別シートに集計表を作る前の確認作業にも役立ちます。
表の上に合計欄を置くと確認しやすい
実務では、合計セルを表の下だけでなく、表の上に置くのもおすすめです。データ件数が多いと、毎回一番下までスクロールするのが手間だからです。
| 項目 | 数式例 |
|---|---|
| 表示中の売上合計 | =SUBTOTAL(109, E2:E1000) |
| 表示中の経費合計 | =SUBTOTAL(109, F2:F1000) |
先頭行付近に「表示中の合計」として配置しておけば、フィルター条件を変えた瞬間に結果を確認できます。上司や先輩に画面を見せながら説明するときも、数字が見やすくなります。
売上表や経費表は、ただデータを並べるだけではなく、「必要な条件で素早く合計を確認できる状態」にしておくことが大切です。SUBTOTAL(109, 範囲)をあらかじめ入れておけば、集計ミスを減らしながら、日々の確認作業をかなり効率化できます。
コメント