LlosLab Software Development BlogNotas técnicas sobre PDF, Delphi e software documental para desenvolvedores

Artigo Técnico

Precisão como exibido: regras de arredondamento do HotXLS

HotXLS Delphi Component

O precision as displayed do Excel arredonda cada número armazenado para as casas decimais que o number format dele mostra: a seção do formato que casa com o sinal do valor, duas casas extras por %, três a menos por vírgula de escala de milhares, arredondando metade para longe do zero. O HotXLS aplica a mesma regra nos seus dois motores Delphi quando TXLSXWorkbook.FullPrecision ou TXLSWorkbook.UseFullPrecision é False. Isso parece coisa de uma linha só até um cliente relatar que os totais da sua fatura exportada divergem do Excel por um centavo, ou que uma coluna de durações em [ss].00 colapsou para zero. Os dois aconteceram, e os dois remontam a errar uma dessas regras. Desde a v2.384.57 os dois motores compartilham uma implementação única cujos valores esperados foram medidos no Excel 16 com Workbook.PrecisionAsDisplayed ligado

O que o precision as displayed realmente muda num workbook?

O precision as displayed é uma única flag a nível de workbook que diz ao motor de cálculo para armazenar os números como eles aparentam, não como foram calculados. Na UI do Excel fica em Arquivo, Opções, Avançado, "Ao calcular esta pasta de trabalho", como "Definir precisão como exibido". No disco é um bit. Um arquivo BIFF8 o carrega no registro CalcPrecision ($000E, [MS-XLS] §2.4.35), cujo campo fFullPrec é 1 para precisão total normal e 0 quando a opção está ligada. Um pacote XLSX o carrega como o atributo fullPrecision do elemento calcPr no workbook.xml, definido no ECMA-376 Parte 1, em que o padrão é true e fullPrecision="0" liga o arredondamento

A flag não é uma preferência de exibição. Quando você marca a caixa, o Excel avisa que os dados perderão precisão permanentemente, e é pra valer: os valores são reescritos para a precisão exibida deles, e os dígitos cortados se foram. Desmarcar a caixa depois não traz os dígitos antigos de volta. Um 0.1234 exibido como 12.3% vira 0.123 para sempre

O HotXLS lê e grava a flag nos dois formatos e a expõe nos dois motores:

  • TXLSXWorkbook.FullPrecision: Boolean no motor XLSX, carregada de e salva em calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean no motor clássico (também em IXLSWorkbook), carregada de e salva no registro CalcPrecision
  • Ambas têm True como padrão, que é o modo seguro, não destrutivo, e o padrão do Excel

Onde o HotXLS aplica o arredondamento importa. O HotXLS arredonda no ponto em que calcula um valor: cada resultado de fórmula é arredondado para a precisão exibida dele antes de ser armazenado como valor em cache da célula, durante o Recalculate e durante a avaliação sob demanda. Constantes que você atribui pelo Value são armazenadas exatamente como dadas. Se a sua saída precisa reproduzir o que o Excel armazena depois que a caixa é marcada, arredonde essas constantes você mesmo antes de gravá-las, por exemplo com o helper mostrado mais adiante

Como o Excel decide quantas casas decimais manter?

O Excel deriva o número de casas mantidas da seção específica do formato que exibe o valor, não da string de formato como um todo. As regras abaixo foram medidas no Excel 16 e são o que a XlsApplyDisplayedPrecision no lxNumFormat implementa para os dois motores do HotXLS

  1. Escolha a seção pelo sinal. Um formato de duas seções usa a segunda seção para valores negativos. Um formato com três ou mais seções usa a segunda para negativos e a terceira para exatamente zero. Todo o resto usa a primeira seção
  2. Conte os placeholders decimais. Cada 0, # ou ? depois do ponto decimal nessa seção adiciona uma casa mantida
  3. Some duas por símbolo de porcentagem. O 0.0% mostra 0.1234 como 12.3%, então o valor armazenado é um centésimo do que você vê e mantém três casas, não uma
  4. Subtraia três por vírgula de escala. Uma vírgula depois do último placeholder inteiro (0,, 0.0,, 0,.0) divide a exibição por 1000. O 0.0, mostra 12345.678 como 12.3, então o Excel mantém uma casa menos três, o que é uma contagem negativa: o valor é arredondado para as centenas e armazenado como 12300. Uma vírgula entre placeholders inteiros, como em #,##0, é agrupamento de dígitos comum e não muda nada
  5. Deixe seções não numéricas em paz. Seções General, de data e hora (incluindo tempo decorrido [h], [mm] e [ss]), científicas, de fração e de texto, e seções sem placeholder de dígito nenhum mantêm precisão total
