宏用于比较和复制数据从一张纸到另一张需要很长时间

2 years ago

应该更快：

Sub MatchAndCopy()

    Dim sheet01 As Worksheet, sheet02 As Worksheet
    Dim c As Range, matchingCell As Long
    Dim RangeInSheet1 As Range
    Dim RangeInSheet2 As Range
    Dim dict As Object, tmp
    Set dict = CreateObject("scripting.dictionary")

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True

    Set sheet01 = Worksheets("Sheet1")
    Set sheet02 = Worksheets("Sheet2")

    Set RangeInSheet1 = sheet01.Range(sheet01.Range("A2"), _
              sheet01.Cells(Rows.count, 1).End(xlUp))
    Set RangeInSheet2 = sheet02.Range(sheet02.Range("A2"), _
              sheet02.Cells(Rows.count, 1).End(xlUp))

    'populate dictionary...
    For Each c In RangeInSheet1.Cells
        tmp = c.Value
        If Not dict.exists(tmp) Then
            dict.Add tmp, c.Row
        End If
    Next c

    For Each c In RangeInSheet2.Cells
      tmp = c.Value
      If dict.exists(tmp) Then
        Application.StatusBar = "Please wait while data is being copied," & _
                                " Processing count : " & c.Row
        sheet01.Cells(dict(tmp), "F").Resize(1, 5).Value = _
                c.Offset(0, 1).Resize(1, 5).Value
      End If
    Next c

    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

2 years ago

对于两张纸之间的400万条记录，请使用数据库 . Excel不是数据库 .

如果您坚持将Excel视为数据库，我建议使用ADODB . 有关类似问题和解决方案，请参阅this answer .

通过将名称放在每列的第一行中，将Sheet1上的每个列命名为要写入的列 . 例如，我们称之为 F1 ，_ _ 27606048， F3 ， F4 和 F5 . 另外，使用Sheet1和Sheet2之间的共享数据命名列;例如，我们称之为 F0 .

然后，如果您的Office版本允许，您可以发出以下声明：

UPDATE [Sheet1$]
INNER JOIN [Sheet2$] ON [Sheet1$].F0 = [Sheet2$].F0
SET 
    [Sheet1$].F1 = [Sheet2$].F1,
    [Sheet1$].F2 = [Sheet2$].F2,
    [Sheet1$].F3 = [Sheet2$].F3,
    [Sheet1$].F4 = [Sheet2$].F4,
    [Sheet1$].F5 = [Sheet2$].F5

如果没有，您可以将CopyFromRecordset方法与从以下SQL语句生成的记录集一起使用：

SELECT s1.F0, 
    Iif(s2.F0 Is Not Null, s2.F1, s1.F1),
    Iif(s2.F0 Is Not Null, s2.F2, s1.F2),
    Iif(s2.F0 Is Not Null, s2.F3, s1.F3),
    Iif(s2.F0 Is Not Null, s2.F4, s1.F4),
    Iif(s2.F0 Is Not Null, s2.F5, s1.F5)
FROM [Sheet1$] AS s1
LEFT JOIN [Sheet2$] AS s2 ON s1.F0 = s2.F0

2 years ago

立即获取整张纸

var values = sheet.getDataRange().getValues();

并在本地比较值

EDIT-1
Google Apps脚本文档https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet为getDataRange（）提供了以下示例

Returns a Range corresponding to the dimensions in which data is present. This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).


var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // This represents ALL the data
 var range = sheet.getDataRange();
 var values = range.getValues();

 // This logs the spreadsheet in CSV format with a trailing comma
 for (var i = 0; i < values.length; i++) {
   var row = "";
   for (var j = 0; j < values[i].length; j++) {
     if (values[i][j]) {
       row = row + values[i][j];
     }
     row = row + ",";
   }
   Logger.log(row);
 }

不应使用大量范围，而应在一次调用中获取数据并在本地处理