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

INDEX

Introduction

Ver.　all　2013　2016　2019　365

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

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

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

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

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

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

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

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

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

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

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

知らない間に使われている時がある関数だにゃ

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

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

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

※フィルター抽出後の場合で、非表示セルを集計する場合は自動で置き換えはされません。

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

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

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

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

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

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

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

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

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

【特徴】

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

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

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

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

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

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

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

集計してみると、

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

データベース的な処理が多い人は覚えておくと便利だにゃ