Descrição dos melhoramentos nas funções estatísticas no Excel 2003 e no Excel 2004 para Mac

Este artigo descreve seis categorias dos melhoramentos às funções estatísticas no Microsoft Office Excel 2003, orienta (leitor) para artigos mais específicos e permite, pelo menos, algumas avaliação do efeito de com versões anteriores do Microsoft Excel quando funções foram melhoradas para o Excel 2003.

Microsoft Excel 2004 para Mac informações

As funções estatísticas no Excel 2004 para Mac foram actualizadas utilizando os algoritmos que foram utilizados para actualizar as funções de estatísticas do Microsoft Office Excel 2003. As informações neste artigo descreve como funciona a uma função ou como uma função foi modificada para o Excel 2003 também se aplicam Excel 2004 para Mac.

Para o Excel 2003, melhoramentos efectuados nas seguintes áreas de seis:

1. PROJ.LIN e funções relacionadas.
2. DIST.NORMP e funções relacionadas.
3. Funções que envolvem somas dos quadrados (por exemplo, VAR e DESVPAD).
4. Distribuição contínua inverso funções (por exemplo, INV.CHI, INVF, INV.NORMP entre INVT).
5. Funções de distribuições discretas (por exemplo, DISTRBINOM e POISSON).
6. Gerador de números aleatórios (aleatório).

Secções separadas abordar cada uma destas seis áreas. Em áreas três, quatro e cinco, existe um tema comum para melhoramentos para cada uma das várias funções. Nas áreas de uma a quatro, melhoramentos de uma função teve efeitos útil no desempenho de outras funções essencialmente chamá-lo.

Porque foram efectuadas melhorias para o Excel 2003, poderá questionar inevitavelmente sobre o risco de (e magnitude) IMPRECISÕES nas funções estatísticas nas versões anteriores do Excel. Estes problemas irão ser abordados brevemente cada uma das seis secções. As áreas de seis estão ordenadas na percepção do autor da respectiva importância. Durante a maioria dos utilizadores não que seja menos bits alarmado pelo desempenho no Microsoft Excel 2002 e versões anteriores, problemas com PROJ.LIN (área 1) é muito provável a superfície de problemas com DISTRBINOM (área de cinco) ou aleatório (área seis).

Existe um shortcoming no Excel 2002 e versões anteriores que tenha sido identificado e não fixo para o Excel 2003. A aplicação prototypical da ferramenta do ferramentas de análise de teste-t: par de duas amostras para médias é um local onde dados incluem medidas no mesmo conjunto de assuntos antes e depois um tratamento (por exemplo, ponderações antes e depois de um programa de Dieta). Esta ferramenta não irá determinar se existem números desiguais de em falta antes e depois de observações;-irá calcular respostas incorrectas se existe faltam Observações e o número de em falta antes e depois observações são iguais. A menos que pode garantir que existem não observações em falta, a função TESTET no Excel é preferida (porque este processa correctamente observações em falta).

Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base: Duas referências principais que apontar IMPRECISÕES numéricos no Microsoft Excel 97 são papéis por Knusel (consulte a nota 1) para áreas de dois, quatro e cinco e McCullough e Wilson (consulte nota dois) para as áreas um três e seis. Melhoramentos para as funções estatísticas no Excel no Microsoft Excel 2000 e no Microsoft Excel 2002 foram relativamente pequenos.

PROJ.LIN e funções relacionadas

Dois principais melhoramentos foram efectuados ao código de PROJ.LIN. A primeira substituído uma fórmula inadequada para soma total de quadrados em casos em que o terceiro argumento de PROJ.LIN por fosse FALSO indicando que não pretende PROJ.LIN para modelar uma intercepção (ou constante). Na prática, esta opção é seleccionada de uma pequena proporção de incidentes. Ao chamar LINEST(known_y's, known_x's, FALSE, TRUE) devolve uma matriz com cinco linhas que contêm informações detalhadas (estatística f, r ao quadrado, regressão e residual ou erro somas dos quadrados), a fórmula inadequada em versões anteriores do Excel conduziu a resultados incorrectos em última três linhas da matriz de saída. Regressão coeficientes e os respectivos erros padrão não foram afectados. Esta fórmula incorrecta pode resultar em negativo r ao quadrado e negativos regressão soma dos quadrados. Isto foi corrigido. O artigo PROJ.LIN sugere uma solução para o Excel 2002 e versões anteriores para gerar valores correctos de saídas de última três linhas da matriz de saída. Este era um erro grave que resulta nestes últimos três linhas sempre estavam incorrectas quando PROJ.LIN foi chamado com o terceiro argumento definido como FALSE.

Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:

