Compartilhar via

Funções estatísticas do Excel: CRESCIMENTO

  • Aplica-se a: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Excel 2004 for Mac

Resumo

Este artigo descreve a função GROWTH no Excel, ilustra como a função é usada 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, LINEST. Alterações abrangentes no LINEST para Excel 2003 e para versões posteriores do Excel são resumidas e suas implicações para o CRESCIMENTO são observadas.

Informações do Microsoft Excel 2004 para Macintosh

As funções estatísticas no Excel 2004 para Mac foram atualizadas usando os mesmos algoritmos que foram usados para atualizar as funções estatísticas no Excel 2003 e em versões posteriores do Excel. Qualquer informação neste artigo que descreve como uma função funciona 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 GROWTH(known_y's, known_x's, new_x's, constant) é usada para executar uma análise de regressão em que uma curva exponencial é instalada. Um critério de quadrados mínimo é usado, e GROWTH tenta encontrar o melhor ajuste sob esse critério. Known_y representam dados sobre a "variável dependente" e known_x representam dados em uma ou mais "variáveis independentes". O Arquivo de Ajuda de CRESCIMENTO discute casos raros em que o segundo ou terceiro argumento pode ser omitido.

Supondo que haja variáveis de preditor p, GROWTH essencialmente chama LOGEST. LOGEST ajusta-se a uma equação da forma:

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" for definido como TRUE, você deseja que o modelo de regressão inclua o coeficiente multiplicativo b no modelo de regressão. Se definido como FALSE, b será excluído essencialmente definindo-o como 1. O último argumento é opcional; se o argumento for omitido, ele será interpretado como TRUE.

Para facilitar a exposição no restante deste artigo, suponha que os dados sejam organizados em colunas para que known_y seja uma coluna de dados y e known_x seja uma ou mais colunas de dados x. As dimensões (comprimentos) de cada uma dessas colunas devem ser iguais. New_x também será considerado organizado em colunas e deve haver o mesmo número de colunas para new_x como para known_x. Todas as nossas observações abaixo serão igualmente verdadeiras se os dados não estiverem organizados em colunas, mas é mais fácil discutir o caso único (usado com mais frequência).

Depois de calcular o modelo de regressão de melhor ajuste (chamando essencialmente a função LOGEST do Excel), GROWTH retorna valores previstos associados a new_x.

Este artigo utiliza exemplos para mostrar a relação do CRESCIMENTO com o LOGEST e para apontar problemas no LOGEST em versões do Excel anteriores ao Excel 2003, os quais se traduzem em problemas no CRESCIMENTO. GROWTH efetivamente chama LOGEST, executa LOGEST, utiliza os coeficientes de regressão derivados da saída de LOGEST para calcular os valores y previstos associados a cada linha de new_x's, e apresenta esta coluna de valores y previstos para você. Portanto, você deve saber sobre problemas na execução do LOGEST. Quando LOGEST é chamado, ele efetivamente chama LINEST. Embora o código para GROWTH e LOGEST não tenha sido reescrito para o Excel 2003 e para versões posteriores do Excel, alterações abrangentes (e melhorias) no código LINEST foram feitas.

Como suplementos a este artigo, o artigo a seguir sobre LINEST é altamente recomendado. Ele contém vários exemplos e problemas de documentos com LINEST em versões do Excel anteriores ao Excel 2003.

Para obter mais informações sobre LINEST, selecione o seguinte número de artigo para exibir o artigo na Base de Dados de Conhecimento da Microsoft:

828533 Descrição da função LINEST no Excel 2003 e no Excel 2004 para Mac

O arquivo de Ajuda LINEST, conforme revisado para o Excel 2003, também é recomendado.

O artigo a seguir sobre LOGEST explica como LOGEST interage com LINEST. Esses detalhes são omitidos aqui.

Para obter mais informações, consulte as funções estatísticas do Excel: LOGEST.

Como 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 do uso do GROWTH. O arquivo de Ajuda em GROWTH 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 devem ser matrizes ou intervalos de células que tenham dimensões relacionadas. Se known_y's é uma coluna por m linhas, então known_x's é c colunas por m linhas, onde c é maior ou igual a um. C é o número de variáveis de preditor; m é o número de pontos de dados. New_x deve então ter c colunas por r linhas, onde r é maior ou igual a um. (Relações semelhantes em dimensões devem conter se os dados forem dispostos em linhas em vez de colunas.) Constante é um argumento lógico que deve ser definido como TRUE ou FALSE (ou 0 ou 1 que o Excel interpreta como FALSE ou TRUE, respectivamente). Os últimos três argumentos para GROWTH são todos opcionais; consulte o arquivo de Ajuda do GROWTH para ver as opções de omitir o segundo argumento, o terceiro argumento ou ambos; omitir o quarto argumento é interpretado como TRUE.

O uso mais comum de GROWTH inclui dois intervalos de células que contêm os dados, como GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Como normalmente há mais de uma variável de preditor, o segundo argumento neste exemplo contém várias colunas. Neste exemplo, há 100 entidades, um valor variável dependente (known_y) para cada assunto e cinco valores variáveis dependentes (known_x) para cada assunto. Há mais oito assuntos hipotéticos em que você deseja usar GROWTH para calcular valores y previstos.

Exemplo de uso

Um exemplo de planilha do Excel é fornecido para ilustrar os seguintes conceitos principais:

  • Como o CRESCIMENTO interage com o LOGEST
  • Problemas que ocorrem com GROWTH (ou LOGEST e LINEST) devido à colinearidade dos valores known_x em versões do Excel anteriores ao Excel 2003

Observação

Uma ampla discussão sobre o segundo item com marcação por pontos no contexto de LINEST é fornecida no artigo sobre LINEST.

Para ilustrar a função GROWTH, crie uma planilha em branco do Excel, copie a tabela a seguir, selecione a célula A1 na planilha do Excel em branco e cole as entradas para que a tabela a seguir preencha as células A1:K35 em sua planilha.

Um B C D E F G H Eu J K
y: de x:
=EXP(F2) 1 2 1 1
=EXP(F3) 3 4 1 2
=EXP(F4) 4 5 1 3
=EXP(F5) 6 7 1 4
=EXP(F6) 7 oito 1 5
novos x's: 9 11
12 14
CRESCIMENTO usando as colunas B,C: Valores do Excel 2002 e versões anteriores do Excel:
Valores para o Excel 2003 e para versões posteriores do Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 3400.16400895377
CRESCIMENTO usando somente col B
=CRESCIMENTO(A2:A6,B2:B6,B7:B8,VERDADEIRO) 472,432432563203 472,432432563203
=CRESCIMENTO(A2:A6;B2:B6;B7:B8;VERDADEIRO) 3400.16400895377 3400.16400895377
Valores ajustados dos resultados do LOGEST no Excel 2003 e em versões posteriores do Excel
Usando as colunas B, C Usando Col B
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7) =EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8) =EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST usando as colunas B, C: Valores do Excel 2002 e versões anteriores do Excel: Valores para o Excel 2003 e para versões posteriores do Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 1 1,9307233720034 1,26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 225 3 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LOGEST usando somente a coluna B
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 1.9307233720034 1,26724101129183 1,9307233720034 1,26724101129183
=LOGEST(A2:A6,B2:B6,VERDADEIRO,VERDADEIRO) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 224.999999999999 3 225 3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

