El problema

Question

Tengo una hoja de cálculo de Excel muy complicada (fórmulas que acceden a celdas con otras fórmulas en varias hojas de trabajo) que al final calcula una sola celda (salida) con entradas de varias otras celdas (parámetros). ¿Existe una forma automatizada (una macro de Excel o una herramienta independiente) que pueda comenzar en la celda de salida y desenrollar recursivamente los cálculos hasta que exprese la fórmula en términos de celdas de parámetros directamente?

Aclaración

En los comentarios hubo una sugerencia de la herramienta Evaluar fórmula. Si bien no pude encontrar cómo activarlo en Excel 2008 para Mac, desdesu descripciónParece que permite al usuario avanzar paso a paso en la evaluación de una celda. Eso no es lo que necesito. Lo que necesito es una forma de convertir la fórmula en una celda determinada que puede hacer referencia a otras celdas que contienen fórmulas, en una fórmula equivalente expresada en términos de celdas finales (aquellas que contienen valores, pero no fórmulas).

He aquí un ejemplo sencillo. Dejar

• A1contener= B1 + C1
• B1contener= B2 * B2
• C1contener= C2 * C2
• B2contener1
• C2contener2

Evaluar fórmula me permitiría avanzar paso a paso en el cálculo de A1para llegar al valor final de 5. Lo que necesito es una herramienta que pueda desarrollar A1la fórmula = B2 * B2 + C2 * C2sin evaluarla realmente.

Answer 1

El problema

No puede hacer esto con Evaluar fórmula porque ese no es el propósito de la función. Por eso se llamaevaluar, es para evaluar las fórmulas. Lo que quieres es algún tipo de desembalaje. Esta es una necesidad un poco especial, por lo que no se implementa como una herramienta en Excel, pero existen soluciones si crea algunas funciones/macros de Visual Basic.

Cree un módulo de código VBA (macro) como puede ver eneste tutorial.

1. Presione Alt+F11
2. Haga clic para Moduleentrar Insert.
3. Pegar código.

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

Luego ingresa lo siguiente en una celda:=CellFormula(A1)

Esto le dirá la fórmula de la celda. El único problema con este código es que sólo funciona para un nivel. Si también desea descomprimir las fórmulas de las celdas contenidas, entonces necesita un código más complejo con recursividad.

La solución

Fue un largo viaje pero creé una macro VBA para ti que implementa esta función. No digo que este código funcione para todas las fórmulas, pero funcionará en la mayoría o en algunas de ellas. Además, no digo que este código genere fórmulas equivalentes al código ingresado originalmente o que dé el mismo resultado que el original.

Código fuente

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, simplemente cree una macro (como la describí al principio de la respuesta) y copie y pegue el código. Después de esto, puedes usarlo con =CellFormula(A1)cualquier A1tipo de celda de 1x1.

Casos que funciona

Creé algunos ejemplos para que puedas verlo en acción. En este caso, demuestro el uso con cadenas. Puedes ver que funciona perfectamente. El único pequeño error es que, de alguna manera, el algoritmo cambia el punto y coma a comas. Después de reemplazarlos (como hice yo en este ejemplo), obtendrá el resultado correcto.

Aquí puedes ver cómo funciona con números. Ahora, nos enfrentamos al primer problema: al algoritmo no le importa la secuencia de operaciones matemáticas, es por eso que el número rojo es 6 cuando debería ser 10. Si ponemos las operaciones sensibles (como suma y resta) entre paréntesis, entonces el La fórmula dada ingresada nuevamente dará el mismo resultado que puede ver en el número verde en la parte inferior que dice 10.

Casos que no funciona

Este algoritmo no es perfecto. Solo intenté implementar los usos más comunes, por lo que se puede mejorar agregando más funciones que manejen otros casos como rangos.
Como puede ver en este ejemplo, utilicé SUM()un rango como parámetro. Dado que el algoritmo descifra el contenido de las celdas de arriba a abajo, comienza reemplazando los SUM()parámetros y luego con cualquier otra cosa. Por lo tanto, el :permanece en su lugar mientras a su alrededor todo se reemplaza, por lo que se reemplazan nuevas células cercanas a él, que cambiarán el significado del mismo. Por lo tanto, la salida será incorrecta. Entonces, en este caso, solo puedes usar esta macro para estudiar la fórmula original.

Answer 2

Dado el uso que hace de la palabra "rastreo" en su pregunta, supongo que está familiarizado con las funciones Trace Precendentsy Trace Dependentsen la sección Auditoría de fórmulas de Excel.

Aquí hay una breve explicación de cada una de las herramientas de auditoría de fórmulas de dummies.com:

Comprensión de las herramientas de auditoría de fórmulas de Excel 2010

Answer 3

Hoy solo lo usaríamos FORMULATEXT()de una manera bastante obvia, pero el truco se perdió en aquel entonces, y hoy para aquellos con versiones anteriores (como mis suegros con 2007), es usar un comando de macro de Excel 4 diferente, GET()dentro de un Named. Rango.

Una de las MUCHAS cosas que podía hacer era leer una fórmula como texto, exactamente como FORMULATEXT()lo hace ahora. Entonces es cuestión de simplemente combinar cadenas. Un poco IF()arrojado para comprobar si hay algo que obtener y uno está libre de casa.