Excelで名簿や一覧表を作成し、先頭に連番を振って管理している方は多いと思います。
しかし、フィルターをかけたり、並べ替えを行った途端に、連番がバラバラに崩れてしまった経験はありませんか。
Excelで「連番が崩れる」「フィルター後に番号がズレる」といった悩みを解決したい方に向けた方法です。
「表示されている行だけに、1・2・3…と連番を振り直したい」
「並べ替えても連番が維持される方法が知りたい」
そんなときに役立つのが、SUBTOTAL関数を使った自動連番です。
特に、業務でExcelを使っている方や、データ管理・名簿管理を行っている方にとっては必須のテクニックです。
この方法を使えば、
・フィルターで絞り込んでも
・行を非表示にしても
・並べ替えを行っても
表示されている行だけに、崩れない連番を自動で付けることができます。
Excelで連番が崩れる人が多い理由
実はこの「連番が崩れる問題」は、Excelの基本操作や関数の理解が曖昧なまま使っていることが原因で起こるケースが非常に多いです。
・フィルターと関数の関係が分からない
・SUBTOTALやOFFSETなどの用途が曖昧
・実務で応用できる知識が不足している
このあたりを一度体系的に学ぶだけで、今回のようなトラブルはほぼ起きなくなります。
こうした処理を毎回手作業で対応していると、作業時間が増えるだけでなくミスの原因にもなります。関数を正しく使うことで、作業効率と正確性を同時に改善できます。
一度設定してしまえば、その後は手作業で連番を直す必要がなくなります。
表示されている行だけに自動で連番を付ける方法
ここでは SUBTOTAL 関数を使って、連番を作成します。
使用する数式は次のとおりです。
=SUBTOTAL(3,$C$3:C3)
この数式は「表示されているセルの数をカウントする」仕組みになっており、その結果がそのまま連番として表示されます。
数式の意味を簡単に説明
SUBTOTAL 関数の
「3」 は COUNTA（空白以外のセルを数える） を意味します。
つまり、
・フィルターで非表示になった行
・手動で非表示にした行
これらはカウントされず、
表示されているセルの個数だけを数える仕組みです。
セル範囲指定が重要
数式内の$C$3:C3
が、この連番のポイントです。
・$C$3 は固定（開始位置）
・C3 は下方向にコピーすると自動で変化
この指定により、
表示されているセルの数がそのまま連番になります。
自動連番を設定する手順
下の表を例として、A列に上記関数を設定します
A3セルに =SUBTOTAL(3,$C$3:C3) を入力。
連番を付けたい以下のセルにコピーします。
$C$3:C3の範囲指定は、$C$3を起点として表示された「セルの個数」をカウントして連番とします。
上記の表で、H列にフィルタをかけます。
例として、H列に「０以上」のフィルタ設定をかけます。
フィルタがかかった状態でA列が連番に表示されています。
H列の並べ替えを行います。
「降順」にしました。
H列の並び替えを行なってもA列の連番は崩れていません。
フィルターをかけても連番は崩れない
表にフィルターを設定し、条件で絞り込んでみてください。
非表示になった行はカウントされないため、
表示されている行だけが 1、2、3… と連番になります。
並べ替えをしても連番は維持される
同じ表を並べ替えても、SUBTOTAL 関数による連番は崩れません。
データの並び順が変わっても、
表示されている行の順に自動で番号が振り直されます。
関数でつまずく人はここを見直すと一気に楽になる
Excelは「なんとなく使う」と限界が来ます。
特に今回のように、
・条件付きで動作する関数
・表示状態に依存する処理
を扱う場面では、基礎理解の差がそのまま作業効率に直結します。
実務レベルでExcelを使うなら、一度しっかり基礎を整理しておくと、その後の作業が圧倒的に楽になります。こうした関数の仕組みは、独学よりも体系的に理解した方が圧倒的に効率的です。
よくある注意点
・COUNT関数（=COUNT）ではうまくいきません。
・COUNTAを使うため、基準列（例：C列）には必ず値が入っている必要があります。
・空白行があると、連番が飛ぶ原因になります。
・SUBTOTALの「3」はCOUNTAのため、数値以外でもカウントされます。
・完全に空白のセルがあると、意図しない連番になることがあります。
・フィルターではなく「削除」した場合は再計算が必要です。
今回紹介したSUBTOTAL関数は非常に便利ですが、Excelには他にも業務効率を大きく改善できる機能が多数あります。
「なんとなく使っている」状態から一歩抜けるだけで、作業時間は大きく変わります。
まとめ
Excelで、フィルター・並べ替え・行の非表示を行っても崩れない自動連番を作成する方法を紹介しました。
一覧表や管理表を扱う機会が多い方にとって、覚えておくと作業効率が大きく上がる基本テクニックです。
ぜひ一度実際のデータで試してみてください。
設定しておくだけで、日々の作業時間とミスの両方を減らすことができます。
Excelの操作に慣れてくると、こうした関数を使った自動化だけでなく、さらに効率的な作業方法も身につけることができます。
コメント