Excelで名簿、注文一覧、見積書、作業管理表などを作成するとき、番号を一つずつ入力する作業は意外に時間がかかります。
連番を自動入力する方法を知っておくと、行の追加や削除があっても番号を整えやすくなり、入力ミスの防止にも役立ちます。
オートフィル、数式、テーブル機能、フィルター後の連番など、用途に合わせて使い分けることが大切です。
単純に番号を増やすだけならオートフィル、データの増減に追従させるなら数式、非表示行を除外するならSUBTOTAL関数が便利です。
ここでは、1行目に見出しがあり、2行目からデータが始まる表を例に、Excelで連番を自動入力する方法を詳しく解説します。
Excelで連番を自動入力する基本操作
| No. | 氏名 | 部署 |
|---|---|---|
| 1 | 田中 | 営業 |
| 2 | 佐藤 | 総務 |
| 3 | 鈴木 | 開発 |
それではまず、連続した番号を素早く作る基本操作について解説していきます。
最初の二つの数字を使うオートフィル
最もわかりやすい方法は、A2セルに1、A3セルに2と入力してから、二つのセルをまとめて選択する方法です。
連続する二つの数値を選択すると、Excelは増加幅を判断して連番として扱います。
選択範囲の右下に表示される小さな四角形はフィルハンドルです。
フィルハンドルを下方向へドラッグすると、A4には3、A5には4というように連番が入力されます。
1と2の代わりに10と20を指定すれば、10ずつ増える番号にもできます。
年月や曜日のような連続データにも応用できるため、表の作成で覚えておきたい基本操作です。
フィルハンドルをダブルクリックする入力
隣の列に氏名や商品名などのデータが連続している場合は、フィルハンドルのダブルクリックが便利です。
A2とA3に1、2を入力して選択し、右下のフィルハンドルをダブルクリックします。
すると、隣接するデータ列の最終行までを目安にして、Excelが自動的に連番をコピーします。
数百行のリストでもドラッグ操作を続ける必要がないため、長い一覧表では特に効率的な方法です。
ただし、隣の列に途中の空白行があると、そこで入力範囲が止まることがあります。
連続データ機能による開始値と増分の指定
開始番号や増分を細かく設定したい場合は、ホームタブの編集グループにあるフィルから連続データを使います。
まず連番を入れたい範囲を選択し、ホーム、フィル、連続データの順に操作します。
表示された画面では、範囲を列、種類を加算、増分値を任意の数値に設定します。
開始番号を100、増分値を5に設定すれば、100、105、110という並びになります。
枝番ではなく管理番号として一定間隔の値を振りたい場面で使いやすい機能です。
【操作のポイント】1だけを入力してドラッグするとコピーになる場合があります。連番にしたいときは、最初の二つの数値を入力してから選択しましょう。
ROW関数による行番号連動の連番入力
| No. | 商品名 | 数量 |
|---|---|---|
| 1 | ノート | 5 |
| 2 | ペン | 10 |
続いては、行の追加や削除にも対応しやすいROW関数を使った連番を確認していきます。
ROW関数で二行目を1にする数式
1行目が見出しで、2行目を1番にしたい場合は、A2セルにROW()-1と入力します。
数式は ROW()-1 です。
ROW関数は数式が入力されたセルの行番号を返します。
A2セルではROW関数の結果が2になるため、そこから1を引くと連番の最初の値である1になります。
A3セルでは3-1で2、A4セルでは4-1で3となる仕組みです。
数式を下へコピーすれば、セルの位置に応じた番号が自動表示されます。
開始番号を変えるROW関数の考え方
連番を100から開始したいときは、A2セルにROW()+98と入力します。
A2の行番号は2なので、2に98を足して100になります。
開始番号を任意に設定する基本形は、ROW()+開始番号-開始行番号です。
2行目から500で開始する場合の数式は ROW()+498 です。
開始行が5行目で開始番号を1にする場合は ROW()-4 となります。
数式の意味を理解すると、表の開始位置が変わっても落ち着いて調整できます。
空白行にも番号が付く場合の注意点
ROW関数はセルの行番号を基準にするため、商品名や氏名が空欄でも番号を表示します。
入力済みの行だけに番号を付けたいなら、B列を判定列としてA2にIF(B2=””,””,ROW()-1)と入力します。
この数式ではB2が空白なら空欄を表示し、データがあれば番号を表示します。
未入力行に不要な番号を表示したくない帳票では、IF関数を組み合わせる方法が実用的です。
【操作のポイント】ROW関数は行番号をそのまま利用します。表の見出し行を増減させたときは、引く数値も見直しましょう。
COUNTIF関数とSUBTOTAL関数による条件付き連番
| No. | 担当者 | 状態 |
|---|---|---|
| 1 | 高橋 | 対応中 |
| 2 | 伊藤 | 完了 |
続いては、空白行やフィルターによる非表示行を考慮した連番の作り方を確認していきます。
COUNTIF関数で入力済みデータだけを数える方法
B列に氏名が入力される表では、A2セルにCOUNTIF($B$2:B2,”<>”)と入力できます。
数式は COUNTIF($B$2:B2,”<>”) です。
$B$2は先頭セルを固定し、B2はコピー先に合わせて動く参照です。
“<>”は空白ではないセルという条件を表します。
氏名がある行だけを数えるため、途中に空白行があっても次のデータには続き番号が表示されます。
データがあるレコードだけを対象にした連番を作りたいときに適しています。
SUBTOTAL関数でフィルター後の番号を振る方法
フィルターで絞り込んだ後、表示されている行だけに1から番号を振り直したい場合があります。
その場合は、A2セルにSUBTOTAL(3,$B$2:B2)と入力します。
SUBTOTAL(3,$B$2:B2)関数番号3はCOUNTAを表し、範囲内の空白ではないセルを数えます。
SUBTOTAL関数はフィルターで非表示になった行を集計から除外するため、絞り込み結果だけに連続した番号を表示できる点が特徴です。
数式をオートフィルで下までコピーする手順
COUNTIF関数やSUBTOTAL関数は、最初のセルに数式を入力した後で下方向へコピーします。
数式セルを選択し、右下のフィルハンドルをダブルクリックすると、隣接するデータの最終行まで数式が入ります。
絶対参照にした先頭セルの$B$2は固定され、末尾のB2だけがB3、B4と変化します。
ドル記号の有無で参照先の動き方が変わるため、数式バーで確認しながらコピーしましょう。
【操作のポイント】フィルターを使う表では、通常のROW関数ではなくSUBTOTAL関数を選ぶと、表示行と番号のずれを防げます。
テーブル機能を使った連番管理
| No. | 案件名 | 担当 |
|---|---|---|
| 1 | 提案書作成 | 山本 |
| 2 | 見積確認 | 中村 |
続いては、行を追加する機会が多い一覧表で便利なテーブル機能を確認していきます。
データ範囲をテーブルへ変換する操作
見出しを含むデータ範囲を選択し、挿入タブのテーブルをクリックします。
テーブルの作成画面で範囲を確認し、先頭行をテーブルの見出しとして使用するにチェックを入れます。
テーブル化すると、見出しにフィルターが付き、行や列を追加したときに書式と数式が自動的に引き継がれます。
更新が頻繁な管理表ほどテーブル化の効果が大きいでしょう。
テーブル内で数式を自動拡張する仕組み
テーブルのNo.列の先頭データセルにROW()-ROW(テーブル名[#見出し])といった数式を入力すると、列全体へ数式が適用されます。
テーブル名は既定ではTable1などですが、テーブルデザインタブから分かりやすい名前へ変更できます。
通常のセル範囲では、新しい行に数式をコピーする必要があります。
テーブルでは、末尾行の直下にデータを入力すると、数式列も自動で拡張されることがあります。
ただし、行の削除によって番号を詰めるか、発行済み番号を維持するかは運用ルールによって異なります。
番号を固定値として残す場合の判断
注文番号や受付番号のように、一度付けた番号を変更してはいけない場合があります。
このような表では、数式による連番ではなく、確定した番号を値として貼り付けて管理する方法が安全です。
数式セルをコピーし、形式を選択して貼り付けから値を選ぶと、計算式ではなく表示結果だけを残せます。
行の並べ替えや削除で番号が変わると困るデータでは、連番の自動化と番号の固定を区別しましょう。
【操作のポイント】テーブルは入力の自動拡張に強い機能です。ただし、伝票番号のような一意の番号には値の確定も検討してください。
連番入力で起こりやすいトラブル対策
| No. | 確認項目 | 状態 |
|---|---|---|
| 1 | 参照セル | 確認済み |
| 2 | フィルター | 確認済み |
続いては、連番が期待どおりに増えないときの代表的な原因と対処法を確認していきます。
ドラッグしても同じ数字がコピーされる場合
1だけが入力されたセルをフィルハンドルでドラッグすると、1、1、1と同じ値がコピーされることがあります。
これはExcelが数値のコピーとして処理しているためです。
1と2を入力して二つのセルを選択するか、ドラッグ後に表示されるオートフィルオプションから連続データを選びます。
連番として認識させるための最初の二つの値が重要です。
行の挿入後に番号がずれる場合
手入力の番号では、途中に行を挿入したあとに後続番号を修正しなければなりません。
この作業を繰り返すと、重複番号や欠番が発生しやすくなります。
ROW関数、COUNTIF関数、テーブルの数式列を利用すれば、表の構造に合わせて番号を計算できます。
ただし、番号を固定する必要がある帳票では、安易に数式へ置き換えないことも大切です。
フィルターで番号が飛んで見える場合
ROW関数で作成した連番をフィルターで絞ると、表示された番号が1、4、8のように飛んで見えることがあります。
これは行番号を基準にしているためで、計算上の誤りではありません。
画面上で表示行だけを1から順に並べたい場合は、SUBTOTAL関数へ変更します。
番号の目的が識別なのか、表示順なのかを最初に決めると、適切な方法を選びやすくなります。
【操作のポイント】連番の乱れは、コピー、数式、フィルターのどれが原因かを切り分けると解決しやすくなります。
まとめ Excelで自動入力する連番の方法
| 目的 | おすすめの方法 |
|---|---|
| すぐに連番を作る | オートフィル |
| 行の増減に対応する | ROW関数またはCOUNTIF関数 |
| 絞り込み後に振り直す | SUBTOTAL関数 |
Excelで連番を自動入力する方法は、作業の目的によって使い分けることがポイントです。
短い表ならオートフィル、更新する表なら数式、フィルターを使う表ならSUBTOTAL関数を選ぶと、番号管理が楽になります。
ROW関数は行番号に連動し、COUNTIF関数は入力済みデータだけを数え、SUBTOTAL関数は表示行だけを対象にできます。
また、テーブル機能を使うと、新しい行の追加時にも書式や数式を引き継ぎやすくなります。
連番は単純な数字に見えますが、表の用途に合った方法を選ぶことで、集計や検索、進捗管理の精度も高まります。
まずは普段使う一覧表でオートフィルとROW関数を試し、必要に応じてCOUNTIF関数やSUBTOTAL関数へ進んでいきましょう。