Notas

• Knusel, l. on a precisão de distribuição estatísticas no Microsoft Excel 97 , utilizaria estatísticas e análise de dados, 26, 377-375, 1998.
• McCullough, B.D. & B. Wilson, na exactidão dos procedimentos estatísticas no Microsoft Excel 97 , utilizaria estatísticas e análise de dados, 31, 37-27, 1999.

McCullough e Wilson (McC e W) apontado (correctamente) que o Excel 97 foi e minúsculas para problemas de colunas predictor collinear (ou quase collinear). Um conjunto de colunas predictor é collinear se um deles pode ser eliminado sem afectar o goodness do ajuste do modelo de regressão. Por outras palavras, as informações incluídas numa coluna eliminada não adiciona nenhum valor porque a coluna eliminada pode ser recriada a partir das restantes colunas. Exemplos de teste em McC e W incluído co-linearidade e junto co-linearidade. Utilizar uma abordagem de solução de problemas de co-linearidade ignorado conduziu a resultados incorrectos quando co-linearidade estava presente. Esses resultados incluído um reduzido número de dígitos significativos de precisão na regressão coeficientes e número de graus de liberdade inadequado. Para o Excel 2003, a abordagem de solução foi alterada para utilizar QR decomposição. Os resultados são muito melhorados níveis de precisão notoriously difícil casos de teste são utilizados por McC e t. Co-linearidade é abordada no artigo no PROJ.LIN; o artigo também fornece um exemplo pequeno para andar pelo algoritmo QR decomposição. Este deficiência em todas as versões do Excel anteriores ao Excel 2003 tem um efeito grave apenas quando existem predictors collinear. Um caso comum onde co-linearidade é garantido que não ocorrem é se existe uma coluna única predictor e nem todos os valores dessa coluna são os mesmos.

PROJ.LIN melhorar tem um efeito boa sobre funções chamá-lo: PROJ.log tendência e crescimento em conjunto com a regressão linear ferramenta no Analysis ToolPak. A ferramenta de criação de gráficos no Excel permite-lhe ajustar uma linha recta a um desenho de pontos de dados utilizando uma ferramenta de regressão linear. Infelizmente, capacidades de regressão linear na ferramenta de criação de gráficos não foram actualizadas no Excel 2003.

Em resumo, se utilizar uma versão do Excel anterior ao Excel 2003, deve rejeitar resultados PROJ.LIN detalhados das últimas três linhas da tabela cinco linha saída PROJ.LIN se PROJ.LIN foi chamado com o terceiro argumento definido como FALSE. Também ter em consideração as hipóteses de co-linearidade; para a maior parte dos conjuntos de dados, co-linearidade não deve ser um problema.

DIST.NORMP e funções relacionadas

NORMSDIST(z) têm de ser avaliadas utilizando um procedimento de aproximação. Versões anteriores do Excel utilizavam o mesmo procedimento para todos os valores de z. Para o Excel 2003, são utilizados dois approximations diferentes: uma para |z| menor ou igual a cinco e um segundo |z| maior do que cinco. Os procedimentos de novos duas são cada mais preciso do que o procedimento anterior para o intervalo que são aplicadas. Em versões anteriores do Excel, exactidão deteriorates de caudas da distribuição paragem três algarismos significativos para z = 4, conforme comunicado no papel do Knusel. Além disso, na Vizinhança de z = 1.2, DIST.NORMP produz apenas seis algarismos significativos. No entanto, na prática, isto é provável que seja suficiente para a maioria dos utilizadores.

