行挿入・行削除しても崩れない連続数

Excel Version: Excel2016 Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97 ([2]2017-11-26)

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

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

1.数式

 下表中の式、“=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


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

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

  A B C
1 No. となりのセル(A列)の内容 解   説
2 1 1  
3 2 =A2+1 (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セルをコピペすれば連続数に戻ります。
一時的に途切れてもいいから、式を短くしたい方はこれでもOKです。
そこで、連続数が途切れないように式を変更します。
5     挿入した行
6 4 =MAX($A$2:OFFSET(A6,-1,0,1,1))+1 (5) MAX関数で№の最小値(A2セル)~A6セルの1つ前のセルまでの範囲から最大値を取出し、それに+1する式にすると行挿入しても連続した表示になります。
(挿入したA5セルはA4セルをコピペするしかありません)
6 4 =MAX(OFFSET($A$2:A6,-1,0))+1 (6) (5)までは分かりやすくするためにOFFSET関数で引いてくる範囲をセル1個にしていましたが、OFFSET関数では複数のセル範囲を対象にすることができます。
最大値の取出しセル範囲をOFFSET関数内で指定することにすると、式が簡単になります。


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

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