Aplicação Estatística em Excel: Capítulo 3 - Dados Bivariados, Notas de estudo de Estatística

Baixe Aplicação Estatística em Excel: Capítulo 3 - Dados Bivariados e outras Notas de estudo em PDF para Estatística, somente na Docsity!

IMES Catanduva

Probabilidades e Estatística

Estatística Aplicada

no Excel

Matemática

Bertolo, L.A.

Versão BETA Maio 2010

Bertolo Estatística Aplicada no Excel

Capítulo 3 – Dados Bivariados

São pares de valores correspondente a um dado indivíduo ou resultado experimental. Para ilustrar o estudo de dados bivariados, recorreu-se ao Diagramas exemplo de altura (cm) e peso (kg) de 10 alunos do curso de Ciência da Computação do IMES-FAFICA.

2.1 – Diagrama de Dispersão ou de Espalhamento (scatter plot)

É uma representação gráfica para os dados bivariados, em que cada par de dados (xi, yi) é representado por um ponto de coordenadas (xi, yi), num sistema de eixos cartesianos.

Pode-se obter com facilidade a representação gráfica de dados bivariados, através do Assistente de Gráficos [ Chart Wizard ].

Comece por selecionar as células contendo os dados e os respectivos títulos e clique no ícone da

Barra de ferramentas.

Na primeira Caixa de diálogo selecione a opção Dispersão (xy).

Para continuar a construção do gráfico, e para passar ao Passo seguinte, clique no botão Seguinte >.

Bertolo Estatística Aplicada no Excel

2.2 – Covariancia e Correlação

Nós usamos regressão e correlação para descrever a variação em uma ou mais variáveis.

A. A variação é a soma dos desvios quadrados de uma variável de sua média.

Variação = �(x − x�) 2

N

i=

B. A variação é o numerador da variância de uma amostra:

Variância =

∑ Ni=1(x − x�) 2 N − 1

C. Ambas, a variação e a variância são medidas de dispersão de uma amostra, já estudadas.

2.2.1 – A Covariância

A covariância entre duas variáveis aleatórias é uma medida estatística do grau para o qual as duas variáveis se movem juntas.

A. A covariância captura o quanto uma variável fica diferente da sua média quando a outra variável ficar diferente da sua média.

B. Uma covariância positiva indica que as variáveis tendem a se moverem juntas; uma covariância negativa indica que as variáveis tendem a se moverem em direções opostas.

C. A covariância é calculada como a razão da co-variação pelo tamanho da amostra menos um

2.2.2 – A função COVAR do Excel

O Excel disponibiliza uma função embutida chamada COVAR que retorna a covariância, a média dos produtos dos desvios de cada par de ponto de dados em dois conjuntos de dados.

A sua sintaxe é:

COVAR(matriz1; matriz2)

Covariância =

∑ Ni=1(x (^) i − x�)(yi − y�) N − 1

onde N é o tamanho da amostra x i é a i-ésima observação da variável x, 𝑥𝑥̅ é a média das observações da variável x, yi é a i-ésima observação da variável y, e 𝑦𝑦� é a média das observações da variável y.

D. O valor real da covariância não é significante porque ele não é afetado pela a escala das duas variáveis. Isto é o porquê de se calcular o coeficiente de correlação – para tornar algo interpretável da informação da covariância.

2.2.3 – Exemplo 1 – Usando a função COVAR do Excel

Com os dados dos Pesos e Alturas da 10 feras do curso de Ciência da Computação (incluindo o Aderbal, por que não? Ele é uma fera ferida!!!!) encontre a covariância entre as grandezas peso e altura. Para tanto vá à célula C2 e digite =COVAR(A2:A11;B2:B11). O valor encontrado será:

Exemplo1: Preços de vendas de casas e pés quadrados Preços de venda de casas (eixo vertical) v. pés quadrados para uma amostra de 34 casas em Setembro de 2005 em St. Lucie County.

Estatística Aplicada no Excel Bertolo

2.2.4 – Coeficiente de Correlação

O coeficiente de correlação , r , é uma medida da intensidade da relação

Cálculo:

r =

covari ância entre x e y �Desviode x^ padr ão��Desviode y^ padr ão�

r =

�∑^ (x (^) i − x�)(yi − y�)

N

i=1 � N − 1

� ∑ Ni=1(x (^) i − x�)^2 N − 1

� ∑ Ni=1(yi − y�)^2 N − 1

entre ou dentre as variáveis.

2.2.5 – Exemplo 2

A B C D E F

Peso (kg) Altura (cm)

72 175 63,44 <--=COVAR(A2:A11;B2:B11)

A B C D E F G H

Observação (^) x y

Desvio de x x - xMédio

Desvio Quadrado de x (x - xMédio ) 2

Desvio de y y - y (^) Médio

Desvio Quadrado de y (y - y (^) Médio ) 2

Produto dos desvios (x - xMédio )(y - y (^) Médio ) 1 12 50 -1,50 2,25 8,40 70,56 -12, 2 13 54 -0,50 0,25 12,40 153,76 -6, 3 10 48 -3,50 12,25 6,40 40,96 -22, 4 9 47 -4,50 20,25 5,40 29,16 -24, 5 20 70 6,50 42,25 28,40 806,56 184, 6 7 20 -6,50 42,25 -21,60 466,56 140, 7 4 15 -9,50 90,25 -26,60 707,56 252, 8 22 40 8,50 72,25 -1,60 2,56 -13, 9 15 35 1,50 2,25 -6,60 43,56 -9, 10 23 37 9,50 90,25 -4,60 21,16 -43, Soma 135 416 0,00 374,50 0,00 2342,40 445, Cálculos x (^) Médio= (^) 135/10 = 13, y (^) Médio= (^) 416/10 = 41, s (^2) x= (^) 374,5/9 = 41, s (^2) y = (^) 2.342,4/9 = 260, r = (445/9)/((41,611) 1/2^ (260,267) 1/2^ ) = 49,444/(6,451*16,133) = 0,

Nota: A correlação não implica que um causa o outro. Podemos dizer que duas variáveis X e Y estão correlacionadas, mas não que X causa Y ou que Y causa X, na média – eles simplesmente estão relaciona- dos ou associados um com o outro.

Estatística Aplicada no Excel Bertolo

Clicando o botão OK aparecerá uma nova janela:

2.3 – Regressão Linear Simples

Regressão é a análise da relação entre uma variável e alguma outra variável(s), assumindo uma relação linear. Também referida como regressão dos mínimos quadrados e mínimos quadrados ordinários ( ordinary least squares - OLS ).

Isto acontece quando a correlação entre as duas variáveis é elevada (quer seja positiva, quer seja negativa), isso significa que se conhecer o valor de uma das variáveis, então é possível ter uma idéia do valor que a outra variável irá tomar. Em linguagem estatística, diz-se que se pode inferir o valor de outra variável.

A. O propósito é explicar a variação numa variável (isto é, como uma variável difere do seu valor médio) usando a variação em uma ou outras mais variáveis. B. Suponha que queremos descrever, explicar, ou predizer porque uma variável difere de sua média. Seja a i- ésima observação desta variável representada como Yi, e seja n indicando o número de observações. A variação nos Yi's (os quais queremos explicar) é:

Variação do Y

= �(yi − y�)^2 = SS (^) Total

N

i=

C. O princípio dos mínimos quadrados é que a linha de regressão é determinada minimizando a soma dos quadrados das distâncias verticais entre os valores reais de Y e os valores previstos de Y.

Assinale a caixa de verificação Ferramentas de Análise. Faça isto sempre para carregar os suplementos que às vezes podem não estar instalados. A seguir vá a guia Dados e no grupo Análise (que agora está ativado) clique em Análise de Dados para aparecer a janela:

Configure nesta janela a Entrada dos dados, o Agrupamento, se deseja ou não os Rótulos na primeira linha e as Opções de saída. Faça tudo como mostra a figura. Depois aperte o botão OK e terás:

Bertolo Estatística Aplicada no Excel

Voltando ao exemplo das alturas e dos pesos das feras e ao seu diagrama de dispersão, pode-se observar uma associação linear entre o peso e a altura. Será que é possível prever a altura de um aluno que pese 70 kg?

Quando perante uma situação análoga, em que tenhamos um conjunto de dados bivariados (xi, yi), i=1, ..., n, que seguem um padrão linear, poderá ter interesse ajustar uma reta da forma:

y = a + bx

que dê a informação de como se refletem em y, as mudanças processadas em x.

2.3.1 – O Exemplo 1 – Brincando com os dados

Retomando o exemplo, prepare uma tabela idêntica à que se apresenta. Os valores do Ajuste, do Desvio e do Desvio^2 , poderão ser calculados com as seguintes expressões:

- Ajuste (y) 1º valor ( célula E2 )

=$A$3+C2*$A$

Copie esta expressão para as células E3 a E.

- Desvio 1º valor ( célula F2 )

=D2-E

Copie esta expressão para as células F3 a F.

- Desvio 2 1º valor ( célula G2 ) =F2^ Copie esta expressão para as células G3 a G.

Uma linha é um ajuste através dos pontos XY tal que a soma dos resíduos quadráticos (isto é, a soma dos quadrados da distância vertical entre as observações e a linha) seja minimizada.

Bertolo Estatística Aplicada no Excel

Selecionando o diagrama, clique no menu Gráfico , selecione o comando Adicionar linha de tendência e siga as opções.

A equação desta reta traduz-se em:

Altura = 109,36 + 0,9016 x Peso

Substituindo na equação o Peso por 70, obtém-se o valor de 172,472, pelo que a altura esperada para um aluno que pese 70 kg , é de cerca de 172,5 cm.

2.3.3 – Coeficiente de determinação R^2

O coeficiente de determinação , R^2 , é a porcentagem da variação da variável dependente (variação dos Yi 's ou a soma dos quadrados total, SST) explicada pela variável independente(s).

A. O coeficiente de determinação é calculado como:

R^2 =

Variação explicada Variação total = Variação total −Variação explicada Variação total =

SS (^) Total − SS (^) Residual SS (^) Total = SS (^) Regressão SS (^) Total

Voltando ao exemplo 2.2.5 temos: Observe que: (20-4) + (20-15) + (20 – 24) + (20 – 27) + (

• 30. = 0

Observação x y ^y y-^y e^2 1 12 50 39,82 10,18 103, 2 13 54 41,01 12,99 168, 3 10 48 37,44^ 10,56 111, 4 9 47 36,25 10,75 115, 5 20 70 49,32 20,68 427, 6 7 20 33,88 -13,88 192, 7 4 15 30,31 -15,31 234, 8 22 40 51,70^ -11,70 136, 9 15 35 43,38 -8,38 70, 10 23 37 52,89 -15,89 252, 0,00 1.813,

Estatística Aplicada no Excel Bertolo

B. Um R^2 de 0,49 indica que as variáveis independentes explicam 49% da variação da variável dependente.

2.4 – Trabalho Final

Parte A –

a. Fazer a mesma coisa da seção 2.2.3 para os dados do exemplo 2 b. Faça mesma coisa da seção 22.7 para os dados do exemplo 2 c. Faça mesma coisa da seção 22.8 para os dados do exemplo 2

Parte B –

Faça a mesma coisa da seção 2.3.2 – Regressão Linear Simples para os dados do exemplo 2, encontrando no final a equação da reta. Resposta yi = 25,559 + 1,188 x (^) i

Parte C –

Dada a amostra da planilha abaixo: Análise de precificação de casas, repita os exercícios 1 e 2 e a secção 2.3.3 (coeficiente de determinação R^2 )