LlosLab Software Development BlogNotas de ingeniería para desarrolladores de software de PDF, Delphi y documentos.

Artículo técnico

Precision as displayed en HotXLS: redondeo como Excel

HotXLS Delphi Component

El precision as displayed de Excel redondea cada número guardado a los decimales que su formato de número muestra: la sección del formato que coincide con el signo del valor, dos decimales extra por cada %, tres menos por cada coma de escala a miles, redondeando half away from zero. HotXLS aplica la misma regla en ambos engines Delphi cuando TXLSXWorkbook.FullPrecision o TXLSWorkbook.UseFullPrecision es False. Suena a una sola línea hasta que un cliente reporta que los totales de sus facturas exportadas discrepan de Excel por un centavo, o que una columna de duraciones en [ss].00 colapsó a cero. Ambas cosas pasaron, y ambas se rastrean hasta equivocarse en una de esas reglas. Desde v2.384.57 los dos engines comparten una sola implementación cuyos valores esperados se midieron en Excel 16 con Workbook.PrecisionAsDisplayed activado

¿Qué cambia realmente precision as displayed en un workbook?

Precision as displayed es una sola bandera a nivel de workbook que le dice al motor de cálculo que guarde los números como se ven, no como se calcularon. En la UI de Excel está bajo Archivo, Opciones, Avanzadas, "Al calcular este libro", como "Establecer precisión como se muestra". En disco es un bit. Un archivo BIFF8 la lleva en el record CalcPrecision ($000E, [MS-XLS] §2.4.35), cuyo campo fFullPrec es 1 para la precisión completa normal y 0 cuando la opción está activa. Un paquete XLSX la lleva como el atributo fullPrecision del elemento calcPr en workbook.xml, definido en ECMA-376 Parte 1, donde el valor por defecto es true y fullPrecision="0" enciende el redondeo

La bandera no es una preferencia de visualización. Cuando marca la casilla, Excel advierte que los datos perderán precisión de forma permanente, y lo dice en serio: los valores se reescriben a su precisión mostrada, y los dígitos que se cortaron se pierden. Desmarcar la casilla después no recupera los dígitos viejos. Un 0.1234 mostrado como 12.3% queda en 0.123 para siempre

HotXLS lee y escribe la bandera en ambos formatos y la expone en ambos engines:

  • TXLSXWorkbook.FullPrecision: Boolean en el engine XLSX, cargada de y guardada en calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean en el engine clásico (también en IXLSWorkbook), cargada de y guardada en el record CalcPrecision
  • Ambas quedan en True por defecto, que es el modo seguro y no destructivo, y el valor por defecto de Excel

Dónde aplica HotXLS el redondeo importa. HotXLS redondea en el punto donde calcula un valor: cada resultado de fórmula se redondea a su precisión mostrada antes de guardarse como valor en caché de la celda, durante Recalculate y durante la evaluación bajo demanda. Las constantes que usted asigna por Value se guardan exactamente como se dan. Si su salida tiene que reproducir lo que Excel guarda tras marcar la casilla, redondee esas constantes usted mismo antes de escribirlas, por ejemplo con el helper que se muestra más adelante

¿Cómo decide Excel cuántos decimales conservar?

Excel deriva la cantidad de decimales conservados de la sección específica del formato que muestra el valor, no de la cadena de formato como un todo. Las reglas de abajo se midieron en Excel 16 y son lo que XlsApplyDisplayedPrecision en lxNumFormat implementa para ambos engines de HotXLS

  1. Escoja la sección por signo. Un formato de dos secciones usa la segunda sección para valores negativos. Un formato con tres o más secciones usa la segunda para negativos y la tercera para exactamente cero. Todo lo demás usa la primera sección
  2. Cuente los placeholders decimales. Cada 0, # o ? después del punto decimal en esa sección agrega un decimal conservado
  3. Sume dos por cada signo de porcentaje. 0.0% muestra 0.1234 como 12.3%, así que el valor guardado es la centésima parte de lo que usted ve y conserva tres decimales, no uno
  4. Reste tres por cada coma de escala. Una coma después del último placeholder entero (0,, 0.0,, 0,.0) divide la visualización entre 1000. 0.0, muestra 12345.678 como 12.3, así que Excel conserva un decimal menos tres, que es un conteo negativo: el valor se redondea a las centenas y se guarda como 12300. Una coma entre placeholders enteros, como en #,##0, es agrupación de dígitos corriente y no cambia nada
  5. Deje tranquilas las secciones no numéricas. Las secciones General, de fecha y hora (incluyendo [h], [mm] y [ss] de tiempo transcurrido), científicas, de fracción y de texto, y las secciones sin ningún placeholder de dígito conservan la precisión completa
