☰

問題

tag-icon • tag-icon microsoft-excel worksheet-function
問題

我有一個非常複雜的 Excel 電子表格（公式透過多個工作表中的其他公式存取儲存格），最終使用來自多個其他儲存格（參數）的輸入計算單一儲存格（輸出）。是否有一種自動化方法（Excel 巨集或獨立工具）可以從輸出單元格開始並遞歸展開計算，直到直接用參數單元格表示公式？

澄清

評論中提出了評估公式工具的建議。雖然我找不到如何在 Excel 2008 for Mac 中啟動它，但從它的描述聽起來它允許用戶逐步評估單元格。那不是我需要的。我需要的是一種將給定單元格中的公式轉換為以最終單元格（包含值但不包含公式）表示的等效公式的方法，該給定單元格可能會引用包含公式的其他單元格。

這是一個簡單的例子。讓

  • A1包含= B1 + C1
  • B1包含= B2 * B2
  • C1包含= C2 * C2
  • B2包含1
  • C2包含2

評估公式將允許我逐步完成 的計算A1以獲得 的最終值 5。我需要的是一個可以展開A1公式= B2 * B2 + C2 * C2而無需實際評估它的工具。

答案1

問題

您不能使用評估公式來執行此操作，因為這不是該函數的目的。這就是為什麼它被稱為評價，用於評估公式。你想要的是某種拆包。這是一個有點特殊的需求，因此它沒有作為 Excel 中的工具實現，但如果您建立一些 Visual Basic 函數/宏，則有一些解決方案。

建立一個 VBA 程式碼模組（巨集），如您所見本教程。

  1. 按Alt+F11
  2. 單擊Module進入Insert.
  3. 貼上代碼。
Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

然後在儲存格中輸入以下內容：=CellFormula(A1)

這將告訴細胞的公式。這段程式碼的唯一問題是它只適用於一個層級。如果您也想解壓縮包含的儲存格公式，那麼您需要帶有遞歸的更複雜的程式碼。

解決方案

這是一個漫長的旅程，但我為您創建了一個 VBA 巨集來實現此功能。我並不是說這段程式碼適用於每個公式，但它適用於大多數/某些公式。另外，我並沒有聲明此程式碼將產生與最初輸入的程式碼等效的公式或將給出與原始程式碼相同的結果。

原始碼

Option Explicit

Function isChar(char As String) As Boolean
    Select Case char
        Case "A" To "Z"
            isChar = True
        Case Else
            isChar = False
    End Select
End Function

Function isNumber(char As String, isZero As Boolean) As Boolean
    Select Case char
        Case "0"
            If isZero = True Then
                isNumber = True
            Else
                isNumber = False
            End If
        Case "1" To "9"
            isNumber = True
        Case Else
            isNumber = False
    End Select
End Function

Function CellFormulaExpand(formula As String) As String
    Dim result As String
    Dim previousResult As String
    Dim cell As Range
    Dim stringArray() As String
    Dim arraySize As Integer
    Dim n As Integer
    Dim trimmer As String
    
    Dim c As Integer 'character number
    Dim chr As String 'current character
    Dim tempcell As String 'suspected cell's temporaly result
    Dim state As Integer 'state machine's state:
    Dim stringSize As Integer

    result = formula
    previousResult = result
    state = 0
    stringSize = 0

    For c = 0 To Len(formula) Step 1
        chr = Mid(formula, c + 1, 1)
        Select Case state
            Case 0
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 5
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 1
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell & chr
                ElseIf isChar(chr) Then
                    state = 2
                    tempcell = tempcell & chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell & chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 2
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf isChar(chr) Then
                    state = 3
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 3
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                ElseIf chr = "$" Then
                    state = 6
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 4
                If isNumber(chr, True) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    stringSize = stringSize + 1
                    ReDim Preserve stringArray(stringSize - 1)
                    stringArray(stringSize - 1) = tempcell
                    tempcell = ""
                End If
            Case 5
                If isChar(chr) Then
                    state = 1
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case 6
                If isNumber(chr, False) Then
                    state = 4
                    tempcell = tempcell + chr
                Else
                    state = 0
                    tempcell = ""
                End If
            Case Else
                state = 0
                tempcell = ""
        End Select
    Next c
    If stringSize = 0 Then
        CellFormulaExpand = result
    Else
        arraySize = UBound(stringArray)
        For n = 0 To arraySize Step 1
            Set cell = Range(stringArray(n))
            If Mid(cell.formula, 1, 1) = "=" Then
                trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
                If trimmer <> "" Then
                    result = Replace(result, stringArray(n), trimmer)
                End If
            End If
        Next
        If previousResult <> result Then
            result = CellFormulaExpand(result)
        End If
    End If
    CellFormulaExpand = result
End Function

Function CellFormula(rng As Range) As String
    CellFormula = CellFormulaExpand(rng.formula)
End Function

要使其工作，只需創建一個巨集（正如我在答案開頭所描述的那樣）並複製貼上程式碼。之後，您可以將其與任何類型的 1x1 單元一起=CellFormula(A1)使用。A1

有效案例

我創建了一些示例，以便您可以看到它的實際效果。在本例中，我示範了字串的使用。你可以看到它運行完美。唯一的小錯誤是，不知何故，演算法將分號更改為逗號。替換它們後（正如我在本範例中所做的那樣），您將獲得正確的輸出。 使用字串

在這裡，您可以看到它如何處理數字。現在，我們面臨的第一個問題是演算法不關心數學運算順序，這就是為什麼紅色數字應該是 10 時卻是 6。定公式將給出與底部綠色數字10 相同的輸出。 處理數字

不起作用的情況

這個演算法並不完美。我只嘗試實現最常見的用途，因此可以透過添加更多處理其他情況（例如範圍）的功能來改進它。
正如您在本範例中看到的，我使用SUM()範圍作為參數。由於演算法從上到下解密單元格內容，因此它從替換參數開始SUM()，然後再替換任何其他內容。因此，它:留在原來的位置，而它周圍的一切都被替換了，所以靠近它的新細胞被替換，誰會改變它的意義。因此輸出將是錯誤的。所以這種情況就只能用這個宏來研究原公式了。 使用範圍

答案2

鑑於您在問題中使用了“跟踪”一詞，我假設您熟悉Excel 公式審核部分中的Trace Precendents和函數？Trace Dependents

以下是 dummies.com 中每個公式審核工具的簡要說明：

了解 Excel 2010 的公式審核工具

答案3

今天我們只是FORMULATEXT()以相當明顯的方式使用，但當時錯過了這個技巧，而今天對於那些使用早期版本的人（例如我的姻親使用的是 2007 年），是GET()在命名的內部使用不同的Excel 4 宏命令。

它可以做的許多事情之一是將公式作為文字讀取，就像FORMULATEXT()現在一樣。然後就是組合字串的問題。有點IF()折騰來測試是否有東西可以得到，而且一個人無家可歸。

相關內容