Precisão numérica no Microsoft Excel - Numeric precision in Microsoft Excel

Como acontece com outras planilhas, o Microsoft Excel funciona apenas com precisão limitada porque retém apenas um certo número de algarismos para descrever números (tem precisão limitada ). Com algumas exceções em relação a valores errôneos, infinitos e números desnormalizados, o Excel calcula em formato de ponto flutuante de precisão dupla a partir da especificação IEEE 754 (além dos números, o Excel usa alguns outros tipos de dados). Embora o Excel possa exibir 30 casas decimais, sua precisão para um número especificado é limitada a 15 algarismos significativos , e os cálculos podem ter uma precisão ainda menor devido a cinco questões: arredondamento , truncamento e armazenamento binário , acúmulo dos desvios de os operandos nos cálculos, e o pior: cancelamento nas subtrações resp. 'Cancelamento catastrófico' na subtração de valores com magnitude semelhante.

Precisão e armazenamento binário

O Excel mantém 15 algarismos em seus números, mas eles nem sempre são precisos: matemático, a linha inferior deve ser igual à linha superior, em 'fp-matemática' a etapa '1 + 1/9000' leva a um arredondamento para cima como o primeiro bit da cauda de 14 bits '10111000110010' da mantissa caindo da tabela ao adicionar 1 é um '1', este arredondamento não é desfeito ao subtrair o 1 novamente, uma vez que não há informações sobre a origem dos valores nesta etapa. Assim, a 're-subtração' de 1 deixa uma mantissa terminando em '100000000000000' em vez de '010111000110010', representando um valor de '1.1111111111117289E-4' arredondado por ex $ el para 15 dígitos significativos: '1.11111111111173E-4'.

É claro que matemático 1 + x - 1 = x, 'matemática de ponto flutuante' às vezes é um pouco diferente, isso não é culpa do Excel. A discrepância indica o erro. Todos os erros estão além do 15º dígito significativo do valor intermediário 1 + x, todos os erros estão em dígitos de valor alto do resultado final, que é o efeito problemático de 'cancelamento'.

Na figura superior, a fração 1/9000 no Excel é exibida. Embora esse número tenha uma representação decimal que é uma seqüência infinita de uns, o Excel exibe apenas os 15 dígitos à esquerda. Na segunda linha, o número um é adicionado à fração e, novamente, o Excel exibe apenas 15 algarismos. Na terceira linha, um é subtraído da soma usando o Excel. Como a soma tem apenas onze 1s após o decimal, a verdadeira diferença quando '1' é subtraído é três 0s seguidos por uma sequência de onze 1s. No entanto, a diferença relatada pelo Excel é três 0s seguidos por uma seqüência de 15 dígitos de treze 1s e dois dígitos extras errôneos. Portanto, os números com os quais o Excel calcula não são os números que ele exibe. Além disso, o erro na resposta do Excel não é simplesmente um erro de arredondamento, é um efeito nos cálculos de ponto flutuante denominado 'cancelamento'.

A imprecisão nos cálculos do Excel é mais complicada do que os erros devido à precisão de 15 algarismos significativos. O armazenamento de números no formato binário do Excel também afeta sua precisão. Para ilustrar, a figura inferior tabula a adição simples 1 + x - 1 para vários valores de x . Todos os valores de x começam na décima quinta casa decimal, portanto o Excel deve levá-los em consideração. Antes de calcular a soma 1 + x , o Excel primeiro aproxima x como um número binário. Se essa versão binária de x for uma potência simples de 2, a aproximação decimal de 15 dígitos ax é armazenada na soma e os dois primeiros exemplos da figura indicam a recuperação de x sem erro. No terceiro exemplo, x é um número binário mais complicado, x = 1,110111 ⋯ 111 × 2 ⁻⁴⁹ (15 bits ao todo). Aqui, o 'valor duplo IEEE 754' resultante da figura de 15 bits é 3,330560653658221E-15, que é arredondado pelo Excel para a 'interface do usuário' para 15 dígitos 3,33056065365822E-15 e, em seguida, exibido com 30 dígitos decimais obtém um 'falso zero 'adicionado, portanto, os valores' binários 'e' decimais 'na amostra são idênticos apenas na exibição, os valores associados às células são diferentes (1,110111111111110000000000000000000000000000000000000000000000 × 2 ⁻⁴⁹ vs. 1,110111111111101111111111111111111111111111111111111101 × 2 ⁻⁴⁹ ). Similar é feito por outras planilhas, o tratamento da quantidade diferente de dígitos decimais que podem ser armazenados exatamente na mantissa de 53 bits de um 'double' (por exemplo, 16 dígitos entre 1 e 8, mas apenas 15 entre 0,5 e 1 e entre 8 e 10) é um pouco difícil e resolvido como 'subótimo'. No quarto exemplo, x é um número decimal não equivalente a um binário simples (embora concorde com o binário do terceiro exemplo quanto à precisão exibida). A entrada decimal é aproximada por um binário e então esse decimal é usado. Esses dois exemplos intermediários na figura mostram que algum erro foi introduzido.

