【Excel VBA】シート間のデータ移行処理を高速化(配列処理)

ExcelVBAヘッダー Excel VBA

今回は、あるシートのデータを他のシートにデータ移行するようなExcelマクロを作る時の注意点について、解説していきます。

あるシートのデータを別シートの違ったフォーマットにデータを移行するのに、手作業でコピペを繰り返し、面倒な思いをされている方って多いのではないでしょうか。

データ移行は、Excel作業の中でも、よくある処理ですよね。

Excelマクロでデータ移行処理の方法を考えた時、思いつくのが下の2つの方法ではないでしょうか。

  • データ1件ずつシート間を移行
  • 一度、配列に格納してから別シートへ転記

今回はこれら処理方法についての解説だけでなく、2つの方法でプログラミングした時の比較検証もしてみたいと思います。

どちらの方法が、高速で効率的に処理できるか注目してください。

まずは準備から

今回、時間を許すようでしたら、一緒にプログラミングしてみてほしいのですが、まず、比較検証に使用するデータを用意します。

下のコードを標準モジュールに入力してください。

Public Sub DataList()

    Dim i As Long
    Dim j As Long
    Dim lngData As Long
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lngData = 1
    
    For j = 1 To 100
        For i = 1 To 1000
            ws.Cells(i, j).Value = lngData
            lngData = lngData + 1
        Next i
    Next j
    
End Sub

このコードは、行、列に対応したカウンタをループさせることで、「Sheet1」という名前のシートに縦1,000行、横100列、10万件の数値データを用意します。

この時、数値データは1ずつ増やしていきます。

行と列を分けて、ループをネストさせているところがポイントですね。

データ移行前イメージ

次項から、ExcelVBAを使って、別シートに移行していく方法について、解説していきます。

1件ずつデータ移行

用意した10万件のデータを1件ずつ、別シートに移行してみます。

今回は、10万件のデータを用意したシートと全く同じ並びで別シートにデータを移行するコードを考えてみます。

下のコードを標準モジュールに用意しましょう。

Public Sub DataMove1()

    Dim i As Long
    Dim j As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim t As Double

    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    For j = 1 To 100
        For i = 1 To 1000
            ws2.Cells(i, j).Value = ws1.Cells(i, j).Value
        Next i
    Next j
    
    MsgBox (Timer - t)
    
End Sub

移行元のシートの名前は「Sheet1」、そして移行先のシートの名前は「Sheet2」ですね。

このコードでは、10万件のデータをループさせて、1件ずつデータ移行させています。

ここでも、行と列に分けて、ループをネストさせているところがポイントです。

また、「Timer」オブジェクトを使って、処理速度を計っています。

実際に実行してみてください。

データ移行タイム

私のPCでは4.5秒ほどで処理が終わりました。

処理速度については、実行するPCのスペックによって違ってくるとは思いますが、以下、他の方法と比較してみます。

配列でデータ移行

10万件全てのデータを、一旦、配列に退避させてから、別シートに配列データを落とし込んでいく方法を考えてみます。

これも、別シートに全く同じ並びでデータを移行させることとします。

下のコードを、標準モジュールに用意してください。

Public Sub DataMove2()

    Dim arrData As Variant
    
    Dim i As Long
    Dim j As Long
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim t As Double
    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    arrData = ws1.Cells(1, 1).CurrentRegion.Value
    
    For j = 1 To UBound(arrData, 2)
        For i = 1 To UBound(arrData, 1)
            ws2.Cells(i, j).Value = arrData(i, j)
        Next i
    Next j
    
    MsgBox (Timer - t)
    
End Sub

配列用の変数「arrData」を用意し、「CurrentRegion」を使って、配列にデータを退避しています。

ポイントはこの配列用の変数「arrData」のデータ型が「Variant」であることです。

範囲指定して、一度に配列にデータを格納する場合は、必ずデータ型が「Variant」でなければならないことに注意しましょう。

さらに、範囲指定して一度に配列にデータを格納すると、2次元配列(インデックス(添え字)は「1」から始まります)になることにも注意してください。

行、列、それぞれカウンタを使って、ループをネストさせ、配列データを移行先の別シートに落とし込んでいきます。

処理とあわせて、タイムも計っていますので、実際に実行してみてください。

データ移行タイム

約3.9秒から4秒でした。

タイムはPCの実行環境によりますが、データを1件ずつ移行させるより、少し高速になったことがわかります。

つまり、データを1件ずつ処理するより、配列を使った方が高速なのです。

配列を一度に貼り付け

同じ方法で10万件のデータを配列に退避させ、それを一度に貼り付けることもできます。

つまり、ループさせて処理するのではなく、貼り付け先を指定して、配列データを「貼り付ける」ことができるのです。

「貼り付ける」と書きましたが、本当にコピペの「貼り付け」のようなイメージです。

下のコードを標準モジュールに用意します。

Public Sub DataMove3()

    Dim arrData As Variant
    
    Dim i As Long
    Dim j As Long
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim t As Double
    t = Timer
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    arrData = ws1.Cells(1, 1).CurrentRegion.Value
    
    With ws2
        .Range(.Cells(1, 1).Address & ":" & .Cells(UBound(arrData, 1), UBound(arrData, 2)).Address).Value = arrData
    End With
    
    MsgBox (Timer - t)
    
End Sub

先ほどと同じく、配列用の変数「arrData」に、「CurrentRegion」を使って、配列にデータを格納します。

そして、貼り付ける範囲を「Range」オブジェクトで指定できれば、「=」を使って、配列データを貼り付けることができてしまいます。

この場合、貼り付け範囲を「Range」オブジェクトで指定するのに、ちょっとしたコツが必要ですが、それについては下のリンクで解説していますので、参考にしてください。

【Excel VBA】セルの番地(Address)を取得する方法
ExcelVBAでセルの番地を取得するにはAddressプロパティを使います。計算式だったり、他のセルの値を参照したり、Excelを使っていれば、必ず意識するのがセルの番地です。ExcelVBAで、さまざまな形式でセルの番地を取得してみましょう。
【Excel VBA】RangeとCellsを互いに変換させる方法
Excel VBAでマクロを作っていると、Rangeオブジェクトだと都合が悪い時、またはCellsプロパティだと都合が悪い時があります。そんな時のために、Rangeオブジェクト、Cellsプロパティを互いに変換させる方法をマスターしておきましょう。

タイムに注目しながら、実際に実行してみてください。

データ移行タイム

いかがでしょうか。

実行してみると、この方法が一番速いことがわかります。

というか、爆速です!

データ移行後イメージ

今回のような大量なデータを別シートに移行させる場合、もちろんさまざまなパターンがあるため、一概に言い切ることはできませんが、配列を使うとより速く、またループを使わないで配列を貼り付けるともっと速いことがわかったと思います。

POINT

ループさせずに、配列を貼り付けると一番速い。

今回の検証で出た結論です。

この法則を頭の片隅に置いてプログラミングしていただければ、いろいろなシーンで応用が利きますし、より高速化、また効率化を図ることだってできるでしょう。

タイトルとURLをコピーしました