ワクワクV036回目レンジのグループと組み合わせ方（2021-04-03）

ほえほえ＠スプシマン @hoehoe1234

ワクワクV036回目レンジの使い方（2021-04-03） 実践的な演習として「買掛金アプリ」を作成していますが、このような典型的な転記モノを作るのに必要なスキルは３つほどあります。 pic.twitter.com/ELRDg94rRR

拡大

ほえほえ＠スプシマン @hoehoe1234

①レンジを自由に操作できること ②ファイルを自由に操作できること（ブック含む） ③モジュール分割（関数分割）ができること となります。エクセルVBAの基礎的な知識、経験は前提ということで除外してあります。 今回は①を目的に講義を行いました。特にCells、Rows、Columnsあたりに注力しました。

ほえほえ＠スプシマン @hoehoe1234

以下、画像、講義内容の時系列が前後することはご容赦ください。さて、Cells、Rwos、Columnsというプロパティはどのようなプロパティなのでしょうか？（以下関数、プロパティ、sub、functionを特に区別せず関数と表記する場合があります）。 pic.twitter.com/3P0Twt0dCQ

拡大

ほえほえ＠スプシマン @hoehoe1234

たとえばCellsは、Cells(i,j)のように書いて引き数i,jで示される特定のセルの内容を取得する関数ではありません。このように理解してしまうとなかなかレンジの本当の使い方に到達できません。かつての私がそうでした。なにか違和感を感じながらも都度検索をしてコピペをしていました。

ほえほえ＠スプシマン @hoehoe1234

Cells、Rows、Columns関数の機能は ①親オブジェクトと同じ範囲の新しいレンジを返す ②ただし、それぞれ内部のスライス（分割単位）は、セル、行、列 となります。Cells(i,j)で特定のセルの値が取得できるのは単にCells.Item(i,j)で単一セルオブジェクトが取得でき、

ほえほえ＠スプシマン @hoehoe1234

かつ、そのオブジェクトのデフォルトプロパティであるValueが呼び出されているに過ぎません。これは中間にあるいくつもの省略が可能であたかもCells(i,j)の関数呼び出しで値が取れるというように見えているにすぎません。

ほえほえ＠スプシマン @hoehoe1234

しかし、この利便性こそエクセルオブジェクトの醍醐味でしょう。なのでRange(参照文字列)とCells(i,j)という枠組みから抜け出して、正しいグルーピングをを元に仕組みを考えることが必要なのだとおもいます。

ほえほえ＠スプシマン @hoehoe1234

黄色で示したCells、Rows、Columnsはたしかに親オブジェクトと同じ範囲を返しますが、スライスが違うのでそれぞれCells(i)、Rows(i)、Coluns(i)は自分自身の文脈にしたがって単一セル、行範囲、列範囲を返します。Cells、Rows、Columnsが同一グループということがわかると思います。 pic.twitter.com/izVNE2prtX

拡大

ほえほえ＠スプシマン @hoehoe1234

複数のレンジを引き数に新しい範囲を生成するグループが ①Range②Union③Intersectとなります。範囲同士の演算のイメージですね。Rangeは2点を引き数に取り新しい範囲を生成します。Unionは複数の範囲を含む新しい範囲を生成します。InterSectは２つの範囲が交差する範囲を生成します。 pic.twitter.com/901DHZh0WT

拡大

ほえほえ＠スプシマン @hoehoe1234

すなわち、これらの第一グーループの特徴は ①複数の範囲を取って ②それらの範囲を演算して新しい包含矩形、和集合、交差集合範囲を生成 します。第二グループのCells、Rows、Columnsとの違いを考えると面白いですね。

ほえほえ＠スプシマン @hoehoe1234

第三のグループがOffset、Resize関数となります。このグループの特徴は ①親オブジェクトのレンジを ②移動、拡大、縮小 することです。これはよく使用されますし、概念的にもw借りやすいかとおもいます。一点気をつけていただきたいのが「範囲は値を含まない」ということです。 pic.twitter.com/RP8CfXBE24

拡大

ほえほえ＠スプシマン @hoehoe1234

範囲は値を含まないので移動、拡大、縮小（以下移動）は、いわばシートに当てている木枠を移動することに相当します。これは単純に「移動」されたワクが新しく生成されるということです。ですからValueを使って値を取ると「そのタイミングで、そのワクを使って値を取得する」ということになります。

ほえほえ＠スプシマン @hoehoe1234

