ExcelのSUBTOTAL関数の使い方をお探しですね。
広告
Excelでフィルターした結果だけを合計したい！SUBTOTAL関数の使い方
Excelで売上表や在庫表を集計するとき、フィルターで絞り込んだ結果だけを合計したいことってよくありますよね。
でも、普通のSUM関数やCOUNTA関数を使うと、画面で隠れている行まで計算に入ってしまって、「あれ？表示と合計が合わない…」なんてことになりがちです。
そんなときに便利なのが**SUBTOTAL関数**です。
この記事では、フィルターや非表示の行を除外して集計する方法を、初心者の方にも分かりやすく解説していきます。
SUBTOTAL関数って何？フィルター後の表示行だけを集計できる便利な関数
SUBTOTAL関数は、Excelの表やリストを集計するための関数です。
一番の特徴は、**オートフィルターで非表示になった行を、自動的に集計から外してくれる**ところなんです。
たとえば、商品別の売上表で「東京支店」だけをフィルター表示したとします。
このとき、SUBTOTAL関数を使っていれば、画面に表示されている東京支店の行だけを対象に、合計や平均、件数を計算してくれます。
普通のSUM関数で「=SUM(C2:C100)」のように指定すると、フィルターで隠れている行も全部含めて合計されちゃいます。
だから、画面には一部のデータしか表示されていないのに、合計値は全体の金額になってしまうんですね。
一方、SUBTOTAL関数はフィルターで抽出されたデータに連動します。
条件を切り替えるたびに集計結果も自動で更新されるので、「今見えているデータだけ」を確認したいときにすごく便利なんです。
SUBTOTAL関数の基本的な書き方は「**=SUBTOTAL(集計方法, 参照1, [参照2]…)**」です。
最初の「集計方法」には、合計・平均・件数などを表す番号を入れます。
その後ろに、集計したいセル範囲を指定します。
たとえば、C2:C100の合計を求めたいときは「=SUBTOTAL(9,C2:C100)」または「=SUBTOTAL(109,C2:C100)」と入力します。
どちらも合計を求める式ですが、手動で非表示にした行を含めるか除外するかに違いがあります（詳しくは後で説明しますね）。
SUBTOTAL関数の基本的な使い方と集計方法の番号一覧
SUBTOTAL関数を使うときは、まず「何を集計したいのか」を決めます。
合計を出したいのか、データの個数を数えたいのか、平均値を求めたいのか。
これによって、最初に入れる番号が変わってきます。
Excelでは、集計方法ごとに番号が決められています。
数式を入力するとき「=SUBTOTAL(」まで入力すると候補一覧が表示されるので、慣れていなくても選びやすくなっています。
よく使う集計方法をまとめると、こんな感じです。
| やりたいこと | 非表示行を含める番号 | 手動非表示行も除外する番号 | 使用例 |
|—|—:|—:|—|
| 平均を求める | 1 | 101 | =SUBTOTAL(101,C2:C100) |
| 数値の個数を数える | 2 | 102 | =SUBTOTAL(102,C2:C100) |
| 空白以外の個数を数える | 3 | 103 | =SUBTOTAL(103,A2:A100) |
| 最大値を求める | 4 | 104 | =SUBTOTAL(104,C2:C100) |
| 最小値を求める | 5 | 105 | =SUBTOTAL(105,C2:C100) |
| 合計を求める | 9 | 109 | =SUBTOTAL(109,C2:C100) |
実際の仕事で一番よく使うのは、**合計の「9」または「109」**です。
売上金額や数量の合計を出すときは「=SUBTOTAL(109,D2:D100)」のように指定すると、フィルターで表示されている行だけを集計できます。
件数を数えたいときは、数値だけを数えるCOUNT相当の「102」と、文字列を含む空白以外を数えるCOUNTA相当の「103」を使い分けます。
商品名や担当者名など、文字が入っているセルの件数を数えるなら「103」が向いています。
入力の手順は簡単です。
集計結果を表示したいセルを選んで、「=SUBTOTAL(」と入力します。
次に集計方法の番号を指定して、カンマを入れます。
それから集計したい範囲をドラッグして指定したら、閉じかっこを入れてEnterキーを押すだけです。
たとえば、D列の売上金額をフィルター結果だけ合計したいなら「=SUBTOTAL(109,D2:D100)」と入力します。
この状態で表にフィルターをかけると、表示されている行に合わせて合計値が変わります。
フィルターと手動の非表示行を除外する番号の違い
SUBTOTAL関数でちょっと分かりにくいのが、「9」と「109」みたいに似た番号が2種類あることです。
結論から言うと、**オートフィルターで非表示になった行は、1〜11の番号でも101〜111の番号でも集計から除外されます**。
だから、単にフィルター結果だけを集計したいなら「9」でも「109」でも同じ結果になります。
違いが出るのは、**行を右クリックして「非表示」にした場合や、グループ化で折りたたんだ場合**です。
– **1〜11の番号**：手動で非表示にした行を集計に含める
– 例：「=SUBTOTAL(9,D2:D100)」の場合、フィルターで隠れた行は除外されるけど、手動で非表示にした行の数値は合計に含まれる
– **101〜111の番号**：手動で非表示にした行も除外する
– 例：「=SUBTOTAL(109,D2:D100)」にしておけば、フィルターで隠れた行だけでなく、自分で非表示にした行も集計対象から外せる
実際の仕事では、**迷ったら101〜111の番号を使うのがおすすめ**です。
特に、他の人と共有するExcelファイルでは、誰かが一部の行を手動で非表示にしている可能性があります。
その状態で「9」を使うと、画面では見えていない行の金額まで合計に含まれて、確認する人が混乱することがあります。
「表示されている行だけを集計したい」という目的なら、合計は「109」、平均は「101」、件数は「103」のように、100番台を選ぶと意図が伝わりやすくなります。
ただし、SUBTOTAL関数が除外の対象にするのは主に「行」です。
列を非表示にした場合、その列が参照範囲に含まれていれば、基本的には計算対象になります。
また、セルの文字色を白にしただけ、行の高さを極端に小さくしただけといった見た目上の調整は、SUBTOTAL関数にとって非表示行とは扱われません。
正しく除外したい場合は、Excelのフィルター機能や行の非表示機能を使うことが大切です。
SUM関数・COUNTA関数との違いと実務での使い分け
SUM関数やCOUNTA関数は、シンプルな集計に向いている基本的な関数です。
表全体の合計や、いつも同じ範囲の件数を出したいなら、SUM関数やCOUNTA関数で十分です。
でも、**フィルターで条件を切り替えながら集計したいなら、SUBTOTAL関数のほうが断然便利**です。
たとえば、こんな作業をするときです。
– 担当者別にフィルターして売上合計を見る
– 未処理のデータだけ表示して件数を数える
– 商品カテゴリーごとに平均単価を確認する
分かりやすい例で説明しましょう。
A列に担当者名、B列に商品名、C列に数量、D列に売上金額が入った表があるとします。
D列の合計をSUM関数で求めると、担当者でフィルターしても全員分の売上が合計されてしまいます。
でも「=SUBTOTAL(109,D2:D100)」と入力しておけば、担当者を「佐藤」で絞り込んだときは佐藤さんの売上だけ、商品を「ノート」で絞り込んだときはノートの売上だけが表示されます。
フィルター条件を変えるだけで集計結果が切り替わるので、簡易的な分析表としても使えるんです。
SUBTOTAL関数には、もう一つ便利な特徴があります。
**範囲内に別のSUBTOTAL関数がある場合、その結果を二重に集計しにくい**んです。
小計行を含む表で、さらに総計を出したいとき、SUM関数では小計と明細を両方足してしまうことがあります。
SUBTOTAL関数を使うと、入れ子になったSUBTOTALの結果を無視するため、二重計上を防ぎやすくなります。
部署ごと、月ごと、カテゴリごとに小計を入れる表では、この性質を知っておくと集計ミスを減らせます。
ちなみに、エラー値を無視して集計したい場合や、上位何番目の値を取り出したい場合は、SUBTOTAL関数よりAGGREGATE関数が向いていることもあります。
AGGREGATE関数は、非表示行だけでなくエラー値を除外する指定もできるので、より複雑なデータ集計に対応できます。
ただし、日常的なフィルター集計や合計確認なら、まずはSUBTOTAL関数を覚えるのが効率的です。
目的が「フィルター後の表示データだけを合計したい」なら、SUBTOTAL関数が一番手軽に使えます。
テーブル機能と組み合わせるとさらに便利
Excelの**テーブル機能**を使っている場合は、さらに簡単にSUBTOTAL関数を利用できます。
表内をクリックして「テーブルデザイン」から「集計行」をオンにすると、テーブルの下に集計行が追加されます。
売上列の集計行で「合計」を選ぶと、Excelが自動で「=SUBTOTAL(109,[売上])」のような数式を作成してくれます。
この「[売上]」のような表記は**構造化参照**と呼ばれ、テーブルの列名を使って範囲を指定する仕組みです。
行が増えても範囲が自動で広がるので、継続的に更新するデータには特に便利です。
まとめ：SUBTOTAL関数を使いこなすポイント
SUBTOTAL関数を正しく使うポイントは、**「フィルターだけを考えるのか」「手動の非表示行も除外したいのか」を最初に決めること**です。
多くの業務では、表示されている行だけを集計したいケースが多いので、こう覚えておくと分かりやすいです。
– 合計なら「**109**」
– 平均なら「**101**」
– 空白以外の件数なら「**103**」
SUM関数やCOUNTA関数で結果が合わないと感じたときは、非表示行が含まれていないかを確認して、SUBTOTAL関数への置き換えを検討してみてください。
フィルターと連動した集計を使いこなせるようになると、Excelでの確認作業や集計ミスの防止に大きく役立ちます。
ぜひ、日々の業務で活用してみてくださいね。
広告
