【Excel】フィルター後も連番を1から振り直す方法｜SUBTOTAL関数で表示行だけ番号を付ける

Excelで名簿や一覧表を作っていると、左端に「1、2、3……」と連番を付けることがあります。

ところが、フィルターでデータを絞り込むと、表示されている行だけを見ると「1、4、7、10」のように番号が飛んでしまうことがあります。

これはExcelの不具合ではなく、手入力やオートフィルで作った連番が固定された番号として残っているためです。

フィルター後に表示されている行だけを、上から順番に「1、2、3……」と振り直したい場合は、SUBTOTAL関数を使います。

結論からいうと、次の数式を連番列の先頭セルに入れて下方向へコピーします。

=SUBTOTAL(103,$C$3:C3)

この数式を使うと、フィルターで非表示になった行を除外して、表示されている行だけに連番を付けられます。

この記事では、Excelでフィルター後も連番を1から振り直す方法と、SUBTOTAL関数で103を使う理由、うまく番号が付かないときの確認点をまとめます。

Excelでフィルター後に連番が飛ぶ理由

Excelでフィルターを使ったあとに連番が飛んで見えるのは、番号が元の行に固定されているためです。

たとえば、A列に次のような連番を入力しているとします。

この状態でフィルターを使い、2行目や4行目が非表示になると、表示されている行だけでは「1、3、5」のように見えます。

Excelとしては、元の番号をそのまま表示しているだけです。そのため、通常の連番では、フィルター後に表示行だけを自動で1から振り直すことはできません。

特に、次のような表では、連番が飛ぶと確認しづらくなります。

フィルターで絞り込んだ結果だけを印刷したいときや、表示されている件数を上から順番に確認したいときは、固定された連番ではなく、表示行だけを数える連番にしておくと便利です。

フィルター後も連番を1から振り直す数式

フィルター後に表示されている行だけへ連番を付けたい場合は、次の数式を使います。

=SUBTOTAL(103,$C$3:C3)

この例では、A列に連番を表示し、C列を基準列として数える形にしています。

C列に氏名や商品名など、各行に必ず入っているデータがあれば、表示されている行だけを数えて、A列に連番を表示できます。

基準にする列は、空白が少ない列を選びます。氏名、商品名、管理番号、日付など、各行に必ず値が入る列を使うと安定します。

SUBTOTAL関数で103を使う理由

SUBTOTAL関数は、フィルターや非表示行を考慮して集計できる関数です。

今回使う数式は、次の形です。

=SUBTOTAL(103,$C$3:C3)

この中の103は、表示されている空白以外のセルを数える指定です。

SUBTOTAL関数では、3と103のどちらもCOUNTAとして使えます。つまり、どちらも空白以外のセルを数える指定です。

ただし、手動で非表示にした行の扱いが違います。

関数番号	内容	手動で非表示にした行	フィルターで非表示になった行
3	COUNTA	カウントに含む	カウントしない
103	COUNTA	カウントしない	カウントしない

フィルターだけを使う表であれば、3でも表示行だけの連番になります。

しかし、実務ではフィルターだけでなく、右クリックから行を手動で非表示にすることもあります。その場合、3では手動で非表示にした行がカウントに含まれてしまいます。

そのため、この記事では手動非表示にも対応しやすい103を基本にしています。

数式の意味を確認する

もう一度、数式を確認します。

=SUBTOTAL(103,$C$3:C3)

この数式は、C3から現在行までの範囲にある「表示されている空白以外のセル」を数えています。

部分	意味
SUBTOTAL	フィルターや非表示を考慮して集計する関数
103	表示されている空白以外のセルを数える指定
$C$3	カウントを始める位置
C3	現在行までの範囲

$C$3 のようにドル記号を付けているのは、カウントを始める位置を固定するためです。

一方、後ろの C3 は、数式を下にコピーすると C4、C5、C6 のように変化します。

つまり、下の行へ進むほどカウントする範囲が広がり、表示されている行だけを上から順番に数えて連番になります。

Excelでフィルター後も連番を振り直す手順

ここからは、実際にSUBTOTAL関数で連番を設定する手順を紹介します。

例として、A列に連番を表示し、C列を基準列として使います。C列には氏名や商品名など、各行に必ず入っているデータがある前提です。

STEP1：連番を表示する列を用意する

まず、表の先頭に連番用の列を用意します。

たとえば、A列を「No」や「番号」として使います。

STEP2：最初の行にSUBTOTAL関数を入力する

連番を開始したい最初のセルに、次の数式を入力します。

=SUBTOTAL(103,$C$3:C3)

この例では、A3セルに数式を入力します。

C3セルを基準にしているため、C3にデータが入っていれば、A3セルには「1」と表示されます。

B列を基準にする場合は、=SUBTOTAL(103,$B$3:B3) のように変更します。表の開始行が2行目なら、=SUBTOTAL(103,$C$2:C2) のように行番号も合わせます。

STEP3：数式を下方向へコピーする

次に、A3セルの数式を下の行へコピーします。

フィルハンドルをドラッグしてもよいですし、表の最終行までコピーしても構いません。

コピーすると、数式は次のように変化します。

セル	数式
A3	=SUBTOTAL(103,$C$3:C3)
A4	=SUBTOTAL(103,$C$3:C4)
A5	=SUBTOTAL(103,$C$3:C5)

開始位置の $C$3 は固定されたまま、後ろの範囲だけが下方向へ広がっていきます。

STEP4：表にフィルターを設定する

表の見出し行を選択し、Excelの「データ」タブから「フィルター」を選びます。

フィルターを設定すると、見出し行に▼のボタンが表示されます。

STEP5：条件で絞り込む

任意の列で条件を指定して絞り込みます。

たとえば、数値が入っている列で「0以上」などの条件を指定すると、一部の行だけが表示されます。

フィルターで一部の行が非表示になると、SUBTOTAL関数が表示されている行だけを数え直します。

このように、非表示になった行はカウントされず、表示されている行だけが1、2、3……と並びます。

並べ替えをしても連番は表示順で振り直される

SUBTOTAL関数で作った連番は、並べ替えを行ったあとも表示順に合わせて番号が振り直されます。

たとえば、売上や点数などの列を降順に並べ替えた場合でも、連番列は表示されている上から順番に1、2、3……と表示されます。

手入力の連番では、並べ替え後に番号がデータと一緒に移動してしまいます。

一方、SUBTOTAL関数を使った連番は、表示されている行数を数えて番号を出しているため、並び順が変わっても現在の表示順に合わせて番号が変わります。

並べ替えを行うときは、表全体を対象にしてください。連番列だけ、または並べ替えたい列だけを選択して操作すると、行ごとのデータの対応関係が崩れる原因になります。

基準列は空白が少ない列を選ぶ

SUBTOTAL関数で連番を作るときは、どの列を基準にして数えるかが重要です。

今回の例では、C列を基準にしています。

=SUBTOTAL(103,$C$3:C3)

この数式は、C列の空白以外のセルを数えます。

そのため、C列に空白があると、その行はカウントされず、連番が思った通りに表示されないことがあります。

基準列には、次のような列を選ぶと安定します。

反対に、備考欄やメモ欄のように空白が多い列は、連番の基準列には向いていません。

うまく連番にならないときの確認点

SUBTOTAL関数を入れても連番がうまく表示されない場合は、数式そのものよりも、基準列や参照範囲に原因があることが多いです。

基準列に空白がある

基準列に空白セルがあると、その行はカウントされません。

たとえば、C列を基準にしている場合、C列に空白がないか確認してください。

空白が多い場合は、氏名、商品名、管理番号など、必ず値が入る列を基準に変更します。

数式の開始位置がずれている

数式の開始位置が表の先頭行と合っていないと、番号がずれることがあります。

データが3行目から始まるなら、次のように開始位置も3行目に合わせます。

=SUBTOTAL(103,$C$3:C3)

データが2行目から始まるなら、次のように変更します。

=SUBTOTAL(103,$C$2:C2)

表の開始行に合わせて、数式の行番号を調整してください。

ドル記号の位置が違う

開始位置を固定するために、最初のセル参照にはドル記号を付けます。

正しい形は次のとおりです。

=SUBTOTAL(103,$C$3:C3)

$C$3 は固定し、後ろの C3 はコピー先に合わせて変化させます。

両方を固定してしまうと、下にコピーしても範囲が広がらず、同じ番号が表示される原因になります。

数式ではなく値として貼り付けている

SUBTOTAL関数で作った連番は、数式として入力されている必要があります。

途中で「値として貼り付け」を行うと、数式ではなく固定された数字になります。

固定された数字になると、フィルターや並べ替えに合わせて番号が変わらなくなります。

連番が自動で変わらない場合は、セルをクリックして、数式バーにSUBTOTAL関数が入っているか確認してください。

通常の連番とSUBTOTAL連番の違い

通常の連番とSUBTOTAL関数による連番は、見た目は似ていますが、動き方が違います。

方法	特徴	向いている場面
手入力の連番	番号が固定される	並べ替えやフィルターを使わない表
オートフィルの連番	簡単に作れるが番号は固定される	単純な一覧表
SUBTOTAL関数の連番	表示されている行だけを数えて番号を振る	フィルターや並べ替えを使う表

表を作ったあとにフィルターや並べ替えを使う予定があるなら、最初からSUBTOTAL関数で連番を作っておくと便利です。

反対に、並べ替えもフィルターも使わない単純な表であれば、オートフィルで作った連番でも問題ありません。

よくある質問

フィルター後だけならSUBTOTALの3でもよいですか？

フィルターで非表示になった行だけを除外したい場合は、3でも連番になります。

ただし、手動で非表示にした行も除外したい場合は、103を使います。実務では手動非表示が混ざることもあるため、この記事では103を基本にしています。

基準列はC列でないといけませんか？

C列である必要はありません。

氏名、商品名、管理番号など、各行に必ず値が入っている列を基準にしてください。

B列を基準にする場合は、次のように変更します。

=SUBTOTAL(103,$B$3:B3)

空白行があるとどうなりますか？

基準列に空白があると、その行はカウントされません。

そのため、連番が飛んだように見えることがあります。空白の少ない列を基準にするか、基準列の空白を埋めてから使ってください。

並べ替えをすると数式は壊れませんか？

通常の範囲で正しく数式をコピーしていれば、並べ替え後も表示順に合わせて番号が振り直されます。

ただし、表の一部だけを選択して並べ替えると、データの対応関係が崩れることがあります。並べ替えるときは、表全体を対象にして操作してください。

連番を固定したい場合はどうすればよいですか？

SUBTOTAL関数の連番は、表示状態に合わせて変わります。

連番を固定したい場合は、数式が入っている範囲をコピーし、「値として貼り付け」を行います。ただし、値として貼り付けると、フィルターや並べ替えに合わせて自動で番号は変わらなくなります。

関連記事

Excelの連番や表作成を効率化したい場合は、次の記事もあわせて確認してみてください。

まとめ

Excelでフィルター後に連番が飛ぶときは、手入力やオートフィルの番号ではなく、SUBTOTAL関数を使うと表示行だけに番号を振り直せます。

表示されている行だけに連番を付けたい場合は、次の数式を使います。

=SUBTOTAL(103,$C$3:C3)

この数式では、C列の表示されている空白以外のセルを数えて、連番として表示します。

ポイントは、関数番号に103を使うことです。3でもフィルターで非表示になった行は除外できますが、103を使うと手動で非表示にした行も除外できます。

名簿、商品一覧、作業管理表など、フィルターや並べ替えをよく使う表では、SUBTOTAL関数による連番を設定しておくと便利です。

一度設定しておけば、表示状態に合わせて番号が自動で振り直されるため、手作業で連番を修正する手間を減らせます。