更新日:、 作成日:

エクセル SUBTOTAL 関数:表示されているセルのみ合計や平均を集計する

はじめに

エクセルの SUBTOTAL 関数の使い方を紹介します。

SUBTOTAL 関数は集計方法を選択して、表示されているセルのみ合計や平均を集計します。無視する値を選択できる上位互換のような「AGGREGATE 関数」があります。

=SUBTOTAL(102,A1:A3) のようにして、「COUNT 関数」と同じようにセルの数をカウントできます。

=SUBTOTAL(109,A1:A3) のようにして、「SUM 関数」と同じようにセルの合計を計算できます。

フィルタなどで非表示になっているセルを無視して、表示されているセルのみ集計できます。

フィルタを使って集計する方法は「非表示のセルを除いて合計や平均などの集計をする」をご覧ください。

SUBTOTAL 関数の引数

SUBTOTAL(集計方法, 範囲1)
SUBTOTAL(集計方法, 範囲1, 範囲2 …)
指定した集計方法ですべての範囲を集計します。

引数「集計方法」集計に使用する関数を番号で指定します。行が非表示になっているセルを含めるか選択できます。スピル化
引数「範囲」セルの範囲を指定できます。配列不可

スピル化:セルの範囲や配列を指定すると結果が「スピル」します。

配列不可:配列を指定できません。セルの範囲のみ指定できます。

引数「集計方法」

非表示のセルを
含める
非表示のセルを
含めない
関数 説明
1101AVERAGE 関数平均を求める
2102COUNT 関数数値のセルを数える
3103COUNTA 関数空白でないセルを数える
4104MAX 関数最大値を取得する
5105MIN 関数最小値を取得する
6106PRODUCT 関数掛け算で合計を求める
7107STDEV.S 関数母集団の標本から標準偏差を求める
8108STDEV.P 関数母集団の全体から標準偏差を求める
9109SUM 関数合計を求める
10110VAR.S 関数母集団の標本から不偏分散を求める
11111VAR.P 関数母集団の全体から不偏分散を求める

使い方

SUBTOTAL 関数の使い方を紹介します。

集計方法を指定する

指定した集計方法で計算します。

=SUBTOTAL(C3,$B$4:$B$8)
17

SUBTOTAL 関数の結果を無視する

SUBTOTAL 関数を無視してセルの数や合計を求めます。セル「B4」に SUBTOTAL 関数を入力しています。

=SUBTOTAL(B7,$B$2:$B$4)
18

非表示の行を無視する

非表示の行を無視してセルの数や合計を求めます。3 行目を非表示にしています。

=SUBTOTAL(B7,$B$2:$B$4)
19

解説

無視する値を選択できるよう上位互換のような「AGGREGATE 関数」があります。

引数「集計方法」に指定した値によって結果が変わります。それぞれの集計方法は、対応する関数と同じです。詳しくはその関数のページをご覧ください。

引数「集計方法」の非表示のセルを含めないというのは、行を右クリックから非表示にしているセルを無視します。

フィルタで非表示になっているセルは、引数「集計方法」の値にかかわらず無視します。

15

引数「範囲」の中に別の SUBTOTAL 関数または「AGGREGATE 関数」があるときは、引数「集計方法」の値にかかわらずそのセルの値を無視します。

セル「B2」~「B6」のような縦方向の集計を求めるために使用します。セル「B2」~「G2」のような横方向を集計するときは、列が非表示になっていても引数「集計方法」の値にかかわらず集計の対象になります。

AGGREGATE 関数との違い

  • SUBTOTAL 関数:フィルタの対象になりません。
  • AGGREGATE 関数:フィルタの対象になります。

フィルタの範囲に「AGGREGATE 関数」を入力したセルも含まれます。そのため、フィルタすると集計値もフィルタされます。SUBTOTAL 関数ならフィルタされません。

6

AGGREGATE 関数」なら、フィルタで非表示になっているセルの値を無視するか選択できます。SUBTOTAL 関数なら必ず無視されます。

AGGREGATE 関数」なら、引数「範囲」の中に別の SUBTOTAL 関数または「AGGREGATE 関数」の値を無視するか選択できます。SUBTOTAL 関数なら必ず無視されます。

セルが表示されているか判定

セルが表示されているか判定する ISVISIBLE 関数はありません。SUBTOTAL 関数や「AGGREGATE 関数」を使用して、セルが表示されているかどうかを取得できます。値が入力されていないセルも非表示として判定します。

=SUBTOTAL(103,B2)=1
=AGGREGATE(3,5,B2)=1

TRUE なら表示、FALSE なら非表示または空です。