Proced i Mentos Bi Dimensional Excel 2003

Anlise Bidimensional de Variveis Quantitativas usando o Microsoft Excel

1

Na coluna A encontram-se os valores de Temperatura, e na coluna B

os das Vendas. preciso identificar corretamente qual varivel a

independente e qual a dependente: caso contrrio o diagrama

estar completamente errado, o modelo eventualmente ajustado

tambm, e as decises tomadas com base neles pouca validade tero.

razovel imaginar que a Temperatura possa influenciar as Vendas

de refrigerante: maiores valores de Temperatura poderiam causar

maiores valores de Vendas. Sendo assim, Temperatura ser a

varivel independente, sendo ento representada no eixo X, e

Vendas a varivel dependente, ocupando o eixo Y.

Passamos agora a construo do diagrama de disperso

propriamente dito, clicando sobre o cone "Assistente Grfico", na

barra de ferramentas do Excel, resultando na figura 2. Selecionando

o grfico Disperso (XY), obtemos a figura 3.

INE 7001 - Procedimentos de Anlise Bidimensional de variveis QUANTITATIVAS utilizando o

Microsoft Excel.

Professor Marcelo Menezes Reis

O objetivo deste texto apresentar os principais procedimentos de Anlise Bidimensional de

variveis quantitativas, tal como apresentados em sala, mas utilizando a planilha eletrnica Excel.

Os dados esto na planilha "Temperatura e vendas", do arquivo Bidimensional.xls, disponvel nas

pginas das disciplinas: contm as informaes sobre 250 pares de observaes temperatura (em

graus Celsius) e quantidade vendida de refrigerantes.

Os procedimentos foram preparados utilizando a verso 2003 do Excel. H algumas

diferenas em relao s verses mais modernas (2007, 2010), mas a essncia permanece a mesma.

1. Construo de diagrama de disperso para as variveis.

No presente caso, em que h apenas 2 variveis, possvel construir um diagrama de

disperso, relacionando temperatura e vendas. O objetivo avaliar a fora, a direo e a forma de

uma eventual correlao entre elas: com isso ser possvel avaliar qual modelo de regresso aplicar

para prever os valores de uma varivel em funo dos da outra. Os dados de interesse esto

mostrados na figura 1:

Figura 1 - Temperatura e vendas

Figura 2 - Assistente grfico - 1a etapa Figura 3 - Assistente grfico - Diagrama de disperso

Para os nossos interesses o subtipo mais interessante o padro, marcado em preto na figura 3.

Pressionando "Avanar" chegaremos a uma tela semelhante figura 4.


2

Figura 4 - Assistente grfico - 2a etapa

Figura 5 - Assistente grfico: adio de seqncias

Figura 6 - Assistente grfico: dados inseridos

Em alguns casos o Excel

automaticamente adiciona as seqncias

de dados necessrias para criar o

grfico. Muitas vezes estas seqncias

incluem dados que no nos interessam.

Se isso ocorrer, pressione "Remover"

at que todas as seqncias sejam

retiradas, resultando na tela mostrada na

figura 4.

Agora podemos adicionar as seqncias

de dados de interesse, pressionando

"Adicionar", o que resultar na figura 5.

Precisamos adicionar os valores de X e

de Y (no h necessidade de adicionar

valores em "Nome"). Podemos fazer

isso de duas formas: ou digitando as

