Aritmética de ponto flutuante pode fornecer resultados imprecisos no Excel

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

Visão geral

O Microsoft Excel foi projetado com base na especificação IEEE 754 para armazenar e calcular números de ponto flutuante. IEEE (Institute of Electrical and Electronics Engineers), uma instituição internacional que, entre outras coisas, determina padrões para software e hardware de 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 é popular porque permite que os números de ponto flutuante sejam armazenados em uma quantidade razoável de espaço e que os cálculos sejam realizados de maneira relativamente rápida. O padrão 754 é utilizado nas unidades de ponto flutuante e nos processadores de dados numéricos em quase todos os microprocessadores atuais com base em PC que implementam aritmética de ponto flutuante, incluindo os processadores Intel, Motorola, Sun e MIPS.

Ao armazenar números, um número binário correspondente pode representar todos o números ou número fracionário. Por exemplo, a fração 1/10 pode ser representada em um sistema de número decimal como 0.1. Entretanto, o mesmo número em um formato binário torna-se o decimal binário repetitivo e pode ser repetido infinitamente. Este número não pode ser representado em uma quantidade de espaço finita (limitada). Por isso, esse número é arredondado para baixo em aproximadamente -2.8E-17 ao ser armazenado.

Entretanto, existem algumas limitações da especificação IEEE 754 que se encaixam em três categorias gerais:

• limitações máximas/mínimas
• precisão
• números binários repetitivos

Limitações máximas/mínimas

Todos os computadores têm um número máximo e mínimo que podem ser manipulados. Como o número de bits de memória na qual o número é armazenado é finito, conseqüentemente 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 nos quais seguimos a IEEE 754

