行挿入・行削除しても崩れない連続数
  • Excel Version：
  • 2019
  • 2016
  • 2013
  • 2010
  • 2007
  • 2003
  • 2002
  • 2000
  • 97
  • ([3]2020-11-25)

Excel アイコン行挿入しても挿入したセルにだけ数式を前又は後からコピペすればよく、行削除してもエラーは発生しない連続値を表示する式です。

　表にはよく連続した番号を付けます。1と2を入れてあとはドラッグしてオートフィルでダーッと入れることが多いでしょうか。でも、行の挿入とか削除を繰り返す表では、その都度番号の振りなおしをしなければなりません。
そこで、数式を使って“１つ前のセルに＋１”する式（例：A10セルに=A9+1）を入れますが、それも行挿入をすると挿入したセルを飛ばした式に変化（例:A11セルなのに=A9+1）してしまいますし、行削除するとエラー（#REF!）になってしまいます。
この数式は、このような行挿入や行削除によって引きずられて数式が変化したりエラーの発生を防ぐ式です。

１．数式

　下表中の式、“=MAX(OFFSET($A$2:……))+1”をコピペしてお使いください。なお、$A$2は№の最小値のセル番号、A3は数式を入れるセル番号です。
　下記の表は、A1セルは項目名（№）、A2セルは№の最小値（1）、A3セルにはその右側にある計算式を入れます。A4セル以降はA3セルをコピペです。

A B
1 No. となりのセル(A列)の内容
2 1 1
3 2 =MAX(OFFSET($A$2:A3,-1,0))+1
4 3 =MAX(OFFSET($A$2:A4,-1,0))+1
5 4 =MAX(OFFSET($A$2:A5,-1,0))+1
← BACK
↑ PAGE TOP

２．数式を組み立てられるようになりたい人のための解説

　ポイントは、行挿入で行番号を引きずられ、行削除で参照先行番号がなくなる、ということがないようにすることです。

　 A B C
1 No. となりのセル(A列)の内容 解　　　説
2 1 1 　
3 2 =A2+1 (1) 基本はこの式：1つ前のセルに＋１をする式
4 3 =OFFSET(A4,-1,0,1,1)+1 (2) OFFSET関数を使い、1つ前のセル番号に頼らない式に変更します。
“A2”の部分を“OFFSET(…)”に置き換え、1つ前のセル番号が式中に出てこない式にします。
5 　 　 (3) この式は、こんな風に行挿入を実行すると、
6 1 =OFFSET(A6,-1,0,1,1)+1 (4) 数値が1になってしまいますが、A5セルにA4セルをコピペすれば連続数に戻ります。
一時的に途切れてもいいから、式を短くしたい方はこれでもＯＫです。
そこで、連続数が途切れないように式を変更します。
5 　 　 挿入した行
6 4 =MAX($A$2:OFFSET(A6,-1,0,1,1))+1 (5) MAX関数で№の最小値（A2セル）～A6セルの1つ前のセルまでの範囲から最大値を取出し、それに＋１する式にすると行挿入しても連続した表示になります。
（挿入したA5セルはA4セルをコピペするしかありません）
6 4 =MAX(OFFSET($A$2:A6,-1,0))+1 (6) (5)までは分かりやすくするためにOFFSET関数で引いてくる範囲をセル1個にしていましたが、OFFSET関数では複数のセル範囲を対象にすることができます。
最大値の取出しセル範囲をOFFSET関数内で指定することにすると、式が簡単になります。
← BACK
↑ PAGE TOP

Excel Tips『行挿入・行削除しても崩れない連続数』更新記録

2020/11/25
[3] Excel2019を追加。
2017/11/26
[2] HTML5,UTF-8対応。Excel version 2016を追加。解説(6)追加。[コピー]ボタンを設置。
2012/11/18
[1] Excel version 2013を追加。
2006/12/15
新規Tipsとして公開
← BACK
↑ PAGE TOP
Today is