Descrição dos efeitos das funções estatísticas aprimoradas para as Ferramentas de Análise no Excel

Esse artigo descreve o efeito de aperfeiçoamentos numéricos nas funções estatísticas do Microsoft Office Excel 2003 e de versões posteriores do Excel em ferramentas ATP. A maioria das ferramentas ATP chamam as funções estatísticas do Excel no processo de cálculo de resultados. Em muitos casos, esse artigo serve como um indicador para artigos sobre funções estatísticas individuais do Excel. Além disso, a discussão de futuros aperfeiçoamentos úteis está incluída para algumas ferramentas ATP.

Informações do Microsoft Excel 2004 for Mac

As funções estatísticas no Excel 2004 for Mac foram atualizadas usando os mesmos algoritmos usados para atualizar as funções estatísticas no Microsoft Office Excel 2003 em versões posteriores do Excel. Qualquer informação neste artigo que descreva como a função funciona ou como uma função foi modificada para o Excel 2003 e para versões posteriores do Excel também se aplica ao Excel 2004 for Mac.

O código para ATP não foi editado diretamente, exceto para introduzir aperfeiçoamentos nas três ferramentas ATP ANOVA.

Para diversas ferramentas ATP, o desempenho numérico foi aprimorado para o Excel 2003 e para versões posteriores do Excel porque as ferramentas chamam uma função estatística do Excel que foi aprimorada para o Excel 2003 e para versões posteriores do Excel. Nos casos em que os resultados são diferentes para as versões anteriores e versões posteriores do Excel, os valores para o Excel 2003 e para versões posteriores do Excel são mais precisos.

A maioria dos usuários não notará uma diferença nos resultados entre as diferentes versões do Excel. Isso ocorre porque as diferenças geralmente são causadas por erros de arredondamento que são significativos apenas em casos extremos. Entretanto, esse artigo deve primeiro indicar um caso onde as diferenças ocorrem por causa de uma fórmula incorreta no Microsoft Excel 2002 e em versões anteriores do Excel. Evite a ferramenta nestas versões.

Um segundo exemplo envolve uma fórmula incorreta no Excel 2002 e em versões anteriores do Excel que persiste no Excel 2003 e em versões posteriores do Excel. Evite usar a ferramenta ATP neste cenário para todas as versões do Excel.

Primeiro, evite a ferramenta Regressão quando tiver que marcar a caixa de seleção Constante é zero. Isso foi corrigido no Excel 2003 e em versões posteriores do Excel. Não é preciso evitar a ferramenta Regressão quando a caixa de seleção Constante é zero está desmarcada (o caso mais comum em prática).

Segundo, os usuários de todas as versões do Excel devem evitar a Ferramenta de Análise Test-t: Duas amostras em par para médias a menos que você possa garantir que não há observações de dados incorretas. A ferramenta dará respostas inapropriadas (ou nenhuma resposta) se houver uma ou mais observações ausentes.

Para obter mais informações sobre a Ferramenta de Análise Test-t de Duas Amostras Iguais, clique no seguinte número para ler o artigo na Base de Dados de Conhecimento Microsoft: Se você deseja usar essa ferramenta e se houver dados ausentes (ou se tiver a chance de haver dados ausentes), a função TTEST no Excel irá manipulá-la corretamente.

Seções separadas são fornecidas posteriormente neste artigo para ferramentas ATP (Ferramentas de Análise) individuais. As ferramentas que não estão listadas não foram afetadas pelos aperfeiçoamentos no Excel 2003 e em versões posteriores do Excel.

ANOVA: Fator Único, Fator Duplo com Replicação e Fator Duplo sem Replicação

Cada uma dessas três ferramentas ANOVA foi regravada para atualizar o procedimento computacional para um algoritmo em dois passos que é mais eficiente numericamente. Esses aperfeiçoamentos são semelhantes aos aperfeiçoamentos nas funções estatísticas que computam somas de desvios quadrados sobre um meio (por exemplo: VAR, STDEV, SLOPE, PEARSON).

Para obter informações adicionais sobre ATP ANOVA, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento da Microsoft:

Correlação

Esta ferramenta não foi alterada. Entretanto, existe uma pequena diferença entre a ferramenta Correlação e a ferramenta Covariância que persiste em todas as versões do Excel. A ferramenta Correlação retorna uma tabela de correlação triangular mais baixa com 1's na diagonal e correlações fora da diagonal. A ferramenta usa CORREL para computar entradas fora da diagonal e preenche essas entradas com o valor retornado pelo CORREL. (Portanto, se alguma entrada de dados for alterada, nenhuma entrada na tabela será alterada. Contraste este comportamento com o comportamento de Covariância).

