Excelで名簿やタスク管理表を作っているとき、行を削除したことで「1、2、3、5、6…」のように連番が歯抜けになってしまい、がっかりした経験はありませんか？その都度マウスでドラッグして連番を振り直すのは、手間がかかる上にミスも起こりやすくストレスですよね。そこで今回は、行を削除しても自動的に正しい番号を維持してくれる便利な「自動連番」の仕組みをご紹介します。中でも基本となる「ROW関数」を使った設定手順を詳しく解説するので、ぜひ参考にしてみてくださいね。
- 行を削除してもその場で自動的に連番が詰まり、修正の手間が一切かからないこと
- ROW関数を使うことで、シート上の現在位置（行番号）に応じた連番が簡単に作れること
- 見出しの行数に合わせて引き算の数値を調整するだけで、どの行からでも連番を開始できること
ジャンプできる目次📖
Excelの連番が壊れる問題とROW関数を使った自動連番の基本手順
Excelで表を作成する際、データの行を特定するために左端に「No.」や「連番」の列を設けるのはごく一般的なデザインですよね。しかし、多くの人が直面するのが「行を削除した際、番号が自動的に詰められない」という問題です。この問題を解決するために非常に有効なのが、Excelの「ROW（ロー）関数」を利用した自動連番ですよ。数式を一つ入力しておくだけで、行の追加や削除といった表の変更に対して、Excelが自動的かつリアルタイムに計算を行って正しい番号を維持してくれます。ここでは、従来の手動オートフィルが抱える課題と、ROW関数を使ってトラブルフリーな表を作る具体的な手順について詳しく見ていきましょう。
連番をドラッグ（オートフィル）で作る従来の方法の限界
多くの方がExcelで連番を振る際、最初に「1」「2」とセルに入力し、その2つのセルを選択した状態でセルの右下にある「■（フィルハンドル）」を下にドラッグ（オートフィル）しているのではないでしょうか。この方法は直感的で分かりやすく、数行から数十行程度の簡単な表を作るだけであれば非常に手軽で便利ですよね。
しかし、この従来の方法には重大な弱点があります。それは、オートフィルで作成された連番は「ただの数値（定数データ）」としてセルに書き込まれているという点です。そのため、表の途中にある行を削除してしまうと、削除された行に入力されていた数値も一緒に消え去ってしまいます。例えば、「1、2、3、4、5」と並んでいた表から、3番目のデータが入った行を削除すると、連番は「1、2、4、5」となってしまい、「3」が完全に欠落した状態になってしまいますよ。
この状態を修正するためには、再度セルを選択し直し、フィルハンドルをドラッグして連番を上書きし直す必要があります。数件のデータならまだしも、これが数百件、数千件の大きなデータシートだった場合、再入力の手間は計り知れません。また、頻繁にデータの更新がある表では、誰かが修正を忘れてそのまま共有してしまうリスクも高まります。
さらに、この数値による連番は、データの「並べ替え」を行ったときにもトラブルの原因になりがちです。特定の項目で昇順や降順に並べ替えると、連番の数値ごと位置が入れ替わってしまい、本来の順番がバラバラになってしまいます。これでは、何のための連番なのか分からなくなってしまいますよね。
オートフィルで作成した連番はセルに直接値が保存されているため、行の削除や並べ替えを行うと連番そのものが破損してしまいます。特に、複数人で共有する共有ファイルや、データ件数が多く頻繁に行の追加・削除が発生する作業用シートでは、手作業での再設定が繰り返されることで作業効率の低下やヒューマンエラーの原因になるため注意が必要ですよ。
もし、大量のデータを扱うことでExcelが重くなったり、計算速度が低下したりして困っている場合は、連番だけでなく数式全体の効率化や配置を見直すことも大切です。例えば、スプレッドシートやExcelの動作を最適化する方法として、こちらの記事（）も参考になるかもしれません。パフォーマンスに悩まされているなら、ぜひ併せてチェックしてみてくださいね。
ROW関数を使って行の削除に対応した自動連番を振る手順
こうしたオートフィルの限界を乗り越え、何行削除しても瞬時に正しい番号へ自動調整してくれる仕組みを作るのが「ROW関数」です。
ROW関数とは、指定したセルの「行番号」を返す関数ですよ。例えば、3行目にあるセルで =ROW() と入力すると、そのセルのある行位置である「3」という数値が返されます。これを利用すれば、セルの現在位置に基づいた動的な連番を振ることができます。
では、実際にROW関数を使って連番を振る具体的な手順を説明しますね。基本的な考え方は、「ROW関数の結果から、見出し行（ヘッダー）の行数を引く」ということです。
たとえば、1行目が見出し（ヘッダー）で、連番を2行目から開始したい場合を考えてみましょう。2行目にあるセルで単に =ROW() と入力すると、返ってくる値は「2」になります。これを「1」にするためには、全体から見出し分の「1行」を引いてあげれば良いわけですね。つまり、入力する数式は次のようになりますよ。
=ROW()-1
この数式を入力し、あとは表の最下部までオートフィルでコピーします。もし、見出しが3行分あり、4行目から連番をスタートさせたい場合は、4行目のセルに以下の数式を入力します。
=ROW()-3
このように、=ROW() - （連番より上にある行数） というルールを覚えておけば、どのようなレイアウトの表であっても柔軟に対応できますよ。
ここで、ROW関数が正しく動作しているかテストしてみましょう。連番を設定した表の途中（例えば、連番「3」が表示されている行）を右クリックし、「削除」を選択してみてください。するとどうでしょうか。削除された瞬間に、それ以下の行が上へ詰まり、連番が自動的に再計算されて「1、2、3、4…」と綺麗な並びに戻るはずです。これは、Excelのシート上でセルが移動した際、ROW関数が常にそのセルが現在位置している「新しい行番号」を取得し直して再計算を行っているからなのです。手動でドラッグし直す手間が一切なくなり、非常にスマートですよね。
ROW関数には引数を指定することもできます。例えば =ROW(A1) と入力すると、A1セルの行番号である「1」を常に返します。しかし、自動連番の用途では、引数を空にして =ROW() と記述するのが基本です。引数を省略することで、「その数式が入力されているセル自身の行番号」を動的に取得してくれるため、セルの移動やコピーに最も強くなりますよ。
外部の公式な解説や詳細な機能を確認したい場合は、Microsoftの公式サポートページである Microsoft Office ROW関数 サポートページ などで仕様を確認するのもおすすめです。引数に関する技術的な情報も網羅されていますよ。
続いて、従来のオートフィルによる連番と、ROW関数を用いた自動連番の特徴の違いを分かりやすく比較テーブルにまとめました。ご自身の作業目的や用途に合わせて使い分けてみてくださいね。
| 比較項目 | オートフィル（値による連番） | ROW関数（数式による自動連番） |
|---|---|---|
| 行削除への耐性 | なし（歯抜けになり、手動での再ドラッグが必要） | あり（行を削除するとリアルタイムに自動で再調整される） |
| 並べ替え時の挙動 | 値ごと並べ替わり、元の順番を示す番号が乱れる | 常に上から1, 2, 3...と連番を維持（元の順序の追跡には不向き） |
| 設定の手軽さ | 非常に高い（「1」「2」と入力してドラッグするだけ） | 中程度（=ROW()-1などの簡単な数式を入力する必要がある） |
| 数式への依存度 | なし（純粋な数値データなのでシートに負担をかけない） | あり（数式を使用するため、超大量行では若干の計算負荷がある） |
フィルターで非表示にした行を飛ばして連番を振るSUBTOTAL関数の手順
Excelで大きなテーブル（表）を扱っていると、特定のデータだけを絞り込んで確認するために「フィルター（オートフィルター）」機能を使うことがよくありますよね。しかし、せっかく入力した連番（1、2、3...）が、フィルターで行を非表示にした途端に「1、3、5、8...」のようにバラバラになってしまい、見た目が美しくないと感じたことはありませんか？特に、その状態のまま印刷して会議の資料にしたり、PDF化して取引先に送付したりする際には、並んでいるデータの連番がスマートに「1、2、3、4...」と詰まって表示されてほしいものです。
このような「非表示の行やフィルターで除外された行を自動的にスキップし、画面に見えている行だけで連番を綺麗に振り直したい」という悩みを一発で解決してくれるのが、Excelの強力な関数であるSUBTOTAL（サブトータル）関数なのです。この関数をマスターすれば、どんなに複雑な絞り込みを行っても、常に画面上は美しく整った連番を維持することができますよ。設定方法も非常にシンプルで、一度覚えてしまえば一生使える実務直結のテクニックですので、ぜひ一緒に学んでいきましょう！
なぜ非表示行を考慮する必要があるのか？通常関数の限界
Excelで連番を振る際、最も手軽な方法として、セルの右下をドラッグする「オートフィル」を使ったり、あるいは「=ROW()-1」といったROW（ロー）関数を使用したりしている方が多いのではないでしょうか。ROW関数は、現在入力されているセルの「行番号」をそのまま取得してくれる関数なので、表の先頭行（ヘッダー）の分をマイナスするだけで、簡単に連番を生成できて一見すると非常に便利ですよね。
しかし、このROW関数や手入力による連番には、大きな弱点があります。それは、「行が非表示になっているかどうかを一切判断できない」という点です。ROW関数が返すのは、あくまでそのセルが位置するExcelシート上の「絶対的な行番号」です。そのため、例えば2行目をフィルターや手動操作で非表示にしたとしても、3行目に入力されたROW関数は依然として自身の行番号である「3」を参照し続けます。その結果、画面上には「1」の次の行として「3」がそのまま表示されてしまい、連番が飛び飛びになってしまうのですね。
手入力された連番も同様で、行が非表示になってもセルの値そのものが自動で書き換わるわけではないため、見た目上の数字は不連続になってしまいます。これでは、データを絞り込んで特定のリサーチ結果だけを並べた資料を作りたいときなどに、非常に不便で不格好な印象を与えてしまいますよね。だからこそ、画面の表示状態を自動的に検知して、見えている行だけで数え直してくれる動的な仕組みが必要不可欠になるのです。
通常関数（ROW関数など）の限界と問題点
- 行の表示・非表示を識別できない：ROW関数はシート自体の物理的な行の位置しか見ていないため、どれだけフィルターをかけても、返ってくる数値は常に一定です。
- 印刷や報告時の見栄えが悪くなる：フィルターをかけたリストをそのまま印刷すると、連番が歯抜け状態（例：1、3、7、10...）になってしまい、データの件数を直感的に把握することが難しくなります。
- データ追加や並べ替えに対応しにくい：手動で数値を修正するアプローチでは、フィルター条件を変更するたびに連番を再入力しなければならず、作業の効率が劇的に低下してしまいます。
このように、通常の数式や手入力ではフィルターという日常的な操作に対応できません。そこで登場するのが、表示されているセルの状態を自動的に判定して数え上げるSUBTOTAL関数というわけです。
SUBTOTAL関数（関数番号3または103）を使った自動連番の設定手順
それでは、具体的にSUBTOTAL関数を使って、非表示の行を綺麗にスキップする自動連番の設定手順を詳しく解説していきますね。ここで使用する公式は「=SUBTOTAL(3, $B$2:B2)」という形です。なんだか少し難しそうに見えるかもしれませんが、それぞれの要素を丁寧に紐解いていけば、とても論理的で分かりやすい仕組みになっていることが分かりますよ。
まず、SUBTOTAL関数の基本的な構造は「=SUBTOTAL(関数番号, 参照範囲)」となっています。最初の引数である「関数番号」には、どの計算方法を使って集計を行うかを指定するための数値を入力します。今回は、セルに値が入っている個数をカウントする「COUNTA（カウントエー）」の役割を担わせるために、関数番号として「3」または「103」を指定します。
ここで気になるのが、「3」と「103」のどちらを使うべきかという違いですよね。実は、この2つの数値には以下のような明確な仕様の差があります。
| 関数番号 | オートフィルターによる非表示行 | 手動で「非表示」にした行（右クリック非表示など） |
|---|---|---|
| 3（COUNTA） | 自動でスキップします（連番にカウントしません）。 | スキップせずカウントに含めます（隠れていても数えます）。 |
| 103（COUNTA） | 自動でスキップします（連番にカウントしません）。 | 自動でスキップします（隠れている行は数えません）。 |
このように、オートフィルターによる絞り込みだけに対応させたい場合は「3」で十分ですが、行ヘッダーを右クリックして手動で「非表示」にした行もスキップして連番を詰めたい場合には「103」を指定する必要があります。実務の柔軟性を考えると、基本的には「103」を使って数式を組んでおく方が、あらゆる非表示パターンに対応できるためおすすめですよ。
次に、2番目の引数である「$B$2:B2」というセル参照の指定方法について説明しますね。この記述には、Excelの非常に重要なテクニックである「絶対参照」と「相対参照」の組み合わせが使われています。これを「拡張参照」や「半絶対参照」などと呼んだりします。
数式の最初の部分「$B$2」には、列と行の前にそれぞれ「$」マークがついていますよね。これは「このセルの位置を絶対に動かさないでね」というロックを意味する絶対参照です。一方で、コロン（:）の後ろにある「B2」には「$」マークがついていません。これは「コピーした場所に合わせて自動的に移動してね」という相対参照になります。
この仕組みによって、数式を下方向のセルにコピーしていくと、以下のように参照範囲の終点だけが自動的に広がっていくようになります。
- 1行目（セルA2）：
=SUBTOTAL(103, $B$2:B2)―― B2からB2までの範囲内にある、表示されているセルをカウント（結果：1） - 2行目（セルA3）：
=SUBTOTAL(103, $B$2:B3)―― B2からB3までの範囲内にある、表示されているセルをカウント（結果：2） - 3行目（セルA4）：
=SUBTOTAL(103, $B$2:B4)―― B2からB4までの範囲内にある、表示されているセルをカウント（結果：3）
このように、スタート地点である「$B$2」を固定したまま、お尻のセルだけを自分の行の高さに合わせて引き伸ばしていくことで、「表の先頭から自分の行までに、値が入力されている行が何個あるか」を順番に数え上げていくことができるのです。そして、SUBTOTAL関数は「非表示になっている行を無視する」という特徴を持っているため、途中の行がフィルターで隠されると、その隠れたセルは自動的にカウントから除外され、見えている行だけで綺麗な連番（1、2、3...）が再計算されて表示されるのですよ。
SUBTOTAL関数を設定する際の具体的なステップ
- 基準となる列を決める：連番を振るためにカウントする基準列（今回の例では商品名や氏名が入っているB列など）を決めます。※空欄がない列を選ぶのがポイントです。
- 先頭のデータセルに数式を入力する：連番を開始したいセルの1行目（例：A2セル）に、「
=SUBTOTAL(103, $B$2:B2)」と入力します。 - 数式を下のセルへコピーする：A2セルの右下角にある「フィルハンドル（小さな■）」をダブルクリックするか、下方向へドラッグして、必要な行まで数式をコピーします。
- フィルターをかけて動作を確認する：テーブルに適当なフィルターを適用してみて、非表示になった行を通り越して、見えているデータに対して「1、2、3...」と隙間なく連番が自動で振り直されているか確認します。
実際に試してみると分かりますが、フィルターを切り替えるたびに一瞬で番号が変わり、常に美しい連続した数字が表示されるようになります。まるで手品を見ているかのようにスムーズに動作するので、初めて設定したときは少し感動するかもしれませんね。
なお、このSUBTOTAL関数の処理の仕組みや、他の関数番号（SUMやAVERAGEなど）との組み合わせ方など、より高度な機能や公式な定義について詳しく知りたい方は、こちらのMicrosoft サポートのSUBTOTAL関数解説ページを参照してみてください。公式ならではの網羅的な情報が分かりやすく掲載されていますよ。
KYOのワンポイントメモ：シート全体の動作速度が気になるあなたへ
今回ご紹介したSUBTOTAL関数は非常に便利ですが、数万行に及ぶような非常に巨大なシートで大量に使用すると、Excelが画面更新やフィルター適用のたびにすべての行の再計算を行うため、ファイル全体の動作が少し重くなってしまうことがあります。特に低スペックのPCや、共同編集しているクラウド上のスプレッドシートなどでは、動作のモタつきがストレスになることも。
もし、Excelやスプレッドシートの動きが遅いなと感じる場合には、数式の使い方やシート構成を見直すことで劇的に軽くすることができますよ。動作をサクサクにして作業効率を上げたい方は、ぜひこちらの内部リンクからのテクニックもあわせて参考にしてみてくださいね。
このように、SUBTOTAL関数を正しく使いこなすことで、実務のドキュメントのクオリティは一気に高まります。見栄えの良さはもちろんのこと、データ数を把握する正確性も向上するため、ぜひ日々のExcel作業に取り入れてみてくださいね。
データが入っている行だけに限定して自動連番を振る応用ワザ
Excelで資料を作成しているとき、「表の途中に空欄の行を挟みたいけれど、連番はデータがある行だけにきれいに振りたいな……」と思ったことはありませんか？単純に「=上のセル+1」という数式を入力したり、オートフィルで無理やり連番を引きずったりすると、データのない空欄行にまで番号が入ってしまって見た目が格好悪くなってしまいますよね。かといって、空欄の行を飛ばして手動で番号を入力していくのは、行の追加や削除があったときにすべての番号がズレてしまうため、後からの修正がとても面倒な作業になってしまいます。
そこで今回は、データが入力されている行だけに限定して、自動で連番を割り振る応用ワザをご紹介しますね。この方法を使えば、空欄の行は自動的にスキップされ、データが入っている行だけに必要な連番が順番に表示されるようになりますよ。データを削除すれば番号が自動で詰まり、新しいデータを追加すればその場で新しい番号が割り振られるため、手動での修正は一切不要になります。表のメンテナンス性をグッと向上させるために、ぜひ覚えておきたい便利なテクニックですよ。
このスマートな仕組みを実現するためには、Excelの代表的な条件分岐関数である「IF関数」と、データの個数を数える「COUNTA（カウント・エー）関数」または「SUBTOTAL（サブトータル）関数」を組み合わせます。数式と聞くと少し難しそうに感じるかもしれませんが、仕組みさえ理解してしまえば誰でも簡単に設定できますので安心してくださいね。それでは、この設定を行う具体的なメリットから確認していきましょう。
空欄の行をスキップして必要な箇所だけ連番を振るメリット
空欄を挟んだ表において、データがある行だけに限定して連番を振る仕組みを構築することには、実務で多くのメリットがあります。単に見栄えが良くなるだけでなく、作業の効率化やミスの防止にも非常に効果的なんですよ。主なメリットを分かりやすく整理してみましたので、あなたの普段の業務にどう役立つかイメージしながら読んでみてくださいね。
まず1つ目のメリットは、リスト全体の視認性が飛躍的に向上することです。例えば、納品書や請求書、プロジェクトの進捗管理表などで、カテゴリーごとに空欄行や区切り行を挟んでレイアウトを整えることがありますよね。もしそうした空欄行や見出し行にまで連番が機械的に振られていると、データの実際の件数が分かりにくくなりますし、何より「入力漏れがあるのではないか」という無用な誤解を招きかねません。必要なデータ行だけに番号が美しく整列していることで、誰が見ても一目で整理されたプロフェッショナルなレイアウトをキープできるかなと思いますね。
2つ目のメリットは、データの追加・削除に自動で追従するダイナミックな表を作れることです。手動で連番を飛ばして入力していると、行を削除した瞬間に番号が歯抜けになってしまいます。また、新しく行を挿入したときには、その都度手動で番号を入力し直して、それ以降のすべての番号を書き換えるという不毛な作業が発生しますよね。関数を組み合わせた設定をしておけば、隣のセルにデータが入力された瞬間に連番が自動計算されます。データが消えれば連番もきれいに前詰めされるため、手動で修正する手間を省き、修正し忘れて番号が狂ってしまうというヒューマンエラーを防ぐことにも繋がりますよ。
3つ目のメリットは、フィルター機能でデータを絞り込んだときにも連番が綺麗に維持されることです（これはSUBTOTAL関数を使う場合ですが）。特定のデータだけを抽出したときに、連番が「1、3、7、10……」と飛び飛びになってしまうと、抽出されたデータが何件あるのか直感的に数えづらいですよね。表示されている行だけに自動で「1、2、3……」と連番を再計算してくれるのは、Excelならではの非常にスマートな挙動だと言えますね。
空欄スキップ連番の主なメリット
- 見た目の美しさと正確性：空欄行や区切り行に余計な番号が入らず、データの存在が一目で分かります。
- 抜群のメンテナンス性：行の追加や削除を行っても、手動で連番を修正する必要が一切ありません。
- ミス防止：番号の重複や抜け漏れといった手動入力ならではのエラーを完全に排除できます。
このように、関数を使って連番をコントロールすることは、表のクオリティを高めるためにとても大切なんです。それでは、実際にどのように設定するのか、具体的な関数の書き方と手順を詳しく見ていきましょう。
IF関数とCOUNTA関数（またはSUBTOTAL）を組み合わせる設定方法
データが入っている行だけに連番を振るための設定方法を解説します。ここでは、最も一般的で理解しやすい「COUNTA関数」を使った方法と、フィルター操作にも対応できる応用的な「SUBTOTAL関数」を使った方法の2パターンをご紹介しますね。それぞれの特徴を掴んで、あなたの目的や用途に合わせて使い分けてみてくださいね。
COUNTA関数を使った連番の自動作成
まずは、最も標準的な「IF関数」と「COUNTA関数」を組み合わせる方法です。例えば、A列に連番を振り、B列にデータ（名前や商品名など）が入力されているケースを想定しますね。A2セル（最初の連番を入力するセル）に以下の数式を入力してみましょう。
=IF(B2="","",COUNTA($B$2:B2))
数式を入力したら、あとはそのセルを一番下の行までオートフィルでコピーするだけで設定完了ですよ。この数式が何を行っているかというと、次のような2つのステップで処理をしています。
- ステップ1（IF関数の条件判定）：まず
B2=""という部分で、「B2セルが空欄（ダブルクォーテーション2つで表現します）かどうか」をチェックしています。もしB2セルが空欄であれば、連番を表示するA2セルにも空欄（""）を返します。これにより、空欄行に番号が表示されるのを防いでいるのですね。 - ステップ2（COUNTA関数でのカウント）：B2セルに何かデータが入力されている場合は、
COUNTA($B$2:B2)が実行されます。これは、「B2セルから、現在の行のB列セルまでの範囲の中で、データが入っているセルの個数を数える」という処理です。これにより、データが入っている行だけに1から順に番号が割り振られるようになりますよ。
ここで非常に重要なポイントとなるのが、範囲の指定方法である「$B$2:B2」という書き方です。最初の「$B$2」には、列と行の前にそれぞれ「$」記号がついていますよね。これは「絶対参照」と呼ばれるもので、数式を下の行にコピーしても開始位置のセルを絶対に動かさないようにロックする役割を持っているんですよ。一方で、後ろの「B2」には「$」がついていません（相対参照）。これにより、数式を下にコピーしていくと、範囲の終点が「$B$2:B3」「$B$2:B4」「$B$2:B5」というように、自分の行に合わせて自動的に広がっていきますね。このロックと拡張の組み合わせによって、常に「表の最初から現在の行までに入力されているデータの数」を正確にカウントできる仕組みになっているのですね。
絶対参照「$」を簡単に入力するコツ
数式を入力するときに、いちいちキーボードから「$」を手入力するのは面倒ですし間違いのもとになりますよね。そんなときは、数式内で「B2」と入力した直後（またはカーソルを合わせた状態）で、キーボードの「F4」キーを1回押してみてください。自動的に「$B$2」と「$」マークが付与されますよ。とても便利なショートカットキーなので、ぜひ試してみてくださいね。
SUBTOTAL関数を使った連番の自動作成（フィルター対応版）
続いて、表に「フィルター」を設定して特定のデータだけを絞り込むことが多い場合におすすめの、より高度な方法をご紹介しますね。実は、先ほどのCOUNTA関数を使った方法では、フィルターで特定の行を隠したとしても、隠された行のデータまで裏でカウントされてしまうため、表示されている連番が飛び飛びになってしまいますよね。
もし、フィルターで絞り込んだ結果に対しても、常に上から「1、2、3……」と綺麗な連番を振りたい場合は、COUNTA関数の代わりに「SUBTOTAL関数」を使用しますよ。A2セルに以下の数式を入力してみてくださいね。
=IF(B2="","",SUBTOTAL(3,$B$2:B2))
この数式の中で使われているSUBTOTAL(3, ...)の「3」という引数は、「COUNTA関数と同じようにデータの個数を数えてください」という指示を意味しているのですね。しかし、通常のCOUNTA関数と大きく異なるのは、「非表示になっている（フィルターで隠されている）行を無視して、画面に見えている行だけをカウントする」という特別な性質を持っている点なんです。
そのため、この数式を設定しておけば、表の行をどれだけフィルターで絞り込んでも、画面に表示されている有効なデータ行だけに順序よく「1、2、3……」と連番が再計算されて表示されるようになります。顧客リストや商品マスターなど、普段から頻繁にデータを検索・抽出するようなシートでは、このSUBTOTAL関数を使う方法がベストな選択肢になりますよ。
注意点：フィルター以外の手動非表示には対応しない？
SUBTOTAL関数で引数を「3」に指定した場合、フィルターによる非表示行は無視されますが、行番号を右クリックして手動で「非表示」にした行はカウントされてしまいますね。もし手動で非表示にした行もスキップして連番を詰めたい場合は、引数を「103」に変更して、=IF(B2="","",SUBTOTAL(103,$B$2:B2))と入力してみてくださいね。こうすれば、あらゆる非表示パターンに対応できるようになりますよ。
COUNTA関数とSUBTOTAL関数の違いまとめ
どちらを使うべきか迷ってしまうこともあると思いますので、2つの関数の挙動の違いを簡単な比較テーブルに整理してみましたよ。あなたの目的に合ったものを選んでみてくださいね。
| 使用する関数 | 空欄行のスキップ | フィルター時の連番再計算 | 手動非表示行のスキップ | おすすめの用途 |
|---|---|---|---|---|
| COUNTA | 可能（スキップする） | 不可（非表示行もカウントされる） | 不可（非表示行もカウントされる） | フィルターを使用しない固定的なレイアウトの表 |
| SUBTOTAL(3, ...) | 可能（スキップする） | 可能（画面上の行だけ再計算） | 不可（手動非表示はカウントされる） | フィルターを多用する顧客管理表や売上リスト |
| SUBTOTAL(103, ...) | 可能（スキップする） | 可能（画面上の行だけ再計算） | 可能（手動で隠した行もスキップする） | あらゆる非表示操作に対して連番を美しく保ちたい場合 |
数式を多用することによる動作の重さへの対策
このように便利な関数による自動連番ですが、ExcelやGoogleスプレッドシートで数万行に及ぶような非常に大きな表に対して、すべての行に複雑な数式を埋め込んでしまうと、再計算に時間がかかって動作が著しく重くなってしまうことがありますよね。「スクロールするたびにカクカクする」「文字を1文字入力するだけでロードが入る」といったストレスを感じるようになったら、シート全体の数式構成を見直すタイミングかもしれませんね。動作を少しでも軽くしたい場合は、数式の使い方を工夫したり、不要な部分を値として貼り付け直したりするなどのアプローチが必要になってくるかなと思います。
特にGoogleスプレッドシートで大量のデータを扱っていて動作の重さに悩んでいる方は、私の別記事であるも参考にしてみてくださいね。Excelでも共通して使えるメモリ節約の考え方や、計算の負荷を最小限に抑えるアイデアを詳しく解説していますので、きっと役に立つはずですよ。
いかがでしたでしょうか？IF関数とCOUNTA関数、あるいはSUBTOTAL関数を上手に使い分けることで、表の見た目がすっきり整うだけでなく、データの手動追加・削除などの日々のメンテナンス作業が格段に楽になりますね。こうした小さな工夫を積み重ねることで、業務全体のスピードアップやミス防止に繋げていきましょうね。あなたのExcel作業が少しでも快適になる手助けになれば嬉しいです！
Excelの自動連番設定・行スキップに関するよくある質問（FAQ）
Excelで自動連番を設定したり、特定の行をスキップして番号を振ったりする方法は、日々のデータ管理をとても楽にしてくれますよね。しかし、実際にこの機能を使っていると、「並べ替えをしたときに順番が崩れるのはどうして？」「非表示にした行を元に戻したらどうなるの？」といった疑問や、想定外の動きに直面することがよくあります。
ここでは、Excelの自動連番設定で行き詰まりやすいポイントや、よくある疑問についてFAQ形式で分かりやすく解決していきます。これらをマスターすれば、どのようなデータ操作を行っても連番が崩れない完璧な表を作ることができますよ。
Q1：自動連番を設定したのに、並べ替え（ソート）を実行すると連番の順番が崩れてしまいます。対策は？
Excelの表でデータを並べ替えた際、連番がバラバラになるのは非常によくあるお悩みです。
この原因は、並べ替えによって行のデータ（物理的な位置）が入れ替わるためです。直接数値を手入力していたり、上のセルを参照する「=A2+1」などの数式を使ったりしていると、ソート時にその値や参照関係も一緒に移動してしまいます。結果として、行順の変更に伴い連番も崩れてしまうのです。
対策は、他のセルを参照せず、自分自身の行番号から連番を計算する「ROW関数」を使うことです。具体的には以下の数式を設定します。
並べ替えに対応する連番数式
=ROW() - [見出し行の数]
例えば表の1行目がヘッダーで、2行目から連番「1」を始めたいなら、=ROW()-1 と入力します。この数式は「今いる行番号から見出し行数（1）を引く」ため、どのセルとも依存せず自己完結します。どれだけ並び替えても、常にその行に応じた正しい連番（上から1、2、3…）が再計算されて維持されますよ。ソートをよく行う表ではぜひ使ってみてくださいね。
Q2：非表示の行を再表示したときに、自動で連番が元通りに振り直されますか？
特定の行を非表示にしたときに、残った行だけで番号を振り直する設定（SUBTOTAL関数など）にしている場合、その行を再表示した際の挙動が気になりますよね。結論からお伝えすると、非表示の行を再表示すれば、自動的に元通りの連続した連番に更新されますよ。
これは、SUBTOTAL関数が「画面に表示されているセルのみをカウントする」という仕様を持っているためです。例えば、=SUBTOTAL(103, $B$2:B2) などの数式を使うと、非表示中の行は計算から自動除外され、行を隠した瞬間に連番がギュッと詰まります。
隠していた行を再表示すると、Excelが自動で再計算を実行します。再表示されたセルがカウント対象に復帰するため、その行にも正しい連番が入り、それ以降の行の番号も自動で押し下げられて、隙間のないきれいな連番に戻る仕組みです。手動で直す必要は一切ないので、安心して非表示・再表示を活用してくださいね。
Q3：自動連番の数式を値（テキスト）に固定して確定したい場合は？
ROW関数やSUBTOTAL関数による自動連番は便利ですが、最終データの確定時や提出時には「これ以上行操作で連番が変わってほしくない」という場合もありますよね。その際は、数式を解除して「値」に変換して固定しましょう。
手順はとてもシンプルです。
- ステップ1：連番が入っているセル範囲を選択する
固定したい連番のセル範囲をすべてドラッグ選択します。
- ステップ2：コピーを実行する
Ctrl + Cを押してコピーします（周囲が点線で囲まれます）。 - ステップ3：「値として貼り付け」を行う
選択した状態で右クリックし、貼り付けオプションから「値の貼り付け」アイコン（123が書かれたボードのマーク）を選択します（または
Ctrl + Alt + Vを押して「値」を選びOK）。
これで数式が消去され、画面表示通りの数値「1」「2」「3」という静的データへと一瞬で確定します。これ以降は連番が完全にロックされるため、行の並び替えや削除を行っても番号が変わることはありません。最終成果物を作る際は、ぜひこの値貼り付けを行ってみてくださいね。
まとめ：自動連番を使いこなしてデータメンテナンスの手間をなくし、今日も早く帰りましょう！
今回は、Excelで自動連番を振る設定や、行を削除・フィルター・非表示にした際にも番号をきれいに維持またはスキップさせるためのテクニックについて、よくある質問と解決策を交えながら解説してきました。
ROW関数やSUBTOTAL関数といった数式の知恵を取り入れるだけで、データがどれだけ増減しても、並び順がどう変わっても、常に完璧な連番をキープできるようになります。日々のデータメンテナンスにかける時間がほぼゼロになり、手作業での修正やミスの心配もなくなりますよ。こうした「小さな自動化」を積み重ねて業務のスピードを上げていくことが、無駄な残業をなくし、今日もサクッと定時で退勤してプライベートを充実させるための第一歩なのです。自動化を味方につけて、明日からのデスクワークをもっと快適に、スマートに進めていきましょうね！
※正確な情報は公式サイトをご確認ください。最終的な判断は専門家にご相談ください。
また、今回はExcelの連番について詳しくご紹介しましたが、スプレッドシートやExcelのシート全体が重くなって困っているという方は、動作を軽くするための具体的な対策をまとめた以下の記事もぜひ参考にしてみてくださいね。
👉
さらに、今回ご紹介したROW関数やSUBTOTAL関数などの詳しい仕様や応用テクニックについて知りたい方は、以下のMicrosoft公式サポートページも併せてチェックしてみてください。
Excelの「Excelで自動連番を設定する方法（行削除・フィルター対応）」の操作と組み合わせて覚えておくと便利な、「Excelでフィルターを複数列・複数条件でかける設定方法」の具体的な設定手順や、「Excelで表作成を綺麗に仕上げるデザインのコツと配色ルール」の活用テクニックを以下の関連記事にまとめました。Excelのデバッグやデータ整理の時間を大幅に削減して、今日もすっきり定時に退社しましょう！
