WorksheetFunction.LinEst(Object, Object, Object, Object) Método

Esta página é útil?

Algum comentário adicional?

Os comentários serão enviados à Microsoft: quando você pressionar o botão Enviar, seus comentários serão usados para aprimorar os produtos e os serviços da Microsoft. Política de Privacidade.

Definição

Namespace:

Microsoft.Office.Interop.Excel

Assembly:

Microsoft.Office.Interop.Excel.dll

Calcula as estatísticas de uma linha usando o método "quadrados menores" para calcular uma linha reta que melhor se adapte aos seus dados e retorna uma matriz que descreve a linha. Como essa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula matricial.

public object LinEst (object Arg1, object Arg2, object Arg3, object Arg4);

Public Function LinEst (Arg1 As Object, Optional Arg2 As Object, Optional Arg3 As Object, Optional Arg4 As Object) As Object

Parâmetros

Arg1

Object

Val_conhecidos_y - o conjunto de valores y que você já conhece na relação y = mx + b.

Arg2

Object

Val_conhecidos_x - um conjunto opcional de valores x que talvez você já conheça na relação y = mx + b.

Arg3

Object

Constante - um valor lógico que especifica a necessidade de forçar ou não a constante b igual a zero.

Arg4

Object

Estatísticas - um valor lógico especificando a necessidade de retornar ou não estatísticas adicionais de regressão.

Retornos

Object

Comentários

A equação para a linha é:

y = mx + b ou

y = m1x1 + m2x2 + ... + b (se houver vários intervalos de valores x)

onde o valor y dependente é uma função dos valores x independentes. Os valores m são coeficientes correspondentes a cada valor x e b é um valor constante. Observe que y, x e m podem ser vetores. A matriz retornada por LinEst é {mn,mn-1,...,m1,b}. LinEst também pode retornar estatísticas de regressão adicionais.

Se a matriz val_conhecidos_y estiver em uma única coluna, cada coluna de val_conhecidos_x será interpretada como uma variável separada.

Se a matriz val_conhecidos_y estiver em uma única linha, cada linha de val_conhecidos_x será interpretada como uma variável separada.

A matriz val_conhecidos_x pode incluir um ou mais conjuntos de variáveis. Se apenas uma variável for usada, val_conhecidos_y e val_conhecidos_x podem ser intervalos de qualquer formato, desde que tenham dimensões iguais. Se mais de uma variável for usada, val_conhecidos_y deverá ser um vetor (ou seja, um intervalo com altura de uma linha ou largura de uma coluna).

Se val_conhecidos_x for omitido, pressupõe-se que a matriz {1,2,3,...} seja do mesmo tamanho que val_conhecidos_y.

Se const for verdadeiro ou omitido, b será calculado normalmente.

Se const for false,b será definido como 0 e os valores m serão ajustados para ajustar y = mx.

Se stats for truuue, LinEst retornará as estatísticas de regressão adicionais, portanto, a matriz retornada será {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey; F,df;ssreg,ssresid}.

Se as estatísticas for false ou omitida, LinEst retornará apenas os coeficientes m e a constante b.

Há exemplos de estatísticas adicionais de regressão a seguir.

