トップページ > Excel エクセル関数 > SUBTOTAL関数 | セル範囲を様々に集計出来る優れもの。

subtotal_01

SUBTOTAL関数 | セル範囲を様々に集計出来る優れもの。


結構、喰わず嫌いな人が多いかもしれないですが、とっても便利な関数、SUBTOTAL関数を取り上げてみたいと思います。

SUBTOTAL関数
種類 数学/三角関数
できること SUBTOTAL関数は指定したデータ範囲を指定の方法で集計した結果を返します。
書式 SUBTOTAL(小計の種類セル範囲1)
小計の種類
(必須)
11種類ある小計方法から指定します。
セル範囲1
(必須)
小計するセル範囲を指定します。
セル範囲1の他にセル範囲がある場合、「 , 」(カンマ)で区切って入力します。

SUBTOTAL関数を使って、セル範囲の小計を求めることが出来ます。
(1)セル範囲、と(2)小計の種類を与えることによって、SUBTOTAL関数は、指定されたデータの小計を返します。

一種類の計算だけを行うSUM関数、AVERAGE関数、COUNT関数などと異なり、SUBTOTAL関数は万能です。SUBTOTAL関数一つで、いくつものセルの合計や平均、個数を求めることが出来ます。

SUBTOTAL関数の構文は、次のようになっています。
= SUBTOTAL (小計の種類, セル範囲)

例を挙げると、=SUBTOTAL(9,A1:A10) は、セル範囲A1:A10にある全ての値の合計を返します。(※フィルタが適用されていない場合) 一つ目の引数”9″はSUBTOTAL関数では”合計”を表します。もし、値の個数を知りたければ、”2″を使います。

なんだか、SUBTOTAL関数は、他の関数とはちょっと違っているみたいですね。
ちょっとクセが有るかもしれませんが、使いこなせるとエクセルの可能性が広がるので、SUBTOTAL関数の特徴をじっくり見ていきましょう。

[1]SUBTOTAL関数でフィルタされた値の合計を得ることができる。

って、何のことか分かりますかね?
オートフィルタ機能を使った際に、関数挿入ボタンで合計を挿入されると、=SUBTOTAL(9,A1:A10) という感じで、SUM関数ではなく、SUBTOTAL関数が挿入されます。
このように、値をフィルタしている際に、SUBTOTAL関数を使うとフィルタされた値だけを対象に集計します。
一方、SUM関数ではフィルタされた値も、フィルタで漏れた値も含めて、セル範囲に入っている値を全て集計します。

[2]SUBTOTAL関数で非表示された行の値も無視できます

しばしば、関連性が無い値を取り除く為に、行を非表示にする機能を使うことがあります。
一つ目の引数を変更することで、非表示になった行の値を無視して集計することができます。

<例> SUBTOTAL(9,A1:A10)は、セル範囲A1:A10にある全ての値の合計を返しますが、一つ目の引数を”9″から”109″に変更し、SUBTOTAL(109,A1:A10)とすることで表示されている行にある値だけを対象に合計を返します。

[3]SUBTOTAL関数で動的なデータ集計ができます。

SUBTOTAL関数では、集計種類がパラメータとなっているので、動的な集計をこのように行うことができます。
【ドロップダウンメニュー】で集計種別を選択すると、右に集計結果が自動更新で表示。

これは、エクセルでダッシュボードを作成したり、全てのデータを記載するスペースがない時に役立ちます。

[4]SUBTOTAL関数の集計範囲内にあるSUBTOTAL関数は無視します。

これはSUBTOTAL関数の目玉機能ではないでしょうか?SUBTOTAL関数(1)の集計範囲内に別のSUBTOTAL関数(2)がある場合、二重に集計されるのを防ぐ為に、SUBTOTAL関数(2)はSUBTOTAL関数(1)では、集計されずに集計対象から無視されます。
小計行が幾つもある表を作る際には、SUM関数を使って小計をしていると、総合計をする時に、小計行のSUM関数を漏れなく合計する必要があります。小計行にSUBTOTAL関数を使えば、全体の合計を出す際も、対象範囲をまるごとSUBTOTAL関数の集計対象範囲にするだけで、作業が簡単に、集計ミスも少なくなります。

逆に、小計行でSUM関数を使ってしまうと、せっかく総合計でSUBTOTAL関数を使っても、小計も合計され、二重で集計されてしまいます。

[5]エクセルの[データ]メニューを使って、SUBTOTAL欄(小計欄)を自動作成できる。

SUBTOTAL関数は便利なのですが、キッチリと成型された表にSUBTOTAL関数をポチポチ記入していくのは面倒なものです。でも、心配はご無用。エクセルの[データ]メニューにある[集計]メニューを使うと、全ての作業をあなたに代わって自動的に行ってくれます。
自動的にSUBTOTAL関数が挿入された集計表を作成するには、表になったデータを選択し、ツールバーから[データ] > [集計] と選択します。すると、[集計の設定]ダイアログが表示され、簡単に集計方法を指定することが出来ます。極めて簡単です。

Tagsカテゴリー:

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

トラックバックURL

http://excel.jiten.org/function/subtotal/trackback/