Combinar automaticamente a fórmula de várias células em uma célula

Question

Eu tenho uma planilha do Excel que usa muitas células para calcular o resultado final. Eu vejo que esses resultados intermediários podem ter sido úteis quando a fórmula foi criada. Agora gostaria de me livrar dos resultados intermediários e ter apenas uma única fórmula em uma célula.

Eu sei como fazer isso manualmente, por exemplo como descrito nesta pergunta e resposta . No entanto, gostaria de saber se existe uma forma automática de fazer isso .

Parece que o Excel já sabe de onde os valores estão vindo quando uso a função trace:

Normalmente eu simplesmente escondo linhas para torná-las invisíveis. Neste caso, não posso fazê-lo porque existem valores em outras colunas que são importantes.

    

Answer 1

A resposta de Charl Pretorius foi útil. Depois de corrigir 2 problemas, o Macro funcionou para mim. Eu adicionei comentários onde mudei o script.

Observe que a macro precisa ser executada com a freqüência que você deseja que as substituições sejam feitas. Seria possível realizar todas as substituições, mas talvez a última referência a uma célula permanecesse.

Sub CombineFormula()

    Dim FormulaCell As Range
    Set FormulaCell = ActiveCell

    Dim StrTemp As String
    StrTemp = FormulaCell.Formula

    'Do not replace the $ sign in absolute addresses.
    'It may be used in text, e.g. in a formula like ="US$"&A1
    'Instead, use different adressing modes in the replacement loop
    '! StrTemp = Replace(StrTemp, "$", "")

    Dim RangeRef As Range

    On Error Resume Next

    Dim ReplaceBy As String
    For Each RangeRef In FormulaCell.Precedents.Cells
        'Add in parentheses to maintain correct order of evaluation, e.g. in cases of addition before multiplication
        ReplaceBy = "(" + Replace(RangeRef.Formula, "=", "") + ")"
        'The order of the following is important
        'Replace absolute ranges first, because A$1 is also contained in $A$1
        StrTemp = Replace(StrTemp, RangeRef.Address(True, True), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(True, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, True), ReplaceBy)
    Next

    FormulaCell.Value2 = StrTemp
End Sub

    

Answer 2

Não tenho certeza se você está satisfeito em usar macros em suas planilhas, mas abaixo está uma macro simples para obter o que você deseja. Apenas copie o código para o seu VBA, selecione a célula que você deseja limpar e execute a macro, você pode até mesmo criar uma tecla de atalho para ela se tiver muito o que fazer. Apenas tenha cuidado, se você executar uma macro, todo o seu histórico de desfazer será excluído.

Sub CombineFormula()

Dim FormulaCell As Range
Set FormulaCell = ActiveCell

Dim StrTemp As String
StrTemp = FormulaCell.Formula
StrTemp = Replace(StrTemp, "$", "")


Dim RangeRef As Range

On Error Resume Next

For Each RangeRef In FormulaCell.Precedents.Cells
    StrTemp = Replace(StrTemp, RangeRef.Address(False, False), Replace(RangeRef.Formula, "=", ""))
Next


FormulaCell.Value2 = StrTemp

End Sub

    

Answer 3

Não há nenhuma maneira direta de abordar isso, pelo que sei, mas acho que a função FORMULATEXT pode ser útil para você. a função FORMULATEXT (número de célula) retornará a fórmula como texto que pode ser usado para algumas modificações, como

A3 = FORMULATEXT (A1) + FORMULATEXT (A2) ou algo desse tipo para atingir seu objetivo.