エクセルの集計の操作を楽にするセル番地の取得方法

( ノ゚Д゚)こんばんは!こんにちは!おやすみなさい!おはようございます! とっちーです。

 

最終行や最終列を求める技!があると「とても便利」なのです。

 

表で扱う件数データは、固定から浮動のものまで数多く分かれます。

 

件数データ数が浮動の場合、いちいち数えていると時間がかかります。

 

そんな浮動の件数データを瞬時に取得出来たら「超ラッキー」です。

 

 

シートの行と列の最大数

 

え、こんなにあるのってなると思います。

 

(はてな君)
え?そんなにあるの?

 

(えっちゃん)
きっと、おどろくね!

 

 

(はてな君)
わぉ!

 

 

 

Excel2007以降のシートの最大行数は104万8576行になり、最大列数は、1万6384列に増えたんですね。

 

(えっちゃん)
半端な数ではない!

 

(はてな君)
そんなに数えていたら大変になる!

 

(えっちゃん)
そこで簡単に済ませる方法を伝えるね

 

(はてな君)
うん

 

RowsとColumns

 

 

RowsやColumnsはオブジェクト(シートやセル)のプロパティです。

 

 

プロパティコードを記述する場合。

「どこ」の「何」を「どうする」という手順を踏まないとその機能を発揮できなものもあるんです。

 

 

オブジェクトの行(Rows)を「どうする」とか、オブジェクトの列(Columns)を「どうする」とかになります。

 

つまり、RowsもColumnsもRangeオブジェクトが戻り値になるんです。

 

今回の「どうする」の部分は、「Count」を使用します。

簡単にいうとカウントなので数える。とでも覚えてください。

 

 

 

記述の仕方は以下になります。

セル、いわゆるRangeオブジェクトで使用する場合

 

Rows.Count

 

行の数を求めるときに使います。つまりシートの行数です。

Range(選択範囲).Rows.Countと記述します。

 

Range(選択範囲)の.Rowsを.Countするですね。

例えば

 

Range(“A1:B20”).Rows.Countと記述すれば、.Rowsは「行」を.Countは「数」ということです。

 

 

セルの選択範囲は、A列の1行目からB列の20行目ですから、「行」に着目するわけなので1から20の数「20」が戻り値になるんです。

 

Columns.Count

 

列の数を求めるときに使用します。つまりシートの列数です。

Range(選択範囲).Columns.Countと記述します。

 

Range(選択範囲)の.Columnsを.Countするですね。

 

例えば

Range(“A1:B20”).Columns.Countと記述すれば、.Columnsは「列」を.Countは「数」ということです。

 

 

セルの選択範囲は、A列の1行目からB列の20行目ですから、「列」に着目するわけなので1から2の数「2」が戻り値になるんです。

 

(はてな君)
知りたいのは、その中で使用しているセルの数かも
(えっちゃん)
だね

 

Endプロパティ

 

オブジェクト(Range)で使う.Endプロパティは、データのある終端セルを参照してそのセルの場所を戻り値として返してくれるのです。

 

書式はオブジェクト.End(引数「Direction」)とします。

 

引数「Direction」

 

引数 動作
xlUp 上方向へ移動
xlDown 下方向へ移動
xlToLeft 左方向へ移動
xlToRight 右方法へ移動

 

 

例えば、セルA1から見た下方向のデータのある終端セルを選択するをコードで書くとこうなります。

 

Range(“A1”).End(xlDown).Select

 

 

そうすると、セルのA13が選択されるんです。

 

ちなみにセルB1から見た下方向のデータのある終端セルってどこだと思いますか?

 

(はてな君)
は~い!B13でーす

 

(えっちゃん)
大はずれでーす

 

(はてな君)
え~

 

(えっちゃん)
答えはB4

 

実は、空白セル(”空白文字”は含まない)はデータがない状態なのです

 

そうするとデータがある終端セルを参照するになりますから、B4が答えになるんですね。

 

ちなみにすべてのセルが空白状態のときは、上記のコードでは最終行へ飛んじゃいます。

 

データのないセルの終端は、どこって参照するからです。

 

(えっちゃん)
はてなくん、おまたせ本題だよ!
(はてな君)
うん!

 

上図セルA1の最終行の値をもとめるには

Range(“A1”).End(xlDown).Selectの.Selectの部分を「.Row」に変更するんです。

 

 

RowとColumn

 

RowとColumnは、セル番地の値(整数値)を取得するためのプロパティになります。

 

 

Row

 

例えば、セルA1から見た下方向のデータのある終端セルの行数の値を取得するをコードで書くとこうなります。

 

Range(“A1”).End(xlDown).Row

 

答えは「13」です。

 

(えっちゃん)
じゃ、はてなくん次の問題の答えは?なに

 

では、Range(“B1”).End(xlDown).Rowの答えはなんでしょう?

 

(はてな君)
は~い 「B4」で~す。もしB5に空白文字があったら「B6」で~す!

 

(えっちゃん)
大正解!すごいよ!はてなくん

 

Column

 

例えば、セルA1から見た右方向のデータのある終端セルの行数の値を取得するをコードで書くとこうなります。

 