referncias das clulas (em "Valores de

X" teramos A2:A251; em "Valores de

Y" teramos B2:B251), ou marcando as

clulas na planilha (pressionando a seta

vermelha na extrema direita de cada

janela, e marcando as clulas de

interesse na planilha).

Aps a adio dos dados, o resultado

ser uma tela semelhante da figura 6.

Observe que j possvel ter uma

idia do diagrama de disperso: os

dados parecem distribuir-se de forma

curva, com os valores de X

comeando acima de 20, e os valores

de Y variando de 500 at quase

4000. Possivelmente teremos que

modificar a escala do eixo X, para

que a visualizao do grfico seja

mais apropriada: da forma como est

o grfico os dados esto muito

agrupados, o que pode dificultar a

anlise do diagrama de disperso.

Pressionando "Avanar" chegaremos

tela mostrada na figura 7.


3



Figura 9 - Diagrama de disperso: Vendas por temperatura

Se colocarmos o mouse sobre o grfico (na parte branca) e pressionarmos o boto esquerdo,

teremos uma situao semelhante mostrada na figura 10.

necessrio pr um

ttulo no grfico, e

identificar as variveis

em cada eixo, incluindo

suas unidades.

Ttulo: Vendas por

temperatura.

Eixo X: temperatura (em

graus Celsius).

Eixo Y: Vendas.

Retiramos a legenda,

pois no h necessidade

neste grfico.

Ao pressionar "Avanar"

chegamos na tela

mostrada na figura 8.

Escolhe-se onde queremos que o

diagrama seja posicionado.

Selecionando "Como objeto em:"

o grfico ser colocado na

planilha onde esto os seus dados,

o que pode ser mais interessante.

O diagrama resultante est na

figura 9.


4

Figura 10 - Diagrama de disperso: grfico e dados

Temos que modificar a escala do eixo X, e o fundo cinza. Comearemos por este ltimo,

precisamos selecionar a rea de plotagem do grfico: ao colocarmos o cursor sobre o grfico, sobre

a parte cinza, e pressionando o boto esquerdo do mouse, vamos obter a tela mostrada na figura 11.

Figura 11 - Seleo da rea de plotagem

Figura 12 - Opes para a rea de plotagem

Observe que ao selecionar o

grfico as clulas que contm os

dados que o geraram tem suas

bordas coloridas, o que pode ser

til para avaliar se no houve

erros ou falta de alguns valores.

O grfico das figuras 9 e 10

apresenta alguns problemas: a

escala do eixo X deixou os

dados muito prximos, o que

pode dificultar a anlise do

diagrama; o fundo cinza do

grfico pode resultar em gasto

desnecessrio de tinta se

decidirmos imprimi-lo depois.

Colocando o cursor sobre a

rea de plotagem, j

selecionada, e pressionando o

boto direito do mouse

teremos a tela mostrada na

figura 12, com as vrias

opes possveis.

Estamos interessados na

primeira opo: "Formatar

rea de plotagem".

Escolhendo esta opo o

Excel apresentar a tela

mostrada na figura 13.


5

Figura 13 - Formatao padro da rea de plotagem Figura 14 - rea de plotagem com fundo branco

Na figura 13 vemos a formatao padro da rea de plotagem, com fundo cinza: observe no campo

"rea" que a cor cinza est selecionada, fazendo com que o campo "Exemplo" tambm tenha cor

cinza. Na figura 14 selecionamos a cor branca, fazendo com que o campo "Exemplo" passe a ser

branco tambm. Pressionando "OK" o grfico passar a ser como o da figura 15.

Figura 15 - Diagrama de disperso com fundo branco

Figura 16 - Seleo do eixo X Figura 17 - Opes para o eixo X

Pressionando "Formatar eixo" vamos ter acesso a uma srie de opes de modificao do eixo X,

mostradas na figura 18.

Resolvemos o problema do fundo, agora

precisamos modificar a escala. Para tanto

preciso colocar o cursor exatamente sobre

o eixo X, e pressionando o boto esquerdo

do mouse teremos uma situao como a

exposta na figura 16. Posteriormente,

mantendo o cursor sobre o eixo e

pressionando o boto direito do mouse

vamos ter acesso s opes relativas ao

eixo X, como mostrado na figura 17.


6

Figura 18 - Opes de formatao de eixo: escala Figura 19 - Formatao de eixo: escala modificada

Escolhendo a opo "Escala" chegamos figura 18. O comportamento padro do Excel construir

a escala do grfico com os valores mnimo e mximo encontrados nos dados. Mas algumas vezes,

como no nosso problema, isso pode ser modificado, levando a um grfico em que os dados esto

muito concentrados. Como TODOS os valores de temperatura esto acima de 25 graus Celsius,

vamos mudar o "Mnimo" da escala para 25, o que pode ser visto na figura 19. Pressionando "OK"

vamos chegar ao grfico mostrado na figura 20.

Figura 20 - Diagrama de disperso vendas por temperatura - Final

Agora podemos fazer uma anlise do diagrama de disperso:

- as variveis parecem estar fortemente correlacionadas, porque os pontos encontram-se bastante

prximos.

- a correlao entre elas parece ser positiva, pois se observa que a nuvem de pontos tem um

comportamento crescente, ou seja, maiores valores de temperatura, maiores valores de vendas (e

razovel imaginar que realmente um aumento na temperatura cause um aumento nas vendas).

- quanto forma do relacionamento, isto , que tipo de curva poderamos ajustar aos dados para

realizao de previses, talvez seja interessante pensar em um polinmio de segundo grau, ou uma

exponencial; a utilizao de uma reta talvez no seja uma boa idia.


7

2. Ajuste de uma tendncia a um diagrama de disperso.

Imagine que quisssemos ajustar uma reta ao diagrama de disperso mostrado na figura 20,

no obstante a anlise feita. Como proceder? O Excel permite ajustar uma variedade de curvas aos

dados mostrados em um diagrama de disperso, e ainda calcula os coeficientes das equaes das

curvas, pelo mtodo dos mnimos quadrados (ou seja, obtm os coeficientes minimizam a soma dos

quadrados dos desvios entre os valores observados e os previstos por cada curva).

Para fazer o ajuste de qualquer curva, que no Excel significa adicionar uma linha de

tendncia, o primeiro passo colocar o cursor sobre os pontos do grfico e pressionar o boto

esquerdo do mouse. Alguns pontos mudaro de cor, tal como mostrado na figura 21.

Figura 21 - Seleo de pontos no grfico

Figura 22 - Opes de modificao dos dados

Figura 23 - Tipos de curva Figura 24 - Opes para os tipos de curva

Em seguida, mantendo o cursor

sobre os pontos, precisamos

pressionar o boto direito do

mouse, e surgiro as opes

possveis para os dados, entre elas

"Adicionar linha de tendncia", tal

como mostrado na figura 22.

Se pressionarmos "Adicionar linha de

tendncia" na figura 22 chegaremos

tela mostrada na figura 23. O tipo

padro de linha a linear (reta), mas

podemos selecionar outras. No nosso

problema vamos manter a curva

linear, mas queremos que o Excel

exiba a equao e o valor de R-

quadrado (coeficiente de

determinao) no grfico. Ento, em

"Opes" (figura 24) selecionamos

ambos. Pressionando "OK" o grfico

ficar como o da figura 25.


8

Figura 25 - Diagrama de disperso com reta

Embora o valor de R2 sugira que a reta um bom modelo de regresso, devemos observar

com cuidado o grfico, e lembrar a anlise feita na figura 20. Realmente a reta passa "entre" a

maioria dos pontos, mas talvez outra curva apresente um melhor ajuste aos dados (polinmio de

segundo grau ou exponencial, conforme sugerido anteriormente). Para realmente saber se o modelo

ajustado bom precisamos analisar seus resduos.

3. Anlise de resduos

Uma vez tendo construdo o diagrama de disperso para as duas variveis, e adicionado a

linha de tendncia a ele, pode ser interessante realizar a anlise dos resduos do modelo. Se o

modelo for apropriado os resduos devero ter um comportamento aleatrio, sem nenhum padro

identificvel, mostrando que a variao residual, que no pode ser explicada pelo modelo

realmente casual, e ele poder ser utilizado para realizar previses e seus resultados sero teis na

tomada de deciso. Se, porm, algum padro for detectado nos resduos a varincia residual no

aleatria, o que significa que o modelo no est conseguindo "explicar" de maneira consistente o

relacionamento entre as variveis, e, portanto, as previses feitas pelo modelo so questionveis.

Isso pode acontecer mesmo que o R2 assuma um valor elevado. Sendo assim a anlise de resduos

indispensvel para avaliar a adequao de qualquer modelo de regresso, sendo especialmente

importante nos casos de regresso mltipla, onde muitas vezes no possvel plotar um grfico dos

dados.

Pensando nos dados de Vendas e Temperatura, estudados nos itens 1 e 2, que culminaram no

grfico mostrado na figura 25, queremos analisar os resduos do modelo linear (reta). O primeiro

passo calcular os valores de vendas previstos pelo modelo linear: na clula C2 da planilha

inserimos a frmula com a equao da reta obtida pelo Excel, tal como na figura 26.

Figura 26 - Frmula de previso de vendas (reta)

Observe no canto superior

direito da figura a equao

da reta, com um coeficiente

angular positivo (reta

crescente), e o coeficiente

de determinao, que vale

0,8565. Este valor significa

que cerca de 85,65% da

variabilidade mdia das

vendas pode ser explicada

pela variabilidade mdia da

temperatura, atravs do

modelo de regresso.

Observe que a frmula construda

em funo da temperatura (cujo

primeiro valor est na clula A2).

Aps digitar a frmula e pressionar

"Enter" (ou "Return", dependendo do

computador), podemos colocar o

cursor sobre a clula C2,

selecionando-a.


9

Para estender os clculos a todos os valores de temperatura basta "arrastar" a frmula at a ltima

linha do arquivo. As previses de vendas atravs do modelo linear estaro ento completas.

Para calcular os resduos devemos obter a diferena entre os valores observados de Vendas e

os valores previstos atravs do modelo linear. A figura 27 mostra isso.

Figura 27 - Clculo dos resduos

A obteno dos resduos muito importante, mas dependendo da unidade das variveis os

resduos podero ser consideravelmente grandes em valores absolutos, embora em termos relativos

sejam pequenos, ou o contrrio. Podemos ter resduos pequenos em termos absolutos, mas

substancialmente grandes em relativos. Para que a anlise seja feita objetivamente preciso

padronizar os resduos: subtra-los de sua mdia esperada (que deve ser igual a zero se o modelo for

bom) e dividir pelo seu desvio padro. O clculo do desvio padro dos resduos est mostrado na

figura 28.

Figura 28 - Clculo do desvio padro dos resduos

Para obter os resduos padronizados basta dividir cada resduo pelo desvio padro. Para que

no haja problemas ao "arrastar" a frmula preciso dar uma referncia absoluta ao denominador

da frmula: acrescentar $ antes da letra que designa a coluna e antes do nmero que designa linha,

tal como na figura 29.

Figura 29 - Clculo dos desvios padronizados

Para obter todos os resduos basta "arrastar" a frmula at a ltima linha do arquivo.

Uma vez obtidos os resduos padronizados podemos fazer a sua anlise propriamente dita.

Precisamos construir dois diagramas de disperso dos resduos: resduos padronizados em funo de

X (Temperatura), e resduos padronizados em funo dos valores preditos. O procedimento

semelhante ao visto no item 1, mudando apenas os valores de X e de Y, e escrevendo os ttulos

adequados, o que mostrado nas figuras 30 e 31.

Novamente, basta construir a frmula

para o primeiro valor e "arrast-la" at

a ltima linha para obter todos os

resduos do modelo.

Inserimos a frmula do desvio

padro amostral, com os dados

das clulas D2 a D251, que

contm os resduos calculados

anteriormente. O resultado est

mostrado na figura 29.


10

Figura 30 - Dados para o anlise de resduos Figura 31 - Ttulos do diagrama de disperso

Figura 32 - Resduos padronizados por temperatura - Modelo linear

Figura 33 - Resduos padronizados por valores previstos - Modelo linear

Sugerimos a utilizao de outro modelo.

Observe a escala do diagrama. Novamente

precisamos modific-la, bem como o fundo

cinza. Devemos fazer o mesmo procedimento

tambm para o diagrama dos resduos

padronizados pelos valores preditos. Os

diagramas resultantes esto nas figuras 32 e 33.

Fazendo a anlise dos resduos

mostrados na figura 32.

Observe a escala vertical do

grfico: devemos sempre torn-

la simtrica ao zero, para

auxiliar na anlise:

1) Nmero de resduos positivos

