☰

问题

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

问题

您无法使用 Evaluate Formula 执行此操作，因为这不是该函数的目的。这就是为什么它被称为评价，它用于评估公式。您需要的是某种解包。这是一个有点特殊的需要，因此它没有作为 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

适用案例

我创建了一些示例，以便您可以看到它的实际效果。在本例中，我演示了如何使用字符串。您可以看到它运行完美。唯一的小错误是算法不知何故将分号更改为逗号。替换它们后（如我在本例中所做的那样），您将获得正确的输出。 使用字符串

在这里，您可以看到它如何与数字一起工作。现在，我们面临的第一个问题是算法不关心数学运算顺序，这就是为什么红色数字是 6，而它应该是 10。如果我们将敏感运算（如加法和减法）放在括号中，那么输入的给定公式将给出相同的输出，就像您在底部绿色数字中看到的 10 一样。 处理数字

不起作用的情况

这个算法并不完美。我只尝试实现最常见的用途，因此可以通过添加更多功能来处理其他情况（例如范围），从而对其进行改进。
正如您在本例中看到的，我使用SUM()范围作为参数。由于算法从上到下解密单元格内容，因此它首先替换SUM()参数，然后再替换其他内容。因此，保留:在其位置，而其周围的所有内容都被替换，因此在其附近替换了新的单元格，这将改变其含义。因此输出将是错误的。因此在这种情况下，您只能使用此宏来研究原始公式。 使用范围

答案2

鉴于您在问题中使用了“跟踪”一词，我认为您熟悉Excel 公式审核部分中的Trace Precendents和函数？Trace Dependents

以下是 dummies.com 对每个公式审核工具的简要说明：

了解 Excel 2010 的公式审核工具

答案3

今天我们只是FORMULATEXT()以相当明显的方式使用，但当时错过了这个技巧，今天对于那些使用早期版本的人来说（比如我的姻亲使用 2007），是GET()在命名范围内使用不同的 Excel 4 宏命令。

它能做的很多事情之一就是将公式读取为文本，就像FORMULATEXT()现在一样。然后就是组合字符串的问题。稍微IF()折腾一下，看看是否有东西可获得，然后就万事大吉了。

相关内容