【コピペでOK】スプレッドシートの関数で『動的な』最終行の行番号・内容を取得する方法

EXCELなんか無くなって、みんなGoogleスプレッドシートに移行すればいいのに」と、いつも本気で思っている、たぬ(@tanuhack)です!

仕事でEXCELやスプレッドシート使ってると、最終行の行番号や内容を取得したいときってよくありませんか?

最終行が可変するデータに合わせて、SUMやCOUNTAの範囲をいちいち変えるのってすごく面倒。ミスも誘発するし…。

そこで今回は、可変データでも関数で最終行の行番号・内容を取得する方法を紹介します。

動的な最終行の『行番号』を取得する

COUNTA関数を使う方法(途中空欄NG)

get-last-row1

こちらで確認する

=COUNTA(A:A)

この方法が一番簡単です。

COUNTAは指定された範囲内で、入力されている値が数値であれ文字列であれ、1つずつカウントしてくれます。

しかし、範囲内で途中に空欄がある場合は使えません

get-last-row2

なので、最初の行から最終行までギッシリ埋まっている場合、COUNTA関数を使って最終行を出しましょう。

MEMO
このときCOUNTA関数で得られる値は数値なので、四則演算を始めとする色んな計算をすることができます。

例えば、1行目が空欄2行目から最終行までギッシリ埋まっている場合は、『COUNTA(範囲セル)+1』で求めることができます。

get-last-row3

MATCH関数を使う方法(途中空欄OK)

get-last-row14

こちらで確認する

=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))

さっきのCOUNTAと比べると、かなり複雑ですね。

このままコピペして使って頂いても構いませんが、せっかくなので1つずつ解説していきます。

ただ、解説がマニアックすぎるので、読み飛ばして次の章まで進んで頂いても大丈夫です!

MATCH関数の誤解を利用する

=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))

まず、赤文字の『MATCH(MAX(A:A)+1,A:A, 1)』に注目してください。

ここで何をしているかと言うと、『A列の最終行が数値の場合のみ、最終行の番号を取得』しています。

get-last-row5

get-last-row6

=MATCH(MAX(A:A)+1,A:A, 1)

get-last-row7

ここで、A列の中で『MAX(A:A)+1』、つまり『101』を探しています。

でもA列に101なんてありませんね。

=MATCH(MAX(A:A)+1,A:A, 1)

MATCH関数の3つ目の引数に『1』がしてある場合、元データが昇順で並んでいることを前提として、指定した数と一致するか、それより小さい値の中で最大値を探すという動きをします。

get-last-row8

MATCH関数の『データが昇順で並んでいることを前提として』というところがポイントで、A列の途中に最終行の値より大きい数字が入っていても、それを無視して最終行の値が最大値であると錯覚して処理されるんですよ。

get-last-row9

その結果、このMATCH関数からA11セルの行数の『11』が返されます。

=MATCH(MAX(A:A)+1,A:A, 1)
=11

これで、A列の最終行が数値のときの、最終行の番号を取得できるようになりました。

A列の最終行が文字列のときの、最終行の番号を取得できるようにする

=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))

次に、赤文字の『MATCH("",A:A,-1)』に注目してください。

ここで何をしているかと言うと、『A列の最終行が文字列の場合のみ、最終行の番号を取得』しています。

get-last-row10

get-last-row11

=MATCH("",A:A,-1)

MATCH関数の3つ目の引数に『-1』がしてある場合、元データが降順で並んでいることを前提として、指定した数と一致するか、それより大きい値の中で最小値を探すという動きをします。

get-last-row12

本来、スプレッドシートでセルの大きさを比較するとき『文字列>空白』という大小関係が成り立っています。

しかし今回のケースでは、MATCH関数が錯覚を起こしていているので、『空白>文字列』が成り立ってしまい、最終行の値が最小値であると処理されてしまいます。

get-last-row13

その結果、このMATCH関数からA11セルの行数の『11』が返されます。

=MATCH("",A:A,-1)
=11

これで、A列の最終行が文字列のときの、最終行の番号を取得できるようになりました。

2つのMATCH関数を組み合わせて、最終行の値が文字列でも数値でも対応できるようにする

  • =MATCH(MAX(A:A)+1,A:A, 1)
  • =MATCH("",A:A,-1)

MAX関数を使います。

=MAX(1, 100)
=大きい値を返す(この場合100)

これで、最終行の値が文字列でも数値でも対応できるようになります。

=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))

動的な最終行の『内容』を取得する

ここまでくれば、あと一息です。

データの中身を取得する場合は『INDEX』関数を使います。

=INDEX(範囲セル, 数値

数値に、COUNTA関数とMATCH関数で求めた最終行の値を代入すると、その列の最後の値を参照することができます。

get-last-row16

get-last-row15

ちゃんと取得できていますね(^o^)

  • 途中に空欄NG:『=INDEX(A:A, COUNTA(A:A))』
  • 途中に空欄OK:『=INDEX(A:A, MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1)))』

さいごに

今回は、スプレッドシートの関数で『動的な』最終行の行番号・内容を取得する方法を紹介させていただきました。

その可変データの最終行の情報と『INDIRECT』関数を組み合わせると、仕事で自動化できることが増えてきます。

【超便利】スプレッドシートの『INDIRECT』関数で動的にセルを指定する方法

動的なデータに対応できるように、少しでも手動で変更することを減らしていって、最高のスプレッドシートを作ろう(^o^)

以下、本日のまとめ

動的な最終行の『行番号』を取得する
  • 途中に空欄NG:『=COUNTA(A:A)』
  • 途中に空欄OK:『=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))』
動的な最終行の『内容』を取得する
  • 途中に空欄NG:『=INDEX(A:A, COUNTA(A:A))』
  • 途中に空欄OK:『=INDEX(A:A, MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1)))』

それでは!