Substituição de fórmulas do excel

Question

é possível fazer a substituição de fórmulas facilmente no excel?

Eu tenho uma planilha semelhante ao exemplo abaixo (embora mais complexa, daí a necessidade de alguma função no excel, se houver uma)

O que eu quero fazer é substituir a função total de vendas pelos valores de entrada reais, em vez de fórmulas.

F6+I6+L6+O6 = F9 + L9
F4+F5+I4+I5+L4+L5+O4+O5 = F6+I6+L6+O6

that is 
F9 + L9 becomes F4+F5+I4+I5+L4+L5+O4+O5

Espero que você entenda o que eu estou procurando

    

Answer 1

Eu escrevi um código rápido que deve fazer o que você quiser.

Basicamente, ele verifica se uma célula contém uma fórmula e, se o fizer, substitui todas as instâncias nas quais essa célula é referenciada com essa fórmula.

Uma vez que ele tenha passado pela planilha, se encontrar algum substituto, ele faz um loop novamente (não tendo certeza de que precisa disso, mas foi mais fácil de colocar). Eu também não tenho ideia de quão rápido isso será executado em planilhas complexas.

Note que ele trata $ A $ 1, $ A1, A $ 1 e A1 como o mesmo que não tem de determinar se deve haver algumas células congeladas na referência.

Sub replace_formulas()
Dim cell_count As Long, flag As Boolean

Do

flag = False

For Each c In ActiveSheet.UsedRange
    If c.HasFormula Then

        'count number of replacements
        cell_count = Application.WorksheetFunction.CountIf(Cells, c.Address) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "")) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "", 1, 1)) + _
            Application.WorksheetFunction.CountIf(Cells, "$" & Replace(c.Address, "$", ""))

        'If there is at least one replacement loop through all the cells after this one
        If cell_count > 0 Then flag = True

        'Replace cell references with and without $ ($A$1,$A1,A$1,A1)
        Cells.Replace What:=c.Address, Replacement:="c.formula", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", "", 1, 1), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="$" & Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End If
Next

Loop While flag = True

End Sub

    

Answer 2

Você pode ter que fazer isso manualmente, mas há um recurso para ajudá-lo: Trace precedents (na guia Fórmulas no Excel 2013).

Isso adicionará setas mostrando onde a fórmula que você selecionou se origina, portanto, se você selecionou o total de vendas na coluna J em seu exemplo e clicou precedentes de rastreio, ele desenharia setas de F9 e L9 para ela.