ももらぼっ！にっき

_ [Excel] Excelで連番を振る方法を考える。

Excelの関数を使って自動で連番を降る方法をいろいろ考えてみます。

まず、ぱっと思いつくのは

A1： 1
A2： =A1+1
A3： =A2+1

という形です。 ただ、この場合は以下の欠点があります。

• 行の削除をすると削除行以降が「#REF!」になっちゃう。
• 行の追加をすると数式がずれる。
• 行の移動をしても数式がずれる。

これじゃ、オートフィルを使って連番生成しても一緒ですので、別の方法を考えます。 他のサイトでよく紹介されているのは、こんな形でしょうか。

A1： =ROW()
A2： =ROW()
A3： =ROW()

行番号をそのまま連番扱いにする方法ですね。 他のセルを参照していないため、行の追加/削除/移動をしても番号がずれることはありません。 ただ、これだとヘッダ行の行数分マイナスして補正してあげないといけないのが面倒です。（A3から始まる場合は、「=ROW()-2」とする） 途中で表の形式が変わった場合に、やっぱり計算式をいじらないといけません。 ちょっと改変して「=ROW()-ROW($A$3)+1」(先頭行がA3の場合)なんてことも考えたのですが、先頭行に行を追加するとやっぱり番号がずれてしまいます。

そこで考えたのが以下の方式です。 最初の案の欠点が他のセルを参照していたから起こっていたので、そこを改善してみました。

A1：=OFFSET(A1,-1,0)+1
A2：=OFFSET(A2,-1,0)+1
A3：=OFFSET(A3,-1,0)+1

必ずヘッダ行が一行以上存在することが前提ですが、これならROW関数のように現在の行数に依存していないため、ヘッダ行が増減しても行番号がずれることはありません。 前提が一つできてしまいましたが、こんな風に関数を入れて連番を生成したい表にヘッダがないなんてことは普通考えられないので、気にしなくてよさそうです。

また、以下のようにIFをいれることで、B列に値が入っていれば１から番号を振りなおす、なんてこともできるようになります。

A1：=IF($B1<>"", 1, OFFSET(A1,-1,0)+1)
A2：=IF($B2<>"", 1, OFFSET(A2,-1,0)+1)
A3：=IF($B3<>"", 1, OFFSET(A3,-1,0)+1)

個人的にはROW関数を使うより使いやすい気がするのですがどうでしょうか。

…余談ですが、このにっきを書くまで、「=INDIRECT(ADDRESS(ROW(A1)-1,COLUMN(A1)))+1」という式を紹介しようとしてました。 INDIRECTとADDRESSを組み合わせて無理やりOFFSETと同様のことを実現してたわけですが・・・。 なんというか、無駄な処理でしたね。 このにっきを書きながらOFFSET関数の存在を思い出せました。 やっぱり、一度整理して文章化するのって大事ですねー。