我有一个非常复杂的Excel电子表格(通过多个工作表使用其他公式访问单元格的公式)最后使用来自其他多个单元格(参数)的输入来计算单个单元格(输出)。是否有一种自动化的方法(Excel宏或独立工具)可以从输出单元格开始并递归展开计算,直到它直接根据参数单元格来表示公式?
澄清度
在评论中,有人提出了“评估公式”工具的建议。虽然我找不到在Mac的Excel 2008中激活它的方法,但从其描述看来,它似乎允许用户逐步评估单元格。那不是我所需要的。我需要的是一种将给定单元格中的公式转换为可以用最终单元格(包含值但不包含公式的单元格)表示的等效公式的方法,该单元格可以引用包含该公式的其他单元格。
这是一个简单的例子。让
A1
包含 = B1 + C1
B1
包含 = B2 * B2
C1
包含 = C2 * C2
B2
包含 1
C2
包含 2
评估公式可让我逐步进行的计算,A1
以得出的最终值5
。我需要的是一个无需实际评估即可展开A1
公式的工具= B2 * B2 + C2 * C2
。
您不能使用“评估公式”来执行此操作,因为这不是该函数的目的。这就是为什么将其称为validate,用于评估公式的原因。您想要的是某种包装。这有点特殊的需要,因此它不是作为Excel中的工具实现的,但是如果您创建一些Visual Basic函数/宏,则有解决方案。
如您在本教程中所见,创建一个VBA代码模块(宏)。
Module
在Insert
。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
要使其工作,只需创建一个宏(如我在答案开头所描述的),然后将代码复制粘贴即可。之后,您可以将其与=CellFormula(A1)
whereA1
可以使用的任何1x1单元一起使用。
我创建了一些示例,以便您可以实际使用它。在这种情况下,我演示了字符串的用法。您可以看到它运行完美。唯一的小错误是该算法为何将分号更改为逗号。替换它们之后(如本例中所做的那样),您将获得正确的输出。
在这里,您可以看到它如何与数字一起使用。现在,我们面临的第一个问题是算法不关心数学运算顺序,这就是为什么红色数字应为10时为6的原因。如果将敏感的运算(如加减法)放在括号中,输入的给定公式将提供与底部10的绿色数字相同的输出。
该算法不是完美的。我只是尝试实现最常见的用途,因此可以通过添加更多功能来处理其他情况(例如范围)来加以改进。
如您在本示例中看到的,我使用SUM()
范围作为参数。由于该算法从上到下解密单元格内容,因此从替换SUM()
参数开始,然后再替换其他任何内容。因此,:
在其周围的所有位置均被替换时,其位置保持不变,因此在其附近替换了新的单元格,这将改变其含义。因此输出将是错误的。因此,在这种情况下,您只能使用此宏来研究原始公式。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句