[直して]実行速度が遅いマクロ(2)

【2025年9月】

「[直して]実行速度が遅いマクロ(1)」で、遅いマクロを速くするアドバイスを、AIにしてもらいました。このときの"遅い理由"は2つあります。ひとつめは、主に数式(関数)の再計算でした。乱数を発生させるRAND関数を10,000個使っていて、行を削除するたびに再計算が行われました。そりゃ、遅いですよ。もうひとつの理由は"削除の回数"です。このマクロでは、特定のデータが見つかったとき行削除します。1,000件のデータが見つかって、そのつど1,000回の削除を行うよりも、見つかった1,000件のセルを覚えておいて、それらを一気に削除すれば1回で済みます。これは、セルの削除に限りません。何かをセルに代入するときも、まったく同じです。Excelのマクロを高速化するには、セルを操作する回数を減らすのがポイントです。詳しくは「[直して]実行速度が遅いマクロ(1)」をご覧ください。

今回のテーマは、実務では欠かせない操作である"値貼り付け"です。みなさん、よくやりますよね。何なら、普通のコピペよりも多いんじゃありません？次のようなリストを作りました。

F列には「A-1001～A-1500」という500個のコードが昇順で入力されています。これらのコードをランダムに並べ替えたのがA列です。数は同じです。B列とC列には数値を入力して、D列で足し算しています。A列のコードを上から順番に見ていって、同じコードをF列で探し、D列の"計算結果"をG列に"値貼り付け"します。実務で、超あるあるな操作です。次のようなコードを作ってみました。

Sub Macro1()
    Dim i As Long, j As Long, lastRow As Long, target As String
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        target = Cells(i, 1).Value
        For j = 2 To lastRow
            If Cells(j, 6).Value = target Then
                Cells(i, 4).Copy
                Cells(j, 7).PasteSpecial xlPasteValues
                Exit For
            End If
        Next j
    Next i
End Sub

PasteSpecialは「形式を選択して貼り付け」で、引数に指定した xlPasteValues が「値」です。このマクロには、速度を遅くする要因が2つあります。では、さっそくAIにお願いしましょう。

Sub Macro1()
　　Dim i As Long, j As Long, lastRow As Long, target As String
　　lastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　For i = 2 To lastRow
　　　　target = Cells(i, 1).Value
　　　　For j = 2 To lastRow
　　　　　　If Cells(j, 6).Value = target Then
　　　　　　　　Cells(i, 4).Copy
　　　　　　　　Cells(j, 7).PasteSpecial xlPasteValues
　　　　　　　　Exit For
　　　　　　End If
　　　　Next j
　　Next i
End Sub
このマクロの実行速度を速くするには？

マクロを高速化するには、すべての行をループして一致するものを探すのは避けましょう。
代わりに、辞書を使ってコードを「合計」値にマッピングし、結果を1回のパスで書き込みます。
また、マクロの実行中は画面更新と自動計算をオフにしてください。

Sub Macro1_Fast()
    Dim dict As Object
    Dim lastRowA As Long, lastRowF As Long, i As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dict = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    lastRowA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastRowF = ws.Cells(ws.Rows.Count, 6).End(xlUp).Row
    
    ' Build dictionary from A:D
    For i = 2 To lastRowA
        dict(ws.Cells(i, 1).Value) = ws.Cells(i, 4).Value
    Next i
    
    ' Write values to G based on F
    For i = 2 To lastRowF
        If dict.Exists(ws.Cells(i, 6).Value) Then
            ws.Cells(i, 7).Value = dict(ws.Cells(i, 6).Value)
        Else
            ws.Cells(i, 7).Value = ""
        End If
    Next i
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

へぇ～そうきましたか。連想配列ですか。まぁ、確かに一理ありますね。「すべての行をループして一致するものを探すのは避けましょう」というのは同感です。だから私も、これではない、もっと劇的に速いコードは試しています。でも、連想配列の発想はなかったです。さっそく実行して速度を計測してみます。

•   1.543秒 (10回平均) 連想配列 (←AIの提案)

