大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excelで、最終行までの「セル範囲」を取得する方法について、ご紹介します。使う関数は、「OFFSET関数」と「COUNTA関数」です。最終行までのセル範囲を取得できれば、最終行が変わったとしても「セル範囲を変更する必要がない」ので、便利です。

はじめに

この記事では、最終行までの「セル範囲」を取得する方法について、ご紹介します。

最終行までの「セル範囲」を取得するには、「OFFSET関数」と「COUNTA関数」を使います。

取得した「セル範囲」は、別のExcel関数と組み合わせて使います。

「SUM関数」や「VLOOKUP関数」などです。

最終行までのセル範囲が取得できれば、最終行が増えても自動で、「セル範囲を変更する必要がない」ので、便利です。

では、最終行までの「セル範囲」を取得する方法について、解説していきます。

この記事で紹介すること

  • 最終行までの「セル範囲」を取得する方法

目次から見たい項目へ移動すると便利ですよ。

目次

Excelで最終行までのセル範囲を取得する

Excelで、最終行までの「セル範囲」を取得するには、「OFFSET関数」と「COUNTA関数」を使います。

OFFSETで最終行までのセル範囲を取得するイメージ

OFFSET関数で最終行までのセル範囲を取得するイメージです。

次のように入力できると、取得することができます。

=OFFSET(A1,0,0,10,1)

セルに入力します。

最終行までの「セル範囲」を取得

OFFSET関数で最終行までのを取得するイメージ

最終行の行数「10」がわからないので、この行数を取得する必要があります。

最終行の番号が必要

最終行の行数をどうにか取得する必要がある

最終行の行数は「COUNTA関数」で取得できます。

「OFFSET関数」の考えが難しいので、使い方を説明します。

OFFSETのイメージ

OFFSET関数で、取得できるのは、「セル範囲」になります。

OFFSETは「セル範囲」を取得する

OFFSET関数で取得できるのはセル範囲

OFFSET関数で、変更できるのは、2つです。

  • 移動する行と列
  • 高さと幅

という感じです。

最初に基準となるセルを決めます。

「基準」を設定する

「基準」は、表の一番上のセルを設定します。

基準は表の一番上のセル

次は、移動する行と列を指定します。

移動する「行」と「列」

行と列は、移動させないので、両方とも「0」となります。

行と列は移動しないので「0」を入力

次は、「高さ」と「幅」を設定します。

「高さ」と「幅」の設定

「高さ」が行数になります。

「幅」は、「1」列と設定します。

高さに行数を設定して列数は1列

こんな感じで、「OFFSET関数」を使うことができます。

最終行までのセル範囲を取得したいので、「10」行という値を取得する必要があります。

最終行の番号を「COUNTA関数」で取得する

最終行の番号をCOUNTA関数で取得する

最終行の番号を「COUNTA関数」で取得します。

COUNTAで最終行の番号を取得

「COUNTA関数」で最終行までの行番号を取得します。

=COUNTA(A:A)

セルに入力します。

最終行の番号を取得

COUNTA関数で最終行までの行番号を取得する

結果は、「10」個あるとなりました。

この「10」を「OFFSET関数」に組み合わせます。

OFFSETとCOUNTAを組み合わせる

COUNTA関数で取得した行番号を、「OFFSET関数」に組み合わせます。

OFFSETとCOUNTAを組み合わせる

次のようになります。

=OFFSET(A1,0,0,C2,1)

セルに入力します。

COUNTA関数をOFFSET関数に組み合わせる

これで、最終行までの「セル範囲」を取得できます。

関数をまとめる

関数をまとめると、次のようになります。

=OFFSET(A1,0,0,COUNTA(A:A),1)

セルに入力します。

COUNTA関数とOFFSET関数をまとめて最終行までのセル範囲を取得

これで、関数をまとめて、最終行までの「セル範囲」を取得しています。

「セル範囲」を取得している

最終行までの「セル範囲」を取得している

これだけ、みても意味わからないですよね。

最終行までの「セル範囲」を取得するだけでは、意味がありません。

取得した「セル範囲」を「別の関数」と組み合わせて使います。

「別の関数」と組み合わせて使う

最終行までのセル範囲を別の関数と組み合わせる

「SUM関数」や「VLOOKUP関数」と組み合わせると、かなり便利なります。

では、やってみます。

Excelで最終行までのセル範囲を合計してみる

最終行までのセル範囲を、合計してみます。

最終行までをSUMで合計してみる

最終行までのセル範囲を「SUM関数」で合計してみます。

「SUM関数」を組み合わせる

入力する数式は、こんな感じになります。

=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))

セルに入力します。

最終行までのセル範囲をSUMで合計する

結果は、「55」となりました。

1から10までを足すと、「55」ですね。

最終行までのセル範囲の合計を、正しく計算できています。

行数を増やして自動で計算

最終行までのセル範囲を使えば、行数を増やしても自動で、計算してくれます。

データを追加します。

「データ」を追加

行数を追加

セル範囲を自動で変更してくれます。

「セル範囲」を自動で変更

行数を増やして最終行までのセル範囲の合計を計算

結果は、「66」となりました。

1から11を足して、「66」ですね。

自動で最終行までの合計を計算してくれます。

Excelで最終行までのセル範囲で値を検索してみる

次は、最終行までのセル範囲で値を検索してみます。

組み合わせるExcel関数は、「VLOOKUP関数」になります。

最終行までセル範囲からVLOOKUPで値を検索

最終行までのセル範囲から、「VLOOKUP関数」で値を検索してみます。

「VLOOKUP関数」を組み合わせる

入力する数式は、こんな感じになります。

=VLOOKUP(10,OFFSET(A1,0,0,COUNTA(A:A),2),2,FALSE)

セルに入力します。

最終行までのセル範囲をVLOOKUP関数に組み合わせる

結果は、「J」となりました。

検索値が「10」で、「2」列目を取得しているので、「J」ですね。

正しく検索できています。

取得しているセル範囲は、ここですね。

取得している「セル範囲」

最終行までのセル範囲を取得している

最終行までのセル範囲を取得しています。

行数を増やして自動で計算

行数を増やしても、自動でセル範囲を取得してくれます。

11行目に値を追加します。

「データ」を追加

11行目に値を追加

自動で最終行までの「セル範囲」を取得してくれます。

「セル範囲」が自動で変更

最終行までのセル範囲を自動で取得

自動で最終行までの「セル範囲」を取得してくれます。

検索値を「11」に変更

検索値を「11」に変更してみます。

=VLOOKUP(11,OFFSET(A1,0,0,COUNTA(A:A),2),2,FALSE)

セルに入力します。

VLOOKUP関数で検索値「11」の行の2列目を取得

結果は、「K」となりました。

最終行を検索できています。

こんな感じで、自動で「セル範囲」を変更してくれます。

おわりに

この記事では、最終行までの「セル範囲」を取得する方法について、ご紹介しました。

最終行までの「セル範囲」を取得するには、「OFFSET関数」と「COUNTA関数」を使います。

取得した「セル範囲」は、別のExcel関数と組み合わせて使います。

「SUM関数」や「VLOOKUP関数」などです。

最終行までのセル範囲が取得できれば、最終行が増えても自動で、「セル範囲を変更する必要がない」ので、便利です。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す