Excel VBA。大量のデータから条件付き合算値を出す方法。アレコレ。
＜Excel VBA＞
大量のデータを処理するには、Excel VBAだから、繰り返し処理で対応すると意外と処理時間がかかることが多々あります。楽に処理するだけでなく、できれば、処理時間も短時間で行えればいいですよね。
今回は、12万件のデータを用意しました。
12万件のデータは読み込み済みですが、本来は読み込んだ後に計算したいわけです。
すると、SUMIF関数をその都度作るのは面倒なので、読み込んだ後に計算させたいという作業イメージだとします。
このデータのA列には、新宿・渋谷・池袋・品川の店舗ごとの金額を集計するマクロを考えてみましょう。
最初は、Excel VBAなので、繰り返し処理で考えた場合どうなるのでしょうか？
Sub 条件付き計算01()
Dim i As Long
Dim j As Long
Dim Ans As Long
Dim EndRow As Long
Dim shop_name As String
EndRow = Range("a1").End(xlDown).Row
For j = 2 To 5
shop_name = Cells(j, 5)
For i = 2 To EndRow
If Cells(i, 1) = shop_name Then
Ans = Ans + Cells(i, 2)
End If
Next
Cells(j, 6).Value = Ans
Ans = 0
Next
End Sub
簡単に説明をしておきます。
EndRow = Range("a1").End(xlDown).Row
これはお馴染みの行ですね。最終レコードは何行目なのか知るための行ですね。
For j = 2 To 5
shop_name = Cells(j, 5)
Next
これは、新宿から品川まで4回繰り返すためのものですね。
For i = 2 To EndRow
If Cells(i, 1) = shop_name Then
Ans = Ans + Cells(i, 2)
End If
Next
For文は、最終レコードまで繰り返し処理を行います。今回は12万回ですね。
If文は、A列のセルが店舗名、例えば新宿と同じだったら、B列のデータを変数に代入して加算するというのを繰り返しています。
Cells(j, 6).Value = Ans
加算された計算結果を表示させます。
いたってシンプルな繰り返し処理です。これを実行してみましょう。
算出することができましたが、実際に処理をすると、処理しているのがわかるぐらい結果が表示されるまで時間がかかります。
さらに大量なデータだった場合は、処理時間も比例してかかるようになります。
原因は繰り返し処理の回数ですね。12万件を4回繰り返しています。
ただ、Excel VBAを勉強しはじめだと、繰り返し処理というイメージが強くなるので、このような方法をイメージする方が多いようです。
しかし、そもそもExcelなので、Excelの数式を使う方法もあります。
次のような構文でも、算出することができます。
Sub 条件付き計算02()
Range("g2").Value = "=SUMIF(A:A,E2,B:B)"
Range("g3").Value = "=SUMIF(A:A,E3,B:B)"
Range("g4").Value = "=SUMIF(A:A,E4,B:B)"
Range("g5").Value = "=SUMIF(A:A,E5,B:B)"
End Sub
単純ですね。
SUMIF関数の数式を直接回答させたいセルに作っちゃおうという考え方ですね。
実行してみると問題なく算出してくれます。
これでいいと思えますが、処理速度。
レスポンスタイムは先ほどの繰り返し処理よりも早いですが、それでもちょっと時間がかかります。
それに、今回のように簡単な数式だったので、作りやすいのですが、もっと煩雑な式だと作るのが大変になってしまいます。
そこで、『WorksheetFunctionプロパティ』を使うと処理速度が大幅に改善することができます。
次の構文は、WorksheetFunctionプロパティを使った場合です。
Sub 条件付き計算03()
Range("h2").Value = WorksheetFunction.SumIf(Range("a:a"), "新宿", Range("b:b"))
Range("h3").Value = WorksheetFunction.SumIf(Range("a:a"), "渋谷", Range("b:b"))
Range("h4").Value = WorksheetFunction.SumIf(Range("a:a"), "池袋", Range("b:b"))
Range("h5").Value = WorksheetFunction.SumIf(Range("a:a"), "品川", Range("b:b"))
End Sub
WorksheetFunctionプロパティでSumIf関数を使った場合です。
実行すると、瞬く間に処理をしてくれます。
WorksheetFunctionプロパティは、すべてのワークシート関数を使えるわけではありませんが、処理時間を考慮すると、繰り返し処理にこだわらなくても、いいように思えますので、機会があれば、WorksheetFunctionプロパティで検討してみるといいかもしれませんね。
