エクセル 非表示のセルを除いて合計や平均などの集計をする

はじめに

エクセルの非表示のセルを除いて合計や平均などの集計をする方法を紹介します。

「SUBTOTAL 関数」を使用して、非表示のセルを除いて合計や平均などを計算できます。

フィルタを使用して非表示になっているセルを除いて、表示されているセルの値だけを「SUM 関数」で集計できます。

より高機能な「AGGREGATE 関数」を使用してもできますが、フィルタの対象に含まれてしまいます。

非表示を除いてセルの合計を求める

表示されているセルだけの合計を求めるには「SUBTOTAL 関数」を使用します。上位互換である「AGGREGATE 関数」も使用できますが、フィルタとの相性がよくありません。

SUBTOTAL 関数

「SUBTOTAL 関数」の第 1 引数に 109 を入力して、第 2 引数にセルの範囲を指定します。

フィルタで非表示にしたり、行を右クリックして行を非表示にします。

表示されているセルだけで合計を求められます。

合計以外にも平均や最大値やセルの数などを集計できます。

AGGREGATE 関数

先にフィルタや行を右クリックして行を非表示にしておきます。

「AGGREGATE 関数」の第 1 引数に 9 を入力し、第 2 引数に 3 を入力して、第 3 引数にセルの範囲を指定します。

表示されているセルだけを集計できます。

ただし、フィルタとの相性がよくありません。フィルタの範囲に「AGGREGATE 関数」を入力したセルも含まれてしまいます。そのため、フィルタすると集計値もフィルタされてしまいます。「SUBTOTAL 関数」ならフィルタに含まれません。

データの範囲と「AGGREGATE 関数」の間を一行空けるなどの対策が必要です。

テーブルの機能を使用する

[ホーム] タブをクリックし、スタイルグループにある [テーブルとして書式設定] から表をテーブルにします。

テーブルを選択して [デザイン] タブをクリックし、[集計行] をチェックします。

集計行の [▼] から合計や平均など選択して、表示されているセルだけを集計できます。この方法では自動的に「SUBTOTAL 関数」が使用されています。