Partilhar via

A vírgula flutuante aritmética pode dar resultados incorretos no Excel

  • Aplica-se a: Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Resumo

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

Descrição geral

O Microsoft Excel segue a especificação IEEE 754 para determinar como armazenar e calcular números de vírgula flutuante. IEEE é o Institute of Electrical and Electronics Engineers, um corpo internacional que, entre outras coisas, determina as normas para software informático e hardware. A especificação 754 é uma especificação amplamente adotada que descreve como os números de vírgula flutuante devem ser armazenados em um computador binário. É popular porque permite que números de vírgula flutuante sejam armazenados em uma quantidade razoável de espaço e que os cálculos ocorram relativamente rapidamente. A norma 754 é utilizada nos processadores de dados numéricos e unidades de vírgula flutuante de quase todos os microprocessos baseados em PC que implementam matemática em vírgula 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 fracionário. Por exemplo, a fração 1/10 pode ser representada num sistema de números decimais como 0,1. No entanto, o mesmo número no formato binário torna-se o seguinte decimal binário repetido:

0001100110011100110011 (e assim por cima)

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

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

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

Mais Informações

Limitações máximas/mínimas

Todos os computadores têm um número máximo e mínimo que pode ser tratado. Uma vez que o número de bits de memória onde o número está armazenado é finito, o número máximo ou mínimo que pode ser armazenado também é finito. Para 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 aderimos à IEEE 754

  • Fluxo insuficiente: o fluxo insuficiente ocorre quando é gerado um número demasiado 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 e o Excel não).
  • Excesso: o excesso ocorre quando um número é demasiado grande para ser representado. O Excel utiliza a sua própria representação especial para este caso (#NUM!).

Casos em que não aderimos ao IEEE 754

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

    Exemplo: um número normalizado tem 1 implícito à esquerda. Por exemplo, se a mantissa representar 0011001, o número normalizado torna-se 10011001 devido ao 1 implícito à esquerda. Um número desnormalizado não tem um número implícito à frente, portanto, no nosso exemplo de 0011001, o número desnormalizado permanece o mesmo. Neste caso, o número normalizado tem oito algarismos significativos (10011001), enquanto o número não normalizado tem cinco algarismos significativos (11001) com zeros à esquerda a serem insignificantes.

    Os números não normalizados são basicamente uma alternativa para permitir que os números inferiores ao limite inferior normal sejam armazenados. A Microsoft não implementa essa parte opcional da especificação porque os números desnormalizados, por sua própria natureza, têm um número variável de dígitos significativos. Isto pode permitir a introdução de erros significativos em cálculos.

  • Infinitos Positivos/Negativos: as infinidades ocorrem quando divide por 0. O Excel não suporta infinidades, pelo contrário, dá um #DIV/0! nesses casos.

  • Não é um número (NaN): NaN é utilizado para representar operações inválidas (como infinito/infinito, infinito-infinito ou a raiz quadrada de -1). Os NaNs permitem que um programa continue a além uma operação inválida. Em vez disso, o Excel gera imediatamente um erro como #NUM! ou #DIV/0!.

Precisão

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

O sinal O expoente A mantissa
1 bit de sinal Expoente de 11 bits 1 bit implícito + 52 bits de fração

O sinal armazena o sinal do número (positivo ou negativo), o expoente armazena a potência de 2 ao qual o número é elevado 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 as mantissas limita a proximidade de dois números de vírgula flutuante adjacentes (ou seja, a precisão).

A mantissa e o expoente são ambos armazenados como componentes separados. Como resultado, a quantidade de precisão possível pode variar consoante o tamanho do número (a mantissa) que está a ser manipulado. No caso do Excel, embora o Excel possa armazenar números de 1,79769313486232E308 a 2,2250738585072E-308, só o pode fazer dentro de 15 dígitos de precisão. Esta limitação é um resultado direto de seguir estritamente a especificação IEEE 754 e não é uma limitação do Excel. Este nível de precisão também se encontra noutros programas de folhas de cálculo.

Os números de vírgula flutuante são representados na seguinte forma, em que expoente é o expoente binário:

X = Fração * 2^(expoente - bias)

Fração é a parte fracionária normalizada do número, normalizada porque o expoente é ajustado de modo a que o bit à esquerda seja sempre um 1. Desta forma, ele não precisa ser armazenado, e você obtém mais um pouco de precisão. É por isso que há um pouco implícito. É semelhante à notação científica, em que manipula o expoente para ter um dígito à esquerda do ponto decimal; Exceto no binário, pode sempre manipular o expoente de modo a que o primeiro bit seja um 1, porque existem apenas 1 e 0.

Bias é o valor de bias utilizado para evitar ter de armazenar expoentes negativos. O bias para números de precisão única é 127 e 1,023 (decimal) para números de precisão dupla. O Excel armazena números utilizando a precisão dupla.

Exemplo utilizando números muito grandes

Introduza o seguinte num novo livro:

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 comparar as células A1 e C1 com a função SE, por exemplo SE(A1=C1), o resultado será VERDADEIRO. Isto é causado pela especificação IEEE de armazenar apenas 15 algarismos significativos de precisão. Para ser capaz de armazenar o cálculo acima, o Excel exigiria pelo menos 100 dígitos de precisão.

Exemplo utilizando números muito pequenos

Introduza o seguinte num novo livro:

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

O valor resultante na célula C1 seria 1,00012345678901 em vez de 1,000123456789012345. Isto é causado pela especificação IEEE de armazenar apenas 15 algarismos significativos de precisão. Para poder armazenar o cálculo acima, o Excel precisaria de, no mínimo, 19 dígitos de precisão.

Corrigir erros de precisão

O Excel oferece dois métodos básicos para compensar os erros de arredondamento: a função ARRED e a opção do livro Precisão ou Definir precisão como apresentado.

Método 1: a função ARRED

Utilizando os dados anteriores, o exemplo seguinte utiliza a função ARRED para forçar um número para cinco dígitos. Isto permite-lhe comparar com êxito o resultado com outro valor.

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

Isto resulta em 1,2E+200.

D1: =SE(C1=1,2E+200; VERDADEIRO, FALSO)

Isto resulta no valor VERDADEIRO.

Método 2: precisão conforme apresentado

Em alguns casos, poderá impedir que o arredondado de erros afete o seu trabalho ao utilizar a opção Precisão como apresentado. Esta opção força o valor de cada número na folha de cálculo para ser o valor apresentado. Para ativar esta opção, siga estes passos.

  1. No menu Ficheiro, clique em Opções e, em seguida, clique na categoria Avançadas.
  2. Na secção Ao calcular este livro, selecione o livro que pretende e, em seguida, selecione a caixa de verificação Definir precisão como apresentada.

Por exemplo, se escolher um formato de número que mostre duas casas decimais e, em seguida, ativar a opção Precisão como apresentada, toda a precisão para além de duas casas decimais é perdida quando guarda o livro. Esta opção afeta o livro ativo, incluindo todas as folhas de cálculo. Não é possível desfazer essa opção e recuperar os dados perdidos. Recomendamos que guarde a sua cópia de segurança antes de ativar esta opção.

Repetição de números binários e cálculos com resultados próximos de zero

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

000110011001100110011 (e assim por diante)

A especificação IEEE 754 não tem qualquer custo especial em qualquer número. Armazena o que pode na mantissa e trunca o resto. Isso resulta em um erro de cerca de -2.8E-17 ou 0,00000000000000028 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 repetida que tem um período de 104 bits). Isso é semelhante ao motivo pelo qual a fração 1/3 não pode ser representada exatamente em decimal (uma repetição de 0,333333333333333333333).

