フィルターをかけた状態で連番を振りたいのに、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 | 佐藤 美咲 | 経理 |
- ファイルを別名で保存します。 操作を間違えたとき元に戻せるよう、先にコピーを作ってください。
- A2セルに次の数式を入力します。
=SUBTOTAL(103,B$2:B2)
- A2セルを選択した状態でコピーし、A3からA5(最終行)まで貼り付けます。貼り付け後、A2〜A5に「1・2・3・4」と表示されれば正しく入力できています。
- 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から連続して振られることを確認したら、そのまま最終行まで数式をコピーして完成です。提出や印刷のために番号を固定したいときは、値として貼り付けたコピーファイルを別に作り、元の数式ファイルは保持しておいてください。
参考資料
- SUBTOTAL 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- ROW 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- Excel で範囲またはテーブルのデータをフィルター処理する | Microsoft Support · support.microsoft.com 参照日 2026-10-23
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
