O problema

Question

Tenho uma planilha Excel muito complicada (fórmulas que acessam células com outras fórmulas em diversas planilhas) que no final calcula uma única célula (saída) com entrada de diversas outras células (parâmetros). Existe uma maneira automatizada (uma macro do Excel ou uma ferramenta autônoma) que poderia começar na célula de saída e desenrolar recursivamente os cálculos até expressar a fórmula diretamente em termos de células de parâmetro?

Esclarecimento

Nos comentários houve sugestão da ferramenta Avaliar Fórmula. Embora não tenha conseguido descobrir como ativá-lo no Excel 2008 para Mac, desua descriçãoparece que permite ao usuário percorrer a avaliação de uma célula. Não é disso que eu preciso. O que preciso é de uma maneira de converter a fórmula em uma determinada célula que possa fazer referência a outras células que contenham fórmulas, em uma fórmula equivalente expressa em termos de células finais (aquelas que contêm valores, mas não fórmulas).

Aqui está um exemplo simples. Deixar

• A1conter= B1 + C1
• B1conter= B2 * B2
• C1conter= C2 * C2
• B2conter1
• C2conter2

Avaliar a fórmula me permitiria avançar no cálculo A1para chegar ao valor final de 5. O que eu preciso é de uma ferramenta que desenrole A1a fórmula = B2 * B2 + C2 * C2sem realmente avaliá-la.

Answer 1

O problema

Você não pode fazer isso com Avaliar Fórmula porque esse não é o propósito da função. É por isso que é chamadoAvalie, é 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 emeste tutorial.

1. Pressione Alt+F11
2. Clique para Moduleentrar Insert.
3. Cole o código.

Function CellFormula(Target As Range) As String
   CellFormula = Target.Formula
End Function

Em seguida, insira o seguinte em uma célula:=CellFormula(A1)

Isso dirá a fórmula da célula. O único problema com este código é que ele só funciona para um nível. Se você quiser descompactar também as fórmulas das células contidas, precisará 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 esta função. Não afirmo que este código funcionará para todas as fórmulas, mas funcionará na maioria/algumas delas. Além disso, não afirmo que este código gerará fórmulas equivalentes ao código inserido originalmente ou dará o mesmo resultado do 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 =CellFormula(A1)qualquer A1tipo de célula 1x1.

Casos em que funciona

Criei alguns exemplos para que você possa ver em ação. Neste caso, demonstro o uso com strings. Você pode ver que funciona perfeitamente. O único pequeno bug é que, de alguma forma, o algoritmo muda os pontos e vírgulas para vírgulas. Depois de substituí-los (como fiz neste exemplo), você obtém a saída correta.

Aqui você pode ver como funciona com números. Agora, enfrentamos o primeiro problema de que o algoritmo não se importa com a sequência de operações matemáticas, é por isso que o número vermelho é 6 quando deveria ser 10. Se colocarmos as operações sensíveis (como adição e subtração) entre parênteses, então o determinada fórmula inserida novamente fornecerá a mesma saída que você pode ver no número verde na parte inferior que diz 10.

Casos não funciona

Este algoritmo não é perfeito. Tentei apenas implementar os usos mais comuns, para que possa ser melhorado adicionando mais recursos que lidam com outros casos, como intervalos.
Como você pode ver neste exemplo, usei SUM()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 SUM()parâmetros e depois com qualquer outra coisa. Portanto, o :elemento permanece no seu lugar enquanto ao seu redor tudo é substituído, então novas células são substituídas próximas a ele, o que mudará o seu significado. Portanto, a saída estará errada. Portanto, neste caso, você só pode usar esta macro para estudar a fórmula original.

Answer 2

Dado o uso da palavra "trace" em sua pergunta, presumo que você esteja familiarizado com as funções Trace Precendentse Trace Dependentsna seção Auditoria de Fórmula do Excel.

Aqui está uma breve explicação de cada uma das ferramentas de auditoria de fórmulas de dummies.com:

Compreendendo as ferramentas de auditoria de fórmulas do Excel 2010

Answer 3

Hoje usaríamos apenas FORMULATEXT()da maneira bastante óbvia, mas o truque que faltou naquela época, e hoje para aqueles com versões anteriores (como meus sogros em 2007), é usar um comando de macro diferente do Excel 4, GET(), dentro de um Named Faixa.

Uma das MUITAS coisas que poderia fazer era ler uma fórmula como texto, exatamente como FORMULATEXT()faz agora. Depois é só combinar as strings. Um pouco IF()jogado para testar se há algo para obter e se está livre de casa.