おっと、なんかいきなり最速レコードがでちゃいました、やりますね。あ、でもさ、それだったら、A列のコードをVLOOKUP関数で調べてD列だけの配列を作って、G列に一括代入した方が速くない？あるいは、A列からD列をSORT関数で並べ替えて、D列の値だけ取り出してG列に一括代入するとか。たぶん、それが最も速いと思うけどな。あ、でも、そっか、今回の前提をAIに伝えてなかったのか...今回のA列とF列は、まったく同じデータです。ただ並び順が違うだけ。どちらかに、存在しないデータはありません。一対一対応です。だったら「配列作ってドカンと代入方式」で可能だけど、もし、どちらかに存在しないデータがあったり、そもそも数が違ったりしたら、なるほど連想配列使う方が良いのか。う～ん、ちょっとAIに負けた気がして悔しいです。

この後で紹介する私のコードより速いやつを提案されちゃいましたのでｗ それでは、AI様が提案なさったコードを解説させていただきます。多くの人にとって、分からないのは「連想配列」でしょう。「連想配列」とは、下図のようなイメージです。

"松の間"に宿泊しているのは"田中"さん、"竹の間"の宿泊者は"小原"さん、のように「○○は→××」みたいなリストです。変数を「箱のようなもの」と例えるなら、次のような感じですかね。

これ、普通の配列と何が違うのかというと、普通の配列は「箱の位置」が決まっているだけです。

なので、もし箱を入れ替えたら

"1"だった"小原"さんは"3"になります。対して「連想配列」は箱に"名前"が紐付いているような感じで、入れ替えても(順番が変わるだけで)同じリストになります。

プログラミングでは、実際に箱を入れ替えるのではありませんよ。あくまでイメージです。なお、連想配列では"部屋名"部分を「キー」と呼び、"宿泊者"に該当するところを「アイテム」といいます。

連想配列の大きな特徴は、上図を見ても分かるとおり「アイテムには同じ値が存在できる」けど「キーには同じ値が存在できない」ことです。この特性を利用して、"重複しないリスト(ユニークリスト)"などの作成にも使われていました[*1(下記参照)]。とはいえ、この連想配列という仕組みは、(厳密には)VBAに存在しません[*2(下記参照)]。なので、別のアプリケーションを呼び出して作ります。連想配列を持っていて、VBAから呼び出せるのが「Windows Script Host」通称「WSH」です。AIが提示したコードでは

Set dict = CreateObject("Scripting.Dictionary")

が、呼び出しているところです。なお、連想配列は"Dictionaryオブジェクト"という名前なので、変数名に"dict"を使っています(たぶん)。さて、この連想配列に、今回は「コード」と「計算結果」をセットしていきます。それが下のコードです。

    ' Build dictionary from A:D
    For i = 2 To lastRowA
        dict(ws.Cells(i, 1).Value) = ws.Cells(i, 4).Value
    Next i

これによって、次のような連想配列が作成されます。

作成が終わったら、F列のコードを"キー"として、"アイテム"を呼び出します。それが次のコード。

If dict.Exists(ws.Cells(i, 6).Value) Then ''もしF列の"キー"が存在したら
    ws.Cells(i, 7).Value = dict(ws.Cells(i, 6).Value) ''該当する"アイテム"を代入する
Else
    ws.Cells(i, 7).Value = ""
End If

要するに、VLOOKUP関数みたいなことを、やっているわけです。Existsというのは、"キー"が存在するかどうかを調べる(WSHの)コマンドです。ここで「"キー"が存在しなかったら」を考慮しているのは、私が"一対一対応"という前提を伝えなかったので、当然の処理ですね。サーセン...

さてさて、私が用意していたコードよりも、速いコードを提示されちゃいましたけど、一応マクロの所要時間を計測してあるので、参考までにご紹介します。きっと驚きますよ。まず、冒頭のコード。

Sub Macro1()
    Dim i As Long, j As Long, lastRow As Long, target As String
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        target = Cells(i, 1).Value
        For j = 2 To lastRow
            If Cells(j, 6).Value = target Then
                Cells(i, 4).Copy
                Cells(j, 7).PasteSpecial xlPasteValues
                Exit For
            End If
        Next j
    Next i
End Sub

これを普通に実行したら、こうなりました。

• 83.259秒 (10回平均) 値貼り付け
•   1.543秒 (10回平均) 連想配列 (←AIの提案)

