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

Excelで連番を自動で付与するための式を作成しました。
以下を参考にさせていただきました。
http://qiita.com/ikedat16/items/7753a8253e73e1d66b91

:::note warn
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行上が日付型の場合は、シリアル値からの連番となるためご注意ください。
（レアケースと思われるため考慮しておりません）