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

更新:2023/09/07

ITスキル

エクセル フィルター 合計

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

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

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

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

SUBTOTAL関数について

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

<構文>
「=SUBTOTAL(集計方法,セルの範囲)」

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

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

SUBTOTAL関数について

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

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

SUBTOTALヘルプ

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

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

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

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

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

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

エクセル フィルター 合計1

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

エクセル フィルター 合計2

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

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

エクセル フィルター 合計3

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

エクセル フィルター 合計4

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

エクセル フィルター 合計5

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

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

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

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

エクセル フィルター 平均1

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

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

エクセル フィルター 平均2

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

エクセル フィルター 平均3

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

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

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

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

SUBTOTAL関数の注意点

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

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

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

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

SUBTOTAL関数の注意点1

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

SUBTOTAL関数の注意点2

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

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

SUBTOTAL関数の注意点3

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

SUBTOTAL関数の注意点4

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

SUBTOTAL関数の注意点5

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

まとめ

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

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

(マイナビ学生の窓口編集部)


関連記事

新着記事

もっと見る

HOT TOPIC話題のコンテンツ

注目キーワード

 ビジネス用語・カタカナ語80選

 キャリアロードマップの一歩目

  • ピックアップ