行数が多いExcelシートを操作する際の集計作業の高速化
以前にVlookup関数の代わりにVBAでDictionaryオブジェクトを使用して高速化する手段をご紹介しました。
この方法はVlookup関数以外の他の関数についても適用することができ、使いこなせるようになるとかなり便利です。
今回はDictionaryオブジェクトを使用してSUMIFS関数で算出される計算の高速化をご紹介します。
SUMIFS関数とは
SUM関数・IF関数を使用される方にとってはSUMIFS関数を使ったことがなくても字面でイメージは湧きやすいと思いますが、SUMIFS関数は「範囲の中で特定の条件に一致するものを合計する」関数です。
私は売上一覧の明細の中から特定の商品の売上を抜き出したり、同じものが何行も含まれている一覧から商品カテゴリごとの売上を算出するのによく使用しています。
SUMIFS関数と似た関数でSUMIF関数というものもあります。
SUMIF関数はSUMIFS関数とよく似ていますが、合致する条件項目を1つしか設定できません。
SUMIFS関数の方が条件設定を複数設定できる分汎用性が高く、私は基本的に条件が1つでもSUMIFS関数を使用しています。
スポンサーリンク
Dictionaryオブジェクト(連想配列)について
Dictionaryオブジェクトは特定のキーに対するアイテムを設定することで、該当のキーに対するアイテムを素早く呼び出す仕組みです。
通常のワークシート関数では行数が増えるごとに処理に時間がかかりますが、Dictionaryオブジェクトを用いた計算では処理時間が大幅に短縮されるのが特徴です。
また、キーは一意のものになりますので重複データから必要なものだけ取り出すときにも便利です。
Dictionaryオブジェクトを用いたSUMIFS関数と同様の処理の高速化
用意したサンプルシート
用意したシートは以下のようなものになります。
シート1はJANコードと品名・包装単位・金額(仮)を記載していて、品名は何度か重複しています。
全体の行数は約27万行、重複除いた品名の種類は約2万行で、重複の削除機能を使ってシート2にJANコードの一覧を用意しています。
処理内容について
今回私が作成したコードは以下のものになります。
’開始時間の記載
Debug.Print “Dictionary開始-” & Time
Dim dicJAN
Set dicJAN = CreateObject(“Scripting.Dictionary”)
’JAN と金額の合計をDictionaryに格納
Dim y, i
With Sheets(1)
y = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To y
’JANコードの登録がない場合、Dictionaryに追加
If Not dicJAN.Exists(.Cells(i, 1).Value) Then
dicJAN.Add .Cells(i, 1).Value, .Cells(i, 5)
’JANコードの登録がある場合、Dictionaryに格納されている値を更新
Else
dicJAN(.Cells(i, 1).Value) = dicJAN(.Cells(i, 1).Value) + .Cells(i, 5)
End If
Next i
End With
’JAN一覧B列に合計金額の出力
With Sheets(2)
For i = 2 To dicJAN.Count + 1
.Cells(i, 2) = dicJAN(.Cells(i, 1).Value)
Next i
End With
’終了時間の記載
Debug.Print “Dictionary終了-” & Time
End Sub
まずシート1のJANコードと金額をDicrionaryオブジェクトに格納します。
Dictionary関数に格納する際、Vlookup関数の代わりの場合は重複分を無視することで同じ挙動になりますが、SUMIFS関数の代わりに使用する場合は以下の条件分岐を用います。
・①JANコードがキーとして登録されていない場合:キー(JANコード)とアイテム(金額)を追加
・②JANコードがキーとして登録されていない場合:キー(JANコード)に登録されているアイテム(金額)に現在の行の金額を追加
処理時間の比較
上記Dictionaryオブジェクトを用いた処理時間と通常通りSUMIFS関数を用いた場合の処理時間について比較してみました。
比較するのに用いたコードは以下の3通りです。
①VBAでWorkSheetFunctionのSUMIFS関数を使用する
’開始時間の記載
Debug.Print “worksheet関数開始-” & Time
Dim y1, y2, i
y1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
With Sheets(2)
y2 = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To y2
.Cells(i, 3) = WorksheetFunction.SumIfs(Range(Sheets(1).Cells(2, 5), Sheets(1).Cells(y1, 5)), Range(Sheets(1).Cells(2, 1), Sheets(1).Cells(y1, 1)), .Cells(i, 1))
Next i
End With
’終了時間の記載
Debug.Print “worksheet関数終了-” & Time
End Sub
②計算式としてSUMIFS関数をセルに格納する
’開始時間の記載
Debug.Print “関数開始-” & Time
Dim y1, y2, i
y1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
With Sheets(2)
y2 = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To y2
.Cells(i, 4) = “=SUMIFS(Sheet1!$E$2:$E$” & y1 & “,Sheet1!$A$2:$A$” & y1 & “,Sheet2!” & Replace(.Cells(i, 1).Address, “$”, “”) & “)”
Next i
End With
’終了時間の記載
Debug.Print “関数終了-” & Time
End Sub
③計算式としてSUMIFS関数をセルに格納するが、1つ目のみ関数を入力、下の行についてはコピペをする
’開始時間の記載
Debug.Print “関数コピペ開始-” & Time
Dim y1, y2, i
y1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
With Sheets(2)
y2 = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(2, 5) = “=SUMIFS(Sheet1!$E$2:$E$” & y1 & “,Sheet1!$A$2:$A$” & y1 & “,Sheet2!” & Replace(.Cells(2, 1).Address, “$”, “”) & “)”
.Cells(2, 5).Copy
Range(.Cells(3, 5), Cells(y2, 5)).PasteSpecial
End With
’終了時間の記載
Debug.Print “関数コピペ終了-” & Time
End Sub
処理時間の違いは以下の表の通りです。
Dictionary | ①WorkSheetFunction | ②計算式の入力 | ③計算式コピペ | |
---|---|---|---|---|
1回目 | 0:00:03 | 0:21:14 | 0:21:09 | 0:01:57 |
2回目 | 0:00:04 | 0:21:02 | 0:21:09 | 0:01:56 |
3回目 | 0:00:03 | 0:21:04 | 0:21:05 | 0:01:54 |
平均 | 0:00:03 | 0:21:07 | 0:21:08 | 0:01:56 |
Dictionaryオブジェクトを用いることで処理が圧倒的に高速になりました。
関数自体のコピペも遅くはないですが、もっと条件分岐を複雑にすれば差はより大きく広がります。
WorkSheetFunctionなどは行数が増えると処理にかなり時間がかかることから、ある程度たくさんの行を扱う業務では使いにくいと思います。
ピポットテーブルも方法としてはありですが、そのあとの加工がやりにくい、フォーマットが自由に設定できないなどがあるため、今回のように単純に関数の代わりに高速処理ができるVBAのコードは必要な場面も多いと思います。
コメント