LBlogue de Desenvolvimento de Software losLabNotas de engenharia para programadores de PDF, Delphi e software de documentos.

Artigo Técnico

Precision as displayed no HotXLS: arredondar como o Excel

HotXLS Delphi Component

O precision as displayed do Excel arredonda cada número guardado para os decimais que o seu formato numérico mostra: a secção de formato que corresponde ao sinal do valor, dois decimais extra por %, três a menos por vírgula de escala de milhares, arredondamento de metade para longe do zero. O HotXLS aplica a mesma regra nos seus dois motores Delphi quando TXLSXWorkbook.FullPrecision ou TXLSWorkbook.UseFullPrecision é False. Isso soa a uma linha de código até um cliente reportar que os totais da sua fatura exportada divergem do Excel por um cêntimo, ou que uma coluna de durações em [ss].00 colapsou para zero. Ambos aconteceram, e ambos remontam a errar uma dessas regras. Desde a v2.384.57 os dois motores partilham uma única implementação cujos valores esperados foram medidos no Excel 16 com Workbook.PrecisionAsDisplayed ligado

O que é que o precision as displayed realmente muda num livro?

O precision as displayed é uma única flag ao nível do livro que diz ao motor de cálculo para guardar os números como aparecem, não como foram calculados. Na UI do Excel senta-se em Ficheiro, Opções, Avançadas, "Ao calcular este livro", como "Definir precisão como exibido". Em disco é um bit. Um ficheiro BIFF8 transporta-a no registo CalcPrecision ($000E, [MS-XLS] §2.4.35), cujo campo fFullPrec é 1 para a precisão total normal e 0 quando a opção está ligada. Um pacote XLSX transporta-a como o atributo fullPrecision do elemento calcPr em workbook.xml, definido no ECMA-376 Parte 1, em que a predefinição é true e fullPrecision="0" liga o arredondamento

A flag não é uma preferência de exibição. Quando marca a caixa, o Excel avisa que os dados vão perder precisão permanentemente, e está a falar a sério: os valores são reescritos para a sua precisão exibida, e os dígitos que foram cortados desaparecem. Desmarcar a caixa mais tarde não traz os dígitos antigos de volta. Um 0.1234 mostrado como 12.3% torna-se 0.123 para sempre

O HotXLS lê e escreve a flag em ambos os formatos e expõe-a nos dois motores:

  • TXLSXWorkbook.FullPrecision: Boolean no motor XLSX, carregada de e gravada em calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean no motor Classic (também em IXLSWorkbook), carregada de e gravada no registo CalcPrecision
  • Ambas são por omissão True, que é o modo seguro, não destrutivo, e a predefinição do Excel

Onde o HotXLS aplica o arredondamento interessa. O HotXLS arredonda no ponto em que calcula um valor: cada resultado de fórmula é arredondado para a sua precisão exibida antes de ser guardado como valor em cache da célula, durante o Recalculate e durante a avaliação a pedido. Constantes que atribui através de Value são guardadas exatamente como dadas. Se a sua saída tem de reproduzir o que o Excel guarda depois de a caixa ser marcada, arredonde essas constantes você próprio antes de as escrever, por exemplo com o ajudante mostrado mais abaixo

Como é que o Excel decide quantos decimais manter?

O Excel deriva o número de decimais mantidos da secção de formato específica 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 o XlsApplyDisplayedPrecision em lxNumFormat implementa para ambos os motores do HotXLS

  1. Escolha a secção pelo sinal. Um formato de duas secções usa a segunda secção para valores negativos. Um formato com três ou mais secções usa a segunda para valores negativos e a terceira para exatamente zero. Todo o resto usa a primeira secção
  2. Conte os placeholders decimais. Cada 0, # ou ? depois do ponto decimal nessa secção acrescenta um decimal mantido
  3. Acrescente dois por sinal de percentagem. O 0.0% mostra 0.1234 como 12.3%, por isso o valor guardado é a centésima parte do que vê e mantém três decimais, não um
  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, por isso o Excel mantém um decimal menos três, o que é uma contagem negativa: o valor é arredondado às centenas e guardado como 12300. Uma vírgula entre placeholders inteiros, como em #,##0, é agrupamento de dígitos simples e não muda nada
  5. Deixe as secções não numéricas em paz. Secções General, de data e hora (incluindo elapsed [h], [mm] e [ss]), científicas, de fração e de texto, e secções sem placeholder de dígito nenhum mantêm a precisão total
Diagrama HotXLS das regras de precisão exibida: escolha a secção de formato pelo sinal do valor, conte os placeholders de dígitos depois do ponto decimal, acrescente dois decimais por sinal de percentagem, subtraia três por vírgula de escala de milhares para que a contagem possa ficar negativa, salte as secções General e de data e hora por inteiro, depois arredonde metade para longe do zero
a contagem de dígitos vem da secção que corresponde ao sinal, mais dois por percentagem e menos três por vírgula de escala, e uma contagem negativa arredonda a dezenas ou centenas; as secções General e de data ficam em paz

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

