Excel 最終行を取得する9つの方法【空白行あり】

Excel 最終行を取得する9つの方法【空白行あり】 Excel

はじめに：そのデータ、本当に「最後」を見ていますか？

Excelで家計簿やお小遣い帳をつけている皆さん！

「一番最後のデータ（最新のデータとは限りません！）」だけを、別の場所にサッと表示させたいと思ったこと、ありませんか？

Excelの説明画像

「え、そんなの簡単じゃない？」と思ったそこのあなた、油断は禁物です！

もし、リストの途中に空白行があったら？
もし、日付が昇順に並んでいなかったら？

単純なCOUNTA関数やMAX関数では、思わぬ罠にはまってしまうかもしれません。

今回の記事は、そんな実務でよくある落とし穴を華麗にかわし、「物理的なデータの最終行」を確実に捉えるための、9種類もの異なる数式アプローチを探求する冒険です！

王道のINDEX&MATCHから、LOOKUP関数の古典的テクニック、そして最新の動的配列関数まで。

あなたのExcelスキルを確実にレベルアップさせる、知的な関数パズルを一緒に楽しみましょう！

【今回の前提条件】
・データは行単位で入力されており、空白行がある場合は、その行のすべての列（日付、カテゴリ、金額）が空白であるとします。
・日付、カテゴリ、金額のいずれかの列には、必ず何かしらの値が入っているものとします。（完全に空白のシートは想定しません）

本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！

準備：舞台となるリストを用意する

何事も、まずは準備から。まっさらなシートのA1セルを起点に、以下のようなデータリストを作成してください。（日付はシリアル値ではなく、”yyyy/m/d”形式で表示しています）

Excelの説明画像

このリストには、今回の冒険の鍵となる「空白行（10行目）」と「日付が昇順でない（11, 12行目）」という、2つの罠が仕掛けられています。

私たちのゴールは、これらの罠に惑わされず、物理的な最終行である12行目のデータ「2025/10/5, 食費, 1670」を、一つの数式で表示させることです。

失敗例：なぜCOUNTAではダメなのか？

「最終行なら、A列のデータ数を数えればいいんじゃない？」

と考え、COUNTA関数を使いたくなりますよね。試してみましょう。

=INDEX(A:C,COUNTA(A:A),COLUMN(A:C))

・COUNTA(A:A): A列の中で、空白ではないセルの個数を数えます。今回のリストでは、見出しを含めて「11」個です。

・COLUMN(A:C): これは{1,2,3}という列番号の配列を生成します。

・INDEX(A:C, 11, {1,2,3}): A列からC列の「11行目」の値を参照しなさい、という命令になります。

結果は…11行目の「2025/10/6, 交通費, 210」が表示されてしまいました！

Excelの説明画像

空白行(10行目)を無視してカウントしてしまったため、真の最終行(12行目)にたどり着けなかったのです。

さあ、どうすればこの問題を解決できるでしょうか？ここからが本当の冒険の始まりです！

9つのアプローチで、「真の最終行」を攻略せよ！

数式はすべて、結果を表示したいセル（例えばE5セルなど）に一つ入力するだけで、スピルして最終行の3つの値が表示されます。（ただし、アプローチ2を除く）

アプローチ1：INDEX + MATCH (数値列向け最強タッグ)

考えかた

COUNTAがダメなら、MATCH関数の「近似一致」を使いましょう！

数値が入力されている列に対して、「とてつもなく大きい数」を探させることで、その列に入力されている最後の数値の位置を特定する、有名なテクニックです。

数式と解説

=INDEX(A:C,MATCH(9^9,A:A),COLUMN(A:C))

1. MATCH(9^9,A:A): ここが心臓部！MATCH関数（第3引数省略、または1）は、「検索値以下の最大値」を探します。

9^9（9の9乗=約3.8億）という、Excelで扱えるほぼ最大級の数値を指定するのはなぜでしょう？

これは、入力される可能性のあるどんな数値よりも確実に大きい数として指定しているのです。

キーボードで「9」と「^」が近くて打ちやすいので9^9がよく使われますが、10^10など、十分大きな数値であれば何でも構いません。

この巨大な数を探させることで、MATCH関数はA列に入力されている数値の中で、一番最後に出てくる数値（今回は12行目の日付シリアル値）の位置「12」を返してくれます。

Excelの説明画像

空白行は無視されます！

2. COLUMN(A:C): これは、列番号を取得する関数です。

範囲をA:Cと指定することで、{1,2,3}という横方向の配列を生成します。

これは、INDEX関数に「1列目、2列目、3列目をすべて返しなさい」と命令するための部品です。

