Funções estatísticas do Excel: INTERCETAR

Resumo

Este artigo aborda a função INTERCETAR no Microsoft Excel, ilustra como utilizar a função e compara os seus resultados para o Excel 2003 e para versões posteriores do Excel com os respetivos resultados em versões anteriores do Excel.

Mais Informações

A função INTERCETAR(known_y,known_x) devolve a INTERCEÇÃO da linha de regressão linear utilizada para prever valores y de valores x.

Sintaxe

INTERCEPT(known_y's,known_x's)

Os argumentos, known_y e known_x, têm de ser matrizes ou intervalos de células que contenham números iguais de valores de dados numéricos. Frequentemente, INTERCETAR inclui dois intervalos de células que contêm os dados, como INTERCETAR(A1:A100, B1:B100).

Exemplo de utilização

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

• No Microsoft Office Excel 2007, clique no separador Base , clique em Formatar no grupo Células e, em seguida, clique em Ajustar Automaticamente Larguras das Colunas.
• No Excel 2003, aponte para Coluna no menu Formatar e, em seguida, clique em Ajustar Automaticamente a Seleção.

Poderá querer formatar as células B2:B7 como Número com 0 casas decimais e células A9:D13 como Número com 6 casas decimais.

As células A2:A7 e B2:B7 contêm os valores de y e x que chamam INTERCETAR na célula A10.

Em versões do Excel anteriores ao Excel 2003, o INTERCEPT pode apresentar erros de arredondamento. O Excel 2003 e versões posteriores do Excel melhoram o comportamento do INTERCETAR. INTERCETAR(known_y, known_x) é o resultado da avaliação de MÉDIA(known_y) – DECLIVE(known_y,known_x) * MÉDIA(known_x). Embora o código para INTERCETAR não tenha sido alterado diretamente para o Excel 2003 e para versões posteriores do Excel, o comportamento de INTERCETAR é melhorado devido a código melhorado para DECLIVE.

Se tiver uma versão anterior do Excel, pode utilizar a folha de cálculo que criou anteriormente para executar uma experimentação para detetar quando ocorrem erros de arredondamento. Adicionar uma constante positiva a cada uma das observações em B2:B7 não deve afetar o valor de DECLIVE. Se desenhar pares x,y com x no eixo horizontal e y no eixo vertical e, em seguida, adicionar uma constante positiva a cada valor x, os dados mudam apenas para a direita. A linha de regressão mais adequada ainda tem o mesmo declive. No entanto, os dados deslocados têm uma interceção diferente.

Com o valor predefinido de 0 em D3, DECLIVE em A9 é 0,775280899. A célula A10 mostra o valor de INTERCETAR e a célula A11 mostra o valor da expressão que é avaliada ao calcular INTERCETAR:

MÉDIA(known_y) – DECLIVE(known_y,known_x) * MÉDIA(known_x)

Os valores nas células A9 e A10 concordam sempre porque o valor em A10 é exatamente o que INTERCETAR devolve. O DECLIVE não deve variar, uma vez que adiciona diferentes constantes positivas aos known_x. A célula A11 mostra MÉDIA(known_y) – 0,775280899 * MÉDIA(known_x). Uma vez que DECLIVE não deve ser alterado e 0,775280899 é o valor de DECLIVE quando D3 = 0, os valores desta expressão em A11 também devem concordar com os valores nas células A9 e A10.

Se aumentar o valor em D3, adicione uma constante maior a B2:B7. Se D3 <= 7, não existem erros de arredondamento que apareçam nas primeiras seis casas decimais de DECLIVE. No entanto, se experimentar 7,25, 7,5, 7,75 e 8, o DECLIVE na A9 muda. Como resultado, os valores nas células A11 (que concordam com A10) e A12 diferem. No entanto, os valores em A11 (ou A10) e A12 devem ser os mesmos porque adicionar uma constante aos known_x não deve afetar o DECLIVE.

D7:D13 mostra os valores que INTERCETAR devolve e os valores que INTERCETAR deveriam ter devolvido se DECLIVE não tivesse sido alterado. Estes pares de valores são apresentados para os casos em que D3 = 7,5 e 8, respetivamente. Os erros de arredondamento tornaram-se tão graves que a divisão em 0 ocorre quando D3 = 8.

As versões anteriores do Excel dão respostas erradas nestes casos porque os efeitos dos erros de arredondamento são maiores com a fórmula computacional que estas versões utilizam. Ainda assim, esta experimentação mostra que os casos em que os erros ocorrem são extremos.

Se tiver o Excel 2003 ou uma versão posterior do Excel, existe pouca ou nenhuma diferença entre os valores comuns em A10 e A11 e o valor em A12 se experimentar a experimentação. No entanto, as células D7:D13 mostram os erros de arredondamento obtidos com as versões anteriores do Excel.

Resultados em versões anteriores do Excel

O artigo sobre DECLIVE descreve a fórmula menos robusta numericamente utilizada pelas versões anteriores. A fórmula requer apenas uma passagem pelos dados. Apenas as deficiências do DECLIVE nestas versões fazem com que o INTERCEPT dê erros de arredondamento nos casos extremos.

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

O Excel 2003 e versões posteriores do Excel utilizam um procedimento melhorado para calcular o DECLIVE. Como resultado, o desempenho do INTERCEPT melhora. O procedimento melhorado requer duas passagens pelos dados. Mais uma vez, o seguinte artigo sobre DECLIVE descreve a melhoria.

Para obter mais informações sobre as melhorias no DECLIVE para Excel 2003 e para versões posteriores do Excel, clique no seguinte número de artigo para ver o artigo na Base de Dados de Conhecimento Microsoft:

828142 funções estatísticas do Excel: DECLIVE

Conclusões

Uma vez que o Excel 2003 e versões posteriores do Excel substituem uma abordagem de um passe por uma abordagem de dois passes, o desempenho numérico do DECLIVE no Excel 2003 e em versões posteriores do Excel é melhor do que em versões anteriores do Excel. Por conseguinte, o desempenho numérico do INTERCEPT é melhor. Os resultados no Excel 2003 e em versões posteriores do Excel nunca serão menos precisos do que os resultados em versões anteriores do Excel.

Normalmente, não existe uma diferença entre os resultados no Excel 2003 e nas versões posteriores do Excel e os resultados em versões anteriores do Excel porque os dados não se comportam frequentemente da forma invulgar que esta experimentação ilustra. É mais provável que a instabilidade numérica apareça em versões anteriores do Excel quando os dados contêm muitos dígitos significativos e pouca variação entre os valores de dados.

O procedimento seguinte localiza a soma dos desvios ao quadrado sobre uma média de amostra:

1. Localize a média da amostra.
2. Calcular cada desvio ao quadrado.
3. Somar os desvios ao quadrado.

Este procedimento é mais preciso do que o seguinte procedimento alternativo (também conhecido como "fórmula calculadora", porque era adequado para ser utilizado numa calculadora para um pequeno número de pontos de dados):

1. Localize a soma dos quadrados de todas as observações, o tamanho da amostra e a soma de todas as observações.
2. Calcular a soma dos quadrados de todas as observações menos ((soma de todas as observações)^2)/tamanho da amostra).

Ao substituir este último procedimento de um passe pelo procedimento de dois passes que localiza a média da amostra no primeiro passe e calcula a soma dos desvios quadrados sobre o mesmo na segunda passagem, o Excel 2003 e versões posteriores do Excel melhoram muitas outras funções. Uma breve lista destas funções inclui VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, PREVISÃO, DECLIVE, INTERCEÇÃO, PEARSON, RSQ e STEYX. A Microsoft fez melhorias semelhantes em cada uma das três ferramentas de Análise de Variância no Analysis ToolPak.