Funções estatísticas do Excel: CRESCIMENTO

Resumo

Este artigo descreve a função CRESCIMENTO no Microsoft Office Excel 2003 e em versões posteriores do Excel, ilustra como a função é utilizada e compara os resultados da função para o Excel 2003 e para versões posteriores do Excel com resultados de CRESCIMENTO em versões anteriores do Excel. O CRESCIMENTO é avaliado ao chamar a função relacionada, PROJ.LIN. As alterações extensas ao PROJ.LINEST para Excel 2003 e versões posteriores do Excel são resumidas e as suas implicações para CRESCIMENTO são anotados.

Informações do Microsoft Excel 2004 para Macintosh

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

Mais Informações

A função CRESCIMENTO(known_y, known_x, new_x, constante) é utilizada para realizar uma análise de regressão onde é ajustada uma curva exponencial. É utilizado um critério com menos quadrados e o CRESCIMENTO tenta encontrar o melhor ajuste nesse critério. Known_y representam dados sobre a "variável dependente" e known_x representam dados numa ou mais "variáveis independentes". O ficheiro de Ajuda GROWTH aborda casos raros em que o segundo ou terceiro argumento pode ser omitido.

Partindo do princípio de que existem variáveis de preditor p, GROWTH chama essencialmente LOGEST. LOGEST ajusta-se a uma equação do formulário:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

Os valores dos coeficientes, b, m1, m2, ..., mp são determinados que dão o melhor ajuste aos dados y.

Se o último argumento "constante" estiver definido como VERDADEIRO, pretende que o modelo de regressão inclua o coeficiente multiplicativo b no modelo de regressão. Se definido como FALSO, b é excluído ao defini-lo essencialmente como 1. O último argumento é opcional; se o argumento for omitido, será interpretado como VERDADEIRO.

Para facilitar a exposição no resto deste artigo, suponha que os dados estão dispostos em colunas para que known_y seja uma coluna de dados y e known_x é uma ou mais colunas de x dados. É claro que as dimensões (comprimentos) de cada uma destas colunas têm de ser iguais. New_x também serão dispostos em colunas e tem de haver o mesmo número de colunas para new_x como para known_x. Todas as nossas observações abaixo são igualmente verdadeiras se os dados não estiverem dispostos em colunas, mas é mais fácil discutir este caso único (mais frequentemente utilizado).

Depois de calcular o modelo de regressão mais adequado (ao chamar essencialmente a função LOGEST do Excel), GROWTH devolve valores previstos associados a new_x.

Este artigo utiliza exemplos para mostrar como o CRESCIMENTO se relaciona com LOGEST e para apontar problemas com LOGEST em versões do Excel anteriores ao Excel 2003 que se traduzem em problemas de CRESCIMENTO. O CRESCIMENTO chama efetivamente LOGEST, executa LOGEST, utiliza coeficientes de regressão na saída LOGEST no cálculo dos valores y previstos associados a cada linha de new_x e apresenta-lhe esta coluna de valores y previstos. Por conseguinte, tem de saber mais sobre os problemas na execução do LOGEST. Quando LOGEST é chamado, por sua vez chama efetivamente PROJ.LINEST. Embora o código para CRESCIMENTO e LOGEST não tenha sido reescrito para o Excel 2003 e para versões posteriores do Excel, foram feitas alterações extensas (e melhorias) no código PROJ.LINEST.

Como complemento a este artigo, o seguinte artigo sobre PROJ.LINEST é altamente recomendado. Contém vários exemplos e problemas de documentos com PROJ.LINEST em versões do Excel anteriores ao Excel 2003.

Para obter mais informações sobre PROJ.LINEST, clique no seguinte número de artigo para ver o artigo na Base de Dados de Conhecimento Microsoft:

828533 Descrição da função PROJ.LIN no Excel 2003 e no Excel 2004 para Mac

O ficheiro de Ajuda PROJ.LINEST, conforme revisto para o Excel 2003, também é recomendado.

O seguinte artigo sobre LOGEST explica como LOGEST interage com PROJ.LINEST. Estes detalhes são omitidos aqui.

Para obter mais informações, clique no seguinte número de artigo para ver o artigo na Base de Dados de Conhecimento Microsoft:

828528 funções estatísticas do Excel: LOGEST