semelhante ao dos negativos.

2) As distncias dos resduos

positivos a zero so maiores do

que as dos negativos.

3) H um padro nos resduos,

parece uma parbola.


mostrados na figura 33.




positivos a zero so maiores do

que as dos negativos.

3) H um padro nos resduos,

parece uma parbola.

Juntando a anlise dos dois

diagramas chegamos concluso

que o modelo linear NO

apropriado para o problema, pois

seus resduos no se comportam

de forma aleatria.


11

Repetindo o procedimento das Figuras 21 a 23 podemos escolher o modelo Polinmio do 2 grau. O

resultado pode ser visto na Figura 34, superposto ao resultado da Figura 25.

Ttulo do grfico

y = 255,17x - 6451,7

R2 = 0,8565

y = 23,039x2 - 1220,1x + 17074

R2 = 0,9147

0

500

1000

1500

2000

2500

3000

3500

4000

25 27 29 31 33 35 37 39

Temperatura

Ve

nd

as

Figura 34 - Diagrama de disperso com reta e polinmio do 2 grau

Percebe-se que o coeficiente de determinao do polinmio de 2 grau maior do que o da reta. E,

tambm, o ajuste da curva do polinmio de 2 grau aos pontos bem melhor. Provavelmente os

resduos sero melhores do que os da reta. Outros modelos poderiam ser ajustados, resultando na

