大量データにおける処理方法の速度王決定戦

公開日：2016-08-14　最終更新日：2016-09-08

大量データにおける処理方法の速度王決定戦

VBAで自動化したが、大量データ処理に時間がかかってしまう・・・
そんな悩みが非常に多いようです、
そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。
つまり、処理方法の速度王決定戦です。

検証する題材としては、最も一般的な集計で行います。
集計は、エクセルらしい処理の代表といえるものでしょう。

以下のようなデータ集計を行います。

キーとして、A1～A1000まで、データとして、1～1,000まで、これが、30回繰り返されて、合計30,000行です。
これが、シート「データ1」です。
これを、1,000*30と表記するとして、
「データ1」：1,000*30 ・・・キー1,000件の30,000行データ
「データ2」：10,000*3 ・・・キー10,000件の30,000行データ
「データ3」：10,000*30 ・・・キー10,000件の300,000行データ
「データ4」：100,000*3 ・・・キー100,000件の300,000行データ
以上の4パターンです。

これを、以下のように集計します。

キーごとにデータを集計します。
規則としては、

・出力順は問わない
・元のシートには変更を加えない
・追加したシートは削除しておく

以上の範囲内で、10通りの処理方法で、VBAを記述します。
VBAコードは、各処理方法としては速度を意識して書いたつもりです。

1.ワークシート関数を使った基本 1
2.ワークシート関数を使った基本 2
3.AdvancedFilter + SumIf 1
4.AdvancedFilter + SUMIF 2
5.ピボット + コピペ
6.配列のみで 1
7.配列のみで 2
8.Dictionary + 配列
9.Sort + 配列
10.ADO + SQL

先の4パターンのデータについて、
上記の10通りの処理時間を3回ずつ計測し平均時間を使います。
集計という題材であれば、一般的なパターンとしてはこれくらいでしょうか。
※「データ3」と「データ4」は、速度上位の処理だけ実施しました。

VBAコードの解説はほとんどしていませんので、
VBAコードについて分からない部分は、別途に調べて下さい。

検証用メインのプロシージャ


  
    
      Option Explicit


      Private ws1 As Worksheet

      Private ws2 As Worksheet

      Private sTimer As Single


      Sub sample()

      　　Dim i As Long

      　　Set ws2 = Worksheets("結果")

      　　Set ws1 = Worksheets("データ1")

      　　Application.ScreenUpdating = False

      　　Application.Calculation = xlCalculationManual

      　　For i = 1 To 3

      　　　　Call sample1

      　　　　Call sample2

      　　　　Call sample3

      　　　　Call sample4

      　　　　Call sample5

      　　　　Call sample6

      　　　　Call sample7

      　　　　Call sample8

      　　　　Call sample9

      　　　　Call sample10

      　　Next

      　　Application.StatusBar = False

      　　Application.Calculation = xlCalculationAutomatic

      　　Application.ScreenUpdating = True

      End Sub

データのシート名を
「データ1」「データ2」「データ3」「データ4」
それぞれに変更して実行します。
イミディエイトウィンドウに出力されたタイムを処理ごとに平均します。
正確を期すため、1回実行したらExcelを一旦終了してから再実行しています。

1.ワークシート関数を使った基本 1


  
    
      'ワークシート関数を使った基本 1

      Sub sample1()

      　　Dim i As Long

      　　Dim j As Long

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　On Error Resume Next

      　　For i = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

      　　　　j = WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Columns(1), 0)

      　　　　If Err.Number = 0 Then

      　　　　　　ws2.Cells(j, 2) = ws2.Cells(j, 2) + ws1.Cells(i, 2)

      　　　　Else

      　　　　　　Err.Clear

      　　　　　　j = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row + 1

      　　　　　　ws2.Cells(j, 1) = ws1.Cells(i, 1)

      　　　　　　ws2.Cells(j, 2) = ws1.Cells(i, 2)

      　　　　End If

      　　Next

      　　Debug.Print Timer - sTimer

      End Sub

まずは、VBAの基本ともいえる処理方法です。
WorksheetFunction.Matchを使い、出力済のキーかどうかを判断し、
新規なら、キーを追加、既にあれば加算