Os dois últimos exemplos ilustram o que acontece se x for um número bastante pequeno. No segundo exemplo anterior, x = 1,110111 ⋯ 111 × 2 ⁻⁵⁰ ; 15 bits no total. O binário é substituído grosseiramente por uma única potência de 2 (neste exemplo, 2 ⁻⁴⁹ ) e seu equivalente decimal é usado. No exemplo inferior, um decimal idêntico ao binário acima para a precisão mostrada, é, no entanto, aproximado de forma diferente do binário e é eliminado por truncamento para 15 algarismos significativos, não contribuindo para 1 + x - 1 , levando a x = 0 .

Para x ′ s que não são potências simples de 2, um erro perceptível em 1 + x - 1 pode ocorrer mesmo quando x é muito grande. Por exemplo, se x = 1/1000, então 1 + x - 1 = 9,99999999999 89 × 10 ⁻⁴ , um erro no 13º algarismo significativo. Nesse caso, se o Excel simplesmente adicionar e subtrair os números decimais, evitando a conversão para binário e de volta para decimal, nenhum erro de arredondamento ocorrerá e a precisão na verdade seria melhor. O Excel tem a opção de "Definir a precisão conforme exibida". Com esta opção, dependendo das circunstâncias, a precisão pode ser melhor ou pior, mas você saberá exatamente o que o Excel está fazendo. (Apenas a precisão selecionada é mantida e não é possível recuperar dígitos extras invertendo esta opção.) Alguns exemplos semelhantes podem ser encontrados neste link.

Em suma, uma variedade de comportamento de precisão é introduzida pela combinação de representar um número com um número limitado de dígitos binários, juntamente com números truncados além do décimo quinto algarismo significativo. O tratamento do Excel de números além de 15 algarismos significativos às vezes contribui com melhor precisão para os poucos algarismos significativos finais de um cálculo do que trabalhar diretamente com apenas 15 algarismos significativos, e às vezes não.

Para saber o raciocínio por trás da conversão para representação binária e de volta para decimal, e para obter mais detalhes sobre a precisão no Excel e VBA, consulte estes links.

1. As deficiências nas tarefas '= 1 + x - 1' são uma combinação de 'fraquezas fp-matemática' e 'como o Excel lida com isso', especialmente os arredondamentos do Excel. O Excel faz alguns arredondamentos e / ou 'ajuste a zero' para a maioria de seus resultados, em média cortando os últimos 3 bits da representação dupla do IEEE. Este comportamento pode ser alterado definindo a fórmula entre parênteses: '= (1 + 2 ^ -52 - 1)'. Você verá que mesmo esse pequeno valor sobrevive. Valores menores desaparecerão, pois há apenas 53 bits para representar o valor, para este caso 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, o primeiro representando '1' e o último '2 ^ -52'.

2. Não são apenas as potências limpas de dois sobreviventes, mas qualquer combinação de valores construída de bits que estará dentro dos 53 bits uma vez que o decimal 1 é adicionado. Como a maioria dos valores decimais não tem uma representação finita limpa em binário, eles sofrerão de 'arredondamento' e 'cancelamento' em tarefas como as acima.

Por exemplo, o decimal 0,1 tem a representação dupla IEEE 0 (1) .10011001100110011001100110011001100110011001100110011010 vezes 2 ^ -4 e adicionado a 140737488355328.0 (que é 2 ^ 47) perderá todos os seus bits, exceto os dois primeiros. Assim, de '= (140737488355328,0 + 0,1 - 140737488355328,0) ele retornará como 0,09375 calculado com www.weitz.de/ieee (64 bits), bem como no Excel com os parênteses ao redor da fórmula. Esse efeito pode ser gerenciado principalmente por arredondamento significativo, que o Excel não aplica, fica a critério do usuário.

Desnecessário dizer: outras planilhas têm problemas semelhantes, o LibreOffice Calc usa um arredondamento mais agressivo, enquanto o gnumeric tenta manter a precisão e tornar tanto a precisão quanto a 'falta de' visíveis para o usuário.

Exemplos em que a precisão não é um indicador de exatidão

Funções estatísticas

Erro no cálculo do desvio padrão do Excel 2007. Todas as quatro colunas têm o mesmo desvio de 0,5

A precisão nas funções fornecidas pelo Excel pode ser um problema. Micah Altman et al. forneça este exemplo: O desvio padrão da população dado por:

${\ displaystyle {\ sqrt {\ frac {\ Sigma (x - {\ bar {x}}) ^ {2}} {n}}} = {\ sqrt {\ frac {\ Sigma \ left [x- \ left (\ Sigma x \ direita) / n \ direita] ^ {2}} {n}}} \,}$

