【エクセル】最終行のデータを関数で取得する方法（別シート対応）

エクセルで、最終行のデータを取得したいと思ったことはありませんか？

たとえば…

随時データが追加される表で、最終更新日がすぐに分かるようにしたい。

膨大なデータベースの最終行に計算された合計を、シートの最上部に表示したい。

可変しない表なら苦労しませんが、最終行のセルの位置が固定されていない場合、データの追加や削除などがある度に参照するセルを変更する必要がありますよね。

【エクセル】セルの参照を固定（列のみ・行のみ）する方法とは？エクセルで数式をコピーしたら計算がおかしい…そんな経験はありませんか？ 下図のC2セルには、静岡店の売上構成比を求めるために「=B...

ナル

もっと簡単に、エクセルの最終行を取得する方法はないのかな？

そこで本記事では、可変するエクセルの表から最終行のデータを抽出する方法をご紹介！

さらに、応用編として別シートの最終行データを取得する方法もお伝えします。

2つのエクセル関数を組み合わせて最終行のデータを取得する！

必要なエクセル関数はINDIRECT関数とCOUNTA関数の2つのみ。

エクセルで最終行のデータを求める手段を検索すると、マクロ（VBA）を利用する方法がよく紹介されていますよね。

しかし、マクロ（VBA）はエクセル初心者には敷居が高く、最低限の知識がないと活用することが困難なため、今回は関数を用いた手法をお伝えします。

普段エクセルで関数をあまり使わないという方でも、真似をするだけで最終行のデータ取得が可能になるので是非チャレンジしてくださいね！
スポンサーリンク

エクセルの最終行データを取得する2つの関数とは？

最終行のデータを取得するために必要な2つのエクセル関数をご紹介します。

エクセルの最終行データを取得する2つの関数①INDIRECT

INDIRECTは、文字列でセルを参照することができるちょっと変わった関数。

【エクセル】連動型プルダウンリストで選択肢を絞り込む方法とは？プルダウンリストの選択肢を絞り込みたいと思ったことはありませんか？エクセル機能の「名前の定義」と「INDIRECT関数」を組み合わせることで連動型プルダウンリストを作成することができます。...

たとえば、D1セルに「A1」という文字列が入力されているとします。

E1セルを選択した状態で、関数の挿入をクリック。

関数の分類を「検索/行列」に変更して、一覧からINDIRECTを探します。

INDIRECT関数の引数は「参照文字列」と「参照形式」の2つですが、実際には「参照形式」は省略してもOK。

参照文字列にD1セルを指定。

すると、E1セルに「=INDIRECT(D1)」という式が入力され、「日付」が結果と返されました。

ちょっとややこしいのですが、INDIRECT関数はD1セルに入力された文字列を参照しています。

そのため、たとえばD1セルの文字列をA1からA6に変更すると、E1セルには「8月5日」が表示されるわけです。

INDIRECT関数は。複数のセルに入力された文字列を組み合わせて利用することもできます。

C1セルに「A」、D1セルに「6」と入力して、INDIRECT関数で「=INDIRECT(C1&D1)」と参照すれば、A6セルを参照可能。

まわりくどい参照をする関数ですが、理屈がわかるとたくさんの使い道があるエクセル関数のため覚えておいて損はありません。

INDIRECT関数を活用すれば、他のシートを参照することも簡単にできちゃいます。（後述します）
スポンサーリンク

エクセルの最終行データを取得する2つの関数②COUNTA

COUNTA関数は、範囲内の空白ではないセルの個数を数えます。

D1セルを選択して、関数の挿入ボタンをクリック。

関数の分類を「統計」に変更してCOUNTAを探します。

COUNTA関数の引数は「値1」に範囲を指定するだけ。

今回はA列に入力されているデータを数えたいので、「A:A」としました。

D1セルに「=COUNTA(A:A)」と式が入力され、結果は「6」が返されました。

A列は1行目から6行目までデータが入力されているので、空白ではないセルの数は6で間違いありませんね。

A列の日付のように更新されるデータの場合、空白ではないでセルの数を求めれば最終行が何行目かを把握することができます。

この表なら、A列のデータの個数が6＝A列の最終行は6行目ということがわかりますね。

ナル

COUNTA関数とCOUNT関数はなにが違うの？

COUNTA関数とよく似た関数にCOUNT関数があります。

