VBAによるデータの整理

データの整形、整理

前回はこのように一か月分のデータを取得しました。

何カ月分ものデータをこのように整理して保存しましょう。

1月のすぐ下に2月のデータが来るようにします。すると日付も、年月がないとわけが分からなくなります。さらに通し番号で整理したいので「年」「月」「日」を一つにまとめた列も作ります。

今回は地味で細かな作業の繰り返しになりますので色をつけて説明します。
プログラム全体の構成としては
For m = 1 to 12
　2008年m月のデータをSheet2に取得
　If m = 1 then
　　上部の項目名をSheet1へコピー
　End If
　m月のデータをSheet1へコピー
Next m
となります。

WebからSheet2へデータを取得して、それをSheet1に整理することにしましょう。

まず最初に、データの項目の説明が必要ですので、Sheet2の黄色い部分、つまり2行目から5行目、B列からU列(2列から21列)を、 Sheet1の1行目から4行目、E列目からX列目(5列目から24列目)へコピーします。
次に、毎月、日にちとデータの部分、つまりSheet2のオレンジ色の部分、の6行目からデータの最後の行まで、A列目からU列目(1列から21列)を、 Sheet1の前月のデータの次の行以降、D列目からX列目(4列目から24列目)へコピーします。
それと同時に、Sheet1のA1セルに書かれた年と月を、Sheet1に先ほど貼り付けたデータの2列目と3列目へコピーします。
最後に、日付の通し番号として、Sheet1のB,C,D列を/で繋げてA列に書きます。

シートの選択とシートを指定してのCells

Webクエリによる取得は、選択しているシートに貼り付けられますので、Webクエリを実行しておく前にデータを貼り付けたいシートを手前に出しておきます。
そのためには、Sheets(1).Selectのように、括弧の中に何番目のシートなのか番号を書いて指定します。あるいはSheets("Sheet1").Selectのようにシートの名前を指定することもできます。
また、シートの間を跨いでのコピーは、例えば今回のようにシート2のB2セルをシート1のE1へコピーするなら
Sheets(1).Cells(1, 5) = Sheets(2).Cells(2, 2)
と書きます。

このように複数のシートに跨るプログラムは、必ず標準モジュールに書いてください。

標準モジュールがない場合は挿入(I)標準モジュール(M)をクリックして追加してください。

データの数を数える

Webから取得したデータの個数は、その月の日数により変化します。これが分からないと、シート2から何行をシート1にコピーするか、そしてシート1の何行目までデータが入っているか分からないので調べる必要があります。今回のデータは日数なので日付A列の最大値がデータの個数ですが、一般にはこのような通し番号が付いていないこともあるので、データを上から見ていって、無くなるまで数えます。そのためにはWhile文を使います。
While文の書式は

Do While 条件
　文1
　文2
　…
Loop

です。条件が成立している間、Do WhileとLoopで挟まれた文を実行します。
シート2のデータであればCells(6, 1)から順にCells(7, 1), Cells(8, 1),…と見ていって、データが無くなるまで続けます。
但し、(6, 1)から始まるのは紛らわしいので、まず
Cells(6, 1).Select
を実行してCells(6, 1)を選択しておくと
Selection.Cells(1, 1)がCells(6, 1)
Selection.Cells(2, 1)がCells(7, 1)
…
ということになってi番目のデータがSelection.Cells(i, 1)となって分かりやすいです。
以上をまとめて

j = 1　'←jを他のループで使っている場合は別の変数を使ってください。
Cells(6, 1).Select
Do While Selection.Cells(j, 1) <> ""　'←<>は大きいか小さい、つまり等しくないことを表し、""は何もないことを表します。つまり<> ""は「空白ではない」という意味です。
　j = j + 1
Loop

によって、データの個数を数えることが出来そうです。本当に数えることが出来たか確かめるために、このプログラムの次の行に

MsgBox (j)

と書いて実行してみてください。31と表示されましたか？違っていたら原因と対策を考えてください。

他にも

Do Until 条件
　文
Loop
とか
Do
　文
Loop While 条件
とか
Do
　文
Loop Until 条件

があります。条件が文より下にあると、少なくとも一度は実行されます。文を実行しないと条件がチェックできない場合に使います。

データのコピー先

シート2のデータをシート1にコピーする場合、コピー元は毎月6行目からなのでCells(1,1)から順にコピーすれば良いのですが、 コピー先は最初の4行が項目名なので1月は5行目から、2月は5+31=36行目から…となるので位置が変動します。
従って、データが既に何行目まで記録されているかを示す変数(例えばlastのような名前)を使って、次のデータをどこにコピーすれば良いのかを管理しておきます。
例えば1月分のデータをコピーする場合は

このようになっているので5行目から、
2月分の場合は

36行目からですが、この何行目、というのを目で見て数えるのではなく、変数に記録しておくということです。

課題

第一問:このような形で1年分のデータを収集するプログラムを書いてください。

第4行の「日付」とか「年」はコピーするのではなくて

Cells(4, 1) = "日付"
Cells(4, 2) = "年"

のように""で囲った文字列を代入します。A列の日付はB,C,D列を繋げますので

Cells(j, 1) = Cells(j, 2) & "/" & Cells(j, 3) & "/" & Cells(j, 4)

と書けばよいでしょう。

B列, C列の年月はSheets(2)の(1,1)セルから切り出すなら
Mid(Sheets(2).Cells(1, 1), 5, 4)
Mid(Sheets(2).Cells(1, 1), 10, 2)
のようにMid関数を使って、何文字目から何文字、という取り出し方でもいいですが、単純にWebから取得するときに指定した年, 月を表す変数の値を書いて構いません。

第二問:2007年1月から2008年12月までの2年分のデータを収集するプログラムを書いてください。これが出来ればどんなに長い期間のデータでも同様に収集できます。