機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > 機能と数式

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





上図のようなリストがあったとします。オートフィルタを使うと、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関数はフィルタ機能と一緒に使うようにと追加された関数なのですが……(^^; いろいろ調べてみましたが、どうやらフィルタの適用範囲内でSUBTOTAL関数を使っていると、データ範囲が正しく認識されないようです。上図をよくご覧ください。絞り込みの結果9行目が表示されていますが、9行目の行番号が青くなっていません。これは、9行目は絞り込みの結果によって表示されているのではないということを表しています。実は上図のケースでは、▼ボタンをクリックしても最終行のデータがリストに表示されません。フィルタの適用範囲として、最終行が含まれていないのです。ほかにも、いくつかのケースで試してみたら別の不具合も確認できました。

同一の事例はありませんでしたが、以下に似たようなケースが紹介されています。

[XL2002]集計とオートフィルタを併用すると集計結果が正しくない
[XL2000]リストオートフィル機能が設定されていると数式が変更される

このバグについては、しかるべきルートで本社に直接問い合わせていますので、何かわかったらご報告します。


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



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

集計方法
(非表示行も計算する)
集計方法
(非表示行は計算しない)
同等な関数
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP






このエントリーをはてなブックマークに追加