é matematicamente equivalente a:

${\ displaystyle {\ sqrt {\ frac {n \ Sigma x ^ {2} - \ left (\ Sigma x \ right) ^ {2}} {n ^ {2}}}} \.}$

No entanto, a primeira forma mantém uma melhor precisão numérica para grandes valores de x , porque os quadrados das diferenças entre x e x _av levam a menos arredondamentos do que as diferenças entre os números muito maiores Σx ² e (Σx) ² . A função interna do Excel STDEVP (), entretanto, usa a formulação menos precisa porque é computacionalmente mais rápida.

Tanto a função de "compatibilidade" STDEVP quanto a função de "consistência" STDEV.P no Excel 2010 retornam o desvio padrão da população de 0,5 para o conjunto de valores fornecido. No entanto, a imprecisão numérica ainda pode ser mostrada usando este exemplo, estendendo o número existente para incluir 10 ¹⁵ , após o que o desvio padrão incorreto encontrado pelo Excel 2010 será zero.

Subtração dos resultados da subtração

Fazer subtrações simples pode levar a erros, pois duas células podem exibir o mesmo valor numérico enquanto armazenam dois valores separados. Um exemplo disso ocorre em uma planilha em que as células a seguir são definidas com os seguintes valores numéricos:

${\ displaystyle A1: = 28.552}$

${\ displaystyle A2: = 27,399}$

${\ displaystyle A3: = 26,246}$

e as células a seguir contêm as seguintes fórmulas

${\ displaystyle B1: = A1-A2}$

${\ displaystyle B2: = A2-A3}$

Ambas as células e display . No entanto, se a célula contiver a fórmula , não será exibida como seria de se esperar, mas em vez disso. ${\ displaystyle B1}$${\ displaystyle B2}$${\ displaystyle 1,1530}$${\ displaystyle C1}$${\ displaystyle B1-B2}$${\ displaystyle C1}$${\ displaystyle -3.55271E-15}$

O acima não está limitado a subtrações, tente '= 1 + 1,405 * 2 ^ -48' em uma célula, o Excel arredonda a exibição para 1,00000000000000000000 e '= 0,9 + 225179982494413 * 2 ^ -51' em outra, mesma exibição (na faixa acima de 1 / abaixo de 1 o arredondamento é diferente, que atinge a maioria das alterações de magnitude decimal ou binária) acima, arredondamento diferente para valor e exibição, viola um dos requisitos elementares de Goldberg: 'O que todo cientista da computação deve saber Sobre a aritmética de ponto flutuante ' (mais ou menos' o livro sagrado 'da matemática fp), afirmava:' é importante certificar-se de que seu uso seja transparente para o usuário. Por exemplo, em uma calculadora, se a representação interna de um valor exibido não for arredondada com a mesma precisão do visor, o resultado de outras operações dependerá dos dígitos ocultos e parecerá imprevisível para o usuário '(o problema não é limitado ao Excel, por exemplo, o LibreOffice calc atua de forma semelhante).

Erro de arredondamento

Os cálculos do usuário devem ser cuidadosamente organizados para garantir que o erro de arredondamento não se torne um problema. Um exemplo ocorre na resolução de uma equação quadrática :

${\ displaystyle ax ^ {2} + bx + c = 0 \.}$

As soluções (as raízes) desta equação são determinadas exatamente pela fórmula quadrática :

${\ displaystyle x = {\ frac {-b \ pm {\ sqrt {b ^ {2} -4ac}}} {2a}}.}$

Quando uma dessas raízes é muito grande em relação à outra, ou seja, quando a raiz quadrada está próxima do valor b , a avaliação da raiz correspondente à subtração dos dois termos torna-se muito imprecisa devido ao arredondamento (cancelamento? )

É possível determinar o erro de arredondamento usando a fórmula da série de Taylor para a raiz quadrada:

${\ displaystyle {\ sqrt {b ^ {2} -4ac}} = b \ {\ sqrt {1 - {\ frac {4ac} {b ^ {2}}}}} \ approx b \ left (1- { \ frac {2ac} {b ^ {2}}} + {\ frac {2a ^ {2} c ^ {2}} {b ^ {4}}} + \ cdots \ right).}$

Consequentemente,

${\ displaystyle b - {\ sqrt {b ^ {2} -4ac}} \ approx b \ left ({\ frac {2ac} {b ^ {2}}} - {\ frac {2a ^ {2} c ^ {2}} {b ^ {4}}} + \ cdots \ direita),}$

indicando que, conforme b se torna maior, o primeiro termo sobrevivente, digamos ε:

${\ displaystyle \ varepsilon = {\ frac {2ac} {b}},}$