Observação

Depois de colar essa tabela em sua nova planilha do Excel, selecione o botão Colar Opções e selecioneFormatação de Destino de Correspondência. Com o intervalo colado ainda selecionado, use um dos seguintes procedimentos, conforme apropriado para a versão do Excel que você está executando:

  • No Microsoft Office Excel 2007, selecione a guia Página Inicial , selecione Formatar no grupo Células e, em seguida, selecione AutoFit Column Width.
  • No Excel 2003, aponte para Coluna no menu Formatar e selecione AutoAjuste Seleção.

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 usadas 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, respectivamente. Os resultados nas células A10:A16 corresponderão à versão do Excel que você usa. Por enquanto, concentre-se nos resultados do Excel 2003 e nas versões posteriores do Excel quando você investiga como o GROWTH chama LOGEST e como o GROWTH usa os resultados do LOGEST.

GROWTH e LOGEST podem ser exibidos como interagindo nas seguintes etapas:

  1. Você utiliza a função GROWTH(known_y's, known_x's, new_x's, constant)
  2. GROWTH chama LOGEST(valores_y, valores_x, constante, VERDADEIRO)
  3. Os coeficientes de regressão dessa chamada para LOGEST são obtidos. Esses coeficientes aparecem na primeira linha da tabela de saída do LOGEST.
  4. Para cada linha de new_x, o valor y previsto é calculado com base nesses coeficientes LOGEST e nos valores do new_x nessa linha.
  5. O valor calculado na etapa 4 é retornado na célula apropriada para a saída GROWTH que corresponde à linha desse new_x.

Se GROWTH retornar os resultados apropriados, o LOGEST deverá gerar resultados apropriados na etapa 3. Como a avaliação de LOGEST na etapa 3 requer uma chamada para LINEST, é essencial que LINEST seja bem comportado. Problemas com o LINEST em versões do Excel anteriores ao Excel 2003 são causados por colunas preditoras colineares. (Há outros problemas com LINEST e LOGEST nas versões anteriores do Excel que ocorrem quando o último argumento para GROWTH é definido como FALSE. No entanto, esses problemas não afetam os resultados do CRESCIMENTO e não são discutidos aqui.)

As colunas do 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 chamada de redundante porque as informações que ela contém podem ser construídas a partir das colunas c1, c2 e outras colunas. O princípio fundamental da colinearidade é que os resultados não devem ser afetados, independentemente de uma coluna redundante ser incluída ou removida dos dados originais. Como o LINEST em versões do Excel anteriores ao Excel 2003 não procurava colinearidade, esse princípio era facilmente violado. As colunas do 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. Nesse 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 menor que 10^(-12), por exemplo.

O primeiro modelo, nas linhas 10 a 12, usa as colunas B e C como previsores e solicita ao Excel para modelar a constante (último argumento definido como TRUE). Em seguida, o Excel insere efetivamente uma coluna de preditor adicional que se parece com as células D2:D6. É fácil observar 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, há 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 quando o terceiro argumento do LOGEST é omitido ou definido como VERDADEIRO (o caso "normal")

Isso causa problemas numéricos que versões do Excel anteriores ao Excel 2003 não podem calcular resultados. Portanto, a tabela de saída GROWTH é preenchida com #NUM!.

O segundo modelo, nas linhas 14 a 16, é aquele que qualquer versão do Excel pode lidar com êxito. Não há collinearidade e o usuário solicita novamente ao Excel que modele a constante. Este modelo está incluído aqui pelos seguintes motivos:

  • Primeiro, é mais comum em casos práticos que não haja colinearidade presente. Esses casos são tratados suficientemente em todas as versões do Excel. Deve ser reconfortante saber que problemas numéricos provavelmente não ocorrerão no caso prático mais comum se você tiver uma versão anterior do Excel.
  • Em segundo lugar, este exemplo é usado para comparar o comportamento do Excel 2003 e das versões posteriores do Excel nos dois modelos. A maioria dos principais pacotes de software estatístico analisa a colinearidade, remove uma coluna que é uma soma de múltiplos de outras do modelo e alerta o usuário com uma mensagem como "a coluna C é linearmente dependente de outras colunas preditoras e foi removida da análise".

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

No segundo modelo das linhas 30 às 35, não há colinearidade e nenhuma coluna foi removida. Você pode ver que os valores y previstos são os mesmos em ambos os modelos. Esse problema ocorre porque a remoção de uma coluna redundante que é uma soma de múltiplos de outras não reduz a bondade do ajuste do modelo resultante. Essas colunas são removidas precisamente porque não representam nenhum valor adicionado na tentativa de localizar o melhor ajuste de quadrados mínimos. Além disso, se você examinar a saída LOGEST nas células I23:K35 no Excel 2003 e em versões posteriores do Excel, observará que as três últimas linhas das tabelas de saída são as mesmas. Além disso, as entradas nas células I31:J32 e células J24:K25 coincidem. Ele demonstra que os mesmos resultados são obtidos quando a coluna C é incluída no modelo, mas é considerada 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). Isso satisfaz o princípio fundamental na existência da collinearidade.

Nas células A18:C21, a Microsoft usa dados do Excel 2003 e de versões posteriores do Excel para ilustrar como o GROWTH usa a saída LOGEST e calcula os valores y previstos relevantes. Examinando as fórmulas nas células A20:A21 e células C20:C21, você 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 (usando colunas B, C como preditores; usando apenas a coluna B como um preditor).

Collinearity é identificada no LOGEST no Excel 2003 e em versões posteriores do Excel porque LOGEST chama LINEST. LINEST usa uma abordagem diferente para resolver os coeficientes de regressão. Essa abordagem é a Decomposição de QR. O artigo LINEST contém um passo a passo 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 LINEST.

LINEST foi calculado usando uma abordagem que desconsiderou problemas de collinearidade. A existência da colinearidade causou erros de arredondamento, erros padrão inadequados nos coeficientes de regressão e graus de liberdade inadequados. Às vezes, os problemas de arredondamento são suficientemente graves que LINEST preencheu sua tabela de saída com #NUM!. Se, como é o caso na prática na maioria das vezes, você pode ter certeza de que não havia colunas de preditor colineares (ou quase colineares), então a função LINEST geralmente fornece resultados aceitáveis. Portanto, os usuários de GROWTH podem ficar igualmente tranquilos se puderem ver a ausência de colunas de preditor colinear (ou quase colinear).

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

As melhorias em LINEST incluem a adoção do método de decomposição QR para determinar coeficientes de regressão. A Decomposição de QR tem as seguintes vantagens:

  • Melhor estabilidade numérica (geralmente, erros de arredondamento menores)
  • 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. Essas melhorias em LINEST se traduzem em melhorias em LOGEST e GROWTH.

Conclusões

O desempenho do GROWTH foi melhorado porque o LINEST foi consideravelmente aprimorado para o Excel 2003 e para versões posteriores do Excel. Melhorias em LINEST também afetam LOGEST, porque LOGEST é chamado por GROWTH. Os usuários de versões anteriores do Excel devem verificar se as colunas preditoras não são colineares antes de usarem GROWTH.

Grande parte do material apresentado neste artigo e no artigo LINEST pode a princípio parecer alarmar os usuários de versões do Excel anteriores ao Excel 2003. No entanto, deve-se observar que a collinearidade é um problema em apenas uma pequena porcentagem de casos. Versões anteriores do Excel fornecem resultados aceitáveis de CRESCIMENTO quando não há collinearidade.

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