☰

非易失性 Excel 公式用于计算给定单元格任意偏移范围内的总和

tag-icon • tag-icon microsoft-excel worksheet-function
非易失性 Excel 公式用于计算给定单元格任意偏移范围内的总和

众所周知，Excel 的 OFFSET 函数是不稳定的并且会导致重新计算，因此 Excel 会要求用户保存任何具有此类函数的加载文件，即使用户没有进行任何实际更改。

众所周知，可以使用非易失性 INDEX 函数来解决此问题。

所以：

  • =OFFSET($A$1,0,5)将返回 F1 单元格的值，并且它将是易失性的
  • =INDEX($A$1,0+1,5+1)将返回相同 F1 单元格的值，并且它不会是易失性的

现在让我们尝试对一个范围而不是单个单元格执行相同的操作：

  • =SUM(OFFSET($A$1,0,5,3,1))将返回 F1:F3 单元格的值总和，并且如预期的那样具有波动性
  • 在 Office 2007 中尝试使用 INDEX 进行相同操作会导致意外结果：=SUM(INDEX($A$1:$Z$1000,0+1,5+1):INDEX($A$1:$Z$1000,2+1,5+1))将返回 F1:F3 单元格值的总和但它会波动（通过打开该文件并尝试立即关闭它来确认 - Excel 将要求用户保存更改）。

因此，虽然INDEX()是非易失性的，但使用INDEX():INDEX()访问范围会使公式变得易失性，至少在 Office 2007 中是这样。

有没有其他方法可以访问任意偏移的范围而不会引起波动？

更新：按照@DjC 评论，Office 365 中的 INDEX():INDEX() 并没有意外的波动，但我正在寻找一种可以适用于尽可能多的 Office 早期版本的解决方案，因为我需要至少一直保持向后兼容性到 Office 2007。

答案1

好的，由于没有发布答案，这里是我根据评论和我自己的研究对调查结果的总结。

A.INDEX():INDEX()在 Office 365 中不是易失性的，但不幸的是，在 Office 2007 中是易失性的。确定 2007 和 365 之间的哪个早期版本的 Excel 修复了不必要的易失性问题可能也会有所帮助，但如果您需要向后兼容，那么INDEX():INDEX()这不是您的最佳选择。

B. 无论 Excel 版本如何，始终不变的解决方案是使用INDEX()省略行或列参数（或将其设置为 0）的方法，这将导致访问整个行（或列）。关于此解决方案需要了解的事项：

  1. 它看起来像这样：=SUM(INDEX(A1:Z1000,,place_your_column_number_here)对于列或=SUM(INDEX(A1:Z1000,place_your_row_number_here,)行
  2. 指定 0 而不是省略参数会执行相同的操作：=SUM(INDEX(A1:Z1000,0,place_your_column_number_here)对于列或=SUM(INDEX(A1:Z1000,place_your_row_number_here,0)行
  3. 限制：访问整个行/列，因此您无法像使用 volatileOFFSET()和/或那样指定较小的范围INDEX():INDEX()
  4. 限制：只能访问一行/列，因此您无法像使用 volatileOFFSET()和/或使用那样指定更宽/更高的范围INDEX():INDEX()
  5. 行/列号以一开始，而不是像 那样以零开始，因此在从 切换到使用OFFSET()时必须小心。OFFSET()INDEX()
  6. 不仅可以与评论中指出的一样正常工作，还可以与任何其他SUMIF()采用数组的函数一起工作，例如SUM()，，，，等等。MIN()MAX()AVERAGE()LINEST()

总之，如果您只需要访问一行/列，而不是任意矩形范围，那么INDEX()省略行/列参数是最好的非易失性选择。如果您需要任意矩形范围访问，那么最好但不完美的选择是，INDEX():INDEX()因为它在至少较新版本的 Excel 中是非易失性的。

如果有人知道更好的解决方案，请发布。

相关内容