Código do Excel 2003 proporciona precisão decimal, pelo menos, quinze para qualquer z. Isto significa quinze algarismos de z = 1.2 e (devido à esquerda zeros) dez algarismos significativos para z = 4. Melhorar a exactidão de DIST.NORMP tem o efeito secundário útil de também melhorar as seguintes funções que dependam dele: Int.confiança, INVLOG, DIST.NORMALLOG, DIST.NORM, INV.NORM, INV.NORMP e TESTEZ. Código Int.confiança, DIST.NORMALLOG, DIST.NORM e TESTEZ não foi revisto; precisão de cada um deles melhorada porque cada, essencialmente chama DIST.NORMP uma ou mais vezes e utiliza os resultados destas chamadas em seus cálculos. Precisão melhorada do DIST.NORMP também benefited INVLOG, INV.NORM e INV.NORMP. Uma vez que estes são distribuição contínua inverso funções (Consulte área quatro abaixo), benefited também de melhorias de procura que estavam associadas essas funções. As melhorias de procura na área quatro descritos para outras funções inverso foram implementadas para estas três funções inverso no Excel 2002.

Em resumo, se utilizar o Excel 2002 e versões anterior, deve estar satisfeito com DIST.NORMP. No entanto, se tem de ter valores NORMSDIST(z) altamente precisos de z longe de 0 (tal como |z| maior ou igual a quatro), Excel 2003 poderá ser necessário. NORMSDIST(-4) = 0.0000316712; versões anteriores seria precisas apenas até como 0.0000317. Pode esperar que "sobre 0.00003" ou "3 cerca hipóteses de 100.000" será provavelmente uma resposta suficientemente precisa para muitos utilizadores e versões anteriores executar isto para duas casas decimais mais.

Funções que envolvam somas dos quadrados

McCullough e Wilson indicam que o Excel parece utilizar uma "Calculadora fórmula" para calcular VAR. Os respectivos observação foi correcta e pode ser expandida para muitas (mas curiously, não todas) funções que calculam a soma ao quadrado desvios sobre uma média. A fórmula Calculadora pode ser executada num único passo nos dados. A velocidade resultante utilizaria foi provavelmente a principal razão para utilizá-lo em versões anteriores do Excel. A fórmula alternativa, implementada para o Excel 2003, requer duas vezes. VAR fórmula Calculadora conta o número de observações, soma os quadrados de todas as observações e soma de observações. Aqui, pode calcular:O procedimento alternativo conta o número de observações e a soma de observações sobre o primeiro passo. Isto-pode calcular a média como soma de observações dividido pelo número de observações. Na segunda fase, o calcula o procedimento:Com qualquer computador abordagem, VAR é calculado dividindo o resultado por número de observações ? 1; VARP é calculado dividindo o resultado por número de observações.

Com infinitamente precisa aritmética, ambos os procedimentos produzir os mesmos resultados. No entanto, devido a precisão finita do Excel, a fórmula de Calculadora é mais passível de arredondar desactivar erros. Em textos em calcular estatísticas, a fórmula Calculadora geralmente é apresentada como um exemplo de como não calcular o desvio. Com a folha de cálculo que acompanha o artigo sobre VAR, pode fazer experiências para avaliar a extensão que redondas desactivar erros em versões anteriores do Excel podem colocar problemas. Verá que ocorrer problemas quando existem muitos algarismos significativos nos dados mas muito pouca diferença entre valores. Pode esperar que para a maior parte dos utilizadores, tal round desactivar erros não estão provavelmente problemático no exercício. No entanto, rever várias funções para substituir o procedimento de dois passos para a fórmula Calculadora foi compensador porque um procedimento desactualizado e discredited é substituído por um adequado. Também afectados muitas funções.

DEVSQ também calcula a soma dos desvios quadrados sobre uma média. DEVSQ sempre foi implementada utilizando o procedimento de dois passos. Assim, é outra alternativa para o processamento VAR(data) calcular DEVSQ(data) / (COUNT(data) ? 1). Isto daria os mesmos resultados em todas as versões do Excel. Se estão a utilizar o Excel 2002 ou versões anteriores, pode calcular tanto VAR DEVSQ(data) / (COUNT(data) ? 1) para ver quanto os respectivos resultados diferem. A diferença é essencialmente a jogada desactivar erro VAR. Outras funções que requerem uma soma dos desvios quadrados sobre uma média e sempre que tiver utilizado o procedimento de dois passos são CORREL e COVAR. PEARSON e CORREL ambos calcular o coeficiente de correlação do momento do produto Pearson. Ambos produzir os mesmos resultados no Excel 2003. Em versões anteriores do Excel, PEARSON é implementada com o algoritmo de uma só vez.

