Excelで連番を自動で付与するための式を作成しました。
以下を参考にさせていただきました。
http://qiita.com/ikedat16/items/7753a8253e73e1d66b91
式
式をコピペするだけで利用できます。
IMEの辞書登録等をしておくことをおすすめします。
セルが連続している場合
※通常はこちらをご利用ください。
=N(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0))+1
- 垂直方向の連番
- セルが連続している場合に連番となる
- 同じ列に複数の連番が作成可能
セルが離れている場合(結合セルの場合)
=MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1))+1
- 垂直方向の連番
- セルが離れていても連番となる
- 同じ列に複数の連番は作成不可(連番が継続するため)
仕組みの解説
セルが連続している場合に利用する式
概要としては、現在位置のセルの1行上のセルの値を数値に変換し1を加算しています。
ADDRESS関数(参照文字列の作成)
ADDRESS( ROW(), COLUMN() )
行番号と列番号からセルの参照文字列（例：A1）を作成します。
ROW()、COLUMN()を利用して、現在位置のセルを取得しています。
INDIRECT関数(参照状態の作成)
INDIRECT( ADDRESS(ROW(),COLUMN()) )
上記のADDRESS関数で作成した参照文字列から参照状態を作成します。
ADDRESS関数で取得した値はあくまで文字列です。
INDIRECT関数に渡すことで初めて「参照」となります。
OFFSET関数(基準位置から指定した距離にあるセルの値の取得)
OFFSET( INDIRECT(ADDRESS(ROW(),COLUMN())), -1, 0) )
第一引数：基準となるセル
第二引数：基準セルからの行の距離(垂直方向)
第三引数：基準セルからの列の距離(水平方向)
第一引数の基準位置には、INDIRECT関数で作成した「参照」を渡します。
第二引数の行の距離を -1 とすることで、1つ上のセルの値を取得することになります。
N関数
N( OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)) )
上記のOFFSET関数で取得した値を数値型に変換します。
この後の1の加算が上手くいくようにするための保険です。
ただし、N関数で変換できないエラー値や、日付型(2020/04/18等)が先頭に来ると、連番がおかしくなります。
（レアケースだと思うので、気にしていません。。。orz）
最後に1を加算
N(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0))) + 1
上記のN関数で数値型に変換した値に、1を加算することで連番となります。
セルが離れている場合に利用する式
概要としては、先頭行から現在位置までセルの値の最大値に1を加算しています。
ADDRESS関数(参照文字列の作成)
ADDRESS( 1, COLUMN() )
行番号と列番号からセルの参照文字列（例：A1）を作成します。
こちらは最終的に、OFFSET関数の基準位置となります。
基準を先頭行の位置としたいため、
行番号は 1、列番号は ROW()（現在位置の列番号） としています。
INDIRECT関数(参照状態の作成)
INDIRECT( ADDRESS(1,COLUMN()) )
上記のADDRESS関数で作成した参照文字列から参照状態を作成します。
ADDRESS関数で取得した値はあくまで文字列です。
INDIRECT関数に渡すことで初めて「参照」となります。
OFFSET関数(基準位置から指定した距離にあるセル範囲の値の取得)
OFFSET( INDIRECT(ADDRESS(1,COLUMN())), 0, 0, ROW()-1, 1 )
第一引数：基準となるセル
第二引数：基準セルからの行の距離(垂直方向)
第三引数：基準セルからの列の距離(水平方向)
第四引数：取得する行の範囲
第五引数：取得する列の範囲
第一引数の基準位置には、INDIRECT関数で作成した「参照」を渡します。
今回、基準位置は1行目となり、距離はそのままのため 0 です。
第四引数と第五引数で、参照範囲を指定できます。
行の範囲は、現在位置の行-1までの範囲とするため ROW()-1
列の範囲は、1列のみのため 1 としています。
すなわち、「先頭行から現在位置より1行前まで」の参照範囲となります。
MAX関数
MAX( OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1) )
上記のOFFSET関数で取得した参照範囲をMAX関数に渡します。
「先頭行から現在位置より1行前までの値」で最大値が取得できます。
最後に1を加算
MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1)) + 1
上記のMAX関数で取得した値に、1を加算することで連番となります。
セルが離れていても最大値を取得するため、連番となります。
結合されているセルで連番とする場合などに役立ちます。
逆に、最大値に1を加算することになるため、同じ列に別の連番は作成できません。