fica cada vez menor. Os números para be a raiz quadrada tornam-se quase os mesmos, e a diferença torna-se pequena:

${\ displaystyle b - {\ sqrt {b ^ {2} -4ac}} \ approx b-b + \ varejpsilon.}$

Nessas circunstâncias, todos os algarismos significativos vão para expressar b . Por exemplo, se a precisão é de 15 algarismos e esses dois números, be a raiz quadrada, são iguais a 15 algarismos, a diferença será zero em vez da diferença ε.

Uma melhor precisão pode ser obtida a partir de uma abordagem diferente, descrita abaixo. Se denotarmos as duas raízes por r  ₁ e r  ₂ , a equação quadrática pode ser escrita:

${\ displaystyle \ left (x-r_ {1} \ right) \ left (x-r_ {2} \ right) = x ^ {2} - \ left (r_ {1} + r_ {2} \ right) x + r_ {1} \ r_ {2} = 0.}$

Quando a raiz r  ₁ >> r  ₂ , a soma ( r  ₁ + r  ₂  ) ≈ r  ₁ e a comparação das duas formas mostra aproximadamente:

${\ displaystyle r_ {1} \ approx - {\ frac {b} {a}},}$

enquanto

${\ displaystyle r_ {1} \ r_ {2} = {\ frac {c} {a}}.}$

Assim, encontramos a forma aproximada:

${\ displaystyle r_ {2} = {\ frac {c} {a \ r_ {1}}} \ approx - {\ frac {c} {b}}.}$

Esses resultados não estão sujeitos a erros de arredondamento, mas não são precisos, a menos que b ² seja grande em comparação com ac .

Gráfico do Excel da diferença entre duas avaliações da menor raiz de uma quadrática: avaliação direta usando a fórmula quadrática (precisa em b menor ) e uma aproximação para raízes amplamente espaçadas (precisa para b maior ). A diferença atinge um mínimo nos pontos grandes e o arredondamento causa rabiscos nas curvas além desse mínimo.

O resultado final é que, ao fazer esse cálculo usando o Excel, à medida que as raízes se tornam mais distantes em valor, o método de cálculo terá que mudar da avaliação direta da fórmula quadrática para algum outro método, a fim de limitar o erro de arredondamento. Os métodos de ponto para alternar variam de acordo com o tamanho dos coeficientes a e b .

Na figura, o Excel é usado para encontrar a menor raiz da equação quadrática x ²  +  bx  +  c  = 0 para c  = 4 e  c  = 4 × 10 ⁵ . A diferença entre a avaliação direta usando a fórmula quadrática e a aproximação descrita acima para raízes muito espaçadas é plotada vs. b . Inicialmente, a diferença entre os métodos diminui porque o método da raiz amplamente espaçada se torna mais preciso em valores b maiores . No entanto, além de algum valor b, a diferença aumenta porque a fórmula quadrática (boa para valores b menores ) se torna pior devido ao arredondamento, enquanto o método da raiz amplamente espaçada (bom para valores b grandes ) continua a melhorar. O ponto para mudar os métodos é indicado por pontos grandes e é maior para valores c maiores . Em valores b grandes , a curva inclinada para cima é o erro de arredondamento do Excel na fórmula quadrática, cujo comportamento errático faz com que as curvas se embaralhem.

Um campo diferente onde a precisão é um problema é a área de computação numérica de integrais e a solução de equações diferenciais . Os exemplos são a regra de Simpson , o método de Runge – Kutta e o algoritmo de Numerov para a equação de Schrödinger . Usando o Visual Basic for Applications, qualquer um desses métodos pode ser implementado no Excel. Os métodos numéricos usam uma grade onde as funções são avaliadas. As funções podem ser interpoladas entre pontos de grade ou extrapoladas para localizar pontos de grade adjacentes. Essas fórmulas envolvem comparações de valores adjacentes. Se a grade for espaçada muito finamente, ocorrerá um erro de arredondamento e, quanto menor a precisão usada, pior será o erro de arredondamento. Se espaçados amplamente, a precisão será prejudicada. Se o procedimento numérico for considerado um sistema de feedback , esse ruído de cálculo pode ser visto como um sinal aplicado ao sistema, o que levará à instabilidade, a menos que o sistema seja cuidadosamente projetado.

Precisão no VBA

Embora o Excel funcione nominalmente com números de 8 bytes por padrão, o VBA tem uma variedade de tipos de dados. O tipo de dados Double tem 8 bytes, o tipo de dados Inteiro tem 2 bytes e o tipo de dados Variant de 16 bytes de uso geral pode ser convertido em um tipo de dados Decimal de 12 bytes usando a função de conversão VBA CDec . A escolha de tipos de variáveis ​​em um cálculo VBA envolve a consideração dos requisitos de armazenamento, precisão e velocidade.

Referências