Windows・MS

Excel オートフィルタの抽出結果に連番を振る(最終行の表示不正を対策)

投稿日:2016-06-11 更新日:

今回は、オートフィルタの抽出結果に対して、連番を正しく振ってあげたいと思います。

関数はSUBTOTALを使用します。

使用するサンプルデータはこちら。

【A列(No.)】には「=row()-1」が設定されており、自動で行No.が振られるようになっています。

subtotal1

このデータで、「部署 = 人事部」の条件でフィルタするとこうなります。
【A列(No.)】の値が正しく表示されません。

subtotal2

フィルタの抽出結果に対して連番を振るにはSUBTOTALを使うのですが、やりがちな失敗例から先にご紹介します。

広告

(失敗例)フィルタの抽出結果にSUBTOTALで連番を振る

多くのサイトで紹介されている手順を試しましたが、SUBTOTALを使うとフィルタの抽出結果が正しくありません。

余計な1行が最終行に表示されてしまうのです。

ご参考までに紹介します。

【A2】セルに「=SUBTOTAL(3,$B$2:$B2)」を設定。

subtotal3

【A2】セルに設定したデータを【A列(No.)】の他のセルにコピーします。

subtotal4

このデータで、「部署 = 人事部」の条件でフィルタするとこうなります。

連番は正しく振られています。

ですが最終行をよく見てください。「部署 = 人事部」の条件でフィルタしたはずが、「購買部」が含まれてしまっています。

フィルタとSUBTOTALを使う際は、工夫が必要のようです。

subtotal5

次に紹介する方法では、フィルタとSUBTOTALを組合せていますが、特に問題なく成功します。

広告

(成功例)フィルタの抽出結果にSUBTOTALで連番を振る

【A2】セルに「=SUBTOTAL(3,$B$1:$B2)-1」を設定。

ポイントは「$B$1」として、タイトル行も対象にしていることです。

subtotal6

【A2】セルに設定したデータを【A列(No.)】の他のセルにコピーします。

subtotal4

このデータで、「部署 = 人事部」の条件でフィルタするとこうなります。

抽出結果、連番ともに完璧です!

subtotal7

広告

-Windows・MS

Copyright© ムコの戯言帳 , 2017 All Rights Reserved Powered by AFFINGER4.