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

问题描述

我有一个索引/匹配公式，它根据某些单元格的日期值匹配特定文件.公式如下:

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)

我注意到即使我导入了一个新的 CSV 值也没有改变.我获得要更新的值的唯一方法是通过从顶部拖动到最后一个单元格来重新输入公式，就像手动操作一样.

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.

Google 表格公式在以下情况下重新计算

Google Sheets formulas are recalculated when

1. 电子表格已打开
2. 函数参数发生变化
3. NOW、TODAY、RAND 和 RANDBETWEEN 函数根据电子表格设置进行更新，在更改时、在更改时以及每分钟、更改时和每小时
4. 外部数据函数按以下时间间隔重新计算:
   • 导入范围:30 分钟
   • ImportHtml、ImportFeed、ImportData、ImportXml:1 小时
   • GoogleFinance:最多可能延迟 20 分钟

注意:某些函数和自定义函数不允许非确定性函数作为参数.

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

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

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