第四のグループがRange(参照文字列)となります。このグループは ①参照文字列で示される範囲を ②実際のレンジ に変換します。ワークシート関数ではIndirect関数がこれに相当しますね。文字列からレンジを生成するという重要な機能を担っています。

ほえほえ＠スプシマン @hoehoe1234

文字列の指定方法により １）単独セル範囲 ２）複数セル範囲(Areas含む場合あり） が生成されます。文字列の形式には "A1"のような単独指定以外にも"A:B"（列指定）、"2:2"（行指定）、"A1:C2"（範囲指定）のような指定ができます。文字列で指定できるのでとても便利ですね。

ほえほえ＠スプシマン @hoehoe1234

この参照文字列にも、Range、Union、InterSectに相当する記法があります。 ①Rangeに相当する記法は「：」 ②Unionは「，」 ③Intersectは「　」（※スペース） となります。①は最もよく使う記法ですね。

ほえほえ＠スプシマン @hoehoe1234

第五のグループとして文脈依存の範囲を返すグループがあります。これらは ①文脈、選択、値、状況に応じた ②特別な範囲 を返します。 CurrentRegion 、SpecialCells 、EntireRow、EntireColumn、End、UsedRange、Selection などが相当します。

ほえほえ＠スプシマン @hoehoe1234

EntireRow/ColumnはOffset/Resizeの第三グループに入れたほうがよいかもしれませんね。ともあれこれでレンジを操作／生成する５つのグループの整理ができました。これらの関数を駆使して狙った範囲を簡潔に指定します。ここまでが「知識」で、ここからどう指定するかが「工夫」となります。

ほえほえ＠スプシマン @hoehoe1234

それではこのような表を元に具体的に５つのグループの関数を使って狙った範囲を選択していきましょう。この表はB3から始まっていますが、コードはA1を基準としているものもあり、そのあたりは適宜よみかえてください。 pic.twitter.com/4tSK6tAZMn

拡大

ほえほえ＠スプシマン @hoehoe1234

演習１　タイトル行（番号～性別）を除いたデータ部分のみを選択してください。Vノック１００にも同様の質問があったかと思います。今回の演習の目的は、さまざまなパターンで範囲を選択することにより「関数のニュアンスと特性」を掴むことです。個々の指定方法に優劣はありません。

ほえほえ＠スプシマン @hoehoe1234

特に、間違って理解されがちなCells、Rows、ColumnsのグループとRange、Union、Intersectのグループの使い方と組み合わせのニュアンスがわかるのではないと思います。

ほえほえ＠スプシマン @hoehoe1234

まずは定番ともいえるOffset関数とResize関数を組み合わせて使います。これはOffsetで１行下に移動してからResizeで１行少なくする作戦ですね。（もちろん、カレントリージョンなので周りは空白行列であり１行短くする必要はないのですが技法確認のためそのようにしています。） pic.twitter.com/2VbjsH43m8

拡大

ほえほえ＠スプシマン @hoehoe1234

これはきれいですね。カレントリージョンの２行目と最終行をRange(開始,終了)を使って範囲矩形を作っています。私はこのパターンには気が付きませんでした。 pic.twitter.com/YvV2TKtBnW

拡大

ほえほえ＠スプシマン @hoehoe1234

この作成は、左上セルと右下セルを指定してRange関数で範囲矩形を生成しています。左上の指定は、１行目をとってきてそれをCellsでセル単位に再分割してその１つ目をとってきています。それぞれItem関数は省略されています。また、右下セルはセル単位に分割後、連番カウントで取得してきています。 pic.twitter.com/tZqpakg1WM

拡大

ほえほえ＠スプシマン @hoehoe1234

crをカレントリージョンとすると、cr.Rowsとすることで、行でスライスした同一範囲が取得できます。同様にcr.Cellsとすることでセルでスライスした同一範囲が取得できます。それぞれ関数形式のように(i)、または(i,j)で指定されていますがこれは省略しなければCells.Item(i)という記法になります。

ほえほえ＠スプシマン @hoehoe1234

これもよいですね。カレントリージョンそれ自身をWith構文の中では指定できないのでCellsを使用しています（カレントリージョンが変数であればそのまま指定可能）。自分自身の範囲とそれを１行下にずらした範囲の交差部分を取得しています。Cellsグループの特徴を生かしたコードだと思います。 pic.twitter.com/Id0gamcTGd

拡大