Figura 35.

Temperatura por Vendas

y = 255,17x - 6451,7

R2 = 0,8565

y = 23,039x2 - 1220,1x + 17074

R2 = 0,9147

y = 8021,5Ln(x) - 26071

R2 = 0,8344

y = 1,81E-04x4,62E+00

R2 = 8,85E-01

y = 15,409e0,1458x

R2 = 0,8924

0

500

1000

1500

2000

2500

3000

3500

4000

25 27 29 31 33 35 37 39

Temperatura

Ve

nd

as

Figura 35 - Diagrama de disperso com cinco modelos de regresso

Todos os cinco modelos aplicveis esto no grfico da Figura 35: reta, polinmio de 2 grau,

logartmico, exponencial e potncia. Mas, observe o formato dos coeficientes no modelo potncia:

est cientfico, 1,81E-04x4,62E+00

. Isso significa 0,000181x4,62

, que o formato que devemos usar

nas previses. s vezes o Excel automaticamente apresenta as equaes de um modelo em formato

cientfico, e com um nmero insuficiente de casas decimais, o que pode prejudicar nossas previses.

Para mudar o formato e as casas decimais veja o procedimento a seguir.


12

Selecione a equao do modelo potncia na Figura 35:

Figura 36 - Seleo de uma equao

Clicando duas vezes sobre a equao surge a tela da Figura 37.