2.ワークシート関数を使った基本 2


  
    
      'ワークシート関数を使った基本 2
Sub sample2()

　　Dim i As Long

　　Dim j As Long

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

　　sTimer = Timer

　　For i = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

　　　　If WorksheetFunction.CountIf(ws2.Columns(1), ws1.Cells(i, 1)) = 0 Then

　　　　　　j = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row + 1

　　　　　　ws2.Cells(j, 1) = ws1.Cells(i, 1)

　　　　　　ws2.Cells(j, 2) = ws1.Cells(i, 2)

　　　　Else

　　　　　　j = WorksheetFunction.Match(ws1.Cells(i, 1), ws2.Columns(1), 0)

　　　　　　ws2.Cells(j, 2) = ws2.Cells(j, 2) + ws1.Cells(i, 2)

　　　　End If

　　Next

　　Debug.Print Timer - sTimer

End Sub

1.ワークシート関数を使った基本 1の応用的な書き方です。
On Error Resume Nextは、出来れば使いたくないものです。
WorksheetFunction.CountIfを使い、出力済のキーかどうかを判断しています。
新規なら、キーを追加、既にあればMatchで行を探し加算

3.AdvancedFilter + SumIf 1


  
    
      'AdvancedFilter + Sumif 1

      Sub sample3()

      　　Dim i As Long

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _

      　　　　　　　　　　　　　　　　　CopyToRange:=ws2.Range("A1"), _

      　　　　　　　　　　　　　　　　　Unique:=True

      　　For i = 2 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

      　　　　ws2.Cells(i, 2) = WorksheetFunction.SumIf(ws1.Columns(1), _

      　　　　　　　　　　　　　　　　　　　　　　　　　　ws2.Cells(i, 1), _

      　　　　　　　　　　　　　　　　　　　　　　　　　　ws1.Columns(2))

      　　Next

      　　Debug.Print Timer - sTimer

      End Sub

オートフィルタの詳細設定である、AdvancedFilterを使い一気にキーをユニーク化しています。
集計は、WorksheetFunction.SumIfを使っています。

4.AdvancedFilter + SUMIF 2


  
    
      'AdvancedFilter + Sumif 2

Sub sample4()

　　Dim i As Long

　　Dim rng As Range

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

　　sTimer = Timer

　　ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _

　　　　　　　　　　　　　　　　　CopyToRange:=ws2.Range("A1"), _

　　　　　　　　　　　　　　　　　Unique:=True

　　i = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

　　Set rng = ws2.Range(ws2.Range("B2"), ws2.Cells(i, 2))

　　rng = "=SUMIF(" & ws1.Name & "!A:A,A2," & ws1.Name & "!B:B)"

　　rng.Value = rng.Value

　　Debug.Print Timer - sTimer

End Sub

3.AdvancedFilter + SumIf 1の応用版です。
AdvancedFilterを使い一気にキーをユニーク化した後、
集計は、シート上で行い、値貼り付けで計算式を消しています。

5.ピボット + コピペ


  
    
      'ピボット + コピペ

      Sub sample5()

      　　Dim i As Long

      　　Dim ws As Worksheet

      　　Dim pvc As PivotCache

      　　Dim pvt As PivotTable

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　Set ws = Sheets.Add

      　　Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _

      　　　　　　　　　　　　　　　　SourceData:=ws1.Range("A1").CurrentRegion, _

      　　　　　　　　　　　　　　　　Version:=xlPivotTableVersion14)

      　　Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R1C1", _

      　　　　　　　　　　　　　　　　TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", _

      　　　　　　　　　　　　　　　　DefaultVersion:=xlPivotTableVersion14)

      　　With pvt

      　　　　With .PivotFields("キー")

      　　　　　　.Orientation = xlRowField

      　　　　　　.Position = 1

      　　　　End With

      　　　　.AddDataField .PivotFields("データ"), "データ合計", xlSum

      　　　　.PivotSelect "キー[All]", xlLabelOnly + xlFirstRow, True

      　　　　Selection.Resize(, 2).Copy Destination:=ws2.Range("A2")

      　　End With

      　　Application.DisplayAlerts = False

      　　ws.Delete

      　　Application.DisplayAlerts = True

      　　Debug.Print Timer - sTimer

      End Sub

