VBAではなく、関数を使って「最終行」の行数や値を取得してみましょう

それはさておき、大きなテーブルや”情報のかたまり”をExcelワークシートで取り扱う場合、「最終行」に着目することは意外に多いものです。

VBAなら、 Cells(Rows.Count,1).End(XlUp).Row で行数を数えて、「最終行まで、カウンタとなる数字を1ずつ足していく」とか、「同じコピー＆ペースト作業を行数分くり返す」などのプログラムを作成するわけです。

今回は、VBAプログラム上ではなく、Excelワークシート上で、関数を使って簡単に大容量データの最終行を見つける方法をご紹介します。

基本のショートカットで最終行へジャンプすることはできますが、関数なら、行数(数値)の取り出しができます

今回も、unwiredlabs OpenCellid(英語サイト)から無償ダウンロードできる、日本国内携帯基地局情報(サイトからは、404.csv というテキストファイルとして取得できます）をサンプル・データとして使用します。

まず、csvファイルをPower Queryで読み込みましたが、総レコード数がわかりません。 Excel基本のショートカット集の1つである［Ctrl］+［End］キーを押せば、この巨大な表の右下端へ一発ジャンプします。

この表は、14列701,043行の資料でした。

しかし、やりたいことは、ただ最終セルへジャンプすることではなくて、「701,043行」という数値を取得することです。数値が取得できれば、「最終701,043行の下に”合計行”をつくる」とか、「新規レコードを3件足す」というような操作につなげることができますから。そんな時には、ぜひExcel標準の関数を、うまく活用してみましょう。

関数で最終行の行数を判定するにしても、いろいろなやり方があります。

もっともシンプルなやり方は、COUNTA(空白でないセルを数える関数）で、1列内のデータ入力済セル数を数えてしまうことです。

最終行数を、正確に701,043行だと返してくれました。

しかし、このやり方だと、問題が残ります。

実務上は、表の途中でデータが一部欠損した（空白の）資料などが山ほどあります。1枚の表として、全列全行データがすべて埋まった完成版を使ってCOUNTAするならよいのですが、途中に空白セルがあったら、正確な答えは得られませんね。

どうしましょう…!? そのような場合には、COUNTAの替わりにINDEX関数を使ってみてください。

途中に空白行があっても、正確な最終行数をカウントできました。

この関数の具体的な内容は以下の通りです。

まず、INDEX関数（”参照形式”ではなく、単体表からデータを抽出する”配列形式”）の基本構文は

=INDEX(配列, 行番号, [列番号])

です。

列番号は省略可能ですが、INDEX関数中の引数である行番号をゼロにした場合には、行全体の値の配列が、それぞれ INDEX によって返されます。

上図では、INDEX関数の第1引数（配列）を、

(LEN(A:A)>0)*ROW(A:A)

としていますが、これはつまり、A列が空白か否かを判定しているわけです。A列1行目は空白ではない(LEN(A1)>0)ので配列の一部とみなされますが、A列2行目は空白(LEN(A2)=0)なので、配列としてはスキップされます。そのように最下行まで見て行った時に、配列の最下端の行数を表す数値が、この配列中の最大数となるため、最後にMAX関数を使って”最大値取り出し”をしているというわけです。

INDEX関数なら、最終行の「セルの値」を抽出することも可能です

前章は、表の最終行（行数）を抽出するための関数式でしたが、INDEX関数を使うと、最終行の「セル値」そのものを抽出することもできます。最終（or 最新)データが何だったか、巨大な表の末尾までスクロールしなくても確認できるようになれば便利ですよね？

INDEX関数の構文はきわめてシンプルで、たとえばA列最終行のセル値を取得したい場合には

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

E列最終行のセル値を取得したい場合には、

=INDEX(E:E,COUNTA(E:E))

とすればOKです（第2引数のCOUNTA関数で、最終行の行番号を指定）。

ただし、COUNTAを使った行数カウントでは、途中に空白セルがあると誤った答えが返ってきますから、それを避けるためには、前章のINDEX配列数式を、第2引数として入れ子にします。つまり、A列であれば、

=INDEX(A:A,MAX(INDEX((LEN(A1:A1048576)>0)*ROW(A1:A1048576),0))) とすることで、途中に空白セルがあろうとなかろうと、正確な最終行値を取得し、そのセルの値そのものを返してもらえるようになるのです。

※注;1046576は、1ワークシートの最大行数

まとめ:VBAでなくても、最終行数や最終セル値は簡単に取り出せます
関数を活用して、大きな表からも必要情報に効率的にアクセスしましょう!

終わりよければすべてよし!? VBAプログラムではなくても、資料の最終行数、もしくは最終行の値を確認しておくことは、意外に重要です。関数（特にINDEX関数と配列数式）を使えば、簡単に必要な情報を取り出すことができますから、大きなテーブルの取り回しに困っているなら、ぜひ試してみてはいかがでしょうか?