Excelで合計や平均を求めるときに関数を使いますが、非表示の行や列は計算結果から除外したい時があります。
非表示の行を計算させないときは、「SUBTOTAL」関数を使うと便利です。
下図はExcel2016です。
< スポンサーリンク >
SUBTOTAL関数
リストまたはデータベースの集計値を返します。
SUBTOTAL(集計方法,範囲 1,[範囲 2],…])
集計方法＝必須。リストの集計に使用する関数を、1 ～ 11 (非表示の値も含める) または 101 ～ 111 (非表示の値を無視する) の番号で指定。
範囲1＝必須。集計する最初の名前付き範囲または参照を指定。
範囲2＝省略可能。集計する名前付き範囲または参照を 2 ～ 254 個まで指定。
| 集計方法 (非表示の値も含める) | 集計方法 (非表示の値を無視する) | 関数 |
|---|---|---|
| 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 ～ 11 の定数を指定すると、集計には、Excel デスクトップ アプリケーションの [ホーム] タブの [セル] で [書式] をクリックし、[非表示/再表示] サブメニューの [行を表示しない] を使用して非表示にした行の値も含められます。 リストで非表示になっている行と表示されている行を集計する場合は、これらの定数を使用します。 集計方法として 101 ～ 111 の定数を指定すると、[行を表示しない] で非表示にした行は無視されます。 リストで表示されている行だけを集計する場合は、これらの定数を使用します。
- SUBTOTAL 関数では、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。
- SUBTOTAL 関数では、列のデータ、つまり縦方向の範囲が集計されます。 行のデータ、つまり横方向の範囲を集計するための関数ではありません。 たとえば、集計方法として 101 以上の値を指定し、SUBTOTAL(109,B2:G2) のように横方向の範囲を集計する場合、いずれかの列を非表示にしても集計結果は変わりません。 縦方向の範囲を集計する場合は、いずれかの行を非表示にすると集計結果に影響します。
- 参照先に 3-D 参照が含まれている場合は、エラー値 #VALUE! が返されます。
SUBTOTAL関数の使用例
SUBTOTAL関数を使って、合計を求めてみます。
A列は集計方法がSUMで非表示の値も含めています。
=SUBTOTAL(9,A2,B5)
B列は集計方法がSUMで非表示の値を無視しています。
=SUBTOTAL(109,A2,B5)
3行目を非表示にしてみると、A列とB列の結果が変わりました。
< スポンサーリンク >※広告先のお問い合わせは広告主様にお願いします
こんな記事も書いています