2015年3月7日土曜日

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