Excelで連番を自動で付与するための式を作成しました。
以下を参考にさせていただきました。
http://qiita.com/ikedat16/items/7753a8253e73e1d66b91
2024/11/15
以前の式では、1行目の場合に参照エラーとなることが判明したため、式を修正しました。
IFERROR関数で判定し、エラーの場合は1とすることで、保険で入れていたN関数が不要となりました。
また、ADDRESS関数で基準位置をずらせるため、OFFSET関数も不要となりました。
式
式をコピペするだけで利用できます。
IMEの辞書登録等をしておくことをおすすめします。
セルが連続している場合（通常時）
- セルが連続している場合に連番となる
- 同じ行/列に複数の連番が作成可能
縦並び
=IFERROR(INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1,1)
横並び
=IFERROR(INDIRECT(ADDRESS(ROW(),COLUMN()-1))+1,1)
セルが離れている場合（結合セルの場合）
- セルが離れていても連番となる
- 逆に、同じ行/列に複数の連番は作成不可（回避策あり）
縦並び
=IFERROR(MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1))+1,1)
横並び
=IFERROR(MAX(OFFSET(INDIRECT(ADDRESS(ROW(),1)),0,0,1,COLUMN()-1))+1,1)
複数の連番を作成する場合
多少無理やりですが、OFFSET関数の基準位置をずらすことで、前の連番を拾わなくなるため複数の連番を作成可能です。
以下は A10 セルから新しい連番を作成する例
縦並び
=IFERROR(MAX(OFFSET(INDIRECT(ADDRESS(ROW($A$10),COLUMN())),0,0,ROW()-ROW($A$10),1))+1,1)
横並び
=IFERROR(MAX(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$10))),0,0,1,COLUMN()-COLUMN($A$10)))+1,1)
仕組みの解説
縦並びを例に解説しますが、横並びも同様です。
セルが連続している場合に利用する式
概要としては、現在位置の1行上のセルの値に1を加算しています。
ADDRESS関数（参照文字列の作成）
ADDRESS( ROW() - 1, COLUMN() )
行番号と列番号からセルの参照文字列（例：A1）を作成します。
ROW()、COLUMN()を利用して、現在位置のセルを取得しています。
現在位置から1行上を取得するため、行番号は ROW()（現在位置の行番号）から1を引いています。
INDIRECT関数（参照状態の作成）
INDIRECT( ADDRESS(ROW()-1,COLUMN()) )
上記のADDRESS関数で作成した参照文字列から参照状態を作成します。
ADDRESS関数で作成した値はあくまで文字列であり、INDIRECT関数に渡すことで初めて「参照」となります。
1を加算
INDIRECT(ADDRESS(ROW()-1,COLUMN())) + 1
上記のINDIRECT関数で1行上のセルの値が取得できるため、その値に1を加算します。
IFERROR関数（エラー判定）
IFERROR( INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1, 1 )
1行上のセルが存在しない場合や、文字列の場合はエラーとなってしまいます。
エラーの場合は、1を返すようにすることで、連番が壊れないようにします。
ただし、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を加算することで連番となります。
セルが離れていても最大値を取得するため、連番となります。
結合されているセルで連番とする場合などに役立ちます。
IFERROR関数（エラー判定）
IFERROR( MAX(OFFSET(INDIRECT(ADDRESS(1,COLUMN())),0,0,ROW()-1,1))+1,1 )
1行上のセルが存在しない場合や、文字列の場合はエラーとなってしまいます。
エラーの場合は、1を返すようにすることで、連番が壊れないようにします。
ただし、1行上が日付型の場合は、シリアル値からの連番となるためご注意ください。
（レアケースと思われるため考慮しておりません）