3. INDEX(A:C, 12, {1,2,3}): 最後に、INDEX関数が、A列からC列の「12行目」にある値を、列番号{1,2,3}に従ってすべて取り出し、スピルさせて表示します。

Excelの説明画像

【注意点】
この方法は、検索対象列に数値が入っている場合に非常に有効ですが、文字列しか入っていない列ではうまく機能しません。

アプローチ2：LOOKUP (最短コード、ただしスピル不可)

考えかた

数値・文字列を問わず、空白以外の最後のセルの位置を見つけるための、非常に短く書ける古典的なテクニックです。

ただし、残念ながらこの関数自体はスピルに対応していません。

数式と解説

E5セル: =LOOKUP(1,0/(A:A<>""),A:A)
F5セル: =LOOKUP(1,0/(A:A<>""),B:B)
G5セル: =LOOKUP(1,0/(A:A<>""),C:C)

・A:A<>"": まず、A列が空白でないかどうかを判定し、{TRUE;TRUE;...;FALSE;TRUE;TRUE}のような配列を作ります。

Excelの説明画像

・0/(...): 次に、このTRUE/FALSE配列で「0」を割ります。

0/TRUEは「0」、0/FALSEはエラー「#DIV/0!」になります。結果として、{0;0;...;#DIV/0!;0;0}という、0とエラーが混在した配列が生まれます。

Excelの説明画像