Range(“A1”).End(xlToRight).Column

そして答えは「2」です。

 

 

ちなみに

Range(“A5”).End(xlToRight).Columnだと

 

(はてな君)
1

 

答えは「1」です。「空白文字」があると変わるけど。

 

(はてな君)
そうなると、空白の次にデータがあると本当の最終行や最終列の値じゃないな~

 

(えっちゃん)
すごいね、そうなんだ!本当の最終行を求める方法があるんだよ!はてなくん

 

(はてな君)
おしえて~

 

最も最後のセルから数える

 

頭から数えてダメなら、おしりから数えてみる!って方法です。

 

(えっちゃん)
はてなくん、なんかわかる?

 

(はてな君)
Rows.CountとかColumns.Countとかつかうんでしょ!

 

(えっちゃん)
すっご~い、その通り!

 

セル番地を操作するために使用するのはCellsプロパティです。

 

 

セルの行数、「Rows.Count」ただし、これではどの行かわかりません。

 

そこで、Cells(Rows.Count,1)とします。

 

ただこれでは、「1列目の行の数」で止まってますので答えは「0」です。数える始点も終点もないからです。

 

そこで、EndプロパティとRowプロパティの登場です。

Cells(Rows.Count,1).End(xlUp).Row

 

説明

 

「1列目の行」の「最終セルからの空白以外の位置」を降順に求めるのでEndプロパティの引数「xlUp」を指定しています。

 

そして、下から求めて見つかった空白以外のセル番地は「Row」ココですよっていうコードになります。

 

こうすると「空白文字」は無しとすれば、最終行を簡単に求めることができるのです。

それでは列の場合はどうなるでしょう。

 

 

セルの列数、「Columns.Count」ただし、これではどの列かわかりません。

 

そこで、Cells(1,Columns.Count)とします。

 

行のときと同じく、ただこれでは、「1行目の列の数」で止まってますので答えは「0」です。数える始点も終点もないからです。

そこでEndプロパティを使用します。

 

今度は「xlToLeft」か「xlToRight」を引数として使用します。

 

1列目のスタートなら迷わず「xlToRight」しかないですが。

 

 

1列目には、それ以上の左は存在しないからですね。

話は戻りますが、今度は「Column」プロパティです。

 

Cells(1,Columns.Count).End(xlToRight).Column

 

説明

 

「1行目の列」の「最終セルからの空白以外の位置」を降順に求めるのでEndプロパティの引数「xlToRight」を指定しています。

 

そして、右から求めて見つかった空白以外のセル番地は「Column」ココですよっていうコードになります。

 

こうすると「空白文字」は無しとすれば、最終列を簡単に求めることができるのです。

 

(はてな君)
じゃさ、本来求めたい最終行の下に、他のデータがあったらどうするの?

 

 

(えっちゃん)
そうだね、上記の方法だとアクティブセルの場所を取得しているだけだからね

 

(はてな君)
ねぇねぇ、えっちゃん、最終行を検索して、そこを最終行に設定するのってどう?

 

(えっちゃん)

 

(はてな君)
こうするの

 

 

最終行を示す「文字」もしくは「数値」を設定する

 

例えば、最終行とか最終列とかをここが最終って指定している箇所よりもそれ以上の場所にデータとか説明文があった場合。

 

そのデータの位置が最終の箇所になるんです。

そうすると、例えば、最終行や最終列を目安にした作業に問題が生じる可能性が出てきます。

 

そこで最終行や最終列に、ここが終わりですよっていう文字や数値を設定するんです。

 

 

そして、検索文を活用して、最終セル番地を取得させてみるんです。

 

最終行や最終列と決めている次のセルに、ここが最終セルですよっていう「暗号文字」を記入する。

 

「ただし「暗号文字」を消すと役に立ちません。

 

「暗号文字」を検索させる

 

例えば、最終行のしたに「この上までが処理データです」よってしたい場合。

その判断を下すセルに「普段使用しない文字列や数値」を「暗号文字」として設定します。

 

 

 

上図は、「Ending」の文字が暗号文字です。つまり、その上までが操作したいエリアの「最終行」だと仮定してのコードになります。

 

 

 

上図のA列19行目までが、操作したい範囲だとします。その操作したい範囲の最終行を取得したいのですよ。

 

そこで「上記のコード」は、前もってシートのセル番地A20に「Ending」を入力しています。

 

 

そうして検索操作で「Ending」を探し、求められたセル番地の行から「-1」を行和させています。

 

そうすると、操作したい範囲の最終行が求めれます。

 

 

こうすると、途中に「空白文字」があろうがなかろうが、必要な最終セルの行が求められるようになるわけです。

 

 

 

 

 

まとめ

 

 

今回は、集計データなど最終行や最終列の求める方法をお伝えしてみました。

 

方法次第で、選択範囲の操作がしやすくなるんです。

 

 

もしよかったら、ぜひお試しください。

 

 

楽しくエクセルをアレンジして今日も、新しい発見をしてみませんか!

自分に合ったお気に入りのスタイルでエクセルを使いこなしていきましょう。

 

ありがとうございました。