Excelの関数を使って最上位行と最終行のデータを取得する

Excelで、大量のデータがある場合、最上位行や最終行のデータを取得することがあります。
今回は、INDEX関数とMATCH関数を使って最上位行のデータを取得する方法とLOOKUP関数を使って最終行のデータを取得する方法についてサンプルアプリを使って解説します。

サンプルアプリの説明

最上位行と最終行のデータを表の上部に固定表示させています。

下記箇所には最上位行のデータを取得するための式が入っています。

下記箇所には最終行のデータを取得するための式が入っています。

他のセルについても同じ式が入ってしますので、セルをコピーすることで値を取得することができます。
各式の解説は以下の通りです。

最上位行のデータを取得する方法

Excelを使って大量のデータを扱っていると、どこからデータが始まっているのか、最初の行がどこにあるのかを確認する必要があります。また、最初の行にあるデータを取得する必要がある場合もあります。そんなときに便利なのが、INDEX関数とMATCH関数を使った最上位行のデータの取得方法です。

以下の式を使用することで、最上位行のデータを取得できます。
 =INDEX(C6:C22,MATCH(1,INDEX(N(C6:C22<>""),0),0))

具体的に、この式がどのように動作するのか見ていきましょう。

  1. 検索対象範囲に対して、空白でないセルの位置を特定します。
    空白でないセルの位置を特定するには、次の式を使用します。
    N(範囲<>"")
    この式は、範囲内の空白でないセルに対して「1」を、空白のセルに対して「0」を返します。
  2. INDEX関数とMATCH関数を使用して、空白でないセルの位置を特定します。
    MATCH(1, INDEX(N(範囲<>""), 0), 0)
    MATCH関数は、1と範囲内の値を比較し、最初に一致した位置を返します。
    INDEX関数は、N(範囲<>"")の結果を行列形式で返し、MATCH関数が特定する位置を検索するために使用されます。
  3. INDEX関数を使用して、最上位行のデータを取得します。
    INDEX(範囲, 位置)
    この式は、指定された範囲内で特定された位置にあるデータを取得します。上記の手順で取得した位置を使用します。

最終的に、次の式を使用して、最上位行のデータを取得します。
=INDEX(範囲, MATCH(1, INDEX(N(範囲<>""), 0), 0))

最終行のデータを取得する方法

Excelでデータ処理をする上で、最終行のデータを取得することは非常に重要な作業の一つです。
最終行のデータを取得する方法として、LOOKUP関数を使用する方法があります。
この記事では、LOOKUP関数を使用して最終行のデータを取得する方法を解説します。

最終行のデータを取得するには以下の式を使用することで取得できます。
=LOOKUP(1,1/(C6:C22<>0),C6:C22)

具体的に、この式がどのように動作するのか見ていきましょう。

  1. LOOKUP関数は、検索値として「1」を指定しています。この「1」という値は、検索範囲である「1/(C6:C22<>0)」と比較されます。
  2. 次に、検索範囲「1/(C6:C22<>0)」について説明します。
    この範囲は、式「1/(C6:C22<>0)」によって計算されます。この式では、セルC6からC22までの範囲に対して、空ではないセルを「1」、空のセルを「0」として変換しています。そして、それらの値を「1」で割ります。つまり、空でないセルには1が、空のセルには無限大が割り当てられます。こうすることで、空でないセルには1が割り当てられます。
  3. LOOKUP関数は、検索範囲「1/(C6:C22<>0)」に対して、検査範囲の最後から検索を開始し、最初に見つかった値を返します。今回は「1」を検索します。
  4. 最後に、結果範囲「C6:C22」から、検索範囲で見つかった最後の「1」に対応するセルの値を取得します。これが、最終行のデータです。

以上のように、INDEX関数とMATCH関数、またはLOOKUP関数を使用することで、Excelで最上位行や最終行のデータを取得することができます。これらの関数を駆使して、大量のデータから必要な情報を迅速かつ正確に取得できるようになります。

Excel2021、および最新のMicrosoft365で新たに使用できるようになった「XLOOKUP関数」を使っても最上位行と最終行のデータを取得することができます。XLOOKUP関数の使用方法については以下のサイトをご覧ください。
  https://scodebank.com/?p=792

LookUp,関数Excel

Posted by CoderM