Excelで名簿や一覧表を作成し、先頭に連番を振って管理している方は多いと思います。
しかし、フィルターをかけたり、並べ替えを行った途端に、連番がバラバラに崩れてしまった経験はありませんか。
「表示されている行だけに、1・2・3…と連番を振り直したい」
「並べ替えても連番が維持される方法が知りたい」
そんなときに役立つのが、SUBTOTAL関数を使った自動連番です。
この方法を使えば、
・フィルターで絞り込んでも
・行を非表示にしても
・並べ替えを行っても
表示されている行だけに、崩れない連番を自動で付けることができます。
表示されている行だけに自動で連番を付ける方法
ここでは 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 関数による連番は崩れません。
データの並び順が変わっても、
表示されている行の順に自動で番号が振り直されます。
よくある注意点
・COUNT関数（=COUNT）ではうまくいきません。
・COUNTAを使うため、基準列（例：C列）には必ず値が入っている必要があります。
・空白行があると、連番が飛ぶ原因になります。
おわりに
Excelで、
フィルター・並べ替え・行の非表示を行っても崩れない
自動連番を作成する方法を紹介しました。
一覧表や管理表を扱う機会が多い方にとって、
覚えておくと作業効率が大きく上がる基本テクニックです。
ぜひ活用してみてください。
コメント