Diagrama de HotXLS de las reglas de precisión mostrada: escoja la sección del formato por el signo del valor, cuente los placeholders de dígitos después del punto decimal, sume dos decimales por cada signo de porcentaje, reste tres por cada coma de escala a miles de modo que el conteo pueda quedar negativo, salte por completo las secciones General y de fecha y hora, y luego redondee half away from zero
El conteo de dígitos sale de la sección que coincide con el signo, más dos por porcentaje y menos tres por coma de escala, y un conteo negativo redondea a decenas o centenas; las secciones General y de fecha se dejan tranquilas

Medidos contra Excel 16, estos son los valores que ambos engines de HotXLS guardan ahora para un resultado de fórmula en cada formato:

Formato de númeroValor calculadoValor guardadoRegla que aplica
0.0%0.12340.123Un decimal más dos por el signo de porcentaje
02.53Half away from zero, no al par
0-2.5-3Half away from zero también del lado negativo
0.00;(0.0)-1.2345-1.2La sección negativa muestra un decimal
0.00;(0.0)1.23451.23La sección positiva muestra dos decimales
#,##0.01234.56781234.6Coma de agrupación, sin escala
0.0,12345.67812300Un decimal menos tres: redondeo a centenas
0.0%;(0.00%)-0.0125-0.0125La sección negativa conserva dos más dos decimales
0.001.0051.01Tolerancia al error de representación binaria
0;-0;0.00.51No es cero, así que decide la sección positiva

La última fila es una linda trampa. El valor 0.5 se redondea a un número entero, y la sección del cero jamás entra en juego, porque Excel escoge la sección a partir del valor calculado antes de redondear. Una limitación honesta del lado de HotXLS: las secciones se escogen solo por signo, así que un formato cuyas secciones llevan condiciones de corchete custom como [>=1000] se sigue dividiendo por signo. Verifique esos formatos contra Excel si le importan

¿Por qué 1.005 se redondea a 1.01 y no a 1.00?

Excel redondea 1.005 en una celda 0.00 a 1.01 aunque el double más cercano a 1.005 está levemente por debajo del punto medio, y HotXLS lo iguala con una tolerancia de unos pocos ulp. El literal 1.005 no se puede representar en coma flotante binaria. El double IEEE 754 más cercano es 1.00499999999999989341858963598497211933135986328125, y multiplicarlo por 100 da 100.49999999999999. Un Floor(x * 100 + 0.5) / 100 de manual por lo tanto devuelve 1.00, lo cual discrepa del número que el usuario tecleó, de lo que Excel muestra, y de lo que Excel guarda

Delphi agrega su propio giro. System.Round redondea los empates al par, así que Round(2.5) es 2 y Round(3.5) es 4. Eso es redondeo de banquero, un valor por defecto sensato para estadística y la regla equivocada aquí: Excel guarda 3 para 2.5 en una celda 0 y -3 para -2.5. La implementación de HotXLS trabaja sobre el valor absoluto, suma 0.5 más una tolerancia relativa de 2-51 veces el valor escalado (unos pocos ulp en esa magnitud, nunca menos de dos ulps de 1.0), trunca, reescala y restaura el signo. La función siguiente es una ilustración autocontenida de ese principio, no el código de la librería, y maneja los conteos de dígitos negativos para comas de escala de la misma manera:

Diagrama de redondeo de HotXLS: 2.5 se redondea half away from zero a 3 y -2.5 a -3, donde el System.Round de Delphi da las respuestas de banquero 2 y -2, y como el double más cercano a 1.005 queda justo por debajo del punto medio, la tolerancia de unos pocos ulp es lo que convierte un 1.00 basado en Floor en la respuesta de Excel 1.01
Excel redondea los empates lejos del cero y perdona el error de representación binaria con una tolerancia pequeña; ambos detalles son medibles, y saltarse cualquiera guarda 2 para 2.5 o 1.00 para 1.005, a un centavo de Excel
// Boceto de principio: redondear half away from zero a ADigits decimales,
// con una tolerancia de unos pocos ulp para que 1.005 llegue a 1.01.
// ADigits < 0 redondea a decenas, centenas, ... ("0.0," da -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dos ulps de 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // más allá de la precisión double: dejar el valor en paz
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // escalar desbordaría
    Scaled := Abs(AValue) * Scale;
  end
  else
    Scaled := Abs(AValue) / Scale;
  Eps := Scaled * Tolerance;
  if Eps < Tolerance then
    Eps := Tolerance;
  Scaled := Int(Scaled + 0.5 + Eps); // half away from zero, no Round()
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

// RoundAsDisplayed(1.005, 2)      = 1.01   (basado en Floor: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 dígitos)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 dígitos)