Figura 37 Formatao de rtulo de dados: Nmero

Temperatura por Vendas

y = 255,17x - 6451,7

R2 = 0,8565

y = 23,039x2 - 1220,1x + 17074

R2 = 0,9147

y = 8021,5Ln(x) - 26071

R2 = 0,8344

y = 0,000181x4,624102

R2 = 0,885469

y = 15,409e0,1458x

R2 = 0,8924

0

500

1000

1500

2000

2500

3000

3500

4000

25 27 29 31 33 35 37 39

Temperatura

Ve

nd

as

Figura 38 - Diagrama de disperso com cinco modelos de regresso - modificado

s vezes o Excel apresenta os

dados em formato cientfico, mas

na categoria Geral. Se quisermos que os nmeros sejam

apresentados da forma usual

devemos escolher Nmero e quantas casas decimais forem

necessrias: no nosso caso, como

o Excel usou E-04, deve-se

escolher no mnimo 4, mas o ideal

um pouco mais para ganhar

preciso nas previses, 6, por

exemplo. O resultado pode ser

visto na Figura 38.


13

Na Figura 26 fizemos a previso usando o modelo de Reta, agora apresentaremos as

previses pelos outros modelos disponveis:

Figura 39 - Modelo polinmio de 2o grau (para equao da Figura 38)

Na Figura 39 possvel observar que no lugar de X colocamos a primeira clula do intervalo

que contm os valores de temperatura (clula A2). Observe que o ^ o smbolo de potenciao no

Excel (e no Calc tambm). Basta arrastar at a clula R251 para completar a previso pelo modelo

polinmio de 2 grau. O clculo dos resduos, desvio padro dos resduos e resduos padronizados

anlogo ao caso da reta (para este e para os prximos modelos).

Figura 40 - Modelo logartmico (para equao da Figura 38)


que contm os valores de temperatura (clula A2). Observe que LN() uma funo do Excel (e do

Calc tambm) que permite calcular o logaritmo neperiano (com base igual a e, a constante de

Neper, igual a 2, 71828...). Basta arrastar at a clula V251 para completar a previso pelo modelo

logartmico.

Figura 41 - Modelo potncia (para equao da Figura 38)


que contm os valores de temperatura (clula A2). Observe que X (no caso o contedo da clula

A2) elevado (^) a 4,624102, que expoente do modelo potncia (ver Figura 38). Basta arrastar at

a clula Z251 para completar a previso pelo modelo potncia.

Figura 42 - Modelo exponencial (para equao da Figura 38)


que contm os valores de temperatura (clula A2). Observe que EXP() uma funo do Excel (e do

Calc tambm) que permite calcular o valor da constante de Neper (e = 2, 71828...) elevada ao

produto de 0,1458 pelo contedo da clula A2). Basta arrastar at a clula AD251 para completar a

previso pelo modelo exponencial.

Vejam os resduos padronizados do modelo polinmio do 2 grau:


14

R e s d u o s p a ra p a r b o la

-4

-3

-2

-1

0

1

2

3

4

2 5 3 0 3 5 4 0

Te m p e ra tu ra

Re

sd

uo

s p

ad

ron

iza

do

s

Figura 43 - Resduos do polinmio de 2 grau por temperatura

R e s d u o s p a ra p a r b o la

-4

-3

-2

-1

0

1

2

3

4

5 0 0 1 0 0 0 1 5 0 0 2 0 0 0 2 5 0 0 3 0 0 0

V a lo re s p re d ito s

Re

sd

uo

s p

ad

ron

iza

do

s

Figura 44 - Resduos do polinmio do 2 grau por valores preditos


mostrados na Figura 43.




positivos e negativos a zero so

semelhantes.

3) Os resduos distribuem-se

aleatoriamente, sem padro.


mostrados na Figura 44.




positivos e negativos a zero so

semelhantes.

3) Os resduos distribuem-se

aleatoriamente, sem padro.

Juntando a anlise dos dois

diagramas chegamos concluso

que o modelo de polinmio de 2

grau apropriado para o

problema, pois seus resduos se

comportam de forma aleatria.

Documents

Proced i Mentos Bi Dimensional Excel 2003