Muitas funções envolvem a soma dos desvios quadrados sobre uma média. O procedimento de dois passos foi implementado para cada um dos seguintes procedimentos: VAR, VARA, VARP, VARPA, DESVPAD, DESVPADA, DESVPADP, DESVPADPA, PEARSON, declive e EPADYX. Incluem outras funções que foram melhoradas porque essencialmente invocam uma das funções na lista anterior: previsão, interceptar, RQUAD, TESTET e TESTEZ (quando o utilizador omite o desvio-padrão dos argumentos). No Analysis ToolPak, cada uma das três ferramentas ANOVA foi melhorada, substituindo a fórmula Calculadora pelo procedimento de dois passos. Também existem versões do VAR, VARP, DESVPAD e DESVPADP utilizados em tabelas dinâmicas e consolidação de dados. Existe um único artigo sobre as funções BDVAR, BDVARP, BDDESVPAD e BDDESVPADP.

Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:

Todos estes também foram actualizados.

Em resumo, se utilizar uma versão anterior do Excel, tem de ser preocupado arredondar erros em casos onde os dados contêm muitos algarismos significativos mas o desvio é pequeno. O artigo sobre VAR apresenta exemplos contrived este; pode esperar que estes problemas arredondar não naturalmente ocorrem frequentemente em dados reais.

Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na Microsoft Knowledge Base:

Funções de inverso da distribuição contínuo

Estas são todas as funções cujo nome termina em "INV". Todos utilizarem procura binária para convergir num valor a devolver o utilizador. Assim precisão de, INV.CHI, INV.NORMP e outras funções depende de dois factores: precisão das distribuições (por exemplo, DIST.CHI e DIST.NORMP) e refinamento do processo de procura binário subjacentes.

Para o Excel 2002, o processo de binários de procura foi melhorado no caso, INV.NORMP. Este melhoramento também afecta INV.NORM. INV.NORMP e INV.NORM são funções inverso por muito mais frequentemente utilizadas. A natureza do melhoramento foi a limitar a procura para que continua até o resultado foi preciso para o limite do Excel precisão, em vez de parar anterior se a probabilidade associada o valor localizado na 3 * 10^(-7) da probabilidade correcta. Enquanto esta refinamento do processo de procura melhoradas INV.NORMP, versão do Excel 2002 ainda requerida uma DIST.NORMP mais precisa.

Para o Excel 2003, a procura de binária melhorada utilizada para INV.NORMP para o Excel 2002 foi implementado para cada uma das outras funções "INV". Uma vez que DIST.NORMP também foi melhorada (área duas), a versão do INV.NORMP no Excel 2003 é melhor do que a versão no Excel 2002 e ainda melhor do que no Excel 2000 e versões anteriores do Excel.

Em resumo, estas funções funcionarão também suficiente em versões anteriores do Excel partindo do princípio chamá-los com um valor de probabilidade (por exemplo, p em NORMSINV(p)) é não demasiado próximos 0 ou 1 (tal como numa sobre 10^(-6)).

Funções de distribuições discretas

