【詳細解説】エクセル関数で最終行の行番号・内容を取得する

2017年4月12日

エクセルで、表の最終行の行番号や内容を取得したいという場合があります。

当然、インターネットで検索すると、様々な答えが出てきます。

ただ、解説がないケースがほとんどのため、意味がわからないまま使っている方も多いと思います。

意味がわからなくても、不具合が起きなければ問題はないのですが、ちょっと表のレイアウトや内容を変更しただけで不具合が生じてしまうこともあります。

そこで、このページでは、表の最終行の行番号・内容を取得する方法と、その詳細解説を書いていこうと思います。

もし、VBAで最終行を取得したい場合にはVBAで最終行の行番号を取得するをご覧ください。

この記事の目次

1 最終行を取得する必要がないレイアウトにできないか？
2 最終行の行番号を取得する方法
3 最終行の「内容」を取得する方法
4 まとめ

最終行を取得する必要がないレイアウトにできないか？

そもそもの話として、一番最初に考えないといけないのは「最終行を取得する必要がある」レイアウトにしないといけないのか？ということです。

例えば、表の「最後」にある「合計欄」を抽出したいという場合。

最初から「合計欄」を表の「上」に表示してしまえば、今回のような処理を考える必要がなくなります。

このように、表のレイアウトを工夫することで、最終行の内容を取得しないでも済むようにできないかを、まずは考えてみてください。

検討の結果、どうしても最終行の情報を取得せざるを得ないという場合には、このページで紹介する方法を使ってください。

最終行の行番号を取得する方法

以下、全て「Ａ列」に何らかのデータが入っている場合に、「Ａ列」の一番下のデータが入っている「行番号」を計算します。

counta関数を使う方法（空欄不可）

計算式

A列に「1行目から最終行」まで値が入っている場合には、次の計算式で最終行の行番号が計算できます。

=counta(A:A)

解説

A列に「1行目から最終行」まで値が入っているという前提ですから、counta関数を使って、A列の中でデータが入力されているセル数を数えれば、それが最終行の行数を表すことになります。

注意点

前提条件の通り、「１行目から最終行」までデータが入っていないとダメです。

例えば、次の表のように「２行目から」表が始まっている場合には、この方法を、そのまま使うことはできません。

この場合には、計算式を少し変更して、counta関数の参照範囲を変更するとともに、次のようにrow関数で補正をかければ、表の位置変更に強い計算式ができます。

表内部（3行目以降）のデータ件数をcounta関数で取得します
「row(A2)」という関数で列タイトルが表示されているA2セルの行数（＝2行目）を取得します

この２つを足すことで、最終行の行数が得られます。

これで、表の外部への空欄挿入や文字入力などにも柔軟に対応できるようになります。

ただし、今度はcounta関数がA行全体ではなく、A3セルからA9999セルまでしか見てくれません。

ですから、約10,000行を超える大きさの表や、表内部で挿入・削除を繰り返すと不具合が出る可能性があります。

作業列を使う方法（途中空欄可）

計算式

作業列を使ってもいいという場合には、Ａ列に空欄があっても比較的平易な方法で、最終行の行番号が計算できます。

（必要なだけ下までコピー）
B1セル：=len(A1)>0
C1セル：=row(A1)
D1セル：=B1*C1

F2セル：=max(D:D)

解説

B列：len(A1)>0

A1セルに入力されているデータの「文字数が０より大きい」かどうかを判定します。

文字数が０より大きいということは、何かデータが入力されているということです。

ですから、この計算式の意味は、

データが入力されている行：TRUE
データが入力されていない行：FALSE

という意味になります。

C列：row(A1)

各行の「行番号」を表示します。

D列：B1*C1

B列とC列の計算結果をかけ算します。

かけ算をする場合には、

「FALSE」→0
「TRUE」→１

と置き換えて、かけ算が行われますので、計算結果は次のようになります。

結果を見ると、

データが入力されている行は「行番号」
データが入力されていない行は「0」

という計算結果になっていることがわかります。

F2セル：=max(D:D)

D列の最大値を計算します。

D列では、データが入力されている場合の「行番号」を計算していました。

ですから、その最大値を取ることで、データが入力されている「最終行の」「行番号」が得られることになります。

注意点

この方法では、作業列を使うため、作業列のメンテナンスが必要となります。

たとえば、表が縦に長くなった場合には、作業列も縦に伸ばしてやらないと、正しい計算ができませんので、ご注意ください。

配列数式を使う方法（途中空欄可）

少し計算式が複雑になってしまいますが、配列数式を使って、作業列を使わないで計算する方法もあります。

計算式

次の計算式で最終行の行番号が計算できます。

=max(index((len(A1:A9999)>0)*row(A1:A9999),0))

解説

こういう、入れ子が何重にもなっている計算式の場合は、入れ子の内側から考えていくのがポイントです。

今回の場合には、

「あ」：	len(A1:A9999)>0
「い」：	row(A1:A9999)
「う」：	「あ」*「い」
「え」：	index(「う」,0)
「お」：	max(「え」)