・LOOKUP(1, {0;...;#DIV/0!;0;0}, A:A): LOOKUP関数で、この特殊な配列から「1」を探します。

LOOKUPは、検索値（1）以上の値がない場合、エラーを無視して配列の中の最後の数値（今回の場合は最後の「0」）を見つけてくれます！

そして、その位置に対応する値を、第3引数の範囲（A:A, B:B, C:C）から返します。非常に巧妙なロジックですね！

Excelの説明画像

この数式は非常に便利ですが、1セルに1つの値しか返せないため、3列分表示するには、数式を3つのセルにコピーする必要があります。

アプローチ3：XLOOKUP (LOOKUPの進化系)

考えかた

アプローチ2のLOOKUP関数のロジックを、最新のXLOOKUP関数で再現します。

XLOOKUPなら、戻り値に複数列を指定できるので、スピル表示が可能です！oshin

数式と解説

=XLOOKUP(1,0/(A:A<>""),A:C,,-1,-1)

・0/(A:A<>""): LOOKUPと同じ方法で、{0;0;...;#DIV/0!;0;0}という検索範囲を作ります。

・XLOOKUP(1, ..., A:C, , -1, -1): XLOOKUP関数で「1」を探します。
　・第3引数A:C: 戻り値としてA列からC列の値を返すように指定します。
　・第5引数-1: 検索モードを「近似一致（検索値以下の最大値）」にします。（LOOKUPの動きを再現）
　・第6引数-1: 検索方向を「末尾から先頭へ」にします。これにより、最後の「0」を確実に見つけ出すことができます。

Excelの説明画像

アプローチ4：XLOOKUP + ワイルドカード (文字列列向け)

考えかた

もし、確実に文字列が入っている列があるなら、XLOOKUPのワイルドカード検索機能を使うのが非常にスマートです。

（例えば今回のB列「カテゴリ」）

数式と解説

=XLOOKUP("*",B:B,A:C,,2,-1)

・XLOOKUP("*", B:B, A:C, ...): 「検索値」にワイルドカード文字「*」（任意の文字列を表す）を指定し、B列（カテゴリ列）を検索します。

Excelの説明画像

・..., , 2, -1): 第5引数「検索モード」に「2」を指定することで、ワイルドカード検索を有効にします。

そして第6引数「検索方向」を「-1」（末尾から先頭へ）にすることで、一番最後の文字列が入っている行を見つけ出します。

Excelの説明画像

アプローチ5：VLOOKUP (数値列向け・近似一致)

考えかた

みんな大好きVLOOKUP関数でも、INDEX&MATCHと似た考え方で最終行を探せます。

ただし、検索列が数値である必要があります。

数式と解説

=VLOOKUP(9^9,A:C,COLUMN(A:C))

・VLOOKUP(9^9, A:C, ...): VLOOKUPの検索方法をTRUE（または省略）にすると、「検索値以下の最大値」を探す近似一致モードになります。

9^9という巨大な数を指定することで、A列の最後の数値がある行（12行目）を特定します。

Excelの説明画像

・..., COLUMN(A:C)): 第3引数「列番号」にCOLUMN(A:C)（つまり{1,2,3}）を指定することで、見つかった行の1列目から3列目までの値をすべて返すように命令し、スピル表示を実現します。

Excelの説明画像

アプローチ6：HLOOKUP (まさかの水平検索！？)

考えかた

普通は使わないでしょうが、水平検索のHLOOKUP関数でも、MATCH関数と組み合わせれば可能です。

意地でも最終行を見つけてやる！という執念を感じる数式です。

数式と解説

=HLOOKUP(A1:C1,A:C,MATCH(9^9,A:A),FALSE)

1. MATCH(9^9,A:A): まず、アプローチ1と同じ方法で、最後の数値がある行番号「12」を特定します。

Excelの説明画像

2. HLOOKUP(A1:C1, A:C, 12, FALSE): 次に、HLOOKUP関数を使います。
　・検索値A1:C1: 見出し行（”日付”, “カテゴリ”, “金額”）を探します。
　・範囲A:C: A列からC列全体を検索範囲とします。
　・行番号12: ここで先ほどMATCHで計算した「12」を指定します。これにより、「見出しが見つかった列の、12行目の値を返しなさい」という命令になります。
　・検索方法FALSE: 見出しを「完全一致」で探します。

Excelの説明画像

アプローチ7：TAKE + FILTER (新関数で直感的に)

考えかた

最新の動的配列関数を使えば、非常に直感的で分かりやすい数式になります。

まず空白行をFILTERで除去し、その結果の最後の行をTAKEで取り出す、という考え方です。

数式と解説

=TAKE(FILTER(A:C,A:A<>""),-1)

・FILTER(A:C,A:A<>""): まず、FILTER関数が、A列からC列の範囲を、「A列が空白ではない」という条件で絞り込みます。これにより、10行目の空白行が除去されたリストがメモリ上に生成されます。

Excelの説明画像

・TAKE(..., -1): 次に、TAKE関数が、この絞り込まれたリストの中から、「-1」、つまり「末尾から1行」だけを取り出します。これで、物理的な最終行のデータが得られます。

Excelの説明画像

アプローチ8：OFFSET + MAX (配列計算で位置特定)

考えかた

検索系の関数を使わずに、配列計算で最後の行番号を特定し、OFFSET関数でその行を参照する方法です。

数式と解説

=OFFSET(A1,MAX((A:A<>"")*ROW(A:A))-1,,,3)

・(A:A<>"")*ROW(A:A): A:A<>""で空白でない行がTRUE(1)になる配列を作り、それに各行の行番号ROW(A:A)を掛け合わせます。すると、データがある行の行番号だけが残り、空白行は0になります。

Excelの説明画像

・MAX(...): この配列の最大値を取ることで、最後のデータがある行番号「12」を特定します。

・OFFSET(A1, 12-1, , , 3): OFFSET関数はOFFSET(基準セル, 行移動数, 列移動数, [高さ], [幅])のように使います。

基準のA1セルから、「12-1=11」行下に移動し（12行目を指す）、高さは省略（1行）、幅を「3」列に指定することで、A12:C12の範囲への参照を返します。

Excelの説明画像

アプローチ9：INDIRECT + LET (文字列結合で参照作成)

考えかた

アプローチ8と考え方は似ていますが、最後の行参照をOFFSETではなく、INDIRECT関数を使って実現します。最後の行番号を計算し、”A12:C12″のような文字列を作り出して参照します。

数式と解説

=LET(a,MAX((A:A<>"")*ROW(A:A)),INDIRECT("A"&a&":C"&a))

・LET(a,MAX(...), ...): まず、LET関数を使って、アプローチ8と同じ方法で計算した最後の行番号「12」に、「a」という名前をつけます。

・("A"&a&":C"&a): 次に、文字列結合を使って"A" & 12 & ":C" & 12、つまり"A12:C12"という文字列を生成します。

Excelの説明画像

・INDIRECT(...): 最後に、INDIRECT関数が、この文字列を本物のセル参照に変換し、A12:C12の値を返します。

Excelの説明画像

まとめ：最終行への道は、一つじゃない！

「最後のデータ行を表示する」というゴールに対して、空白行や並び順の罠をかいくぐる、9つもの異なるアプローチが存在することに、驚かれたのではないでしょうか。

実務で使うなら、データの種類に応じて、
アプローチ1(INDEX/MATCH)
アプローチ4(XLOOKUP+ワイルドカード)
アプローチ7(TAKE/FILTER)
あたりが、堅牢で分かりやすいでしょう。

特に最新のExcelを使っているなら、TAKE/FILTERの直感性は魅力的です。

しかし、他の関数パズルたちも、決して無駄ではありません。

LOOKUPの特殊な挙動、配列計算による位置特定、OFFSETやINDIRECTの柔軟性…。

これらの知識は、あなたの「Excel問題解決能力」を確実に引き上げてくれます。

どんな状況でも、最適な数式でスマートに最終行を捉えられる、真のExcel使いを目指してくださいね！

タイトルとURLをコピーしました