Knusel apontado que DISTRBINOM, DIST.HIPERGEOM e POISSON não calcular numérico resultados (e devolver # núm!) em determinados casos. CRIT.BINOM e DIST.bin.NEG apresentam um comportamento semelhante. Estas funções produzem resultados precisos sempre que não produzir uma mensagem de erro como # NÚM!.

Estes problemas foram corrigidos. Podem ser visualizados como relativamente minúsculas porque ocorrem apenas para improvável valores de parâmetros de entrada. Por exemplo, DISTRBINOM devolverá respostas correctas no Excel 2002 e versões anteriores, desde que o número de tentativas esteja menos 1,030.

Todas as cinco destas funções foram corrigidos da mesma forma: continuar a utilizar código existente em casos onde entradas para as funções garantida sem problemas utilizaria (tais como menos 1,030 tentativas para DISTRBINOM); mudar para um plano alternativo problematical casos. Para cada uma das cinco funções, este método utiliza um processo que permite a avaliação da probabilidade que pretende sem ter de avaliar um coeficiente combinatorial como COMBIN(1030, 515) ou um factorial de um grande número como FACT(200). Cada um dos casos, o plano alternativo tem a mesma abordagem geral.

Para obter mais informações, clique números de artigo que se seguem para visualizar os artigos na base de dados de conhecimento da Microsoft:

Em resumo, utilizadores de versões anteriores do Excel deverão não ser troubled exercício por deficiencies numéricos uma vez que valores de entrada que causa o valor de erro # NÚM! os resultados são extremos. Além disso, a garantia que, se receber um resultado diferente de uma mensagem de erro, o resultado é exacto. Arredondar erro não é um problema aqui.

Gerador de números aleatórios

Existem testes padrão de aleatoriedade para uma cadeia de números aleatórios. Sequência ?s aleatório de números aleatórios se repetiu com uma frequência demasiado pequena para passar estes testes. Foi implementado um algoritmo melhorado que passa todos os testes padrão de aleatoriedade.

A possibilidade de um efeito de prática grave nos dados aleatórios, o gerador de números aleatório no Excel 2002 e versões anteriores é mínima. Por exemplo, tem de ter uma sequência longa de números aleatórios (tal como 1 milhão) antes do comportamento repetitivo teria um efeito de grave sobre os resultados.

Ainda assim, uma vez que o algoritmo de substituição facilmente foi implementado, foi compensador para tentar efectuar melhoramentos.

Em resumo, se utilizar uma versão anterior do Excel, não se preocupe com a qualidade de números pseudo-aleatória gerados pelo aleatório, excepto se utilizar muitos números aleatórios.

Conclusões

Este artigo de descrição geral explica seis categorias de funções que foram estabelecidas com base no inadequacies nas funções de estatísticas de Excel que foram indicadas na técnica Knusel McCullough e Wilson. Foram efectuadas melhorias às funções em cada categoria. Os leitores são aconselhados a consulte artigos sobre as funções individuais para obter mais detalhes.

Este artigo tem avisado de utilizadores do Excel 2002 e versões anteriores para evitar utilizando os resultados das últimas três linhas da tabela de resultados para LINEST(known_y's, known_x's, FALSE, TRUE). Os utilizadores de todas as versões não devem utilizar ferramenta o ferramentas de análise teste-t: par de duas amostras para médias se existirem dados em falta. Estes dois casos devolvem resultados incorrectos independentemente dos valores de dados.

Noutros casos, é difícil medir a extensão que IMPRECISÕES numéricos no Excel 2002 e versões anteriores irão afectar os utilizadores. Para funções de onde fornecer argumentos numéricos, grau de inaccuracy normalmente depende do valor destes argumentos (por exemplo, DISTRBINOM com 1.500 tentativas ou NORMSDIST(-7)). Nestes casos esses argumentos normalmente têm de ser "extremos" antes um risco de graves problemas numéricos. Para funções onde tem de fornecer um ou mais intervalos de dados, tal como qualquer função que envolvam somas dos quadrados (VAR, DESVPAD, declive) valores de dados também têm de ser "extremos" de um sentido com muitos algarismos significativos e pequena variação antes de arredondar problemas tem um efeito. Para PROJ.LIN, tem de estar atento a possibilidade de co-linearidade.

Talvez nunca irá notar uma diferença quando uma folha de cálculo que é criada no Excel 2002 ou versões anteriores é recalculada no Excel 2003. No entanto, foi importante melhorar funções cuja precisão numérica foi encontrado não deve para ser suficientes nos testes foram concebidos para investigar a capacidade das funções para processar entradas conhecidas por especialistas na comunidade estatísticas utilizaria para esticar essas funções para os respectivos limites. Quando as funções foram originalmente adicionadas ao Excel, ninguém pode prever futuras utilizações. Por exemplo, Six Sigma técnicas não estivessem na utilização frequente. Agora, não poderá devolver valores incorrectos de NORMSDIST(-6) ou NORMSDIST(6). Deve, melhoramentos numéricos para o Excel 2003 fará com que estas funções estatísticas apropriado para utilização futura imprevista.