自定义函数可能无法在 Excel 中计算预期值

2025-05-26
适用于: Microsoft Excel

症状

计算工作表时，某些单元格可能似乎计算了错误的值。

原因

如果存在以下情况，则会发生这种问题：

工作表上的单元格包含自定义函数。
自定义函数包含一个或多个引用工作表上单元格区域的参数。
自定义函数的结果依赖于与其直接引用的单元格相比更多的单元格。

此行为由 Microsoft Excel 设计。当 Excel 计算包含自定义函数的单元格时，它会重新计算作为参数传递给自定义函数的所有单元格区域。如果函数的结果依赖于函数未显式引用的单元格，则可能不会重新计算这些单元格。

解决方法

若要解决此问题，请使用以下任一方法。

方法 1：修改函数，以便传递所有相关单元格区域

修改函数以接受计算函数结果所需的所有单元格作为参数。

方法 2：使自定义函数可变

Microsoft 提供的编程示例仅用于进行说明，而不提供明示或默示担保。这包括但不限于适销性或对特定用途的适用性的默示担保。本文假设您熟悉正在演示的编程语言和用于创建和调试过程的工具。 Microsoft 支持工程师可以帮助解释特定过程的功能，但他们不会修改这些示例以提供附加的功能或构造过程来满足你的特定要求。如果使自定义函数可变，则不会出现此问题。若要使自定义函数可变，请将以下代码行添加到函数。

Application.Volatile

注意

如果使自定义函数可变，则每次更改值或重新计算打开的工作簿时，该函数都会重新计算。这可能会影响工作表模型的性能。

方法 3：强制 Excel 重新计算所有打开的工作簿

按 Ctrl+Alt+F9 重新计算所有打开的工作簿中的值。在 Microsoft Office Excel 2007 中，按 Ctrl+Alt+Shift+F9 以重新计算所有打开的工作簿中的值。

更多信息

问题示例

若要说明此问题，请执行以下步骤：

关闭并保存所有打开的工作簿，然后打开新工作簿。
启动 Visual Basic 编辑器 (按 Alt+F11) 。
在“插入”菜单上，单击“模块”。

在模块工作表中键入下面的代码：

' This function counts the number of blank cells by starting from the cell 
' referred to by the rngStartCell argument and moving up the column.

Function FindTextUp(rngStartCell As Range) As Single
    Dim iIndex As Integer

For iIndex = 0 To 100
        If rngStartCell.Value <> "" Then
            FindTextUp = iIndex
            Exit Function
        Else
            Set rngStartCell = rngStartCell.Offset(-1, 0)
        End If
    Next iIndex
End Function

按 Alt+F11 返回到 Excel。
在单元格 A2 中键入“Test”，然后按 Enter。
在单元格 A10 中键入以下公式，然后按 Enter：
```
=FindTextUp(A9)
```
公式返回值 7。
在单元格 A5 中键入“其他测试”，然后按 Enter。

当预期值为 4 时，公式仍返回值 7。在此示例中，FindTextUp 函数显式引用单元格 A9。但是，该函数可能依赖于单元格 A1：A8，具体取决于在工作表中输入的数据。

如果实现本文“解决方法”部分中的方法 1，函数将计算预期结果。以下代码行演示了如何修改此示例中的函数，以便计算预期结果。

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

现在，如果将步骤 7 中的函数调用替换为以下函数调用，该函数将始终返回预期结果。

=FindTextUp(A9,A1:A8)

通过