【Excel】連番が崩れない！フィルター前後も使える関数とテクニック

こんにちは、さわやかです。

Excelで連番に関してこんな悩みを耳にします。

行の追加・削除のたびに連番を振り直すのが面倒くさい！

そもそもフィルターをかけても連番が崩れない方法はないの？

フィルターをかけた後のデータに連番を振るにはどうしたらいい？

連番よりもデータの内容に集中したいですよね。

しかし疎かにすると変な表になってしまう。

地味に手間がかかるんですよね。

今回は、フィルター前後の連番に関する便利技を紹介します。

この記事でわかること

Excelで連番を作成する簡単な方法3選
フィルターをかけても崩れない連番を作るにはSUBTOTAL関数
フィルター後に連番を振るにはAlt + ;とフィル機能

仕事でExcelを使いまくりMOSエキスパートも取得した私が、連番に関して時短になるHOW TOをまとめました。

さわやか

Excelを制する者は早く帰れる！

フィルターに関するところだけ読みたい方はこちらからどうぞ！

Excelで連番を簡単作成！初心者でもすぐできる方法3選
フィルターをかけても崩れない連番作成｜SUBTOTAL関数の使い方
1. テーブル形式の表でフィルター後も崩れない連番を作成する方法
2. 通常の表でフィルター後も連番を維持する方法
フィルター後に連番を振る方法｜Alt + ;とフィルの使い方
1. 非表示行を除外して可視セルを選択する｜Alt + ;の活用
2. 可視セルだけに連続データを入力する｜フィルのコツ
まとめ：状況に合わせて効率的に連番を振ろう

Excelで連番を簡単作成！初心者でもすぐできる方法3選

まずExcelで連番を入力するには、3つの方法があります。

オートフィルを使う
ショートカットキーを使う
ROW関数を使う

一般的な順番で見ていきましょう。

オートフィルを使った連番作成｜最も手軽な方法

最も基本的なオートフィルを使う方法です。

オートフィルとは規則性のある数値や文字列を自動入力する機能です。

連番だけでなく、同じ数ずつ増減する値や、曜日・月の入力などにも使えます。

①「最初の値」「2番目の値」まで入力して、2つのセルを選択

②右下にカーソルを合わせて「＋」の形になったら下にドラッグする

「1」「2」ときたら次は「3」でしょ！とExcelが判断してくれます。

横の列にデータがある場合、「＋」をダブルクリックするだけでOK！

但しデータに空白があったら、その上の行で止まります。

この方法は、連番だけでなく様々な規則性のあるデータに使えるため、応用すると便利です。

さらに効率を高めるなら、ショートカットキーを使ってみましょう。

ショートカットキーで素早く連番作成｜作業効率アップ！

「Ctrl」キーを使うと、「2番目の値」入力を省略できます。

飛び飛びの連続データには使えませんが、1つずつ増える連番ならば有効な方法です。

①「最初の値」を入力

②入力したセルの右下にカーソルを合わせて「＋」の形になったら、
「Ctrl」を押しながら下にドラッグする

この方法の場合、横の列にデータがあるからと「＋」をダブルクリックしたら
連番ではなく数値のコピーになります。

1つずつ増える連番を入力する場合に、1セルの入力で済むので楽です。

行の追加や削除がない表ならこの方法で十分ですが、実際は行増減する場面も多いと思います。

そんなときには数式を使って対応するとさらに時短になります。

ROW関数で連番を自動更新｜行の追加や削除でも自動調整！

知っておくと非常に便利なのが、ROW関数を使う方法です。

数式を入れておくと、行の追加や削除をしても連番が途切れることなく自動で変更されるので簡単手間いらずになります。

①一番上の行に「=ROW()-○」を入力

例：2行目に見出しがあって、3行目から1としたい場合

②表の最下行までコピー

このとき、表をテーブルにしている場合は下までコピーしなくても自動で適用されます。

>>ショートカットで簡単作成！テーブル機能の使いどころとメリット

行追加・削除しても連番が崩れない！

数式の解説

ROW()は行番号を表示させる関数。
「-○」（マイナス数値）とすることで上から該当行までの数字を引いて、1から始まるようにする。

この方法を使うと、行の追加や削除をするたびに連番を振り直す手間が省けるので、かなり効率化できます。

フィルターをかけても崩れない連番作成｜SUBTOTAL関数の使い方

フィルターでデータの並べ替えや抽出を行う前に、SUBTOTAL関数を使った数式を入れておくと連番を維持できます。

テーブル形式の表の場合
通常の表の場合

テーブルは、データ管理を効率化できる機能です。

デザインを一瞬で統一し、フィルターや連番作成もしやすくなります。

詳しくは以下の記事で解説しています。

>>ショートカットで簡単作成！テーブル機能の使いどころとメリット

それではSUBTOTAL関数の使い方を、表の形式ごとに解説します。

テーブル形式の表でフィルター後も崩れない連番を作成する方法

フィルターをかけても連番が崩れないようにするには、SUBTOTAL関数を使用します。

SUBTOTAL関数はデータの集計を行う関数で、フィルター後に見えるデータのみを計算する特長があります。

以下の手順で設定してみましょう。

①連番を振る列の最初のセルに以下の数式を入力する

=SUBTOTAL(103, $C$3:[@ID])

数式の解説

SUBTOTAL関数：データの集計を行う関数
集計方法（103）：手動で非表示にした行を除外して、データの個数をカウント
参照範囲（$C$3:[@ID]）：
- $C$3: 始点となるセル。絶対参照にすることでセルが固定される
- [@ID]: テーブル形式の列参照を示し、行ごとにデータをカウント

C3セルからテーブル内の各行までのデータの個数をカウントします。

これでフィルターをかけてもカウントが自動調整されます。

入力のコツ

=SUBTOTAL(103,C3まで入力したら「F4キー」を押して絶対参照を設定
「:」を入力し、最後にC3セルをクリックすると、[@ID]（列名）が自動的に補完
※必ずデータが入っている列を選ぶこと。空白があると誤動作の原因に。

② 数式の適用を確認する

「Enter」を押すと、テーブル内のすべての行に数式が自動適用されます。

フィルターを使用しても、連番が動的に維持できるようになりました。

補足情報｜SUBTOTAL関数の便利な使い方

SUBTOTAL関数はさまざまな集計（平均値、合計、データ数など）に使えます。

今回は連番作成に「103」を利用しましたが、他の用途にも応用できます。

▼詳しくはこちらが参考になります
SUBTOTAL関数の公式ドキュメント

通常の表でフィルター後も連番を維持する方法

テーブルではない表に自動で連番を振る場合は、以下の手順で数式を入力します。

①連番を振る列の最初のセルに以下の数式を入力する

=SUBTOTAL(103,$C$2:C3)-1

数式の解説

SUBTOTAL関数：データの集計を行う関数
集計方法（103）：手動で非表示にした行を除外して、データの個数をカウント
参照範囲（$C$2:C3）：
- $C$2：見出し行のセル。絶対参照（$）にすることでセルが固定される
- C3：連番を開始するデータ行。範囲が動的に増えるよう、相対参照を使用
-1：見出し行の分をマイナス