Excelの関数を使って最上位行と最終行のデータを取得する
Excelで、大量のデータがある場合、最上位行や最終行のデータを取得することがあります。
今回は、INDEX関数とMATCH関数を使って最上位行のデータを取得する方法とLOOKUP関数を使って最終行のデータを取得する方法についてサンプルアプリを使って解説します。
サンプルアプリの説明
最上位行と最終行のデータを表の上部に固定表示させています。
下記箇所には最上位行のデータを取得するための式が入っています。
下記箇所には最終行のデータを取得するための式が入っています。
他のセルについても同じ式が入ってしますので、セルをコピーすることで値を取得することができます。
各式の解説は以下の通りです。
最上位行のデータを取得する方法
Excelを使って大量のデータを扱っていると、どこからデータが始まっているのか、最初の行がどこにあるのかを確認する必要があります。また、最初の行にあるデータを取得する必要がある場合もあります。そんなときに便利なのが、INDEX関数とMATCH関数を使った最上位行のデータの取得方法です。
以下の式を使用することで、最上位行のデータを取得できます。
=INDEX(C6:C22,MATCH(1,INDEX(N(C6:C22<>""),0),0))
具体的に、この式がどのように動作するのか見ていきましょう。
- 検索対象範囲に対して、空白でないセルの位置を特定します。
空白でないセルの位置を特定するには、次の式を使用します。
N(範囲<>"")
この式は、範囲内の空白でないセルに対して「1」を、空白のセルに対して「0」を返します。 - INDEX関数とMATCH関数を使用して、空白でないセルの位置を特定します。
MATCH(1, INDEX(N(範囲<>""), 0), 0)
MATCH関数は、1と範囲内の値を比較し、最初に一致した位置を返します。
INDEX関数は、N(範囲<>"")の結果を行列形式で返し、MATCH関数が特定する位置を検索するために使用されます。 - INDEX関数を使用して、最上位行のデータを取得します。
INDEX(範囲, 位置)
この式は、指定された範囲内で特定された位置にあるデータを取得します。上記の手順で取得した位置を使用します。
最終的に、次の式を使用して、最上位行のデータを取得します。
=INDEX(範囲, MATCH(1, INDEX(N(範囲<>""), 0), 0))
最終行のデータを取得する方法
Excelでデータ処理をする上で、最終行のデータを取得することは非常に重要な作業の一つです。
最終行のデータを取得する方法として、LOOKUP関数を使用する方法があります。
この記事では、LOOKUP関数を使用して最終行のデータを取得する方法を解説します。
最終行のデータを取得するには以下の式を使用することで取得できます。
=LOOKUP(1,1/(C6:C22<>0),C6:C22)
具体的に、この式がどのように動作するのか見ていきましょう。
- LOOKUP関数は、検索値として「1」を指定しています。この「1」という値は、検索範囲である「1/(C6:C22<>0)」と比較されます。
- 次に、検索範囲「1/(C6:C22<>0)」について説明します。
この範囲は、式「1/(C6:C22<>0)」によって計算されます。この式では、セルC6からC22までの範囲に対して、空ではないセルを「1」、空のセルを「0」として変換しています。そして、それらの値を「1」で割ります。つまり、空でないセルには1が、空のセルには無限大が割り当てられます。こうすることで、空でないセルには1が割り当てられます。 - LOOKUP関数は、検索範囲「1/(C6:C22<>0)」に対して、検査範囲の最後から検索を開始し、最初に見つかった値を返します。今回は「1」を検索します。
- 最後に、結果範囲「C6:C22」から、検索範囲で見つかった最後の「1」に対応するセルの値を取得します。これが、最終行のデータです。
以上のように、INDEX関数とMATCH関数、またはLOOKUP関数を使用することで、Excelで最上位行や最終行のデータを取得することができます。これらの関数を駆使して、大量のデータから必要な情報を迅速かつ正確に取得できるようになります。
Excel2021、および最新のMicrosoft365で新たに使用できるようになった「XLOOKUP関数」を使っても最上位行と最終行のデータを取得することができます。XLOOKUP関数の使用方法については以下のサイトをご覧ください。
https://scodebank.com/?p=792