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

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

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

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

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

この記事の著者

データサイエンティスト / ブロガー

たぬ

tanu

詳しいプロフィール

プロフィール

福岡在住のデータサイエンティスト。Pythonでルーチンワークを瞬殺する仕組みを作ることやデータを視覚化することが得意です。

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

COUNTA関数を使う方法（途中空欄NG）

こちらで確認する

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

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

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

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

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

MATCH関数を使う方法（途中空欄OK）

こちらで確認する

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

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

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

MATCH関数の誤解を利用する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MAX関数を使います。

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

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

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

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

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

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

さいごに

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

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

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

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

以下、本日のまとめ

それでは！