集計といったらピボットというくらいに、エクセルではよく使われる機能です。
これをVBAで行っています。
作成されたピボットより、値のコピーをしています。

6.配列のみで 1


  
    
      '配列のみで 1

      Sub sample6()

      　　Dim i As Long

      　　Dim j As Long

      　　Dim ix As Long

      　　Dim ary

      　　Dim ary1()

      　　Dim ary2()

      　　Dim blnFind As Boolean

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　ary = ws1.Range("A1").CurrentRegion

      　　ix = 0

      　　ReDim ary1(1, ix)

      　　ary1(0, 0) = ary(2, 1)

      　　ary1(1, 0) = 0

      　　For i = 2 To UBound(ary, 1)

      　　　　ix = UBound(ary1, 2) + 1

      　　　　For j = 0 To UBound(ary1, 2)

      　　　　　　If ary1(0, j) = ary(i, 1) Then

      　　　　　　　　blnFind = True

      　　　　　　　　ix = j

      　　　　　　　　Exit For

      　　　　　　End If

      　　　　Next

      　　　　If ix > UBound(ary1, 2) Then

      　　　　　　ReDim Preserve ary1(1, ix)

      　　　　　　ary1(0, ix) = ary(i, 1)

      　　　　　　ary1(1, ix) = ary(i, 2)

      　　　　Else

      　　　　　　ary1(1, ix) = ary1(1, ix) + ary(i, 2)

      　　　　End If

      　　Next

      　　ReDim ary2(UBound(ary1, 2), UBound(ary1, 1))

      　　For i = 0 To UBound(ary1, 1)

      　　　　For j = 0 To UBound(ary1, 2)

      　　　　　　ary2(j, i) = ary1(i, j)

      　　　　Next

      　　Next

      　　ws2.Range(ws2.Cells(2, 1), ws2.Cells(UBound(ary2, 1) + 2, 2)) = ary2

      　　Debug.Print Timer - sTimer

      End Sub

もう、エクセルとか関係ない、全部自分で集計します、という感じでしょうか。
配列を使い慣れていないと、書くのにちょっと苦労するかもしれません。

7.配列のみで 2


  
    
      '配列のみで 2

      Sub sample7()

      　　Dim i As Long

      　　Dim j As Long

      　　Dim ix As Long

      　　Dim ary

      　　Dim ary1() As String

      　　Dim ary2() As Long

      　　Dim ary3()

      　　Dim aryT

      　　Dim blnFind As Boolean

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　ary = ws1.Range("A1").CurrentRegion

      　　ix = 0

      　　ReDim ary1(ix)

      　　ReDim ary2(ix)

      　　ary1(0) = ary(2, 1)

      　　ary2(0) = 0

      　　For i = 2 To UBound(ary, 1)

      　　　　aryT = Filter(ary1, ary(i, 1))

      　　　　If UBound(aryT) < 0 Then

      　　　　　　ix = UBound(ary1) + 1

      　　　　　　ReDim Preserve ary1(ix)

      　　　　　　ReDim Preserve ary2(ix)

      　　　　　　ary1(ix) = ary(i, 1)

      　　　　　　ary2(ix) = ary(i, 2)

      　　　　Else

      　　　　　　For j = 0 To UBound(ary1)

      　　　　　　　　If ary1(j) = ary(i, 1) Then

      　　　　　　　　　　blnFind = True

      　　　　　　　　　　ix = j

      　　　　　　　　　　Exit For

      　　　　　　　　End If

      　　　　　　Next

      　　　　　　ary2(ix) = ary2(ix) + ary(i, 2)

      　　　　End If

      　　Next

      　　ReDim ary3(UBound(ary1), 1)

      　　For i = 0 To UBound(ary1)

      　　　　ary3(i, 0) = ary1(i)

      　　　　ary3(i, 1) = ary2(i)

      　　Next

      　　ws2.Range(ws2.Cells(2, 1), ws2.Cells(UBound(ary2, 1) + 2, 2)) = ary3

      　　Debug.Print Timer - sTimer

      End Sub

6.配列のみで 1とほぼ同じなのですが、
キーが配列にすでにあるかどうかを、Filter関数を使って判断しています。