Uma vez que o foco neste artigo está em problemas numéricos em versões do Excel anteriores ao Excel 2003, este artigo não tem muitos exemplos práticos da utilização do CRESCIMENTO. O ficheiro de Ajuda em CRESCIMENTO contém exemplos úteis.

Sintaxe

GROWTH(known_y's, known_x's, new_x's, constant)

Os argumentos, known_y, known_x e new_x têm de ser matrizes ou intervalos de células com dimensões relacionadas. Se known_y é uma coluna por m linhas, known_x é c colunas por m linhas em que c é maior ou igual a uma. C é o número de variáveis de predição; m é o número de pontos de dados. New_x tem de ser colunas c por linhas r em que são maiores ou iguais a uma. (As relações semelhantes em dimensões têm de conter se os dados estiverem dispostos em linhas em vez de colunas.) Constante é um argumento lógico que tem de ser definido como VERDADEIRO ou FALSO (ou 0 ou 1 que o Excel interpreta como FALSO ou VERDADEIRO, respetivamente). Os últimos três argumentos para CRESCIMENTO são todos opcionais; veja o ficheiro de Ajuda GROWTH para obter opções de omitir o segundo argumento, terceiro argumento ou ambos; omitir o quarto argumento é interpretado como VERDADEIRO.

A utilização mais comum de CRESCIMENTO inclui dois intervalos de células que contêm os dados, como CRESCIMENTO(A1:A100, B1:F100, B101:F108, VERDADEIRO). Como normalmente existe mais do que uma variável de predição, o segundo argumento neste exemplo contém múltiplas colunas. Neste exemplo, existem 100 assuntos, um valor variável dependente (known_y) para cada assunto e cinco valores de variáveis dependentes (known_x) para cada assunto. Existem oito assuntos hipotéticos adicionais em que pretende utilizar CRESCIMENTO para calcular valores y previstos.

Exemplo de utilização

É fornecido um exemplo de folha de cálculo do Excel para ilustrar os seguintes conceitos-chave:

• Como o CRESCIMENTO interage com LOGEST
• Problemas que ocorrem com CRESCIMENTO (ou LOGEST e PROJ.º PLANO) devido a known_x em versões do Excel anteriores ao Excel 2003

Para ilustrar a função CRESCIMENTO, crie uma folha de cálculo do Excel em branco, copie a seguinte tabela, selecione a célula A1 na sua folha de cálculo do Excel em branco e, em seguida, cole as entradas para que a tabela seguinte preencha as células A1:K35 na sua folha de cálculo.

Os dados para CRESCIMENTO estão nas células A1:C8. (As entradas nas células D2:D6 não fazem parte dos dados, mas são utilizadas para ilustração abaixo.) Os resultados de CRESCIMENTO para dois modelos diferentes para versões anteriores do Excel e para versões posteriores do Excel são apresentados nas células E10:E16 e I10:116, respetivamente. Os resultados nas células A10:A16 corresponderão à versão do Excel que está a utilizar. Por agora, concentre-se nos resultados do Excel 2003 e das versões posteriores do Excel quando investigar como o CRESCIMENTO chama LOGEST e como o CRESCIMENTO utiliza os resultados LOGEST.

GROWTH e LOGEST podem ser vistos como interagindo nos seguintes passos:

1. Chama-se CRESCIMENTO(known_y, known_x, new_x, constante)
2. CRESCIMENTO chama LOGEST(known_y, known_x, constante, VERDADEIRO)
3. Os coeficientes de regressão desta chamada para LOGEST são obtidos. Estes coeficientes aparecem na primeira linha da tabela de saída LOGEST.
4. Para cada linha de new_x, o valor y previsto é calculado com base nestes coeficientes LOGEST e nos valores do new_x nessa linha.
5. O valor calculado no passo 4 é devolvido na célula adequada para a saída CRESCIMENTO que corresponde à linha desse new_x.

Se CRESCIMENTO for para devolver os resultados adequados, LOGEST tem de gerar resultados adequados no passo 3. Uma vez que a avaliação do LOGEST no passo 3 requer uma chamada para PROJ.LINEST, é essencial que o PROJ.LINEST seja bem comportado. Os problemas com o PROJ.LINEST em versões do Excel anteriores ao Excel 2003 provêm de colunas de predição em linha. (Existem outros problemas com PROJ.LINEST e LOGEST nas versões anteriores do Excel que ocorrem quando o último argumento para CRESCIMENTO está definido como FALSO. No entanto, esses problemas não afetam os resultados do CRESCIMENTO e não são discutidos aqui.)

As colunas preditor (known_x) são collineares se, pelo menos, uma coluna, c, puder ser expressa como uma soma de múltiplos de outras, c1, c2 e outras colunas. A coluna c é frequentemente denominada redundante porque as informações que contém podem ser construídas a partir das colunas c1, c2 e outras colunas. O princípio fundamental na existência de collinearidade é que os resultados não devem ser afetados se uma coluna redundante está incluída nos dados originais ou removida dos dados originais. Uma vez que PROJ.LINEST em versões do Excel anteriores ao Excel 2003 não procuravam a collinearidade, este princípio foi facilmente violado. As colunas preditor são quase collineares se, pelo menos, uma coluna, c, puder ser expressa como quase igual a uma soma de múltiplos de outras, c1, c2 e outras colunas. Neste caso, "quase igual" significa uma pequena soma de desvios quadrados de entradas em c de entradas correspondentes na soma ponderada de c1, c2 e outras colunas. "Muito pequeno" pode ser inferior a 10^(-12), por exemplo.

O primeiro modelo, nas linhas 10 a 12, utiliza as colunas B e C como preditores e pede ao Excel para modelar a constante (último argumento definido como VERDADEIRO). Em seguida, o Excel insere eficazmente uma coluna preditor adicional que se assemelha às células D2:D6. É fácil notar que as entradas na coluna C nas linhas 2 a 6 são exatamente iguais à soma das entradas correspondentes nas colunas B e D. Portanto, existe uma collinearidade presente porque a coluna C é uma soma de múltiplos dos seguintes itens:

• Coluna B
• A coluna adicional de 1s do Excel que é inserida porque o terceiro argumento para LOGEST foi omitido ou VERDADEIRO (o caso "normal")

Isto causa tais problemas numéricos que as versões do Excel anteriores ao Excel 2003 não conseguem calcular os resultados. Por conseguinte, a tabela de saída CRESCIMENTO é preenchida com #NUM!.

O segundo modelo, nas linhas 14 a 16, é um modelo que qualquer versão do Excel consegue processar com êxito. Não existe nenhuma collinearidade e o utilizador volta a pedir ao Excel para modelar a constante. Este modelo está incluído aqui pelos seguintes motivos:

• Em primeiro lugar, é mais típico de casos práticos: que não existe nenhuma collinearidade presente. Estes casos são processados de forma suficiente em todas as versões do Excel. Deve ser reconfortante saber que não é provável que ocorram problemas numéricos no caso prático mais comum se tiver uma versão anterior do Excel.
• Em segundo lugar, este exemplo é utilizado para comparar o comportamento do Excel 2003 e das versões posteriores do Excel nos dois modelos. A maioria dos principais pacotes estatísticos analisa a collinearidade, remove uma coluna que é uma soma de múltiplos de outros do modelo e alerta o utilizador com uma mensagem como "a coluna C depende linearmente de outras colunas preditivas e foi removida da análise".

No Excel 2003 e em versões posteriores do Excel, essa mensagem é transmitida não num alerta ou numa cadeia de texto, mas na tabela de saída LOGEST. O CRESCIMENTO não tem nenhum mecanismo para entregar essa mensagem ao utilizador. Na tabela de saída LOGEST, um coeficiente de regressão que é um, e cujo erro padrão é zero, corresponde a um coeficiente de uma coluna que foi removida do modelo. As tabelas de saída LOGEST estão incluídas nas linhas 23 a 35 correspondentes à saída CRESCIMENTO nas linhas 10 a 16. As entradas nas células I24:I25 mostram uma coluna preditor redundante eliminada. Neste caso, LOGEST optou por remover a coluna C (os coeficientes nas células I24, J24, K24 correspondem às colunas C, B e coluna constante do Excel, respetivamente). Quando existe uma collinearidade presente, qualquer uma das colunas envolvidas pode ser removida e a escolha é arbitrária.

No segundo modelo nas linhas 30 a 35, não existe nenhuma collinearidade e nenhuma coluna removida. Pode ver que os valores y previstos são os mesmos em ambos os modelos. Este problema ocorre porque remover uma coluna redundante que é uma soma de múltiplos de outros não reduz a bondade do ajuste do modelo resultante. Estas colunas são removidas precisamente porque não representam nenhum valor adicionado ao tentar encontrar os melhores quadrados mínimos adequados. Além disso, se examinar a saída LOGEST nas células I23:K35 no Excel 2003 e em versões posteriores do Excel, irá reparar que as últimas três linhas das tabelas de saída são as mesmas. Além disso, as entradas nas células I31:J32 e nas células J24:K25 coincidem. Isto demonstra que os mesmos resultados são obtidos quando a coluna C é incluída no modelo, mas considerado redundante (saída nas células I24:K28) como quando a coluna C foi eliminada antes da execução do LOGEST (saída nas células I31:J35). Isto satisfaz o princípio fundamental na existência de collinearidade.

Nas células A18:C21, a Microsoft utiliza dados do Excel 2003 e de versões posteriores do Excel para ilustrar como CRESCIMENTO aceita a saída LOGEST e calcula os valores y previstos relevantes. Ao examinar as fórmulas nas células A20:A21 e células C20:C21, pode ver como os coeficientes LOGEST são combinados com os dados de new_x nas células B7:C8 para cada um dos dois modelos (utilizando as colunas B, C como preditores; utilizando apenas a coluna B como preditor).

A collinearidade é identificada no LOGEST no Excel 2003 e em versões posteriores do Excel porque LOGEST chama PROJ.LINEST. PROJ.LINEST utiliza uma abordagem diferente para resolver os coeficientes de regressão. Esta abordagem é a Decomposição QR. O artigo PROJ.LINEST contém instruções do algoritmo de Decomposição QR para um pequeno exemplo.

Resumo dos resultados em versões anteriores do Excel

Os resultados de CRESCIMENTO são afetados negativamente em versões do Excel anteriores ao Excel 2003 devido a resultados imprecisos em LOGEST que, por sua vez, decorrem de resultados imprecisos em PROJ.LINEST.

O PROJ.LINEST foi calculado através de uma abordagem que não prestou atenção a problemas de collinearidade. A existência de collinearidade causou erros de arredondamento, erros padrão inadequados de coeficientes de regressão e graus de liberdade inadequados. Por vezes, os problemas de arredondamento são suficientemente graves para que PROJ.LINEST tenha preenchido a tabela de saída com #NUM!. Se, tal como na grande maioria dos casos na prática, puder ter a certeza de que não existem colunas preditoras (ou quase collineares), então PROJ.LINE geralmente forneceria resultados aceitáveis. Por conseguinte, os utilizadores do GROWTH podem ser igualmente tranquilizados se conseguirem ver a ausência de colunas preditores collineares (ou quase collineares).

Resumo dos resultados no Excel 2003 e em versões posteriores do Excel

As melhorias no PROJ.LINEST incluem mudar para o método de Decomposição QR para determinar os coeficientes de regressão. A Decomposição QR tem as seguintes vantagens:

• Melhor estabilidade numérica (geralmente, erros de arredondamento mais pequenos)
• Análise de problemas de collinearidade

Todos os problemas com versões do Excel anteriores ao Excel 2003 ilustradas neste artigo foram corrigidos para o Excel 2003 e para versões posteriores do Excel. Estas melhorias no PROJ.LINEST traduzem-se em melhoramentos em LOGEST e GROWTH.

Conclusões

O desempenho do CRESCIMENTO foi melhorado porque o PROJ.LINEST foi bastante melhorado para o Excel 2003 e para versões posteriores do Excel. As melhorias no PROJ.LINEST também afetam o LOGEST, uma vez que LOGEST é chamado por CRESCIMENTO. Os utilizadores de versões anteriores do Excel devem verificar se as colunas preditoras não são colelineares antes de utilizarem CRESCIMENTO.

Grande parte do material apresentado neste artigo e no artigo PROJ.LINEST pode aparecer inicialmente a alarmar os utilizadores de versões do Excel anteriores ao Excel 2003. No entanto, deve observar-se que a collinearidade é um problema apenas numa pequena percentagem de casos. As versões anteriores do Excel fornecem resultados de CRESCIMENTO aceitáveis quando não existe uma collinearidade.

Felizmente, as melhorias no PROJ.LIN também afetam a ferramenta de regressão linear do Analysis ToolPak (esta ferramenta chama PROJ.LIN) e outras duas funções relacionadas do Excel: LOGEST e TREND.