Excelでフィルターをかけた数値の合計や平均を求める方法って？

Excelは、表計算を利用して合計や平均値などを求めることができます。

最も有名なのは「SUM関数」や「AVERAGE関数」ですが、これらの関数はフィルターをかけている表や複数の合計を求めたい表では非常に使いにくいです。

たとえば、フィルターをかけて特定のデータを抽出し、そのデータのみの合計や平均値を知りたい場合、IF関数を利用して別のセルに計算結果を求める必要があり手間がかかりますよね。

そこで、フィルターをかけている表で使い勝手の良い関数「SUBTOTAL関数」について、解説していきたいと思います。

SUBTOTAL関数を利用すれば、可視化したセルのみの結果も自動的に算出されるので、効率が良いです。

この機会に覚えておきましょう！

▼関連記事 Excelのフィルター機能の便利な使い方【基礎編】

Excelの関連記事はこちら

SUBTOTAL関数について

SUBTOTAL関数は、指定した範囲を指定した集計方法で算出する関数です。

集計方法には、「合計」「平均値」「最大値」「最小値」などいろいろな種類があります。

表示の仕方は以下のとおりです。

=SUBTOTAL(集計方法,セルの範囲)

SUBTOTAL関数では、集計方法を数字（1～11、もしくは101～111）で指定します。

下の表に、 SUBTOTAL関数でよく利用される集計方法をまとめました。

特に「9」の合計は利用できるようにしておきましょう。

また、これらの数字を覚えておく必要はありません。

以下のようにヘルプで表示してくれるため、使いたい関数を選ぶだけで良いです。

SUBTOTAL関数とSUM関数の違いについて

SUM関数とSUBTOTAL関数は、算出結果に違いがあります。

特に以下2つを出したい時に、その違いがわかるでしょう。

• ・小計をだしたいとき
• ・フィルターをかけて可視化したセルのみの合計をだしたいとき

小計をだしたいとき

たとえば、以下のような表があり、小計と合計をだしたいと仮定します。

SUM関数を利用するときは、C7のセルに「=SUM(C2:C6)」、C12のセルに「=SUM(C8:C11)」と入れることで、それぞれの小計をだすことができます。

C13の全体の合計を出したい時は、SUM関数ではなく「=C7+C12」といれるでしょう。

なぜなら「=SUM(C2:C12)」とした場合、小計の数も合計されてしまい正しい結果が表示されなくなるからです。

さらにSUM関数を利用すると、途中でCグループが入ってきたときや、別の人が中に入ってきたとき、さらに小計の選択を間違えてしまうと、すべての結果がおかしくなります。

SUM関数は全体の合計を出すときに使うものであり、このように細かく設定することには向いていません。

一方SUBTOTAL関数を利用すると、全体合計でいちいちセルを選択する必要がなくなり、計算のミスがなくなります。

さらに、SUBTOTAL関数でだした合計は足されない特徴があるため、全体の合計でもセルをすべて選択して利用することが可能です。

このように、SUM関数では小計をだしてしまうと全体の合計にミスを起こしてしまう可能性がありますが、SUBTOTAL関数ではそのようなミスはおきません。

フィルターをかけて可視化したセルのみの合計をだしたいとき

フィルターをかけて、可視化したセルのみの合計をだしたいときは、SUM関数でだすことはできません。

SUM関数はフィルター関係なしに、全部の合計をだす特徴があるからです。

しかしSUBTOTAL関数を利用していれば、フィルターで特定のデータを抽出しても自動的にその合計が出されます。

見たいデータのみの合計をだすことができるため、非常に便利な機能です。

しかし、フィルターではなく主導で非表示にした場合は合計されるため、あくまでもフィルター機能を利用した関数となります。

SUM関数だけではなく、AVERAGE関数でも同じで、SUBTOTAL関数はデータが多くなり、フィルターでグループわけしているものほど、利用価値があがります。

フィルターをかけた数値結果を合計する

フィルターをかけた数値結果を合計する手順を解説します。

以下の方法でおこなってください。

1. 1.セルを選択し「SUBTOTAL」を入力
2. 2.セルの範囲、集計方法を指定
3. 3.Enterキーを押す
4. 4.フィルターでデータを抽出する

（1）セルを選択し「SUBTOTAL」を入力

セルの中、もしくは上部の計算式を入力する欄に「=SUBTOTAL」と入力します。

（2）セルの範囲、集計方法を指定

「=SUBTOTAL(9,C2:C10)」と入力します。

今回は合計なので「9」を入力し、合計したい範囲は「C2～C10」を指定します。

（3）Enterキーを押す

全体の合計が算出されました。

（4）フィルターでデータを抽出する

フィルターをかけて、特定のデータを抽出します。

すると、合計の欄に自動的に「A」のみの合計結果が表示されます。

フィルターをかけた数値結果の平均を求める

フィルターをかけた数値結果の平均を求める手順を解説します。

合計と同じ方法で、指定する集計方法が異なります。

平均の表示方法は「1」を指定してください。

手順は合計と同様以下のとおりとなります。

1. 1.セルを選択し「SUBTOTAL」を入力
2. 2.セルの範囲、集計方法を指定
3. 3.Enterキーを押す
4. 4.フィルターでデータを抽出する

（1）セルを選択し「SUBTOTAL」を入力

平均を算出したいセルに「=SUBTOTAL」と入力してください。

（2）セルの範囲、集計方法を指定

今回は平均値を出したいので「1」を入力します。

（3）Enterキーを押す

平均値が算出されました。

（4）フィルターでデータを抽出する

フィルターをかけて、特定のデータを抽出すると、自動的に平均値が算出されました。

フィルターをかけて絞られたセルの件数をカウントする

フィルターをかけて絞られたセルの件数をカウントする手順は、上記二つの「合計」「平均値」と全く同じです。

そのため、詳しい解説は割愛します。

カウントを表示するためには「2」を入力してください。

SUBTOTAL関数の注意点

SUBTOTAL関数を利用する際には、以下2つの項目に注意しましょう。

• ・手動で非表示にした行は反映されない
• ・間に小計をいれると表示されないときがある

単純に行を非表示にしても、フィルター機能の作動範囲が変化することはありません。

たとえば、行番号4～8の行を非表示にしてみましょう。

フィルター機能を使って絞り込み結果が表示された場合と同じように、一部の行が表示されなくなっています。

しかし、SUBTOTAL関数の結果には、非表示は反映されません。

紛らわしいので、隠されている行を再表示し、フィルター機能を使ってデータを絞り込むようにしましょう。

ほかにも、図のようにフィルター範囲に小計行が含まれていると、フィルターをかけた後に、合計行が消えてしまうことがあります。

フィルタリングの条件によっては、小計行が表示されなくなってしまいます。

対処法としては、フィルターの範囲外で合計値を表示するようにしましょう。

まとめ

SUBTOTAL関数を利用することで、フィルターで絞り込んだセルの数値の合計や平均、件数を自動でカウントすることができます。

合計や平均を計算で求めると非常に手間がかかりますので、SUBTOTAL関数をうまく使って、業務の効率化を図りましょう。

（マイナビ学生の窓口編集部）

excel-filter