Diagrama do HotXLS das regras de precisão exibida: escolha a seção do formato pelo sinal do valor, conte os placeholders de dígito depois do ponto decimal, some duas casas por símbolo de porcentagem, subtraia três por vírgula de escala de milhares para que a contagem possa ficar negativa, pule seções General e de data e hora por completo, depois arredonde metade para longe do zero
A contagem de dígitos vem da seção que casa com o sinal, mais dois por porcentagem e menos três por vírgula de escala, e uma contagem negativa arredonda para dezenas ou centenas; seções General e de data ficam em paz

Medido contra o Excel 16, estes são os valores que os dois motores do HotXLS agora armazenam para um resultado de fórmula em cada formato:

Formato de númeroValor calculadoValor armazenadoRegra aplicada
0.0%0.12340.123Uma casa mais duas pelo símbolo de porcentagem
02.53Metade para longe do zero, não para o par
0-2.5-3Metade para longe do zero no lado negativo também
0.00;(0.0)-1.2345-1.2A seção negativa mostra uma casa
0.00;(0.0)1.23451.23A seção positiva mostra duas casas
#,##0.01234.56781234.6Vírgula de agrupamento, sem escala
0.0,12345.67812300Uma casa menos três: arredonda para centenas
0.0%;(0.00%)-0.0125-0.0125A seção negativa mantém duas mais duas casas
0.001.0051.01Tolerância para erro de representação binária
0;-0;0.00.51Não é zero, então a seção positiva decide

A última linha é uma armadilha boa. O valor 0.5 arredonda para um número inteiro, e a seção de zero nunca entra em cena, porque o Excel escolhe a seção pelo valor calculado antes de arredondar. Uma limitação honesta do lado do HotXLS: as seções são escolhidas só pelo sinal, então um formato cujas seções carregam condições entre colchetes customizadas como [>=1000] ainda é dividido pelo sinal. Confira esses formatos contra o Excel se eles importam para você

Por que 1.005 arredonda para 1.01 e não para 1.00?

O Excel arredonda 1.005 numa célula 0.00 para 1.01 embora o double mais próximo de 1.005 fique ligeiramente abaixo do ponto médio, e o HotXLS acompanha isso com uma tolerância de poucos ulps. O literal 1.005 não é representável em ponto flutuante binário. O double IEEE 754 mais próximo é 1.00499999999999989341858963598497211933135986328125, e multiplicar por 100 dá 100.49999999999999. Um Floor(x * 100 + 0.5) / 100 de livro portanto devolve 1.00, o que diverge do número que o usuário digitou, do que o Excel mostra e do que o Excel armazena

O Delphi adiciona o próprio twist. O System.Round arredonda empates para o par, então Round(2.5) é 2 e Round(3.5) é 4. Esse é o banker's rounding, um padrão sensato para estatística e a regra errada aqui: o Excel armazena 3 para 2.5 numa célula 0 e -3 para -2.5. A implementação do HotXLS trabalha no valor absoluto, soma 0.5 mais uma tolerância relativa de 2-51 vezes o valor escalado (poucos ulps nessa magnitude, nunca menos que dois ulps de 1.0), trunca, reescala e restaura o sinal. A função seguinte é uma ilustração autossuficiente desse princípio, não o código da biblioteca, e ela trata contagens de dígitos negativas para vírgulas de escala do mesmo jeito:

Diagrama de arredondamento do HotXLS: 2.5 arredonda metade para longe do zero para 3 e -2.5 para -3, onde o System.Round do Delphi dá as respostas de banker 2 e -2, e como o double mais próximo de 1.005 fica logo abaixo do ponto médio, a tolerância de poucos ulps é o que transforma um 1.00 via Floor na resposta do Excel 1.01
O Excel arredonda empates para longe do zero e perdoa o erro de representação binária com uma tolerância pequena; os dois detalhes são mensuráveis, e pular qualquer um deles armazena 2 para 2.5 ou 1.00 para 1.005, a um centavo do Excel
// Esboço de princípio: arredonda metade para longe do zero a ADigits casas,
// com tolerância de poucos ulps para que 1.005 chegue a 1.01.
// ADigits < 0 arredonda para dezenas, centenas, ... ("0.0," dá -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dois ulps de 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // além da precisão de double: deixe o valor em 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 transbordaria
    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); // metade para longe do zero, não 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   (via 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)

