A aritmética de ponto flutuante pode dar resultados imprecisos no Excel

Resumo

Este artigo discute como o Microsoft Excel armazena e calcula números de ponto flutuante. Isso pode afetar os resultados de alguns números ou fórmulas devido a arredondamento ou truncamento de dados.

Visão Geral

O Microsoft Excel foi criado com base na especificação do IEEE 754 para determinar como armazenar e calcular números de ponto flutuante. O IEEE é o Electrical and Electronics Engineers, uma organização internacional que, entre outras coisas, determina padrões para software e hardware do computador. A especificação 754 é uma especificação amplamente adotada que descreve como os números de ponto flutuante devem ser armazenados em um computador binário. Ela é muito usada porque permite que números de ponto flutuante sejam armazenados em uma quantidade razoável de espaço e os cálculos ocorram de modo relativamente rápido. O padrão 754 é usado nas unidades de ponto flutuante e processadores de dados numéricos de quase todos os microprocessadores baseados em computador atuais que implementam a matemática de ponto flutuante, incluindo os processadores Intel, Motorola, Sun e MIPS.

Quando os números são armazenados, um número binário correspondente pode representar cada número ou número fracional. Por exemplo, a fração 1/10 pode ser representada em um sistema de números decimais como 0,1. No entanto, o mesmo número no formato binário se torna o seguinte decimal binário repetido:

0001100110011100110011 (e assim por diante)

Isso pode ser infinitamente repetido. Esse número não pode ser representado em uma quantidade finita (limitada) de espaço. Portanto, esse número é arredondado para baixo por aproximadamente -2.8E-17 quando é armazenado.

No entanto, há algumas limitações da especificação do IEEE 754 que se enquadram em três categorias gerais:

• Limitações máximas/mínimas
• Precisão
• Números binários repetidos

Informações adicionais

Limitações Máximas/Mínimas

Todos os computadores têm um número máximo e mínimo que pode ser manipulado. Como o número de bits de memória em que o número é armazenado é finito, o número máximo ou mínimo que pode ser armazenado também é finito. Para o Excel, o número máximo que pode ser armazenado é 1,79769313486232E+308 e o número positivo mínimo que pode ser armazenado é 2.2250738585072E-308.

Casos em que aderimos ao IEEE 754

