概要
縦方向に連番を振るときによく使う=ROW() - nでは、連番より上の行を追加/削除すると連番がズレてしまいますよね。
=N(INDIRECT("R[-1]C",FALSE))+1を使えば、以下を満たす連番を振ることができます。
- 手前の行を追加/削除してもズレない
- 1から始まる連番を、同じExcel列に複数配置できる
使ってみる
まず、3通りの方法で記入した連番からなる表を、同一Excelシート内、2つの領域に配置しました。先頭2行および連番表の間の1行は空白としています。ROW() - nで実現する連番は、この時点ですでにnの部分がメンテしづらそうな感じが出ていますね。
先頭行に空白行を追加し、2つ目の表途中に行を挿入（後ろの行をコピーして挿入）した状態がこちら。
手打ちとROW() - nの2パターンは、以下の点で不都合が出ていてメンテナンス性に難があります。=N(INDIRECT("R[-1]C",FALSE))+1を使えば、それらを解決できます。
- 手打ちの難点
- 行をコピーして挿入した場合に、コピー元の連番が複製される
- 挿入した行の後続行が連番更新されない
- 上記2点より、挿入行以後の連番を自分で変更する必要がある
-
ROW() - nの難点- 先頭行に追加が入ったために、連番がすべて1つずつズレてしまう
- 連番表のそれぞれにおいて、
nを再度数えて手で修正する必要がある
=N(INDIRECT("R[-1]C",FALSE))+1が動いてる理屈
-
INDIRECT（参照文字列, 参照形式）を使って、「現在の行から 1 行上、同じ列」を参照しています。- 参照形式
- 参照形式を省略、またはtrueとするとA1形式での参照となります。（参照先のセル位置を"A1"と指定するアレ）
- これに対して、今回の例のように参照形式をfalseにすると、R1C1形式での参照となります。これは、行と列を相対的に指定するスタイルです。
- 参照文字列
R[-1]Cの意味-
R[-1]がRow（行）を示します。-1がついているので、現在の行から1つ上を指します。 -
CがColumn（列）を示します。数字が無いので、現在と同じ列を指します。 - これらを合わせると「現在の行から 1 行上、同じ列」の意味になります。現在のセルがB5ならB4を、D10ならD9を参照します。
-
- 参照形式
- 1で得られた値を
N()で数値に変換します。数値として解釈できない値は0が返ります。 - 2で得られた値に1を加算します。
これにより、連番開始位置は1つ上のセル値にN()をかまして取得した0に1を加算して1を表示します。次行以降は1つ上のセル値 + 1が続いて行って、連番を実現します。行の挿入や削除があっても、連番開始位置が1である限りは崩れません。
=N(INDIRECT("R[-1]C",FALSE))+1が使えないケース
連番開始位置（1にしたいところ）の1つ上のセル値が数値である場合、「連番開始位置の値が1つ上のセル値 + 1」になってしまうので使えません。1つ上のセルが真偽値（TRUE/FALSE）である場合も、TRUEは1扱いとなるので連番がズレます。まぁそんな使い方しないと思うので、大丈夫。
参考
Qiita「エクセル連番式いろいろ」