SUBTOTAL 関数 - シートの達人

SUBTOTAL 関数

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 その他…表示数を減らす

ここでは、Microsoft Excel の SUBTOTAL 関数の書式および使用法について説明します。

説明

リストまたはデータベースの集計値を返します。通常は、Excel デスクトップアプリケーションの [データ] タブの [アウトライン] で [小計] を使用して、リストに集計行を挿入する方が簡単です。作成した集計リストを修正するときは、SUBTOTAL 関数を編集します。

構文

SUBTOTAL(集計方法,範囲 1,[範囲 2],…)

SUBTOTAL 関数の書式には、次の引数があります。

集計方法 必ず指定します。番号 1 ~ 11 または 101 ~ 111 を集計に使用する関数として指定します。 1 ~ 11 には手動で非表示にした行が含まれるのに対して、101 ~ 111 ではそれらを除外します。つまり、フィルター処理されたセルは常に除外されます。

Function_num(非表示の行を含む) |
Function_num(非表示の行は無視されます) | 関数
—|—|—
1 | 101 | AVERAGE
2 | 102 | COUNT
3 | 103 | COUNTA
4 | 104 | MAX
5 | 105 | MIN
6 | 106 | PRODUCT
7 | 107 | STDEV
8 | 108 | STDEVP
9 | 109 | SUM
10 | 110 | VAR
11 | 111 | VARP

範囲 1 必ず指定します。集計する最初の名前付き範囲または参照を指定します。
範囲 2,… 省略可能です。集計する名前付き範囲または参照を 2 ～ 254 個まで指定します。

解説

範囲 1、範囲 2などの範囲内に他の集計がある場合 (または入れ子になった集計)、それらの入れ子になった集計は、二重計算を回避するために無視されます。
集計方法として 1 ～ 11 の定数を指定すると、集計には、Excel デスクトップアプリケーションの [ホーム] タブの [セル] で [書式] をクリックし、[非表示/再表示] サブメニューの [行を表示しない] を使用して非表示にした行の値も含められます。リストで非表示になっている行と表示されている行を集計する場合は、これらの定数を使用します。集計方法として 101 ～ 111 の定数を指定すると、[行を表示しない] で非表示にした行は無視されます。リストで表示されている行だけを集計する場合は、これらの定数を使用します。
SUBTOTAL 関数では、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。
SUBTOTAL 関数では、列のデータ、つまり縦方向の範囲が集計されます。行のデータ、つまり横方向の範囲を集計するための関数ではありません。たとえば、集計方法として 101 以上の値を指定し、SUBTOTAL(109,B2:G2) のように横方向の範囲を集計する場合、いずれかの列を非表示にしても集計結果は変わりません。縦方向の範囲を集計する場合は、いずれかの行を非表示にすると集計結果に影響します。
参照先に 3-D 参照が含まれている場合は、エラー値 #VALUE! が返されます。

使用例

次の表のサンプルデータをコピーし、新しい Excel ワークシートのセル A1 に貼り付けます。数式を選択して、F2 キーを押し、さらに Enter キーを押すと、結果が表示されます。必要に応じて、列幅を調整してすべてのデータを表示してください。

データ
120
10
150
23
数式	説明	結果
=SUBTOTAL(9,A2:A5)	セル A2 から A5 までの集計値の合計を計算します (最初の引数として 9 を指定)。	303
=SUBTOTAL(1,A2:A5)	セル A2 から A5 までの集計値の平均を計算します (最初の引数として 1 を指定)。	75.75
メモ

SUBTOTAL 関数には常に、最初の引数として数値引数 (1 から 11、101 から 111) を指定する必要があります。この数値引数は、後続の引数として指定された範囲 (セルの範囲、名前付き範囲) の値を集計する方法として適用されます。