【Excel】行を移動・削除・非表示にしても連番を崩さないテクニック★
こんにちは、HARUです！
顧客名簿や商品・サービスの情報をまとめた表に通し番号（No.）を付記することがあります。
こうした連続した番号＝「連番」をふる際は、オートフィルを使ったり、"上のセル＋１"といった簡易的な数式を入力したりするのが一般的です。
ただしこの手順で追記した連番は、行を移動したり、削除したり、非表示にしたりすると、通し番号が成り立たなくなることがほとんどです。
下図は5行目のデータを削除した状態。
そこでこの記事では、行の移動・削除・非表示などの操作を行っても連番を成立させる方法をご紹介します。
番号が各データに紐づいているマスターデータではなく、今見えている情報に正しく通し番号を設定して閲覧しやすい表を作成したいときに重宝できるテクニックです。
ぜひご一読ください！
行を移動・削除しても連番が成立
行の移動や削除をしても連番を成り立たせるには、ROW関数を使います。
（row：行、並び）
ROW関数
ROW関数は、参照したセルの行番号を返す関数です。
特にセルを参照しなければ、ROW関数を入力したセルの行番号を表示します。
※下図は3行目にROW関数を入力したことで、"3"が返された状態。
今回は先頭の空白行と見出し行の2行分を差し引いた3行目から通し番号をスタートしたいので、ROW関数で返した行番号から"2"を引きます。
これにより、"1"が表示されます。
数式を下へコピーすると、キレイな連番となります。
ROW関数で採番することで数字が行番号に紐づくため、行を削除しても自動で連番に更新されます。
※下図は5行目のデータを削除した状態。
また、行を移動しても連番は成立します。
※下図は9行目のデータを4行目と5行目の間に移動した状態。
COLUMN関数
本題から少し反れますが、参照セルの行番号を返すROW関数に対して、参照セルの列番号を返すCOLUMN関数があります。
（column：列、柱）
入力要領はROW関数と同じで、特に参照元を指示しなければCOLUMN関数を入力したセルの列番号を数値で返します。
列番号を数値化しておくことで検索関数などによるデータの抽出を効率的に行うことができます。
ROW関数とCOLUMN関数はぜひセットでおさえておきましょう！
行を非表示にしても連番が成立
数値が行番号に紐づくROW関数の場合、行ごと非表示にすると連番が崩れます。
※下図はオートフィルターでSSクラスの会員に絞り込んだ状態。
非表示行を除き、今見えているデータに改めて通し番号をふるには、SUBTOTAL関数を使います。
SUBTOTAL関数は非表示行を除いて様々な集計を行ってくれる関数でした。
↓SUBTOTAL関数の詳細解説はこちら↓
この「非表示行を集計対象から除外する」性質を利用して、隠れているデータを除いて連番を生成する仕組みを作っていきます。
SUBTOTAL関数
①SUBTOTAL関数を挿入し、第１引数「集計方法」で【103（COUNTA）】を選択する。
※COUNTAはデータの個数を数える役割がある。103を選ぶことにより、フィルターによる絞り込みで非表示になった行も、グループ化などにより手動で非表示になった行も、いずれもカウント対象から除外される。
②第２引数「参照１」に、氏名の見出しと会員の氏名の範囲を参照する。
このあと数式を下へコピーすることを見据え、氏名の見出し（B2セル）のみ絶対参照にしておく。
③SUBTOTAL関数で求めたデータの個数から、"1"を差し引く。
これにより、先頭の会員に"1"が表示されます。
数式を下へコピーすると、連番が生成されます。
第２引数「参照１」において、氏名の見出し（B2セル）のみ絶対参照にしたことにより、数式を下へコピーするに従い、集計対象の範囲が拡張していきます。
データの個数を数えるCOUNTAを第１引数「集計方法」とすることで、各地点でそれまでに表示されている氏名の数をカウントしているということです。
ここで改めてSSクラスの会員に絞り込んでみると、現在表示されているデータにあわせて通し番号が更新されます。
行を手動で非表示にした場合でも連番が成立します。
※下図は5行目、8行目、9行目を隠した状態。
非表示行を集計対象から除外するSUBTOTAL関数だからこそ成せるワザですね！
ちなみに、第２引数「参照１」にあえて氏名の見出しを含んで最後に"-1"という処理をしているのは、SUBTOTAL関数とオートフィルターの相性次第で誤ったデータが残ってしまう可能性があるためです。
試しに氏名の見出しを含まず、先頭の会員からデータのカウントをスタートしたとします。（"-1"という処理もしておりません）
ここで、前述と同じくオートフィルターでSSクラスの会員に絞り込んでみると、最終行にSクラスの会員が残ってしまいます。
氏名の見出しからカウントし、"-1"という処理を行うことで、こうした事態を防ぐことができます。
いかがでしたか？
今回は、行の移動・削除・非表示などの操作を行っても連番を成立させる方法をご紹介しました。
ROW関数とSUBTOTAL関数のそれぞれの特長を理解した上で、シーンに応じて使い分けていきましょう！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？