O problema
Você não pode fazer isso com a Fórmula Avaliar porque este não é o propósito desta função. É por isso que é chamado avaliar , é para avaliar as fórmulas. O que você quer é algum tipo de descompactação. Esta é uma necessidade um pouco especial, por isso não é implementada como uma ferramenta no Excel, mas existem soluções se você criar algumas funções / macros do Visual Basic.
Crie um módulo de código VBA (macro) como você pode ver em este tutorial .
- Pressione Alt + F11
- Clique para
ModuleemInsert. - Colar código.
Function CellFormula(Target As Range) As String
CellFormula = Target.Formula
End Function
Em seguida, digite o seguinte para uma célula: =CellFormula(A1)
Isto irá dizer a fórmula da célula. O único problema com este código é que ele funciona apenas para um nível. Se você quiser descompactar as fórmulas de células contidas também, então você precisa de um código mais complexo com recursão.
A solução
Foi uma longa jornada, mas criei uma macro VBA para você que implementa essa função. Não afirmo que esse código funcionará para todas as fórmulas, mas funcionará na maioria delas. Também não afirmo que este código irá gerar fórmulas que equivalem ao código inserido originalmente ou darão o mesmo resultado que o original.
Código-fonte
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
Para que funcione, basta criar uma macro (como descrevi no início da resposta) e copiar e colar o código. Depois disso, você pode usá-lo com o =CellFormula(A1) , em que A1 pode ser qualquer tipo de célula 1x1.
Casos que funcionam
Eu criei alguns exemplos para que você possa vê-lo em ação. Neste caso, eu demonstro o uso com strings. Você pode ver que funciona perfeitamente. O único pequeno bug é que em algum lugar o algoritmo muda o ponto e vírgula para vírgula. Depois de substituí-los (como fiz neste exemplo), você obtém a saída correta.
Aquivocêpodevercomofuncionacomnúmeros.Agoraenfrentamosoprimeiroproblema,queoalgoritmonãosepreocupacomasequênciadeoperaçõesmatemáticas,éporissoqueonúmerovermelhoé6quandodeveriaser10.Secolocarmosasoperaçõessensíveis(comoadiçãoesubtração)entreparênteses,entãoodadofórmulaintroduzidadevoltadaráamesmasaídaquevocêpodevernonúmeroverdenaparteinferiorquediz10.
Casos que não funcionam
Este algoritmo não é perfeito. Eu só tentei implementar os usos mais comuns, para que ele possa ser melhorado adicionando mais recursos que lidam com outros casos como intervalos.
Como você pode ver neste exemplo, usei SUM() com um intervalo como parâmetro. Como o algoritmo descriptografa o conteúdo das células de cima para baixo, ele começa com a substituição dos parâmetros SUM() do que mais tarde por qualquer outra coisa. Portanto, o : fica em seu lugar enquanto tudo ao redor é substituído, então as novas células são substituídas perto dele, o que mudará o significado dele. Assim, a saída estará errada. Portanto, neste caso, você só pode usar essa macro para estudar a fórmula original.
