｢StiLL｣Excel情報466 関数--SUBTOTAL関数を使って連番を表示させる

■一覧表のデータに連番をつけたとき、オートフィルタをかけると番号が飛び飛びになってしまいますが、オートフィルタをかけても番号が飛ばないようにする方法をご紹介します。

☆対応方法：SUBTOTAL関数を使用して連番をつけます。
１つ目の引数に「３」、２つ目の引数にはデータの個数を数えるセルの範囲を指定します。

※ご参考までに：オートフィルタだけではなく、非表示の行にも対応したいときは、１つ目の引数を「１０３」に変えます。
また、２つ目の引数で指定したセルが空欄の場合は正しく連番をつけることができませんので、ご注意ください。

バックナンバー356で SUBTOTAL関数をご紹介しました。

今回は SUBTOTAL関数を使って、一覧表に行番号をつける方法をご紹介します。

例えば、次のような一覧表でオートフィルタをかけた場合、一番左の「No」が連番ではなくなってしまいます。

オートフィルタをかけたときにも連番にしたい場合は、SUBTOTAL関数を使います。

■表示している文字の数を数える

今回は、C列「氏名」の欄に文字が入っているセルの数を数えて、B列「No」に連番を入れる例でご紹介します。

・「集計方法」

　どのように集計するかを１～１１の数字で指定します。

　今回は C列「氏名」のデータ（文字列）の個数を数えるため「3」と指定します。

　　※オートフィルタだけではなく、「行の非表示」についても対応したい場合は「103」と指定します。

　　※文字列ではなく数字のセルを数えたい場合は「2」または「102」と指定します。

・「参照1」

　データの個数を数えるセルの範囲を指定します。

　データの先頭行（今回の例では C3 セル）から、SUBTOTAL関数を入れた行のC列を指定します。

　データの先頭行のセルは絶対参照（$C$3）にしておきます。

　　※先頭行（３行目）の場合は「$C$3:C3」ですが、次の行（４行目）は「$C$3:C4」になります。

B3セルに入力した数式を「No」の最後のセルまでコピーします。

■ご注意下さい

①SUBTOTAL関数の「参照1」で指定したセル（今回の場合は C列「氏名」）に空欄があると

データの個数を数えられなくなってしまいますので、必ず文字が入っている列を指定して下さい。

② 「No」（SUBSTITUTE関数が入っている列）も含めてオートフィルタを設定した場合、

オートフィルタの抽出条件に関係なく、データの最終行が必ず表示されてしまいますので

オートフィルタを設定するときは「No」（SUBSTITUTE関数が入っている列）を外して設定してください。

※フィルタ設定手順

これで、「支店名」を「東京本店」でフィルタをかけても「No」に連番が表示されるようになりました。