という５つの部分から構成されています。

これを順番に考えていきます。

※結論からいうと、先ほど解説した作業用列を使う方法と全く同じ計算を行っていることになります。

「あ」：len(A1:A9999)>0

いきなり、あまり見ない形が出てきました。

普通は「len(A1)」というような形だと思うのですが、今回は「len(A1:A999)」というように、lenの内側が「A1:A9999」と範囲指定されています。

これは、配列数式と呼ばれる形で、
「len(A1)」～「LEN(A9999)」の9999個の計算を同時に行う
という意味になります。

実際の計算式は「len(A1:A9999)>0」ですから、
「len(A1)>0」～「LEN(A9999)>0」の9999個の比較を同時に行う
ことになります。

※計算イメージ。
実際にはB列を使うわけではなく目に見えないところで計算されます。

「い」：row(A1:A9999)

これも、配列数式です。

先ほどのlen関数と考え方は一緒で
row(A1)～row(A9999)の9999個の計算を同時に行う
という意味になります。

※計算イメージ。
実際にはC列を使うわけではなく目に見えないところで計算されます。

「う」：「あ」*「い」

「あ」の結果と「い」の結果を「かけ算」します。

通常のかけ算と違うのは、「あ」と「い」は、両方とも配列数式であるという点です。

この場合には、各行の、それぞれの「計算結果」ごとにかけ算を行います。

例えば、
１行目であれば「FALSE」×「1」
２行目であれば「TRUE」×「2」
・・・
１１行目であれば「FALSE」×「11」
・・・
というような9999通りの計算を行うことになります。

かけ算の場合に、

「FALSE」→0
「TRUE」→１

と置き換えられるのは、作業列を使う場合と全く一緒ですので、実質的に、次のような計算が行われることになります。

※計算イメージ。
実際にはD列を使うわけではなく目に見えないところで計算されます。

「え」：index(「う」,0)

先ほどの9999個の計算結果を「配列」に変換する計算式です。

意味がわかりにくいと思うのですが、先ほどの9999個の計算結果を、次のmax関数で使うために「ひとまとめにする」一種のおまじないと考えてください。

「お」：max(「え」)

先ほどの「う」で計算した9999個の計算結果の「最大値」を計算します。

「う」のステップでは、データが入力されている場合の「行番号」を計算していました。

ですから、その最大値を取ることで、データが入力されている「最終行の」「行番号」が得られることになります。

計算過程を見ると、先ほどの「作業列」を使った場合の計算と全く同じだ、ということがわかると思います。

注意点

この計算式では「Ａ列全体」という指定ができません。

今回の例でいえば、A1～A9999セルというように最終行を9999行に指定しています。

逆にいうと、指定したよりも表が大きくなってしまうと正しい計算ができませんので、ご注意ください。

match関数を使う方法（最終行が数値。途中空欄可）

ここからは、特定の条件を満たす場合に、上で紹介した計算式よりも簡便な計算式で、最終行の行番号を求める計算式を紹介します。

計算式

A列の最後のデータが数値の場合には、次の計算式で最終行の行番号が計算できます。

=match(max(A:A)+1, A:A, 1)

解説

match関数は、「目的の値」を「指定した範囲」から探して、その位置を返す関数です。

今回の計算式では、

目的の値：	max(A:A)+1	（＝A列中で最大の値よりも１だけ大きい数）
指定した範囲：	A:A	（＝A列）

を指定しています。

指定した範囲に「A:A」と指定している場合には、match関数で得られる「位置」は「行番号」と一致します。

つまり、この計算式では、

「A列の中で最大の数よりも１」大きい数を
A列から探して
該当するデータの行番号を返す

という計算式を入れているわけです。

元表よりも「大きい値」を探すとどうなる？

今回の例では「A列の中で最大の数」は11です。

それよりも１だけ大きい数は「12」。

ですから、今回の計算式では「12」という数字をA列から探すことになるわけです。

・・・ってそんなのA列にあるわけないですよね？

なぜなら、A列に入っているデータの最大値が11ですから、12という数字は絶対に存在していません。

実は、match関数の３つ目の引数で「１」を指定していると、このような場合には指定範囲の「一番下」の行の行番号を返してくれるのです。

match関数で3つ目の引数を「１」にしたときの動作

match関数の３つ目の引数を「１」に指定すると、

元データが小さい数→大きい数の順番で並んでいることを前提として
指定した数と一致するか、それより小さい数の中で最大の数を探す

という動きをします。

ここで重要なのが、「小さい数→大きい数」という順番に並んでいることが前提になっているというところです。

仮に、実際に入力されている値が「小さい数→大きい数」の順番に並んでいなかったとしても、そうなっているはずだと信じ切って、処理をしてしまうのです。

実際には、エクセルは、次のような処理を行います。

１．A10セルに最大の値が入っていると誤解する

A3セル～A10セルの中で一番大きい数値は「A10」セルに入っているはずとエクセルは考えます。

