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