【エクセル】削除行や非表示行があっても連番を自動入力

エクセルで表を作る時には、だいたい『№』や『項番』などの列を作って、連番を振ることが多いですよね。

でも、手で1、２、３と順番に入力するのは入力ミスのもとにもなるし、行が多い表などは手間がかかって大変です。

そんな時には、現在の行数を取得できるrow関数を使って、番号を直接入力しない工夫をします。（それにフィルコピーも使える）

そんなrow関数ですが、途中の行が削除されても自動連番を入力してくれるので使いやすいんですが、『オートフィルターで抽出した行』や『途中に非表示行が含まれる』場合には正しい連番となりません。

今回は、行の状態に関わらず正しい連番を自動入力してくれるSUBTOTAL関数を紹介します。

この関数を使えば、行の状態が変わっても連番を振り直す必要がないので、ぜひ参考にすしてください。

SUBTOTAL関数を使うとオートフィルターでも連番が入力できる

次のエクセルの№の列にはSUBTOTAL関数が入力されていて、オートフィルターで性別が『女』の行だけを抽出しています。

抽出された行に、正しい連番が自動で入力されていることがわかるかと思います。

行削除や非表示行があっても連番を自動入力する方法

SUBTOTALの使い方の前に、どんな状態の行の時に連番が入力できるのかまとめてみました。

SUBTOTAL関数が対応する行の状態

SUBTOTAL関数は、行が次の状態になっても正しく連番を入力してくれます。

要は、『今見えている行に連番を入力する』ことができます。

SUBTOTAL関数を使って連番を自動入力させる

では、SUBTOTAL関数を使って連番を自動入力させてみます。

連番を入力する最初のセル（B4）に、次のSUBTOTAL関数の式を入力します。

=SUBTOTAL(103, $C$3:C4) - 1

SUBTOTAL関数の最初の引数の『103』が『COUNTAの計算』をする指定で、二番目の引数には計算対象の範囲＄C$3～C4（C3～C4）を指定しています。

結局、ここで入力した式は、『見出しのセル＄C$3から、式を入力したセル（C4）までのセルの個数』を求めています。

つまり、セルの個数は見出しのセルと式を入力したセルの2つなので、『2 – 1』の『1』がこの式の答え（連番）となるわけです。

見出しのセルまで含めてセルをカウントしている理由は、SUBTOTAL関数を使ったオートフィルターの問題（最終行がフィルターの対象から外れる）を回避するためで、ようは苦肉の策です＾＾

次に、入力したSUBTOTAL関数の式を最後の行までフィルコピー（式のコピー）します。

上のエクセルにも書いているように、フィルコピーの方法は2つあるので、どちらの方法でもかまいせん。

これで、最後の行まで連番が入力されるはずです。

ここまでできたら、後はオートフィルターしようが非表示行を作ろうが、正しく連番が入力されます。

SUBTOTAL関数はオートフィルターで抽出した行に連番を入力する

『性別』で『男』をフィルターした場合です。

ちゃんと連番が入力されてますね。

SUBTOTAL関数は途中の行を非表示にしても連番を入力する

こちらは、途中のいくつかの行を非表示にしていますが、問題なく連番が入力されます。

SUBTOTAL関数は途中の行を削除しても連番を入力する

同様に、途中の行を削除しても正しい連番が入力されます。

SUBTOTAL関数は『小計』などの合計にも使える

SUBTOTALは日本語に訳すと『小計』という意味で、『小計』だけを合計するということが簡単にできます。

表に小計を使っている場合には重宝する関数だと思うので、次の記事で使い方を参考にしてください。

また、SUBTOTAL関数は『今見えている行だけを合計する』ことも簡単にできます。

フィルターだけではなく、行を非表示にしたときも非表示の行を計算の対象から除外してくれるので非常に便利ですよ＾＾

使い方は、次の記事で詳しく解説しているので、ぜひ参考にしてください。