Formato numéricoValor calculadoValor guardadoRegra que se aplica
0.0%0.12340.123Um decimal mais dois pelo sinal de percentagem
02.53Metade para longe do zero, não para o par
0-2.5-3Metade para longe do zero também do lado negativo
0.00;(0.0)-1.2345-1.2A secção negativa mostra um decimal
0.00;(0.0)1.23451.23A secção positiva mostra dois decimais
#,##0.01234.56781234.6Vírgula de agrupamento, sem escala
0.0,12345.67812300Um decimal menos três: arredonda às centenas
0.0%;(0.00%)-0.0125-0.0125A secção negativa mantém dois mais dois decimais
0.001.0051.01Tolerância ao erro de representação binária
0;-0;0.00.51Não é zero, por isso a secção positiva decide

A última linha é uma boa armadilha. O valor 0.5 arredonda a um número inteiro, e a secção do zero nunca entra em cena, porque o Excel escolhe a secção a partir do valor calculado antes de arredondar. Uma limitação honesta do lado do HotXLS: as secções são escolhidas apenas pelo sinal, por isso um formato cujas secções transportem condições entre parênteses retos personalizadas como [>=1000] ainda é dividido pelo sinal. Verifique tais formatos contra o Excel se lhe interessarem

Porque é 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 mesmo que o double mais próximo de 1.005 esteja 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 vírgula flutuante binária. O double IEEE 754 mais próximo é 1.00499999999999989341858963598497211933135986328125, e multiplicar por 100 dá 100.49999999999999. Um Floor(x * 100 + 0.5) / 100 de manual por isso devolve 1.00, o que diverge do número que o utilizador digitou, do que o Excel mostra, e do que o Excel guarda

O Delphi acrescenta o seu próprio toque. O System.Round arredonda empates para o par, por isso Round(2.5) é 2 e Round(3.5) é 4. Isso é arredondamento de banqueiro, uma predefinição sensata para estatística e a regra errada aqui: o Excel guarda 3 para 2.5 numa célula 0 e -3 para -2.5. A implementação do HotXLS trabalha sobre o 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, desescala e restaura o sinal. A função seguinte é uma ilustração autossuficiente desse princípio, não o código da biblioteca em si, e trata contagens de dígitos negativas para vírgulas de escala da mesma maneira:

Diagrama de arredondamento 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 banqueiro 2 e -2, e como o double mais próximo de 1.005 fica mesmo abaixo do ponto médio, a tolerância de poucos ulps é o que transforma um 1.00 baseado em 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 pequena tolerância; ambos os detalhes são mensuráveis, e saltar qualquer um deles guarda 2 para 2.5 ou 1.00 para 1.005, a um cêntimo do Excel
// Esboço de princípio: arredondar metade para longe do zero a ADigits decimais,
// com uma tolerância de poucos ulps para que 1.005 chegue a 1.01.
// ADigits < 0 arredonda a dezenas, centenas, ... ("0.0," dá -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dois ulp 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 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; // a escala 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   (Floor-based: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 digits)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 digits)

A tolerância é uma troca deliberada. 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 do erro de representação, e tratá-lo como um meio passo é o que faz os decimais digitados comportarem-se como os utilizadores esperam

O que correu mal antes da v2.384.57?

Antes da v2.384.57 o motor XLSX e o motor Classic tinham cada um o seu próprio código de precision-as-displayed, e cada um errava de uma maneira diferente. Se produz livros com a opção ligada, estes são os sintomas a procurar em ficheiros gerados por compilações mais antigas

Motor XLSX: só a primeira secção, sem percentagem, arredondamento de banqueiro

O caminho XLSX antigo pedia a contagem de decimais da string de formato como um todo, que só olhava para a primeira secção e ignorava %, e depois arredondava com Round. Um 0.1234 em 0.0% era guardado como 0.1, que são 10% em vez dos 12.3% no ecrã. Um 2.5 em 0 era guardado como 2 em vez de 3. Valores negativos num formato como 0.00;(0.0) eram arredondados para os dois decimais da secção positiva. Desde a v2.384.57 o motor XLSX chama a mesma rotina partilhada que o motor Classic, que também ganhou suporte a vírgulas de escala nessa versão

Motor Classic: TRUE tornava-se -1

O motor Classic guardava o seu arredondamento com VarIsNumeric, e o VarIsNumeric devolve True para um Variant varBoolean. Converter esse Variant com Double(V) produz -1, porque um Boolean True à maneira COM é guardado como -1. Uma fórmula como =A1>0 numa célula formatada 0.00 por isso 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 um resultado lógico em ambos os motores

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

O terceiro bug sentava-se no modelo de formatos numéricos e não no arredondamento. O parser classificava cada token entre parênteses retos que não fosse uma condição como uma cor, por isso [h], [mm] e [ss] nunca marcavam a sua secção como data/hora. A exibição não era afetada, porque a formatação corre num caminho separado, mas o precision as displayed depende dessa flag para saltar valores de tempo. Uma duração de cinco segundos são 5/86400 de um dia, cerca de 0.0000579, e um formato como [ss].00 parecia um número comum de dois decimais, por isso com o FullPrecision desligado a duração era arredondada para 0.00 dias. Desde a v2.384.9 uma sequência entre parênteses retos de uma única letra h, m ou s é analisada como um token de tempo decorrido e a secção é tratada como data/hora. A mesma versão corrigiu a deteção de minutos em h:mm, onde os dois pontos entre os tokens escondiam a hora ao parser

