ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.015

データの最終行が範囲から外れ、フィルターの結果に常に表示される

2003/2007/2010/2013

フィルターを使用してデータの抽出を行うときに、データの最終行が抽出条件にあてはまらないのに表示されてしまう、という不具合があります。

原因はSUBTOTAL関数の使用かと思われます。
抽出データにSUBTOTAL関数を使用している場合は、一度、SUBTOTAL関数のセルをコピーして値の貼り付けを行い関数を取り外すか、SUBTOTAL関数を使用しているセルを削除してください。それで改善する場合は、SUBTOTAL関数が原因ですね。

では、再現してみましょう。
例えば下のようなデータがあります。A列の連番は入力されたものです。

データ

この状態では、問題なく抽出されます。

フィルターで抽出

では、A列はSUBTOTAL関数を使って、抽出表示されているデータの連番を求める式を入れてみます。

  =SUBTOTAL(3,$B$2:B2)

式の意味は、範囲におけるデータの個数を調べています。SUBTOTAL関数を使用すると、フィルターで抽出され表示されたセルが対象となります。

SUBTOTAL関数

さて同じ抽出をすると、抽出外のデータである最終行が入ってきてしまいます。

フィルターで抽出

よく見ると、最終行のデータは、行番号は黒文字であるため抽出によって表示されたのではなく、範囲からはずれていることがわかります。

そもそもSUBTOTAL関数は、オートフィルタで抽出データの集計を行うことができるようになっており便利に利用できるのですが、それが仇となって不具合を起こすようです。

関連ヘルプ

データを並べ替えるコマンドについて
1列に入力されているCSVまたはテキスト ファイルのデータをカンマごとにセルに分割したい
セルにに入力されている数値データを1桁ずつにセルに分けるには
複数のキーで並べ替えを行う
アウトライン機能がついてる集計行を折りたたんだ状態でコピーする
昇順、降順でない独自の順番でデータを並べ替えるには
データのふりがな情報を取り戻す
小計(集計)を行ったがアウトラインが表示されない
特定の値を持つ行(レコード)をまとめて削除する
ピボットテーブルの元データ範囲を更新する
ピボットテーブルで行ラベルの複数フィールドを横に並べるには(レイアウトの変更)
並べ替えデータの基本について
重複データを削除する
重複のない一意(一通り)のデータを取得する
セルの色で抽出を行いたい(色フィルター)
twitter hatena line pocket