se1,se2,...,sen	Os valores padrão de erro dos coeficientes m1,m2,...,mn.
seb	O valor de erro padrão da constante b (seb = #N/A quando const for false).
r2	O coeficiente de determinação. Compara valores y reais e estimados e intervalos no valor de 0 a 1. Se for 1, haverá uma correlação perfeita no exemplo, ou seja, não haverá diferença entre o valor y estimado e o real. Por outro lado, se o coeficiente de determinação for 0, a equação de regressão não ajudará a prever um valor y.
sey	O erro padrão da estimativa de y.
S	A estatística F ou valor F observado. Use a estatística F para determinar se a relação observada entre as variáveis dependentes e independentes ocorrerá aleatoriamente.
df	Os graus de liberdade. Use os graus de liberdade para ajudá-lo a obter valores F críticos em uma tabela estatística. Compare os valores que você encontra na tabela com a estatística F retornada pela LinEst para determinar um nível de confiança para o modelo.
ssreg	A soma de regressão dos quadrados.
ssresid	A soma residual dos quadrados.

A ilustração a seguir mostra a ordem na qual as estatísticas adicionais de regressão são retornadas.

Figura 1: Planilha

Você pode descrever qualquer linha reta com a inclinação e a interceptação y:

Inclinação (m):

Para encontrar a inclinação de uma linha, geralmente escrita como m, leve dois pontos na linha, (x1,y1) e (x2,y2); a inclinação é igual a (y2 - y1)/(x2 - x1).

Interceptação Y (b):

A interceptação y de uma linha, geralmente escrita como b, é o valor de y no ponto em que a linha cruza o eixo y.

A equação de uma linha reta é y = mx + b. Quando souber os valores de m e b, você poderá calcular qualquer ponto na linha conectando o valor y ou x à equação. Você também pode usar a Trend(Object, Object, Object, Object) função.

Quando você tem apenas uma variável x independente, você pode obter os valores de inclinação e interceptação y diretamente usando as seguintes fórmulas:

Inclinação:

=INDEX(LINEST(known_y,known_x)1)

Interceptação Y:

=INDEX(LINEST(known_y,known_x)2)

A precisão da linha calculada pelo LineEst depende do grau de dispersão em seus dados. Quanto mais linear os dados, mais preciso o modelo LineEst. LineEst usa o método de mínimos quadrados para determinar o melhor ajuste para os dados. Quando você tem apenas uma variável x independente, os cálculos para m e b são baseados nas seguintes fórmulas:

Figura 2: Equação

Figura 3: Equação

onde x e y são médias de exemplo, ou seja, x = AVERAGE(conhecidos x's) e y = AVERAGE(known_y's).

As funções de ajuste de linha e curva LineEst podem calcular a melhor linha reta ou curva exponencial que se ajuste LogEst(Object, Object, Object, Object) aos seus dados. No entanto, você precisa escolher o resultado mais adequado aos seus dados. É possível calcular TENDÊNCIA(val_conhecidos_y,val_conhecidos_x) para uma linha reta ou CRESCIMENTO(val_conhecidos_y,val_conhecidos_x) para uma curva exponencial. Essas funções, sem o argumento novos_valores_x, retornam uma matriz de valores y previstos ao longo dessa linha ou curva nos pontos de dados reais. Você poderá então comparar os valores previstos com os reais. Talvez seja conveniente colocá-los em um gráfico para comparação visual.

Na análise de regressão, Microsoft Excel calcula para cada ponto a diferença quadrada entre o valor y estimado para esse ponto e seu valor y real. A soma dessas diferenças quadradas é chamada de soma residual de quadrados, ssresid. Microsoft Excel calcula a soma total de quadrados, sstotal. Quando const = true, ou omitido, a soma total de quadrados é a soma das diferenças quadradas entre os valores y reais e a média dos valores y. Quando const = false, a soma total de quadrados é a soma dos quadrados dos valores y reais (sem subtrair o valor y médio de cada valor y individual). Em seguida, a soma de regressão de quadrados, ssreg, pode ser encontrada em: ssreg = sstotal - ssresid. Quanto menor for a soma residual de quadrados, em comparação com a soma total de quadrados, maior será o valor do coeficiente de determinação, r2, que é um indicador de quão bem a equação resultante da análise de regressão explica a relação entre as variáveis. r2 é igual a ssreg/sstotal.

Em alguns casos, uma ou mais colunas X (pressupondo que Y e X estão em colunas) podem não ter nenhum valor preditivo adicional na presença das outras colunas X. Em outras palavras, a eliminação de uma ou mais colunas X pode levar a valores Y previstos que sejam igualmente precisos. Nesse caso, essas colunas X redundantes devem ser omitidas do modelo de regressão. Esse fenômeno é chamado de "collinearity" porque qualquer coluna X redundante pode ser expressa como uma soma de múltiplos das colunas X não redundantes. LinEst verifica se há collinearidade e remove quaisquer colunas X redundantes do modelo de regressão quando as identifica. As colunas X removidas podem ser reconhecidas na saída LinEst como tendo coeficientes 0, bem como 0 se's. Se uma ou mais colunas são removidas como redundantes, o df é afetado porque df depende do número de colunas X realmente usadas para fins preditivos. Se df for alterado porque as colunas X redundantes são removidas, os valores de sey e F também serão afetados. A collinearidade deve ser relativamente rara na prática. No entanto, um caso em que é mais provável surgir é quando algumas colunas X contêm apenas 0 e 1 como indicadores de se um assunto em um experimento é ou não membro de um determinado grupo. Se const = true ou omitido, LinEst insere efetivamente uma coluna X adicional de todos os 1 para modelar a interceptação. Se você tiver uma coluna com um 1 para cada assunto, se for masculino, ou 0, se não for, e também tiver uma coluna com um 1 para cada assunto se for do sexo feminino, ou 0 se não for, essa última coluna será redundante porque as entradas nele podem ser obtidas da subtração da entrada na coluna "indicador masculino" da entrada na coluna adicional de todos os 1 adicionados por LineEst.

df é calculado da seguinte maneira quando nenhuma coluna X é removida do modelo devido à collinearidade: se houver k colunas de known_x e const = true ou omitida, df = n – k – 1. Se const = false, df = n - k. Em ambos os casos, cada coluna X removida devido à colinearidade aumenta df em 1.

As fórmulas que fornecem matrizes devem ser inseridas como fórmulas matriciais. Ao inserir uma constante, como um argumento val_conhecidos_x, use vírgulas na mesma linha e ponto-e-vírgulas para separar linhas. Os caracteres separadores podem ser diferentes dependendo da configuração da localidade em Opções Regionais e de Idioma no Painel de Controle.

Lembre-se de que os valores y previstos pela equação de regressão talvez não sejam válidos se estiverem fora do intervalo dos valores y usados para determinar a equação. O algoritmo subjacente usado na função LinEst é diferente do algoritmo subjacente usado Slope(Object, Object) nas funções Intercept(Object, Object) e. A diferença entre esses algoritmos pode levar a diferentes resultados quando os dados forem indeterminados e colineares. Por exemplo, se os pontos de dados do argumento val_conhecidos_y forem 0 e os pontos de dados do argumento val_conhecidos_x forem 1:

LineEst retorna um valor 0. O algoritmo LinEst foi projetado para retornar resultados razoáveis para dados collineares e, nesse caso, pelo menos uma resposta pode ser encontrada. Slope(Object, Object) e Intercept(Object, Object) retornar um #DIV/0! . O algoritmo e foi projetado para procurar uma e apenas uma resposta e, nesse caso, pode Slope(Object, Object) haver mais de uma Intercept(Object, Object) resposta.