エクセルでフィルターをかけた行だけを合計したい!

エクセルでフィルターをかけた行だけを合計したい! 計算する

エクセルは合計を求めるなんてことは得意中の得意なんですが、見えている範囲(行や列)だけを計算することが基本になっています。

なので、オートフィルターによる絞り込み(フィルター)や非表示の行があったりすると、通常は見えてない行までも合計されてしまいます。

でも、やっぱり見えてないところは合計してほしくないですよね^^

この記事の内容

  • オートフィルターで絞り込まれた行(見えている行)だけを合計する。
  • 非表示行を除いた行(見えている行)だけを合計する。
  • SUBTOTAL関数の万能な計算方法。

表を作っているとオートフィルターをかけたり、行を非表示にすることがよくありますが、いざ見えてない行を除外した計算をしようとしても、どうやったらいいかわからないという人が多いです。

そんな時に、今回紹介する方法を参考にしてください。

フィルターしても見えている行だけを合計するSUBTOTAL関数

合計する関数といえばSUM関数ですが、このSUM関数はフィルターや非表示などで見えなくなった行までも合計してしまいます。

それとは逆に、見えている行だけを合計してくれるのがSUBTOTAL関数。

下のエクセルではわかりやすくするために、『商品券の購入金額の合計』をSUM関数とSUBTOTAL関数の両方で計算させています。

フィルターがかかっていないと、SUM関数とSUBTOTAL関数での合計は同じになる

まだフィルターによる絞り込みはしていないので、SUM関数、SUBTOTAL関数とも同じ合計が計算されています。

 

この状態で、『会員に●がついている人だけ』をフィルターした結果がこれです。

SUBTOTAL関数はフィルターによって見えなくなった行は除外して合計する

SUM関数は見えてない行まで合計しているので、フィルターする前と同じ金額になっています。

対して、SUBTOTAL関数は見えている行だけを合計していることがわかります。

フィルターしても見えている行だけを合計する方法

フィルターで見えている行だけを合計したい時のSUBTOTAL関数は、次のように入力します。

例として、下のエクセルようにセル『F3』から『F10』までの合計を求める場合です。

=SUBTOTAL(9, F3:F10)

SUBTOTAL関数の入力

第一引数には『9』、第二引数に合計の計算範囲(F3:F10)を指定します。

第一引数は『計算方法』を指定する引数で、『9』は『合計』を求める計算方法となります。

第一引数の『計算方法』については、後述の『SUBTOTAL関数の万能な計算方法』で詳しく説明します。

これで、SUM関数『=SUM(F3:F10)』と同じ計算をしますが、SUM関数との違いは『フィルターで見えなくなった行は除外して合計する』ということです。

非表示の行を除いて合計する方法

続いては、行を非表示にした場合に見えている行だけを合計する方法です。

下のエクセルでは、男の行の5行目と7行目を『非表示』にした結果です。

SUBTOTAL関数は非表示によって見えなくなった行は除外して合計する

こちらもフィルター同様に、『見えている行』だけが合計されていることがわかります。

非表示行を除外した行を合計するSUBTOTAL関数は、次のように入力します。

=SUBTOTAL(109, F3:F10)

フィルターの場合とは第一引数が違います。

合計の計算方法には『9』に100を足した『109』を指定できます。

『109』は『非表示行を除外する合計』という意味にになります。

行を非表示にした場合は、第一引数の計算方法は『109』を指定する必要がありますが、フィルターの場合はこの計算方法は『109』を指定しても構いません。

SUBTOTAL関数の万能な計算方法

最後に、SUBTOTAL関数の計算方法(引数)について補足します。

SUBTOTAL関数はちょっとした万能な計算関数です。

第一引数に『9』(または『109』)を指定することで、SUM関数のように合計を求めることができ、同じく第一引数に『1』(または『101』)を指定するとAVERAGE関数のように平均を求めることができます。

SUBTOTAL関数で指定できる第一引数の計算方法を次の通り。

SUBTOTAL関数の計算方法(第一引数)
非表示行も含めて計算非表示行を除外して計算対応する計算
1101AVERAGE(平均)
2102COUNT(数値が入力されているセルの個数)
3102COUNTA(何か入力されているセルの個数)
4104MAX(最大値)
5105MIN(最小値)
9109SUM(合計)

他にも、PRODUCT(数値の積)、STDEV(標準偏差)などの計算もありますが、ほとんど使わないので割愛しています。

SUBTOTAL関数には便利な使い方が他にもある

SUBTOTALは日本語に訳すと『小計』という意味で、『小計』だけを合計するということが簡単にできます。

表に小計を使っている場合には重宝する関数だと思うので、次の記事で使い方を参考にしてください。

 

また、SUBTOTAL関数は、フィルターがかかっていたり、非表示行や削除された行があっても、正しく連番を入力することもできます。

こちらもかなり便利な機能なので、次の記事をあわせて参考にしてください。

まとめ

まとめ

  • フィルターや非表示の指定により、『見えている行』のみ合計(計算)したい場合は、SUBTOTAL関数を使う。
  • SUBTOTAL関数は、合計だけではなく平均やカウントなどよく使う計算方法を引数で指定することができる。

エクセルで合計を求める場合はSUM関数を使いますが、解説しているようにフィルターがかかるとSUM関数では正しく合計を求めることができません。

フィルターがかかっても正しく合計が計算されることは非常に重要であるため、表内の合計などの計算はSUBTOTAL関数を積極的に使うようにしましょう。

万能なSUBTOTAL関数は使い慣れるとかなり便利ですよ!

タイトルとURLをコピーしました