3分SHAREツイートシェアはてブLINEPocket「EXCELなんか無くなって、みんなGoogleスプレッドシートに移行すればいいのに」と、いつも本気で思っている、たぬ(@tanuhack)です!仕事でEXCELやスプレッドシート使ってると、最終行の行番号や内容を取得したいときってよくありませんか?最終行が可変するデータに合わせて、SUMやCOUNTAの範囲をいちいち変えるのってすごく面倒。ミスも誘発するし...。そこで今回は、可変データでも関数で最終行の行番号・内容を取得する方法を紹介します。さいごに動的な最終行の『行番号』を取得するCOUNTA関数を使う方法(途中空欄NG)こちらで確認する=COUNTA(A:A)この方法が一番簡単です。COUNTAは指定された範囲内で、入力されている値が数値であれ文字列であれ、1つずつカウントしてくれます。範囲内で途中に空欄がある場合は使えませんなので、最初の行から最終行までギッシリ埋まっている場合、COUNTA関数を使って最終行を出しましょう。MEMOこのときCOUNTA関数で得られる値は数値なので、四則演算を始めとする色んな計算をすることができます。1行目が空欄で2行目から最終行までギッシリ埋まっている場合は、『COUNTA(範囲セル)+1』で求めることができます。MATCH関数を使う方法(途中空欄OK)こちらで確認する=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))さっきのCOUNTAと比べると、かなり複雑ですね。このままコピペして使って頂いても構いませんが、せっかくなので1つずつ解説していきます。1)』に注目してください。ここで何をしているかと言うと、『A列の最終行が数値の場合のみ、最終行の番号を取得』しています。1)ここで、A列の中で『MAX(A:A)+1』、つまり『101』を探しています。A列に101なんてありませんね。1)MATCH関数の3つ目の引数に『1』がしてある場合、元データが昇順で並んでいることを前提として、指定した数と一致するか、それより小さい値の中で最大値を探すという動きをします。MATCH関数の『データが昇順で並んでいることを前提として』というところがポイントで、A列の途中に最終行の値より大きい数字が入っていても、それを無視して最終行の値が最大値であると錯覚して処理されるんですよ。その結果、このMATCH関数からA11セルの行数の『11』が返されます。1)=11これで、A列の最終行が数値のときの、最終行の番号を取得できるようになりました。A列の最終行が文字列のときの、最終行の番号を取得できるようにする=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))次に、赤文字の『MATCH("",A:A,-1)』に注目してください。ここで何をしているかと言うと、『A列の最終行が文字列の場合のみ、最終行の番号を取得』しています。=MATCH("",A:A,-1)MATCH関数の3つ目の引数に『-1』がしてある場合、元データが降順で並んでいることを前提として、指定した数と一致するか、それより大きい値の中で最小値を探すという動きをします。本来、スプレッドシートでセルの大きさを比較するとき『文字列>空白』という大小関係が成り立っています。今回のケースでは、MATCH関数が錯覚を起こしていているので、『空白>文字列』が成り立ってしまい、最終行の値が最小値であると処理されてしまいます。その結果、このMATCH関数からA11セルの行数の『11』が返されます。=MATCH("",A:A,-1)=11これで、A列の最終行が文字列のときの、最終行の番号を取得できるようになりました。1)=MATCH("",A:A,-1)MAX関数を使います。100)=大きい値を返す(この場合100)これで、最終行の値が文字列でも数値でも対応できるようになります。=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))動的な最終行の『内容』を取得するここまでくれば、あと一息です。データの中身を取得する場合は『INDEX』関数を使います。数値)数値に、COUNTA関数とMATCH関数で求めた最終行の値を代入すると、その列の最後の値を参照することができます。MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1)))』さいごに今回は、スプレッドシートの関数で『動的な』最終行の行番号・内容を取得する方法を紹介させていただきました。その可変データの最終行の情報と『INDIRECT』関数を組み合わせると、仕事で自動化できることが増えてきます。:関数組み合わせテクニックこの記事を書いた人たぬ効率沼ブロガールーチンワークを自動化で瞬殺することに人生の楽しさを見つけた効率沼ブロガーです。本業は「データ分析」と「作業の効率化」を生業としているデータアナリスト。PythonとGASに人生救われました。当ブログは、おかげさまで月間12万PV。2020年までに30万PV目指して頑張ってます。