Precision as displayed en HotXLS: el redondeo de Excel

Precision as displayed de Excel redondea cada número guardado a los decimales que su formato de número muestra: la sección de formato que casa con el signo del valor, dos decimales extra por %, tres menos por cada coma de escala de miles, redondeando el medio lejos de cero. HotXLS aplica la misma regla en sus dos motores Delphi cuando TXLSXWorkbook.FullPrecision o TXLSWorkbook.UseFullPrecision es False. Suena a una línea de código hasta que un cliente reporta que los totales de su factura exportada discrepan de Excel por un céntimo, o que una columna de duraciones en [ss].00 colapsó a cero. Ambas cosas pasaron, y ambas se remontan a equivocarse con una de esas reglas. Desde v2.384.57 los dos motores comparten una única implementación cuyos valores esperados se midieron en Excel 16 con Workbook.PrecisionAsDisplayed activado

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

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

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

HotXLS lee y escribe el flag en ambos formatos y lo expone en ambos motores:

• TXLSXWorkbook.FullPrecision: Boolean en el motor XLSX, cargada de y guardada en calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean en el motor Classic (también en IXLSWorkbook), cargada de y guardada en el registro CalcPrecision
• Ambas valen 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 cacheado de la celda, durante Recalculate y durante la evaluación bajo demanda. Las constantes que asigne por Value se guardan exactamente como se las da. 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 abajo

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

Excel deduce el número de decimales conservados de la sección concreta 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 las que XlsApplyDisplayedPrecision en lxNumFormat implementa para ambos motores de HotXLS

1. Escoja la sección por el signo. Un formato de dos secciones usa la segunda sección para valores negativos. Un formato de tres o más secciones usa la segunda para negativos y la tercera para cero exacto. Todo lo demás usa la primera sección
2. Cuente los placeholders decimales. Cada 0, # o ? tras el punto decimal en esa sección añade un decimal conservado
3. Suma dos por cada signo de porcentaje. 0.0% muestra 0.1234 como 12.3%, así que el valor guardado es una centésima de lo que usted ve y conserva tres decimales, no uno
4. Reste tres por cada coma de escala. Una coma tras el último placeholder entero (0,, 0.0,, 0,.0) divide la visualización por 1000. 0.0, muestra 12345.678 como 12.3, así que Excel conserva un decimal menos tres, que es una cuenta negativa: el valor se redondea a los centenares y se guarda como 12300. Una coma entre placeholders enteros, como en #,##0, es agrupación de dígitos a secas y no cambia nada
5. Deje tranquilas las secciones no numéricas. Las secciones General, de fecha y hora (incluidos los tiempos transcurridos [h], [mm] y [ss]), científicas, de fracción y de texto, y las secciones sin placeholder de dígito alguno conservan la precisión completa

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

Formato de número	Valor calculado	Valor guardado	Regla que aplica
0.0%	0.1234	0.123	Un decimal más dos por el signo de porcentaje
0	2.5	3	Medio lejos de cero, no al par
0	-2.5	-3	Medio lejos de cero también en el lado negativo
0.00;(0.0)	-1.2345	-1.2	La sección negativa muestra un decimal
0.00;(0.0)	1.2345	1.23	La sección positiva muestra dos decimales
#,##0.0	1234.5678	1234.6	Coma de agrupación, sin escala
0.0,	12345.678	12300	Un decimal menos tres: redondeo a centenares
0.0%;(0.00%)	-0.0125	-0.0125	La sección negativa conserva dos más dos decimales
0.00	1.005	1.01	Tolerancia al error de representación binaria
0;-0;0.0	0.5	1	No es cero, así que decide la sección positiva

La última fila es una trampa bonita. El valor 0.5 se redondea a número entero, y la sección cero nunca entra en juego, porque Excel escoge la sección del valor calculado antes de redondear. Una limitación honesta por el lado de HotXLS: las secciones se escogen solo por el signo, así que un formato cuyas secciones lleven condiciones entre corchetes personalizadas como [>=1000] sigue partiéndose por el signo. Contraste tales formatos con 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á ligeramente por debajo del punto medio, y HotXLS lo iguala con una tolerancia de unos pocos ulp. El literal 1.005 no puede representarse en coma flotante binaria. El double IEEE 754 más cercano es 1.00499999999999989341858963598497211933135986328125, y multiplicar por 100 da 100.49999999999999. Un Floor(x * 100 + 0.5) / 100 de manual devuelve por tanto 1.00, que discrepa del número que tecleó el usuario, de lo que muestra Excel y de lo que Excel guarda

Delphi añade su propio giro. System.Round redondea los empates al par, así que Round(2.5) es 2 y Round(3.5) es 4. Ese es el redondeo del 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 a esa magnitud, nunca menos que dos ulp de 1.0), trunca, reescala y restaura el signo. La siguiente función es una ilustración autocontenida de ese principio, no el código de la biblioteca, y maneja las cuentas de dígitos negativas de las comas de escala igual:

