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

Excelでは、表計算を利用して合計や平均値などを求めることができます。最も有名なのは「SUM関数」や「AVERAGE関数」ですが、これらの関数はフィルターをかけている表や複数の合計を求めたい表など、場合によっては使いにくいことも。

たとえば、フィルターをかけて特定のデータを抽出し、そのデータのみの合計や平均値を知りたい時はIF関数を使ったりと一手間かける必要が出てくるのです。

そこで今回は、フィルターをかけている表でも使い勝手の良い「SUBTOTAL関数」をご紹介。SUBTOTAL関数ならフィルターをかけた表や、間に小計が含まれているような表でもうまく計算してくれるので、この機会にマスターしましょう！

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

SUBTOTAL関数について

SUBTOTAL関数とは、「合計」のほか「平均値」「最大値」「最小値」などいろいろな集計方法で計算してくれる関数です。

上記の「集計方法」は数字（1～11、もしくは101～111）で指定します。

特によく利用される集計方法を表にまとめました。

「合計」の計算をしたい時は「9」を入力することになります。

また、これらの数字を覚えておく必要はありません。以下のようにヘルプで表示してくれるため、使いたい関数を選ぶだけで良いです。

SUBTOTAL関数とSUM関数の違いは？

では、ポピュラーなSUM関数の合計と、SUBTOTAL関数の合計はどんな違いがあるのでしょう。

主に次のようなケースで違いが明らかとなるため、具体的に見ていきましょう。

小計を出したいとき

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

SUM関数を利用する場合は、A合計となるセルC7に「=SUM(C2:C6)」、B合計となるセルC12に「=SUM(C8:C11)」と入れることで、A・Bそれぞれの小計を出します。その後で全体合計となるセルC13には「=C7+C12」と入れたりするのが一般的です。

このとき全体合計を上から下まで「=SUM(C2:C12)」としてしまうと、個々の数と小計の数がごっちゃになってしまい、正しい結果が得られません。

それに、新たにCグループが入ってきた時など、計算式をきちんと書き換えないと全体合計が狂ってしまうことに。

このように、SUM関数はシンプルに合計したい時は便利ですが、小計など細かな設定のある表には向いていません。

一方、SUBTOTAL関数なら、途中SUBTOTAL関数で出した小計は足されないのが大きな特徴。そのため全体合計でもセルをすべて選択して利用することが可能です。

全体合計で小計のセルを都度選択していく必要がないため、計算ミスもなくなるというわけです。

このように、SUM関数では途中に小計があると全体合計でミスを起こしやすいですが、SUBTOTAL関数では数式をシンプルにできるため、確実に正しい結果を出すことができるのです。

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

フィルターをかけて、例えば「Aグループのみ抽出して合計したい」といったケースがありますよね。この場合、

このような大きな違いがあります。

SUM関数の方は、フィルター関係なしに全部の合計を出すという特徴があります。一方、SUBTOTAL関数の方は、フィルターで特定のデータを抽出すると自動的にその合計が計算されます。

見たいデータのみの合計を出すことができ、とても便利ですね。ただし、フィルターではなく手動で非表示にした場合は関係なく全て合計されますのでご注意ください。

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

フィルターをかけた数値を合計する手順

では実際に、フィルターをかけた数値を合計するという計算をやってみましょう。まずはフィルターがかかっていない状態で数式を入れていきます。

（1）合計を出したいセルを選択してから、セルの中もしくは上部の数式バーに「=SUBTOTAL」と入力

（2）続いて数式を「=SUBTOTAL(9,C2:C10)」と入力していきます。

「9」は集計方法で「合計」を表します。「C2:C10」というのは集計したいデータの範囲ですね。

（3）Enterキーで、いったん全体の合計が算出されます。

（4）表全体を選択してから「データ」→「フィルター」でフィルターが設置されます。

（5）フィルターのボタンを押して「A」のみ選択して抽出します。

すると、合計の欄は自動的に「A」のみの合計結果に変わります。

フィルターをかけた数値を平均する手順

次に、フィルターをかけた数値の平均を求める手順を解説します。基本的な流れは先ほどの合計と変わりませんが「集計方法」だけが異なります。

（1）平均値を出したいセルを選択してから「=SUBTOTAL(1,C2:C10)」と入力していきます。

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

（2）Enterキーで平均値が算出されました。

（3）先ほどのフィルターでAグループのみ抽出

すると、平均の欄は自動的に「A」のみの結果に変わります。

フィルターで絞られたセルの個数をカウントする手順

フィルターをかけて絞られたセルの個数をカウントする手順は、上記2つの「合計」「平均値」と全く同じです。そのため、詳しい解説は割愛します。

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

SUBTOTAL関数の注意点

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

単純に行を非表示にしても、フィルターのように計算結果が変化することはありません。

例えば、行番号4～8の行を非表示にしてみましょう。該当の行を選択し、右クリックして「表示しない」を選択します。

しかし、SUBTOTAL関数の結果は非表示にかかわらず、全体の数値が表示されたままです。

そのため非表示ではなく、必ずフィルター機能を使ってデータを絞り込むようにしましょう。

※非表示を元に戻すには、境目の部分を選択してから右クリック→「再表示」です。

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

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

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

まとめ

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

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

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