Covariância

Esta ferramenta retorna uma tabela de covariância triangular mais baixa com variações na diagonal e covariâncias fora da diagonal. As células na diagonal contêm uma fórmula "=VARP(...)" para que caso uma entrada de dados seja alterada, o resultado na tabela também seja alterado. VARP foi aprimorado para o Office Excel 2003 e para versões posteriores do Excel.

Para obter informações adicionais sobre o VARP, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft: A ferramenta Covariância usa COVAR para computar entradas fora da diagonal e preenche essas entradas com o valor retornado pelo COVAR. Portanto, se uma entrada de dados for alterada, as entradas fora da diagonal não serão alteradas.

Estatísticas descritivas

Esta ferramenta chama as funções estatísticas do Excel para tudo que ela computa. Como o VAR e o STDEV são aprimorados para o Excel 2003 e para versões posteriores do Excel, diferentes valores são possíveis devido aos erros de arredondamento em casos extremos.

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

Teste F de Duas Amostras para Variâncias

Assim como a ferramenta Estatísticas descritivas, essa ferramenta chama o VAR. Novamente, diferentes valores são possíveis devido aos erros de arredondamento em casos extremos.

Geração de números aleatórios

Essa ferramenta preenche um intervalo com observações aleatórias. Os valores dessas observações são colocados diretamente nas células para que esses valores de célula não sejam recomputados e substituídos por novas observações quando a planilha for recalculada. A função RAND interna no Excel, por outro lado, substitui os números aleatórios existentes por novos sempre que a planilha é recalculada. É possível usar a função RAND para preservar os valores. Para fazer isso, copie os resultados em um intervalo e use o comando Colar especial para colar os valores no mesmo intervalo.

A ferramenta RNG (Geração de números aleatórios) também produz observações aleatórias a partir de várias distribuições de probabilidade onde RAND corresponde à única opção na ferramenta: Uniforme com intervalo entre 0 e 1. Este artigo descreve como combinar RAND com as funções estatísticas no Excel para gerar tais observações.

Portanto, em termos de funcionalidade, é possível emular a ferramenta de números aleatórios ATP usando RAND e alguma criatividade. Às vezes, isto é útil, particularmente quando muitos números aleatórios são desejados.

Para o Excel 2002 e posterior, ambos RNG e RAND são conhecidos pelo fraco desempenho em testes padrão de aleatoriedade. O desempenho era fraco por causa da duração de um ciclo, pois a seqüência de repetição de números pseudo-aleatórios era muito curta. Isto é um problema apenas quando muitos números aleatórios são solicitados.

A função RAND foi aprimorada para o Excel 2003 e para versões posteriores do Excel de modo que RAND agora passa em todos os testes padrão. A seqüência de números aleatórios da função RAND irá começar a se repetir após a geração de mais de 1 trilhão de números.

Para obter informações adicionais sobre RAND, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft : Entretanto, o RNG separado do ATP não foi atualizado. Como na versão da função RAND no Excel 2002 e em versões anteriores do Excel, o RNG separado do ATP é conhecido por seu desempenho fraco em testes padrão de aleatoriedade e por ter um ciclo de repetição curto. Isso terá implicações negativas apenas se você solicitar uma seqüência muito longa de números aleatórios (por exemplo, 1 milhão).

A ferramenta RNG fornece observações aleatórias a partir de várias distribuições de probabilidade e do Uniform[0,1], a distribuição usada para saída de números aleatórios através de RAND. A ferramenta ATP primeiro desenha um número aleatório Uniform[0,1] (ou mais de um número) e converte a resposta em um observação a partir de uma das seguintes distribuições específicas. Para o benefício daqueles que preferem RAND pois irão gerar mais observações, esse artigo sugere fórmulas que usam RAND na tabela abaixo. Seguindo a tabela, existem alguns comentários de advertência sobre o caso de distribuição normal do ATP.Existem duas razões pelas quais você pode preferir RAND e a fórmula nessa tabela em vez da ferramenta de números aleatórios ATP no caso Normal(mu, sigma). Primeira, RAND é um gerador de número aleatório do Uniform[0,1] do que do ATP. Segunda, a ferramenta ATP não chama a função NORMINV do Excel, em vez disso ela tem sua própria versão interna de distribuição normal inversa. Ela não é tão precisa quanto a versão do NORMINV no Excel 2003 e em versões posteriores do Excel. É inferior tanto na precisão da aproximação de distribuição normal usada (o Excel usa a função NORMSDIST muito mais aprimorada) quanto no refinamento da pesquisa binária (o Excel executa isso mais adiante para garantir um valor mais próximo ao argumento de probabilidade do NORMINV). Em poucas palavras, usar o ATP neste caso não é vantajoso para o Excel 2003 e para versões posteriores do Excel para obter aperfeiçoamentos nas funções NORMINV, NORMSDIST e RAND.

