Excelで意味のない番号を表に振っている人はこの数式使って下さい ～ INDIRECT関数

とくに意味はなくとも、番号を表に振っている人、よくいますね？

そういう人はおそらくコピペで番号がずれたりしてイライラしていると思うので、それを解消するために、この数式を使うといいと思います。

=MAX(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))+1

数式の解説

ちょっと詰まっていて見づらいですね。

この数式を見やすく整形すると、こうなります。

=MAX(
  INDIRECT(
    ADDRESS(1,COLUMN())
    & ":"
    & ADDRESS(ROW()-1,COLUMN())
  )
 ) + 1

下記ポイントをおさえるとわかりやすいでしょう。

• ROW関数は現在行の番号、COLUMN関数は現在列の番号を返す関数
• ADDRESS関数は行と列番号をもらって、アドレス($A$1みたいなの)文字列を生成する関数
• INDIRECT関数はアドレスを指す文字列をもらうとセルへの参照を作成する関数
• MAX関数で今のセルと同じ列について、行番号1～今のセルの一個上までのMAXを取り、それに+1する。

たとえば、$C$10にこの数式をおいた場合は =MAX( $C$1:$C$9 ) + 1 と同じになります。

要するに今のセルより上の方の最大の番号+1を今のセルに与えるってこったな！

番号づけに数式を使う場合の注意点

ただし、この方法で番号を振ると、いわゆる個体識別的な意味で番号を振っているわけではないので、間に行を入れたりすると容赦なく番号が振り直されます。そういうのが困る人は、数式ではなく固定の番号をセルに入れましょう。

上から内容を追記するだけのシートは、このエントリの方法が一番適しています。

共有フォルダに共有モードで置かれた課題管理表とかいう呪われた代物の管理にはぴったりでしょう。

この数式のメリット

この数式の一番嬉しい点は 数式にアドレスが出現しないことです。

この数式に限らず、INDIRECT関数などを用いて数式からアドレスを排除することにより、コピペしても忠実に数式のセル位置から結果を算出してくれます。

たとえば、VBAでこういった番号を入れる場合、現在の行番号をなんらかの形で持たなきゃいけなかったりして、とっても面倒ですが、この数式を入れるだけなら、わりと簡単です。

' こうするだけでいい
currentRange = "=MAX(INDIRECT(ADDRESS(1,COLUMN())&"":""&ADDRESS(ROW()-1,COLUMN())))+1"

INDIRECT関数の応用方法

条件付き書式など、自分のセルの値に基いて書式を決定させる場合、下記のような数式を使えば忠実に自分からの相対セルを見るようになります。（コピペなどでズレたりしません）

="×"=INDIRECT(ADDRESS(ROW(),COLUMN()))

※自セルが×かどうか判定。

ただ、上記例であれば、"×"が入っているセルに連動して行全体が灰色になったりしてほしいケースがほとんどでしょう。

私の場合、そういったケースの時は、まず、"×"が入っている列のヘッダに名前を与えちゃいます。

その上で下記のような条件付き書式（数式を使用して、書式設定するセルを決定）をヘッダから下の行全体に適用させます。

="×"=INDIRECT(ADDRESS(ROW(),COLUMN(ステータス)))

ちゃんとぜんぶ灰色になりました。やったね！

Q&A

ROW()関数で番号振ればよくね？

歯抜けになったとき、途中で番号があがった時、このエントリの方法のほうが有利です。

それに加え、ヘッダ行より上の行を増減したとかで全部の数式書き換えたりしたくないですね。 （ただし、この場合は、=ROW()-ROW(ヘッダのセル) で対処しても可）

この呪文覚えるのめんどい

IMEに変換候補として登録してください。

まとめ

• INDIRECT関数は便利だ。もっと使え！

みなさんもメンテナンス性のいいシートを作成してください。方眼紙作る奴は死刑だ！

2014-05-26追記） ちなみにINDIRECTで数字振るのは大量の行がある場合は、かなり重くなる場合も考えられるので、そういう場合は直接数字入れていったほうがいいです。大量の件数データ吐くマクロとか。