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. Pulse Evaluar y esa subexpresión colapsará en su valor, luego se subrayará la siguiente, y verá cómo una larga expresión se reduce a un solo número reducción a reducción. Es la forma más rápida de averiguar qué rama de un IF anidado se ejecutó realmente, o qué referencia alimentó un total erróneo. 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 la forma en que lo hizo. Cada paso registrado conlleva el texto de la subexpresión y el valor al que se reduce
Cómo el motor de reducción recorre la expresión
El rastreador no se adentra en el motor de cálculo. Convierte la fórmula en tokens y la analiza con un analizador sintáctico de descenso recursivo, para luego reducir el árbol primero en profundidad, empezando por la subexpresión evaluable más interna. Cuando un nodo se reduce a un valor, ese valor se vuelve a sustituir en la expresión circundante como un literal, y el motor pide a la calculadora 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 mediante 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 contra cualquier hoja de cálculo sin perturbar su estado
El analizador sintáctico sigue una escalera de precedencia de operadores, con un nivel recursivo por cada banda de precedencia. Desde la unión más baja a la más alta 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 finalmente más y menos unario por debajo de eso. Cada nivel analiza el nivel por encima de él en busca de sus operandos, de modo que una banda más alta 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 asienta en el nivel 3, la suma en el nivel 2, por lo 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 demo incluida en Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construya una hoja de cálculo (o abra un libro de trabajo existente), construya un rastreador sobre la hoja, llame a Trace e itere el array 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 se reducen los productos, después el factor de impuesto entre paréntesis, y la multiplicación final lo cierra. El campo Depth permite sangrar de forma que las reducciones más internas se sitúen visiblemente más profundas, exactamente igual que Excel subraya el término más interno antes que cualquier término externo
La trampa de los literales sin localización
El detalle más peligroso de todo este esquema es invisible en una máquina inglesa y se rompe estrepitosamente en una alemana. Cuando un número calculado se vuelve a sustituir en el texto de la fórmula, tiene que escribirse como una cadena y luego ser analizado de nuevo por el motor de cálculo, que trata . 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 de impuesto, la coma se leería como un separador de argumentos y el recálculo de A1*B1*1,08 se analizaría con la forma equivocada o fallaría directamente
El rastreador evita esto formateando cada literal numérico a través de un TFormatSettings privado que fija en la 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 aflora cuando un cliente en otra configuración regional ejecuta el mismo código, por lo que vale la pena afirmarlo claramente: la conversión de un valor a través de texto de fórmula y viceversa es un problema de serialización, y la serialización debe estar libre de configuraciones regionales
Los booleanos se reducen a 1 y 0
Una decisión de sustitución relacionada afecta a los valores lógicos. Cuando una subexpresión se evalúa a un booleano, 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 le 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 aceptara una palabra clave booleana desnuda en una multiplicación. Sustituir por 1 elude la cuestión por completo, porque 1*B1 es inequívoco en cualquier posición aritmética. Además concuerda 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 primero los argumentos de una función 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. 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 de 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 provocaría el mismo error que existe para evitar. Al evaluar la llamada completa atómicamente, el rastreador preserva la evaluación diferida que hace que esas 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 contrapartida 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
Dos normalizaciones adicionales mantienen la honestidad de la recomputación. El compilador del motor de cálculo espera ; como separador de argumentos de función, por lo que cuando el rastreador reconstruye una llamada a función a partir de su árbol analizado, une los argumentos con ;, incluso si el usuario tecleó 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 analice sintácticamente
Las referencias a rangos 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 deja que la función envolvente se reduzca en su totalidad. 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
// 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));Tomadas en conjunto, estas reglas hacen que la lista de pasos sea un fiel reflejo del comando Evaluar fórmula de Excel en lugar de una aproximación. Las reducciones ocurren en el orden en que las realiza Excel, los literales sustituidos sobreviven a cualquier configuración regional, los booleanos se coaccionan de la forma en que lo hace Excel y las funciones diferidas se mantienen diferidas. Si desea llevar el motor más lejos 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 sobre funciones de distribución estadística en Delphi cubre la biblioteca integrada contra la que evalúa el rastreador. Todo ello se distribuye como parte del HotXLS spreadsheet component para Delphi y C++Builder, junto a las API de lectura, escritura, formato y cálculo cubiertas en otras partes de este blog