• Subfluxo: o subfluxo ocorre quando um número gerado é muito pequeno para ser representado. No IEEE e no Excel, o resultado é 0 (com a exceção de que o IEEE tem um conceito de -0, o que não é o caso do Excel).
• Estouro: o estouro ocorre quando um número é muito grande para ser representado. O Excel usa sua própria representação especial para esse caso (#NUM!).

Casos em que não aderimos ao IEEE 754

• Números desnormalizados: um número desnormalizado é indicado por um expoente de 0. Nesse caso, o número inteiro é armazenado na mantissa e a mantissa não tem nenhum 1 inicial implícito. Como resultado, você perde precisão e quanto menor for o número, mais precisão será perdida. Os números na extremidade menor desse intervalo têm apenas um dígito de precisão.

  Exemplo: um número normalizado tem um número 1 inicial implícito. Por exemplo, se a mantissa representar 0011001, o número normalizado se tornará 10011001 devido ao 1 inicial implícito. Um número desnormalizado não tem um número inicial implícito, portanto, no nosso exemplo de 0011001, o número desnormalizado permanece o mesmo. Nesse caso, o número normalizado tem oito dígitos significativos (10011001), enquanto o número desnormalizado tem cinco dígitos significativos (11001) com zeros à esquerda insignificantes.

  Números desnormalizados são basicamente uma solução alternativa para permitir que números menores do que o limite inferior normal sejam armazenados. A Microsoft não implementa essa parte opcional da especificação porque números desnormalizados por sua própria natureza têm um número variável de dígitos significativos. Isso pode permitir que um erro significativo entre em cálculos.

• Infinitos Positivos/Negativos: os infinitos ocorrem quando você divide por 0. O Excel não oferece suporte a infinitos, em vez disso, ele exibe um erro #DIV/0! nesses casos.

• Not-a-Number (NaN): o NaN é usado para representar operações inválidas (como infinito/infinito, infinito-infinito ou a raiz quadrada de -1). NaNs permitem que um programa continue após uma operação inválida. O Excel, em vez disso, gera imediatamente um erro como #NUM! ou #DIV/0!.

Precisão

Um número de ponto flutuante é armazenado em binário em três partes dentro de um intervalo de 65 bits: o sinal, o expoente e a mantissa.

O signo armazena o sinal do número (positivo ou negativo), o expoente armazena a potência de 2 para a qual o número é gerado ou reduzido (a potência máxima/mínima de 2 é +1.023 e -1.022) e a mantissa armazena o número real. A área de armazenamento finita para a mantissa limita a proximidade de dois números de ponto flutuante adjacentes (ou seja, a precisão).

A mantissa e o expoente são armazenados como componentes separados. Como resultado, a quantidade de precisão possível pode variar dependendo do tamanho do número (a mantissa) que está sendo manipulado. No caso do Excel, embora o Excel possa armazenar números de 1.79769313486232E308 a 2.2250738585072E-308, ele só pode fazer isso dentro de 15 dígitos de precisão. Essa limitação é um resultado direto de seguir estritamente a especificação do IEEE 754 e não é uma limitação de Excel. Esse nível de precisão também é encontrado em outros programas de planilha.

Os números de ponto flutuante são representados no seguinte formulário, onde o expoente é o expoente binário:

X = Fração * 2^(exponente - diferença)

Fração é a parte fracionada normalizada do número, normalizada porque o expoente é ajustado para que o bit à frente seja sempre um 1. Dessa forma, ele não precisa ser armazenado e você terá mais um pouco de precisão. Por isso, há um bit implícito. Isso é semelhante à notação científica, onde você manipula o expoente para ter um dígito à esquerda do ponto decimal; exceto no binário, você sempre pode manipular o expoente para que o primeiro bit seja um 1, pois há apenas 1 e 0.

A diferença é o valor de diferença usado para evitar ter que armazenar expoentes negativos. A diferença para números de precisão única é 127 e 1.023 (decimal) para números de precisão dupla. O Excel armazena números usando precisão dupla.

Exemplo usando números muito grandes

Insira o seguinte em uma nova pasta de trabalho:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

O valor resultante na célula C1 seria 1.2E+200, o mesmo valor da célula A1. Na verdade, se você comparar as células A1 e C1 usando a função IF, por exemplo, IF(A1=C1), o resultado será TRUE. Isso é causado pela especificação do IEEE de armazenar apenas 15 dígitos significativos de precisão. Para poder armazenar o cálculo acima, o Excel exigiria pelo menos 100 dígitos de precisão.

Exemplo usando números muito pequenos

Insira o seguinte em uma nova pasta de trabalho:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

O valor resultante na célula C1 seria 1.00012345678901 em vez de 1.000123456789012345. Isso é causado pela especificação do IEEE de armazenar apenas 15 dígitos significativos de precisão. Para poder armazenar o cálculo acima, o Excel exigiria pelo menos 19 dígitos de precisão.

Correção de erros de precisão

O Excel oferece dois métodos básicos para compensar os erros de arredondamento: a função ROUND e a opção da pasta de trabalho Precisão conforme exibido ou Definir precisão conforme exibido.

Método 1: a função ROUND

Usando os dados anteriores, o exemplo a seguir usa a função ROUND para forçar um número a ter cinco dígitos. Isso permite comparar com êxito o resultado com outro valor.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

Isso resulta em 1.2E+200.

D1: =IF(C1=1.2E+200, TRUE, FALSE)

Isso resulta no valor TRUE.

Método 2: precisão conforme exibido

Em alguns casos, você pode impedir que erros de arredondamento afetem seu trabalho usando a opção Precisão conforme exibido. Essa opção força o valor de cada número na planilha a ser o valor exibido. Para alterar essa opção, siga estas etapas.

1. No menu Arquivo, clique em Opções e depois na categoria Avançado.
2. Na seção Ao calcular esta pasta de trabalho, selecione a pasta de trabalho que você deseja e marque a caixa de seleção Definir precisão conforme exibido.

Por exemplo, se você escolher um formato de número que mostra duas casas decimais e, em seguida, ativar a opção Precisão conforme exibido, toda a precisão além de duas casas decimais será perdida ao salvar sua pasta de trabalho. Essa opção afeta a pasta de trabalho ativa, incluindo todas as planilhas. Não é possível desfazer essa opção e recuperar os dados perdidos. Recomendamos que você salve sua pasta de trabalho antes de habilitar essa opção.

Números binários e cálculos repetidos que têm resultados quase zero

Outro problema confuso que afeta o armazenamento de números de pontos flutuantes no formato binário é que alguns números finitos, não repetidos na base decimal 10, são infinitos, repetindo números em binário. O exemplo mais comum disso é o valor 0,1 e suas variações. Embora esses números possam ser representados perfeitamente na base 10, o mesmo número em formato binário se torna o seguinte número binário repetido quando armazenado na mantissa:

000110011001100110011 (e assim por diante)

A especificação do IEEE 754 não faz nenhuma concessão especial para qualquer número. Armazena o que é possível na mantissa e trunca o restante. Isso resulta em um erro de cerca de -2.8E-17 ou 0,000000000000000028 quando armazenado.

Mesmo frações decimais comuns, como decimal 0,0001, não podem ser representadas exatamente em binário. (0,0001 é uma fração binária que tem um período de 104 bits). Isso é semelhante ao motivo pelo qual a fração 1/3 não pode ser exatamente representada em decimal (uma repetição 0,33333333333333333333).

Por exemplo, considere o seguinte exemplo simples no Microsoft Visual Basic for Applications:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Isso IMPRIMIRÁ 0,999999999999996 como saída. O pequeno erro ao representar 0,0001 em binário propaga-se para a soma.

Exemplo: adicionar um número negativo

1. Insira o seguinte em uma nova pasta de trabalho:

   A1: =(43.1-43.2)+1

2. Clique com o botão direito do mouse na célula A1 e em Formatar Células. Na guia Número, clique em Científico em Categoria. Defina as Casas decimais como 15.

Em vez de exibir 0,9, o Excel exibe 0,899999999999999. Como (43.1-43.2) é calculado primeiro, -0.1 é armazenado temporariamente, e o erro de armazenamento -0,1 é introduzido no cálculo.

Exemplo quando um valor atinge zero

1. No Excel 95 ou anterior, insira o seguinte em uma nova pasta de trabalho:

   A1: =1.333+1.225-1.333-1.225

2. Clique com o botão direito do mouse na célula A1 e em Formatar Células. Na guia Número, clique em Científico em Categoria. Defina as Casas decimais como 15.

Em vez de exibir 0, o Excel 95 exibe -2.22044604925031E-16.

O Excel 97, no entanto, introduziu uma otimização que tenta corrigir esse problema. Se uma operação de adição ou subtração resultar em um valor igual ou muito próximo a zero, o Excel 97 e as versões posteriores compensarão qualquer erro introduzido como resultado da conversão de um operand com destino e origem de um binário. O exemplo acima, quando executado no Excel 97 e versões posteriores corretamente exibe 0 ou 0,000000000000000E+00 em notação científica.

Para obter mais informações sobre números de ponto flutuante e a especificação do IEEE 754, consulte os seguintes sites da World Wide Web:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html