Excel約4分で読めます

Excelでフィルター後に連番を振る|SUBTOTALの使い方

Excelでフィルター後に連番を振るを表したイメージ
記事の内容を表したイメージ(AI生成)

フィルターをかけた状態で連番を振りたいのに、ROW関数だと非表示の行も数えてしまい、番号が飛んでしまう――そういう場面での対処法です。

この記事では、SUBTOTAL関数を使って「表示されている行だけに1から順番を振る」方法を説明します。フィルターを切り替えるたびに番号が自動で振り直される仕組みで、手で打ち直す必要はありません。まず結論だけ言うと、=SUBTOTAL(103,B$2:B2) という形の数式を連番列に入れるのが基本の答えです。

なお、画面の表示はExcelの版や設定によって異なります。数式の動作が異なる場合は公式のヘルプで確認してください。また、作業の前には必ずファイルを別名でコピーしてから進めてください。


なぜROW関数では番号が飛ぶのか

連番によく使われる =ROW()-1 などの式は、行番号をシートの行位置から計算します。フィルターで行を非表示にしても、シートの行番号自体は変わりません。そのため、たとえば3行目・5行目・7行目だけが表示された状態では「2・4・6」と表示され、1・2・3にはなりません。

SUBTOTAL関数は「表示されている行だけを対象に集計する」という性質を持っています。この性質を使って「表示行の累計カウント」を出すことで、フィルター後に1から始まる連番を作れます。


SUBTOTAL関数の基本の形

SUBTOTAL関数の構文は次のとおりです。

=SUBTOTAL(集計方法, 範囲)

集計方法に 103 を指定すると「表示されているセルの数を数える(COUNTA相当)」という動作になります。3 でも同じカウントができますが、手動で非表示にした行も無視するには 103 を使います。フィルター専用であれば 3 でも機能しますが、103 にしておくほうが想定外の動作を防げます。

連番用の数式では、範囲の「開始行だけを絶対参照、終了行を相対参照」にするのがポイントです。

=SUBTOTAL(103,B$2:B2)

この例では B 列を数えています。B$2 は固定(絶対参照)、最後の B2 は行ごとにずれます。3行目では B$2:B3、4行目では B$2:B4 になり、先頭から自分の行までの表示行数が出ます。


手順:フィルター対応の連番を設定する

架空の例として、次のような申込一覧を使います(架空の例です)。

表は横にスクロールできます

行A列(番号)B列(氏名)C列(部署)
1番号氏名部署
2山田 太郎営業
3鈴木 花子総務
4田中 一郎営業
5佐藤 美咲経理
  1. ファイルを別名で保存します。 操作を間違えたとき元に戻せるよう、先にコピーを作ってください。
  2. A2セルに次の数式を入力します。
=SUBTOTAL(103,B$2:B2)
  1. A2セルを選択した状態でコピーし、A3からA5(最終行)まで貼り付けます。貼り付け後、A2〜A5に「1・2・3・4」と表示されれば正しく入力できています。
  2. C列(部署)にフィルターをかけ、「営業」だけを表示します。A列が「1・2」に変わることを確認してください。「1・3」のように飛んでいる場合は、数式の絶対参照の位置を見直します。

迷いやすい例外:数える列の選び方

B$2:B2 の部分には、フィルター対象の行で必ず値が入っている列を指定します。B列(氏名)のように空白がない列が適しています。空白が入りうる列を指定すると、値が入っていない行をカウントしない場合があり、番号がずれます。

もし数える列に空白セルが混在するときは、代わりに行番号を含む列として、A列自体は使えません。その場合は別途「管理用の補助列」として空白なしの列を用意するか、番号列とは別のどこかに空白のない列を確保してから参照します。

もう一つよく起きる問題が、「フィルター前の表示で番号がすでに飛んでいる」ケースです。これは数式を入れたあとにフィルターなしの状態でも確認し、1から連続した番号になっているかを先に検証すると防げます。


注意点:値として貼り付けるときの扱い

SUBTOTAL式の連番は、フィルターがかかっている状態で「コピー → 値として貼り付け」をすると、表示されている行の番号だけを値に変換できます。印刷用や提出用として番号を固定したいときに使える操作です。

ただし、値に変換してしまうと以降のフィルター切り替えで番号は自動更新されません。「更新が必要な元の表」と「提出用に値化したコピー」を別ファイルで管理するのが安全です。

また、個人名や連絡先などの個人情報を含む名簿を外部サービスや生成AIで処理する場合は、事前に利用規約や社内ルールを確認してください。


よくある設定ミスと確認ポイント

表は横にスクロールできます

症状原因確認すること
番号が1から始まらず2や0になる範囲の開始行がずれているB$2 の $ の位置と、実際のデータ開始行が一致しているか
フィルターしても番号が飛ぶ103 でなく 3 を使っている、または手動非表示がある集計方法を 103 に変更
全行で同じ番号になる末尾の参照が相対になっていないB$2:B$2 のように両方絶対参照になっていないか確認
空白行がカウントされない参照列に空白がある値が必ず入っている列に変更

次の行動

まず手元のファイルを別名でコピーし、A2セルに =SUBTOTAL(103,B$2:B2) を入力してみてください。フィルターをかけた状態で番号が1から連続して振られることを確認したら、そのまま最終行まで数式をコピーして完成です。提出や印刷のために番号を固定したいときは、値として貼り付けたコピーファイルを別に作り、元の数式ファイルは保持しておいてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