// Boceto de principio: redondea el medio lejos de cero a ADigits decimales,
// con una tolerancia de unos pocos ulp para que 1.005 llegue a 1.01.
// ADigits < 0 redondea a decenas, centenares, ... ("0.0," da -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dos ulp 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: deje el valor tranquilo
  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); // medio lejos de cero, 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 compensación deliberada. Un valor genuinamente dos ulp por debajo de un paso de medio también redondea hacia arriba, pero a esa distancia la diferencia es indistinguible de un error de representación, y tratarlo como paso de medio es lo que hace que los decimales tecleados se comporten como los usuarios esperan

¿Qué fallaba antes de v2.384.57?

Antes de v2.384.57 el motor XLSX y el motor Classic tenían cada uno su propio código de precision-as-displayed, y cada uno fallaba de una manera distinta. Si produce libros con la opción activa, estos son los síntomas que buscar en archivos generados por compilaciones antiguas

Motor XLSX: solo primera sección, sin porcentaje, redondeo del banquero

El camino XLSX antiguo pedía la cuenta 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 un 10% en lugar del 12.3% en pantalla. Un 2.5 en 0 se guardaba como 2 en lugar 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 motor XLSX llama a la misma rutina compartida que el motor Classic, que además ganó soporte de coma de escala en esa versión

Motor Classic: TRUE se volvía -1

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

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

El tercer bug estaba en el modelo de formato de número más que en el redondeo. El parser clasificaba todo token entre corchetes que no fuera una condición como color, así que [h], [mm] y [ss] nunca marcaban su sección como fecha/hora. La visualización no se afectaba, porque el formateo corre por un camino aparte, pero precision as displayed se apoya en ese flag 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 secuencia entre corchetes de una sola letra h, m o s se parsea como 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 escondían la hora al parser

Activar precision as displayed en HotXLS desde Delphi

Para obtener valores guardados equivalentes a Excel, fije el flag antes del recálculo que debe honrarlo, y después lea los resultados cacheados o guarde. En el motor XLSX, FullPrecision es un flag a secas: cambiarlo no invalida los resultados que un Recalculate anterior ya guardó, así que fíjelo justo después de Create o Open y antes del primer Recalculate. El ejemplo usa fórmulas porque es 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 motor XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Los resultados cacheados ya 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 motor Classic se comporta igual, con una comodidad: asignar TXLSWorkbook.UseFullPrecision marca como sucias todas las fórmulas del grafo de dependencias, así que el próximo Recalculate reevalúa el libro entero bajo la regla nueva. Cambiar un NumberFormat con la opción activa también marca como sucias las celdas de fórmula afectadas, porque el formato ahora decide el valor guardado. Tenga en cuenta que el Recalculate Classic devuelve el número 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 sucias
    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 sigue siendo Boolean True (las compilaciones antes de v2.384.57 guardaban -1)
    Wb.SaveAs('report.xls'); // registro CalcPrecision con fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Ambos motores también honran el flag que llega con un archivo. Abra un libro guardado con la opción activa y FullPrecision o UseFullPrecision ya es False, así que un Recalculate tras 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 cacheados sin recálculo. Para cómo interactúan los números de serie y los formatos de fecha con el modelo de formato que gobierna la comprobación de fecha/hora, vea números de serie 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 libro deban igualar a los números mostrados, y usted acepte perder los dígitos extra para siempre. El caso legítimo clásico es un calendario financiero donde columnas de importes redondeados deben sumar el total redondeado en pantalla, sin fracciones ocultas de céntimo que produzcan un total desviado en el último dígito. Igualar un libro existente de un cliente que ya tiene la opción activada es la otra buena razón, y HotXLS conserva el flag en la ida y vuelta para que no los devuelva a la precisión completa en silencio

Evítelo en casi todas 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 informe destruye información que ningún cambio de formato posterior puede restaurar
• Porcentajes con formatos gruesos. Un formato 0% conserva solo dos decimales del ratio guardado, así que 0.1234 se vuelve 0.12, y toda 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 centenares, lo cual rara vez es lo que pretendía quien escogió el formato
• Plantillas compartidas. El flag es de todo el libro. Quien añada después una hoja hereda el comportamiento, normalmente sin saber que está activo

Si lo que usted quiere de verdad son resultados redondeados en unas pocas celdas concretas, escriba ROUND en esas fórmulas en su lugar. ROUND es explícito, local a la celda, visible para quien lea la fórmula, y lo evalúa el motor de fórmulas de HotXLS como cualquier otra función, sin efectos secundarios a nivel de libro

Referencia rápida de precision as displayed

• Flag del 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 cero exacto
• Dígitos: placeholders decimales, más dos por %, menos tres por coma de escala; la cuenta puede ser negativa
• Redondeo: medio lejos de cero con tolerancia de unos pocos ulp, así que 2.5 da 3, -2.5 da -3 y 1.005 da 1.01
• Se saltan: General, fecha/hora y tiempo transcurrido, científicos, fracciones, texto, valores booleanos y de error
• Alcance en HotXLS: los resultados de fórmula tal como se calculan; las constantes se guardan como se asignan
• Motor XLSX: fije FullPrecision antes del primer Recalculate; el setter Classic vuelve a ensuciar todas las fórmulas él mismo
• Versiones: igualado a Excel 16 en ambos motores desde v2.384.57; formatos de tiempo transcurrido protegidos desde v2.384.9

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