8.Dictionary + 配列


  
    
      'Dictionary + 配列

      Sub sample8()

      　　Dim myDic As New Dictionary

      　　Dim i As Long

      　　Dim j As Long

      　　Dim ix As Long

      　　Dim ary

      　　Dim ary1

      　　Dim ary2

      　　Dim ary3

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　For i = 2 To ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

      　　　　If myDic.Exists(ws1.Cells(i, 1).Value) Then

      　　　　　　myDic(ws1.Cells(i, 1).Value) = myDic(ws1.Cells(i, 1).Value) _

      　　　　　　　　　　　　　　　　　　　　 + ws1.Cells(i, 2).Value

      　　　　Else

      　　　　　　myDic.Add ws1.Cells(i, 1).Value, ws1.Cells(i, 2).Value

      　　　　End If

      　　Next

      　　ary1 = myDic.Keys

      　　ary2 = myDic.Items

      　　Set myDic = Nothing

      　　ReDim ary3(UBound(ary1), 1)

      　　For i = 0 To UBound(ary1)

      　　　　ary3(i, 0) = ary1(i)

      　　　　ary3(i, 1) = ary2(i)

      　　Next

      　　ws2.Range(ws2.Cells(2, 1), ws2.Cells(UBound(ary3) + 2, 2)) = ary3

      　　Debug.Print Timer - sTimer

      End Sub

Dictionary（ディクショナリー）のパフォーマンスについて

・Dictionaryの検証に使うシート・ユニーク化（重複削除）の方法について・Dictionaryでユニーク化1 ・Dictionaryでユニーク化2 ・Dictionaryでユニーク化3 ・Dictionaryでユニーク化4 ・最初のVBAはなぜ遅かったのか・Dictionaryを使わない方法・サイト内の関連ページ

こちらで検証したものとほぼ同じです。
Dictionaryから配列に一気に取り出しています。

9.Sort + 配列


  
    
      'Sort + 配列

      Sub sample9()

      　　Dim i As Long

      　　Dim j As Long

      　　Dim ix As Long

      　　Dim ws As Worksheet

      　　Dim ary

      　　Dim ary1()

      　　Dim ary2()

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　ws1.Copy Before:=ActiveSheet

      　　Set ws = ActiveSheet

      　　ws.Range("A1").CurrentRegion.Sort Key1:=ws.Range("A1"), _

      　　　　　　　　　　　　　　　　　　　Order1:=xlAscending, _

      　　　　　　　　　　　　　　　　　　　Header:=xlYes

      　　ary = ws.Range("A1").CurrentRegion

      　　ix = -1

      　　For i = 2 To UBound(ary, 1)

      　　　　If ary(i, 1) <> ary(i - 1, 1) Then

      　　　　　　ix = ix + 1

      　　　　　　ReDim Preserve ary1(1, ix)

      　　　　　　ary1(0, ix) = ary(i, 1)

      　　　　　　ary1(1, ix) = ary(i, 2)

      　　　　Else

      　　　　　　ary1(1, ix) = ary1(1, ix) + ary(i, 2)

      　　　　End If

      　　Next

      　　ReDim ary2(UBound(ary1, 2), UBound(ary1, 1))

      　　For i = 0 To UBound(ary1, 1)

      　　　　For j = 0 To UBound(ary1, 2)

      　　　　　　ary2(j, i) = ary1(i, j)

      　　　　Next

      　　Next

      　　ws2.Range(ws2.Cells(2, 1), ws2.Cells(UBound(ary2, 1) + 2, 2)) = ary2

      　　Application.DisplayAlerts = False

      　　ws.Delete
　　Application.DisplayAlerts = True

      　　Debug.Print Timer - sTimer

      End Sub

大量データで処理時間がかかる関数の対処方法（WorksheetFunction）

・大量データで処理時間がかかるサンプルデータ・普通にマクロVBAコ－ドを書いた場合・指定範囲を絞ってみる・配列を使って書いてみる・アルゴリズムを考えてみる・Dictionary（連想配列）を使う・大量データで処理時間がかかる関数の対処方法の最後に・XLOOKUP+SORTが最強だった … 後日追加（2025/11/4）

こちらで解説した考え方に基づいた処理方法です。
キーを並べて、順処理しています。

10.ADO + SQL


  
    
      'ADO + SQL

      Sub sample10()

      　　Dim objCn As New ADODB.Connection

      　　Dim objRS As ADODB.Recordset

      　　Dim strSQL As String

      　　Dim i As Long

      　　Dim j As Long

      　　Dim ix As Long

      　　ws2.Range("A1").CurrentRegion.Offset(1).Clear

      　　sTimer = Timer

      　　With objCn

      '　　　　Excel2003まで
'　　　　.Provider = "Microsoft.Jet.OLEDB.4.0"

      '　　　　.Properties("Extended Properties") = "Excel 8.0"

      '　　　　Excel2007以降

      　　　　.Provider = "Microsoft.ACE.OLEDB.12.0"

      　　　　.Properties("Extended Properties") = "Excel 12.0"

      　　　　.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name

      　　End With

      　　strSQL = ""

      　　strSQL = strSQL & " SELECT D.キー, SUM(D.データ)"

      　　strSQL = strSQL & " FROM [" & ws1.Name & "$] AS D"

      　　strSQL = strSQL & " GROUP BY D.キー"

      　　Set objRS = New ADODB.Recordset

      　　Set objRS = objCn.Execute(strSQL)

      　　ws2.Range("A2").CopyFromRecordset objRS

      　　objCn.Close

      　　Set objCn = Nothing

      　　Debug.Print Timer - sTimer

      End Sub

シートをデータベースのテーブルとして扱うものです。
ADODBを使い、SQL文を発行し集計結果を取得しています。
Excel2003までの記述の場合、一定件数を超えると正しく集計されません。
ADOとSQLについては、以下を参照してください。
VBAでのSQLの基礎（SQL:Structured Query Language）

・SQL文・SELECT文・SQLの学習について・実践例

ADOでマスタ付加と集計（SQL）

結果発表

当初掲載した以下の結果において、ADOの計測に間違いがありましたので、修正掲載しています。

1位.Sort + 配列
2位.ピボット + コピペ
3位.Dictionary + 配列
4位.ADO + SQL

		データ1 1,000*30		データ2 10,000*3		データ3 10,000*30		データ4 100,000*3		総合
		順位	タイム	順位	タイム	順位	タイム	順位	タイム	順位
1	ワークシート関数を使った基本 1	9	4.794	7	41.513
2	ワークシート関数を使った基本 2	10	46.973	10	97.799
3	AdvancedFilter + SumIf 1	8	4.132	9	43.215
4	AdvancedFilter + SUMIF 2	7	4.091	8	42.647
5	ピボット + コピペ	1	0.115	4	0.495	1	0.992	4	6.678	2
6	配列のみで 1	5	2.120	5	21.066
7	配列のみで 2	6	3.224	6	23.802
8	Dictionary + 配列	3	0.276	2	0.286	4	2.853	2	4.863	3
9	Sort + 配列	2	0.125	1	0.135	2	1.017	1	1.788	1
10	ADO + SQL	4	0.281	3	0.361	3	2.188	3	5.452	4

「データ3」「データ4」は、それまでの時間が1秒を切っている、
5.ピボット + コピペ
8.Dictionary + 配列
9.Sort + 配列
10.ADO + SQL
以上だけで行いました。
さすがに、このデータ量になると、他の処理方法では時間がかかりすぎてしまいます。

総評

1.ワークシート関数を使った基本 1
一般的には、十分な速度ではないでしょうか。
特に処理速度にこだわらなければ、数万程度までは、この処理方法で十分でしょう。

2.ワークシート関数を使った基本 2
どうしても、CountIfとMatchの2回行っているので、遅くなるのは仕方ないでしょうか。
On Error Resume Nextを使わないためのVBAコードとしての位置づけでしょうか。
処理速度にこだわらないのなら、とてもきれいな処理方法だと思います。

3.AdvancedFilter + SumIf 1
VBAコードの記述が短くて、AdvancedFilterさえ知っていれば、わかり易い処理方法です。
しかしどうしても、大量データでは、SumIfが遅いです。

4.AdvancedFilter + SUMIF 2
VBAでSumIfを使うか、シートでSUMIFを使うかは、ほとんど好みの問題でしょうか。
ただし、単なる集計ではなく、他の関数と組み合わせて使うような場合は、
WorksheetFunctionより、シート関数を使った方がVBAが書きやすいかもしれません。

5.ピボット + コピペ
やはり、ピボットの集計は速いですね。
特に、集約度合（元データ件数に対するキーの件数）が高いと驚異的な速さです。
ただし、VBAでピボットを扱うのは、結構骨が折れます。
なにより、単純集計以外では、なかなか使うのが難しいです。
データベース形式が崩れているような表（ピボット集計できない表）では使えないという難点もあります。

6.配列のみで 1
数万程度までのデータ量なら、これは融通の利くVBAコードで、
臨機応変に処理を変更できるのが便利です。
しかし、大量データになってくると、配列内の検索に時間がかかってしまいます。

7.配列のみで 2
配列のみで 1とほぼ同じですね。
Filter関数って、便利そうなのですが、意外と使う機会がないものです。

8.Dictionary + 配列
速度としては申し分ないと思います。
ピボットとも遜色ありませんね。
集約度合（元データ件数に対するキーの件数）が高いとピボットに負けるといった感じです。
しかし、Dictionaryには、配列やオブジェクトを格納できるので、
より複雑な集計処理では絶大な威力を発揮します。

9.Sort + 配列
私見としては、これが最速最強です。
複雑な集計でも、臨機応変に対応するVBAコードにすることが出来ます。

10.ADO + SQL
今回は、既に開いているブックでの集計でしたので、他に後れを取った気がします。
他のブックを集計する場合でも、ADOであればあまり変わらない速度で集計できますので、順位がかなり変わってくるはずです。
また、最大値、最小値、平均値等々、SQLに豊富な関数が用意されていますので、
SQL文を自在に書けるなら、かなり複雑な処理も可能です。
ただし、シートのデータが正しくデータベース形式になっている必要があります。

全体として
上位4通りの処理速度は、測定誤差と実用度を考えると大差ないと言ってよいと思います。
データの特質（データ量、集約度）によって使い分けすれば良いでしょう。
VBAで自動化する場合、
仕様が完全に固まっていれば良いのですが、仕様変更が頻繁に発生するものです。
そのような時に、自在に変更しやすいのは、
8.Dictionary + 配列 と 9.Sort + 配列 であろうと思います。
これらのVBAコードであれば、臨機応変に仕様変更に対応できます。
この2つの方法をマスターすれば、どんな大量データでも恐るるに足りず。
10.ADO + SQLについては、これを自在に使えるのなら、何もいう事はありません。
SQLが分からない人は、SQLを勉強してみるのも良いと思います。

本記事はまとめるのに、ずいぶんと時間がかかりました、結構疲れました。
大方としては、予想通りの結果でした。
ただ、VBAでピボットはあまり使わないので、こんなに速かったんだ、と再認識したことと、
ADOは使い方によっては、やはりとても便利だなーと、
そして何より、並べ替えての配列処理のすごさを再認識しました。

VBAの基本的な速度対策については、以下を参照してください。
速度比較決定版【Range,Cells,Do,For,For Each】

何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いかとか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。

エクセルVBAのパフォーマンス・処理速度に関するレポート

ExcelのマクロVBAは遅い・重いと良く言われることが多いようですが、マクロVBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。

マクロVBAの高速化・速度対策の具体的手順と検証

マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。

大量データで処理時間がかかる関数の対処方法（WorksheetFunction）

同じテーマ「マクロVBA技術解説」の記事

マクロVBAの高速化・速度対策の具体的手順と検証
 動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
 大量VlookupをVBAで高速に処理する方法について
 Withステートメントの実行速度と注意点
 IfステートメントとIIF関数とMax関数の速度比較
 スピルって速いの?スピルの速度について
 1次元配列の下限インデックスを高速に変更する関数
 レーベンシュタイン距離を求めるVBA（スピル対応）とセル数式

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
大量データにおける処理方法の速度王決定戦

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA技術解説
大量データにおける処理方法の速度王決定戦