Excel（エクセル）でフィルター抽出後など、非表示の行を計算しないで、合計や平均を算出する方法｜SUBTOTAL関数の使い方

Introduction

Ver.　all　2016　2019　2021　2024　365

エクセルで、オートフィルターを使用して抽出した結果や、非表示にした行を省いて合計や平均の計算をしたい場合に便利なSUBTOTAL関数の説明です。

SUMやAVERAGEだけではなく、COUNT、COUNTA、MAX、MINなどの関数も使用できますよ。

---

SUBTOTAL関数・・・自分で集計方法を指定して集計する

=SUBTOTAL（集計方法,範囲1,[範囲2],…）

• 集計方法・・・集計に使用する関数を数字で指定
• 範囲1・・・集計する範囲を指定

---

例えば、下の表からオートフィルターを使用して、

「金額」が50,000以上のデータだけを抽出したのが下の画像です。

この抽出結果の「金額」の合計を算出しようとSUM関数を使用してみると、

抽出後のデータだけではなく、抽出前のデータも含めて合計されてしまいます。

実際は、「599,400」ではなく「372,000」になるはずです。

同じように、非表示にした行が存在した状態でSUM関数などを使用すると、非表示行も含めて計算されます。

表示されているセルだけを計算したい時に便利なのが「SUBTOTAL関数」です。合計だけではなく、平均などを算出する場合にも使用できます。

 

SUBTOTAL関数の使い方

関数の説明の前に、先ほどの合計を出したい場合なのですが、

［ホーム］タブの「オートSUM」ボタンを使用すれば、

自動で、「SUM関数」が「SUBTOTAL関数」に置き換わって処理してくれます。

うーーん、便利。しかし、同じように平均もと思って［平均］を選んでも、

通常通り［AVERAGE］関数が入ってしまいます。

つまり、フィルター抽出後の合計を出したい場合は「オートSUM」ボタンを使えば早く済み、それ以外は「SUBTOTAL関数」を使おうという感じが良いかもしれません。

では、今回は平均を算出してみましょう。

関数を入れるセルを選択して、［関数の挿入］ボタンをクリック。

「SUBTOTAL」を選んで［OK］をクリック。［関数の挿入］ダイアログボックスでは、［すべて表示］を選ぶか、［関数の分類］で「数学/三角」を選ぶと探しやすいですよ。

下が［関数の引数］を入力する画面です。

 

---

SUBTOTAL関数・・・自分で集計方法を指定して集計する

=SUBTOTAL（集計方法,範囲1,[範囲2],…）

• 集計方法・・・集計に使用する関数を数字で指定
• 範囲1・・・集計する範囲を指定

---

【特徴】

• 自分で集計方法を選ぶことができる
• 範囲内に他の集計があった場合は無視する
• フィルター機能を使用していた場合、抽出された行のみ計算する
• 非表示の行列を計算するかを選択することができる

 

［集計方法］には、番号を入力します。その番号によって使用する関数が変わります。

• ［集計方法1］・・・非表示になっている行の値を計算したい場合
• ［集計方法2］・・・非表示になっている行の値は計算したくない場合

※オートフィルタ後の集計はどちらも抽出された行のみの計算になります

 

今回は平均を集計したいので、［集計方法］には「1」を入力して、［参照1］に「D3:D17」を指定すればOK。

これで、抽出後の見た目上のセルだけ計算してくれます。

まとめ

途中で紹介したように、非表示のセルを計算するかしないかを選ぶことができますが、縦方向の計算の「行」に関してのみです。横方向の計算で、「列」の非表示セルは常に計算されます。

また、この機能は「集計」機能で使用される関数でもあります。

たとえば、［データ］タブ　→　［小計］をクリックして、

集計してみると、

下のように「SUBTOTAL関数」が使用されているのが確認できます。