自定義函式可能無法計算 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)

共用方式為