Google表格在不需要时重新计算单元格 [英] Google sheets recalculating cell when not needed

问题描述

即使依存关系没有变化，是否有任何理由让Google表格重新计算单元格?我遇到的IMPORTRANGES巨大，在重新计算成千上万未更改的单元格时造成了巨大的延迟.

Is there any reason for Google Sheets to recalculate a cell even though no dependencies have changed? I'm having this issue with huge IMPORTRANGES, creating huge delays in recalculating thousands of cells which didn't changed.

为使此问题可见，我创建了一个简单的测试电子表格您可以在这里访问.

To make this issue visible, I've created a simple test spreadsheet you can visit here.

此电子表格包含一个自定义函数，该函数返回一个2维数组，其中包含170'000个随机值，包括当前日期时间，因此我们知道何时调用该自定义函数.自定义函数采用1个常数.通常，当我打开电子表格时，会调用自定义函数，再次填充所有内容.但是，不应这样做，因为自定义函数的唯一参数是常量.

This spreadsheet contains a custom function returning an 2-dim array containing 170'000 random values, including the current datetime so we know when the custom function was called. The custom function takes 1 parameter which is a constant. Very often when I open the spreadsheet, the custom function gets called, refilling everything again. However, it shouldn't since the only argument to the custom function is a constant.

有什么主意吗?

当然，这只是一个例子，我之所以这样写是因为我有大量的电子表格，即使没有任何更改，打开它们时大约需要10分钟重新计算.

Of course this is just an example, I'm writing this because I have huge spreadsheets with takes around 10 minutes to recalc when I open them, even though nothing have changed.

推荐答案

重新计算

Google电子表格在打开时会重新计算.可能会有一些限制，例如:

Recalculation

Google spreadsheets are recalculated when they are opened. Some limitations could apply, like:

• 如果通过打开电子表格或通过基于时间的阈值(例如IMPORTDATA，IMPORTHTML，IMPORTRANGE，IMPORTXML)重新计算服务器的内置函数和自定义函数，它们可能会冻结".

AFAIK某些计算的冻结"未包含在 Google文档帮助中，但这种Google通常会对免费的消费者帐户(通常是那些使用gmail.com域电子邮件地址的帐户)施加一定的限制，以防止滥用行为".付费"(现在称为G Suite)帐户可以具有更高的配额".

AFAIK the "freeze" of some calculations is not included on the Google Docs Help but this kind of limitations are usually applied by Google for free consumer accounts (usually those with with a gmail.com domain email address) in order to "prevent abuses". "Paid" (now they are called G Suite) accounts could have "higher quotas".

如对问题的评论所建议的那样，如果这是一个问题，则应考虑如何构建电子表格.

As was suggested on the comments to the question, if this is an issue, you should think about how are you are building your spreadsheets.

基本是减少工作表，行，列和公式的数量，以减小大小和复杂性.

The basic is to reduce the number of sheets, rows, columns and formulas to reduce size an complexity.

如果您使用公式进行仅一次的计算，则将这些公式替换为其结果.在这种情况下，粘贴值很方便.

If you are using formulas to do calculations that are required only one time, replaces those formulas with their results. Paste as values come handy on this cases.

仅应按需进行的其他计算应移至脚本或外部电子表格，您可以从中导入计算值.

Other calculations that should be made only on demand, should be moved to a script or to an external spreadsheet from where you could import the calculated values.

这篇关于Google表格在不需要时重新计算单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！