コスパ最強の格安中古パソコンはこちら!

SUBTOTAL関数の使い方と集計方法の指定

SUBTOTAL関数の使い方と集計方法

SUBTOTAL関数は、指定した範囲の数値の計算を行う関数で、「合計」「平均」「最大値」など11種類の算出を行うことができる関数です。

とても便利な関数で、集計表の計算にはよく使用されます。

この関数を使えると、ワンランク上のエクセルユーザーへと進化できます。

また、SUBTOTAL関数はフィルター後の表示されているセルのみを合計したりすることができるため、重宝されています。

少し複雑になりますが、より高度な集計を行えるAGGREGATE関数というものも存在しますが、簡単な集計ならSUBTOTAL関数で十分です。

AGGREGATE関数の使い方AGGREGATE関数の使い方と集計方法

SUBTOTAL関数の使い方

SUBTOTAL関数の「SUBTOTAL」を日本語に訳すと、「小計」という意味になります。

そのため、集計表の担当者などの条件に付随する小計を計算するのを目的に作られた関数です。

小計と言われると直感的に使いどころが分からない?

SUBTOTAL関数の入力規則

SUBTOTAL関数は集計方法と、計算範囲の2つを指定します。

項目内容
規則=SUBTOTAL( 集計方法 , 参照1 , [参照2] , … )
集計方法参照範囲に対して集計する方法を指定します。(集計方法の種類を参照)
参照集計を行うセルの範囲を指定します。複数範囲の指定が可能です。

入力サンプル(合計)

= SUBTOTAL ( 9 , A1:A4 )

上記の計算式で、A1~A4の数値を合計するという意味になります。

入力サンプル(平均)

= SUBTOTAL ( 1 , A1:A4 , B1:B4 )

上記の計算式で、A1~A4とB1~B4の数値の平均を求めるという意味になります。

集計方法の種類

SUBTOTAL関数の第一引数として指定できる集計方法は下記の通りです。

集計方法集計内容機能関数
1(101)平均値を求めるAVERAGE
2(102)数値の個数を求めるCOUNT
3(103)データのある個数を求めるCOUNTA
4(104)最大値を求めるMAX
5(105)最小値を求めるMIN
6(106)積を求めるPRODUCT
7(107)不偏標準偏差を求めるSTDEV.S
8(108)標準偏差を求めるSTDEV.P
9(109)合計値を求めるSUM
10(110)不偏分散を求めるVAR.S
11(111)分散を求めるVAR.P

関数入力時にこのように予測変換として表示されるので、特に覚える必要はありません。

SUBTOTAL関数の予測変換

100以降の数値は、非表示行の扱いの切り分けに使用するよ。

非表示行を対象外とする

SUBTOTAL関数を使用して集計すれば、フィルター機能を使用して非表示となっているセルはデフォルトで集計の対象外となります。

フィルターとは無関係に非表示とされているセルを集計の対象とするか対象外とするかは、集計方法の指定で指定することができます。

  • 集計方法の指定が100未満:集計の対象とする
  • 集計方法の指定が100以上:集計の対象外とする
非表示行を対象外

このために100以降の数字があるんだね。
見えている数値のみ合計する方法【エクセル】見えているセルのみを合計・カウントする方法|非表示行を除外

SUBTOTAL関数の入力手順

SUBTOTAL関数の入力手順を紹介します。

エクセル関数の入力について

エクセル関数は関数の挿入ではなく、予測変換を使用して入力するのがおすすめです。詳しくは「関数の挿入を使わないで予測変換を使う理由3つ」をご覧ください。

集計結果を表示したいセルに「=SUB」と入力すると、予測変換で「SUBTOTAL」が表示されるので、上下キーで選択後にTabを押します。

SUBTOTAL関数の予測変換

集計方法の指定に移るので、該当する集計方法を数値で入力し、半角のカンマ(,)を入力します。(今回は数値の合計を行います。)

SUBTOTAL関数の集計方法を指定する

続いて、集計する範囲を指定しましょう。範囲が複数ある場合は半角のカンマで区切って入力してください。

SUBTOTAL関数の参照範囲を指定する

範囲の指定が終われば、Enterを入力し確定しましょう。

SUBTOTAL関数の結果が表示される

集計方法さえ指定できれば、普通に範囲を指定するだけだね。

SUBTOTAL関数の注意点

SUBTOTAL関数を使用するにあたっての注意点を記載しておきます。

SUBTOTAL関数は集計されない

SUBTOTAL関数は、SUBTOTAL関数の集計の対象外となります。

そのため、下図のようにSUBTOTAL関数を含んだ範囲を指定しても、小計を除外して計算結果を求めることができます。

SUBTOTAL関数はSUBTOTAL関数で集計されない

SUM関数は集計されるから注意!

非表示列は集計される

SUBTOTAL関数自体は集計の対象とらないことからも分かるように、縦方向に使用されることを基本に考えられています。

そのため、100以降の数値を指定(非表示を対象外)しても横向きに参照範囲を指定した場合は非表示列も集計されるので注意しましょう。

非表示列は集計される

数値のベタ打ちは不可

SUBTOTAL関数は、数値のベタ打ちはエラーとなります。

SUBTOTAL関数は数値のベタ打ち不可

参照先には、範囲を正しく指定するようにしましょう。

おわりに

数種類の集計を行うことができるSUBTOTAL関数の使い方を紹介しました。

少し複雑になりますが、より高度な集計を行うことができるAGGREGATE関数もあるので、いっしょに見ておきましょう。

AGGREGATE関数の使い方AGGREGATE関数の使い方と集計方法

集計するデータが複雑になればなるほど、集計の関数をうまく使うのが重要です。

コメントを残す

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