在Excel中检测到挥发性公式-保持距离

上周,我在一个论坛上遇到了一个excel问题。我立即采取行动,创建了一个很长的公式,从抵消()

几个小时之内,它就被其他专家击落了,因为它含有挥发性的公式。

我立刻意识到自己犯了重大的罪。

所以,让我来分享一下我在Excel中所学到的关于volatile函数的知识。简单来说,它是一个可以让你的excel变得更好的函数电子表格缓慢,因为它会一次又一次地重新计算公式。许多操作可以触发这种情况(稍后将在本文中描述)。

一个非常简单的volatile函数示例是NOW()函数(用于获取单元格中的当前日期和时间)。每当编辑工作表中的任何单元格时,都会重新计算它。如果您的数据集比较小,公式的数量也比较少,这是可以接受的,但是如果您的电子表格比较大,这可能会大大降低处理速度。

以下是一些常见的volatile函数,应该避免使用:

超级不稳定的公式:

几乎不稳定的公式:

好消息是,我最喜欢的INDEX (),行(),列()不要表现出波动性。坏消息是条件格式是不稳定的

另外,确保在非易失性函数中没有这些函数,如IF()、LARGE()、SUMIFS()和COUNTIFS(),因为这最终会使整个公式易失性。

例如,假设有一个公式=If(A1>B1, " Trump Excel ",RAND())。现在,如果A1大于B1,它将返回Trump Excel,如果不大于B1,则返回RAND(),这是一个volatile函数。

触发重新计算易失性公式
  • 输入新的数据(如果Excel处于自动重新计算模式)。
  • 明确指示Excel重新计算工作簿的全部或部分。
  • 删除或插入行或列。
  • 创建时保存工作簿重新计算之前保存的选项设置(它在文件- >选项- >公式)。
  • 执行某些自动筛选操作。
  • 双击行或列分隔符(在自动计算模式下)。
  • 添加、编辑或删除已定义的名称。
  • 重命名工作表。
  • 改变工作表相对于其他工作表的位置。
  • 隐藏或不隐藏行,但不隐藏列。

如果你的工作表中有很多公式,使它变慢,我建议你切换到手动计算模式。这停止自动重新计算,并给你的权力告诉excel何时计算(通过点击“计算现在”或按F9)。此选项在公式- >计算选项中可用。

易失性公式-手动计算

相关教程:
Excel电子书订阅

免费的EXCEL的书

获得51个Excel提示电子书来提高你的生产力,更快地完成工作

关于“Excel中发现的挥发性公式-保持距离”的5个想法

  1. 我试图在我的Excel环境中避免这个问题,但是这个博客说明了“错误”有时在明智地使用时是有用的。谢谢你!

  2. 我有一个非常复杂的工作簿,其中包含多个NOW()和TODAY()引用,随着它在一年中增长的大小,运行事务张贴宏开始花费更长的时间。

    1.我用指向我的TBL工作表中名为NVDate (Non-Volatile Date)的单元格的指针替换了对NOW()和TODAY()的所有引用。

    2.然后我添加了以下代码到ThisWorkbook对象:

    私人潜艇Workbook_Open ()
    ' Seed NVDate以消除volatile Today()和Now()函数
    TBL.Range(“NVDate”)。值=日期
    终止子

    速度的增长是惊人的。一个花费了9秒的交易现在只需要不到1秒就可以完成了。

    • 谢谢这个代码。非常有用,但是下面的细节可能对一些人有帮助。

      私人潜艇Workbook_Open ()
      ' Seed NVDate以消除volatile Today()和Now()函数
      工作表(“台”).Range(“NVDate”)。值=日期
      终止子

评论都关门了。