Artículo técnico

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 quejas se remontan a equivocarse con una de esas reglas. Los dos motores de HotXLS 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
Diagrama de HotXLS de las reglas de precisión mostrada: escoja la sección de formato por el signo del valor, cuente los placeholders de dígito tras el punto decimal, añada dos decimales por signo de porcentaje, reste tres por cada coma de escala de miles de modo que la cuenta pueda salir negativa, salte las secciones General y fecha hora por completo, y luego redondee el medio lejos de cero
La cuenta de dígitos sale de la sección que casa con el signo, más dos por cada porcentaje y menos tres por cada coma de escala, y una cuenta negativa redondea a decenas o centenares; las secciones General y de fecha se dejan tranquilas

Medido contra Excel 16, estos son los valores que ambos motores de HotXLS guardan 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.53Medio lejos de cero, no al par
0-2.5-3Medio lejos de cero también en el 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 centenares
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 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 a partir del valor calculado antes de redondear. HotXLS escoge las secciones por el signo, así que para un formato cuyas secciones lleven condiciones entre corchetes personalizadas como [>=1000], compare los valores guardados 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:

Diagrama de redondeo de HotXLS: 2.5 se redondea medio lejos de cero a 3 y -2.5 a -3, donde Delphi System.Round 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 de 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 céntimo de Excel
// 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é trampas de implementación rompen precision as displayed?

Tres trampas explican la mayoría de las discrepancias de precision-as-displayed, y cada una guarda un valor que discrepa de la pantalla. Si implementa la regla por su cuenta, o audita libros generados con la opción activa, estos son los síntomas que buscar

Solo la primera sección, sin porcentaje, redondeo del banquero

El atajo tentador pide la cuenta de decimales de la cadena de formato como un todo, que mira solo la primera sección e ignora %, y luego redondea con Round. Un 0.1234 en 0.0% se guarda entonces como 0.1, que es un 10% en lugar del 12.3% en pantalla. Un 2.5 en 0 se guarda como 2 en lugar de 3. Los valores negativos en un formato como 0.00;(0.0) se redondean a los dos decimales de la sección positiva. HotXLS dirige ambos motores por una única rutina compartida que escoge la sección por el signo, cuenta signos de porcentaje y comas de escala, y redondea el medio lejos de cero

TRUE se convierte en -1

Proteger el redondeo con VarIsNumeric es la segunda trampa, porque 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 saldría entonces del recálculo como el número -1. HotXLS excluye los resultados booleanos antes de cualquier prueba numérica, así que un resultado lógico sigue siendo lógico en ambos motores

Los formatos de tiempo transcurrido se leen como colores

La tercera trampa está en el modelo de formato de número más que en el redondeo. Un parser que clasifica todo token entre corchetes que no sea una condición como color nunca marca las secciones [h], [mm] y [ss] como fecha/hora. La visualización no se afecta, 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 entonces parece un número ordinario de dos decimales, así que con FullPrecision apagada la duración se redondea a 0.00 días. HotXLS parsea una secuencia entre corchetes de una sola letra h, m o s como token de tiempo transcurrido y trata la sección como fecha/hora. La detección de minutos en h:mm necesita el mismo cuidado, porque los dos puntos entre los tokens pueden 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 un parser que trata todo corchete que no es condición como un color marca como número plano de dos decimales, así que precision as displayed redondearía la duración a 0.00 salvo que la sección se parsee como tiempo transcurrido
El formateo corre por su propio camino, así que la celda puede verse bien mientras el valor guardado se redondea 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 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, no el número -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
  • Referencia: valores esperados medidos en Excel 16, compartidos por ambos motores

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