間に行が入ろうが何だろうが、通し番号を振ってやるぜ的な勢いで。
お久しぶりですな。震災以来。
実はこの記事、書き掛けで2ヶ月くらい放置してた……。もったいないので、最後まで書いてアップしておきますかね。
さておき、何かと言うと、よくある表の通し番号を、どうコピペしたり移動したり行追加したり結合したりしてもズレないようにする小技でございます。
その数式はこうです。
=MAX(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))+1
以下、関数の説明と数式の解説です。長いので、お好みでどうぞ。
| (1)はじめに | |
 | とりあえず、セルA1に数字の「1」を入れます。 |
 | セルA2に「=A1+1」と入れます。 |
 | もちろん、これは「2」となりますね。 |
| (2)ADDRESS関数 | |
 | お次、セルA3に「=ADDRESS(2,1)」と入れます。 |
 | すると、「$A$2」となりました。これは、()の中の左側の数字が「行」を、右側の数字が「列」を指定することになります。結果、これはセル「A2」を表し、さらに絶対値扱いで「$」が付きます。説明は適当です御無礼。 |
| (3)INDIRECT関数 | |
 | お次、セルA4に「=INDIRECT(A3)」と入れてみましょう。 |
 | すると、「2」となりました。INDIRECT関数は、引数を文字列として扱い、その文字列を指定セル名と解釈して、そのセルの値を返します。この場合は、セルA3の「$A$2」を引数とし、対象となるセル「A2」の値を引っ張ってきたわけです。 |
| (4)ROW関数 | |
| 次は、セルA5に「=ROW()」と入れてみます。 |
 | すると、「5」となりました。ROW関数は行番号を取得するのです。()内にセル名を入れると、そのセルの行番号を返します。指定しない場合は、ROW関数を入力しているセルの行番号を返します。 |
| (5)COLUMN関数 | |
 | 今度は、セルA6に「=COLUMN()」と入れてみましょう。 |
 | 今度は「1」となりました。COLUMN関数は、ROW関数の列バージョンなのです。考え方もROW関数と同じです。 |
| (6)MAX関数 | |
 | さて、次はセルA7に「=MAX(A1:A6)」と入れてみましょう。 |
 | 返ってきたのは「5」です。MAX関数は文字通り、引数に指定された範囲内の最大値を返すものです。なお、数値のみを対象とするため、セルA3の文字列「$A$2」は除外されます。ちなみに、対象範囲に数値がない場合は「0」を返しますが、日付があると想定外の値が返される可能性があります。詳細は説明メンドイので調べてください御無礼。 |
| (7)関数の合わせ技Lv.1 | |
 | ここからは、上記の関数の合わせ技解説です。とりあえず、セルA8に「=ADDRESS(ROW(),COLUMN())」と入れてみましょう。 |
 | はい、「$A$8」となりました。簡単ですね。ROW()は「8」、COLUMN()は「1（A)」、そうしたらADDRESS(8,1)となるわけです。 |
| (8)関数の合わせ技Lv.2 | |
 | お次はセルA9に「=MAX(A1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))」と入れてみます。どうなるかな？ |
 | 答えは「5」でした。(7)のROW()を-1することで、ADDRESS関数は1つ上のセル（A8）を返します。これを受けて、INDIRECT関数はセル「A8」の「$A$8」を返します。最後に、MAX関数は「A1:$A$8」の範囲の最大値「5」を返した、ということですね。要約すると、「自セルの列の、自セルより上のセル全範囲内の最大値を返す」というものです。 |
| (9)これを使って | |
 | というわけで(8)を利用して、A1を固定指定、さらに「+1」してやるだけで……？ |
 | とりあえず、連番になりました。 |
 | この式が入ったセルを、下にコピーしたら……上手いこと連番になっていますね。 |
 | 適当に間に行を挿入してみても、空白行は跳んで連番を維持しています。 |
| (10)「連続データコピー機能」の難 | |
 | たとえば、こんな表があって、通し番号をつけたいとします。 |
 | まぁ、セルの連続データコピーを使えば、できてしまうのですが。 |
 | ここで車種を増やしたいので、一行追加してみました。あ、No.がずれてしまいますね。また連続データコピーしなきゃ。この行数だから楽だけど、何百、何千と行があったら大変ですなぁ。 |
| (11)「上のセル+1」の難 | |
 | だったら、上のセルに「+1」してやれば大丈夫だよね、と。 |
 | セルをコピーして、連番になりました。 |
 | これなら、行が追加されても大丈夫……ではないのです。行が追加されても、数式はそのままなので、結局追加された行以下すべてコピーしなおしが必要です。 |
| (12)ROW関数の難 | |
 | じゃ、(4)のROW関数を応用して、行番号2から開始なら「=ROW()-1」ってやればOKでは？ |
 | はい、開始が「1」になりました。 |
 | このセルを下にコピーして……お、連番になりました。 |
 | で、車種を追加するので、行を追加したら、ちゃんと1個ずれたのでOKですね。 |
 | やっぱりメーカごとにわかりやすいよう、行を追加しよう……あ。こんなことに。 |
| (13)ということで | |
 | じゃ、今回説明した数式「=MAX(A$1:INDIRECT(ADDRESS(ROW()-1,COLUMN())))+1」を入れてみましょうか。 |
 | ちゃんと開始が「1」になりました。 |
 | このセルを下にコピーして、ちゃんと連番になりました。 |
 | さて、車種を追加して。 |
 | セルを上からコピー。おー、綺麗に連番になっていますね。ちなみに、「Ctrl + D」で上のセルのコピーができます。 |
 | さらに、メーカごと分かりやすいようにしてみましょう。 |
 | おー、番号はちゃんと通しで連番になっていますね。 |
