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

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」となる。

表示データだけを集計できる関数SUBTOTAL

それでは、関数SUBTOTALの使い方を紹介していこう。関数SUBTOTALの第1引数には、「データの集計方法」を1～11の数値で指定する。続いて、第2引数に「データを集計するセル範囲」を指定する。

◆関数SUBTOTALの書式
　=SUBTOTAL(集計方法, 範囲1, [範囲2], ・・・)

・第1引数（集計方法）に指定できる値
　　1：平均（AVERAGE）
　　2：数値の個数（COUNT）
　　3：データの個数（COUNTA）
　　4：最大値（MAX）
　　5：最小値（MIN）
　　6：積（PRODUCT）
　　7：不偏標準偏差（STDEV.S）
　　8：標準偏差（STDEV.P）
　　9：合計（SUM）
　　10：不偏分散（VAR.S）
　　11：分散（VAR.P）

具体的な入力例を示していこう。「=SUBTOTAL(」の部分まで関数を入力すると、第1引数（集計方法）に指定可能な数値が一覧表示される。

関数SUBTOTALの入力（1）

この一覧を参考にしながら第1引数（集計方法）を指定すればよい。よって、先ほど示した「1～11の数値」と「集計方法」の対応を暗記しておく必要はない。たとえば「合計」を求めたいときは、関数SUMに対応する「9」を入力する。

続いて、第2引数に「集計するセル範囲」を指定する。この際に注意すべき点は、全データのセル範囲を指定すること。今回の例では、ワークシートの4行目から33行目にデータが入力されているので、「G4:G33」のセル範囲を指定する必要がある。

関数SUBTOTALの入力（2）

上図のように、すでにフィルター機能でデータを抽出している場合は、「何行目から何行目までデータが入力されているのか？」を把握しづらい状況になってしまう。よって、本来であれば、フィルターを解除した状態（全データを表示した状態）で関数SUBTOTALを入力するのが基本といえる。

「Enter」キーを押して関数を実行すると、以下の図のような結果が表示された。

算出された合計値

この計算結果は、“画面に表示されているデータ”の集計結果となる。今回の例では集計方法に「合計」を指定しているため、「前売りチケットの販売数の合計は233枚」という結果になる。つまり、全部で863枚のうち、233枚が「前売り」として販売された、ということを読み取れる訳だ。

同様の手順で「金額」の合計も算出してみよう。関数SUBTOTALの第2引数（集計するセル範囲）が変化するだけで、基本的な考え方は先ほどと同じだ。

関数SUBTOTALの入力

こちらは約83万円という計算結果が表示された。つまり、全部で約301万円ある売上のうち、約83万円が「前売り」として販売された、ということになる。

算出された合計値

フィルターの抽出条件を変化させると、それに応じて関数SUBTOTALの結果も自動的に再計算される。たとえば、席を「A」または「B」に限定する条件を追加すると、関数SUBTOTALの計算結果は以下の図のように変化する。

抽出の条件を変更した場合

このように、“画面に表示されているデータ”についてのみ「合計」を算出できるのが関数SUBTOTALの特長となる。もちろん、「合計」以外の指標を関数SUBTOTALで算出することも可能だ。たとえば、第1引数に「1」を指定すると、“画面に表示されているデータ”についてのみ「平均」を求めることが可能となる。