毎月増えていく売上一覧で、一番下の値を数式で表示したい。そう思って Excel で INDEX と COUNTA を組み合わせたのに、なぜか1つ上の値が出てしまう。そんな経験はありませんか？
原因の多くは、列の途中にある空白セルや、数式が返す「””（空の文字列）」です。COUNTA は空白セルを数えず、”” を返すセルは数えるため、見た目の行数とズレます。気づかずに使い続けると、月次報告に古い数字が載ることも。
この記事では、Excel の関数だけで最終行・最終値・最終列を取得する方法を、1つのサンプル表で比べていきます。空白や “” があっても崩れない式と、自分の Excel で使える式の選び方がわかります。VBA は使いません。
結論｜最終行・最終値・最終列の取得、状況別のおすすめ式早見表
先に結論です。Excel で最終行を取る式は、データの状態と Excel のバージョンで選び分けます。空白がない列なら COUNTA、空白や “” が混ざるなら LOOKUP か FILTER の組み合わせを選びましょう。
対応バージョンの目安
対応バージョン: COUNTA・INDEX・MATCH・LOOKUP・MAX・ROW は Excel 2016 以降のどの版でも使えます。FILTER と XLOOKUP は Microsoft 365・Excel 2024・Excel 2021 が対象。TAKE・CHOOSEROWS・TOCOL の公式の対応一覧は Microsoft 365 と Excel 2024 のみで、Excel 2021 は含まれていません。
| 関数 | Excel 2016・2019 | Excel 2021 | Excel 2024・Microsoft 365 |
|---|---|---|---|
| COUNTA・INDEX・MATCH・LOOKUP・MAX・ROW | ○ | ○ | ○ |
| FILTER・XLOOKUP | × | ○ | ○ |
| TAKE・CHOOSEROWS・TOCOL | × | 公式の対応一覧に含まれない | ○ |
古い版で FILTER や XLOOKUP を使うと #NAME? エラーになります。版ごとのエラーの見分け方は XLOOKUP・FILTERが#NAME?になる原因とバージョンの違い で詳しく解説しています。
なお、Office 2016 と Office 2019 は 2025年10月14日に延長サポートが終わっています。とはいえ、共有相手が旧版で開く職場もまだあるでしょう。その場合は、全バージョンで動く式を選んでおくと安心です。
まず押さえる早見表
やりたいことから式を選べるよう、早見表にまとめました。範囲や列は、この後のサンプル表に合わせています。
| やりたいこと | データの状態 | おすすめの式 | 対応版 |
|---|---|---|---|
| 最終行の行番号 | 空白なし | =COUNTA(A:A) | 全版 |
| 最終行の値 | 空白なし | =INDEX(A:A,COUNTA(A:A)) | 全版 |
| 最終行の値 | 空白・”” あり | =LOOKUP(2,1/(D1:D100<>""),D1:D100) | 全版 |
| 最後の数値 | 数値の列 | =INDEX(C:C,MATCH(9^9,C:C)) | 全版 |
| 最終行の値 | 空白・”” あり | =TAKE(FILTER(D1:D100,D1:D100<>""),-1) | 365・2024 |
| 最終列の値 | 横方向のデータ | =LOOKUP(2,1/(A2:Z2<>""),A2:Z2) | 全版 |
迷ったら、LOOKUP の式を覚えておけば大半の表に対応できます。ここからは、それぞれの式がなぜそう動くのかを見ていきましょう。
この記事で使うサンプル表
記事全体で、次の表（シート「売上」）を使い回します。B4 は未入力の空白セル、C7 は文字列の「未定」。
| 行 | A列：受付No | B列：商品コード | C列：売上 | D列：判定（数式） |
|---|---|---|---|---|
| 1 | 受付No | 商品コード | 売上 | 判定 |
| 2 | 1 | A-101 | 1200 | 達成 |
| 3 | 2 | A-102 | 800 | 未達 |
| 4 | 3 | （空白） | 600 | 未達 |
| 5 | 4 | B-201 | 950 | 未達 |
| 6 | 5 | B-202 | 1500 | 達成 |
| 7 | 6 | C-301 | 未定 | “”（空に見える） |
| 8〜10 | （空白） | （空白） | （空白） | “”（空に見える） |
D列には、D2 から D10 まで次の数式が入っています。C列が数値なら判定を表示し、それ以外なら “” を返す式。
=IF(ISNUMBER(C2),IF(C2>=1000,"達成","未達"),"")D7 から D10 は空に見えますが、中身は数式です。来月の入力に備えて、数式だけ先に下までコピーしてある状態ですね。結果を確かめる式は、F列など表の外のセルに入力してください。
空白行がない表の最終行・最終値（COUNTA / INDEX+COUNTA）
まずは基本形から。途中に空白がない列なら、COUNTA と INDEX の組み合わせで最終行と最終値が取れます。サンプル表では、A列（受付No）が空白のない列にあたります。
最終行の行番号を出す
COUNTA は、空白でないセルの数を数える関数です。見出しが1行目にあり、データが2行目から隙間なく続く列なら、数えた個数がそのまま最終行の行番号になります。
=COUNTA(A:A)結果は 7 です。A1 の見出しと、A2〜A7 の6件を合わせて7個あるためですね。データの件数だけが欲しいなら、見出しの分を引いて =COUNTA(A:A)-1（結果は 6）とします。
表の上にタイトル行などがある場合は要注意。見出しが3行目にあり、1〜2行目が完全に空なら =COUNTA(A:A)+2 のように、空いている行数を足して調整します。COUNTA の基本は COUNTA関数の使い方 にまとめています。
最終行の値を出す
行番号がわかれば、INDEX 関数（範囲の中から、指定した位置のセルを取り出す関数）で値を取り出せます。
=INDEX(A:A,COUNTA(A:A))INDEX(A:A,7) と同じ意味になり、A7 の 6 が返ります。ここで覚えておきたいのが、「数える列」と「取り出す列」を分けるテクニック。
=INDEX(B:B,COUNTA(A:A))この式は、空白のない A列で行番号を決めて、B列の値を取り出します。結果は B7 の「C-301」です。B列には B4 の空白がありますが、行番号を A列から取るので影響を受けません。INDEX の詳しい使い方は INDEX関数の使い方 をご覧ください。
空白があるとズレる理由
では、B列そのものを数えるとどうなるでしょうか。
=INDEX(B:B,COUNTA(B:B))COUNTA(B:B) は 6 になります。B1〜B7 のうち、B4 だけが空白で数えられないためです。その結果、B6 の「B-202」が返り、本当の最終値「C-301」より1つ上にズレます。
逆方向にズレるのが D列。Microsoft の公式ヘルプでは、COUNTA は「空の文字列（””）」を含むセルも数えると説明されています。D1〜D10 はすべて数式が入っているので、COUNTA(D:D) は 10 になります。
=INDEX(D:D,COUNTA(D:D))この式が返すのは D10、つまり空に見えるセル。本当の最終値は D6 の「達成」なので、今度は4行下にズレてしまいました。つまり、空白セルがあると上に、”” の数式があると下にズレます。
COUNTA を使うなら、必ず「空白も “” もない列」で数えましょう。そうした列がない表では、次の章の方法に切り替えます。
空白行がある表の最終行・最終値（MAX+ROW / MATCH / LOOKUP）
列の途中に空白や “” がある場合は、セルを数えるのではなく「中身があるセルの位置」を直接探します。代表的な4つの方法を、サンプル表で順に試してみましょう。
MAX+ROW（旧版の入力注意つき）
1つ目は、中身があるセルの行番号のうち、一番大きいものを取る方法です。
=MAX((B1:B100<>"")*ROW(B1:B100))結果は 7。B1:B100<>"" は、各セルが空でなければ TRUE、空なら FALSE を返します。TRUE は 1、FALSE は 0 として ROW（行番号を返す関数）の結果に掛けられ、中身がある行だけ行番号が残る仕組み。
<>"" という条件は、空白セルも “” を返す数式も「空」とみなします。そのため D列で使うと、”” の行は除かれます。
=MAX((D1:D100<>"")*ROW(D1:D100))結果は 6 となり、「達成」がある D6 を正しく指します。値が欲しいときは =INDEX(B:B,MAX((B1:B100<>"")*ROW(B1:B100))) のように INDEX で包みましょう。この式は「C-301」を返します。
注意したいのは、この式が配列数式（複数のセルをまとめて計算する数式）だという点。Microsoft 365 では Enter だけで確定できます。Excel 2019 以前では、従来の配列数式として Ctrl+Shift+Enter が必要な場合があります。
スピルは、1つの数式の結果が隣のセルにあふれて表示される仕組みです。版ごとの違いは UNIQUE・FILTER・SORT・SEQUENCEで学ぶスピル で解説しています。旧版と共有するなら、後述の LOOKUP 版が扱いやすいでしょう。
MATCH(9^9)：数値の最終値
2つ目は、MATCH 関数（範囲の中で値が何番目にあるかを返す関数）を使う方法です。数値の列で、最後の数値を探すときに向いています。
=MATCH(9^9,C:C)結果は 6 です。C7 は文字列の「未定」なので読み飛ばされ、最後の数値 1500 がある6行目を指します。値を取るなら、INDEX と組み合わせましょう。
=INDEX(C:C,MATCH(9^9,C:C))結果は 1500 になります。「最後の値」ではなく「最後の数値」が返る点に注意してください。最終行に「未定」と入っている今回の表では、最終値（未定）と答えが食い違います。
照合の種類を省略した MATCH は、「検査値以下の最大値」を探します。公式ヘルプでは、この型は範囲が昇順に並んでいることが前提です。並んでいない列でも最後の数値が返るのは広く使われている書き方ですが、ヘルプが保証する動きではありません。
9^9 は 387,420,489 のこと。「どの数値よりも大きい数」を短く書くために使います。ただし、データに 387,420,489 より大きい数値があると、正しい位置を返しません。億単位の金額を扱う列なら 10^15 のように、データの上限より大きい数に変えましょう。
日付はシリアル値（1900年1月1日を1とする通し番号）という数値なので、日付の列にも使えます。一方、B列や D列のように数値が1つもない列では、#N/A エラーになります。MATCH の基本は MATCH関数の使い方 を参考にしてください。
MATCH(“ZZZ”)：文字列の最終値
3つ目は、数値の代わりに文字列を探す方法。英数字のコードが並ぶ列で、最後の文字列を取りたいときに使います。
=MATCH("ZZZ",B2:B100)+1結果は 7 です。範囲を B2 から始めたので、MATCH が返す「6番目」に 1 を足して行番号に直しています。値は次の式で取れます。
=INDEX(B2:B100,MATCH("ZZZ",B2:B100))結果は「C-301」。サンプルのコードは A-101 から C-301 まで昇順に並び、どれも “ZZZ” より前に来ます。そのため、”ZZZ” 以下で最大の値として最後のコードが選ばれます。
ただし、この方法は日本語の文字列には向きません。ひらがなや漢字が “ZZZ” より前に並ぶとは限らないためです。範囲から見出し行（商品コード）を外したのも、同じ理由。
「達成」「未達」のような日本語の列なら、次の LOOKUP 版を使いましょう。
LOOKUP(2,1/…)
4つ目が、空白・””・数値と文字列の混在のどれにも強い LOOKUP 版です。LOOKUP 関数（範囲から値を探し、別の範囲の同じ位置の値を返す関数）は全バージョンで使えるので、迷ったらこれを選びましょう。
=LOOKUP(2,1/(D1:D100<>""),D1:D100)結果は D6 の「達成」です。行番号が欲しいなら、3つ目の引数を ROW にします。
=LOOKUP(2,1/(D1:D100<>""),ROW(D1:D100))こちらの結果は 6。仕組みを順に見ていきます。
まず 1/(D1:D100<>"") で、中身があるセルは 1/TRUE＝1、空のセルは 1/FALSE＝#DIV/0! になります。これで、1 とエラーが並んだ配列ができました。
次に LOOKUP が検査値 2 を探します。公式ヘルプでは、検査値がどの値よりも大きいとき「検査値以下で最大の値」に一致するとされています。配列の中で 2 以下の最大値は 1 なので、最後の 1 が選ばれるという流れ。
途中の #DIV/0! を読み飛ばす点は、公式ヘルプに明記がありません。広く使われている書き方で、経験的に知られた挙動です。あとは、最後の 1 と同じ位置の値を3つ目の範囲から返す仕組み。
LOOKUP は配列をそのまま扱える関数なので、旧版でも通常の Enter で動くのが一般的です。<> や 1/、TRUE を数値に変える -- などの記号の意味は Excel数式の記号の意味一覧 にまとめています。LOOKUP 自体の使い方は LOOKUP関数の使い方 をどうぞ。
数式の””・数値と文字列の混在があるとどうなる？方法別の比較表
ここまでの式を、サンプル表の4列すべてで並べてみます。どの式が、どんなデータで崩れるのかを一覧で確認しましょう。
同じサンプル表で比べた結果
各列の「正解」は、最後に中身がある行です。A〜C列は7行目、D列は “” を除いた6行目になります。式の範囲は列に合わせて読み替えてください（例: B列なら B1:B100）。
| 方法 | A列（空白なし） | B列（途中に空白） | C列（末尾が文字列） | D列（”” を含む） |
|---|---|---|---|---|
| 正解の行 | 7行目 | 7行目 | 7行目 | 6行目 |
| COUNTA（行番号） | ○ 7 | × 6 | ○ 7 | × 10 |
| INDEX+COUNTA（値） | ○ 6 | × B-202 | ○ 未定 | × “”（空に見える） |
| MAX+ROW（行番号） | ○ 7 | ○ 7 | ○ 7 | ○ 6 |
| MATCH(9^9)（行番号） | ○ 7 | × #N/A | △ 6（最後の数値） | × #N/A |
| MATCH(“ZZZ”)+1（2行目から・行番号） | × #N/A | ○ 7 | 対象外（日本語） | 対象外（日本語） |
| LOOKUP(2,1/…)（値） | ○ 6 | ○ C-301 | ○ 未定 | ○ 達成 |
| TAKE+FILTER（値） | ○ 6 | ○ C-301 | ○ 未定 | ○ 達成 |
| TAKE+TOCOL（値） | ○ 6 | ○ C-301 | ○ 未定 | × “”（空に見える） |
A列で MATCH(“ZZZ”) が #N/A になるのは、A2:A100 に文字列が1つもないためです。C列の MATCH(9^9) は「未定」を飛ばして1500の行を返すので、△としました。TAKE を使う2つの式は、次の章で詳しく解説します。
データの状態別の向き・不向き
表から読み取れるポイントを、データの状態ごとに整理します。
| 方法 | 苦手なデータ | 対応版 | ひとこと |
|---|---|---|---|
| COUNTA / INDEX+COUNTA | 空白・”” | 全版 | 空白のない列で数えるなら最速で簡単 |
| MAX+ROW | 特になし | 全版（旧版は確定方法に注意） | 行番号を出したいとき向き |
| MATCH(9^9) | 数値がない列 | 全版 | 「最後の数値」を取る専用 |
| MATCH(“ZZZ”) | 日本語・数値だけの列 | 全版 | 英数字コードの列向き |
| LOOKUP(2,1/…) | 特になし | 全版 | 値も行番号も取れる万能型 |
| TAKE+FILTER | 特になし | 365・2024 | 読みやすく、複数行にも拡張できる |
“” を返す数式がある列で頼れるのは、<>"" で判定する MAX+ROW・LOOKUP・TAKE+FILTER の3つ。COUNTA や、空白セルだけを除く TOCOL は “” を中身ありとして扱うため、ズレの原因になります。
数値と文字列が混在する列では、「最後の値」と「最後の数値」のどちらが欲しいかで式が変わります。最後の値なら LOOKUP、最後の数値なら MATCH(9^9) と覚えておきましょう。
全列参照(A:A)の負荷
この記事では、COUNTA や MATCH には A:A のような列全体の参照を使いました。一方、MAX+ROW や LOOKUP、FILTER では A1:A100 のように範囲を区切っています。
Microsoft の計算パフォーマンスの公式記事によると、計算時間は使用中のセル数に比例し、未使用のセルは無視されます。ただし、これは SUM や SUMIF を例にした説明。配列数式については、範囲を必要な大きさに絞るよう推奨されています。
そこで、配列として計算する式は、データが増える見込みに余裕を持たせた範囲にするのがおすすめです。たとえば今100行の表なら A1:A1000 のように指定します。範囲の外まで増えると最終行を取りこぼすので、余裕はしっかり見ておきましょう。
最終列の取得と、TAKE関数によるモダン版（Microsoft 365）
ここからは横方向のデータと、Microsoft 365 で使える新しい書き方です。最終列も、考え方は最終行と変わりません。
最終列の列番号と値
横方向の例として、別シート「月別」に次の表を用意します。D2 は6月分が未集計のため空白です。数式は4行目以降のセルに入力してください。
| 行 | A列 | B列 | C列 | D列 | E列 |
|---|---|---|---|---|---|
| 1 | 月 | 4月分 | 5月分 | 6月分 | 7月分 |
| 2 | 件数 | 32 | 28 | （空白） | 41 |
COUNTA で数えると、2行目は A2・B2・C2・E2 の4個。=INDEX(2:2,COUNTA(2:2)) は4列目の D2（空白）を指し、0 が表示されてしまいます。途中に空白があれば、横方向でも縦と同じようにズレるということ。
空白に強い式なら、正しく5列目（E列）を取れます。列番号を求める式は次の3つです。
=MATCH(9^9,2:2)
=LOOKUP(2,1/(A2:Z2<>""),COLUMN(A2:Z2))
=MAX((A2:Z2<>"")*COLUMN(A2:Z2))3つとも結果は 5。ROW を COLUMN（列番号を返す関数）に置き換えるだけで、縦の式がそのまま横で使えます。値と、その列の見出しは次の式で取れます。
=LOOKUP(2,1/(A2:Z2<>""),A2:Z2)
=INDEX(1:1,MATCH(9^9,2:2))1つ目の結果は 41、2つ目は「7月分」です。最新月の見出しと件数を並べて表示すれば、毎月の更新が数式だけで済みます。マクロで最終列を扱いたい場合は、後半の判断フローを参考にしてください。
TAKE+FILTER/TOCOLの1行記述
Microsoft 365 か Excel 2024 なら、TAKE 関数で読みやすく書けます。TAKE は =TAKE(配列,行数,[列数]) の形で、配列の先頭や末尾から指定した数だけ取り出す関数です。行数をマイナスにすると末尾から数えます。
=TAKE(FILTER(D1:D100,D1:D100<>""),-1)結果は「達成」。FILTER 関数（条件に合うデータだけを抜き出す関数）で “” と空白を除き、残った最後の1行を TAKE で取り出す流れ。-1 を -3 にすれば、最後の3件をまとめて表示することも可能です。
注意点として、TAKE の範囲そのものにデータの端を判断する力はありません。=TAKE(D1:D100,-1) は空白の D100 を取り出すだけで、最終値にはなりません。必ず FILTER などで中身のあるセルに絞ってから使いましょう。
FILTER の代わりに TOCOL 関数（範囲を1列に並べ直す関数）を使う書き方もあります。第2引数の 1 は「空白セルを無視する」指定です。
=TAKE(TOCOL(B1:B100,1),-1)B列なら B4 の空白が除かれ、結果は「C-301」。
ただし、”” を返す数式セルは空白セルではないため除かれません。D列で =TAKE(TOCOL(D1:D100,1),-1) とすると、D10 の “” が返ってしまいます。数式の列では FILTER 版を選びましょう。
横方向なら、列数の引数に -1 を指定します。=TAKE(FILTER(A2:Z2,A2:Z2<>""),,-1) の結果は 41。行数を省略すると、すべての行が対象になります。
TAKE の行数や列数に 0 を指定すると、#CALC! エラーが返ります。FILTER で条件に合うデータが0件のときも #CALC! になります。気になる場合は、FILTER の3つ目の引数で0件のときの表示を決めておきましょう。
たとえば =TAKE(FILTER(D1:D100,D1:D100<>"","なし"),-1) なら、0件のときに「なし」と表示されます。
TAKE と FILTER の詳細は TAKE関数の使い方 と FILTER関数の使い方 で解説しています。TAKE と逆に、先頭や末尾を除く DROP関数 もあわせてチェックしておきましょう。
Excel 2021・2019以前で使う場合
冒頭の表のとおり、Excel 2021 では FILTER と XLOOKUP が使えますが、TAKE は公式の対応一覧に含まれません。同じ「新しい関数」でも扱いが違うので気をつけてください。
Excel 2021 で同じことをするなら、XLOOKUP 関数の検索方向を逆にする方法があります。最後の引数の -1 は「末尾から先頭へ検索する」指定です。
=XLOOKUP(TRUE,D1:D100<>"",D1:D100,,0,-1)中身があるセル（TRUE）を下から探すので、結果は「達成」です。XLOOKUP は Web 版の Excel でも使えます。詳しくは XLOOKUP関数の使い方 をご覧ください。
Excel 2019 以前の版では、FILTER も XLOOKUP も使えません。この場合は、前の章の LOOKUP 版がいちばん確実です。365 で作ったブックを旧版の人と共有するときも、LOOKUP 版にしておくとトラブルを防げます。
使い分け判断フロー（VBA版との選び方）と応用・FAQ
最後に、どの式を使うかの判断フローと、実務でよく使う応用例を紹介します。よくある疑問にも答えていきますね。
どれを使う？判断フロー
次の順に質問に答えていくと、使う式が決まります。
Q1. 最終行を使って、行のコピーや追記まで自動化したい？
├ はい → VBA（マクロ）版へ
└ いいえ → Q2
Q2. Microsoft 365 か Excel 2024 を使っている？
├ はい → =TAKE(FILTER(範囲,範囲<>""),-1)
└ いいえ → Q3
Q3. 列の途中に空白セルや "" を返す数式がある？
├ ない → =INDEX(列,COUNTA(列))
└ ある → Q4
Q4. 欲しいのは「最後の数値」？
├ はい → =INDEX(列,MATCH(9^9,列))
└ いいえ → =LOOKUP(2,1/(範囲<>""),範囲)関数は「表示する」ことは得意ですが、最終行の下に行を追加したり、別シートへ転記したりはできません。そうした処理まで自動化するなら VBA の出番です。マクロでの方法は VBAで最終行を取得する方法 と VBAで最終列を取得する方法 で解説しています。
検索系の関数の使い分けをまとめて知りたい方は、VLOOKUP・XLOOKUP・INDEX+MATCHの違いと使い分け もあわせてどうぞ。
応用：プルダウンリストの自動拡張
最終行の取得がいちばん活きる場面が、プルダウン（入力規則のリスト）の自動拡張です。リストに項目を足すたびに、入力規則の範囲を直す手間がなくなります。
例として、シート「リスト」の A1 に見出し「担当者」、A2〜A5 に「佐藤・鈴木・高橋・田中」が入っているとします。まず、次の手順で名前を定義しましょう。
- 「数式」タブの「名前の定義」をクリック
- 名前に「担当リスト」と入力
- 参照範囲に次の式を入力して「OK」
=リスト!$A$2:INDEX(リスト!$A$2:$A$200,COUNTA(リスト!$A$2:$A$200))今は COUNTA が 4 なので、範囲は A2:A5。A6 に「伊藤」を追加すると COUNTA が 5 になり、範囲が自動で A2:A6 に広がります。
続いて、プルダウンを付けたいセルを選び、「データ」タブの「データの入力規則」を開きます。入力値の種類を「リスト」にして、元の値に =担当リスト と入力すれば完成。
同じことは、OFFSET 関数でもできます。OFFSET と COUNTA で動的な名前を作る書き方も、昔からよく使われています。
=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A$2:$A$200),1)ただし、OFFSET は INDIRECT と同じく揮発性関数（ブックのどこかが再計算されるたびに計算し直される関数）です。一方、INDEX は Microsoft の公式ヘルプが挙げる揮発性関数の一覧に含まれていません。大きなブックで使うなら、INDEX:INDEX 型を選ぶのがおすすめです。
どちらの式も COUNTA を使っているため、リストの途中に空白や “” を入れないでください。リストをテーブル（Ctrl+T）に変換して管理する方法もあります。
プルダウンの基本と応用は Excelのプルダウンリストの作り方 にまとめています。OFFSET の詳細は OFFSET関数の使い方、INDIRECT は INDIRECT関数の使い方 をどうぞ。
FAQ
Q. 「件数」と「最終行」は同じものですか？
見出しが1行目にあり、空白なく続く表なら「件数＋1＝最終行」です。空白や “” がある表では一致しません。”” を除いた件数なら、Excel 2021 以降で =ROWS(FILTER(D2:D100,D2:D100<>"")) が使え、サンプル表の結果は 5 です。
ROWS は範囲や配列の行数を返す関数です。詳しくは ROWS関数の使い方 をご覧ください。
Q. 最終行の1つ上（前回分）の値も取れますか？
Microsoft 365 か Excel 2024 なら、CHOOSEROWS 関数が便利です。行番号にマイナスを指定すると、末尾から数えて取り出せます。
=CHOOSEROWS(FILTER(D1:D100,D1:D100<>""),-2)D列で “” 以外の値は「判定・達成・未達・未達・未達・達成」です。末尾から2番目なので、結果は D5 の「未達」になります（-1 なら最終値の「達成」）。詳しくは CHOOSEROWS関数の使い方 をどうぞ。
Q. MATCH(9^9) の式が #N/A になります。
範囲に数値が1つもないと #N/A になります。文字列の列なら LOOKUP 版に切り替えましょう。まだデータがない月などに備えるなら、=IFERROR(MATCH(9^9,C:C),"") のように IFERROR で包んでおく方法もあります。
Q. Ctrl+Shift+Enter は必要ですか？
Microsoft 365 では、どの式も Enter だけで確定できます。Excel 2019 以前で MAX+ROW を使うときは、Ctrl+Shift+Enter が必要な場合があります。確定方法で迷いたくないなら、LOOKUP 版を選んでおきましょう。
まとめ
Excel の関数で最終行・最終値を取るときは、データの状態に合った式を選ぶことが大切です。要点を振り返ります。
- 空白も “” もない列なら
INDEX(列,COUNTA(列))がいちばん簡単 - COUNTA は空白で上に、”” の数式で下にズレる
- 空白・”” ・混在に強いのは
LOOKUP(2,1/(範囲<>""),範囲)（全版対応） - 最後の「数値」だけが欲しいなら
MATCH(9^9,列) - Microsoft 365・Excel 2024 なら
TAKE(FILTER(範囲,範囲<>""),-1)が読みやすい - 最終列は ROW を COLUMN に替えるだけで同じ考え方が使える
まずは自分の表の「一番下の値」を、LOOKUP 版で表示してみてください。月次の集計表や申込一覧の更新が、ぐっと楽になるはずです。
