Artigo Técnico

Precisão como exibido: regras de arredondamento do HotXLS

O precision as displayed do Excel arredonda cada número armazenado para as casas decimais que o number format dele mostra: a seção de formato que corresponde ao sinal do valor, duas casas extras por %, três a menos por vírgula de escalonamento de milhar, arredondando metade para longe do zero. O HotXLS aplica a mesma regra nos dois motores Delphi dele quando TXLSXWorkbook.FullPrecision ou TXLSWorkbook.UseFullPrecision é False. Isso soa como uma linha de código até um cliente relatar que os totais da fatura exportada divergem do Excel por um centavo, ou que uma coluna de durações em [ss].00 colapsou para zero. Ambos os casos remontam a errar uma dessas regras. Os dois motores do HotXLS compartilham uma única implementação 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 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 interessante. O valor 0.5 arredonda para um número inteiro, e a seção zero nunca entra em cena, porque o Excel escolhe a seção a partir do valor calculado antes de arredondar. O HotXLS escolhe seções pelo sinal, então para um formato cujas seções carregam condições de colchete personalizadas como [>=1000], compare os valores armazenados com o Excel se eles importarem 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

Quais armadilhas de implementação quebram o precision as displayed?

Três armadilhas explicam a maioria das divergências de precision-as-displayed, e cada uma armazena um valor que discorda da tela. Se você implementa a regra por conta própria, ou audita workbooks gerados com a opção ligada, estes são os sintomas a procurar

Só a primeira seção, sem porcentagem, banker's rounding

O atalho tentador pede a contagem de decimais da string de formato como um todo, o que olha só a primeira seção e ignora %, e então arredonda com Round. Um 0.1234 em 0.0% é então armazenado como 0.1, que é 10% em vez dos 12.3% na tela. Um 2.5 em 0 é armazenado como 2 em vez de 3. Valores negativos em um formato como 0.00;(0.0) são arredondados para as duas casas decimais da seção positiva. O HotXLS roteia os dois motores por uma única rotina compartilhada que escolhe a seção pelo sinal, conta sinais de porcentagem e vírgulas de escalonamento, e arredonda metade para longe do zero

TRUE vira -1

Proteger o arredondamento com VarIsNumeric é a segunda armadilha, porque VarIsNumeric retorna True para um Variant varBoolean. Converter esse Variant com Double(V) produz -1, porque um Boolean True em estilo COM é armazenado como -1. Uma fórmula como =A1>0 em uma célula formatada com 0.00 então sairia do recálculo como o número -1. O HotXLS exclui resultados Boolean antes de qualquer teste numérico, então um resultado lógico continua um resultado lógico nos dois motores

Formatos de tempo decorrido lidos como cores

A terceira armadilha fica no modelo de number format, não no arredondamento. Um parser que classifica todo token entre colchetes que não é uma condição como cor nunca marca as seções [h], [mm] e [ss] como date/time. A exibição não é afetada, porque a formatação roda em um 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 então parece um número ordinário de duas decimais, de modo que com FullPrecision desligado a duração arredonda para 0.00 dias. O HotXLS interpreta uma sequência entre colchetes de uma única letra h, m ou s como um token de tempo decorrido e trata a seção como date/time. A detecção de minuto em h:mm precisa do mesmo cuidado, porque os dois-pontos entre os tokens podem esconder a hora de um parser

Diagrama do HotXLS de um parse errado de tempo decorrido: cinco segundos armazenados como uma fração minúscula de dia em uma célula formatada com o token ss entre colchetes, que um parser que trata todo colchete sem condição como cor marca como um número simples de duas decimais, de modo que o precision as displayed arredondaria a duração para 0.00 a menos que a seção seja interpretada como tempo decorrido
A formatação roda em um caminho próprio, então a célula pode parecer certa enquanto o valor armazenado arredonda 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, não o número -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
  • Referência: valores esperados medidos no Excel 16, compartilhados pelos dois motores

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