連想配列うんぬんじゃ、ありません。「形式を選択して貼り付け」の「値貼り付け」って、メチャクチャ遅いんです。ここに「画面抑止」と「再計算の手動」を加えた結果がこちら。

• 83.259秒 (10回平均) 値貼り付け
• 69.282秒 (10回平均) 値貼り付け＋画面抑止＋再計算手動
•   1.543秒 (10回平均) 連想配列 (←AIの提案)

それほど変わらないでしょ。そのくらい遅いんです。じゃ、どーするかというと。値貼り付けって、要するに"コピー元セルの値"を、"コピー先セルの値"に代入する行為ですよね。これ、VBAなら"Value = Value"です。値貼り付けをValueの代入に変えたコードがこちら。

Sub Macro2()
    Dim i As Long, j As Long, lastRow As Long, target As String
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        target = Cells(i, 1).Value
        For j = 2 To lastRow
            If Cells(j, 6).Value = target Then
                Cells(j, 7).Value = Cells(i, 4).Value
                Exit For
            End If
        Next j
    Next i
End Sub

• 83.259秒 (10回平均) 値貼り付け
• 69.282秒 (10回平均) 値貼り付け＋画面抑止＋再計算手動
•   2.647秒 (10回平均) Valueの代入
•   2.166秒 (10回平均) Valueの代入＋画面抑止＋再計算手動
•   1.543秒 (10回平均) 連想配列 (←AIの提案)

さらに、もうひとつの改善点。A列を上から順番に見ていくのは当然です。でも、該当するF列を"上から順番"に探すのは無駄です。Excelには"検索"機能があります。

Sub Macro3()
    Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Range("F:F").Find(Cells(i, 1).Value).Offset(0, 1).Value = Cells(i, 4).Value
    Next i
End Sub

今回は、A列のコードはF列で必ず見つかる、という前提なので、検索で見つからなかったときのことは考慮していません。この結果が、こちら。

• 83.259秒 (10回平均) 値貼り付け
• 69.282秒 (10回平均) 値貼り付け＋画面抑止＋再計算手動
•   2.647秒 (10回平均) Valueの代入
•   2.166秒 (10回平均) Valueの代入＋画面抑止＋再計算手動
•   2.794秒 (10回平均) 検索
•   1.756秒 (10回平均) 検索＋画面抑止＋再計算手動
•   1.543秒 (10回平均) 連想配列 (←AIの提案)

くっ、ちょっと負けた...orz だけどぉ、俺の方がぁ、コード短いしぃ、こんなん誤差みたいなもんだしぃ、負けたっていうか引き分けみたいなもんだしぃ...[*3(下記参照)]

まとめ

今のExcelは、普通にセルをコピーした後で、値貼り付けに変更することもできます。値貼り付けには、Ctrl + Shift + Vというショートカットキーも割り当てられましたし、実務でもメッチャ使う機能のひとつです。手動でやるときは(そもそも人間の操作が遅いので)、それほど意識しないでしょうけど、マクロでやると唖然とするほど超遅いです。値貼り付けは「Valueの代入」と覚えてください。

補足

(*1)Excelのマクロで「重複しない(ユニーク)リスト」を作るには、いろいろな方法があります。何よりも今では、ワークシート関数のUNIQUE関数があります。これをWorksheetFunctionで呼び出すのが、最も簡単な方法のひとつです。今や、ユニークリストを「連想配列(ディクショナリ)」で作る時代ではないでしょう。

(*2)VBAに「連想配列」という仕組みはありませんが、よく似た機能として「Collectionオブジェクト」があります。詳しくは、下記のページをご覧ください。
独自のコレクションを作る

(*3)

今回はさ、その、なんだ、引き分け？だったよね。うん、そう。てゆーか、むしろコードの短さで俺の勝ち？みたいなｗ

はぁ？ナニ言ってんの？こっちは前提を聞いてないんですけどぉｗ 
あ～あ～前提を聞いてたらな～もっと良いコード出したのにな～言わないんだもんな～
てゆーかぁ、前提なんか知らなくても、あれくらいぜんぜん超余裕なんですけどぉｗｗ

Sub Macro4()
    Range("G2:G501") = [TAKE(SORT(A2:D501),,-1)]
End Sub