A tolerância é um trade-off deliberado. Um valor genuinamente dois ulps abaixo de um meio passo também arredonda para cima, mas a essa distância a diferença é indistinguível de erro de representação, e tratá-lo como meio passo é o que faz decimais digitados se comportar do jeito que os usuários esperam

O que dava errado antes da v2.384.57?

Antes da v2.384.57 o motor XLSX e o motor clássico tinham cada um o próprio código de precision-as-displayed, e cada um errava de um jeito diferente. Se você produz workbooks com a opção ligada, estes são os sintomas a procurar em arquivos gerados por builds antigas

Motor XLSX: só a primeira seção, sem porcentagem, banker's rounding

O caminho XLSX antigo pedia a contagem de decimais da string de formato como um todo, o que olhava só a primeira seção e ignorava %, depois arredondava com Round. Um 0.1234 em 0.0% era armazenado como 0.1, que é 10% em vez dos 12.3% na tela. Um 2.5 em 0 era armazenado como 2 em vez de 3. Valores negativos num formato como 0.00;(0.0) eram arredondados para as duas casas da seção positiva. Desde a v2.384.57 o motor XLSX chama a mesma rotina compartilhada do motor clássico, que também ganhou suporte a vírgula de escala nesse release

Motor clássico: TRUE virava -1

O motor clássico guardava o arredondamento dele com VarIsNumeric, e VarIsNumeric devolve True para um Variant varBoolean. Converter esse Variant com Double(V) produz -1, porque um Boolean True à moda COM é armazenado como -1. Uma fórmula como =A1>0 numa célula formatada 0.00 portanto saía da recalculação como o número -1. Desde a v2.384.57 resultados Boolean são excluídos antes de qualquer teste numérico, e um resultado lógico continua lógico nos dois motores

Formatos de tempo decorrido lidos como cores (v2.384.9)

O terceiro bug ficava no modelo de number format, não no arredondamento. O parser classificava todo token entre colchetes que não era uma condição como cor, então [h], [mm] e [ss] nunca marcavam a seção deles como data/hora. A exibição não era afetada, porque a formatação roda num caminho separado, mas o precision as displayed depende dessa flag para pular valores de tempo. Uma duração de cinco segundos é 5/86400 de um dia, cerca de 0.0000579, e um formato como [ss].00 parecia um número comum de duas casas, então com FullPrecision desligada a duração era arredondada para 0.00 dias. Desde a v2.384.9 uma sequência entre colchetes de uma única letra h, m ou s é parseada como token de tempo decorrido e a seção é tratada como data/hora. O mesmo release corrigiu a detecção de minutos no h:mm, em que os dois pontos entre os tokens escondiam a hora do parser

Diagrama do HotXLS de um parse errado de tempo decorrido: cinco segundos armazenados como uma fração de dia minúscula numa célula formatada com o token ss entre colchetes, que o parser antigo lia como cor e marcava como um número comum de duas casas, então o precision as displayed arredondava a duração para 0.00 até ela ser parseada como seção de tempo decorrido
A formatação rodava no caminho dela, então a célula parecia certa enquanto o valor armazenado arredondava para zero; uma única letra h, m ou s entre colchetes é um token de tempo decorrido, não uma cor, e a seção mantém precisão total

Ativar precision as displayed no HotXLS a partir do Delphi

Para obter valores armazenados equivalentes aos do Excel, defina a flag antes da recalculação que deve honrá-la, depois leia os resultados em cache ou salve. No motor XLSX, o FullPrecision é uma flag simples: mudá-lo não invalida resultados que um Recalculate anterior já armazenou, então defina-o logo após o Create ou Open e antes do primeiro Recalculate. O exemplo usa fórmulas porque é aí que o HotXLS aplica o arredondamento:

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%';   // mostra 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // mostra 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // mostra 12.3 (milhares)

    // Precisa ser definido antes do primeiro Recalculate no motor XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Resultados em cache agora batem com o Excel 16: 0.123, 3 e 12300.
    // As constantes na coluna A mantêm a precisão total.
    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'); // grava <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

