SUBTOTAL 関数は、リスト (一覧形式の表、テーブル) のデータの集計値を返す関数です。

むかーしから、Excel で活用されている関数ですが、実はご存じなかったり自ら使おうとする方が少ないような気がしますので、今更感はありますがご紹介します。

構文 (書式) はこんな感じ↓。セル範囲は 1 つだけでなく 2 つ以上も指定できますが、ここではシンプルに 1 つだけ書いてあります。詳細が知りたい方はヘルプをご覧ください。

構文：＝SUBTOTAL(集計方法，セル範囲)

 

「(合計なのか、平均なのか、個数なのかなど) 指定した方法で」「この列 (セル範囲) を計算してね」と指定する、ということです。

通常、セル範囲の合計値を求めるときには SUM 関数を使用し、平均を求めるときには AVERAGE 関数を使用しますが、SUBTOTAL 関数の場合は、第 1 引数の指定によって集計の種類を変更できます。

でも、数式内に言葉で「合計」とか「平均」のように書くわけにいかないので、合計の場合は 9、平均の場合は 1、というように数字で指定します。

たとえば、「=SUBTOTAL(9,セル範囲)」と指定すると合計が、「=SUBTOTAL(1,セル範囲)」と指定すると平均が求められます。

どの集計方法がどの数字なのかはヘルプにもありますし、数式を作るときの支援機能でもでてきますが、こんな感じ↓。(いまは その 2 は気にしなくてよいです)
01.png
 

 

SUBTOTAL 関数の数式

たとえば、下図のセル A10：E30 (データ部分は A11：E30) のリストの [金額] 列の数値を SUBTOTAL 関数を使って合計してみます。
02.png

 

  1. 数式を作成するときに、「=SUBTOTAL(」のように最初の括弧までを入力すると、集計方法のリストが表示されるので、使いたい集計方法を数字で選びます。

    [↓] キーで移動していって [Tab] キーで選ぶこともできるし、数字を手入力しても OK です。
    03.png

    04.png
     
  2. 集計するセル範囲 (ここでは [金額] 列) を選択 / 指定して、「)」で数式を閉じて確定します。
    05.png
     
  3. 数式が作成され、合計値が表示されます。
    06.png


ん？でもこれだと SUM 関数で合計しても同じじゃない？と思いますよね。

うん。合計を求めるだけならそう。でも、それだけじゃない SUBTOTAL 関数を使う理由がいくつかあるのです。

 

フィルターと組み合わせて使う

リストでフィルターを使ってデータを抽出すると、条件に一致しないデータは非表示になります。

たとえば、下図の青枠には、SUM 関数による数式 (=SUM(E11:E30)) で E11：E30 の合計値が算出されていますが、フィルターを実行してもその合計値に変化はなく、フィルターによって非表示になっているセルの値も含まれています。

そりゃそうです。非表示かどうかは関係なく、指定したセル範囲が合計されるんだもの。

一方、赤枠の SUBTOTAL 関数による数式 (=SUBTOTAL(9,E11:E30)) の場合は、フィルターによって非表示になっているセルの値は計算に含まれておらず、結果的に抽出結果だけを対象に合計値を求めることができています。

フィルターで抽出されている (画面に表示されている) データだけを対象に数値の合計やデータの個数を求めたりするときは SUBTOTAL 関数を利用します。

07.png

 

"非表示" って何

行の [非表示] を設定するとその行はワークシートから非表示になりますが、SUBTOTAL 関数では、この設定によって非表示になっているセルを計算に含めるかどうかを制御できます。

書式で指定したセル範囲の中に、行の非表示によって表示されていないセルが含まれているとき、そのセルも集計に含む場合は第 1 引数に下表の「1 ～ 11」を、含まない場合は「101 ～ 111」を指定します。

含む場合は小さいほう、除外したかったら大きいほうの数字を選びます。

ただし、前述のフィルターによって表示されていないセルは、「1 ～ 11」と「101 ～ 111」のどちらを指定しても結果に違いがなく、条件に一致せずに非表示になっている行 (セル) は集計に含まれません。(どっちでもいっしょってこと)

08.png


下図の青枠には SUM 関数による数式が、赤枠には SUBTOTAL 関数によって合計値を求める数式が作成されています。
09.png

SUBTOTAL 関数の数式で、集計方法として「9」を指定しているため、行の [非表示] によって表示されていないセルも計算に含まれています。
10.png

SUBTOTAL 関数の数式の集計方法を「109」に変更すると、行の非表示によって表示されていないセルは計算から除外されます。
11.png
 

しつこいですがもう 1 回。

フィルターによって表示されていないセルは、「1 ～ 11」と「101 ～ 111」のどちらを指定しても結果に違いがなく、条件に一致せずに非表示になっている行 (セル) は集計に含まれません。手動で非表示にしたセルの場合に違いが出ます。

 

SUBTOTAL 関数を使う理由の 1 つはフィルターとの組み合わせで、「条件に一致するデータは何件あるんだろう？」とか「条件に一致するデータの合計値はいくつだろう？」ということが知りたいときに使います。

「非表示」は手動の設定による非表示だけじゃないんだけれど、長くなるので その 2 で書こうかな、と思っています。


石田 かのこ