フィルターをかけても番号順！Excelでズレない連番を作る方法

はじめに：その連番、フィルターをかけるとボロボロになりませんか？
検証！なぜ普通の連番はダメなのか？
解決策1：見えているセルだけを数える関数（SUBTOTAL / AGGREGATE）
1. 方法1：AGGREGATE関数（推奨）
  1. 数式と解説
2. 方法2：SUBTOTAL関数（古典的テクニック）
  1. 数式と解説
  2. 豆知識：なぜ他の関数じゃダメなの？
解決策2：FILTER関数で「新しい表」として抽出する
まとめ：これであなたも配列職人！

はじめに：その連番、フィルターをかけるとボロボロになりませんか？

Excelで名簿や商品リストを作るとき、「No.」として連番を振ること、よくありますよね。

でも、いざ特定のデータだけを見ようと「フィルター」をかけると…

1, 2, 3, 4, …, 10 → 1, 2, 4, 7, 10

「うわっ、番号が飛び飛びになっちゃった！」

画面で見るだけならいいですが、これを印刷して配布したり、会議資料に使ったりする場合、「1, 2, 3…」ときれいに並んでいないと、困るときってありませんか？

今回のテーマは、「フィルターをかけても、行を非表示にしても、常に上から『1, 2, 3…』と並ぶ最強の連番」を作ることです！

前半は実務ですぐ使えるテクニック、後半は最新関数を使った「配列職人」向けのテクニックを紹介します。

さあ、一緒に連番を極めましょう！

※通常の連番作成については、連番入力！Excelで1～10000の数字を瞬時に入力する方法で紹介しています！

本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！

検証！なぜ普通の連番はダメなのか？

まずは、検証用のデータを用意します。

A1セルを起点に、以下のデータを入力してください。

={"名前";"a";"b";"c";"b";"d";"c";"a";"d";"c";"a"}

そして、B1セルに「連番」と入力し、B2セル以下にまずは普通の連番を作ってみましょう。

【失敗例】COUNTA関数で連番

=COUNTA($A$2:A2)

これをB11までフィルコピーします。

見た目は完璧な連番です。

しかし、ここでフィルターをかけてみます。

A1:B11の範囲を選択し、[Ctrl] + [Shift] + [L]を押します。
または、「ホーム」タブ→「並べ替えとフィルター」→「フィルター」を選択します。

A列の「▼」ボタンから、「a」と「b」だけを選択して絞り込んでみましょう。

結果は… 1, 2, 4, 7, 10

はい、飛び飛びになってしまいました。

SEQUENCE関数やROW関数を使っても結果は同じです。

=SEQUENCE(10) =ROW(A1:A10)

なぜなら、これらの関数は「隠れている行も、そこに存在するものとして数えてしまう」からです。

解決策1：見えているセルだけを数える関数（SUBTOTAL / AGGREGATE）

この問題を解決するには、「画面に見えている（非表示になっていない）セルだけをカウントする」特殊な関数が必要です。

それが、SUBTOTAL関数と、その進化版であるAGGREGATE関数です。

方法1：AGGREGATE関数（推奨）

まずは、最も高機能なこちらから。

数式と解説

=AGGREGATE(3,5,$A$2:A2)

この数式をB2に入力し、下にフィルコピーしてください。

フィルターをかけても、手動で行を非表示にしても、必ず「1, 2, 3…」と連番になります！

【引数の解説】

AGGREGATE(集計方法, オプション, 範囲)

・3 (集計方法)：これはCOUNTA（データの個数）と同じ意味です。

・5 (オプション)：これが最強の理由！「非表示の行を無視する」というオプションです。

これをつけることで、見えているセルだけをカウントします。

似たようなオプションと比較してみましょう。

4：何も無視しません。（普通のCOUNTAと同じになります）
5：隠れた行を無視します。（今回はこれ！）
6：エラー値を無視します。（隠れた行は数えてしまいます）
7：隠れた行とエラー値の両方を無視します。（これも使えますが、今回はエラーがない前提なので5で十分です）

・$A$2:A2 (範囲)：範囲の先頭を絶対参照で固定し、自分自身の行までを範囲とすることで、行が進むごとに範囲が広がる「累計カウント」を行っています。

方法2：SUBTOTAL関数（古典的テクニック）

数式と解説

昔からあるSUBTOTAL関数でも可能です。ただし、引数の指定に少しクセがあります。

