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」を指定すると、“画面に表示されているデータ”についてのみ「平均」を求めることが可能となる。
行の非表示などにも対応する関数SUBTOTAL
フィルター機能を使ってデータを抽出する場合だけでなく、行を非表示にする場合にも関数SUBTOTALが活用できる。たとえば、先ほどの表で「7/16」のデータを非表示にすると、それに応じて関数SUBTOTALの計算結果も変化する。
行を非表示にする操作
関数SUBTOTALにより算出された合計値
上図の場合、画面に表示されている4件のデータについてのみ「販売数」と「金額」の合計が算出されることになる。このように、画面に表示されているデータだけを対象に「合計」などを算出できることが関数SUBTOTALの利点となる。使い方次第で便利に活用できるので、必ず覚えておこう。
途中に「小計」などを含む表の合計
途中に「小計」を含む表を作成するときも、関数SUBTOTALは重宝する存在となる。まずは、普通に関数SUMで「小計」や「合計」を算出した例（失敗例）を紹介しておこう。
以下の図は、先ほどのデータに「公演日ごとの小計」を挿入した例だ。「小計」の値は、「=SUM(G4:G13)」や「=SUM(H4:H13)」という具合に、普通に関数SUMで計算している。
途中に「小計」を含む表（関数SUMの記述）
同様の手順で、それぞれの「公演日」について「小計」を算出し、最後に全体の「合計」を算出する場合を考えてみよう。このとき、普通に関数SUMを入力すると、正しい合計ではなく、合計を2倍した数値が表示されてしまう。
関数SUMで「合計」を求めると・・・
合計を2倍した値が算出される（失敗例）
それもそのはず。上記の例では、各々のデータ（金額）だけでなく、それを集計した「小計」も計算の対象になっているからだ。その結果、“本来の合計”を2倍した数値が合計値として表示されてしまう。
このような場合にも「関数SUBTOTAL」が活用できる。関数SUBTOTALを使って「小計」や「合計」を求めた場合は、「小計」を除いた形で「合計」が算出される仕組みになっている。
先ほどの失敗例を正しく修正していこう。まずは、関数SUMではなく、関数SUBTOTALで「小計」を算出する。具体的には、第1引数に「9」、第2引数に「セル範囲」を指定すればよい。
関数SUBTOTALで「小計」を算出
この方法でも、指定したセル範囲の合計（小計）を求めることが可能だ。
算出された小計値
同様の手順で、他の「公演日」についても「小計」を関数SUBTOTALで算出していく。その後、全体の「合計」を算出する。こちらも関数SUBTOTALを使用する。具体的には、第1引数に「9」を指定し、第2引数に「小計を含むセル範囲」を指定すればよい。
関数SUBTOTALで「合計」を算出
このような手順で計算を実行していくと、「小計」を除いた形で「合計」を算出することが可能となる。つまり、正しい合計値を求められる訳だ。
算出された合計値
「小計」や「合計」という表現は少し抽象的なので、もっと厳密に補足説明をしておこう。関数SUBTOTALは、「他の関数SUBTOTALで計算した結果」を除外した形で計算を行う仕組みになっている。このため、途中に「小計」などの数値があっても、正しい計算結果を得ることが可能となっている。
Excel関数について多少なりとも勉強したことがある人にとって、SUBTOTALはそれなりに有名な関数といえる。よって、今回の連載で紹介した内容を「すでに知っていたよ！」という方も沢山いるだろう。そこで次回は、関数SUBTOTALの上位互換となる「関数AGGREGATE」の使い方を紹介していこう。