La tolerancia es una contrapartida deliberada. Un valor que genuinamente esté dos ulps por debajo de un medio paso también redondea hacia arriba, pero a esa distancia la diferencia es indistinguible de un error de representación, y tratarlo como medio paso es lo que hace que los decimales tecleados se comporten como los usuarios esperan

¿Qué salía mal antes de v2.384.57?

Antes de v2.384.57 el engine XLSX y el engine clásico tenían cada uno su propio código de precision-as-displayed, y cada uno estaba mal de una manera distinta. Si produce workbooks con la opción activa, estos son los síntomas que buscar en archivos generados por builds antiguos

Engine XLSX: solo la primera sección, sin porcentaje, redondeo de banquero

La ruta XLSX antigua pedía el conteo de decimales de la cadena de formato como un todo, que miraba solo la primera sección e ignoraba %, y luego redondeaba con Round. Un 0.1234 en 0.0% se guardaba como 0.1, que es 10% en vez del 12.3% en pantalla. Un 2.5 en 0 se guardaba como 2 en vez de 3. Los valores negativos en un formato como 0.00;(0.0) se redondeaban a los dos decimales de la sección positiva. Desde v2.384.57 el engine XLSX llama a la misma rutina compartida que el engine clásico, que además ganó soporte de coma de escala en esa versión

Engine clásico: TRUE se volvía -1

El engine clásico protegía su redondeo con VarIsNumeric, y VarIsNumeric devuelve True para un Variant varBoolean. Convertir ese Variant con Double(V) da -1, porque un Boolean True al estilo COM se guarda como -1. Una fórmula como =A1>0 en una celda formateada 0.00 por lo tanto salía del recálculo como el número -1. Desde v2.384.57 los resultados Boolean se excluyen antes de cualquier prueba numérica, y un resultado lógico queda siendo lógico en ambos engines

Formatos de tiempo transcurrido leídos como colores (v2.384.9)

El tercer bug estaba en el modelo de formatos de número y no en el redondeo. El parser clasificaba cada token entre corchetes que no era una condición como un color, así que [h], [mm] y [ss] jamás marcaban su sección como fecha/hora. La visualización no se afectaba, porque el formateo corre por una ruta aparte, pero precision as displayed se apoya en esa bandera para saltarse los valores de tiempo. Una duración de cinco segundos es 5/86400 de un día, unos 0.0000579, y un formato como [ss].00 parecía un número ordinario de dos decimales, así que con FullPrecision apagada la duración se redondeaba a 0.00 días. Desde v2.384.9 una corrida entre corchetes de una sola letra h, m o s se parsea como un token de tiempo transcurrido y la sección se trata como fecha/hora. La misma versión arregló la detección de minutos en h:mm, donde los dos puntos entre los tokens solían esconderle la hora al parser

Diagrama de HotXLS de un mal parseo de tiempo transcurrido: cinco segundos guardados como una fracción de día diminuta en una celda formateada con el token ss entre corchetes, que el parser antiguo leía como un color y marcaba como un número plano de dos decimales, así que precision as displayed redondeaba la duración a 0.00 hasta que se parseaba como una sección de tiempo transcurrido
El formateo corría por su propia ruta, así que la celda se veía bien mientras el valor guardado se redondeaba a cero; una sola letra h, m o s entre corchetes es un token de tiempo transcurrido, no un color, y la sección conserva la precisión completa

Activar precision as displayed en HotXLS desde Delphi

Para obtener valores guardados equivalentes a Excel, fije la bandera antes del recálculo que debe honrarla, y luego lea los resultados en caché o guarde. En el engine XLSX, FullPrecision es una bandera simple: cambiarla no invalida resultados que un Recalculate anterior ya guardó, así que fíjela justo después de Create o Open y antes del primer Recalculate. El ejemplo usa fórmulas porque es ahí donde HotXLS aplica el redondeo:

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Totals');
    Sh.Cells[1, 1].Value := 0.1234;
    Sh.Cells[2, 1].Value := 2.5;
    Sh.Cells[3, 1].Value := 12345.678;

    Sh.Cells[1, 2].Formula := '=A1';
    Sh.Cells[1, 2].NumberFormat := '0.0%';   // muestra 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // muestra 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // muestra 12.3 (miles)

    // Debe fijarse antes del primer Recalculate en el engine XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Los resultados en caché ahora coinciden con Excel 16: 0.123, 3 y 12300.
    // Las constantes de la columna A conservan su precisión completa.
    Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
    Assert(Double(Sh.Cells[2, 2].Value) = 3);
    Assert(Double(Sh.Cells[3, 2].Value) = 12300);

    Wb.SaveAs('totals.xlsx'); // escribe <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

