☰

Substituição de fórmula excel

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Substituição de fórmula excel

é 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 existir)

O que quero fazer é substituir a função total de vendas pelos valores reais de entrada, 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

insira a descrição da imagem aqui

Espero que você entenda o que estou procurando

Responder1

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

Basicamente, verifica se uma célula contém uma fórmula e, se contém, substitui todas as instâncias em que essa célula é referenciada por essa fórmula.

Depois de passar pela planilha, se encontrar alguma substituição, ela fará um loop novamente (não tenho certeza se precisa disso, mas foi mais fácil de colocar). Também não tenho ideia de quão rápido isso será executado em planilhas complexas.

Observe que ele trata $A$1,$A1,A$1 e A1 como iguais, mas não tem como 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

Responder2

Talvez seja necessário fazer isso manualmente, mas há um recurso para ajudá-lo: Rastrear precedentes (na guia Fórmulas do Excel 2013).

Isso adicionará setas mostrando a origem da fórmula que você selecionou, portanto, se você selecionou o total de vendas na coluna J em seu exemplo e clicou em rastrear precedentes, serão desenhadas setas de F9 e L9 para ela.

informação relacionada