はじめに
Excelの資料に連番を振りたいという場面は多いと思います。
しかし、連番は手作業で振るとデータ追加の度に修正が必要となり、大きな手間がかかるため数式で作成して楽をしたいところです。
ただ数式を使って連番を振る方法にしても、途中にセル結合、空白行、文字列、非表示、グループ化による非表示（隠された状態）が入っていると上手く連番が振られなかったり、
シートの1番上（1行目）では使えないといった問題が起こるので、それらの問題に対応できる数式を紹介します。
紹介する数式
=IFERROR(AGGREGATE(2,5,OFFSET(INDIRECT("A1"),0,COLUMN()-1,ROW()-1))+1,1)途中にセル結合、文字列、空白、非表示、フィルターがあっても崩れない連番を振る数式
シノさん…私に強力な連番が作れる数式を下さい。
力が必要です。
あらあら、どうしたんですか？
会議で使う資料の連番が崩れていると注意されちゃいました。
データの追加や削除、並び替えがあったり途中に小計行を挟んでいたり…
連番の管理を楽にしたいです。
わかりました、それでは力（数式）を授けましょう！
紹介する数式
=IFERROR(AGGREGATE(2,5,OFFSET(INDIRECT("A1"),0,COLUMN()-1,ROW()-1))+1,1)
途中にセル結合、文字列、空白、非表示、フィルター…
1つの数式で全部対応している…！？
なかなか強力でしょう！
IFERROR関数は１行目に入れるとエラーの代わりに1を返す役割をしています。
AGGREGATE関数は第1引数を2、第2引数を5に設定して非表示を無視した数字のカウントをします。
OFFSET関数のまとまりはセルA1を起点に、数式が入っているセルと同じ列の1行目から1つ上の行までのセル範囲の参照を返します。
つまり、数式が入っているセルの上に何個数字があるのかを数え、1を加えた数字を返します。
「数字をカウント」というのが空白、文字列、セル結合の影響を受けないとイコールなんですね！
そう言い換えると分かり易いですね！
よ～し、この強力な数式でリベンジしてきます！
がんばってね～
おわりに
これもTwitterで教えて頂いた数式がベースになった数式です。
途中でセル結合、文字列、空白、非表示、グループ化に阻まれようと突き進む強力な連番…
それにしても、連番って便利ですよね。
資料を使った会議では、どこの話をしているのか特定するために必須でしょう。
連番抜けがあるために資料全体の質を疑われたことがあります。当然です。
楽と品質、両方を兼ね備えたエクセル作りの助けになれば幸いです！
【外部リンク】AGGREGATE関数|Microsoftサポート
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント