パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセルマクロVBAで大量データの処理動作が遅いときの対処法|高速化する方法

f:id:gene320:20180708194718j:plain

エクセルマクロで大量データを処理すると、マクロの処理が遅かったり、重くなってしまったりします。

たとえば、1000行を超えるデータを扱うとなると、処理に10分以上かかってしまうこともあります。

この記事では、エクセルマクロのプログラムを高速で処理する方法を紹介します。

また、高速で使えるプログラムを無料ダウンロードできるようにしていますので、ぜひ利用してみてください。

エクセルマクロVBAで大量データを高速で処理する方法

マクロvbaの処理速度が遅くなる原因

エクセルマクロVBAの処理速度が遅くなる主な原因は、以下の3つです。

[1] エクセルワークシート関数の使用
[2] エクセル画面更新
[3] Selectの使用

上記の3つを使用すると、マクロの処理が一気に低下します。なぜ処理が遅くなるかというと、理由は以下の通りです。

エクセルワークシート関数の使用を処理が遅くなる

ワークシート関数とは、vlookupやsumif関数のことです。

実は、マクロで処理をするたびに、開かれている「すべてのエクセルファイル」の「すべてのシート」の「すべてのワークシート関数」が再計算されます。

つまり、ワークシート関数があればあるほど、関数の計算量が増えてしまい、エクセルマクロの動作が重くなります。もし、計算量の多い関数(vlookup,sumif,sumproductは要注意)が含まれていると、さらに重たくなります。

もし、あなたがエクセル仕事が遅くて困っているなら、 ・エクセルワークシート関数を使わない ・マクロ起動中だけ、エクセルワークシート関数の再計算をオフにする といった対策が必要です。

エクセルの画面表示の更新のせいで処理が遅くなる

マクロは、高速で処理対象を切り替えます。あなたも、マクロを使っているとき、画面表示がパッパッと次々と切り替わるのをみたことがあるでしょう。

しかし、この画面表示の切り替えは処理時間を奪っています。とくにエクセルファイルを2つ開いたり、パワポやワードなどの外部ソフトウェアを操作したりすると、時間がかかります。

そのため、エクセルの画面表示の更新設定をオフにすると、処理が早くなます。

Selectを使用すると処理が遅くなる

マクロでは、Selectを使用すると処理が遅くなります。たとえば、以下のは、処理が遅いプログラムです。

マクロの処理速度が遅い例
    Range("C1").Select
    ActiveCell.Value = "100"
    Range("C2").Select
    ActiveCell.Value = "200"
    Range("C3").Select
    ActiveCell.Value = "300"

このプログラムの処理速度は著しく遅いです。なぜなら、一つ一つをセルを指定して、確認しながら作業をしているからです。100行くらいであれば、不便さは感じませんが、200,300行とデータが増えると、処理が遅くて仕事になりません

Selectは、マクロの自動記録で作成したプログラムには、必ず記載されています。手書きで編集しないと、select頻発により、マクロの動きが遅いままです。

マクロの自動記録でプログラムを作成している人は、注意が必要です。

マクロvbaの処理を高速化するときの基本

マクロの処理速度を高速化したいときに、必ず取り入れてほしいプログラムを紹介します。

[1] Application.Calculation=xlManual|自動計算・再計算を停止
[2] Application.ScreenUpdating = False|画面表示の更新を停止
[3] Selectを使わない

たとえば、以下のプログラムでは、マクロの処理が遅くなります。

No1.マクロの処理速度が遅くなる例
Sub slow_program()
    Dim hida, migi As Long

    For migi = 2 To 11
        Range("F" & migi).select
        For hida = 2 To 100001
            If selection.value = Range("B" & hida).Value Then
                Range("C" & hida).Value = "OK"
            End If
        Next
    Next

End Sub

逆に以下のプログラムを使うと、処理速度が速くなります。

No2.マクロの処理速度が早い例
Sub fast_program()
    Dim hida, migi As Long

    Application.ScreenUpdating = False '---画面の表示更新をオフ---
    Application.Calculation = xlManual '---自動計算をオフ---
    
    For migi = 2 To 11
       '---Selectを入れない---
        For hida = 2 To 100001
            If Range("F" & migi).Value = Range("B" & hida).Value Then
                Range("C" & hida).Value = "OK"
            End If
        Next
    Next

    Application.Calculation = xlAutomatic '画面の表示更新をオン
    Application.ScreenUpdating = True '自動計算をオン
End Sub

実際に、上記の2つのマクロの処理時間を計測してみると、以下の違いがあります。

マクロ [1] Application.
Calculation
=xlManual
[2] Application.
ScreenUpdating
= False
[3] Select 処理時間
No1 なし なし なし 35分12秒
No2 あり あり あり 0分47秒

Application.Calculation=xlManual|自動計算・再計算を停止

エクセル関数の自動計算や再計算を停止させると、マクロの処理速度が一気に上がります。

もし、あなたが使うエクセルファイルにワークシート関数が含まれているのであれば、Application.Calculation=xlManualのプログラムを入れましょう。

ただし、プログラムの使い方には、注意が必要です。上手に使わないと、ワークシート関数が自動計算されなくなってしまいます。

なぜなら、Application.Calculation=xlManualのプログラムは、関数を手動計算に変更するからです。正しい入れ方は、プログラムを参考にしてください。

Application.ScreenUpdating = False|画面表示の更新を停止

マクロ動作中に切り替わる画面更新をオフにします。スクリーンの更新を解除できるので、処理が早くなります。

なお、Application.ScreenUpdating = Falseのプログラムを入れた後は、Application.ScreenUpdating = Trueを必ず入れましょう。

そうしないと、マクロを画面表示の更新がオフになったままになってしまいます。

Selectを使わない

Selectを使うと、遅くなるので、できる限りselectは避けましょう。

しかし、Selectを使わないようにするには、あなた自身がマクロのプログラムを自分で編集できるようになる必要があります。

もし、あなたが自分でマクロを編集できるようになりたいなら、こちらの無料動画で学んでみてください。

マクロvbaで大量データのマッチング・照合・集計を高速で行う方法(VBAの高速化)

マクロで大量データをマッチングすることがあります。その場合は、以下の5つの方法があります。

[1] for next文
[2] for each文
[3] Findメソッド
[4] 動的配列
[5] SQLの利用

[1]に近いほど、プログラムとしてはカンタンですが、処理速度は遅くなります。逆に、[5]に近いほど、プログラムは難しいですが、処理速度は早くなります。

実際に、10万行あるエクセルデータをそれぞれのマクロで処理して、その時間を計測しました。

マクロを起動前

f:id:gene320:20171028222056p:plain

マクロを起動後

f:id:gene320:20171028222107p:plain

この処理を行ったマクロは、以下のような処理時間でした。

No マクロ 処理の時間(秒)
1 for next文 24秒
2 for each文 12秒
3 find 9秒
4 動的配列 6秒
5 SQL 未計測

方法1|For Next 構文でマッチング

もっとも基本的なデータマッチングのやり方です。データ数があまりに増えると、遅く感じます。それでも、ワークシート関数で行くより格段に効率的です。

Sub sample1_for_next()
    Dim hida, migi As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlManual        
    
    For migi = 2 To 21
        For hida = 2 To 100001
            If Range("F" & migi).Value = Range("B" & hida).Value Then
                Range("C" & hida).Value = "OK"
            End If
        Next
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

方法2|For Each 構文でマッチング

調査対象のセルをすべてメモリで参照する方法です。個々のセルを順に見つけなおしている方法1より高速処理が可能です。

Sub sample2_for_each()

    Dim rM, rH As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    For Each rM In Range("F2:F21")
        For Each rH In Range("B2:B100001")
            If rM.Value = rH.Value Then
                rH.Offset(, 1).Value = "OK"
            End If
        Next
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True

End Sub

方法3|Findメソッドでマッチング

.Findメソッドを使って条件に一致するセルを見つけます。エクセルの表の機能で言うと、「検索」に相当します。For Each構文でセルを探すよりさらに高速です。

Sub sample3_find()

    Dim rM, rH, rMy, rFirst, rU As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    For Each rM In Range("F2:F21")
        Set rH = Range("B1:B100001")
        Set rMy = rH.Find(What:=rM.Value)

        If rMy Is Nothing Then
            Exit For
        Else
            Set rFirst = rMy
            rMy.Offset(, 1).Value = "OK"
        End If
        Do
            Set rMy = rH.FindNext(rMy)
            If rMy.Address = rFirst.Address Then
                Exit Do
            Else
                rMy.Offset(, 1).Value = "OK"
            End If
        Loop
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True

End Sub

Findメソッドを使用すれば、
・完全一致/部分一致を指定
・大文字小文字を区別する/しないを指定
と、検索のバリエーションもあります。

上に示したサンプルは、完全一致、大文字小文字指定なし、の場合です。.Findメソッドを上記のサンプルを加工すれば、あなたの仕事に合わせたマクロを作成できます

たとえば、 .Findメソッドについては、マクロの自動記録をしながら、それなりに動くマクロを入手できます。

ぜひ試してみてください。

方法4|動的配列

動的配列を使えば、さらに高速でデータマッチングを行うことができます。ただし、動的配列はかなりレベルが高い概念なので、習得するまでに時間がかかるでしょう。

Sub sample4()

    Dim tate As Long
    Dim rH As Range
    Dim st() As String

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    For tate = 0 To 19
        ReDim Preserve st(tate)
        st(tate) = Range("F2").Offset(tate).Value
    Next

    For tate = 0 To UBound(st)
        For Each rH In Range("B2:B100001")
            If rH.Value = st(tate) Then
                rH.Offset(, 1).Value = "OK"
            End If
        Next
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True

End Sub

方法5|SQLで処理

データが増えれば増えるほど、この方法は強力です。ただし、この方法を使えるようになるには、2つのスキルが必要です。

1.アクセスのファイル形式のファイルに接続する方法
2.複数テーブルをマッチングさせるSQLを新たに習得

データ件数が多いときには、正直これが最強です。なお、PCにアクセスがインストールされている必要はありません。

ちなみに、アクセスでの処理を行うには、

[1] Microsoft Active X Data Objects x.x Libraryというライブラリへの参照設定
( x.x の部分は、あなたのエクセルのいちばん大きい数字のものを使えばOKです )

[2] ADODB型のオブジェクトを生成し、マッチングしたい表の入ったアクセス形式のファイルに接続

[3] あらかじめアクセスのテーブルに入れてあった2つの表から、必要な情報を取り出すSQLを発行し、その結果をエクセルに出力

マクロで大量データをマッチングする仕事は、こちらの記事で詳しく紹介しています。この記事で紹介している高速化のワザと組み合わせて、あなたの仕事に活かしてみてください。

www.fastclassinfo.com

WithステートメントやVariant型はそこまで影響しない

・Withステートメントを利用する
・Variant型を利用しない
といった知識は覚える必要はありません。なぜなら、そこまでマクロの処理速度に影響しないからです。もちろん、スピードは早くなります。しかし、そこまで影響しないものまで覚えていてはキリがありません。

それよりも、改善の効果が大きいプログラムに絞って実践する方が、成果につながります。この場合でいえば、この記事で紹介した以下の内容を実践すれば、かなりマクロの速度が早くなります。

[1] Application.Calculation=xlManual|自動計算・再計算を停止
[2] Application.ScreenUpdating = False|画面表示の更新を停止
[3] Selectを使わない

照合作業では、

[1] for next文
[2] for each文
[3] Findメソッド
[4] 動的配列
[5] SQLの利用

細かいテクニックは覚えるのは、これらを試した後です。もし、これらの改善を行った上で、さらに処理速度を上げたいなら、以下のことも試してみてください。

・Withステートメント
・Variant型で変数宣言

何かを覚えるとき、必ず優先順位があります。効果の高いモノから試すことを意識しましょう。

この記事を読んだ方へのオススメ

以下の記事をあわせて読んでみて下さい

エクセルマクロの実例について詳しく知りたいなら

www.fastclassinfo.com

マクロの作成には型がある!プログラムの開発手順を詳しく紹介

www.fastclassinfo.com

エクセルマクロを時間をかけずに効率的に作成する方法

www.fastclassinfo.com

エクセルマクロを独学で上達するコツを知りたいなら

www.fastclassinfo.com

パソコン仕事を効率的に使うワザを知りたいなら

www.fastclassinfo.com