オートフィルタで絞り込んだ件数を表示する


上図のようなリストがあったとします。オートフィルタを使うと、A列が「東京」のデータだけを簡単に絞り込むことが可能です。そのとき、絞り込んだ結果が何件あるかを調べるにはSUBTOTAL関数を使います。

SUBTOTAL関数は、2番目の引数で指定した範囲に対して、1番目の引数で指定した集計方法による計算結果を返す関数です。SUBTOTAL(3,A:A)で指定した「3」は、COUNTA関数と同じようにデータをカウントします。集計の範囲に指定した「A:A」はA列全体を意味します。ただし、これではセルA1の「住所」が常にカウントされてしまいますので、セルB11の式では「-1」しています。SUBTOTAL関数で使用できる集計方法は、このページの最後にある「SUBTOTAL関数で使用できる集計方法」をご覧ください。

また、データの横に連番を表示している場合、オートフィルタで絞り込むと連番がくずれてしまいますね。

こんなときも、SUBTOTAL関数を使うと常に連番を表示することができます。A列の「番号」に「1・2・3…」と数字を直接入力するのではなく、セルA2に「=SUBTOTAL(3,$B$2:B2)」という数式を入力し、これをセルA9までコピーします。

セルA2の数式「=SUBTOTAL(3,$B$2:B2)」は、SUBTOTAL関数で抽出されるデータをカウントしていますが、集計する範囲がポイントです。セルA2では「$B$2:B2」と「セルB2からセルB2まで」を集計しています。このとき、最初のアドレスが絶対参照で指定している点に注目してください。この数式を下方向にコピーすると、最初の「$B$2」は変化せず、2番目の「B2」だけが「C2・D2・E2…」と変化します。つまり、セルA2の集計範囲は「セルB2からセルB2まで」、セルA3の集計範囲は「セルB2からセルB3まで」、セルA4の集計範囲は「セルB2からセルB4まで」となります。その結果、たとえばB列を「東京」でフィルタリングしても、A列には常に連番が表示されるわけです。

注意!

上の図、よく見ると「東京で絞り込んだ」のに、最終行の「神奈川」が表示されています。これは、このページを公開するときには気づきませんでした。こっそり修正してもいいのですが、どうやらExcelのバグっぽいので、本コンテンツはこのままにしておきます。まぁ、バグっぽいっちゃバグっぽいんですけど、理由は分かりますし。

SUBTOTAL関数で使用できる集計方法

SUBTOTAL関数の第1引数に指定する数字と集計方法は次の通りです。101~111はExcel 2003で追加された集計方法です。Excel 2002までのバージョンでは使用できませんから注意してください。ここでいう「非表示行」とは、メニュー操作などでユーザーが故意に隠した非表示行のことです。オートフィルタによって自動的に隠される非表示行は、常に計算の対象から除外されます。

集計方法(非表示行も計算する) 集計方法(非表示行は計算しない) 同等な関数
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