Excel

手動で非表示にした行を合計したくないならSUBTOTAL( 109、合計範囲 )を使おう!

 

Excel(エクセル)関数の1つ、SUBTOTAL(サブトータル)関数は、指定した範囲を、指定した方法(合計・個数・空白ではないセル個数等)で集計する関数で、SUM関数やCOUNT関数、COUNTA関数、AVERAGE関数などのような機能を持っています。

 

この記事では、知っておいてほしい、SUBTOTAL関数を使って、手動で非表示にした行を合計しないようにするテクニックを紹介します。

非表示にしたときには合計したくない

 

関連記事小計が複数あるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう!

 

サブトータル 書式
SUBTOTAL関数 集計方法 , セル範囲 

 

 

SUBTOTAL関数の書式

SUBTOTAL(サブトータル)関数は、指定した範囲を、指定した方法(合計・平均・個数等)で集計する関数です。

[=SUBTOTAL( 集計方法 , セル範囲 )]という書式で表します。

 

集計方法
1~11
101~111のいずれかの数値(番号)で指定します。

 

セル範囲
セル範囲は 1つだけでなく、2つ以上も指定できます。

 

集計方法

SUBTOTAL関数は、集計方法を1~11101~111のいずれかの数値(番号)で指定します。

手動で非表示にした行合計しないようにするには、第1引数・数値(番号)を、109 としてください。

 

フィルタを使って非表示にするときは、109でも9でもかまいませんが、行を手動で非表示にすることがあり、合計したくないときは、109としてください。

関連記事フィルタと組み合わせるときはSUM関数よりSUBTOTAL( 9、合計範囲 )を使おう!

 

 

SUBTOTAL関数、集計方法[109]

SUBTOTAL関数の代表的な使い方である、集計方法9とする場合と109とする場合とを比較しながら解説します。

 

手動で非表示にする

今、以下のような表があるとします。

行を非表示にする前

 

SUBTOTAL関数、集計方法[9]の場合

C5セル、C10セル、C11セルには、以下のようなSUBTOTAL関数を使った式が入力されています。

C5セル =SUBTOTAL(9,C2:C4)
C10セル =SUBTOTAL(9,C6:C9)
C11セル =SUBTOTAL(9,C2:C10)

 

6行目~8行目を非表示にする

この表の6行目~8行目を非表示にしてみます。

6行目~8行目を非表示にするには、6行目~8行目を選択し、[Ctrl]+[9]と押すか、右クリックして非表示を選択してください。

非表示にしたセルの売上を含んでいる

 

合計には、非表示にしたセルの売上が含まれています

 

 

SUBTOTAL関数、集計方法[109]の場合

C5セル、C10セル、C11セルには、以下のようなSUBTOTAL関数を使った式が入力されています。

C5セル =SUBTOTAL(109,C2:C4)
C10セル =SUBTOTAL(109,C6:C9)
C11セル =SUBTOTAL(109,C2:C10)

 

6行目~8行目を非表示にする

先ほどと同じく、6行目~8行目を非表示にしてみます。

非表示にしたセルの売上を含んでいない

 

合計には、非表示にしたセルの売上が含まれていません

手動で非表示にした行合計しないようにするには、第1引数・数値(番号)を、109 とします。

 

 

フィルタで非表示にする

今度は、フィルタを使って非表示にした場合の第1引数・数値(番号)9 と、109 の違いを確認します。

 

SUBTOTAL関数、集計方法[9]の場合

フィルタを使って、C6セル~C8セル(営業2課)を非表示にしてみます。

※営業1課と営業3課が表示されるようにする

フィルタで非表示になったセルの売上を含んでいない

 

手動で非表示にしたときとは異なり、フィルタを使って非表示にしたセル(営業2課)の売上は含まれません

 




 

SUBTOTAL関数、集計方法[109]の場合

同じく、フィルタを使って、C6セル~C8セル(営業2課)を非表示にしてみます。

※営業1課と営業3課が表示されるようにする

フィルタで非表示になったセルの売上を含んでいない

 

手動で非表示にしたときと同じく、フィルタを使って非表示にしても、売上は含まれません

SUBTOTAL関数にフィルタをかけた

 

集計方法[109]と[9]の違いのまとめ

SUBTOTAL関数の第1引数・集計方法の[109]と[9]の違いを表すと次の表とおりです。

この違いを理解して、SUBTOTAL関数を使ってください。

手動で非表示にする フィルタで非表示にする
第1引数・集計方法[109] 合計されない 合計されない
第1引数・集計方法[9] 合計される 合計されない

 

 

おすすめ・関連記事

参考文献

-Excel
-

© 2021 まなびっと Powered by AFFINGER5