Observação
O acesso a essa página exige autorização. Você pode tentar entrar ou alterar diretórios.
O acesso a essa página exige autorização. Você pode tentar alterar os diretórios.
Calcula as estatísticas de uma linha ao utilizar o método de mínimos quadrados para calcular uma linha reta que melhor se adequa aos seus dados e devolve uma matriz que descreve a linha. Como essa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula matricial.
Sintaxe
expressão. LinEst (Arg1, Arg2, Arg3, Arg4)
expressão Uma variável que representa um objeto WorksheetFunction .
Parâmetros
| Nome | Obrigatório/Opcional | Tipo de dados | Descrição |
|---|---|---|---|
| Arg1 | Obrigatório | Variant | Known_y- o conjunto de valores y que já conhece na relação y = mx + b. |
| Arg2 | Opcional | Variant | Val_conhecidos_x - um conjunto opcional de valores x que talvez você já conheça na relação y = mx + b. |
| Arg3 | Opcional | Variant | Constante - um valor lógico que especifica a necessidade de forçar ou não a constante b igual a zero. |
| Arg4 | Opcional | Variant | Estatísticas - um valor lógico especificando a necessidade de retornar ou não estatísticas adicionais de regressão. |
Valor de retorno
Variant
Comentários
A equação para a linha é y = mx + b ou y = m1x1 + m2x2 + ... + b (se existirem vários intervalos de valores de x), em que 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 que o LinEst devolve é {mn,mn-1,...,m1,b}.
O LinEst também pode devolver estatísticas de regressão adicionais.
Se a matriz known_y estiver numa única coluna, cada coluna de known_x é interpretada como uma variável separada.
Se a matriz known_y estiver numa única linha, cada linha de known_x é 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 known_x for omitido, assume-se que é a matriz {1,2,3,...} que tem o mesmo tamanho que known_y.
Se constante for Verdadeiro ou omitido, b é calculado normalmente.
Se constante for Falso, b é definido como igual a 0 e os valores m são ajustados para se ajustarem
y = mxa .Se as estatísticas forem Verdadeiras, LinEst devolve as estatísticas de regressão adicionais, pelo que a matriz devolvida é
{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.Se estatísticas for Falso ou omitido, LinEst devolve apenas os coeficientes m e a constante b.
Há exemplos de estatísticas adicionais de regressão a seguir.
| Estatística de regressão | Descrição |
|---|---|
| se1,se2,...,sen | Os valores padrão de erro dos coeficientes m1,m2,...,mn. |
| seb | O valor de erro padrão para a constante b (seb = #N/D quando constante é Falso). |
| r2 | O coeficiente de determinação. Compara valores y reais e estimados e intervalos no valor de 0 a 1. Se for 1, existe uma correlação perfeita na amostra— não há diferença entre o valor estimado de y e o valor y 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 encontra na tabela com a estatística F devolvida pelo 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.
Pode descrever qualquer linha reta com o declive e a interceção y: Slope (m). Para localizar o declive de uma linha, muitas vezes escrita como m, tome dois pontos na linha (x1,y1) e (x2,y2); o declive é igual a (y2 - y1)/(x2 - x1). Interceção Y (b): a interceção y de uma linha, muitas vezes 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. Depois de saber os valores de m e b, pode calcular qualquer ponto na linha ao ligar o valor y ou x a essa equação. Também é possível usar a função TENDÊNCIA.
Quando tem apenas uma variável x independente, pode obter os valores de declive e interceção de y diretamente com as seguintes fórmulas:
- Declive:
=INDEX(LINEST(known_y's,known_x's),1) - Interceção Y:
=INDEX(LINEST(known_y's,known_x's),2)
A precisão da linha calculada pelo LinEst depende do grau de dispersão nos seus dados. Quanto mais lineares forem os dados, mais preciso será o modelo LinEst . O LinEst utiliza o método de mínimos quadrados para determinar a melhor opção para os dados. Quando tem apenas uma variável x independente, os cálculos para m e b baseiam-se nas seguintes fórmulas:
onde x e y são médias de exemplo, ou seja, x = MÉDIA(x conhecidos x) e y = MÉDIA(known_y).
As funções de ajuste de linhas e curvas LinEst e LogEst podem calcular a melhor linha reta ou curva exponencial que se ajuste aos seus dados. No entanto, você precisa escolher o resultado mais adequado aos seus dados. Pode calcular TREND(known_y's,known_x's) para uma linha reta ou GROWTH(known_y's, known_x's) 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, o Microsoft Excel calcula para cada ponto a diferença quadrada entre o valor de y estimado para esse ponto e o respetivo valor y real. A soma destas diferenças quadradas é denominada soma residual de quadrados, ssresid. Em seguida, o Excel calcula a soma total de quadrados, sstotal. Quando constante = VERDADEIRO ou omitido, a soma total de quadrados é a soma das diferenças ao quadrado entre os valores reais de y e a média dos valores y. Quando constante = FALSO, a soma total de quadrados é a soma dos quadrados dos valores reais de y (sem subtrair o valor y médio de cada valor y individual). Em seguida, pode encontrar a soma de regressão de quadrados, ssreg, em ssreg = sstotal - ssresid. Quanto menor for a soma residual dos quadrados, em comparação com a soma total dos quadrados, maior é 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 das colunas X (suponha que Y e X estão em colunas) podem não ter nenhum valor preditivo adicional na presença das outras colunas X. Por outras palavras, eliminar uma ou mais colunas X pode levar a valores Y previstos que são igualmente precisos. Nesse caso, estas colunas X redundantes devem ser omitidas do modelo de regressão. Este fenómeno chama-se collinearidade porque qualquer coluna X redundante pode ser expressa como uma soma de múltiplos das colunas X não redundantes. O LinEst verifica a collinearidade e remove todas as colunas X redundantes do modelo de regressão quando as identifica. As colunas X removidas podem ser reconhecidas na saída linEst como tendo 0 coeficientes, bem como 0 ses.
- Se uma ou mais colunas forem removidas como redundantes, df será afetado porque df depende do número de colunas X realmente utilizadas para fins preditivos. Se df for alterado porque as colunas X redundantes são removidas, os valores de sey e F também são afetados.
- A collinearidade deve ser relativamente rara na prática. No entanto, um caso em que é mais provável que surja é quando algumas colunas X contêm apenas 0 e 1 como indicadores de se um assunto numa experimentação é ou não membro de um grupo específico. Se constante = VERDADEIRO ou omitido, LinEst insere efetivamente uma coluna X adicional de todos os 1 para modelar a interceção. Se tiver uma coluna com um 1 para cada assunto se for homem, ou 0 caso contrário, e também tiver uma coluna com um 1 para cada assunto se for feminino ou 0 caso contrário, esta última coluna será redundante porque as entradas na mesma podem ser obtidas ao subtrair a entrada na coluna indicadora masculina da entrada na coluna adicional de todas as 1 adicionadas pelo LinEst.
- df é calculado da seguinte forma quando não são removidas colunas X do modelo devido à collinearidade: se existirem k colunas de known_x e const = VERDADEIRO ou omitido,
df = n - k - 1. Se constante = FALSO,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 utilizado na função LinEst é diferente do algoritmo subjacente utilizado nas funções Declive e Interceção . 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:
- LinEst devolve um valor de 0. O algoritmo LinEst foi concebido para devolver resultados razoáveis para dados colelineares e, neste caso, pode encontrar pelo menos uma resposta.
- Declive e Interceção devolvem um #DIV/0! . O algoritmo Declive e Interceção foi concebido para procurar uma e apenas uma resposta e, neste caso, pode haver mais do que uma resposta.
Suporte e comentários
Tem dúvidas ou quer enviar comentários sobre o VBA para Office ou sobre esta documentação? Confira Suporte e comentários sobre o VBA para Office a fim de obter orientação sobre as maneiras pelas quais você pode receber suporte e fornecer comentários.