フィルター SUBTOTAL関数
フィルター
2017-06-17 17:06:18
フィルターを使ってデータを抽出したときに抽出されて行についてのみ集計してみます。
41行目セルE41にオートサムで合計を表示してみます。
オートサムで合計が表示されました。
次にこの表にフィルタを設定します。表の中をクリックしておきます。ホームタブの編集の並べ替えとフィルターからフィルターをクリックします。
店名で本店だけを表示します。店名の右のドロップダウンのボタンをクリックします。本店だけにチェックを入れます。
なぜか空白セルなるものがありますが、合計のセルのある最下行（41行目)もフィルターが有効になっています。本店だけ表示されましたが、本店だけの売り上げの合計が欲しいです。このまま最下行である42行目に本店だけの合計を表示する式を入力してもいいのですが、フィルターを解除したときに合計の行が2行になってしまいます。
いったん、フィルターを終了して合計のセルを削除します。再度フィルターを設定し、本店だけ表示します。
今回は空白のセルの選択肢がありません。この状態で最下行に合計を表示させてみます。セルE41を選択し、オートサムのボタンをクリックします。
数式バーを見ていただくと=sum()ではなく=SUBTOTAL(9,E2:E40)当数式が入力されています。とりあえずエンターキーを叩いて確定しておきます。
フィルターで店名を変更しても表示されている売り上げの合計だけが計算されているのが分かります。
もちろんフィルターを解除してすべてのデータを表示した状態でもきちんとすべてを合計してくれます。
フィルタをかけた状態でオートサムのボタンをクリックすると自動でSUBTOTAL関数の合計が選択されますので手入力する必要はありません。
ちなみにあまり使わないかもしれませんが、SUBTOTAL関数の第1引数を変更すると表示されている行数を表示してくれたりもします。
以下は少し複雑なことになっていますので飛ばしていただいても構わないと思います。
まず第1引数の一覧
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
第1引数を1とすればフィルタにかけられた数値の平均になります。9がsumなので合計ですね。
SUBTOTAL関数ふるまいですが、通常（第1引数の書き方による）ではフィルターに対して非表示の行は計算対象になりませんが、手動で行を非表示にした場合（フィルターによらない非表示行）は計算対象になります。
手動で非表示にした行も計算対象から外したい場合は第1引数に100を足した数値を設定します。
101 AVERAGE
102 COUNT
103 COUNTA
104 MAX
105 MIN
106 PRODUCT
107 STDEV
108 STDEVP
109 SUM
110 VAR
111 VARP
SUBTOTAL関数の第1引数を109にするとフィルタで除外されている行と手動で非表示にされている行は計算対象にはなりません。
詳しくはMicrosoftのSUBTOTAL 関数を参照してみてください。
