时间戳

首页 > 解决方案 > Excel VBA 宏 - 如何以最有效的方式组合 2 张工作表

excel - Excel VBA 宏 - 如何以最有效的方式组合 2 张工作表

问题描述

目前我有三张床单。其中两个包含要写入第三个的数据。

工作表 A 具有以下结构：

| 一个 | 乙| C | D | E | F |
| 版本 | 姓名 | XX | XY | XZ | 数据 |
| 5.0 | 测试 | XX | XY | XZ | 测试1 |
| 5.1 | 树 | XX | XY | XZ | 100 |
| 5.1 | 测试 | XX | XY | XZ | 测试 |
| 5.0 | 树 | XX | XY | XZ | 50 |
...

表 B 有这个：

| 一个 | 乙| C | D | E |
| 版本 | 姓名 | XX | XY | 数据 |
| 5.1 | 测试 | XX | XY | 测试 |
| 5.0 | 树 | XX | XY | 300 |
| 5.1 | 树 | XX | XY | 400 |
| 5.0 | 测试 | XX | XY | 测试 |
...

表 C 有这个：

| 一个 | 乙| C | D | E | F | 克| H | ..
| 姓名 | XX | 表A - 5.0 | 表 B - 5.0 | XX | 表A - 5.1 | 表 B - 5.1 | XX | ..
...

XX/XY/XZ = 随机数据。版本是字符串，从 5.0 到 5.9。（实际版本是几年，但我认为这样更简单）


现在我想将工作表 A 和工作表 B 合并到工作表 C 中。它应该如下所示：

| 一个 | 乙| C | D | E | F | 克| H | ..
| 姓名 | XX | 表A - 5.0 | 表 B - 5.0 | XX | 表A - 5.1 | 表 B - 5.1 | XX | ..
| 测试 | XX | 测试1 | 测试 | XX | 测试 | 测试 | XX | ..
| 树 | XX | 50 | 300 | XX | 100 | 400 | XX | ..
…

目前我只是把工作表 A 放在工作表 C 的正确位置。
这不需要太多时间，但现在我想把工作表 B 也放入工作表 C，所以我目前的解决方案是循环工作表 B 中的每一行，在“A”列中找到具有相同名称的行并填补空白。
问题是，每个工作表都有 150k+ 行，这需要太多时间。而且工作表 B 中的名称不在工作表 C 中，因此“.find”函数搜索整个 150k+ 行，其速度约为每秒 3 行。有更快的方法吗？

我如何将工作表 B 填充到工作表 C 的代码示例：（从 A 到 C 的代码几乎相同）

For Each Row In DataFromSheetB.Rows
    With Selection
        Set FindRow = Sheets("C").Range("A:A").Find(What:=Row.Cells(2), LookIn:=xlValues, LookAt:=xlWhole)
    End With
    If FindRow Is Nothing Then
        GoTo Skip2
    Else
        Select Case Row.Cells(1)
        Case "5.0"
            FindRow.EntireRow.Cells(4).Value = Row.Cells(5)
        Case "5.1"
            FindRow.EntireRow.Cells(7).Value = Row.Cells(5)
        Case "5.2"
            FindRow.EntireRow.Cells(10).Value = Row.Cells(5)
        Case "5.3"
            FindRow.EntireRow.Cells(13).Value = Row.Cells(5)
        Case "5.4"
            FindRow.EntireRow.Cells(16).Value = Row.Cells(5)
        Case "5.5"
            FindRow.EntireRow.Cells(19).Value = Row.Cells(5)
        Case "5.6"
            FindRow.EntireRow.Cells(21).Value = Row.Cells(5)
        Case "5.7"
            FindRow.EntireRow.Cells(24).Value = Row.Cells(5)
        Case "5.8"
            FindRow.EntireRow.Cells(27).Value = Row.Cells(5)
        Case "5.9"
            FindRow.EntireRow.Cells(30).Value = Row.Cells(5)
        End Select
    End If
Skip2:
Next Row

标签： excelvba

解决方案

你实际上要做的是发出 ~150K^2/2 比较（~1E10），所以我认为没有计算机可以让它变得更快。您应该以更科学的方式来处理它，例如使用更有效的搜索方法。

因此，如果您确定Name+Version对在所有工作表中都是唯一的，并且想要找到匹配的对，则可以执行以下操作：

  1. 通过 Dimming Idx(1 to 150000, 1 to 2) 将工作表 A 的对 (=name+version) 和行号组成一个数组，并将 cstr(name+version) 放入 (,1) 并将 excel 行号放入 ( ,2)。然后使用例如快速排序对这个数组进行排序，以生成一个唯一的排序列表。
  2. 取出工作表 B 中的每一行，制作关键字，然后在索引中使用例如二进制搜索找到它。
  3. 现在您知道工作表 A 和工作表 B 中的匹配行了。

或者，您可以按名称+版本键对其中一张表进行排序，以便使用二进制搜索。祝你好运。

推荐阅读