Para obter observações de uma distribuição discreta, considere que os valores estão na coluna B e que suas probabilidades estão na coluna C. Uma deve querer preencher cada linha da coluna A com a probabilidade de observar um valor estritamente menor que o valor na coluna B naquela linha. Supondo que há 10 valores, considere que esses dados estão nas células A1:C10. Então, como A1 contém a probabilidade de observar um valor estritamente menor que o primeiro valor, ela deve ser definida como 0. É possível usar VLOOKUP(RAND(), A1:C10, 2); o quarto argumento para VLOOKUP é opcional e deve ser omitido ou definido como VERDADEIRO. O "2" significa que você deseja retornar o valor na segunda coluna (coluna B neste exemplo).

O ATP usa uma adaptação do método de geração de observações de Poisson em Press, W.H., S.A. Teukolsky, W. T. Vetterling e B.P. Flannery, Numerical Recipes in C, The Art of Scientific Computing, 2nd ed., Cambridge University Press, 1992, pp. 293-295. Existem dois métodos para tirar vantagem fácil das funções existentes do Excel.

O primeiro usa a observação de que uma variável aleatória de POISSON com meio m tem uma distribuição bem aproximada por um BINOMIAL(n, m/n) para n grande. Você pode então chamar CRITBINOM(n, m/n, RAND()). A escolha de n depende de m; n é maior que 1,000 vezes m deve ser grande o suficiente.

O segundo relata a distribuição de POISSON para o Exponencial. Se ocorrerem eventos de acordo com um processo de POISSON na taxa m por tempo de unidade, o tempo entre os eventos terá uma distribuição de Exponencial com meio 1/m. Para uma observação de POISSON, é possível tirar uma seqüência de observações desta distribuição de Exponencial contar quantas delas ocorrem antes de suas somas excederem 1. Para obter uma observação dessa distribuição de Exponencial, use GAMMAINV(RAND(), 1, 1/m). Esse método deve ser adequado quando m é relativamente próximo a 0.

Regressão

A ferramenta Regressão chama a função LINEST do Excel. O artigo sobre LINEST descreve aperfeiçoamentos extensos para o Excel 2003 e para versões posteriores do Excel.

Para obter informações adicionais sobre LINEST, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft: Se você usar o Excel 2002 ou uma versão anterior do Excel, observe as mesmas duas deficiências da ferramenta Regressão do ATP em LINEST:

• Regressão da Soma dos Quadrados, quadrado r e valores estatísticos estão sempre incorretos para o caso onde a regressão é forçada através da origem.
  
  Para LINEST, isso significa "terceiro argumento definido como FALSO em vez de VERDADEIRO ou de ser omitido". Para a ferramenta ATP, isso significa "caixa de seleção Constante é zero está marcada".
• A função LINEST e a ferramenta ATP são insensíveis aos problemas de colinearidade. O artigo sobre LINEST discute a abordagem computacional para a função LINEST no Excel 2003 e em versões posteriores do Excel desenvolvida para encontrar colinearidade ou quase colinearidade quando houver e para agir apropriadamente.

Ambas as deficiência da função LINEST foram resolvidas no Excel 2003 e em versões posteriores do Excel. O desempenho da ferramenta Regressão do ATP será igualmente aprimorado. Não houve alterações no código da ferramenta, ele foi aprimorado chamando uma função aprimorada do Excel. Este autor considera o aperfeiçoamento na função LINEST o mais importante dos aperfeiçoamentos da função estatística.

A tabela a seguir mostra a saída da ferramenta Regressão para versões anteriores e posteriores do Excel com a caixa de seleção Constante é zero marcada. Ela ilustra a primeira deficiência mencionada anteriormente. Nas versões anteriores do Excel, a soma dos quadrados da regressão é negativo assim como o valor do quadrado R.

Test t: Duas Amostras em Par para médias

