「Excel」フィルターで行番号を連番にしたい【U】
|
フィルター後の表示内容に合わせて番号を更新する仕組み
「Excel」の通常の連番入力はセルの値を固定しているためフィルターを適用すると非表示行の番号が隠れてしまい欠番が生じたように見えます。
この機能を適切に活用することで画面に表示されている行だけに上から順に新しい番号を割り振り直し整ったリストを作る結果が期待できます。
具体的には以下の操作が実現可能です。
-
「SUBTOTAL」関数で集計方法に「3」を指定しデータの有無を判定する。
-
範囲指定に絶対参照と相対参照を組み合わせることでドラッグした際に自動で累積カウントを行う。
-
行を削除したり並べ替えたりしても計算式が再計算され常に正しい順序を維持する。
アドバイスとして、フィルターを解除すれば元の全データに対して正しい連番が戻るためデータの管理精度を高めるための解決に役立つ可能性があります。
SUBTOTAL関数で自動連番の計算式を作成したい
フィルターの影響を受けずに「見えている行」だけを数えるための数式を番号欄に入力します。
基本的な解決手順を整えることで手入力で番号を打ち直す手間を完全になくせるようになります。
以下の手順で操作を行います。
-
番号を表示したい最初のセルを選択し「=SUBTOTAL(3, $B$2:B2)」と入力する。
-
「B2」の部分は隣の列など必ずデータが入っているセルを指定し最初のセルだけを「$」で固定する。
-
オートフィル機能を使って下のセルまで数式をコピーし全ての行に計算式を反映させる。
-
フィルターを適用した際に表示された行の番号が「1」から順番に並んでいるかをチェックする。
注意点として、「3」という引数は「非表示の行を無視する」設定ではないためフィルター機能そのものを使って行を隠すことが望ましいでしょう。
テーブル機能やAGGREGATE関数でさらに高度に管理したい
表の範囲が頻繁に増減する場合やエラー値を無視したい場合にはより柔軟な設定を検討することで解決に近づける可能性があります。
複数の機能を使い分けることでメンテナンス性の高い名簿や在庫表を作成できる可能性があるため以下の項目をチェックします。
テーブル化して数式の自動コピーを有効にしたい
データが追加されるたびに手動で数式を伸ばす手間を省く手順を確認します。
以下の手順で確認します。
-
表全体を選択して「Ctrl + T」を押し範囲を「テーブル」に変換する。
-
先ほどの「SUBTOTAL」関数を入力し新しい行を追加した際に自動で番号が振られるかをチェックする。
-
フィルターで絞り込んだ後でも末尾の行まで正しく連番が続いているかを試す。
AGGREGATE関数でエラーを回避しながら連番を振りたい
データ内にエラー値が含まれていても計算を止めずに番号を表示させることで解決に近づける可能性があります。
以下の手順で操作を行います。
-
セルに「=AGGREGATE(3, 5, $B$2:B2)」という数式を入力する。
-
第2引数に「5」を指定することで非表示行を無視する設定が有効になっているかをチェックする。
-
フィルター操作を行いエラーがある行を飛ばしても番号が途切れずに表示されるかを試す。
-
これによりどのようなデータ状況でも常に一貫した連番が表示される表が完成したかを確認する。
アドバイスとして、数式を入力した列にフィルターをかけると計算がループして正しく表示されないことがあるため番号以外の列で抽出を行うことが解決に役立つ可能性があります。
動的な行番号管理を実現した後の効果
フィルター連動型の連番を自分のワークフローに合わせて最適化できるようになると資料の見た目が向上しデータの件数把握が劇的にスムーズになる期待が持てます。
「Excel」の参照機能や集計関数を状況に合わせて使いこなすことは手作業によるミスを減らしプロフェッショナルな帳票を作成するための大きな支えとなるでしょう。
具体的には以下の利点があります。
-
特定の条件で抽出した際もそのまま印刷用資料として使える綺麗なリストが即座に手に入る。
-
データの並べ替えを行っても番号が自動修正されるため常に何件目のデータかを直感的に把握できるようになる。
-
行の入れ替えや削除が発生しても番号が振り直し作業が不要になり事務作業の効率が大幅に向上する。
-
複雑な集計表においても可視セルのみを対象とした正確なナンバリングが維持されるようになる。
注意点として、数式が入ったセルを値として貼り付けてしまうと自動更新されなくなるため数式のまま保護しておく設定をおすすめします。
