☰

Excel 工作表计算公式缓慢

tag-icon • tag-icon microsoft-excel-2007 performance
Excel 工作表计算公式缓慢

我有一张工作表（不是很大，目前略多于 6000 行 K 列），出于某种原因，它需要花费很长时间进行重新计算，并且经常重新计算和保存。有时需要 10-15 分钟，有时可能需要长达半天的时间，计算时间没有太大的一致性。公式不是很复杂，据我所知，它相当干净。大多数公式的参考资料是同一工作表中“源”工作表上的数据，这些数据从 SQL 中提取并使用 Cognos Impromptu 导出到 excel。我有另一张工作表，没有这个计算缓慢的问题，但它的数据是直接从包含数据的系统中导出的。公式是相同的，数据的结构基本相同，但来自不同的地方。这是唯一的区别，但其中一个很慢。

它不是实时从 SQL 中提取数据，而是从 SQL 中检索数据（不使用 Excel），然后将值粘贴到 Excel 中进行计算。我所说的系统是指手动输入所有信息的程序（不是每晚自动从系统更新的 SQL），即帐户人口统计、费用、付款、预约等。我提取到 Excel 中的数据是提供商名称、提供商类型、负责付款人、费用、付款、调整和日期。它每天提取和更新，这是问题的一部分，每次我添加新的一天的数据时，它都必须重新处理以包含新行。

以下是一些公式

=IF(ISERROR(SUM(IF((Source!$A$5:$A$150000='Provider Weekly-WTD'!$A8)*((Source!$K$5:$K$150000>='Provider Weekly-WTD'!B$6)*(Source!$K$5:$K$150000<('Provider Weekly-WTD'!B$6)+7)),Source!$E$5:$E$150000,""))),"-",SUM(IF((Source!$A$5:$A$150000='Provider Weekly-WTD'!$A8)*((Source!$K$5:$K$150000>='Provider Weekly-WTD'!B$6)*(Source!$K$5:$K$150000<('Provider Weekly-WTD'!B$6)+7)),Source!$E$5:$E$150000,"")))

，

=IF(ISERROR(SUM(IF(($A8=Source!$A$5:$A$150000)*(IF(Source!$K$5:$K$150000=(B$6+6),(B$6+6),MAX(IF((Source!$A$5:$A$150000=$A8)*(Source!$K$5:$K$150000>=B$6)*(Source!$K$5:$K$150000<(B$6+7)),Source!$K$5:$K$150000)))=Source!$K$5:$K$150000),Source!$I$5:$I$150000))),"-",SUM(IF(($A8=Source!$A$5:$A$150000)*(IF(Source!$K$5:$K$150000=(B$6+6),(B$6+6),MAX(IF((Source!$A$5:$A$150000=$A8)*(Source!$K$5:$K$150000>=B$6)*(Source!$K$5:$K$150000<(B$6+7)),Source!$K$5:$K$150000)))=Source!$K$5:$K$150000),Source!$I$5:$I$150000)))

，

=SUMIFS($I$5:$I$150000,$A$5:$A$150000,$A7111,$C$5:$C$150000,$C7111,$K$5:$K$150000,(MAX(IF(($A7111=$A$5:$A$150000)*($C7111=$C$5:$C$150000)*($K7111>$K$5:$K$150000),$K$5:$K$150000))))

， 和

=$D7111+$E7111-$F7111+$G7111-$H7111

由于我在这里显示实际数据的能力有限，只能显示公式，所以我想我是在询问我使用的公式是否正确，以及/或者是否导致了问题。如果不是公式，那么数据或数据格式是否可能导致问题。例如，有人告诉我，由于日期格式（在格式设置中）前面有 *，所以它导致了问题。

答案1

它不会是根据行大小和计算时间给出的公式 - 除非源表实际上链接到其他源，即您的计算是否试图使用 SQL 输出链接到另一个工作簿？

两个建议尝试清理你的文件，通过清理 ayd 冗余空间和可能的损坏

  1. 下载并安装免费的 ASAP Utilities 插件。选择所有工作表，从 ASAP 运行“工作表”实用程序，然后选择“25 删除未使用的空结尾行/列”
  2. 然后选择所有工作表，右键单击工作表选项卡，选择“移动或复制......”并选择“新书”

如果你能找到某个地方上传有问题的工作簿（必要时进行清理），那么这也会有所帮助

答案2

您的公式所指的范围比您使用的范围大得多：

Source!$A$5:$A$150000

然后，您的公式将该范围内的每个单元格与单个参考单元格进行比较：

Source!$A$5:$A$150000 = 'Provider Weekly-WTD'!$A8

减少公式引用的单元格跨度，你会发现速度有所提高。

相关内容