2013.02.07 カテゴリ：EXCEL

SUBTOTAL関数の使い方

こんにちは、市民パソコン教室北九州小倉校の篠原です。


今日は、エクセル（Excel）のSUBTOTAL関数についてです。

SUBTOTAL関数の書式
　=SUBTOTAL(集計方法番号,範囲１,範囲２,・・・・・,範囲254）

SUBTOTAL関数について

　データの集計をする場合は、よく使う関数に、SUM関数がありますが、ここで紹介するSUBTOTAL関数も集計を行う関数です。
　SUM関数との違いは、一つの関数で、多種類の演算を行うことが出来る点と、集計の仕方に特徴があります。
　SUBTOTAL関数で使用できる演算の一覧です。

集計方法番号
(非表示のセルを含める）		集計方法番号
(非表示のセルを除く）		同機能の関数関数の意味
1101AVERAGE平均
2102COUNT個数
3103COUNTA個数(空白を除く)
4104MAX最大値
5105MIN最小値
6106PRODUCT引数の掛け算
7107STDEV母集団の標準偏差の推定値（Excel2007以前の互換性維持のために残されています。後継はSTDEV.S
8108STDEV.P母集団の標準偏差
9109SUM合計
10110VAR母集団の分散の推定値（Excel2007以前の互換性維持のために残されています。後継はVAR.S
11111VAR.P母集団の分散

SUBTOTAL関数の特徴

　ここで大切なことを一つ。

SUBTOTAL関数での集計の特徴は
◆1◆SUBTOTAL関数は、フィルターを使用した場合、集計方法番号にかかわらず、フィルターの結果として表示されなくなったセルのデータは集計から除外されます。
◆2◆A2:A50のように、同じ列での集計では、集計方法番号によりセルの表示・非表示で集計結果が変わる。
◆3◆A2:Z2のように、列が異なる範囲の集計では、集計方法番号によりセルの表示・非表示で集計結果が変わることはない。（非表
示のセルを含める計算となる）Excelヘルプには、「横方向の範囲を集計するための関数ではありません」と書いてありました。
◆4◆SUBTOTAL関数の引数の範囲の中に、SUBTOTAL関数の結果が含まれるときは、その範囲内のSUBTOTAL関数の集計結果は、集計から除外される。
◆5◆串刺しによる集計では、エラーになります。

SUBTOTAL関数の使用例

　では、実際の関数の使い方を見てみましょう。

図1-1　SUBTOTAL関数を使って、集計表を作りました。
SUBTOTAL関数の説明


図2-1　結果を表示して見ました。
SUBTOTAL関数の説明

　それぞれの関数の働き方を見てみましょう。

図3-1　フィルターを使って、3行目が非表示になっています。
　まず集計結果を見てみましょう。

  • A列は、非表示セルを含んで集計するように設定されています。しかし、フィルターにより3行目が非表示になっているため、セルA3のデータは無視され、A5の集計結果は「2」となっています。特徴◆1◆の例です。
  • B列は、非表示セルを含まないで集計するように設定されています。フィルターにより3行目が非表示になっています。この場合、どちらの条件も当てはまりますので、セルA3のデータは無視され、集計されています。特徴◆1◆の例です。
  • C列は、列をまたいだ集計を行っています。集計は、非表示セルを含んで集計する設定になっています。特徴◆3◆
  • D列は、列をまたいだ集計を行っています。集計は、非表示セルを含まないで集計する設定になっています。集計結果は、C列もD列も同じ結果です。（C5の集計結果が、0(ゼロ)になっているのは、範囲A5：B5はSUBTOTAL関数により集計された結果だからです◆4
    ◆）
  • E列は、比較として、SUM関数で集計をしてみた結果です。E18の合計は、E列全てのデータの付いて(それぞれのSUM関数の結果も）集計がされているのが分かります。
    SUBTOTAL関数の説明

図4-1　行の非表示でセルを非表示にしています。

  • A列は、非表示セルを含んで集計するように設定されています。結果のとおり、セルの非表示により3行目が非表示になっていますが、セルA3のデータは集計に含まれ、A5の集計結果は「3」となっています。特徴◆2◆の例です。
  • B列は、非表示セルを含まないで集計するように設定されています。セルの非表示により3行目が非表示になっています。このため、セルA3のデータは無視され、B5の結果は「2」となっています。特徴◆2◆の例です。
  • C列は、列をまたいだ集計を行っています。集計は、非表示セルを含んで集計する設定になっています。特徴◆3◆
  • D列は、列をまたいだ集計を行っています。集計は、非表示セルを含まないで集計する設定になっています。集計結果は、C列もD列も同じ結果です。（C5の集計結果が、0(ゼロ)になっているのは、範囲A5：B5はSUBTOTAL関数により集計された結果だからです◆4
    ◆）
    SUBTOTAL関数の説明

図5-1　串刺し計算をしようとした例です。
特徴◆5◆の例で、結果として「#VALUE!」となり、エラーになっています。
SUBTOTAL関数の説明

SUBTOTAL関数が使用される場面、

　月別の集計と年間合計や、グループでソートされた表のグループ別の集計と合計などには非常に役に立つ関数だと思います。
　また、SUBTOTAL関数は、「データ」タブの「小計」の機能を使うことで、グループごとの小計を自動的にSUBTOTAL関数を使って集計してくれる機能もあります。

以上で、SUBTOTAL関数については終わりです。




市民パソコン教室北九州小倉校の「パソコン無料相談」も見てね。

コメント


認証コード5272

コメントは管理者の承認後に表示されます。