6
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Excelにて自動連番のための式

Last updated at Posted at 2020-04-18

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を加算することになるため、同じ列に別の連番は作成できません。

6
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?