列または行の最後の値を検索する – Excel

このチュートリアルでは、Excelで列または行の最後の値を検索する方法について説明します。

列の最後の値

LOOKUP関数を使用すると、列の最後の非空白セルを見つけることができます。

=LOOKUP(2,1/(B:B<>""),B:B)

この数式を見てみましょう。

数式 B:B<>”” の部分は、 列Bの各セルが空白（FALSE）かどうかをテストし、{FALSE, TRUE, TRUE, …}のような True と False の値を含む配列を返します。

=LOOKUP(2,1/({FALSE;TRUE;TRUE;TRUE;FALSE;...),B:B)

これらのブール値は0または1に変換され、1を割り算するために使用されます。

=LOOKUP(2,{#DIV/0!;1;1;1;1;#DIV/0!;,B:B)

これはLOOKUP関数のためのlookup_vectorです。この場合、lookup_valueは2ですが、lookup_vectorの最大の値は1なので、LOOKUP関数は配列の最後の1にマッチし、result_vectorに対応する値を返します。

もし、列に数値しかなく、データが1行目から始まり、データの範囲が連続的であることが確かであれば、INDEX関数とCOUNT関数でもう少し簡単な数式を使うことができます。

=INDEX(B:B,COUNT(B:B))

COUNT関数は連続した範囲のデータで満たされたセルの数（4）を返し、INDEX関数はこのように対応する行のセルの値（4番目）を与えます。 データ範囲に数値と非数値が混在している場合や、空白セルがある場合に起こりうるエラーを回避するには、LOOKUP関数とISBLANK関数、NOT関数を一緒に使用するとよいでしょう。

=LOOKUP(2,1/(NOT(ISBLANK(B:))),:B)

ISBLANK関数は、1と0に対応するTrueとFalseの値を含む配列を返します。NOT関数は、True（つまり1）をFalseに、False（つまり0）をTrueに変更します。この結果の配列を反転すると（この配列で1を割ると）、再び#DIV/0!エラーと1を含む結果の配列が得られ、これはLOOKUP関数のルックアップ配列（lookup_vector）として使用することができます。LOOKUP関数の機能は、最初の例と同じで、ルックアップ配列の最後の1の位置にある結果ベクトルの値を返します。 最後のエントリのある行番号を返す必要があるときは、最初の例で使った式をresult_vectorのROW関数と一緒に修正すればよいのです。

=LOOKUP(2,1/(B:B<>""),ROW(B:B)) 

行の最後の値

数値データで満たされた行の、空でない最後のセルの値を得るには、似たような方法を使いますが、OFFSET関数と MATCH関数、MAX関数という異なる関数を使うことができます。

=OFFSET(参照範囲, 行, 列)

=OFFSET(B2,0,MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)-1)

この式がどのように機能するか見てみましょう。

MATCH関数

MATCH関数を使って、B2から始まる2行目の全値の最大値＋1以下のセル値がいくつあるかを「数え」ます。

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)

MATCH関数のlookup_valueは、2行目の全ての値の最大値+1です。この値は明らかに2行目には存在せず、match_typeは1（lookup_value以下）に設定されているので、MATCH関数は配列内の最後の「チェックした」セルの位置、つまりB2:XFD2（XFDは新しいバージョンのExcelでは最後の列）の範囲内のデータで満たされたセルの数を返すことになります。

OFFSET関数

次に、OFFSET 関数を使って、MATCH 関数で返された位置のセルの値を取得します。

=OFFSET(B2,0,C4-1)