O motor clássico se comporta igual, com uma conveniência: atribuir ao TXLSWorkbook.UseFullPrecision marca toda fórmula do grafo de dependências como dirty, então o próximo Recalculate reavalia o workbook inteiro sob a nova regra. Mudar um NumberFormat com a opção ligada também marca as células de fórmula afetadas como dirty, porque o formato agora decide o valor armazenado. Note que o Recalculate clássico devolve o número de células de fórmula que não conseguiu avaliar, então zero significa sucesso:

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 toda fórmula como dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: a seção negativa "(0.0)" mostra uma casa decimal
    // C1 continua Boolean True (builds antes da v2.384.57 gravavam -1)
    Wb.SaveAs('report.xls'); // registro CalcPrecision com fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Os dois motores também honram a flag que vem com um arquivo. Abra um workbook salvo com a opção ligada e o FullPrecision ou UseFullPrecision já está False, então um Recalculate depois de carregar arredonda exatamente como o Excel faria. Se você só precisa ler os números que o Excel já armazenou, pode pular a recalculação por completo, como descrito em ler valores de fórmula em cache sem recalculação. Para como números de série e formatos de data interagem com o modelo de formato que dirige a checagem de data/hora, veja números de série de data do Excel, o sistema 1904 e numFmt em Delphi

Quando ligar o precision as displayed, e quando não?

Ligue o precision as displayed só quando os números armazenados do workbook precisam igualar os números exibidos, e você aceita perder os dígitos extras para sempre. O caso legítimo clássico é uma planilha financeira em que colunas de valores arredondados precisam somar o total arredondado na tela, sem frações escondidas de centavo produzindo um total que erra por um no último dígito. Reproduzir um workbook existente de um cliente que já tem a opção marcada é o outro bom motivo, e o HotXLS preserva a flag no round-trip para que você não os retorne silenciosamente à precisão total

Evite-o na maioria das outras situações:

  • Dados de engenharia e científicos. Arredondar uma medição porque alguém escolheu um formato de duas casas para um relatório destrói informação que nenhuma mudança de formato posterior restaura
  • Porcentagens com formatos grosseiros. Um formato 0% mantém só duas casas decimais da razão armazenada, então 0.1234 vira 0.12, e toda fórmula a jusante que lê a célula trabalha com 0.12
  • Exibições escaladas. Um formato 0, ou 0.0, usado para mostrar milhares arredonda o valor armazenado para os milhares ou centenas, o que raramente é o que quem escolheu o formato pretendia
  • Templates compartilhados. A flag vale para o workbook inteiro. Qualquer um que depois adicionar uma planilha herda o comportamento, normalmente sem saber que ele está ligado

Se o que você realmente quer são resultados arredondados em algumas células específicas, escreva ROUND nessas fórmulas em vez disso. O ROUND é explícito, local à célula, visível para quem lê a fórmula, e avaliado pelo motor de fórmulas do HotXLS como qualquer outra função, sem efeitos colaterais no workbook inteiro

Referência rápida do precision as displayed

  • Flag no arquivo: CalcPrecision $000E com fFullPrec = 0 em BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" em XLSX (ECMA-376 Parte 1)
  • Interruptores do HotXLS: TXLSXWorkbook.FullPrecision := False e TXLSWorkbook.UseFullPrecision := False, ambos True por padrão
  • Seção: escolhida pelo sinal do valor calculado; terceira seção só para exatamente zero
  • Dígitos: placeholders decimais, mais dois por %, menos três por vírgula de escala; a contagem pode ser negativa
  • Arredondamento: metade para longe do zero com tolerância de poucos ulps, então 2.5 dá 3, -2.5 dá -3 e 1.005 dá 1.01
  • Ignorados: General, data/hora e tempo decorrido, científico, fração, texto, Boolean e valores de erro
  • Escopo no HotXLS: resultados de fórmula à medida que são calculados; constantes são armazenadas como atribuídas
  • Motor XLSX: defina FullPrecision antes do primeiro Recalculate; o setter clássico remarca todas as fórmulas como dirty por conta própria
  • Versões: igualado ao Excel 16 nos dois motores desde a v2.384.57; formatos de tempo decorrido protegidos desde a v2.384.9

O HotXLS lê, grava e calcula workbooks XLS e XLSX nativamente do Delphi e do C++Builder, incluindo as opções de cálculo de workbook cobertas aqui. Detalhes, edições e o download da versão trial estão na página do componente de planilha HotXLS para Delphi