Excel esconde un pequeño depurador a simple vista. Seleccione una celda, abra Fórmulas y haga clic en Evaluar fórmula, y un diálogo le mostrará la fórmula con una subexpresión subrayada. Presione Evaluar y esa subexpresión colapsará en su valor, luego se subrayará la siguiente, y verá una expresión larga encogerse hasta convertirse en un solo número a través de una reducción a la vez. Es la forma más rápida de encontrar qué rama de un IF anidado se activó realmente, o qué referencia alimentó un total incorrecto. HotXLS reproduce ese comportamiento exacto a través de TXLSFormulaTracer, para que un programa Delphi o C++Builder pueda renderizar la misma lista de pasos para auditar un libro de trabajo, depurar una fórmula generada o enseñarle a alguien por qué un resultado salió como salió. Cada paso registrado incluye el texto de la subexpresión y el valor al que se reduce
Cómo recorre la expresión el motor de reducción
El rastreador no alcanza el interior del motor de cálculo. Tokeniza la fórmula y la analiza con un analizador sintáctico de descenso recursivo, luego reduce el árbol primero en profundidad, comenzando por la subexpresión evaluable más interna. Cuando un nodo se reduce a un valor, ese valor se sustituye en la expresión circundante como un literal, y el motor le pide a la calculadora real que vuelva a calcular la expresión ahora más simple. Debido a que cada paso se evalúa a través del método público Calculate de la hoja de cálculo en lugar de un atajo privado, cada paso concuerda exactamente con lo que produciría un recálculo completo de la celda. El analizador sintáctico no es invasivo por diseño, que es lo que le permite ejecutarse en cualquier hoja de cálculo sin alterar su estado
El analizador sintáctico sigue una escalera de precedencia de operadores, con un nivel recursivo por banda de precedencia. De la vinculación más baja a la más alta, las bandas son: nivel 0 para la comparación (=, <>, <, >, <=, >=), nivel 1 para la concatenación de cadenas (&), nivel 2 para la suma y la resta, nivel 3 para la multiplicación y la división, nivel 4 para la exponenciación, y por último un signo más y menos unario debajo de eso. Cada nivel analiza el nivel superior para obtener sus operandos, por lo que una banda más alta se une más estrechamente. Esta es la misma precedencia que aplica Excel, por lo que A1*B1+A2*B1 reduce los dos productos antes que la suma: la multiplicación se encuentra en el nivel 3 y la suma en el nivel 2, por lo que las multiplicaciones están más profundas en el árbol y se reducen primero
Rastreo de una fórmula y recorrido por los pasos
El uso refleja la demostración incluida en Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Compile una hoja de cálculo (o abra un libro de trabajo existente), construya un rastreador sobre la hoja, llame a Trace y repase el arreglo devuelto. Cada TXLSFormulaStep expone Depth para la indentación, Source para la subexpresión original, Expression para esa subexpresión con sus operandos ya sustituidos y Value para el resultado del paso
uses
SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Tracer: TXLSFormulaTracer;
Steps: TXLSFormulaStepArray;
Final: Variant;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Order');
Sheet.Cells[1, 1].Value := 10; // A1 units
Sheet.Cells[1, 2].Value := 25; // B1 unit price
Sheet.Cells[1, 3].Value := 0.08; // C1 tax rate
Tracer := TXLSFormulaTracer.Create(Sheet);
try
Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
for I := 0 to High(Steps) do
Writeln(StringOfChar(' ', Steps[I].Depth * 2),
Steps[I].Source, ' -> ', Steps[I].Expression,
' = ', VarToStr(Steps[I].Value));
Writeln('result = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Las referencias de celda se resuelven primero y aparecen como sus propios pasos, luego los productos se reducen, a continuación el factor fiscal entre paréntesis, y la multiplicación final lo cierra. El campo Depth le permite indentar para que las reducciones más internas se asienten visiblemente más profundo, exactamente como Excel subraya el término más interno antes que cualquier término externo
La trampa de los literales sin configuración regional
El detalle más peligroso de todo este esquema es invisible en una computadora en inglés y se rompe ruidosamente en una en alemán. Cuando un número calculado se sustituye nuevamente en el texto de la fórmula, debe escribirse como una cadena y luego el motor de cálculo debe volver a analizarlo, el cual trata a . como el punto decimal. Si la sustitución utilizara la configuración regional del sistema, un TFormatSettings alemán escribiría 1,08 para el factor fiscal, la coma se leería como un separador de argumentos y el recálculo de A1*B1*1,08 se analizaría sintácticamente en la forma incorrecta o fallaría por completo
El rastreador evita esto al formatear cada literal numérico a través de un TFormatSettings privado que fija en el momento de su construcción, con DecimalSeparator forzado a . y ThousandSeparator establecido en #0 para que nunca se emita ningún carácter de agrupación. FloatToStr produce entonces un literal que el motor siempre puede volver a leer, independientemente de la configuración regional del operador
// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)Este es el tipo de error que nunca aparece en las propias pruebas del autor y solo surge cuando un cliente en otra región ejecuta el mismo código, por lo que vale la pena decirlo claramente: enviar y recibir un valor a través del texto de una fórmula es un problema de serialización, y la serialización debe estar libre de configuración regional
Los booleanos se reducen a 1 y 0
Una decisión de sustitución relacionada concierne a los valores lógicos. Cuando una subexpresión se evalúa como booleana, el rastreador la vuelve a escribir como 1 o 0, no como TRUE o FALSE. La razón es que el literal reducido tiene que volver a analizarse limpiamente en cualquier contexto que lo rodee, y la aritmética es el caso exigente. Si una comparación como A1>A2 se redujera al texto TRUE y ese texto aterrizara dentro de TRUE*B1, el recálculo dependería de que el motor acepte una palabra clave booleana simple en una multiplicación. Sustituir 1 elude la pregunta por completo, porque 1*B1 no es ambiguo en ninguna posición aritmética. También coincide con la propia coerción de Excel, donde TRUE se comporta como 1 y FALSE como 0 en el momento en que se espera un número
Las llamadas a funciones se reducen atómicamente
Un motor de pasos ingenuo reduciría los argumentos de una función primero y luego la llamada. Eso es incorrecto para Excel, y el rastreador deliberadamente no lo hace. Una llamada de función se evalúa en su totalidad, a partir de su texto original, en un solo paso. La razón es la semántica de cortocircuito. IF, CHOOSE e IFERROR evalúan solo la rama que seleccionan, y reducir los argumentos primero obligaría al motor a calcular ramas que Excel nunca toca. La víctima clásica es una protección contra división por cero como IF(B1=0,0,A1/B1): si el rastreador redujera A1/B1 antes de evaluar el IF, la protección fallaría y generaría el mismo error que existe para prevenir. Al evaluar toda la llamada de forma atómica, el rastreador preserva la evaluación perezosa que hace que dichas protecciones funcionen
// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.La desventaja es que no puede ver el interior de la llamada a la función como pasos separados, pero ese es el comportamiento correcto. Mostrar reducciones de argumentos que Excel nunca realiza sería un rastreo más engañoso que tratar a la llamada como la única unidad de evaluación que realmente es
Separadores de argumentos y rangos intactos
Dos normalizaciones más mantienen el recálculo honesto. El compilador del motor de cálculo espera a ; como separador de argumentos de función, por lo que cuando el rastreador reconstruye una llamada de función a partir de su árbol analizado, une los argumentos con ;, incluso si el usuario originalmente escribió ,. Una fórmula escrita como SUM(A1,A2,A3) se vuelve a calcular como SUM(A1;A2;A3), que es lo que acepta el motor. La sustitución de valores es lo que hace que esta reconstrucción sea necesaria, y colocar bien el separador es lo que hace que la reconstrucción se analice sintácticamente
Las referencias de rango son el otro caso. Un rango como A1:A3 no es un escalar y no debe dividirse en tres valores separados, porque la función que lo consume espera un argumento de rango. El rastreador mantiene un rango intacto como su texto original y permite que la función adjunta se reduzca en su conjunto. En SUM(A1:A3)*B1 el rango permanece entero, SUM(A1:A3) se reduce a un número en un paso atómico y solo entonces se ejecuta la multiplicación exterior. Este es el mismo límite que traza Excel entre un operando de rango y el escalar que finalmente aporta
// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));En conjunto, estas reglas hacen de la lista de pasos un fiel espejo del comando Evaluar fórmula de Excel en lugar de una aproximación de este. Las reducciones ocurren en el orden en que Excel las realiza, los literales sustituidos sobreviven a cualquier configuración regional, los booleanos ejercen coerción de la forma en que lo hace Excel, y las funciones perezosas se mantienen perezosas. Si desea llevar el motor más allá con sus propias funciones, el artículo sobre motor de fórmulas y funciones personalizadas muestra cómo registrarlas, y para el trabajo numérico más pesado el artículo de funciones de distribución estadística en Delphi cubre la biblioteca integrada contra la cual se evalúa el rastreador. Todo esto se incluye como parte de HotXLS spreadsheet component para Delphi y C++Builder, junto con las API de lectura, escritura, formato y cálculo cubiertas en otras partes de este blog