それでは、前回の①「個人シート(sheet)のアンケート結果をサマリーシート(sheet)の一覧表に転記する」 処理を元データの変更に強いプログラムにしていきましょう。
考え方の順番としては以下の形になると思います。
1) 繰り返すプログラムの書き方を覚える
2) シート(sheet)の数だけという条件の書き方を覚える
3) 繰り返すたびにシート(sheet)を指定する方法を覚える
4) シート名を取得する方法を覚える
5) 繰り返すたびに転記するセル(Cell)を移動する方法を覚える
Contents
1) 繰り返すプログラムの書き方を覚える
これまでは、処理が上から下に降りていくだけのプログラムを書いてきましたが「繰り返す」という事は、”同じ行を何回も通る”という事を意味しています。違う表現をすると、プログラムの処理が”上に戻る”という事です。
ただ、このまま何もせずに勝手に繰り返してくれるわけではありません。プログラムらしく「ここからここまでを何回繰り返してください」という書き方があります。以下がその書き方(構文と言います)です 。
Visual Basic
For i = 初期値 To 終了値
ここに繰り返す処理を記述する
Next i
例えば、以下のような書き方をします。
Visual Basic
Sub sample()
‘整数の入るAという箱を作った
Dim A As Integer
‘Aに0を代入した
A = 0
‘1から10まで繰り返す
For i = 1 To 10
‘Aに1を足す
A = A + 1
‘ここまで繰り返す(iを1増加させる)
Next i
‘メッセージダイアログで結果を表示させる
MsgBox A
End Sub
上から見ていきましょう。
「Dim 〇 as ■」はある塊を入れる箱を作るときの決まり文句でしたね。今回はAという箱を用意しています。そしてどんな塊を入れるかというと”Integere(整数)”の塊です。1とか2とか1000などをひとつ入れることが出来ます。今回は「A=0」という事で、まずは初期化という意味で「０」を代入しています。
ここからが繰り返しの処理です。今回は「1から10まで繰り返す」という意味になり、繰り返す範囲はFor～Nextです。繰り返す処理は「A=A+1」ですので、Aに1を足して、Aに入れなおすという処理ですから、実質、毎回プラス1していることと同じです。
最後の「MsgBox ■」ですが、ダイアログを表示する書き方です。今回の■にあたる部分は「A」なので、1を10回足されたものですので、ダイアログには10と表示されます。
どうでしょうか。繰り返し処理の書き方のルールはイメージ出来たでしょうか。
この繰り返し処理の書き方を使ってシート(sheet)やセル(Cell)を操作するプログラムをどのように記述していけばよいかを考えていくことになります。
2) シート(sheet)の数だけという条件の書き方を覚える
とりあえず、For~nextの中に前回のプログラムを入れてみましょう。
Visual Basic
Sub sample()
‘1から■まで繰り返す
For i = 1 To ■
‘山田さんの転記
Worksheets(“サマリー”).Range(“C4”).Value = “山田” ‘追加
Worksheets(“サマリー”).Range(“D4”).Value = _
Worksheets(“山田”).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E4”).Value = _
Worksheets(“山田”).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F4”).Value = _
Worksheets(“山田”).Range(“D7”).Value
‘田中さんの転記
Worksheets(“サマリー”).Range(“C5”).Value = “田中” ‘追加
Worksheets(“サマリー”).Range(“D5”).Value = _
Worksheets(“田中”).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E5”).Value = _
Worksheets(“田中”).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F5”).Value = _
Worksheets(“田中”).Range(“D7”).Value
‘鈴木さんの転記
Worksheets(“サマリー”).Range(“C6”).Value = “鈴木” ‘追加
Worksheets(“サマリー”).Range(“D6”).Value = _
Worksheets(“鈴木”).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E6”).Value = _
Worksheets(“鈴木”).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F6”).Value = _
Worksheets(“鈴木”).Range(“D7”).Value
‘ここまで繰り返す(iを1増加させる)
Next i
End Sub
ただこれでは、For～nextの中で3人分記述してしまっているので、意味がないですよね。なので一人分の処理×3回という書き方にしてみましょう。残すのは誰の処理でもよいのですが、初めに記述している山田さんの処理だけ残します。
そして、Forの■部分についてですが、今回３人分ですから「3」に変換したいところなのですが、それだと従業員数が増えた場合にプログラムの修正が入ってしまうのは、前回、説明したのを覚えていますでしょうか。退職したり中途採用したり従業員数は常に変化があるので、その月の人数は固定じゃないですよね。
では、何回にすればよいかという話になるのですが、結論としては「個人シート数」繰り返せばよいのではないでしょうか。従業員の人数分の個人シートがあるはずです。従業員数が増えれば個人シート数も増えますし、従業員数が減れば個人シート数も減ることになります。
ただ「シート数」を指定する方法が分かりませんね。ここが新しい学習ポイントです。ここで覚えていただきたいのは「Sheets.Count」という書き方です。
上記のように書くと現在のブック(Book)にあるシート数を指定したことになります。ただ、ひとつ問題なのは、今回のサマリーのブック(Book)にはサマリーシート (sheet) +個人シート (sheet) ×3の４シート(sheet)あるので、少し細工が必要です。反映したプログラムを以下に記述します。
Visual Basic
Sub sample()
‘1から3まで繰り返す
For i = 1 To Sheets.Count–1
‘山田さんの転記
Worksheets(“サマリー”).Range(“C4”).Value = “山田” ‘追加
Worksheets(“サマリー”).Range(“D4”).Value = _
Worksheets(“山田”).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E4”).Value = _
Worksheets(“山田”).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F4”).Value = _
Worksheets(“山田”).Range(“D7”).Value
‘ここまで繰り返す(iを1増加させる)
Next i
End Sub
細工の部分は「 Sheets.Count-1」の部分です。単純に4シート(sheet)から1引いて3シート(sheet)分にしているだけです。このような微調整はプログラムを記述していると頻繁に出てくるので覚えておくと良いかもしれません。
これで”山田さんのアンケート結果を３回繰り返す”プログラムが出来ました。ちなみに、このプログラムを実行すると、全く同じことを３回繰り返すだけなので、以下の結果の通り、１行だけ追加された形で終わりです。
3) 繰り返すたびにシート(sheet)を指定する方法を覚える
次に意識すべきは、繰り返しが行われ1回目から2回目になった時に、山田シート(sheet)から、隣の田中シート(sheet)の指定に変わればいいわけです。この指定方法を学びましょう。
シート(sheet)の指定方法は、実はひとつではありません。これまではシート名を文字列で指定していました。「Worksheets(“サマリー”)」のような形です。他の指定方法として「何シート目か」という指定方法があります。つまり整数で指定します 「Worksheets(1)」という書き方です。”サマリー”は左から1番目のシート(sheet)なので、同じ意味になります。右に行くほど数が増えていくわけです。
そして、繰り返すたびに次のシートに移るということは、整数が変化しなければいけません。先ほどのような「Worksheets(1)」という書き方では、常に1番目しか指定しないので結果は同じです。ではどうすればいいのでしょうか。
繰り返すたびに整数が変わるものをカッコ”()”の中に入れられればいいのですが、そんな都合のいいものがあるでしょうか。
すいません。見つけてしまいました。「i」です。「For i = 0」の「i」です。
実は、これまで1から10までと記載してきましたが、正確には「iの値が1から10まで」と書くのが正確な書き方になります。ループを繰り返すたびに「i」という変数(箱でしたね)の中に繰り返した整数が格納されるようになっています。
しつこいようですが、1回目なら「i」は１、2回目なら「i」は2、という事です。この「i」をシートの順番を指定する整数として指定してあげれば、毎回選択されるシートが変わっていくことになります。
それでは、変更結果を見ていきましょう。
Visual Basic
Sub sample()
‘1から3まで繰り返す
For i = 1 To Sheets.Count–1
‘山田さんの転記
Worksheets(“サマリー”).Range(“C4”).Value = “山田” ‘追加
Worksheets(“サマリー”).Range(“D4”).Value = _
Worksheets(i+1).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E4”).Value = _
Worksheets(i+1).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F4”).Value = _
Worksheets(i+1).Range(“D7”).Value
‘ここまで繰り返す(iを1増加させる)
Next i
End Sub
「Worksheets(“山田”)」 の部分が 「Worksheets(i+1)」に変更されています。「i+1」の微調整は前回の「Sheets.Count-1」と同じです。 山田シート(sheet)は2番目ですが、「i」は1から始まりますので1足すことで調整しています。
4) シート名を取得する方法を覚える
先ほどのシート(sheet)の指定方法の考え方である「固定で指定している部分に対して”変数”を使って動的に値を変えていく」という考え方は、他の指定項目についても同様なアプローチとなります。
上記の変更プログラムでも、まだ固定でしているところがいくつかありますが、今度はどこを変更するのがよいでしょうか。
この行に注目してみてください。ここは一覧表へ名前を転記する処理になるので、このままだと毎回「山田」と転記されてしまいます。この「山田」を変数化する必要がありそうです。
ここも新しい内容ですので、まずは答えをお見せします。
「Sheet(i+1).Name」という部分を変更しました。ここも変数「i」が絡んでいます。この書き方は「シート(sheet)のi+1番目のシート名」を指定している書き方です。先ほど書いた通り山田シート(sheet)は2番目なので、「For i = 1」なので「+1」が必要になるわけです。
今回の変更を加えたプログラムを見てみましょう。
Visual Basic
Sub sample()
‘1から3まで繰り返す
For i = 1 To Sheets.Count–1
‘山田さんの転記
Worksheets(“サマリー”).Range(“C4”).Value = Sheet(i+1).Name
Worksheets(“サマリー”).Range(“D4”).Value = _
Worksheets(i+1).Range(“D5”).Value
Worksheets(“サマリー”).Range(“E4”).Value = _
Worksheets(i+1).Range(“D6”).Value
Worksheets(“サマリー”).Range(“F4”).Value = _
Worksheets(i+1).Range(“D7”).Value
‘ここまで繰り返す(iを1増加させる)
Next i
End Sub
こちらを実行すると、どうなるでしょうか。答えは以下の通りです。
1行目に最後の鈴木さんのアンケート結果が出力された状態になります。これは、各個人シート(sheet)の内容は書いているのですが、集計結果の表に記述するセル(Cell)の位置が固定のままになっているので、毎回上書きしている結果、最後の鈴木さんの内容だけ残っているわけです。
そうなれば、次にやるのは、そのセル(Cell)の移動について考えていきたいところですが、長くなってしまったので、次回にしましょう。
