可視セルのみ連番が必要な理由
Excelで大量データを管理していると、フィルターや行の非表示を使って部分的にデータを表示させることがあります。この状態で通常の連番コピーを行うと、非表示行にも番号が振られるため、見た目や処理の順序が崩れてしまいます。可視セルのみ連番を振ることで、次のようなメリットがあります：
- フィルター結果に応じた正しい番号付けが可能
- 非表示行に影響を与えずにデータ整理できる
- VLOOKUPやSUMIFなどの関数で正しい参照が可能
方法1：SUBTOTALとROW関数を使う
ExcelのSUBTOTAL関数を使うと、可視セルのみを対象に計算できます。ROW関数と組み合わせることで、可視セルだけに連番を振ることができます。
- 連番を振りたい列の最初のセルに次の式を入力します：
=SUBTOTAL(3,$B$2:B2)※ここでB2は対象列の最初のデータセルです。SUBTOTAL(3,…)は可視セルのカウントを意味します。 - 式を下方向にコピーします。
- フィルターを適用しても、表示されている行だけに連番が振られます。
↓↓↓
この方法は、関数だけで完結するので、プラグインやマクロを使わずに処理できます。
方法2：F5 → 可視セル選択 → 連番入力（ホーム → フィル → 連続データ）
最初に番号を振りたいセルに１を入力
No.1を指定します。
連番を振りたい範囲を選択
フィルターや非表示行がある列（番号を振りたいセル）を全て範囲選択します。
F5キー → [ジャンプ] → [セル選択] → 可視セル を選択
これで非表示セルは除外され、可視セルのみが選択された状態になります
すべてのセルを「開いた状態」にする
オートフィルタを解除してセルを完全に開いた状態にします。この時、選択された可視セルがそのまま選択された状態であることを確認します
ホーム → 編集グループ → フィル → 連続データの作成 をクリック
連続データの設定
種類：行または列（今回なら列方向）
加算：チェックを入れる
開始値：1
終了値：適宜（省略可）
OKをクリック
可視セルのみを対象に、連番が自動で振られます
あとは再びデータをソートして表示したい状態に戻します。
可視セル連番作成時の注意点
- SUBTOTAL関数を使う場合、コピー範囲に非表示行があると正しくカウントされますが、絶対参照・相対参照の設定に注意しましょう。
- 手動でF5 → 可視セル選択する方法は、範囲を誤って選択すると番号がずれることがあります。
- フィルターを変更した場合、関数を使った方法なら自動で連番が再計算されますが、手動入力は再度処理が必要です。
よくある質問（FAQ）
Q1：マクロを使わずに可視セルのみ連番できますか？
A1：SUBTOTAL関数やF5 → 可視セル選択を使えば、マクロなしでも可能です。大規模データでは関数を使う方法が便利です。
Q2：フィルターを切り替えたら連番が崩れますか？
A2：関数を使う方法なら自動で再計算されます。手動入力の場合は再度可視セル選択して連番を振り直す必要があります。
Q3：連番が途中で抜けてしまいます
A3：SUBTOTAL関数で範囲が正しく指定されていない場合に起こります。式のコピー範囲を確認してください。
まとめ
今回は、Excelで可視セルのみ連番を振る方法を、関数を使う方法とF5ジャンプ＋連続データ機能を使う方法の2つで解説しました。フィルターや非表示行がある場合でも、どちらの方法を使えば正しく番号を振ることができます。
ポイントは次の通りです。
- SUBTOTAL関数を使う方法は動的で、フィルター変更後も自動で連番が再計算される
- F5 → 可視セル選択＋連続データ作成は、関数不要で手早く連番を振るのに便利
- 範囲指定やセル参照を正しく設定することが、途中抜けやズレを防ぐコツ
どちらの方法も、Excel標準機能だけで作業できるため、マクロや追加プラグインは不要です。今回ご紹介した手順を覚えておけば、フィルターや非表示行を使ったデータ管理でも、正しい番号付けが簡単に実行できます。ぜひ実際の業務で試してみてください。
コメント