Você está offline; aguardando reconexão

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

O suporte para o Office 2003 terminou

A Microsoft terminou o suporte para o Office 2003 em 8 de abril de 2014. Esta alteração afetou as suas atualizações de software e opções de segurança. Saiba o que isto significa para você e como permanecer protegido.

Sumário
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
0001100110011100110011 (e assim por diante)
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
Mais Informações

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.
1 bit de sinalExpoente de 11 bits1 bit implícitoMantissa de 52 bits
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:
X = Fração * 2^(expoente - ajuste)
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:
   A1: 1.2E+200   B1: 1E+100   C1: =A1+B1
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:
   A1: 0.000123456789012345   B1: 1   C1: =A1+B1
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.
   A1: 1.2E+200   B1: 1E+100   C1: =ROUND(A1+B1,5)
é igual a 1.2E+200.
<![CDATA[   D1: =IF(C1=1.2E+200, VERDADEIRO, FALSO)
é igual a VERDADEIRO.

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:
000110011001100110011 (e assim por diante)
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
   Sub Main()      MySum = 0      For I% = 1 To 10000         MySum = MySum + 0.0001      Next I%      Debug.Print MySum   End Sub
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:
172911 Resultado incorreto ao elevar 10 a uma potência muito grande/muito pequena
214373 Resultado incorreto ao elevar 10 a uma potência muito grande/muito pequena
Para obter mais informações sobre números de ponto flutuante e a especificação IEEE 754, visite os seguintes sites :
Referências
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:
214118 Como corrigir erros de arredondamento em aritmética de ponto flutuante
XL98 XL97 XL7 XL5 XL4 XL3 2.0 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 5.00a 5.00c erro de matemática w_works XL2007 XL2003 XL2010
Propriedades

ID do Artigo: 78113 - Última Revisão: 09/18/2011 23:36:00 - Revisão: 2.0

  • Microsoft Excel 2010
  • Microsoft Office Excel 2008 for Mac
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X para Macintosh
  • Microsoft Excel 2001 para Mac
  • Microsoft Excel 98 for Macintosh
  • Microsoft Office Excel 2003
  • kbinfo KB78113
Comentários
om/ms.js">