Excelで空行を飛ばして上にある数字+1する数式

タイトルだけみると何のことかわかりづらいが、要するに以下の様な動作をする数式となる。空行や文字列のセルは無視して、該当セルの直前にある数字を見つけて、その数字をインクリメントする数式で、飛び飛びで連番を作る必要がある場合に便利。

数式は以下の通り。長くてわかりづらいので、分解して説明する。

=INDEX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),MATCH(MAX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)))+1,INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),1))+1

数式で使用している関数の説明

まず使っている数式の説明。

INDEX関数

INDEX(配列, 行番号, 列番号, 領域番号)
説明：配列で指定した場所から(行番号, 列番号)の位置にある情報を取り出す関数。例えば、図で示したExcelシートの場合、「=INDEX(A1:A4, 3, 1)」とすれば、A3にある数字「2」が返ってくる。

ROW関数

ROW(参照)
説明：ROW()とするとExcelの行番号を返す関数。

COLUMN関数

COLUMN(参照)
説明：COLUMN()とするとExcelの列番号を返す関数。

ADDRESS関数

ADDRESS(行番号, 列番号, 参照の型, 参照形式)
※「参照の型」は以下の通り。
　1：絶対参照(例：$A$1) (※省略時)
　2：行は絶対、列は相対(例：A$1)
　3：行は相対、列は絶対(例：$A1)
　4：相対(例：A1)
※「参照形式」は以下の通り。
　1：A1形式 (※省略時)
　0：R1C1形式

説明：行番号と列番号を指定すると、セルの場所を文字列として表示する関数。例えばA5のセルに「=ADDRESS(ROW()-1,COLUMN(),4,1)」と入れれば「A4」という文字列が返ってくる。

INDIRECT関数

INDIRECT(参照文字列, 参照形式)

※「参照形式」は以下の通り。
　1：A1形式 (※省略時)
　0：R1C1形式

説明：参照文字列にExcelの数式を入れると、文字列ではなく数式として処理する関数。

MATCH関数

MATCH(検査値, 検査範囲, 照合の型)
※照合の型
　1：検索値以下の最大の場所を返す(正しく使う場合、値は昇順で並べておく)
　0：検索値に等しい最初の場所を返す
　-1：検索値以上の最小の場所を返す(正しく使う場合、値は降順で並べておく)
説明：検査範囲から検査値の値を検索し、相対的な場所を返す関数。

数式を分解して説明

A14のセルに本数式を入れた場合で考えてみる。

=INDEX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),MATCH(MAX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)))+1,INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),1))+1

まず、ADDRESS関数を変換してみる。

=INDEX(INDIRECT(A1):INDIRECT(A13),MATCH(MAX(INDIRECT(A1):INDIRECT(A13))+1,INDIRECT(A1):INDIRECT(A13),1))+1

INDIRECT関数にて文字列を数式として扱うよう変換する。ここでだいぶスッキリする。

=INDEX(A1:A13,MATCH(MAX(A1:A13)+1,A1:A13,1))+1

MAX関数を計算する。最大値はA7の"4"になるため、4+1=5になる。

=INDEX(A1:A13,MATCH(5,A1:A13,1))+1

MATCH関数を計算する。ここがミソで、検索値は最大値+1の"5"となり、検索範囲において一致する値は絶対に無いことになる。この場合、MATCH関数では「検索値以下の最大の行の位置」が返されるので、結果的に"10"行が返ってくることになる。

=INDEX(A1:A13,10)+1

INDEX関数を最後に計算して完了。A10に入っている"1"の値が取り出され+1されることになる。

=A10+1=1+1=2

もうちょっとシンプルなやつ

単純に連番にするだけなら、以下でもできる。単純な連番なので、途中で数字を1からリセットするような動きにはならないので注意。

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