【Excelの関数】SUBTOTAL関数の使い方

SUBTOTAL関数は、様々な集計を行う関数です。SUM関数などの集計関数と似ていますが、SUBTOTAL関数では表示しているセルのみ集計することができます。

  • SUBTOTAL関数とは
    指定した方法で集計を行う関数です。 =SUBTOTAL(集計方法,範囲1,範囲2,..)

    のように記述します。「集計方法」の部分に1~11の値を指定することにより、どのような集計を行うかが決まります。

    1:AVERAGE(平均を求める)
    2:COUNT(数値の入ったセル数を求める)
    3:COUNTA(数値や文字の入ったセル数を求める)
    4:MAX(最大値を求める)
    5:MIN(最小値を求める)
    6:PRODUCT(積を求める)
    7:STDEV(標準偏差の推定値を求める)
    8:STDEVP(標準偏差を求める
    9:SUM(合計を求める)
    10:VAR(不偏分散を求める)
    11:VARP(標本分散を求める)

    つまり、
    =SUBTOTAL(9,A1:A10)
    と記述すると、A1からA10までの合計を表示します。

  • 使い方の例
    では、SUM関数などを直接使用するのと何が違うのでしょうか。まず異なるのは、SUBTOTAL関数では非表示のセルは計算対象から外すことができます。上述した「集計方法」に1~11の値を指定したときは、非表示セルも含めた結果になります。101~111の値を指定すると、非表示セルを含めない結果になります。

    ~非表示セルを含めない集計方法の指定~
    101:AVERAGE(平均を求める)
    102:COUNT(数値の入ったセル数を求める)
    103:COUNTA(数値や文字の入ったセル数を求める)
    104:MAX(最大値を求める)
    105:MIN(最小値を求める)
    106:PRODUCT(積を求める)
    107:STDEV(標準偏差の推定値を求める)
    108:STDEVP(標準偏差を求める
    109:SUM(合計を求める)
    110:VAR(不偏分散を求める)
    111:VARP(標本分散を求める)

    非表示のセルとは、列や行を「表示しない」と設定している場合や、オートフィルタで表示されていないセルのことです。

    また、SUBTOTAL関数は、集計にSUBTOTAL関数の結果を含みません。たとえば、A列に下記のようなデータがあったとします。Excel環境のある方は、A1セルにコピーペーストしてみてください。

    100
    200
    300
    400
    =SUBTOTAL(9,A1:A4)

    A5セルの集計結果は「1000」となります。ここでA6セルに「=SUBTOTAL(9,A1:A5)」と入力すると、結果はやはり「1000」になります。データの途中に小計をいれて、最後に合計を出すような場合に便利ですね。