Excel(エクセル)でフィルターを使った集計をしたいのに、「合計を出しても全体の数字しか表示されない」「抽出後の合計がうまく計算できない」と困ったことはありませんか？
特にデータが多くなるほど、手作業で計算するのは手間も時間もかかり、間違いの原因にもなります。
しかし、SUBTOTAL関数を使うことで、フィルターで抽出したデータだけを正しく集計でき、作業効率を大幅に上げることが可能です。
また、テーブル機能を併用すれば、集計行に自動でSUBTOTAL関数が設定され、フィルターの切り替えにも対応するため、手間をかけずに最新の集計結果を確認できます。
そこで今回の記事では、セルに直接SUBTOTAL関数を入力する方法から、テーブルの集計行を使った方法まで、それぞれの特徴や使い分けのポイントをわかりやすく解説します。
ExcelのSUM関数って難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
SUM関数とSUBTOTAL関数の違い
エクセルで合計を出したいとき、よく使われるのが「SUM関数（サム関数）」です。
たとえば売上の合計や点数の合計など、数値をまとめて足したいときに使います。
「SUM関数」はとても便利な関数ですが、ひとつ注意点があります。
それは、「フィルター」を使ってデータを絞り込んでも、非表示になっているデータまで含めて合計してしまうという点です。
たとえば、売上表にフィルターをかけて「東京支店」だけを表示しても、SUM関数では「大阪支店」や「名古屋支店」など、非表示の行もすべて計算に含めてしまいます。
つまり、フィルターで見えている部分だけの合計を出したい場合、SUM関数では正しい結果が出ないのです。
そこで便利なのが「SUBTOTAL関数（サブトータル関数）」です。
SUBTOTAL関数は、フィルターで表示されているデータだけを合計できるという特徴があります。
フィルターで一部の行が非表示になっていても、表示されている行だけを自動で判別して合計してくれます。
このように、
- 表全体の合計を出したいときは → SUM関数
- フィルターで絞り込んだデータの合計を出したいときは → SUBTOTAL関数
という使い分けがポイントです。
どちらの関数も「合計を出す」という点では同じですが、表示・非表示の扱いが大きく異なります。
普段はSUM関数で十分ですが、データを絞り込んで分析したいときや、担当者別・店舗別などで集計したいときには、SUBTOTAL関数を使うと正確な合計を求められます。
下記の例を見てみましょう。
表の合計をセル【C8】に求めます。
セル【C8】を選択し、「ホーム」タブの「編集」グループにある「オートSUM」をクリックします。
売上金額のセル範囲を選択し、「Enter」キーを押して確定します。
これで、参照した範囲の合計が求められます。
次に、表に「フィルター」を適用しましょう。
表内のセルを選択し「データ」タブの「並べ替えとフィルター」グループにある「フィルター」をクリックします。
表の1行目にフィルターボタンが表示され、表にフィルターが適用されていることが確認できます。
次に、東京支店のデータのみ抽出してみましょう。
「支店」のフィルターボタンをクリックし、「東京」のみチェックマークを付けた状態にして「OK」をクリックします。
これで、東京のデータを抽出することができましたが、SUM関数だと抽出後も全体の合計が表示されてしまいます。
非表示になっているセルの数値も合計の対象になってしまうので、注意が必要です。
このような場合は、SUBTOTAL関数を使うことで、抽出後のデータのみの合計を出すことができます。
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
SUBTOTAL関数の基本と特徴
SUBTOTAL関数は、フィルターを使ってデータを絞り込んだ状態でも、表示されているデータだけを集計できる関数です。
「合計」だけでなく、「平均」「最大値」「件数」など、さまざまな計算に対応しています。
たとえば、売上データを支店別にフィルターで絞り込み、「東京支店だけの売上合計を出したい」といった場合、SUM関数ではすべての行を合計してしまいますが、SUBTOTAL関数なら表示されている行だけを計算してくれます。
このように、SUBTOTAL関数はフィルターを使った集計や分析にとても便利な関数です。
表全体をまとめるときだけでなく、特定の条件に絞って確認したいときにも役立ちます。
SUBTOTAL関数の構文
SUBTOTAL関数の基本的な構文は次のとおりです。
=SUBTOTAL(集計方法, 参照1, [参照2], …)
- 集計方法：どの計算を行うかを数字（関数番号）で指定します。
- 参照1, 参照2, …：集計したいセル範囲を指定します。
「集計方法」に指定できる番号は 1～11 まであり、番号によって計算内容が変わります。
以下の表は、その一覧です。
| 集計方法 (番号) | 関数名 | 集計内容(何ができるか) |
| 1 | AVERAGE関数 | 平均を求める |
| 2 | COUNT関数 | 数値が入力されているセルの個数を数える |
| 3 | COUNTA関数 | 空白でないセルの個数を数える |
| 4 | MAX関数 | 最大値を求める |
| 5 | MIN関数 | 最小値を求める |
| 6 | PRODUCT関数 | 積(かけ算の結果)を求める |
| 7 | STDEV関数 | 標本に基づいて予測した標準偏差を求める |
| 8 | STDEVP関数 | 母集団の標準偏差をを求める |
| 9 | SUM関数 | 合計を求める |
| 10 | VAR関数 | 標本に基づく、分散の予測値を求める |
| 11 | VARP関数 | 母集団全体に基づく分散を求める |
このように、SUBTOTAL関数は「集計方法」の番号を変えるだけで、1つの関数でさまざまな集計処理ができるのが特徴です。
今回のように、抽出したデータの合計を求めたい場合は、集計方法「9」のSUM関数を使います。
SUBTOTAL関数でフィルター後の合計を出す方法
SUBTOTAL関数は、フィルターで絞り込んだデータの合計を自動で計算できる便利な関数です。
ここでは、実際に関数を入力して合計を求める手順を確認してみましょう。
SUBTOTAL関数を使った合計の求め方
- 抽出後のデータの合計を求めたいセルを選択します。
- 半角入力モードで「=SUBTOTAL(」と入力します。
- 今回は合計を求めるので、集計方法の番号「9」を指定します。
- 半角カンマ「,」を入力して、引数を区切ります。
- 合計したい数値が入力されているセル範囲をドラッグして選択します。
- 最後に「)」を入力し、「Enter」キーを押して確定します。
これで、指定した範囲の合計が表示されます。
フィルターを使ってデータを絞り込むと、表示されているデータだけの合計が自動で再計算されます。
ポイント：関数を入力する位置に注意！
フィルターを設定する際に、SUBTOTAL関数を入力したセルをフィルター範囲に含めてしまうと、抽出したときに合計セルまで非表示になってしまう場合があります。
そのため、フィルターの範囲外に関数を入力しておくのが安全です。
こうしておけば、どんな条件で絞り込んでも、合計セルが常に表示され、結果が自動で切り替わります。
それでは、下記の例を見てみましょう。
表にはあらかじめフィルターを適用しています。
フィルターの適用は、表内のセルを選択後、「データ」タブをクリックし、「フィルター」ボタンを押してフィルターを設定します。これで各列の見出しに「▼」マークが表示されます。
また、今回は比較しやすいように、セル【C8】にSUM関数で全体の合計を求めています。
それでは、SUBTOTAL関数を挿入しましょう。
まず、抽出後の合計のみを表示させたいセルを選択します。今回は、セル【C9】を選択します。
半角入力モードで「=sub」と入力します。
関数の候補から「SUBTOTAL」をダブルクリック、または選択後に「Tab」キーを押します。
半角入力モードで「=SUBTOTAL(」と入力できます。
引数の集計方法を指定します。今回は「合計」を求めたいので「SUM関数」の「9」を入力します。
続けて「半角カンマ（,）」を入力して引数を区切ります。
売上金額の範囲（セル【C2】から【C6】）をドラッグして選択します。
「=SUBTOTAL(9, C2:C6)」と入力されていることを確認し、「Enter」キーを押して確定します。
これで、売上金額の合計が表示されました。
ここでは、まだデータを抽出していないので全体の合計金額の合計が表示されます。
フィルターボタンを使って、東京支店のデータを抽出してみましょう。
これで、SUBTOTAL関数で抽出後のデータのみを合計することができました。
SUM関数の方は全体の合計が表示されたままですが、SUBTOTAL関数であれば抽出後のデータのみ合計できていることが分かりますね。
もちろん、表の抽出内容を変更すれば、その都度合計の結果は自動で反映されます。
集計方法の違い
SUBTOTAL関数の第1引数（集計方法の番号）には、「1～11」と「101～111」があります。
この2つの違いは、「手動で非表示にした行を集計に含めるかどうか」です。
1～11の番号
→ 手動で非表示にした行も集計に含まれます。
101～111の番号
→ 手動で非表示にした行は集計から除外されます。
どちらを使っても、フィルターで抽出された結果に応じて集計される点は共通です。
つまり、フィルターで非表示になった行は、どちらの場合も自動的に集計から除外されます。
そのため、
- 手動で非表示にした行も含めたい → 1～11
- 手動で非表示にした行は除外したい → 101～111
という使い分けをすると良いでしょう。
下記の例を見てみましょう。
SUBTOTAL関数の集計方法を「109」にしています。
この状態で、表内の行を非表示にしてみます。
すると、集計方法「109」は非表示にした行は集計の対象外となるため、集計結果も表示されている行のみになります。
SUM関数では、非表示の行を含めた範囲の合計が出ていることが分かります。
ちなみに、集計方法「9」の場合だと非表示の行も含めて表集計されます。
ExcelのSUM関数って難しい…
そんなお悩み、当教室のレッスンで一気に解決できます。
✔ 基本操作からしっかり学べる
✔ 「見てマネするだけ」で自然と覚えられる
✔ わからないところはすぐ質問できるから安心
文字を読むのが苦手でも大丈夫。
「実践重視のレッスン」＋「元パソコンが苦手だった講師のサポート」で、着実に「感覚としてExcelに慣れていく」ようになります。
詳細は下記をご覧ください。
SUBTOTAL関数を表に適用する方法
SUBTOTAL関数は、セルに直接入力して使うこともできますが、Excelの「テーブル」機能を使うと、もっと簡単に集計ができます。
テーブルに変換しておくと、集計行を自動で追加でき、Excelが自動的にSUBTOTAL関数を設定してくれるのが特徴です。
また、フィルターでデータを抽出したときも、表示されているデータだけを正しく集計できます。
テーブルとは
「テーブル」とは、Excel上のデータ範囲を一覧表として管理しやすくする機能のことです。
通常のセル範囲と違い、テーブルにすると次のようなメリットがあります。
- 行や列を追加しても自動で書式や数式が反映される
- フィルターが自動で設定される
- テーブルの最下部に集計行を表示できる
- 集計行にはSUBTOTAL関数が自動的に挿入される
つまり、テーブルに変換しておくことで、集計作業をより効率的に行うことができます。
テーブルに変換する方法
- テーブルにしたい表内のセルを選択します。
- メニューから「挿入」タブをクリックします。
- 「テーブル」を選択します。
- 「テーブルの作成」ダイアログボックスが表示されるので、範囲を確認します。
- 先頭行に見出しがある場合は、「先頭行をテーブルの見出しとして使用する」にチェックを入れます。
- 「OK」をクリックします。
これで、選択した範囲がテーブルとして設定されます。
下記の例を見てみましょう。
まず、表内のセルを選択します。
※テーブルを適用する場合は、セルの塗りつぶしなどは不要です。
「挿入」タブをクリックします。
「テーブル」グループにある「テーブル」をクリックします。
「テーブルの作成」ダイアログボックスが表示されます。
テーブルに変換するデータ範囲で自動参照されているセル範囲を確認します。
この時、自動参照されたセル範囲に誤りがある場合は、手動で範囲をドラッグします。
「先頭行をテーブルの見出しとして使用する」にチェックマークが付いていることを確認して「OK」をクリックします。
これで、表にテーブルが適用されました。
テーブルが適用されると自動で、塗りつぶしなどの書式が設定されます。
また、見出しの行にフィルターボタンが表示されます。
集計行を追加する手順
- テーブルの中をクリックします。
- 「テーブルデザイン」タブを開きます。
- 「集計行」にチェックを入れます。
- テーブルの最下行に新しい行が追加され、ここが集計行になります。
- 金額列などで合計を出したい場合、該当セルの▼をクリックし、「合計」を選びます。
これで、自動でSUBTOTAL関数を使うことができます。
集計行が不要な場合は、「集計行」のチェックを外せば解除が可能です。
それでは、テーブルに集計行を追加しましょう。
「テーブルデザイン」タブをクリックします。
「テーブルスタイルのオプション」グループにある「集計行」をクリックしてチェックマークを付けます。
※「テーブルデザイン」タブは、テーブル(表)内のセルを選択すると表示されるタブです。
「集計行」にチェックマークを入れると、表の最終行に集計行が追加されます。
追加された集計行のセルをクリックすると、SUBTOTAL関数が挿入されていることが分かります。
テーブルに適用されるSUBTOTAL関数の集計方法は「109」になっています。
集計のセルをクリックすると、セルの右側に「▼」ボタンが表示されます。
このボタンをクリックすると、SUBTOTAL関数の集計方法の種類を選択することができます。
テーブルのデータを抽出して「大阪支店」のデータのみを表示してみましょう。
すると、抽出結果の大阪支店のデータのみをSUBTOTAL関数で集計することができました。
SUBTOTAL関数が自動で設定される仕組み
テーブルで集計行を表示すると、Excelは自動的にSUBTOTAL関数（109）を使って合計を計算します。
この「109」は「SUM関数＋手動で非表示にした行を除外する」という意味の集計方法です。
そのため、テーブルの集計行では、
- フィルターで抽出したデータだけを集計
- 手動で非表示にした行も集計から除外
といった処理が自動で行われます。
つまり、SUBTOTAL関数を自分で入力しなくても、テーブルの集計行を使えば、常に正しい集計結果を得ることができるのです。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
関数入力とテーブル適用の使い分け
SUBTOTAL関数は、セルに直接入力して使う方法と、テーブルの集計行として使う方法があります。
どちらも「フィルター後のデータだけを集計できる」という点は同じですが、目的や使い方によって向き・不向きがあります。
セルに直接SUBTOTAL関数を入力する場合
セルに直接SUBTOTAL関数を入力する方法は、集計範囲を自由に設定したいときに向いています。
たとえば、一覧表の中から特定の列や、特定の範囲だけを合計したい場合に便利です。
この方法では、SUBTOTAL関数の引数（集計方法やセル範囲）を自分で指定するため、
部分集計や途中の小計など、柔軟な集計が可能です。
また、表の下以外の場所に合計を表示したい場合など、集計結果の表示位置を自由に決められる点もメリットです。
ただし、表の構造を変更したり行を追加した場合は、範囲の指定を修正する必要があります。
そのため、集計範囲が固定されている表に向いています。
テーブルの集計行を使う場合
表を「テーブル」に変換してから集計行を追加すると、SUBTOTAL関数が自動で設定されます。
この機能を使うと、関数を入力する手間がなく、簡単に合計や平均などを求めることができます。
テーブルの集計行は、フィルターで抽出したデータだけを自動で集計するため、常に最新の状態を反映した集計結果を表示できます。
また、行や列を追加しても自動で範囲が拡張されるため、日々データが増えるような表でもメンテナンスが不要です。
集計行が不要になった場合、表示したくない場合は「集計行」のチェックマークを外すことで簡単に解除もできます。
一方で、テーブル全体に対する集計に限定されるため、途中の小計など「部分的な集計」には向いていません。
SUBTOTAL関数で効率的に集計する
いかがでしたか？
SUBTOTAL関数は、Excelのデータ集計において非常に柔軟で便利な関数です。
フィルターを使った抽出後の合計や平均など、表示されているデータだけを正しく計算できる点は、通常のSUM関数では得られない大きなメリットです。
セルに直接関数を入力する場合は、自由に集計範囲や表示位置を指定できるため、部分的な小計や特定条件下の集計に向いています。
テーブル機能を活用すれば、集計行に自動でSUBTOTAL関数が設定され、フィルターと連動した集計結果を瞬時に確認できるうえ、行や列を追加しても範囲が自動で拡張されます。
これにより、日々増えるデータや複数条件での分析でも手間をかけずに管理できるのが大きなメリットです。
さらに、集計方法の番号「1～11」と「101～111」の違いや、手動で非表示にした行を含めるか除外するかを理解しておくことで、意図しない集計結果を避けることができます。
例えば、部分集計を作る場合や全体の平均・最大値を確認する場合など、状況に応じて適切な番号を選択することが可能です。
これにより、作業の正確性と効率性が格段に向上します。
また、テーブルの集計行と関数の直接入力を使い分けることで、操作の自由度と自動化の両方を活かせます。
SUBTOTAL関数の仕組みやテーブル機能との連携、集計番号の違いを理解しておくことで、データ管理をより確実でスムーズにすることができます。
Excel（エクセル）が苦手であればパソコン教室で学んでみませんか？
Excel（エクセル）はかなり奥が深く、知識を得ただけは操作が身に付きにくいものです。
そんな時には、弊社のようなパソコン教室を利用するのも一つの手です。
「苦手な方専門のパソコン教室パレハ」のレッスンは、動画ではなく、直接講師に質問できる生レッスンです。
生レッスンでありながらリーズナブルに学べるのが人気の秘密です。
また、苦手な方に特化しているのでパソコンが特に苦手だと言う方には最適です。
パソコンが苦手な方でも苦労なく上達できる仕組みがあります。
詳細は下記をご覧ください。
教室に通う時間がなくてちょっとずつ覚えたいという方向けの「パソコン苦手さん向け通信講座」を作りました。
基本から順番に「実践で覚えるのではなく慣れていく」教材を使います。
「文字を読むのが苦手…」という方でも大丈夫。
動画とセットで感覚的に身につけられます。
Excel（エクセル）の操作が苦手な方のために、操作する上でのコツをまとめたページを用意しました。
「パソコン教室パレハのLINE友達登録」（無料）で「パソコンに役立つ豪華15大特典」をお送りしております。
Excel（エクセル）の操作はもちろん、パソコンが苦手な方に役立つ特典を15個ご用意しております。
良かったら下記からご登録ください。
でフィルターを使った集計をしたいのに、「合計を出しても全体の数字しか表示されない」「抽出後の合計がうまく計算できない」と困ったことはありませんか？ 特にデータが多くなるほど、手作){kind=link}