Excel esconde un pequeño depurador a simple vista. Seleccione una celda, abra Fórmulas y haga clic en Evaluar fórmula, y un cuadro de diálogo mostrará la fórmula con una subexpresión subrayada. Presione Evaluar y esa subexpresión se colapsará en su valor, luego se subrayará la siguiente y verá cómo una expresión larga se reduce a un solo número, una reducción a la vez. Es la forma más rápida de descubrir qué rama de un IF anidado se ejecutó realmente, o qué referencia alimentó un total incorrecto. HotXLS reproduce ese comportamiento exacto a través de TXLSFormulaTracer, de modo que un programa en Delphi o C++Builder puede renderizar la misma lista de pasos para auditar un libro de trabajo, depurar una fórmula generada o enseñar a alguien por qué un resultado salió de esa manera. 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 penetra en el motor de cálculo. Tokeniza la fórmula y la analiza con un analizador 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 nuevamente en la expresión circundante como un literal, y el motor le pide al calculador real que vuelva a calcular la expresión ahora más simple. Dado 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 coincide exactamente con lo que produciría un recálculo completo de la celda. El analizador es no invasivo por diseño, que es lo que le permite ejecutarse en cualquier hoja de cálculo sin alterar su estado
El analizador sigue una escala de precedencia de operadores, con un nivel recursivo por cada banda de precedencia. De menor a mayor vinculación, las bandas son: nivel 0 comparación (=, <>, <, >, <=, >=), nivel 1 concatenación de cadenas (&), nivel 2 suma y resta, nivel 3 multiplicación y división, nivel 4 exponenciación y, por último, más y menos unarios por debajo de eso. Cada nivel analiza el nivel por encima en busca de sus operandos, por lo que una banda superior se une con más fuerza. Esta es la misma precedencia que aplica Excel, que es la razón por la que A1*B1+A2*B1 reduce los dos productos antes de la suma: la multiplicación se encuentra en el nivel 3, la suma en el nivel 2, de modo que las multiplicaciones están más profundas en el árbol y se reducen primero
Rastrear una fórmula y recorrer los pasos
El uso refleja la demostración incluida en Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Cree una hoja de cálculo (o abra un libro de trabajo existente), construya un rastreador sobre la hoja, llame a Trace e itere sobre la matriz devuelta. Cada TXLSFormulaStep expone Depth para la sangría, 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; // unidades A1
Sheet.Cells[1, 2].Value := 25; // precio unitario B1
Sheet.Cells[1, 3].Value := 0.08; // tasa de impuesto C1
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('resultado = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Las referencias a celdas se resuelven primero y aparecen como sus propios pasos, luego los productos se reducen, después el factor impositivo entre paréntesis y la multiplicación final lo cierra. El campo Depth le permite aplicar una sangría para que las reducciones más internas se ubiquen visiblemente en la mayor profundidad, exactamente como Excel subraya el término más interno antes que cualquier término externo
La trampa del literal sin configuración regional
El detalle más peligroso de todo este esquema es invisible en un equipo en inglés y falla estrepitosamente en uno en alemán. Cuando un número calculado se sustituye nuevamente en el texto de la fórmula, tiene que escribirse como una cadena y luego el motor de cálculo debe volver a analizarlo, el cual trata el . 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 impositivo, la coma se leería como un separador de argumentos y el recálculo de A1*B1*1,08 se analizaría de forma incorrecta o fallaría directamente
El rastreador evita esto al dar formato a cada literal numérico a través de un TFormatSettings privado que fija en la construcción, con DecimalSeparator forzado a . y ThousandSeparator ajustado a #0 para que nunca se emita ningún carácter de agrupación. Luego, FloatToStr produce un literal que el motor siempre puede volver a leer, independientemente de la configuración regional del operador
// Conceptualmente lo que el rastreador fija una vez, en la construcción
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// cada número reducido se escribe con: 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: el ciclo de conversión de un valor a través del texto de una fórmula es un problema de serialización, y la serialización debe ser independiente de la configuración regional
Los booleanos se reducen a 1 y 0
Una decisión de sustitución relacionada tiene que ver con los valores lógicos. Cuando una subexpresión se evalúa como un booleano, el rastreador lo reescribe 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 cayera dentro de TRUE*B1, el recálculo dependería de que el motor acepte una palabra clave booleana simple en una multiplicación. Sustituir por 1 esquiva la cuestión por completo, porque 1*B1 es inequívoco en cualquier posición aritmética. Esto 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 de manera atómica
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 a función se evalúa en su conjunto, desde su texto original, en un solo paso. El motivo 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 la 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 diferida que hace que esas protecciones funcionen
// IF es un paso atómico; solo se evalúa la rama seleccionada
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 es verdadero, por lo que el paso registra A1*B1 como el resultado elegido;
// A2*B1 nunca se calcula, exactamente como lo haría Excel.El costo es que no se ve 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 rastro más engañoso que tratar la llamada como la única unidad de evaluación que realmente es
Separadores de argumentos y rangos intactos
Otras dos normalizaciones mantienen el recálculo sincero. El compilador del motor de cálculo espera ; como el separador de argumentos de la función, por lo que cuando el rastreador reconstruye una llamada a una función a partir de su árbol analizado, une los argumentos con ;, incluso si el usuario escribió originalmente ,. Una fórmula escrita como SUM(A1,A2,A3) se recalcula como SUM(A1;A2;A3), lo cual el motor acepta. La sustitución de valores es lo que hace necesaria esta reconstrucción, y acertar con el separador es lo que hace que la reconstrucción se pueda analizar
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 contenedora se reduzca en su conjunto. En SUM(A1:A3)*B1 el rango se mantiene completo, SUM(A1:A3) se reduce a un número en un paso atómico, y solo entonces se ejecuta la multiplicación externa. Este es el mismo límite que traza Excel entre un operando de rango y el escalar que finalmente aporta
// El rango A1:A3 nunca se divide; SUM es una reducción atómica,
// luego el producto con B1 se reduce sobre él.
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 reflejo del comando Evaluar fórmula de Excel en lugar de una aproximación. Las reducciones ocurren en el orden en que Excel las realiza, los literales sustituidos sobreviven a cualquier configuración regional, los booleanos se fuerzan de la manera en que Excel lo hace, y las funciones de evaluación diferida permanecen así. Si desea llevar el motor más allá con sus propias funciones, el artículo sobre el motor de fórmulas y funciones personalizadas muestra cómo registrarlas, y para trabajo numérico más pesado el artículo sobre funciones de distribución estadística en Delphi cubre la biblioteca integrada que el rastreador evalúa. Todo esto se incluye como parte del componente de hoja de cálculo HotXLS para Delphi y C++Builder, junto con las API de lectura, escritura, formato y cálculo cubiertas en otras partes de este blog