Diagrama HotXLS de uma má leitura de tempo decorrido: cinco segundos guardados como uma fração de dia minúscula numa célula formatada com o token ss entre parênteses retos, que o parser antigo lia como uma cor e marcava como um número simples de dois decimais, por isso o precision as displayed arredondava a duração para 0.00 até ser analisada como uma secção de tempo decorrido
a formatação corria no seu próprio caminho, por isso a célula parecia correta enquanto o valor guardado arredondava para zero; uma única letra h, m ou s entre parênteses retos é um token de tempo decorrido, não uma cor, e a secção mantém a precisão total

Ligar o precision as displayed no HotXLS a partir de Delphi

Para obter valores guardados equivalentes aos do Excel, defina a flag antes da recalculação que a deve honrar, depois leia os resultados em cache ou grave. No motor XLSX, o FullPrecision é uma flag simples: mudá-lo não invalida resultados que um Recalculate anterior já guardou, por isso defina-o logo a seguir ao 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)

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

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

O motor Classic comporta-se da mesma maneira, com uma conveniência: atribuir TXLSWorkbook.UseFullPrecision marca todas as fórmulas do grafo de dependências como dirty, por isso o próximo Recalculate reavalia o livro 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 guardado. Note que o Recalculate do Classic devolve o número de células de fórmula que não conseguiu avaliar, por isso 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 todas as fórmulas como dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: a secção negativa "(0.0)" mostra um decimal
    // C1 fica Boolean True (compilações antes da v2.384.57 guardavam -1)
    Wb.SaveAs('report.xls'); // registo CalcPrecision com fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Ambos os motores também honram a flag que vem com um ficheiro. Abra um livro gravado com a opção ligada e o FullPrecision ou UseFullPrecision já está False, por isso um Recalculate após carregar arredonda exatamente da maneira que o Excel faria. Se só precisa de ler os números que o Excel já guardou, pode saltar a recalculação por inteiro, como descrito em ler valores de fórmulas em cache sem recalculação. Para a forma como números de série e formatos de data interagem com o modelo de formatos que comanda a verificação de data/hora, veja números de série de data do Excel, o sistema 1904 e numFmt em Delphi

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

Ligue o precision as displayed só quando os números guardados do livro têm de ser iguais aos seus números exibidos, e aceita perder os dígitos extra para sempre. O caso legítimo clássico é um quadro financeiro em que colunas de montantes arredondados têm de somar o total arredondado no ecrã, sem frações escondidas de um cêntimo a produzir um total que falha por uma unidade no último dígito. Reproduzir um livro existente de um cliente que já tem a opção definida é a outra boa razão, e o HotXLS preserva a flag no round-trip para não mudar os livros silenciosamente de volta para a 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 dois decimais para um relatório destrói informação que nenhuma mudança de formato posterior pode restaurar
  • Percentagens com formatos grosseiros. Um formato 0% mantém só dois decimais do rácio guardado, por isso 0.1234 torna-se 0.12, e toda a fórmula a jusante que leia a célula trabalha com 0.12
  • Exibições escaladas. Um formato 0, ou 0.0, usado para mostrar milhares arredonda o valor guardado aos milhares ou às centenas, o que raramente é o que a pessoa que escolheu o formato pretendia
  • Templates partilhados. A flag é de todo o livro. Qualquer pessoa que mais tarde acrescente uma folha herda o comportamento, normalmente sem saber que está ligada

Se o que realmente quer são resultados arredondados em poucas células específicas, escreva antes ROUND nessas fórmulas. 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 de livro inteiro

Referência rápida do precision as displayed

  • Flag no ficheiro: CalcPrecision $000E com fFullPrec = 0 em BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" em XLSX (ECMA-376 Parte 1)
  • Interruptores HotXLS: TXLSXWorkbook.FullPrecision := False e TXLSWorkbook.UseFullPrecision := False, ambos True por omissão
  • Secção: escolhida pelo sinal do valor calculado; terceira secçã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, por isso 2.5 dá 3, -2.5 dá -3 e 1.005 dá 1.01
  • Saltados: General, data/hora e tempo decorrido, científicos, frações, texto, valores Boolean e de erro
  • Âmbito no HotXLS: resultados de fórmulas à medida que são calculados; constantes são guardadas como atribuídas
  • Motor XLSX: defina o FullPrecision antes do primeiro Recalculate; o setter do Classic volta a marcar todas as fórmulas como dirty ele próprio
  • Versões: a coincidir com o Excel 16 em ambos os motores desde a v2.384.57; formatos de tempo decorrido protegidos desde a v2.384.9

O HotXLS lê, escreve e calcula livros XLS e XLSX nativamente a partir de Delphi e C++Builder, incluindo as opções de cálculo do livro cobertas aqui. Detalhes, edições e a transferência de avaliação estão na página do componente de folhas de cálculo Delphi HotXLS