パターンA：フィルターのみ対応

=SUBTOTAL(3,$A$2:A2)

パターンB：フィルター＆手動非表示に対応

=SUBTOTAL(103,$A$2:A2)

【引数の解説】

第1引数（集計方法）がポイントです。

・3：COUNTAと同じですが、「フィルターで隠れた行」は無視します。

しかし、「手動で右クリックして非表示にした行」は数えてしまいます。

・103：こちらは「フィルター」も「手動非表示」も両方無視します。

実務では、とりあえず AGGREGATE(3,5,...) か SUBTOTAL(103,...) を使っておけば間違いありません！

豆知識：なぜ他の関数じゃダメなの？

Excelの計算は基本的に「データが存在するかどうか」で行われます。

「画面に見えているかどうか」という”見た目の状態“を計算結果に反映できるのは、このSUBTOTALとAGGREGATE（あとはセル色の判定などができる旧式のExcel4.0マクロなど）くらいしかありません。

非常に貴重な関数なんです。

解決策2：FILTER関数で「新しい表」として抽出する

ここからは発想を転換します。

「既存の表にフィルターをかける」のではなく、「条件に合うデータだけを抽出して、別の場所に新しい表を作る」という、現代Excelのアプローチです。

抽出したデータに対して、新しく連番を振り直せば、絶対にズレることはありません。

今回は、A列から「a」または「b」であるデータを抽出し、その隣に連番を振ってみましょう。

ステップ1：条件フラグを作る

まずは、「a」か「b」かを表す0と1の配列を作ります。

=(A2:A11="a")+(A2:A11="b")

これを計算すると、{1;1;0;1;0;0;1;0;0;1} のような配列になります。（1が対象、0が対象外）

ステップ2：0を無視して連番を振る（SCAN関数）

次に、この0と1の配列を使って、「1が出てきたときだけカウントアップする」連番を作ります。

ここで登場するのが、配列操作の強力な武器 SCAN関数 です。

=SCAN(0, フラグ配列, LAMBDA(acc, x, acc+(x<>0)))

【変数の意味】

・acc (Accumulator：アキュムレータ)：累積値という意味です。これまでの計算結果を溜め込んでおく場所ですね。
・x: 配列から取り出した一つひとつの値（ここでは1か0）。

acc+(x<>0) の部分で、「もしxが0でなければ（対象データなら）、累積値（acc）に1を足す」という処理を繰り返しています。

これで、{1;2;2;3;3;3;4;4;4;5} のような、「対象データが見つかるたびに増える連番」が生成されます。

ステップ3：合体して抽出！（完成形）

最後に、これをFILTER関数で抽出します。

数式と解説

=LET( rng, A2:A11, flag, (rng="a")+(rng="b"), seq, IF(flag=0, 0, SCAN(0, flag, LAMBDA(acc, x, acc+(x<>0))) ), FILTER(HSTACK(rng, seq), flag) )

うーん、長いですがブロックごとに見れば簡単です。

rng (Range:範囲): 元のデータ範囲（名前）です。
flag: 「aかbなら1、それ以外は0」の配列を作ります。
seq (Sequence:連番): 先ほどのSCAN関数で連番を作ります。ただし、対象外（0）の行は「0」にしておきます。
FILTER: HSTACK(rng, seq)で名前と連番を横に並べ、flagが1（対象）の行だけを抜き出します。

これで、抽出されたデータに対して、きれいに「1, 2, 3…」と連番が振られます！

応用：条件をもっと自由にしたい！（汎用版）

「aとbだけじゃなくて、cも入れたい！」

そんな時に、いちいち (rng="a")+(rng="b")+(rng="c")... と書くのは大変ですよね。

そこで、MATCH関数を使ってスマートに書き換えたのがこちらです。

数式と解説

=LET( rng, A2:A11, cond, {"a","b"}, flag, ISNUMBER(MATCH(rng, cond, 0)), frng, FILTER(rng, flag), seq, SCAN(0, frng, LAMBDA(acc, x, acc+1)), HSTACK(frng, seq) )

【解説】
・cond (Condition：コンディション): 「条件」という意味です。ここに{"a", "b"}のように抽出したいリストを入れます。
・flag: MATCH関数を使って、リストに含まれているかを判定します。
・seq: 今度は抽出した後（FILTERした後）のデータに対して、単純に1ずつ足すSCANを行っているので、ロジックがシンプルになっています。