今回は、Excelの連番を自動採番して、行の追加や削除があっても表を管理しやすくする方法を紹介します。
Excelの連番は手入力より自動採番に向いている
Excelで名簿、作業一覧、問い合わせ管理、備品リストなどを作るとき、左端に「No.」や「管理番号」を置くことはよくあります。少ない行なら手入力でも対応できますが、行を途中に追加したり、不要な行を削除したりすると、番号を付け直す手間が出ます。
そこで使いやすいのがExcelの連番を自動採番する設定です。数式やテーブル機能を使っておくと、表の編集に合わせて番号を整えやすくなります。連番の目的は、見た目を整えることだけではありません。別の人に作業を依頼するときに「12番の内容を確認してください」と伝えたり、印刷した資料の行を指し示したりするときにも役立ちます。
連番を自動採番するときは、まず「何のための番号か」を決めておくと迷いにくくなります。上から順に並ぶだけでよい番号なのか、削除しても欠番を残したい管理番号なのかで、向いている方法が変わります。
- 行の順番を示すだけなら、数式で連番を作る
- 表に行を追加する予定が多いなら、テーブル機能を使う
- 番号を変えたくない管理番号なら、手動入力や専用列を検討する
ROW関数で上から順に連番を作る
Excelで連番を自動採番する基本の方法は、ROW関数を使うことです。ROW関数はセルの行番号を返す関数です。たとえば2行目からデータが始まる場合、A2セルに次のような式を入れると「1」から始まる連番を作れます。
=ROW()-1
この式は、A2セルではROW関数の結果が2になるため、そこから1を引いて1にしています。A3セルへコピーすると「2」、A4セルでは「3」というように続きます。見出し行の位置に合わせて引く数字を変えれば、別の開始行にも対応できます。
たとえば見出しが4行目、データが5行目から始まる表なら、最初のデータ行に次の式を入れます。
=ROW()-4
ROW関数を使う方法は、行の追加や削除に合わせて番号が変わる点が特徴です。途中の行を削除すると、下の行の番号が詰まります。これは、受付順や固定IDには向きませんが、一覧表の表示番号としては扱いやすい方法です。
見出し行が変わりやすいときの考え方
資料作成の途中でタイトル行を足したり、説明行を追加したりすると、ROW関数から引く数字を直す必要が出ます。表の上に行を増やす可能性がある場合は、次のように基準セルを使った式にすると調整しやすくなります。
=ROW()-ROW($A$1)
この例では、A1を基準にしています。実際には見出し行や基準にしたいセルへ置き換えて使います。式の中に固定の数字を入れるより、どこを基準にしているかが分かりやすくなります。
テーブル機能で行追加に強い連番にする
行を増やすことが多い表では、Excelのテーブル機能を使うと管理しやすくなります。表の中を選択して、挿入タブから「テーブル」を選ぶと、入力範囲をテーブルとして扱えます。見出し行がある場合は、見出しを含めて指定します。
テーブルにしておくと、末尾の行にデータを入力したときに表の範囲が自動で広がります。数式列も引き継がれやすいため、連番の式を何度もコピーする手間を減らせます。
テーブルで連番を作る場合も、まずはROW関数を使えます。データ行が2行目から始まるなら、No.列の最初のデータ行に次の式を入れます。
=ROW()-1
ただし、テーブルの上に説明文やタイトルを追加する予定があるなら、基準セルを使う式にしておくと後から直しやすくなります。表の見出し行を基準にしておくと、上に行を追加したときの影響を抑えられます。
テーブル名を付けておく
テーブルを作ったら、テーブルデザインタブで名前を付けておくと後の作業がしやすくなります。たとえば「受付一覧」「備品一覧」のように内容が分かる名前にすると、数式や参照を確認するときに迷いにくくなります。
テーブル名は連番そのものには必須ではありませんが、複数の表を扱うブックでは役立ちます。どの表のNo.列なのかを判断しやすくなり、別シートから参照するときにも整理しやすくなります。
フィルターを使う表ではSUBTOTAL関数を検討する
Excelの連番を自動採番するときに気を付けたいのが、フィルターを使った場合です。ROW関数の連番は、非表示になった行も含めた行番号をもとに計算されます。そのため、フィルターで一部の行だけを表示すると、番号が飛んで見えることがあります。
フィルター後の表示行だけに連番を振り直したい場合は、SUBTOTAL関数を使う方法があります。たとえば、A列にNo.、B列にデータがあり、2行目から始まる表なら、A2に次のような式を入れます。
=SUBTOTAL(103,$B$2:B2)
この式では、B列の表示されているセルを数えながら番号を作ります。A3へコピーすると範囲が$B$2:B3に広がり、表示中の行だけを数える動きになります。フィルターで行を絞り込んだとき、見えているデータに合わせて連番を付けたい場合に向いています。
ただし、SUBTOTAL関数を使うときは、参照する列に空白が多いと番号が期待どおりにならないことがあります。商品名、氏名、案件名など、データが必ず入る列を参照先にすると安定します。
非表示行とフィルター行の違い
Excelでは、手動で行を非表示にする場合と、フィルターで絞り込む場合で見え方が似ています。連番の式を作るときは、どちらの非表示を想定するかを決めておくと後で混乱しにくくなります。
フィルター後に表示行だけを数えたいならSUBTOTAL関数を使い、単純に元の表の順番を残したいならROW関数を使う、というように使い分けるとよいです。
削除しても番号を変えたくない場合の注意点
自動採番は便利ですが、すべての番号に向いているわけではありません。たとえば申請番号、受付番号、管理台帳のIDのように、後から番号が変わると困るものがあります。このような番号にROW関数を使うと、行を削除しただけで下の番号が変わってしまいます。
番号を固定したい場合は、連番の列を数式ではなく値として管理する方法を検討します。最初にオートフィルで番号を作り、必要な範囲へ入力した後、番号列を値のまま残します。行を削除しても番号は変わりません。
- 表示順の番号は数式で自動採番する
- 管理番号や受付番号は値として固定する
- 並べ替えに使う番号と、見た目の番号を分ける
この区別をしておくと、表の使い方に合った番号管理ができます。特に複数人で使うブックでは、「No.」が表示順なのか固定番号なのかを見出しで分かるようにしておくと、入力ミスを減らしやすくなります。
並べ替えを使う表では番号列の役割を分ける
Excelの表では、日付順、担当者順、状態順などで並べ替えることがあります。連番を自動採番している列があると、並べ替えた後に番号がどう見えるかを確認しておく必要があります。
ROW関数で作った連番は、並べ替え後も上から順に番号が並びます。これは、現在の表示順に合わせてNo.を振り直したい場合には便利です。一方で、元の登録順を残したい場合には向きません。
元の登録順を残したいときは、次のように列を分けると扱いやすくなります。
- 表示No.：ROW関数などで上から順に自動採番する
- 登録No.：入力時に決めた番号を値として固定する
- 並べ替え用列：日付や優先度など、並べ替えの基準を入れる
このように役割を分けると、一覧として見やすい番号と、履歴として残す番号を混同しにくくなります。表が育ってから列を分けるより、作成時に決めておく方が運用しやすくなります。
連番を見やすく保つための工夫
連番は小さな列ですが、表全体の使いやすさに影響します。数字が詰まりすぎていたり、見出しとデータの位置がずれていたりすると、確認に時間がかかります。連番列は、次の点を整えておくと見やすくなります。
- No.列の幅を必要な分だけにする
- 数字を中央揃えにする
- 見出しを「No.」「表示No.」「管理No.」のように目的に合わせる
- 番号列に不要な装飾を入れすぎない
- テーブルの見出し行を固定して、スクロールしても項目名を確認できるようにする
番号を3桁でそろえたい場合は、表示形式を使う方法があります。セルの値は1、2、3のままにして、表示だけを001、002、003のように見せられます。番号列を選択し、セルの書式設定からユーザー定義を選び、種類に次のように入力します。
000
この設定は見た目だけを変えるため、数式で参照するときの値は数字のまま扱えます。ファイル名やコードとして使うために文字列の001が必要な場合は別の処理が必要ですが、表の表示をそろえる目的なら表示形式で十分なことが多いです。
途中から番号を始めたい場合
リストの途中から「101」や「5001」のような番号を振りたい場合は、ROW関数に開始番号を足します。たとえば2行目から始まり、最初を101にしたい場合は次のようにします。
=ROW()+99
A2セルではROW関数の結果が2なので、99を足して101になります。見出し行の位置によって調整が必要です。開始番号が変わる可能性があるなら、別セルに開始番号を入れて、そのセルを参照する形にすると修正しやすくなります。
たとえばE1セルに開始番号を入れておき、A2セルに次のような式を入れます。
=$E$1+ROW()-2
この形なら、開始番号を変えたいときにE1セルだけを直せます。複数の連番表を作るときにも、開始番号の管理がしやすくなります。
入力ミスを避けるためのチェック
連番列を作った後は、式が途中で途切れていないか確認します。途中のセルだけ値に置き換わっていたり、式の参照範囲がずれていたりすると、行を追加したときに番号が乱れることがあります。
確認するときは、No.列を選択して数式バーを見るだけでなく、数式タブの「数式の表示」を使うと確認しやすくなります。式が入っているセルと値だけのセルが混ざっていないかを見ておくと、後の修正が少なくなります。
また、数式列を保護したい場合は、シート保護を使う方法もあります。入力する列だけロックを外し、No.列は編集できないようにしておくと、共有して使う表でも式を壊しにくくなります。保護をかける前に、行追加やフィルターが問題なく使えるか確認しておくと安心です。
まとめ
Excelの連番を自動採番するなら、まず番号の役割を決めることが大切です。表示順を示す番号ならROW関数、フィルター後の表示行だけを数えたいならSUBTOTAL関数、行追加が多い表ならテーブル機能と組み合わせると扱いやすくなります。
一方で、管理番号や受付番号のように後から変わると困る番号は、数式で自動採番するより値として固定する方が向いています。表示No.と管理No.を分けておくと、並べ替えや削除があっても表の意味を保ちやすくなります。
連番列は目立つ機能ではありませんが、表を探す、伝える、確認する作業を支える列です。Excelで一覧表を作るときは、行の追加、削除、フィルター、並べ替えまで考えて、自分の表に合う自動採番の方法を選びましょう。