强制Google表格公式重新计算 [英] Force google sheet formula to recalculate

问题描述

我有一个索引/匹配公式，它与基于特定单元格的日期值的特定文件匹配。以下是公式：

  = IFERROR（INDEX（INDIRECT（'& TEXT（$ O $ 3，mm-dd $&$; $ D $ 500），MATCH（$ D5，INDIRECT（'& TEXT（$ O $ 3，mm-dd-yyyy）& '！$ B $ 3：$ B500），0）），0）
  

即使我导入了新的CSV，值也没有改变。只有这样，我才能更新数值，从本质上重新输入公式，就像手动操作一样从顶部拖拽到最后一个单元格。

我尝试改变重新计算时间在设定下，但似乎这个设置并不适用于我的公式，因为我将它设置为每分钟都没有发生。

我想写一个脚本让它重新输入公式并将它设置为每天运行，但我希望有一个更简单的方法来做到这一点。

解决方案

简答

您的公式不会被重新计算，因为其论点不会改变。正如你自己已经想出的那样，解决方案是重新将合适的参数输入到公式引用的单元格中。

解释

当

1. 电子表格打开时，Google表格公式会重新计算


2. 函数参数更改


3. 函数NOW，TODAY，RAND和RANDBETWEEN根据电子表格设置，更改，更改和每分钟，更改和每小时更新


4. 外部数据函数按以下时间间隔重新计算：
   
   
   
   
   • 导入范围：30分钟
   
   
   • ImportHtml，ImportFeed，ImportData，ImportXml：1小时
     
   • GoogleFinance：可能会被延迟20分钟
   
   

注意：某些函数和自定义函数不允许确定性函数作为参数。

• 更改电子表格的区域设置，时区，重新计算和语言
  

I have an index/match formula that matches a specific file based on the date value of certain cells. Here's the formula:

=IFERROR(INDEX(INDIRECT("'"&TEXT($O$3,"mm-dd-yyyy")&"'!"&"$D3:$D$500"),MATCH($D5,INDIRECT("'" & TEXT($O$3, "mm-dd-yyyy") &"'!$B$3:$B500"),0)),0)

I noticed the values did not change even when I imported a new CSV. Only way I got the values to update was to essentially re-enter the formula by dragging from top to the last cell like one would manually do.

I tried changing the recalculation time under settings, but it seemed like the setting does not apply to my formula, as I set it to every minute and nothing happened.

I thought about writing a script to have it re-enter the formulas and set it to run every day, but I'm hoping that there's a easier way to do this.

解决方案

Short answer

Your formula is not being recalculated because its arguments do not change. The solution is, as you already figured out by yourself, to re-enter the proper arguments into cells that your formula references to.

Explanation

Google Sheets formulas are recalculated when

1. The spreadsheet is open
2. The function arguments changes
3. The functions NOW, TODAY, RAND, and RANDBETWEEN are updated are according to the spreadsheet settings, on change, on change and every minute, on change and every hour
4. External data functions recalculate at the following intervals:
   • ImportRange: 30 minutes
   • ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
   • GoogleFinance: may be delayed up to 20 minutes

Note: Some functions and custom functions doesn't allow not deterministic functions as arguments.

References

• Change a spreadsheet's locale, time zone, recalculation, and language

这篇关于强制Google表格公式重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！