関数の挿入時にもCOUNT関数とCOUNTA関数が並んで表示されるため、戸惑う方もいるかもしれません。

違いとしては、COUNTA関数は範囲の空白ではないセルの数を返すのに対して、COUNT関数は範囲の数値が入力されているセルの数を返します。

表によってはどちらの関数でも同じ結果になることもありますが、COUNT関数では文字列のセルは数えないため、正しく最終行を把握できない可能性がありますね。

COUNTA関数とCOUNT関数は表に合わせて使い分けてください。
スポンサーリンク

【実践】エクセル関数で最終行データを取得する方法（可変対応）

最終行に入力された日付を取得して、E1セルに表示させてみます。

エクセル関数を使わずにA列の最終日付を参照するとしたら、E1セルに入力するのはA4となりますよね。

では、INDIRECT関数とCOUNTA関数を組み合わせて、表の最終行に入力されている日付を求めてみましょう。

A4セルを「A」と「4」に分解して考えます。

日付は必ずA列に入力するため、「A」については固定でＯＫですが、問題はデータ追加される度に可変する「4」の部分。

ナル

そこで、「4」の部分をCOUNTA関数で求めます。

E2セルに「=COUNTA(A:A)」と式を挿入し、A列の空白ではないセルの数を表示しました。

COUNTA関数でA列のデータが4という結果が返されていますね。

次にE1セルにINDIRECT関数を挿入。

「=INDIRECT(“A”&E2)」と入力することで、「A」と「4」を組み合わせた文字列「A4」セルを参照することになります。

E1セルにA列の最終行「8月3日」が表示されました。

このままだと、式がちょっとスマートではないので２つの式を１つにします。

E1のセルを「=INDIRECT(“A”&COUNTA(A:A))」に変更。

これでエクセルの最終行データを取得できるようになりました。

日付が追加されたとしても、COUNTA関数がA列のデータの数を自動で求めてくれるため、常に最終行のデータを取得してくれます。

スポンサーリンク

【応用】エクセルで別のシートの最終行データを取得する方法とは？

取得したい最終行データは、いつも同じシート内にあるとは限りませんよね。

たとえば、各シートに店舗の売上金額が入力されていて、それぞれの合計金額を1つのシートに集めたい場合です。

しかも、シートごとにデータが入力されている最終行は異なるという状況。

ナル

集計するのが大変そう…

そこで応用編として、別シートの最終行データを取得する方法をお伝えします。

利用する関数は、先ほど同様にINDIRECTとCOUNTAのみ。

どんな式で別シートの最終行データを取得できるのかを考えていきます。

まず、エクセル関数を使わずに別シートの最終行を取得するとしたらどうなるか？

B3セルに、銀座店シートの合計が入力されているB6セルを入力すると「=銀座店!B6」なりますね。

同じシート内の最終行データを取得するのと、別シートの最終行データを取得することの違いは、参照に「シート名」が付くことだけです。

シート名だけ直接入力しても良いのですが、せっかくなのでINDIRECT関数を活用しましょう。
B3セルに「=INDIRECT(A3&”!”&”B”&COUNTA(INDIRECT(A3&”!”&”b:b”)))」と入力。

ナル

INDIRECT関数が式の中に2回も組み込まれてる！？

やっていることはシンプルで、式の「シート名」となる部分をA3セルに入力した店舗名をINDIRECT関数で文字列参照しているだけです。

あとは同じシート内の最終行データを取得する方法と一緒。

B3セルの式をB4セルとB5セルにコピーすれば、それぞれA列にある店舗名のシートを参照して、そこから最終行の合計金額を取得できます。

スポンサーリンク

エクセル関数で最終行データを取得する方法まとめ

表のデータが少ない場合や、シートの数が多くないなら、手作業で対象のセルを参照してもそんなに苦労はしないかもしれません。

しかし、可変対応できる式を一度作成しておくと、データが追加されて最終行の位置が変更されても自動的に参照してくれるため、あとの作業がかなりラクになります。

INDIRECT関数とCOUNTA関数の組み合わせは仕事でも活躍する場面が多いので、この機会にぜひためしてくださいね！

Ｅｘｃｅｌ最強の教科書 すぐに使えて、一生役立つ「成果を生み出す」超エクセ /ＳＢクリエイティブ/藤井直弥

posted with カエレバ

 

楽天市場

Amazon

Yahooショッピング