主页 / computer / 问题 / 1813682
Accepted
Regus Pregus
Asked: 2023-10-23 05:37:44 +0800 CST

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

  • 772

众所周知，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。

microsoft-excel

1 个回答

  1. Best Answer

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

    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()和/或 with 那样指定较小的范围INDEX():INDEX()
    4. 限制：仅访问一行/列，因此您不能像 volatileOFFSET()和/或 那样指定更宽/更高的范围INDEX():INDEX()
    5. 行/列号是从 1 开始的，而不是像 中那样从 0 开始，因此在切换到使用OFFSET()时必须小心。OFFSET()INDEX()
    6. SUMIF()正如评论中所述，不仅适用于，而且适用于任何其他采用数组的函数，例如SUM()、MIN()、、等。MAX()AVERAGE()LINEST()

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

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

    • 0

相关问题