フィルターで抽出したデータだけを合計する方法

Excelには、「合計」や「平均」などの集計値を算出できる「SUBTOTAL」という関数が用意されている。通常、「合計」を求めるときは関数SUM、「平均」を求めるときは関数AVERAGEを使用するのが一般的であるが、少し特殊な状況では思い通りの結果を得られない場合もある。このような場合に備えて、関数SUBTOTALの使い方も覚えておくとよい。

データの抽出と関数SUMで算出した合計値

関数SUBTOTALは、「合計」や「平均」、「最大値」、「最小値」、「標準偏差」などの指標を算出できる関数だ。これらの指標は関数SUMや関数AVERAGEなどで求めるのが一般的だが、関数SUBTOTALを使うと少し特殊な状況にも対応できるようになる。今回は「合計」を求める場合を中心に、関数SUBTOTALならではの特長を紹介していこう。

• 「抽出データ」や「小計を含む表」の集計に使える関数SUBTOTAL

まずは、例として使用するデータ表を紹介する。以下に示した図は、ある公演におけるチケットの売上状況をまとめたものだ。データを分析しやすいように、「公演日」、「前売り券／当日券」、「S席／A席／B席」、「一般／学割／シリア割」を分類した形で、それぞれの「チケット単価」、「販売数」、「金額」（チケット単価×販売数）を入力してある。

• 「チケットの売上状況」をまとめた表

この表から「販売数」と「金額」の合計を求めるケースを考えてみよう。この場合、「=SUM(G4:G33)」や「=SUM(H4:H33)」といった具合に、関数SUMを入力するのが最も簡単な手法となる。今回の例では、「販売数」の合計は863枚、「金額」の合計は約301万円という結果が得られた。

• 「販売数」と「金額」の合計

以上で作業完了となるのであれば、上記は特に問題のない処理手順といえる。しかし、「フィルター機能を使ってデータの傾向を分析したい」となると、関数SUMでは力不足になってしまう。

たとえば、フィルター機能を使って「前売り」チケットのデータだけを抽出してみると、以下の図のような結果になる。

• フィルター機能で「前売り」のデータだけを抽出した場合

フィルター機能により、区分が「前売り」のデータだけを抽出することには成功しているが、その下にある「合計」の数値は何も変化していない。この値は、全データを足し算した「合計」であり、画面に表示されているデータと連動する形にはなっていない。

通常、こういった形で分析を行うときは、「画面に表示されているデータについてのみ合計を算出したい」と考えるのが一般的ではないだろうか？　このような場合に活用できるのが「関数SUBTOTAL」となる。