Excel で連番を振る。なるだけ綺麗な数式を心がけて

メモ。

前提として、

  • 連番を振る列は、テーブル（ListObject オブジェクト）に含まれる
  • 連番を振りたい列の見出しは文字列である

とします。Excel のテーブル機能を積極的に使わない理由は無いですし、列見出しが文字列以外である場合もあまり無いだろう、という考えです。

※テーブル名は、日本語版 Excel のデフォルトである テーブル1 とする

数式↓

=IF(TYPE(OFFSET(テーブル1[@],-1,0))=1,OFFSET(テーブル1[@],-1,0)+1,1)

見やすいように適当に整形すると（セルにそのまま入れる場合は Alt + Enter で改行して半角スペースでインデント）、

=IF(
    TYPE(
        OFFSET(テーブル1[@],-1,0)
            )=1,
    OFFSET(テーブル1[@],-1,0)+1,
    1
    )

こんな感じ。処理の流れとしては、

自セルの一行上のセルのデータ型が数値であれば、上のセルの値に 1 を足し、そうで無ければ 1 を入れる

というもの。

メリットは、自セルを構造化参照でスッキリ参照出来る所ですね。テーブルを使わないと、ADDRESS・ROW・COLUMN・INDIRECT　等の各関数を組み合わせてやる必要があって、かなりごちゃごちゃします。構造化参照なら、[@]とだけ入れれば良いです。テーブル名は自動的に補完されるので、テーブル1[@]というようになります。
@ は、そのセルが属する行の指定子なので、それだけ入れてあげれば、後は自セルが属する列の指定は省略出来るので、自セルの構造化参照になる、という寸法。

そして、OFFSET 関数で一つ上のセルを参照して、TYPE 関数で（ISNUMBER でも可）データ型が数値であるかを判定して、数値であれば一つ上の値は見出しでは無いので、一つ上の値に 1 を足したものを自セルの値に入れ、もし一つ上のセルの値が文字列であれば、それは見出しなので、自セルの値には 1 を入れてあげると。

この数式であれば、明示的なセル参照が一切無く、見出し列の文字列すら入っていないので、それなりにスッキリしていて、そこそこ汎用的であると思います。テーブルの数式に明示的参照を入れると、行を削除した時などにエラーが発生したり、相対参照を入れてあると、行の追加で参照が狂う事があるので、構造化参照のみの数式を作りたい所です。