列の途中に空白、文字列、セル結合があっても途切れない強力な連番を振ることができる数式【エクセルTips】

エクセル

はじめに

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行目に入れるとエラーの代わりに1を返す役割をしています。
AGGREGATE関数は第1引数を2、第2引数を5に設定して非表示を無視した数字のカウントをします。
OFFSET関数のまとまりはセルA1を起点に、数式が入っているセルと同じ列の1行目から1つ上の行までのセル範囲の参照を返します。
つまり、数式が入っているセルの上に何個数字があるのかを数え、1を加えた数字を返します。

キュウ
キュウ

「数字をカウント」というのが空白、文字列、セル結合の影響を受けないとイコールなんですね!

シノ
シノ

そう言い換えると分かり易いですね!

キュウ
キュウ

弱点とかは無いんですか?

シノ
シノ

グループ化(ショートカットキーは『Shift + Alt + →』、解除は『Shift + Alt + ←』)による非表示状態には対応できません。

キュウ
キュウ

なるほど、その点には注意しますね!
よ~し、この強力な数式でリベンジしてきます!

シノ
シノ

がんばってね~

おわりに

これもTwitterで教えて頂いた数式がベースになった数式です。
途中でセル結合、文字列、空白、非表示、グループ化に阻まれようと突き進む強力な連番…
ただしグループ化には対応できませんでした。
(グループ化に対応する方法があったら教えてください)
それにしても、連番って便利ですよね。
資料を使った会議では、どこの話をしているのか特定するために必須でしょう。
連番抜けがあるために資料全体の質を疑われたことがあります。当然です。
楽と品質、両方を兼ね備えたエクセル作りの助けになれば幸いです!

【外部リンク】AGGREGATE関数|Microsoftサポート

最後までお付き合い頂き、ありがとうございました!

当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています!
よろしければこちらの記事もどうぞ~!

【内部リンク】エクセル関数 SUMIF関数擬人化記事

コメント

タイトルとURLをコピーしました