Excelで名簿や一覧表を作成するとき、先頭列に「1、2、3……」と連番を付けて管理することがあります。
ところが、フィルターでデータを絞り込んだり、並べ替えを行ったりすると、通常の連番では番号が飛んだり、順番が崩れたりすることがあります。
たとえば、A列に手入力で「1、2、3……」と入力している場合、フィルターで一部の行を非表示にしても、元の番号はそのまま残ります。そのため、表示されている行だけを見ると「1、4、7、10」のように番号が飛んで見えることがあります。
このようなときに便利なのが、SUBTOTAL関数を使った自動連番です。
SUBTOTAL関数を使うと、フィルターで表示されている行だけを対象にして、1から順番に番号を振り直すことができます。さらに、関数番号に103を使うことで、手動で非表示にした行も除外した連番にできます。
この記事では、Excelでフィルターや並べ替えをしても崩れにくい連番を作る方法を紹介します。
Excelでフィルター後に連番が崩れる理由
Excelで連番が崩れる原因は、番号そのものが「固定された値」になっているためです。
たとえば、A列に次のような番号を手入力しているとします。
- 1
- 2
- 3
- 4
- 5
この状態でフィルターを使い、2行目や4行目が非表示になると、表示されている行だけでは「1、3、5」のように見えます。
Excelとしては元の行番号や入力値をそのまま表示しているだけなので、これは異常ではありません。ただ、一覧表や名簿として見ると、番号が飛んでいるように見えてしまいます。
特に次のような表では、連番が崩れると見づらくなります。
- 顧客名簿
- 社員一覧
- 商品リスト
- 受付一覧
- 作業管理表
- 問い合わせ管理表
このような表では、フィルター後に表示されている行だけを「1、2、3……」と振り直せるようにしておくと、確認や印刷がしやすくなります。
表示されている行だけに連番を付ける数式
フィルター後も表示されている行だけに連番を付けたい場合は、次の数式を使います。
=SUBTOTAL(103,$C$3:C3)この数式を連番列の最初のセルに入力し、下方向へコピーします。
ここでは例として、A列に連番を付け、C列を基準列として使う形にしています。
ポイントは、C列にデータが入っている行だけを数えることです。C列が氏名や商品名など、必ず入力される列であれば、表示されている行だけを数えて連番にできます。
基準にする列は、空白が少ない列を選びます。氏名、商品名、管理番号など、各行に必ず値が入る列を使うと安定します。
SUBTOTAL関数で103を使う理由
SUBTOTAL関数は、集計方法を番号で指定する関数です。
今回使う数式は次の形です。
=SUBTOTAL(103,$C$3:C3)この中の103は、表示されている空白以外のセルを数える指定です。
SUBTOTAL関数では、3と103のどちらもCOUNTA、つまり空白以外のセルを数える指定です。ただし、扱いに違いがあります。
| 関数番号 | 内容 | 手動で非表示にした行 | フィルターで非表示になった行 |
|---|---|---|---|
| 3 | COUNTA | カウントに含む | カウントしない |
| 103 | COUNTA | カウントしない | カウントしない |
フィルターだけを使う場合は、3でも表示行だけを数えられます。
しかし、実務ではフィルターだけでなく、行を右クリックして手動で非表示にすることもあります。その場合、3では手動で非表示にした行がカウントに含まれてしまいます。
そのため、この記事では手動非表示にも対応しやすい103を使います。
数式の意味を整理する
もう一度、数式を確認します。
=SUBTOTAL(103,$C$3:C3)この数式は、C3から現在行までの範囲にある「表示されている空白以外のセル」を数えています。
それぞれの意味は次のとおりです。
| 部分 | 意味 |
|---|---|
| SUBTOTAL | フィルターや非表示を考慮して集計する関数 |
| 103 | 表示されている空白以外のセルを数える指定 |
| $C$3 | カウントを始める位置 |
| C3 | 現在行までの範囲 |
$C$3 のようにドル記号を付けているのは、開始位置を固定するためです。
一方、後ろの C3 は、数式を下にコピーすると C4、C5、C6 のように変化します。
その結果、下の行へ進むほどカウント範囲が広がり、表示されている行だけを1つずつ数えて連番になります。
自動連番を設定する手順
ここからは、実際にSUBTOTAL関数で自動連番を設定する手順を紹介します。
例として、A列に連番を表示し、C列を基準列として使います。C列には氏名や商品名など、各行に必ず入っているデータがある前提です。
STEP1：連番を表示する列を用意する
まず、表の先頭に連番用の列を用意します。
たとえば、A列を「No」や「番号」として使います。
STEP2：最初の行にSUBTOTAL関数を入力する
連番を開始したい最初のセルに、次の数式を入力します。
=SUBTOTAL(103,$C$3:C3)この例では、A3セルに数式を入力します。
C3セルを基準にしているため、C列にデータが入っていれば、A3セルには「1」と表示されます。
C列ではなくB列やD列を基準にしたい場合は、数式内のCを対象列に変更します。たとえばB列を基準にするなら、=SUBTOTAL(103,$B$3:B3) とします。
STEP3：数式を下方向へコピーする
次に、A3セルの数式を下の行へコピーします。
フィルハンドルをドラッグしてもよいですし、表の最終行までコピーしても構いません。
コピーすると、数式は次のように変化します。
| セル | 数式 |
|---|---|
| A3 | =SUBTOTAL(103,$C$3:C3) |
| A4 | =SUBTOTAL(103,$C$3:C4) |
| A5 | =SUBTOTAL(103,$C$3:C5) |
開始位置の $C$3 は固定されたまま、後ろの範囲だけが下方向へ広がっていきます。
STEP4：フィルターを設定する
表にフィルターを設定します。
表の見出し行を選択し、Excelの「データ」タブから「フィルター」を選びます。
フィルターを設定すると、見出し行に▼のボタンが表示されます。
STEP5：条件で絞り込む
次に、任意の列で条件を指定して絞り込みます。
たとえば、H列に数値が入っている場合、「0以上」などの条件でフィルターをかけます。
フィルターで一部の行が非表示になると、SUBTOTAL関数が表示されている行だけを数え直します。
このように、非表示になった行はカウントされず、表示されている行だけが1、2、3……と並びます。
並べ替えをしても連番は振り直される
SUBTOTAL関数で作った連番は、並べ替えを行ったあとも表示順に合わせて番号が振り直されます。
たとえば、H列を降順に並べ替えた場合でも、A列の連番は表示されている上から順に1、2、3……と表示されます。
手入力の連番では、並べ替え後に番号がデータと一緒に移動してしまいます。
一方、SUBTOTAL関数を使った連番は、表示されている行数を数えて番号を出しているため、並び順が変わっても現在の表示順に合わせて番号が変わります。
一覧表を印刷する場合や、フィルター後の結果だけを確認したい場合に便利です。
並べ替えを行うときは、表全体を対象にしてください。連番列だけ、または並べ替えたい列だけを選択して操作すると、行ごとのデータの対応関係が崩れる原因になります。
基準列は空白が少ない列を選ぶ
SUBTOTAL関数で自動連番を作るときは、どの列を基準にして数えるかが重要です。
今回の例では、C列を基準にしています。
=SUBTOTAL(103,$C$3:C3)この場合、C列の空白以外のセルを数えて連番にします。
そのため、C列に空白があると、連番が思った通りに表示されないことがあります。
基準列には、次のような列を選ぶと安定します。
- 氏名が入っている列
- 商品名が入っている列
- 管理番号が入っている列
- 日付が必ず入っている列
- 取引先名が入っている列
反対に、備考欄やメモ欄のように空白が多い列は基準列に向いていません。
3ではなく103を使う場面
SUBTOTAL関数で連番を作る例では、次のように3を使う方法を見かけることがあります。
=SUBTOTAL(3,$C$3:C3)フィルターで非表示になった行だけを除外したい場合は、この数式でも連番になります。
ただし、手動で行を非表示にした場合は、3では非表示行もカウントに含まれます。
実務では、フィルターだけでなく、右クリックから行を非表示にする場面もあります。そのため、表示されている行だけをより確実に数えたい場合は、103を使う方が安全です。
フィルター後の連番だけなら3でも動きます。ただし、手動で非表示にした行も除外したい場合は103を使います。この記事では実務での使いやすさを考えて103を基本にしています。
うまく連番にならないときの確認点
SUBTOTAL関数を入れても連番がうまく表示されない場合は、次の点を確認してください。
基準列に空白がある
基準列に空白セルがあると、その行がカウントされず、連番が飛んで見えることがあります。
たとえば、C列を基準にしている場合、C列に空白がないか確認してください。
空白が多い場合は、氏名や管理番号など、必ず値が入る列を基準に変更します。
数式の開始位置がずれている
数式の開始位置が表の先頭行と合っていないと、番号がずれることがあります。
たとえば、データが3行目から始まるなら、次のように開始位置も3行目に合わせます。
=SUBTOTAL(103,$C$3:C3)データが2行目から始まるなら、次のように変更します。
=SUBTOTAL(103,$C$2:C2)表の開始行に合わせて、数式の行番号を調整してください。
ドル記号の位置が違う
開始位置を固定するために、最初のセル参照にはドル記号を付けます。
正しい例は次の形です。
=SUBTOTAL(103,$C$3:C3)$C$3 は固定し、後ろの C3 はコピー先に合わせて変化させます。
両方を固定してしまうと、下にコピーしても範囲が広がらず、同じ番号が表示される原因になります。
数式ではなく値として貼り付けている
SUBTOTAL関数で作った連番は、数式として入力されている必要があります。
途中で「値として貼り付け」を行うと、数式ではなく固定された数字になります。
固定された数字になると、フィルターや並べ替えに合わせて番号が変わらなくなります。
連番が自動で変わらない場合は、セルをクリックして数式バーにSUBTOTAL関数が入っているか確認してください。
通常の連番とSUBTOTAL連番の違い
通常の連番とSUBTOTAL関数による連番は、見た目は似ていますが、動き方が違います。
| 方法 | 特徴 | 向いている場面 |
|---|---|---|
| 手入力の連番 | 番号が固定される | 並べ替えやフィルターを使わない表 |
| オートフィルの連番 | 簡単に連番を作れるが番号は固定される | 単純な一覧表 |
| SUBTOTAL関数の連番 | 表示されている行だけを数えて番号を振る | フィルターや並べ替えを使う表 |
表を作ったあとにフィルターや並べ替えを使う予定があるなら、最初からSUBTOTAL関数で連番を作っておくと便利です。
反対に、並べ替えもフィルターも使わない単純な表であれば、オートフィルで作った連番でも問題ありません。
Excelの表管理では関数の使い分けが重要
Excelでは、同じように見える操作でも、使う関数や設定によって結果が変わります。
今回のように、フィルター後の表示行だけを数えたい場合は、単純な連番ではなくSUBTOTAL関数を使う必要があります。
また、SUBTOTAL関数でも3と103では、手動で非表示にした行の扱いが変わります。
この違いを知っておくと、名簿、管理表、売上一覧、作業リストなどを扱うときに、番号のズレで悩むことが少なくなります。
Excelを仕事で使う機会が多い場合は、関数を単体で覚えるよりも、「どの場面で何を使うか」を整理しておくと作業が楽になります。
よくある質問
フィルター後だけならSUBTOTALの3でもよいですか？
フィルターで非表示になった行だけを除外したい場合は、3でも連番になります。
ただし、手動で非表示にした行も除外したい場合は、103を使う必要があります。この記事では、実務での使いやすさを考えて103を使っています。
基準列はC列でないといけませんか？
C列である必要はありません。
氏名、商品名、管理番号など、各行に必ず値が入っている列を基準にしてください。B列を基準にする場合は、次のように変更します。
=SUBTOTAL(103,$B$3:B3)空白行があるとどうなりますか？
基準列に空白があると、その行はカウントされません。
そのため、連番が飛んだように見えることがあります。空白の少ない列を基準にするか、基準列の空白を埋めてから使ってください。
並べ替えをすると数式は壊れませんか？
通常の範囲で正しく数式をコピーしていれば、並べ替え後も表示順に合わせて番号が振り直されます。
ただし、表の一部だけを選択して並べ替えると、データの対応関係が崩れることがあります。並べ替えるときは、表全体を対象にして操作してください。
連番を固定したい場合はどうすればよいですか？
SUBTOTAL関数の連番は、表示状態に合わせて変わります。
連番を固定したい場合は、数式が入っている範囲をコピーし、「値として貼り付け」を行います。ただし、値として貼り付けると、フィルターや並べ替えに合わせて自動で番号は変わらなくなります。
関連記事
Excelの連番や表作成を効率化したい場合は、次の記事もあわせて確認してみてください。
まとめ
Excelでフィルターや並べ替えをしても崩れにくい連番を作るには、SUBTOTAL関数を使います。
表示されている行だけに番号を振りたい場合は、次の数式を使います。
=SUBTOTAL(103,$C$3:C3)この数式では、C列の表示されている空白以外のセルを数えて、連番として表示します。
ポイントは、関数番号に103を使うことです。3でもフィルターで非表示になった行は除外できますが、103を使うと手動で非表示にした行も除外できます。
名簿、商品一覧、作業管理表など、フィルターや並べ替えをよく使う表では、SUBTOTAL関数による連番を設定しておくと便利です。
一度設定しておけば、表示状態に合わせて番号が自動で振り直されるため、手作業で連番を修正する手間を減らせます。
コメント