Excel 性能随着时间的推移而下降（数十万次以上循环迭代/数百万次计算）

Question

我目前正在使用一个工作簿，我连接到 MySQL 数据库，下载记录集，然后循环遍历 100,000 个唯一 ID。该工作簿有 31 个工作表。Excel 在 AWS 中运行，我们打开 5 个副本，每个副本有 13 个 Excel 实例。我发现这是最佳的副本和实例数量。

工作簿设计如下：

1. Application.ScreenUpdating = False, Application.Calculation = xlManual

2. 下载 MySQL 记录集

3. 循环遍历记录集中的每个唯一 ID：

   3.1 循环遍历31张表，全部清除mysql数据范围，除第一行外计算范围全部清除

   3.2 将mysql数据从记录集复制到sheet中

   3.3 将与 SQL 数据相关的定义名称更新为新范围

   3.4 在计算中使用 .FillDown

   3.5 将与计算相关的定义名称更新为新范围

   3.6 计算工作表

4. 当循环遍历完所有 ID 后，Application.ScreenUpdating = True, Application.Calculation = xlCalculationAutomatic

然而，我注意到，每条记录的计算时间随着时间的推移逐渐变慢。例如，前 10,000 条记录可能计算时间为 0.7 秒/条记录，到第 140,000 条记录时，计算时间将增加到 1.5 秒/条记录。记录越多，这个问题就越严重，例如 70,000 条记录 vs 140,000 条记录。

• Excel 计算引擎中是否存在可能导致性能逐渐下降的因素？
• 我认为当前计算一个工作表的设置效率低下，因为工作表之间的依赖关系没有得到解决。是否应该计算整个工作簿，而不是按工作表计算
• 关于已定义名称，每张表上的每个唯一 ID 可能具有不同的行数。为每个已定义名称设置一个合理的范围，然后仅在超出范围时才更新该范围，这样会更好吗？我计算过，更新名称所花的时间可能与 .FillDown 一样长

Answer 1

从架构层面来看：我遇到过类似的问题，并通过创建一个处理 10,000 个行块的循环解决了该问题，然后它将完成计算 [仅针对工作表]，然后将这些计算粘贴为值，然后移动到下一个 10,000 个单元格并重复直到完成。这样，您就不会使电脑内存过载。

Answer 2

我不确定，但如果您的问题可以解释为“如何解决计算速度慢的问题”，而不仅仅是“如何用 Excel 解决问题”，我很想知道这与 GS-Base 和 GS-Calc 相比如何。如果我理解正确的话，如果这些是二分搜索 100000 个 ID 以查找 3000 万条记录中的匹配行，并对匹配行求和等 - 这看起来可以在几分钟内完成。