「エクセルの表をフィルターで検索したときに、なぜか合計値が変化しない。。。」
そんな疑問を抱えていませんか？
- 表でフィルターを使用しても、合計値が変化しない！
- 非表示の行を無視して集計したい！
そんな場合に便利なのが「SUBTOTAL関数」です！
この記事では、エクセルを使ってフィルター適用後のデータの合計を求める方法を詳しくご紹介します。
さらに、SUBTOTAL関数は合計だけでなく、さまざまな集計処理を行うことができます。
最大値や平均値、カウントなどもフィルター適用後のデータに対して計算できるので、とても便利ですよ。
基本的な使い方から応用的なテクニックまで、初心者の方でもすぐに実践できる内容です。
ぜひ最後までお読みください。
【 この記事の概要 】
| よく使う度 | |
| 難しさ | |
| 覚えておくと安心度 |
この記事を見てできるようになること
この記事を読めば、以下のことができるようになります。
エクセルSUBTOTAL関数とは？基本の使い方を解説
SUBTOTAL関数は、エクセルにおけるデータ集計を効率化するための強力な関数です。
特に、データにフィルターを適用した際や行を非表示にした場合でも、表示されているデータのみを対象に正確な集計を行うことができます。
SUBTOTAL関数の具体的な機能は以下の通りです。
- フィルター適用時の集計
- データをフィルターで絞り込んだ場合でも、フィルター後の表示データに対して合計や平均などの集計を正確に行います。
- 非表示行の無視
- 手動で非表示にした行や、フィルターで非表示になった行を自動的に除外し、表示されているデータのみを集計対象とします。
- 多様な集計方法の選択
- 引数を設定することで、合計（SUM）、平均（AVERAGE）、最大値（MAX）、最小値（MIN）など、さまざまな集計方法を柔軟に選択できます。
- 階層的な集計の対応
- グループ化されたデータに対しても適切に集計を行うため、複雑なデータ構造にも対応可能です。
SUBTOTAL関数の構成
- 集計方法 (必須)
- 「SUM」や「AVERAGE」の関数を選択して使用することができます。
関数は「1 ~ 11」または「101 ~ 111」にて指定します。
※番号に対応する関数については、事項「集計方法の対応番号について」で説明します。 - 「1～11」と「101～111」の違いについて
- 「1～11」は、行を非表示にしても、非表示の値も含んで処理されます。
- 「101～111」は、行を非表示にした場合、表示されている数値のみ処理します。
※使い分けの方法については、使用例3で紹介をしています。
合わせてご確認ください。
- 「SUM」や「AVERAGE」の関数を選択して使用することができます。
- 範囲 (必須)
- 集計方法で指定した関数を使用する範囲を指定します。
- 範囲 2 以降 (任意)
- 範囲が複数ある場合は、さらに範囲を指定します。
最大 254 の範囲を指定できます。
- 範囲が複数ある場合は、さらに範囲を指定します。
より詳しい情報は、Microsoft公式 – SUBTOTAL関数についてを参照してみてください。
SUBTOTAL関数の「 集計方法 」の対応番号について
SUBTOTAL関数は、次のような関数を選択して使用することができます。
「集計方法」の引数に対応する番号を入力して、目的の関数を実行します。
| 非表示の行を含む | 非表示の行は無視する | 使用する関数名 | 詳細動作 |
|---|---|---|---|
| 1 | 101 | AVERAGE | 平均を求める |
| 2 | 102 | COUNT | 数値の個数をカウントする |
| 3 | 103 | COUNTA | 空でないセルの個数をカウントする |
| 4 | 104 | MAX | 最大値を求める |
| 5 | 105 | MIN | 最小値を求める |
| 6 | 106 | PRODUCT | 積を計算する |
| 7 | 107 | STDEV.S | 標本標準偏差を求める |
| 8 | 108 | STDEV.P | 母集団標準偏差を求める |
| 9 | 109 | SUM | 合計を求める |
| 10 | 110 | VAR.S | 標本分散を求める |
| 11 | 111 | VAR.P | 母集団分散を求める |
SUBTOTAL関数がよく使われる場面
SUBTOTAL関数は、特定の状況や用途で特に役立つ関数です。
以下のような場面で活躍します。
- データのフィルタリング後に集計を行いたい場合
- フィルターを適用した後のデータに対して、合計や平均を計算する際に使用されます。
通常のSUM関数ではフィルタリングが反映されないため、SUBTOTAL関数を使うことで正確な集計が可能です。
- フィルターを適用した後のデータに対して、合計や平均を計算する際に使用されます。
- 非表示行を除外した集計
- 手動で非表示にした行や、フィルターで非表示にされた行を除外し、表示されているデータだけを集計したい場合に使用します。特に、データの一部を頻繁に非表示にするようなシートで有効です。
- グループ化されたデータの集計
- グループ化したデータを合計や平均などで集計する際にも、SUBTOTAL関数は便利です。
グループ化や階層化されたデータに対しても正確な集計を行うことができます。
- グループ化したデータを合計や平均などで集計する際にも、SUBTOTAL関数は便利です。
SUBTOTAL関数を使用する際の注意ポイント
SUBTOTAL関数を使用する際には、以下の注意点に留意することが重要です。
- 適切な集計方法の選択
- SUBTOTAL関数には複数の集計方法が用意されていますが、使用する集計方法によって結果が異なる場合があります。
例えば、合計を求める際には「9」を、平均を求める際には「101」を指定するなど、適切な引数を選択する必要があります。
誤った引数を指定すると、意図しない集計結果が得られる可能性がありますので、公式のリストを確認して正しい引数を使用するようにしましょう。
- SUBTOTAL関数には複数の集計方法が用意されていますが、使用する集計方法によって結果が異なる場合があります。
- 非表示行とフィルタリングの関係
- SUBTOTAL関数はフィルターで非表示にされた行だけでなく、手動で非表示にされた行も集計から除外します。
手動で非表示にする場合には、集計結果に影響を与えないように注意しましょう。
- SUBTOTAL関数はフィルターで非表示にされた行だけでなく、手動で非表示にされた行も集計から除外します。
- 計算結果の正確性
- SUBTOTAL関数はフィルタリングや非表示行を考慮して集計を行いますが、関数の引数や範囲の設定ミスによって計算結果が意図しないものになることがあります。
集計対象の範囲や引数の設定を再確認し、正確な結果を得るよう心掛けましょう。
- SUBTOTAL関数はフィルタリングや非表示行を考慮して集計を行いますが、関数の引数や範囲の設定ミスによって計算結果が意図しないものになることがあります。
SUBTOTAL関数の使用例をご紹介
それでは、SUBTOTAL関数の使用例を3つ紹介します。
以下に紹介する使用例は、すべてコピペしてすぐに実行できるコードです。
気になる例は、ぜひご覧いただき、実際に実行してみてください。
使用例1: フィルター適用後の合計を求める。（引数「9」または「109」）
表でフィルター適用した際に、表示されている数値の合計を求める方法を紹介します。
例えば、このようなエクセルデータがあるとします。
フィルターを使用して商品名「商品A」でデータを絞り込んだ場合、「SUM関数」を使用すると、フィルターを適用した後でも全てのデータの合計を表示してしまいます。
フィルターで非表示になった行を無視して合計値を求めたい場合、次のように記述します。
=SUBTOTAL(9,$B$2:$B$6)または、
=SUBTOTAL(109,$B$2:$B$6)引数の解説
この例では、引数を下記の通り設定しています。
計算結果
こちらが、「商品名A」で絞り込んだ場合の結果です。
「商品名A」のみの合計が表示されています。
続いて「商品名B」で絞り込んだ場合の結果です。
「商品名B」のみの合計へ自動で変化しました。
このようにSUBTOTAL関数を使用することで、フィルターを適用しても範囲内の合計値を常に変化させながら確認することができます。
使用例2: フィルター適用後の平均を求める（引数「1」または「101」）
フィルター適用後の平均を求める方法を紹介します。
例えば、このようなエクセルデータがあるとします。
フィルターを使用して商品名「商品A」でデータを絞り込んだ場合、「AVERAGE関数」を使用すると、フィルターを適用した後でも全てのデータの平均を表示してしまいます。
フィルターで非表示になった行を無視して平均値を求めたい場合、次のように記述します。
=SUBTOTAL(1,$B$2:$B$6)または、
=SUBTOTAL(101,$B$2:$B$6)引数の解説
この例では、引数を下記の通り設定しています。
計算結果
こちらが、「商品名A」で絞り込んだ場合の結果です。
「商品名A」のみの平均が表示されています。
続いて「商品名B」で絞り込んだ場合の結果です。
「商品名B」のみの平均へ自動で変化しました。
このようにSUBTOTAL関数を使用することで、フィルターを適用しても範囲内の平均値を常に変化させながら確認することができます。
使用例3: 非表示の行を無視して合計を求める （引数「109」）
隠し行を無視して合計を求める方法を紹介します。
例えば、このようなエクセルデータがあるとします。
B4セルの行を非表示にして、データの合計を求めたい場合、「SUM関数」を使用すると、非表示の行も含めた合計を表示します。
非表示の行を無視して合計値を求めたい場合、次のように記述します。
=SUBTOTAL(109,$B$2:$B$6)引数の解説
この例では、引数を下記の通り設定しています。
計算結果
こちらが、SUBTOTAL関数を使用した結果です。
引数の集計方法に「109」を使用しているため、非表示の行を無視して合計値を求めることができます。
次は、引数の集計方法に「9」を使用したNGの場合です。
こちらは、非表示の行を含めた合計値を表示してしまいます。
このように、非表示の行を無視した合計を表示したい場合は、「109」のように3桁の関数番号を使用します。
SUBTOTAL関数 と AGGREGATE関数との比較
SUBTOTAL関数と同様の処理を行うことができる関数に「AGGREGATE関数」があります。
先ほど行った「使用例1: フィルター適用後の合計を求める」については、AGGREGATE関数を使用しての処理も可能です。
その場合、AGGREGATE関数では次のように記述します。
=AGGREGATE(9,3,$B$2:$B$6)計算結果
では、この2つの関数は何が違うのか？というと以下の通りです。
引用元:エクセルでAGGREGATE関数を使ってエラーを無視して合計を求める方法
- AGGREGATE関数
- エラー処理も可能。
- AGGREGATE関数は、エラー値が含まれるデータに対して、エラーを無視して集計を行うことができます。
この機能により、エラーが含まれる範囲でも正確な結果を得ることができます。- 多機能
- AGGREGATE関数は、「SUM」や「AVERAGE」などの基本的な集計だけでなく、様々な集計方法（例えば最大値や標準偏差の計算）に対応しており、より柔軟な使用が可能です。
- SUBTOTAL関数
- エラー処理はできない。
- SUBTOTAL関数は、エラー処理に対応していません。
データにエラーが含まれている場合、そのエラーが結果に影響を与えてしまいます。
例えば、エラー値を含む範囲でSUMを使用すると、計算結果がエラーとなります。
※本記事の「使用例1: エラーを無視して合計を求める」は、SUBTOTAL関数では処理できません。- シンプルで使いやすい
- SUBTOTAL関数は、機能が限定されている分、シンプルで直感的に使いやすいという利点があります。
特にエラー処理が不要な場合には、簡単に使用できるため、多くの場面で利用されています。
結果: AGGREGATE関数 と SUBTOTAL関数は、どちらを使用するべきか？
どちらを使用するかは、状況によりますが、次の使い分けがおすすめです。
引用元:エクセルでAGGREGATE関数を使ってエラーを無視して合計を求める方法
AGGREGATE関数については、別記事で解説していますので、こちらも参考にしてみてください。
SUBTOTAL関数のまとめ
SUBTOTAL関数は、エクセルにおけるフィルタリングや非表示行を考慮したデータ集計に非常に便利な関数です。
この記事では、SUBTOTAL関数の基本的な使い方から応用的なテクニックまでを詳しく解説しました。
ポイントのおさらい
さらに学ぶために
VBA（Visual Basic for Applications）を使用することで、SUBTOTAL関数と同様の処理を自動化することが可能です。
関数からさらにステップアップしたい場合には、VBAを使用してみるのも良いでしょう。
この記事が役に立ったと思った方は、ぜひサイトマップから違う記事も参照ください。
