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

はじめに

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

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

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

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

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

SUBTOTAL 関数の引数

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

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

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

引数「集計方法」

使い方

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

集計方法を指定する

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

SUBTOTAL 関数の結果を無視する

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

非表示の行を無視する

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

解説

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

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

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

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

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

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

AGGREGATE 関数との違い

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

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

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

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

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

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

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