photo credit: AndreyFilippov.com Set of red cups Danilovsky Market, Moscow, Russia via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでバラバラの経費精算書からデータを集めるマクロの作り方について解説をしています。
前回の記事はコチラです。
Do While～Loop文の使い方についてお伝えしました。
ですが、まだそれを使ったデータの転記のルーチンを作成していませんでしたので、その部分を今回作成していきたいと思います。
また、繰り返しを使って２つのシート間で転記処理を作る際に気をつけるポイントについても紹介しますね。
ということで、エクセルVBAで繰り返しを使ってデータを転記する方法、またその際の２つのポイントについてです。
では、行ってみましょう！
前回のおさらい
社内の太郎さんという方から受け取った以下のようなエクセルファイル「
201807経費精算書_経費_3001.xlsx」があります。
これを以下の「経費データ」シートの該当する列に、それぞれコピペするというプログラムを作りたいのです。
なお、この「経費データ」シートのオブジェクト名は「wsData」に変更してあるとします。
それで前回、Do While～Loop文による繰り返しを使うといいよ、ということで以下のような書き方を紹介しました。
カウント変数 = 最初の行数
Do While Worksheetオブジェクト.Cells(カウント変数, 列数).Value <> ""
'繰り返したい処理
カウント変数 = カウント変数 + 1
Loop
今回は、これをベースに、経費精算書からデータを転記するプロシージャを作成していきます。
そこで、シートからシートにデータを転記する際のポイントを２つほど紹介しますね。
カウント変数を２つ用意する
カウント変数1つで構成したプロシージャ
まず、以下のようなプロシージャを作ってみました。
Sub 経費精算データ取り込み()
Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)
Dim i As Long
i = 12
Do While ws.Cells(i, 1).Value <> ""
wsData.Cells(i - 10, 1).Value = ws.Range("G4").Value '1 対象月
wsData.Cells(i - 10, 2).Value = ws.Cells(i, 1).Value '2 日付
wsData.Cells(i - 10, 3).Value = "" '3 部署No
wsData.Cells(i - 10, 4).Value = ws.Range("G6").Value '4 部署
wsData.Cells(i - 10, 5).Value = ws.Range("G8").Value '5 社員No
wsData.Cells(i - 10, 6).Value = ws.Range("G7").Value '6 氏名
wsData.Cells(i - 10, 7).Value = ws.Cells(i, 2).Value '7 科目
wsData.Cells(i - 10, 8).Value = ws.Cells(i, 5).Value '8 摘要
wsData.Cells(i - 10, 9).Value = ws.Cells(i, 6).Value '9 金額
wsData.Cells(i - 10, 10).Value = ws.Cells(i, 7).Value '10 備考
i = i + 1
Loop
End Sub
カウント変数iは、太郎さんの経費精算書のデータを走査する際の行数を表すもので、経費精算書のデータが12行目からスタートしていますので、初期値は12です。
それで転記先は「経費データ」つまりwsDataの2行目からになります。
いずれの行数も繰り返しをするたびに1ずつ増やしていくので、カウント変数iの値と、転記先行数の関係は以下の表ようになります。
| i | 転記先の行数 |
|---|---|
| 12 | 2 |
| 13 | 3 |
| … | … |
| 15 | 5 |
だから、転記先のwsDataシートのCellsで指定する行数を「i-10」としているわけですね。
ただ…正直これはちょっと分かりづらい。「-10」ってなんじゃ？って思いましたでしょ。
また、どちらかのシートの構成が変わったときに「-10」の箇所をすべて修正する必要があるので大変です。
カウント変数2つで構成したプロシージャ
なので、wsDataにはwsData用のカウント変数を別に用意してあげます。
つまり、このようにします。
Sub 経費精算データ取り込み()
Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)
Dim i As Long: i = 12
Dim di As Long: di = 2
Do While ws.Cells(i, 1).Value <> ""
wsData.Cells(di, 1).Value = ws.Range("G4").Value '1 対象月
wsData.Cells(di, 2).Value = ws.Cells(i, 1).Value '2 日付
wsData.Cells(di, 3).Value = "" '3 部署No
wsData.Cells(di, 4).Value = ws.Range("G6").Value '4 部署
wsData.Cells(di, 5).Value = ws.Range("G8").Value '5 社員No
wsData.Cells(di, 6).Value = ws.Range("G7").Value '6 氏名
wsData.Cells(di, 7).Value = ws.Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = ws.Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = ws.Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = ws.Cells(i, 7).Value '10 備考
i = i + 1: di = di + 1
Loop
End Sub
wsData用のカウント変数「di」を用意しました。
変数名は「i」に次いで「j」を使う場合が多いですが、今回は「wsData」用ということで「d」をつけて「di」としてみました。
カウント変数名の付け方として「役割を表す文字 & i」という作り方も覚えておくと良いと思います。
diも1ずつ増やさないと正しく動作しませんので、Doブロック内に「di = di + 1」を忘れずに！
さて、こちらのプロシージャのほうが、それぞれのカウント変数の役割がはっきり見えますし、すっきりしていますよね。
また、どちらかのシートに変更が加わって開始行が変更になっても、それぞれの初期値を変更すれば対応ができるので修正が楽です。
複数のステートメントを一行に記述する
先程のプロシージャ内の、6,7行目や19行目にある「:」ですが、補足しておきますね。
VBAでは記号「:」を使うことで、複数のステートメントを連続して一行内に記述することができます。
今回のように、同じ系統のステートメントを一行にまとめてしまったほうが、可読性が上がることがあります。
ただ、一行が長くなりすぎると見づらくなるので、その点は注意ですね。
繰り返さなくてよい処理をブロックの外に出す
さて、もう一点ポイントがあります。
先程のプロシージャですが、9行目と12～14行目を見てください。
これらの右辺は、太郎さんの経費精算書の特定のセルから固定で引っ張ってきている値ですから、Doループの繰り返しの何回目であったとしても変わりませんよね。
ただ、VBAの処理としては、毎回のように「wsシートのG4セルの値を取り出す」という処理が行われてしまっています。
これをループの外に出してしまいましょう。
Sub 経費精算データ取り込み()
Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)
Dim month As Date: month = ws.Range("G4").Value
Dim departmentName As String: departmentName = ws.Range("G6").Value
Dim staffId As Long: staffId = ws.Range("G8").Value
Dim staffName As String: staffName = ws.Range("G7").Value
Dim i As Long: i = 12
Dim di As Long: di = 2
Do While ws.Cells(i, 1).Value <> ""
wsData.Cells(di, 1).Value = month '1 対象月
wsData.Cells(di, 2).Value = ws.Cells(i, 1).Value '2 日付
wsData.Cells(di, 3).Value = "" '3 部署No
wsData.Cells(di, 4).Value = departmentName '4 部署
wsData.Cells(di, 5).Value = staffId '5 社員No
wsData.Cells(di, 6).Value = staffName '6 氏名
wsData.Cells(di, 7).Value = ws.Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = ws.Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = ws.Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = ws.Cells(i, 7).Value '10 備考
i = i + 1: di = di + 1
Loop
End Sub
私達の体感としてはあっという間に思えていたとしても、WorksheetオブジェクトやRangeオブジェクトへの読み書きの処理は、計算時間としては比較的遅めの処理なんですね。
それよりも変数への読み書きのほうが高速です。
なので、シートやセルへのアクセスをする処理は、できる限り繰り返しの中から出してしまったほうが計算時間がエコですみます。
ちょっと行数は増えてしまいましたが、変数名も気をつければ可読性も失われずに済むと思います。
さて、上記プロシージャを実行すると、以下のように太郎さんの経費データをすべて転記できるようになります。
まとめ
以上、エクセルVBAで繰り返しを使ってデータの転記をするときの２つのポイントについてお伝えしました。
以下２点、参考にしていただければと思います。
- 転記元と転記先でそれぞれ別のカウント変数を使用する
- ループ内でなくてよい処理をループの外に出す
また、変数名にも工夫のしどころがありますね。ぜひ、気にしながらプログラミングをしてみてください。
次回はWithを使ってプログラムをスッキリ書く方法を解説しています。
どうぞお楽しみに！
連載目次：エクセルVBAで経費データをデータベースに集約する
請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。- 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
- 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
- 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
- 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
- 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
- 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
- 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
- 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
- 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
- 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
- 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
- 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
- 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
- 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
- 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