Por exemplo, considere o seguinte 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

Isto irá IMPRIMIR 0,999999999999996 como resultado. O pequeno erro ao representar 0,0001 em binário propaga-se para a soma.

Exemplo: adicionar um número negativo

  1. Introduza o seguinte num novo livro:

    A1: =(43,1-43,2)+1

  2. Clique com o botão direito do rato na célula A1 e, em seguida, clique em Formatar Célula. No separador Número, clique em Científico em Categoria. Defina as Casas decimais para 15.

Em vez de apresentar 0,9, o Excel apresenta 0,899999999999999. Uma vez que (43,1-43,2) é calculado primeiro, -0,1 é armazenado temporariamente e o erro ao armazenar -0,1 é introduzido no cálculo.

Exemplo quando um valor atinge zero

  1. No Excel 95 ou anterior, introduza o seguinte num novo livro:

    A1: =1,333+1,225-1,333-1,225

  2. Clique com o botão direito do rato na célula A1 e, em seguida, clique em Formatar Célula. No separador Número, clique em Científico em Categoria. Defina as Casas decimais para 15.

Em vez de apresentar 0, o Excel 95 apresenta -2,22044604925031E-16.

O Excel 97, no entanto, introduziu uma otimização que tenta corrigir este problema. Caso uma operação de adição ou subtração resulte num valor igual ou muito próximo de zero, o Excel 97 e posterior irão compensar qualquer erro introduzido como resultado da conversão de um operando de e para o binário. O exemplo acima, quando efetuado no Excel 97 e posterior, apresenta corretamente 0 ou 0,000000000000000E+00 em notação científica.

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