• Estouro negativo: O estouro negativo ocorre quando um número gerado é muito pequeno para ser representado. Em IEEE e Excel, o resultado é 0 (com exceção de que o IEEE tem um conceito de of -0 e o Excel não).
• Estouro: O estouro ocorre quando um número é muito grande para ser representado. O Excel utiliza sua própria representação especial para este caso (#NUM!).

Casos nos quais não seguimos a IEEE 754

• Números desordenados: Um número desordenado é indicado por um expoente de 0. Neste caso, o número completo é armazenado na mantissa, e a mantissa não tem nenhum 1 à esquerda 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 pequena dentro deste intervalo possuem apenas um dígito de precisão.
  Exemplo: Um número normalizado tem um 1 à esquerda implícito. Por exemplo, se a mantissa representa 0011001, o número normalizado será 10011001 por causa do 1 à esquerda implícito. Um número desordenado não tem nenhum 1 à esquerda implícito, então, em nosso exemplo de 0011001, ele permanece o mesmo. Neste caso, o número normalizado tem oito dígitos significativos (10011001) enquanto que o número desordenado tem cinco dígitos significativos (11001), sendo os zeros à esquerda insignificantes.
  
  Os números desordenados são basicamente uma solução para permitir que os números menores que o limite normal mais baixo sejam armazenados. A Microsoft não implementa esta parte opcional da especificação, pois os números desordenados, por natureza, têm um número variável de dígitos significativos. Isto pode permitir erros significativos ao fazer cálculos.
• Infinitos positivos/negativos: Os infinitos ocorrem quando você divide por 0. O Excel não oferece suporte aos infinitos, em vez disso, ele dá um erro #DIV/0! nesses casos.
• Não é um número (NaN): NaN é usado para representar operações inválidas (tais como infinito/infinito, infinito-infinito ou a raiz quadrada de -1). Os NaNs permitem que um programa passe por uma operação inválida. O Excel, por sua vez, imediatamente gera um erro como #NUM! ou #DIV/0!.

Precisão

Um número de ponto flutuante é armazenado em um binário em três partes dentro de um intervalo de 65 bits: o sinal, o expoente e a mantissa. O sinal armazena o sinal do número (positivo ou negativo), o expoente armazena a potência de 2 para os quais o número é elevado ou diminuído (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 que dois números de ponto flutuante adjacentes podem estar (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 conforme o tamanho do número (a mantissa) que está sendo manipulado. No caso do Excel, embora possa armazenar números de 1.79769313486232E308 a 2.2250738585072E-308, ele só poderá fazer isso dentro de 15 dígitos de precisão. Esta limitação é um resultado direto por seguir estritamente a especificação IEEE 754 e não uma limitação do Excel. Este nível de precisão é encontrado em outros programas de planilha eletrônica também.

Os números de ponto flutuante são representados na seguinte forma, onde expoente é o expoente binário: Fração é a parte fracionária normal do número, normalizada porque o expoente é ajustado de modo que o bit à esquerda seja sempre 1. Assim, ele não precisa ser armazenado e você obtém mais um bit de precisão. Por isso existe um bit implícito. Isso parece com uma notação científica, onde você manipula o expoente para ter um dígito à esquerda do ponto decimal, com exceção dos binários, você pode sempre manipular o expoente de modo que o primeiro bit seja 1, pois existem apenas 1s e 0s.

O ajuste é a diferença usada para evitar o armazenamento de expoentes negativos. O ajuste para números de precisão única é 127 e 1,023 (decimal) para números de precisão dupla. O Excel armazena os números usando precisão dupla.

Exemplo usando números muito grandes

Digite o que segue em uma nova pasta de trabalho: O valor resultante na célula C1 será 1.2E+200, o mesmo valor da célula A1. De fato, se você comparar as células A1 e C1 usando a função IF, por exemplo, IF (A1=C1), o resultado será VERDADEIRO. Isso é causado pela especificação IEEE ao armazenar somente 15 dígitos significativos de precisão. Para poder armazenar o cálculo acima, o Excel solicitará pelo menos 100 dígitos de precisão.

Exemplo usando números muito pequenos

Digite o que segue em uma nova pasta de trabalho: O valor resultante na célula C1 será 1.00012345678901, em vez de 1.000123456789012345. Isso é causado pela especificação IEEE ao armazenar somente 15 dígitos significativos de precisão. Para poder armazenar o cálculo acima, o Excel solicitará pelo menos 19 dígitos de precisão.

Corrigir erros de precisão

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

Método 1: A função ROUND

O exemplo a seguir usando os dados acima, utiliza a função ROUND para forçar um número a cinco dígitos. Isto permite que você compare com êxito o resultado a outro valor.

Método 2: Precisão conforme exibido

Em alguns casos, você pode evitar que os erros de arredondamento afetem seu trabalho usando a opção Precisão conforme exibido. Essa opção força que o valor de cada número na planilha seja o valor exibido. Para ativar essa opção, execute as seguintes etapas:

1. No Excel 2003 e em versões anteriores, clique em Opções no menu Ferramentas.
2. Na guia Cálculo, marque a caixa de seleção Precisão conforme exibido.

1. No Excel 2007, clique no Botão Microsoft Office, em Opções do Excel e clique na categoria Avançado.
2. Na seção Ao calcular esta pasta de trabalho, selecione a pasta de trabalho desejada e marque a caixa de seleção Definir precisão conforme exibido.

Por exemplo, se você escolher um formato de número mostrando duas casas decimais e ativar a opção Precisão conforme exibido, toda a precisão além de dois decimais será perdida quando você 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. É recomendável que você salve sua pasta de trabalho antes de habilitar essa opção.

Repetir números binários e cálculos com resultados próximos a zero

Outro problema que causa confusão com o armazenamento de números de ponto flutuante em binários é que alguns números, que são números finitos não repetitivos na base decimal 10, são números finitos e repetitivos nos binários. O exemplo mais comum é o valor 0.1 e as variantes do mesmo. Embora esses números possam ser representados perfeitamente na base 10, o mesmo número no formato binário torna-se o próximo número binário repetitivo quando armazenado na mantissa: A especificação IEEE 754 não cria nenhuma provisão especial para qualquer número e trunca o resto. Isso resulta em um erro de aproximadamente -2.8E-17 ou 0.000000000000000028 quando armazenado.

Mesmo frações decimais comuns, tais como decimal 0.0001, não podem ser representadas corretamente nos binários. (0.0001 é uma fração binária repetitiva com um período de 104 bits). Isto é semelhante ao porque a fração 1/3 não pode ser representada corretamente em decimal (um 0.33333333333333333333 repetitivo).

Isto explica porque um simples exemplo no Microsoft Visual Basic for Applications IMPRIMIRÁ 0.999999999999996 como saída. O pequeno erro em representar 0.0001 no binário propaga na soma.

Exemplo Adicionar um número negativo

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

   <![CDATA[
      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 por armazenar -0.1 é introduzido no cálculo.

Exemplo Quando um valor chega a zero

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

   <![CDATA[
      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, porém, introduziu uma otimização que tenta corrigir esse problema. Se uma operação de soma ou subtração resultar em um valor igual ou muito próximo a zero, o Excel 97 e posterior irão compensar por qualquer erro introduzido como um resultado da conversão de um operando para ou de um binário. O exemplo acima quando executado corretamente no Excel 97 e posterior exibe 0 ou 0.000000000000000E+00 na notação científica. Para obter mais informações, clique nos números abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft: Para obter mais informações sobre números de ponto flutuante e a especificação IEEE 754, visite os seguintes sites :

Para obter informações adicionais sobre como solucionar esses erros, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft: