locked
Problema com projeção linear

    Pergunta

  • Tenho no excel uma planilha que possui um calculo de projeção liner, são dois campos, segue abaixo os códigos:

    Primeiro campo: =PROJ.LIN(C7:H7;C$6:H$6;2)
    Segundo campo: =ÍNDICE(PROJ.LIN(C7:H7;C$6:H$6);2)

    Queria implementar esse código no access mas não sei como fazer.
    A idéia seria eu ter dois campos, cada um recebendo o resultado que é de direito.
    Melhor que dar o peixe é ensinar a pescar

    Daniel Yamaguti
    Gerente de TI
    sábado, 20 de fevereiro de 2010 01:27

Todas as Respostas

  • Não tenho idéia do que seja PROJ.LIN e ÍNDICE no Excel.


    Poste as fórmulas e tentamos implementar.

    sábado, 20 de fevereiro de 2010 17:28
  • Ola Daniel,

    De uma olhada no help do Excel sobre as funções que você passou, e veja qual a fundamentação matemática delas, e no fundo qual a formula que o Excel usa para chegar ao resultado. Ai poste novamente, com mais detalhes.

    atc

    Ronaldo
    sábado, 20 de fevereiro de 2010 23:12
  • Mostrar tudoMostrar tudo

    PROJ.LIN

    <object id="hhobj_1" classid="clsid:adb880a6-d8ff-11cf-9377-00aa003b7a11" width="14" height="14" type="application/x-oleobject"> <param name="Width" value="0" /> <param name="Height" value="0" /> <param name="Command" value="ALink" /> <param name="Item1" /> <param name="Item2" value="xlfctLINESTC" /> </object>

    Consulte também

    Calcula as estatísticas para uma linha usando o método de "mínimos quadrados" para calcular uma linha reta com o ajuste perfeito para seus dados e retorna uma matriz que descreve a linha. Como dessa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula de matriz.

    A equação para a linha é:

    y = mx + b ou

    y = m1x1 + m2x2 + ... + b (se existirem múltiplos intervalos de valores x)

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

    Sintaxe

    PROJ.LIN(val_conhecidos_y;val_conhecidos_x;constante;estatística)

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

    • 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 uma única linha, cada linha de val_conhecidos_x será interpretada como uma variável separada.

    Val_conhecidos_x    é um conjunto opcional de valores x que você já deve conhecer na relação y = mx + b.

    • 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.

    Constante    é um valor lógico que força ou não a constante b a se igualar a 0.

    • Se constante for VERDADEIRO ou omitido, b será calculado normalmente.

    • Se constante for FALSO, b será definido como 0 e os valores m serão ajustados para que y = mx.

    Estatística    é o valor lógico que especifica se estatísticas de regressão adicionais serão retornadas.

    • Se estatística for VERDADEIRO, PROJ.LIN retornará as estatísticas de regressão adicionais, de forma que a matriz retornada seja {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Se estatística for FALSO ou omitida, PROJ.LIN retornará apenas os coeficientes m e a constante b.

    As estatísticas de regressão adicionais são:

    EstatísticasDescrição
    se1,se2,...,sen Os valores de erro padrão para os coeficientes m1,m2,...,mn.
    seb Os valores de erro padrão para a constante b (seb = #N/D quando constante é FALSO).
    r2 O coeficiente de determinação. Compara valores y estimados e reais e varia de 0 a 1. Se for igual a 1, existirá uma correlação perfeita na amostra — não há diferença entre os valores y estimados e os valores y reais. Por outro lado, se o coeficiente de determinação for igual a 0, a equação de regressão não terá utilidade para prever um valor y. Para obter informações sobre como r2 é calculado, consulte "Comentários" mais adiante neste tópico.
    sey O erro padrão para a estimativa y.
    F A estatística F ou o valor F observado. Use a estatística F para determinar se a relação observada entre as variáveis dependentes e independentes ocorre por acaso.
    df Os graus de liberdade. Use os graus de liberdade para ajudá-lo a encontrar os valores F críticos em uma tabela estatística. Compare os valores encontrados na tabela com a estatística F retornada por PROJ.LIN para determinar um nível de confiança para o modelo. Para obter mais informações sobre o cálculo de df, consulte " Comentários" mais à frente, neste tópico. O exemplo 4 apresentado abaixo mostra a utilização de F e de df.
    ssreg A soma de regressão dos quadrados.
    ssresid A soma residual dos quadrados. Para obter informações sobre como ssreg e ssresid são calculadas, consulte "Comentários" mais à frente, neste tópico.

    A ilustração a seguir mostra a ordem em que as estatísticas adicionais são fornecidas.

    Planilha

    Comentários

    • Você pode descrever qualquer linha reta com a inclinação e o ponto de origem y:

      Inclinação (m):
      Para calcular a inclinação de uma linha, freqüentemente representada por m, use dois pontos da linha, (x1,y1) e (x2,y2); a inclinação será igual a (y2 - y1)/(x2 - x1).

      Ponto de origem y (b):
      O ponto de origem y de uma linha, freqüentemente representado por b, é o valor y no ponto em que a linha cruza o eixo y.

      A equação de uma linha reta é y = mx + b. Uma vez fornecidos os valores de m e b, você poderá calcular qualquer ponto da linha inserindo o valor y ou x nessa equação. Você também pode usar a função TENDÊNCIA.

    • Quando tiver apenas uma variável x independente, você poderá obter os valores de inclinação e de ponto de origem y diretamente, usando as seguintes fórmulas:

      Inclinação:
      =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);1)

      Ponto de origem y:
      =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);2)

    • A precisão da linha calculada por PROJ.LIN depende do grau de dispersão dos dados. Quanto mais lineares forem os dados, mais preciso será o modelo de PROJ.LIN. PROJ.LIN usa o método dos mínimos quadrados para determinar o ajuste perfeito dos dados. Quando você tiver apenas uma variável x independente, os cálculos para m e b serão baseados nas seguintes fórmulas:

      Equação

      Equação

      onde x and y são exemplos de média, ou seja, x = MÉDIA(val_conhecidos_x) e y = MÉDIA(val_conhecidos_y).

    • As funções de ajuste de linha e de curva PROJ.LIN e PROJ.LOG podem calcular a melhor linha reta ou curva exponencial que se ajustem aos dados. No entanto, você deverá decidir qual dos dois resultados melhor se adapta aos dados. Você pode 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 dos valores y estimados ao longo da linha ou curva nos pontos de dados reais. Dessa forma, você poderá comparar os valores previstos com os valores reais. Além disso, é possível representá-los graficamente para uma comparação visual.
    • Na análise de regressão, o Microsoft Excel calcula a diferença de quadrados entre o valor y estimado e o valor y real para cada ponto. A soma dessas diferenças de quadrados é chamada de soma dos quadrados de resíduo, ssresid. O Microsoft Excel calcula a soma total dos quadrados. Quando const for igual a VERDADEIRO ou for omitido, a soma total dos quadrados será a soma das diferenças dos quadrados entre os valores y reais e a média dos valores y. Quando const for igual a FALSO, a soma total dos quadrados será a soma de quadrados dos próprios valores de y (sem subtrair a média dos valores de y de cada valor de y individual). A soma da regressão dos quadrados, ssreg, pode ser obtida de: ssreg = sstotal - ssresid. Quanto menor for a soma de quadrados de resíduo, comparada com a soma de quadrados total, maior será o valor do coeficiente de determinação, r2, que indica a precisão com que a equação resultante da análise de regressão descreve a relação entre as variáveis. r2 é igual a ssreg/sstotal.
    • Em alguns casos, uma ou mais colunas de X (supondo que os Ys e Xs estejam em colunas) podem ter valores previsíveis adicionais na presença de outras colunas de X. Em outras palavras, se forem eliminadas uma ou mais colunas de X, poderemos chegar a valores previsíveis de Y com a mesma precisão. Nesse caso, as colunas de X redundantes devem ser omitidas no modelo de regressão. Esse fenômeno é chamado de “colinearidade” porque qualquer coluna de X redundante pode ser expressa como uma soma dos múltiplos das colunas de X não-redundantes. PROJ.LIN verifica a colinearidade e remove as colunas de X redundantes do modelo de regressão quando as identifica. As colunas de X removidas podem ser reconhecidas no resultado de PROJ.LIN como tendo 0 coeficiente assim como 0 ses. Se uma ou mais colunas forem removidas como redundantes, df será afetada porque depende do número de colunas de X realmente utilizadas para fins previsíveis. Para obter mais detalhes sobre o cálculo de df, consulte o Exemplo 4 abaixo. Se df for alterada porque as colunas de X redundantes foram removidas, os valores de sey e F também serão afetados. Na prática, a colinearidde deverá ser relativamente rara. Contudo, um caso em que sua ocorrência será mais provável é quando algumas colunas de X contiverem somente 0s e 1s indicando se um dado é um experimento ou se não faz parte de um determinado grupo. Se const for igual a VERDADEIRO ou for omitido, PROJ.LIN irá inserir uma coluna de X adicional de 1s para modelar a interceptação. Se você tiver uma coluna com um 1 para cada dado se masculino, ou 0 se não for, você também terá uma coluna com um 1 para cada dado se for feminino e 0 se não for. Essa segunda coluna será redundante porque suas informações poderão ser obtidas pela subtração da entrada da coluna “indicador de masculino” da entrada da coluna adicional de 1s adicionada por LINEST.
    • df é calculada da seguinte maneira quando nenhuma coluna de X é removida do modelo devido à colinearidade: se houver k colunas de _x’s conhecidos e const = VERDADEIRO ou omitido, então df = n – k – 1. Se const = FALSO, então df = n - k. Em ambos os casos, cada coluna de X removida devido a colinearidade aumentará df em 1.
    • As fórmulas que fornecem matrizes devem ser inseridas como fórmulas de matriz.
    • Ao inserir a constante de uma matriz, por exemplo, valores_conhecidos_x como um argumento, use vírgulas para separar valores na mesma linha e pontos-e-vírgulas para separar linhas. Os caracteres separadores podem ser diferentes dependendo da configuração de localidade em Configurações regionais ou em Opções regionais no Painel de controle.
    • Você deve observar que os valores y estimados pela equação de regressão talvez não sejam válidos se estiverem fora do intervalo de valores y usado para determinar a equação.

    Exemplo 1   Inclinação e Ponto de origem y

    Talvez seja mais fácil entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    AB
    y conhecidox conhecido
    1 0
    9 4
    5 2
    7 3
    FórmulaFórmula
    =PROJ.LIN(A2:A5,B2:B5,,FALSO)  

    Observação  A fórmula no exemplo deve ser inserida como uma fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A7:B7 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será 2.

    Quando inserida como uma matriz, a inclinação (2) e o ponto de origem y (1) são retornados.

    Exemplo 2   Regressão linear simples

    Talvez seja mais fácil entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    6
    7
    AB
    MêsVendas
    1 3100
    2 4500
    3 4400
    4 5400
    5 7500
    6 8100
    FórmulaDescrição (resultado)
    =SOMA(PROJ.LIN(B2:B7, A2:A7)*{9,1}) Estimativa das vendas para o nono mês (11000)

    Em geral, SOMA({m,b}*{x,1}) é igual a mx + b, o valor y estimado para um determinado valor x. Você também pode usar a função TENDÊNCIA.

    Exemplo 3   Regressão linear múltipla

    Suponha que um empresário esteja pensando em comprar um grupo de prédios de salas comerciais em um bairro comercial.

    O empresário pode usar a análise de regressão linear múltipla para fazer uma estimativa do valor de um prédio em uma determinada área, de acordo com as variáveis a seguir.

    VariávelRefere-se a
    y Valor estimado do prédio
    x1 Área útil em metros quadrados
    x2 Número de salas
    x3 Número de entradas
    x4 Idade do prédio em anos

    Este exemplo considera que existe uma relação de linha reta entre cada variável independente (x1, x2, x3 e x4) e a variável dependente (y), o valor dos prédios comerciais no bairro.

    O empresário escolhe aleatoriamente uma amostra de 11 prédios comerciais a partir de um conjunto de 1500 prédios comerciais possíveis e obtém os seguintes dados. "Meia entrada" significa que o prédio só dispõe de uma entrada para entregas.

    Talvez seja mais fácil de entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    ABCDE
    Espaço do andar (x1)Escritórios (x2)Entradas (x3)Idade (x4)Valor avaliado (y)
    2310 2 2 20 142.000
    2333 2 2 12 144.000
    2356 3 1,5 33 151.000
    2379 3 2 43 150.000
    2402 2 3 53 139.000
    2425 4 2 23 169.000
    2448 2 1,5 99 126.000
    2471 2 2 34 142.900
    2494 3 3 23 163.000
    2517 4 4 55 169.000
    2540 2 3 22 149.000
    Fórmula
    =PROJ.LIN(E2:E12;A2:D12;VERDADEIRO;VERDADEIRO)

    Observação  A fórmula no exemplo deve ser inserida como uma fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A14:E18 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será -234,2371645.

    Quando inserida como uma matriz, as seguintes estatísticas de regressão são retornadas. Use esta chave para identificar a estatística desejada.

    Planilha

    A equação de regressão múltipla, y = m1*x1 + m2*x2 + m3*x3 - m4*x4 + b, pode ser obtida com os valores da linha 14:

    y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318

    Agora, o empresário poderá fazer uma estimativa do valor de um prédio na mesma área com aproximadamente 233 metros quadrados, três salas comerciais e duas entradas, e que tem 25 anos de idade, usando a seguinte equação:

    y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = $158.261

    Ou então, você pode copiar a seguinte tabela para a célula A21 da pasta de trabalho de exemplo.

    Espaço do andar (x1)Escritórios (x2)Entradas (x3)Idade (x4)Valor avaliado (y)
    2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

    Você também pode usar a função TENDÊNCIA para calcular este valor.

    Exemplo 4   Usando as estatísticas F e r2

    No exemplo anterior, o coeficiente de determinação, ou r2, é 0,99675 (consulte a célula A17 na saída de PROJ.LIN), o que indica uma forte relação entre as variáveis independentes e o preço de venda. Você pode usar a estatística F para determinar se esses resultados com um valor de r2 tão alto ocorreram por acaso.

    Suponha que, na verdade, não há relação entre as variáveis, mas que você selecionou uma amostra rara de 11 prédios que fará com que a análise estatística demonstre uma forte relação. O termo "Alfa" é usado para indicar a probabilidade de se concluir de forma incorreta a existência de uma relação.

    F e df no resultado de LINEST pode ser usado para avaliar a probabilidade de ocorrer um valor mais alto de F por acaso. F pode ser comparado a valores críticos em tabelas de distribuição F publicadas ou o FDIST do Excel pode ser usado para calcular a probabilidade de ocorrer um valor de F maior por acaso. A distribuição apropriada de F possui graus v1 e v2 de liberdade. Se n for o número de pontos de dados e const = VERDADEIRO ou omitido, então v1 = n – df – 1 e v2 = df. (Se const = FALSO, então v1 = n – df and v2 = df.) O FDIST(F,v1,v2) do Excel retornará a probabilidade de ocorrer um valor de F mais alto por acaso. No Exemplo 4, df = 6 (célula B18) e F = 459.753674 (célula A18).

    Supondo um valor de Alpha igual a 0.05, v1 = 11 – 6 – 1 = 4 e v2 = 6, o nível crítico de F e 4.53. Como F = 459.753674 é muito mais alto do que 4.53, é extremamente improvável que um valor de F tão alto tenha ocorrido por acaso (Com Alpha = 0.05, a hipótese de que não há relação entre os y’s conhecidos e os x’s conhecidos deve ser rejeitada quando F excede o nível crítico, 4.53.) Utilizando o FDIST do Excel você pode obter a probabilidade de que um valor F dessa magnitude tenha ocorrido por acaso. FDIST(459.753674, 4, 6) = 1.37E-7, uma probabilidade extremamente baixa. Você pode concluir, seja encontrando o nível crítico de F em uma tabela ou usando FDIST do Excel, que a equação de regressão é útil na previsão do valor avaliado de prédios comerciais nesta área. Lembre-se que é crítica a utilização dos valores corretos de v1 e v2 calculados no parágrafo anterior.

    Exemplo 5   Calculando o dado estatístico t

    Outro teste hipotético pode determinar se um coeficiente de inclinação é útil para prever o valor estimado de um prédio no exemplo 3. Por exemplo, para testar o coeficiente de idade para o valor estatístico, divida -234,24 (coeficiente de idade da inclinação) por 13,268 (o valor de erro estimado para os coeficientes de idade na célula A15). A equação a seguir representa o valor de t observado:

    t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

    Se o valor absoluto de t for suficientemente alto, poderemos concluir que que o eficiente da curva é útil na estimativa do valor avaliado de um prédio comercial no Exemplo 3. A tabela abaixo mostra os valores absolutos dos 4 valores observados de t.

    Se consultar uma tabela em um manual de estatística, você descobrirá que o valor t crítico e bicaudal com 6 graus de liberdade e Alfa = 0,05 é 2,447. Este valor crítico também pode ser obtido por meio da função TINV do Excel. TINV(0.05,6) = 2.447. Na medida em que o valor t absoluto 17,7 é maior que 2,447, a idade será uma variável importante para prever o valor estimado de um prédio. Cada uma das outras variáveis independentes pode ser testada para valor estatístico de maneira semelhante. Na tabela a seguir, encontram-se os valores t observados para cada variável independente:

    VariávelValor t observado
    Área útil 5,1
    Número de salas 31,3
    Número de entradas 4,8
    Idade 17,7

    Todos esses valores apresentam um valor absoluto maior que 2,447; dessa forma, todas as variáveis usadas na equação de regressão são úteis para prever o valor estimado dos prédios dessa área.


    Melhor que dar o peixe é ensinar a pescar

    Daniel Yamaguti
    Gerente de TI
    domingo, 21 de fevereiro de 2010 00:24
  • Esse outro é quando clico em mostrar tudo


    Mostrar tudoOcultar tudo

    PROJ.LIN

    <object id="hhobj_1" classid="clsid:adb880a6-d8ff-11cf-9377-00aa003b7a11" width="14" height="14" type="application/x-oleobject"> <param name="Width" value="0" /> <param name="Height" value="0" /> <param name="Command" value="ALink" /> <param name="Item1" /> <param name="Item2" value="xlfctLINESTC" /> </object>

    Consulte também

    Calcula as estatísticas para uma linha usando o método de "mínimos quadrados" para calcular uma linha reta com o ajuste perfeito para seus dados e retorna uma matriz que descreve a linha. Como dessa função retorna uma matriz de valores, ela deve ser inserida como uma fórmula de matriz.

    A equação para a linha é:

    y = mx + b ou

    y = m1x1 + m2x2 + ... + b (se existirem múltiplos intervalos de valores x)

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

    Sintaxe

    PROJ.LIN(val_conhecidos_y;val_conhecidos_x;constante;estatística)

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

    • 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 uma única linha, cada linha de val_conhecidos_x será interpretada como uma variável separada.

    Val_conhecidos_x    é um conjunto opcional de valores x que você já deve conhecer na relação y = mx + b.

    • 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.

    Constante    é um valor lógico que força ou não a constante b a se igualar a 0.

    • Se constante for VERDADEIRO ou omitido, b será calculado normalmente.

    • Se constante for FALSO, b será definido como 0 e os valores m serão ajustados para que y = mx.

    Estatística    é o valor lógico que especifica se estatísticas de regressão adicionais serão retornadas.

    • Se estatística for VERDADEIRO, PROJ.LIN retornará as estatísticas de regressão adicionais, de forma que a matriz retornada seja {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Se estatística for FALSO ou omitida, PROJ.LIN retornará apenas os coeficientes m e a constante b.

    As estatísticas de regressão adicionais são:

    EstatísticasDescrição
    se1,se2,...,sen Os valores de erro padrão para os coeficientes m1,m2,...,mn.
    seb Os valores de erro padrão para a constante b (seb = #N/D quando constante é FALSO).
    r2 O coeficiente de determinação. Compara valores y estimados e reais e varia de 0 a 1. Se for igual a 1, existirá uma correlação perfeita na amostra — não há diferença entre os valores y estimados e os valores y reais. Por outro lado, se o coeficiente de determinação for igual a 0, a equação de regressão não terá utilidade para prever um valor y. Para obter informações sobre como r2 é calculado, consulte "Comentários" mais adiante neste tópico.
    sey O erro padrão para a estimativa y.
    F A estatística F ou o valor F observado. Use a estatística F para determinar se a relação observada entre as variáveis dependentes e independentes ocorre por acaso.
    df Os graus de liberdade. Use os graus de liberdade para ajudá-lo a encontrar os valores F críticos em uma tabela estatística. Compare os valores encontrados na tabela com a estatística F retornada por PROJ.LIN para determinar um nível de confiança para o modelo. Para obter mais informações sobre o cálculo de df, consulte " Comentários" mais à frente, neste tópico. O exemplo 4 apresentado abaixo mostra a utilização de F e de df.
    ssreg A soma de regressão dos quadrados.
    ssresid A soma residual dos quadrados. Para obter informações sobre como ssreg e ssresid são calculadas, consulte "Comentários" mais à frente, neste tópico.

    A ilustração a seguir mostra a ordem em que as estatísticas adicionais são fornecidas.

    Planilha

    Comentários

    • Você pode descrever qualquer linha reta com a inclinação e o ponto de origem y:

      Inclinação (m):
      Para calcular a inclinação de uma linha, freqüentemente representada por m, use dois pontos da linha, (x1,y1) e (x2,y2); a inclinação será igual a (y2 - y1)/(x2 - x1).

      Ponto de origem y (b):
      O ponto de origem y de uma linha, freqüentemente representado por b, é o valor y no ponto em que a linha cruza o eixo y.

      A equação de uma linha reta é y = mx + b. Uma vez fornecidos os valores de m e b, você poderá calcular qualquer ponto da linha inserindo o valor y ou x nessa equação. Você também pode usar a função TENDÊNCIA.

    • Quando tiver apenas uma variável x independente, você poderá obter os valores de inclinação e de ponto de origem y diretamente, usando as seguintes fórmulas:

      Inclinação:
      =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);1)

      Ponto de origem y:
      =ÍNDICE(PROJ.LIN(val_conhecidos_y;val_conhecidos_x);2)

    • A precisão da linha calculada por PROJ.LIN depende do grau de dispersão dos dados. Quanto mais lineares forem os dados, mais preciso será o modelo de PROJ.LIN. PROJ.LIN usa o método dos mínimos quadrados para determinar o ajuste perfeito dos dados. Quando você tiver apenas uma variável x independente, os cálculos para m e b serão baseados nas seguintes fórmulas:

      Equação

      Equação

      onde x and y são exemplos de média, ou seja, x = MÉDIA(val_conhecidos_x) e y = MÉDIA(val_conhecidos_y).

    • As funções de ajuste de linha e de curva PROJ.LIN e PROJ.LOG podem calcular a melhor linha reta ou curva exponencial que se ajustem aos dados. No entanto, você deverá decidir qual dos dois resultados melhor se adapta aos dados. Você pode 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 dos valores y estimados ao longo da linha ou curva nos pontos de dados reais. Dessa forma, você poderá comparar os valores previstos com os valores reais. Além disso, é possível representá-los graficamente para uma comparação visual.
    • Na análise de regressão, o Microsoft Excel calcula a diferença de quadrados entre o valor y estimado e o valor y real para cada ponto. A soma dessas diferenças de quadrados é chamada de soma dos quadrados de resíduo, ssresid. O Microsoft Excel calcula a soma total dos quadrados. Quando const for igual a VERDADEIRO ou for omitido, a soma total dos quadrados será a soma das diferenças dos quadrados entre os valores y reais e a média dos valores y. Quando const for igual a FALSO, a soma total dos quadrados será a soma de quadrados dos próprios valores de y (sem subtrair a média dos valores de y de cada valor de y individual). A soma da regressão dos quadrados, ssreg, pode ser obtida de: ssreg = sstotal - ssresid. Quanto menor for a soma de quadrados de resíduo, comparada com a soma de quadrados total, maior será o valor do coeficiente de determinação, r2, que indica a precisão com que a equação resultante da análise de regressão descreve a relação entre as variáveis. r2 é igual a ssreg/sstotal.
    • Em alguns casos, uma ou mais colunas de X (supondo que os Ys e Xs estejam em colunas) podem ter valores previsíveis adicionais na presença de outras colunas de X. Em outras palavras, se forem eliminadas uma ou mais colunas de X, poderemos chegar a valores previsíveis de Y com a mesma precisão. Nesse caso, as colunas de X redundantes devem ser omitidas no modelo de regressão. Esse fenômeno é chamado de “colinearidade” porque qualquer coluna de X redundante pode ser expressa como uma soma dos múltiplos das colunas de X não-redundantes. PROJ.LIN verifica a colinearidade e remove as colunas de X redundantes do modelo de regressão quando as identifica. As colunas de X removidas podem ser reconhecidas no resultado de PROJ.LIN como tendo 0 coeficiente assim como 0 ses. Se uma ou mais colunas forem removidas como redundantes, df será afetada porque depende do número de colunas de X realmente utilizadas para fins previsíveis. Para obter mais detalhes sobre o cálculo de df, consulte o Exemplo 4 abaixo. Se df for alterada porque as colunas de X redundantes foram removidas, os valores de sey e F também serão afetados. Na prática, a colinearidde deverá ser relativamente rara. Contudo, um caso em que sua ocorrência será mais provável é quando algumas colunas de X contiverem somente 0s e 1s indicando se um dado é um experimento ou se não faz parte de um determinado grupo. Se const for igual a VERDADEIRO ou for omitido, PROJ.LIN irá inserir uma coluna de X adicional de 1s para modelar a interceptação. Se você tiver uma coluna com um 1 para cada dado se masculino, ou 0 se não for, você também terá uma coluna com um 1 para cada dado se for feminino e 0 se não for. Essa segunda coluna será redundante porque suas informações poderão ser obtidas pela subtração da entrada da coluna “indicador de masculino” da entrada da coluna adicional de 1s adicionada por LINEST.
    • df é calculada da seguinte maneira quando nenhuma coluna de X é removida do modelo devido à colinearidade: se houver k colunas de _x’s conhecidos e const = VERDADEIRO ou omitido, então df = n – k – 1. Se const = FALSO, então df = n - k. Em ambos os casos, cada coluna de X removida devido a colinearidade aumentará df em 1.
    • As fórmulas que fornecem matrizes devem ser inseridas como fórmulas de matriz.
    • Ao inserir a constante de uma matriz, por exemplo, valores_conhecidos_x como um argumento, use vírgulas para separar valores na mesma linha e pontos-e-vírgulas para separar linhas. Os caracteres separadores podem ser diferentes dependendo da configuração de localidade em Configurações regionais ou em Opções regionais no Painel de controle.
    • Você deve observar que os valores y estimados pela equação de regressão talvez não sejam válidos se estiverem fora do intervalo de valores y usado para determinar a equação.

    Exemplo 1   Inclinação e Ponto de origem y

    Talvez seja mais fácil entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    AB
    y conhecidox conhecido
    1 0
    9 4
    5 2
    7 3
    FórmulaFórmula
    =PROJ.LIN(A2:A5,B2:B5,,FALSO)  

    Observação  A fórmula no exemplo deve ser inserida como uma fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A7:B7 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será 2.

    Quando inserida como uma matriz, a inclinação (2) e o ponto de origem y (1) são retornados.

    Exemplo 2   Regressão linear simples

    Talvez seja mais fácil entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    6
    7
    AB
    MêsVendas
    1 3100
    2 4500
    3 4400
    4 5400
    5 7500
    6 8100
    FórmulaDescrição (resultado)
    =SOMA(PROJ.LIN(B2:B7, A2:A7)*{9,1}) Estimativa das vendas para o nono mês (11000)

    Em geral, SOMA({m,b}*{x,1}) é igual a mx + b, o valor y estimado para um determinado valor x. Você também pode usar a função TENDÊNCIA.

    Exemplo 3   Regressão linear múltipla

    Suponha que um empresário esteja pensando em comprar um grupo de prédios de salas comerciais em um bairro comercial.

    O empresário pode usar a análise de regressão linear múltipla para fazer uma estimativa do valor de um prédio em uma determinada área, de acordo com as variáveis a seguir.

    VariávelRefere-se a
    y Valor estimado do prédio
    x1 Área útil em metros quadrados
    x2 Número de salas
    x3 Número de entradas
    x4 Idade do prédio em anos

    Este exemplo considera que existe uma relação de linha reta entre cada variável independente (x1, x2, x3 e x4) e a variável dependente (y), o valor dos prédios comerciais no bairro.

    O empresário escolhe aleatoriamente uma amostra de 11 prédios comerciais a partir de um conjunto de 1500 prédios comerciais possíveis e obtém os seguintes dados. "Meia entrada" significa que o prédio só dispõe de uma entrada para entregas.

    Talvez seja mais fácil de entender o exemplo se você copiá-lo para uma planilha em branco.

    MostrarComo?

    1. Crie uma pasta de trabalho ou planilha em branco.
    2. Selecione o exemplo no tópico da Ajuda. Não selecione os cabeçalhos de linha ou de coluna. 

      Selecionando um exemplo na <b>Ajuda</b>

      Selecionando um exemplo na Ajuda

    3. Pressione CTRL+C.
    4. Na planilha, selecione a célula A1 e pressione CTRL+V.
    5. Para alternar entre exibir os resultados e exibir as fórmulas que retornam os resultados, pressione CTRL+` (acento grave) ou, no menu Ferramentas, aponte para Auditoria de fórmulas e clique em Modo de auditoria de fórmulas.
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    ABCDE
    Espaço do andar (x1)Escritórios (x2)Entradas (x3)Idade (x4)Valor avaliado (y)
    2310 2 2 20 142.000
    2333 2 2 12 144.000
    2356 3 1,5 33 151.000
    2379 3 2 43 150.000
    2402 2 3 53 139.000
    2425 4 2 23 169.000
    2448 2 1,5 99 126.000
    2471 2 2 34 142.900
    2494 3 3 23 163.000
    2517 4 4 55 169.000
    2540 2 3 22 149.000
    Fórmula
    =PROJ.LIN(E2:E12;A2:D12;VERDADEIRO;VERDADEIRO)

    Observação  A fórmula no exemplo deve ser inserida como uma fórmula de matriz. Após copiar o exemplo para uma planilha em branco, selecione o intervalo A14:E18 iniciando com a célula de fórmula. Pressione F2 e, em seguida, pressione CTRL+SHIFT+ENTER. Se a fórmula não for inserida como uma fórmula de matriz, o único resultado será -234,2371645.

    Quando inserida como uma matriz, as seguintes estatísticas de regressão são retornadas. Use esta chave para identificar a estatística desejada.

    Planilha

    A equação de regressão múltipla, y = m1*x1 + m2*x2 + m3*x3 - m4*x4 + b, pode ser obtida com os valores da linha 14:

    y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318

    Agora, o empresário poderá fazer uma estimativa do valor de um prédio na mesma área com aproximadamente 233 metros quadrados, três salas comerciais e duas entradas, e que tem 25 anos de idade, usando a seguinte equação:

    y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = $158.261

    Ou então, você pode copiar a seguinte tabela para a célula A21 da pasta de trabalho de exemplo.

    Espaço do andar (x1)Escritórios (x2)Entradas (x3)Idade (x4)Valor avaliado (y)
    2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

    Você também pode usar a função TENDÊNCIA para calcular este valor.

    Exemplo 4   Usando as estatísticas F e r2

    No exemplo anterior, o coeficiente de determinação, ou r2, é 0,99675 (consulte a célula A17 na saída de PROJ.LIN), o que indica uma forte relação entre as variáveis independentes e o preço de venda. Você pode usar a estatística F para determinar se esses resultados com um valor de r2 tão alto ocorreram por acaso.

    Suponha que, na verdade, não há relação entre as variáveis, mas que você selecionou uma amostra rara de 11 prédios que fará com que a análise estatística demonstre uma forte relação. O termo "Alfa" é usado para indicar a probabilidade de se concluir de forma incorreta a existência de uma relação.

    F e df no resultado de LINEST pode ser usado para avaliar a probabilidade de ocorrer um valor mais alto de F por acaso. F pode ser comparado a valores críticos em tabelas de distribuição F publicadas ou o FDIST do Excel pode ser usado para calcular a probabilidade de ocorrer um valor de F maior por acaso. A distribuição apropriada de F possui graus v1 e v2 de liberdade. Se n for o número de pontos de dados e const = VERDADEIRO ou omitido, então v1 = n – df – 1 e v2 = df. (Se const = FALSO, então v1 = n – df and v2 = df.) O FDIST(F,v1,v2) do Excel retornará a probabilidade de ocorrer um valor de F mais alto por acaso. No Exemplo 4, df = 6 (célula B18) e F = 459.753674 (célula A18).

    Supondo um valor de Alpha igual a 0.05, v1 = 11 – 6 – 1 = 4 e v2 = 6, o nível crítico de F e 4.53. Como F = 459.753674 é muito mais alto do que 4.53, é extremamente improvável que um valor de F tão alto tenha ocorrido por acaso (Com Alpha = 0.05, a hipótese de que não há relação entre os y’s conhecidos e os x’s conhecidos deve ser rejeitada quando F excede o nível crítico, 4.53.) Utilizando o FDIST do Excel você pode obter a probabilidade de que um valor F dessa magnitude tenha ocorrido por acaso. FDIST(459.753674, 4, 6) = 1.37E-7, uma probabilidade extremamente baixa. Você pode concluir, seja encontrando o nível crítico de F em uma tabela ou usando FDIST do Excel, que a equação de regressão é útil na previsão do valor avaliado de prédios comerciais nesta área. Lembre-se que é crítica a utilização dos valores corretos de v1 e v2 calculados no parágrafo anterior.

    Exemplo 5   Calculando o dado estatístico t

    Outro teste hipotético pode determinar se um coeficiente de inclinação é útil para prever o valor estimado de um prédio no exemplo 3. Por exemplo, para testar o coeficiente de idade para o valor estatístico, divida -234,24 (coeficiente de idade da inclinação) por 13,268 (o valor de erro estimado para os coeficientes de idade na célula A15). A equação a seguir representa o valor de t observado:

    t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

    Se o valor absoluto de t for suficientemente alto, poderemos concluir que que o eficiente da curva é útil na estimativa do valor avaliado de um prédio comercial no Exemplo 3. A tabela abaixo mostra os valores absolutos dos 4 valores observados de t.

    Se consultar uma tabela em um manual de estatística, você descobrirá que o valor t crítico e bicaudal com 6 graus de liberdade e Alfa = 0,05 é 2,447. Este valor crítico também pode ser obtido por meio da função TINV do Excel. TINV(0.05,6) = 2.447. Na medida em que o valor t absoluto 17,7 é maior que 2,447, a idade será uma variável importante para prever o valor estimado de um prédio. Cada uma das outras variáveis independentes pode ser testada para valor estatístico de maneira semelhante. Na tabela a seguir, encontram-se os valores t observados para cada variável independente:

    VariávelValor t observado
    Área útil 5,1
    Número de salas 31,3
    Número de entradas 4,8
    Idade 17,7

    Todos esses valores apresentam um valor absoluto maior que 2,447; dessa forma, todas as variáveis usadas na equação de regressão são úteis para prever o valor estimado dos prédios dessa área.


    Melhor que dar o peixe é ensinar a pescar

    Daniel Yamaguti
    Gerente de TI
    domingo, 21 de fevereiro de 2010 00:25
  • Ola Daniel,

    O que eu quis dizer foi aonde voce encontra uma referencia sobre a formula que te auxilie a criar o código, o help eu também tenho disponivel aqui no excel.

    Se tiver alguma dúvida sobre o código VBA, podemos ajudar.

    atc

    Ronaldo
    quinta-feira, 25 de fevereiro de 2010 23:52
  • Daniel,

    Caso for utilizar uma consulta no modo visual utilize para

    proj.lin -> =(soma([campo_x]*[campo_y])-soma([campo_x])*soma([campo_y]))/(soma([campo_x]*[campo_x])-soma([campo_x])*soma([campo_x]))

    e

    Indice -> =(soma([campo_y]-(soma([campo_x])*(soma([campo_x]*[campo_y])-soma([campo_x])*soma([campo_y]))/(soma([campo_x]*[campo_x])-soma([campo_x])*soma([campo_x])))/contar([campo_x])

    No VBA utilize de varios SQL e variaveis para calcular a covariancia e a variancia, assim cacule

    indice = covariancia/variancia

    e

    proj.lin = media de y - indice * media de x

    Espero ter ajudado.



    quarta-feira, 17 de março de 2010 19:33
  • Daniel,

    desculpe mas me equivoquei no post anterior.

    Segue novamente, agora testado.

    proj_lin: ((contar([campo_x])*soma([campo_x]*[campo_y]))-soma([campo_x])*soma([campo_y]))/((contar([campo_x])*soma([campo_x]*[campo_x]))-soma([campo_x])*soma([campo_x]))


    e


    Indice: (soma([campo_y])-(soma([campo_x])*((contar([campo_x])*soma([campo_x]*[campo_y]))-soma([campo_x])*soma([campo_y]))/((contar([campo_x])*soma([campo_x]*[campo_x]))-soma([campo_x])*soma([campo_x]))))/contar([campo_x])

    quarta-feira, 17 de março de 2010 19:53