Como mencionado anteriormente, evite essa ferramenta se houver alguma chance de ter um ou mais valores de dados ausentes. O aplicativo prototípico desse teste é um experimento com medidas sobre os assuntos Antes e Depois de um tratamento (como os pesos Antes e Depois de uma plano de dieta de 60 dias). Se não estiver faltando observações, a ferramenta irá se comportar bem. Se houver números diferentes de observações Antes e Depois ausentes, uma mensagem de erro será exibida e a ferramenta não computará nada. Se houver observações ausentes e os números da observações Antes e Depois ausentes forem iguais, a ferramenta retornará resposta com vários erros.

O procedimento padrão será remover um assunto dos dados se a medida Antes ou Depois estiver faltando e analisar os dados que contêm apenas os assuntos que têm as medidas Antes e Depois. A função TTEST do Excel manipula os dados ausentes de acordo com este procedimento padrão.

As outras duas ferramentas Test t, Duas Amostras Presumindo Variâncias Equivalentes e Duas Amostras Presumindo Variâncias Diferentes, não compartilham esse defeito.

Test z: Duas Amostras para Média

Esse artigo notou que o caso de distribuição normal da ferramenta de geração de números aleatórios não chama a função NORMSINV (ou mais precisamente, NORMINV chama NORMSINV), mas tem seu próprio procedimento inferior para encontrar valores inversos.

A ferramenta z-Test não chama a função NORMSINV e tira vantagem dos aperfeiçoamentos para o Excel 2003 e para versões posteriores do Excel.

Resultados em versões anteriores do Excel

Existem ferramentas ATP cujo desempenho foi aprimorado para o Excel 2003 e para versões posteriores do Excel porque elas chamam funções estatísticas do Excel que foram aprimoradas para o Excel 2003 e para versões posteriores do Excel. Um desses aperfeiçoamentos para LINEST, quando seu terceiro argumento está definido como FALSO, implica que a ferramenta Regressão do ATP retorna resultados incorretos no Excel 2002 e em versões anteriores do Excel quando a caixa de seleção Constante é zero está marcada. Em outros casos onde as funções do Excel foram aprimoradas, os usuários de versões anteriores não podem notar as diferenças (a maioria dessas diferenças envolve erros de arredondamento em situações extremas).

As três ferramentas ATP ANOVA foram aprimoradas ao editar o código ATP para substituir um algoritmo mais eficiente numericamente (como no aperfeiçoamento na função VAR do Excel). Os usuários dessas ferramentas em versões anteriores do Excel notam as diferenças apenas em situações extremas.

Aviso aos usuários de todas as versões: evite a ferramenta t-Test: Paired Two Sample for Means se houver a mínima chance de ausência de dados.

Resultados no Excel 2003 e em versões posteriores do Excel

Aperfeiçoamentos significativos foram feitos nas funções estatísticas do Excel. Isso se traduz em aperfeiçoamentos em muitas ferramentas ATP que chamam essas funções. Existe uma ferramenta ATP, a geração de números aleatórios, que não tira vantagem de uma função RAND aprimorada (porque ela é implementada de uma maneira auto-suficiente e não chama RAND). Isso é lastimável, mas ainda mais lastimável é o caso especial de observações aleatórias distribuídas normalmente. A distribuição normal inversa também é implementada de uma forma auto-suficiente e não chama a função NORMSINV muito mais aprimorada.

A tabela a seguir lista as ferramentas ATP e as funções do Excel que elas chamam que foram aprimoradas para o Excel 2003 e para versões posteriores do Excel. Os leitores são direcionados para artigos separados sobre cada função do Excel chamada.Para todas as funções que aparecem nesta tabela, com exceção de LINEST e RAND, é provável que você veja diferenças entre as versões anteriores e as versões posteriores do Excel somente por causa dos erros de arredondamento em situações extremas. A função LINEST foi muito aprimorada, como dito anteriormente. A função RAND também foi aprimorada. Curiosamente, a ferramenta Amostragem chama RAND, mas a ferramenta RNG depende de um gerador auto-suficiente que oferecerá um desempenho inferior quando uma longa seqüência de observações aleatórias for solicitada.

Conclusões

Com exceção das alterações no código ATP para cada uma das três ferramentas ANOVA, o código ATP não foi regravado. Entretanto, ela se beneficia da chamada de funções aprimoradas do Excel, conforme exibido na tabela da ferramenta ATP. Falhas no Test t: O teste Duas Amostras em Par para Médias não foi corrigido para o Excel 2003 ou para versões posteriores do Excel. Os aperfeiçoamentos mais notáveis são os feitos na ferramenta Regressão onde a função LINEST não mais retorna resultados incorretos quando a caixa de seleção Constante é zero está marcada e foi desenvolvida para manipular a colinearidade apropriadamente.