☰

Excel de sustitución de fórmulas

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel de sustitución de fórmulas

¿Es posible sustituir fórmulas fácilmente en Excel?

Tengo una hoja de trabajo similar al ejemplo siguiente (aunque más compleja, de ahí la necesidad de alguna función en Excel, si existe alguna)

Lo que quiero hacer es reemplazar la función de ventas totales con los valores de entrada reales, en lugar 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

ingrese la descripción de la imagen aquí

Espero que entiendas lo que busco

Respuesta1

Escribí un código rápido que debería hacer lo que quieras.

Básicamente, verifica si una celda contiene una fórmula y, si la contiene, reemplaza todas las instancias en las que se hace referencia a esa celda con esa fórmula.

Una vez que ha revisado la hoja, si encuentra algún reemplazo, vuelve a realizar un bucle (en realidad no estoy seguro de que lo necesite, pero fue más fácil de colocar). Tampoco tengo idea de qué tan rápido se ejecutará esto en hojas de cálculo complejas.

Tenga en cuenta que trata $A$1,$A1,A$1 y A1 como lo mismo y no tiene forma de determinar si debería haber algunas celdas congeladas en la referencia.

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

Respuesta2

Es posible que tengas que hacerlo manualmente, pero hay una función que te ayudará: rastrear precedentes (en la pestaña Fórmulas en Excel 2013).

Esto agregará flechas que muestran dónde se origina la fórmula que ha seleccionado, por lo que si seleccionó las ventas totales en la columna J en su ejemplo y hizo clic en rastrear precedentes, se dibujarán flechas desde F9 y L9 hacia ella.

información relacionada