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

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

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

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

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

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

この記事の内容

  • 連番を自動入力するSUBTOTAL関数の使い方。
  • オートフィルターの場合はSUBTOTALの注意点。

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

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

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

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

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

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

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

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

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

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

  1. オートフィルターで抽出された行
  2. 途中に非表示行が含まれる行
  3. 途中の行を削除

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

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

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

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

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

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

SUBTOTAL関数の最初の引数の『103』が『COUNTAの計算』をする指定で、二番目の引数には計算対象の範囲$C$3~C4(C3~C4)を指定しています。
結局、ここで入力した式は、『見出しのセル$C$3から、式を入力したセル(C4)までのセルの個数』を求めています。
つまり、セルの個数は見出しのセルと式を入力したセルの2つなので、『2 – 1』の『1』がこの式の答え(連番)となるわけです。
見出しのセルまで含めてセルをカウントしている理由は、SUBTOTAL関数を使ったオートフィルターの問題(最終行がフィルターの対象から外れる)を回避するためで、ようは苦肉の策です^^

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ポイント

ポイント

  • 行の状態に関わらず、見えている行の連番を自動入力したい場合はSUBTOTAL関数を使う。

エクセルでは表を作ることが多いので、連番を入力することは当たり前の作業となります。

行の削除や非表示などの操作を一切しないのであればrow関数などで事足りますが、どうしても行を編集しないといけない場合が出てきます。

なので、正しい連番を入力するためにも、SUBTOTAL関数を利用して余計な手間を増やさないようにしましょう。

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

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

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

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

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

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

タイトルとURLをコピーしました