El engine clásico se comporta igual, con una comodidad: asignar TXLSWorkbook.UseFullPrecision marca como dirty cada fórmula del grafo de dependencias, así que el próximo Recalculate reevalúa el workbook completo bajo la nueva regla. Cambiar un NumberFormat con la opción activa también marca como dirty las celdas de fórmula afectadas, porque el formato ahora decide el valor guardado. Note que el Recalculate clásico devuelve la cantidad de celdas de fórmula que no pudo evaluar, así que cero significa éxito:

var
  Wb: TXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  try
    Sh := Wb.Sheets.Add;
    Sh.Range['A1', 'A1'].Value := -1.2345;
    Sh.Range['B1', 'B1'].Formula := '=A1';
    Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
    Sh.Range['C1', 'C1'].Formula := '=A1<0';
    Sh.Range['C1', 'C1'].NumberFormat := '0.00';

    Wb.UseFullPrecision := False; // marca todas las fórmulas como dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: la sección negativa "(0.0)" muestra un decimal
    // C1 queda Boolean True (builds antes de v2.384.57 guardaban -1)
    Wb.SaveAs('report.xls'); // record CalcPrecision con fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Ambos engines también honran la bandera que viene con un archivo. Abra un workbook guardado con la opción activa y FullPrecision o UseFullPrecision ya es False, así que un Recalculate después de cargar redondea exactamente como lo haría Excel. Si solo necesita leer los números que Excel ya guardó, puede saltarse el recálculo por completo, como se describe en leer valores de fórmula en caché sin recálculo. Para cómo interactúan los números seriales y los formatos de fecha con el modelo de formato que mueve el chequeo de fecha/hora, vea números seriales de fecha de Excel, el sistema 1904 y numFmt en Delphi

¿Cuándo conviene activar precision as displayed y cuándo no?

Active precision as displayed solo cuando los números guardados del workbook tienen que igualar a sus números mostrados, y usted acepta perder los dígitos extra para siempre. El caso legítimo clásico es un calendario financiero donde columnas de montos redondeados tienen que sumar el total redondeado en pantalla, sin fracciones ocultas de centavo que produzcan un total desviado en el último dígito. Igualar un workbook existente de un cliente que ya tiene la opción fijada es la otra buena razón, y HotXLS conserva la bandera en el round-trip para que no los devuelva a precisión completa en silencio

Evítelo en la mayoría de las demás situaciones:

  • Datos de ingeniería y científicos. Redondear una medición porque alguien escogió un formato de dos decimales para un reporte destruye información que ningún cambio de formato posterior puede restaurar
  • Porcentajes con formatos gruesos. Un formato 0% conserva solo dos decimales de la razón guardada, así que 0.1234 se vuelve 0.12, y cada fórmula aguas abajo que lea la celda trabaja con 0.12
  • Visualizaciones escaladas. Un formato 0, o 0.0, usado para mostrar miles redondea el valor guardado a los miles o a las centenas, cosa que rara vez es lo que la persona que escogió el formato pretendía
  • Plantillas compartidas. La bandera es de todo el workbook. Cualquiera que después agregue una hoja hereda el comportamiento, normalmente sin saber que está activa

Si lo que de verdad quiere son resultados redondeados en unas pocas celdas específicas, escriba ROUND en esas fórmulas en cambio. ROUND es explícito, local a la celda, visible para cualquiera que lea la fórmula, y lo evalúa el motor de fórmulas de HotXLS como cualquier otra función, sin efectos secundarios de workbook completo

Referencia rápida de precision as displayed

  • Bandera en archivo: CalcPrecision $000E con fFullPrec = 0 en BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" en XLSX (ECMA-376 Parte 1)
  • Interruptores de HotXLS: TXLSXWorkbook.FullPrecision := False y TXLSWorkbook.UseFullPrecision := False, ambos True por defecto
  • Sección: escogida por el signo del valor calculado; tercera sección solo para exactamente cero
  • Dígitos: placeholders decimales, más dos por cada %, menos tres por cada coma de escala; el conteo puede ser negativo
  • Redondeo: half away from zero con tolerancia de unos pocos ulp, así que 2.5 da 3, -2.5 da -3 y 1.005 da 1.01
  • Saltados: General, fecha/hora y tiempo transcurrido, científico, fracción, texto, valores Boolean y de error
  • Alcance en HotXLS: resultados de fórmula al calcularse; las constantes se guardan como se asignan
  • Engine XLSX: fije FullPrecision antes del primer Recalculate; el setter clásico vuelve a marcar todas las fórmulas dirty por su cuenta
  • Versiones: igualado a Excel 16 en ambos engines desde v2.384.57; formatos de tiempo transcurrido protegidos desde v2.384.9

HotXLS lee, escribe y calcula workbooks XLS y XLSX de forma nativa desde Delphi y C++Builder, incluidas las opciones de cálculo de workbook cubiertas aquí. Detalles, ediciones y la descarga de prueba están en la página del componente de hojas de cálculo HotXLS para Delphi