Home » Excel(エクセル)の使い方 » データベース・検索 » フィルタ実行時に連番を表示させたい−SUBTOTAL関数

フィルタ実行時に連番を表示させたい−SUBTOTAL関数

対象：Excel97,Excel2000,Excel2002,Excel2003

Excelは元来表計算ソフトですが、データベース機能も充実しています。特にデータの絞込みをするためのフィルタ機能は、データベースソフトであるAccessのクエリやフィルタ機能より便利に感じることも少なくありません。

そのフィルタ機能を使いこなしている方から、抽出されたデータに連番が振られるような列を先頭に作っておくにはどうすればいいのか、という質問をいただくことがあります。

［スポンサードリンク］

以下の設定を行うとA列に連番が表示され、フィルタを実行すると表示されているデータの連番が振られるようになります。

▼操作手順：フィルタ実行時に連番を表示させる列を作成する
（1行目がフィールド名、2行目からデータが表示されているリストのA列に連番を表示させる例、B列には必ずデータが入力されているものとします）
A2セルに「=SUBTOTAL(3,B$2:B2)」と入力後［Enter］キーを押す
　↓
データの入力されている行までA2セルをコピー

SUBTOTAL関数はリストの集計値を取得する関数で、第1引数でどんな値を取得したいのか集計方法を指定します。こここではデータの個数を数えたいので「3」を指定しています。合計・平均・最大値・最小値なども求めることができます。
ここで指定した「3」以外に、どんな値を指定したときにどんな集計値を得ることができるのかは、SUBTOTAL関数のヘルプに記載されていますから、是非一度ご覧いただくことをおすすめします。

第2引数で集計する範囲を指定するのですが、ここでは「B$2:B2」という複合参照を利用した指定をしたので、A列で下に向かってコピーしたときに「B$2:B3」「B$2:B4」と変化します。つまりB列の先頭行からのデータの個数を取得することになり、結果として連続した数値が表示されるわけです。

そしてSUBTOTAL関数は、フィルタリングをしたときに表示されていない行は無視して集計を行ってくれるため、フィルタをかけると表示されている行のデータ個数を数え直し、連番を振りなおしてくれるわけです。

但しちょっと問題があって、この連番を表示する列を追加すると、リストの一番下のレコードが抽出条件に合致していなくても表示されてしまうという不具合があります。解決策をご存知の方は是非教えてください。

■不具合の回避方法■

このネタをご覧いただいた方から、不具合の回避方法を教えていただきました。ありがとうござます。

「=SUBTOTAL(3,B$2:B2)」と入力してコピーする方法をここではご紹介しているわけですが、この数式をちょっとだけ修正すればいいようです。

数式を、

「=IF(B2="","",SUBTOTAL(3,B$2:B2))」

としてデータのある行にコピーをします。すると不具合が発生しなくなります。

なぜこれで回避できるのか考えてみたのですが、残念ながら理由はわかりません。

理由・原因がわからないのは気持ち悪いのですが、そもそも不具合がなぜ発生しているのかもよくわからない（バグと言っていいものじゃないかと考えてます）ので、IF関数をかませれば回避はできるということで納得し、ご紹介させていただきます。

ご連絡いただきどうもありがとうございました。

（2005.05.19追記）

関連語句

オートフィルタで連番をふる, 可視セルだけに連番を振る, 連続数字, 連続番号

［スポンサードリンク］