実際にはA6セルの値が最大の値なわけですが、「小さい数→大きい数」と並んでいると信じ切っていますので、何も考えずにA10セルの値が最大だと誤解してしまうのです。

２．「A10セル」の値を、指定した数と比較する

A10セルに入っている（その最大であるはずの）値「9」を調べると、指定した数「12」よりも小さいことがわかります。
そのため、エクセルは、A10セルの「9」は、A列のデータの中で「最大のデータ」だと誤解してしまいます。

その結果、このmatch関数の返り値はA10セルの行数である「10」となるのです。

注意点

この方法は、表の一番下のデータが「数値」である場合にだけ使用可能です。

下記の例のように、表の一番下のデータが「文字列」データだと、正しい結果が得られませんので、ご注意ください。

厳密には、一番下のデータが「論理値」（＝TRUEまたはFALSE）の場合も正しい結果が得られませんが、実務上は問題ないことがほとんどでしょう。

また、今回の計算式は、本来エクセルが想定していないだろうと思われるmatch関数の使い方をしています。

もし、気持ち悪さを感じるのであれば使わないほうがいいでしょう。

match関数を使う方法（最終行が文字列。途中空欄可）

最終行が文字列データだ、とわかっている場合には、次の計算式で最終行の行番号を取得することができます。

計算式

A列の最後のデータが文字列の場合には、次の計算式で最終行の行番号が計算できます。

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

先ほどの計算式と似ていますが、

１つめの引数が「""」（＝空欄）
３つ目の引数が「-1」（先ほどは「+1」でした）

という違いがあります。

解説

今回の計算式を理解するポイントは、次の２つです。

１．３つめの引数を「-1」にしたときの挙動

３つ目の引数を「-1」にすると、先ほどとは違い、

データが「大きいデータ→小さいデータ」という順番で並んでいることを前提に
指定した数と一致するか、それより大きいデータの中で最小のデータを探す

という動きをします。

「+1」を指定したときと比べて、大小関係が入れ替わっているのがわかると思います。

２．文字列データの大小関係

エクセルの世界では、文字列データを比較した場合には、

（空欄）＜（何か値が入っているデータ）

という関係が成り立ちます。

この２つの前提知識が理解できれば、あとは、先ほどの話と一緒です。

具体的には、

「大きいデータ→小さいデータ」の順番に並んでいるのが前提です。そのため、A3セル～A10セルの中で一番小さいデータが「A10」セルに入っているはず、とエクセルは誤解します。
A10セルに入っている値「YMA」を見ると、指定した値「(空欄)」よりも「大きい」と判断します。

結局、この表の中で一番「小さいデータ」であるはずの「YMA」でさえも、指定した値よりも「大きい」と判断されてしまうのです。

ということは、A10セルの「YMA」が、指定した数「（空欄）」「より大きいデータの中で最小」と判断されるということでもあります。

その結果、match関数で、一番下のデータの行番号「10」が得られるのです。

注意点

この方法は、表の一番下のデータが「文字列」である場合にだけ使用可能です。

下記の例のように、表の一番下のデータが「数値」データだと、正しい結果が得られませんので、ご注意ください。

厳密には、一番下のデータが「論理値」（＝TRUEまたはFALSE）の場合も正しい結果が得られませんが、実務上は問題ないことがほとんどでしょう。

match関数を使う方法（最終行は任意。途中空欄可）

先ほどの２つを組み合わせると、数値と文字列が混じっているデータでも、最終行を取得することができます。

要するに、

「=match(max(A:A)+1, A:A, 1)」で数値の最終行
「=match("", A:A, -1)」で文字列の最終行

が計算できるわけですから、この２つのうち大きいほうが、数値・文字列混在している場合の最終行だということになります。

計算式

エラー処理なども加え、具体的に計算式化すると、次のようになります。

=max(iferror(match(max(A:A)+1,A:A, 1),0),iferror(match("",A:A,-1),0))

解説

赤字の部分は、先ほどの２つの計算式と全く一緒です。

その外側をiferror関数でくるんでエラーの場合は０として取り扱う。

さらに、max関数で大きい方の行番号を取ってきます。

これで、最終行が数値でも文字列でも、最終行の行番号を取得することができます。

最終行の「内容」を取得する方法

さて、上記のいずれかの手段で最終行の「行番号」が取得できれば、最終行の「内容」を取得する方法は簡単です。

概要

例えば、最終行の「A列」のデータを取得したいのであれば、次の計算式を入れます。

=index(A:A,「最終行の行番号」)

「最終行の行番号」の欄には、このページで解説した方法のいずれかを入れてください。

例えば、一番最初に解説した「counta(A:A)」を入れると、次のような感じになります。

解説

index関数を使うと、index関数の第１引数（A:A）で指定したセルの中で、第２引数で指定した行のデータを抽出することができます。

例えば、

「=index(A:A,1)」 → A1セル
「=index(A:A,5)」 → A5セル

という感じです。

この第２引数の部分に、このページで解説した「最終行の行数」を入れることで、目的のデータを抽出することができます。