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

3 回答

• 0

  应该更快：

  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
  

  回复于 2024-04-26T03:02:45+08:00
• -1

  对于两张纸之间的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
  

  回复于 2024-04-26T03:02:45+08:00
• 1

  立即获取整张纸

  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);
   }
  

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

  回复于 2024-04-26T03:02:45+08:00