Uso do Excel para análises estatísticas - fsp.usp.br

“O Uso do EXCEL para Análises Estatísticas”

HEP58000 Curso de Bioestatística

Regina Bernal Nilza Nunes da Silva Março de 2012

Índice I. Sobre o Excel ..................................................................... 4 II. Planilha Eletrônica............................................................. 5 II.1. Pasta de trabalho....................................................................... 5 II.2. Tipos de dados .......................................................................... 6 II.2.1. Referência Relativa e Absoluta ............................................. 7 II.3. Editando a planilha .................................................................... 9 II.3.1. Mover ................................................................................. 9 II.3.2. Copiar ................................................................................. 9 II.3.3. Inserir................................................................................10 II.3.4. Excluir ...............................................................................11 II.4. Salvando o arquivo ...................................................................11 II.5. Imprimindo arquivo ..................................................................12 II.6. Barra de ferramenta .................................................................12

III. Formatando os dados de uma planilha ............................ 13 IV. Formatando tabelas ....................................................... 14 V. Fazendo Gráficos ............................................................. 18 V.1. Diagrama de barras (uma variável qualitativa ou quantitativa discreta) ..........................................................................................20 V.2. Diagrama linear com uma e duas variáveis (escala aritmética e logarítmica) .....................................................................................22 V.3. Histograma - intervalos com mesma amplitude ...........................26 V.4. Diagrama de barras com duas variáveis ......................................28

VI. Tabela Dinâmica ............................................................ 29 VII. Gráfico dinâmico ........................................................... 34 VIII. Análise de Dados ......................................................... 35 VIII.1. Módulo de Análise de Dados ..................................................35 VIII.2. Ferramentas Estatísticas ........................................................36 VIII.3. Procedimentos para as análises ..............................................37 VIII.3.1. Análise Exploratória de Dados ..........................................37 VIII.3.2. Teste de hipóteses para as variâncias de duas populações .39 Regina Bernal e Nilza Nunes da Silva

2

VIII.3.3. Teste de hipóteses para as médias de duas populações independentes e com variâncias iguais ...........................................41 VIII.3.4. Teste de hipóteses para as médias de duas população independentes e com variâncias diferentes .....................................43 VIII.3.5. Teste de hipóteses para as médias com duas populações pareadas (dependentes) ...............................................................45 VIII.4. Outras funções estatísticas .....................................................47

IX. Regressão Linear Simples ............................................... 48

Regina Bernal e Nilza Nunes da Silva

3

I. Sobre o Excel O Excel é um aplicativo Windows que fornece ferramentas para organizar, analisar e interpretar dados. Este programa dispõe de quatro funções principais: Planilhas: A planilha eletrônica é organizada em linhas numeradas e colunas identificáveis por letras (A, B, C, ...) onde é possível identificar facilmente cada uma de suas células ou caselas. A figura abaixo ilustra o exemplo de número de internações ocorridas em cada dia da semana, em 2007, no Hospital X.

Bancos de dados: A planilha eletrônica pode ser usada para armazenar dados. O Excel dispõe funções de banco de dados que permite: consultar, buscar, ordenar, filtrar, calcular estatísticas e administrar facilmente uma grande quantidade de dados utilizando operações de bancos de dados padronizadas. A figura abaixo ilustra o exemplo de dados provenientes da Pesquisa de Medicamentos do município de São Paulo em 2005.


4

Gráficos: A função de gráficos permite a representação gráfica dos dados disponível na planilha. A figura abaixo ilustra o exemplo de ocorrências de internações em cada dia da semana em 2007. 35 30 25

%

20 15 10 5 0

segunda

terça

quarta

quinta

sexta

sábado

domingo

Dia da Semana

O EXCEL fornece um conjunto de ferramentas estatísticas para análise de dados com limite de 1.6 milhões observações. As análises estatísticas mais sofisticadas, como ex: regressão logística, cluster, fatorial entre outras não estão disponíveis no Excel.

II. Planilha Eletrônica II.1. Pasta de trabalho O arquivo no formato Excel chama-se pasta de trabalho e tem extensão xls (exemplo:internações.xls). O usuário poderá armazenar várias planilhas dentro de uma mesma pasta. A figura abaixo ilustra o exemplo de uma pasta com 10 planilhas (plan1 a plan10).


5

II.2. Tipos de dados O usuário poderá inserir dois tipos de dados: a) Valor constante: digita-se diretamente na célula um número, incluindo data e hora, ou um texto. Procedimentos para entrada de dados: 1) Selecione a célula para a qual deseja entrar com o dado 2) Digite o dado 3) Teclar Enter Exemplo1: Digitar na coluna A e linhas 1, 2, 3, 4, 5, 6, 7 e 8 os textos: dia da semana, segunda, terça, quarta, quinta, sexta, sábado e domingo. Digitar na coluna B1 o texto Número de Pacientes e nas linhas 2, 3, 4, 5, 6, 7 e 8 os valores: 100, 30, 20, 10, 60, 80 e 20. b) Fórmula: Pode ser uma função ou uma operação que produzirá novo valor a partir de valores existentes. A fórmula inicia com sinal de igual (=) seguida da operação ou função desejada. No exemplo 1, para encontrar a soma de pacientes internados digite na célula B9 a fórmula =SOMA(B1:B8) ou clicar sobre o ícone e pressionar a tecla Enter. Você encontrará o número total de pacientes. Digite na coluna A9 o texto Total. Resultado:

Exemplo 2: Para calcular a porcentagem de pacientes internados em cada dia da semana digite na célula C1 o texto % e digite nas células: C2 = B2/B9*100 e tecle Enter C3 = B3/B9*100 e tecle Enter C4 = B4/B9*100 e tecle Enter C5 = B5/B9*100 e tecle Enter C6 = B6/B9*100 e tecle Enter C7 = B7/B9*100 e tecle Enter C8 = B8/B9*100 e tecle Enter


6

Resultado:

II.2.1. Referência Relativa e Absoluta As fórmulas podem ser copiadas de uma célula para outra e são denominadas de referência relativa ou absoluta.

a) Referência Relativa A fórmula da célula usada como referência relativa varia de linha e coluna. Exemplo: Número de parturientes segundo tipo de parto e assistência pré-natal

Para calcular a porcentagem de parturientes Normal com e em pré-natal faça: digite na célula E3 a fórmula = B3/D3*100 e para copiar a fórmula de uma célula faça: 1) na célula E3 use o comando CTRL-C


7

2)

e na célula E4 use o comando CTRL-V

Resultado: a porcentagem de parturientes Cesariano com pré-natal é dado por: E4=B4/D4*100. Repita o mesmo procedimento para as demais.

b) Referência Absoluta Uma referência absoluta indica como encontrar uma célula baseada na localização exata. A célula é expressa é pelo caracter dólar ($) e representa: 1) $coluna$  fixa a coluna e linha 2) $coluna  fixa a coluna 3) coluna$  fixa a linha coluna=A, B, C ... No exemplo anterior, para calcular a porcentagem de parturientes Normal com pré-natal digite na célula F3 = C3/$D3*100. Na célula F3  CTRL-C  na célula G3  CRTL-V. O usuário copiou a fórmula na célula F3 para G3.

Repetir o procedimento para os demais tipo de parto.


8

II.3. Editando a planilha II.3.1. Mover O conteúdo de uma célula pode ser removida para uma nova célula mantendo o formato e a fórmula caso exista. A linha e coluna também podem ser removidas. Procedimento para mover uma célula: 1) Selecione a célula Botão da direita do mouse selecione a opção Recortar(*) e tecle Enter 2) Selecione a nova célula e com o botão direito do mouse selecione a opção Colar e tecle Enter Procedimento para mover uma ou mais colunas : 1) Selecione a(s) coluna(s) 2) Botão da direita do mouse selecione a opção Recortar(*) e tecle Enter Procedimento para mover uma ou mais linhas : 1) Selecione a(s) linhas(s) 2) Botão da direita do mouse selecione a opção Recortar(*) e tecle Enter

(*) Usar o ícone

para recortar a área selecionada para a área de transferência

II.3.2. Copiar O conteúdo de uma célula pode ser copiada para uma nova célula mantendo o formato e a fórmula caso exista. A linha e coluna também podem ser copiadas. Procedimento para copia uma célula: 1) Selecione a célula 2) Botão da direita do mouse selecione a opção Copiar(*) e tecle Enter 3) Selecione a nova célula e com o botão direito do mouse selecione a opção Colar(*) e tecle Enter Procedimento para copiar uma ou mais colunas : 1) Selecione a(s) coluna(s) 2) Botão da direita do mouse selecione a opção Copiar(*) 3) Selecione a nova coluna com o botão direito do mouse selecione a opção Colar(*) e tecle Enter Procedimento para copiar uma ou mais linhas : 1) Selecione a(s) linhas(s) 2) Botão da direita do mouse selecione a opção Copiar(*)


9

3) Selecione a nova linha e com o botão direito do mouse selecione a opção Colar(*) e tecle Enter

(*) Use o ícone ícone

para Copiar a área selecionada para a Área de Transferência e o

para Colar a seleção da Área de Transferência para a área indicada.

II.3.3. Inserir O usuário poderá inserir célula(s), linha(s) ou coluna(s) em branco em qualquer lugar da planilha. Ao inserir a célula, linha ou coluna o Excel deslocará as informações para a inserção de novos valores. Procedimento para inserir uma célula ou mais: 1) Selecione a(s) célula(s) 2) Botão da direita do mouse selecione a opção Inserir . Escolha uma das opções e OK.

Procedimento para inserir uma ou mais colunas : 1) Selecione a(s) coluna(s)

2) Botão da direita do mouse selecione a opção Inserir e tecle Enter

Procedimento para mover uma ou mais linhas : 1) Selecione a(s) linhas(s)


10

2) Botão da direita do mouse selecione a opção Inserir(*)

II.3.4. Excluir O usuário poderá excluir célula(s), linha(s) ou coluna(s). Procedimento para excluir uma célula ou mais: 1) Selecione a(s) célula(s) 2) Botão da direita do mouse selecione a opção Excluir . Escolha uma das opções e OK.

Procedimento para excluir uma coluna ou mais: 1) Selecione a(s) coluna(s) 2) Botão da direita do mouse selecione a opção Excluir. Procedimento para excluir uma linha ou mais: 1) Selecione a(s) linha(s) 2) Botão da direita do mouse selecione a opção Excluir.

II.4. Salvando o arquivo No menu Arquivo selecione a opção de Salvar Como e escolha o diretório de trabalho e o nome do arquivo. Outra opção selecione o ícone


para salvar o arquivo. 11

II.5. Imprimindo arquivo Selecione as células ou a planilha que serão impressas. Use o ícone

para imprimir.

II.6. Barra de ferramenta Botões que facilitam a execução das tarefas mais freqüentes do aplicativo, bastando apenas acioná-los. Novo - cria uma nova pasta de trabalho, com base no modelo padrão. Abrir - abre uma pasta de trabalho existente. Salvar - salva a pasta de trabalho. Imprimir - imprime a planilha ativa na pasta de trabalho ou os itens selecionados. Visualizar - visualiza o documento a ser impresso. Verificar Ortografia - verifica os erros de ortografia existentes na planilha. Recortar - recorta a área selecionada para a Área de Transferência. Copiar - copia a área selecionada para a Área de Transferência. Colar - cola a seleção da Área de Transferência para a área indicada. Desfazer - desfaz a última ação. Refazer - refaz a última ação.


12

III. Formatando os dados de uma planilha A formatação dos dados é importante para facilitar a leitura, interpretação e impressão dos dados. Procedimentos: 1) Selecione as células, linhas ou colunas que serão formatadas 2) Com o botão direito do mouse escolha uma das opções: a. Número: Categoria Células com formato Geral não possuem formato de número específico Número  é usada para exibir números em geral Moeda  os formatos “Moeda” são usados para quantias monetárias em geral Contábil  os formatos “Contábil” alinham símbolos de moeda e vírgulas decimais em uma coluna Data  os formatos de datas exibem números de série de data e hora com valores de data Hora  os formatos de hora exibem números de série de data e hora com valores de data Porcentagem  os formatos de porcentagem multiplicam o valor da célula por 100 e exibem o resultado com o símbolo de porcentagem Fração  Valores fracionários podem ser expressos em forma de fração Científico Texto Especial  Os formatos de “Especial” são úteis para rastear valores de banco de dados e listas Personalizado  Digite o código de formatação do número, usando um dos códigos existentes como ponto de partida. b. Alinhamento de Texto c. Fonte d. Borda  utilizado para formatação de tabelas e. Preenchimento  Cor do pano de fundo


13

IV. Formatando tabelas No exemplo 1, o usuário poderá inserir duas colunas para o cálculo da porcentagem. A coluna C é a distribuição da variável tipo de parto das mulheres que fizeram o pré-natal. Enquanto que, a coluna E é a distribuição da variável tipo de parta daquelas que não fizeram o pré-natal. A coluna G é a distribuição da variável tipo de parto. Resultado:

Selecionar a linha 3 e inserir uma linha  recorte e cole a célula A2 para célula A3  nas células B3, D3 e F3 escreva FA (freqüência absoluta) e nas células C3, E3 e G3 escreva FR (frequência relativa).

Resultado:

Selecione as células B2 e C2 e clique no ícone para as células D2 e E2 e F2 e G2.

para mesclar as células, faça o mesmo

Resultado:


14

Selecione as células A1, A2 e A3 e clique no ícone mesmo para as células B1 a E1 e F1 e G1.

para mesclar as células. Faça o

Resultado:

Selecione as colunas C, E e G e formate o número com uma casa decimal.

Selecione células A1, A2, A3, C1, C2, C3, ... , G1, G2 e G3  com o botão da direita selecione a opção de formatar células e Borda:

Escolha uma opção de estilo da linha  OK


15

Escolha uma opção

Marque no texto

Escolha a opção Alinhamento  Alinhamento de texto Vertical  Centro

Pré-natal Tipo de parto Normal

Sim

Total

Não

FA

FR(%)

FA

FR(%)

FA

FR(%)

120

75,0

20

50,0

140

70,0

Cesariano

40

25,0

20

50,0

60

30,0

Total

160

100,0

40

100,0

200

100,0


16

Ícones de formatação:

Fonte - Altera a fonte do texto e dos números selecionados. Tamanho - Altera o tamanho da fonte. Efeito Negrito - Ativa ou desativa o efeito negrito. Efeito Itálico - Ativa ou desativa o efeito itálico. Efeito Sublinhado - Ativa ou desativa o efeito sublinhado. Alinhamento a esquerda - Alinha o conteúdo da célula à esquerda. Alinhamento Centralizado - Centraliza o conteúdo da célula de acordo com a região selecionada. Alinhamento a direita - Alinha o conteúdo da célula à direita. Mesclar e Centralizar - Centraliza o conteúdo da célula de acordo com a região selecionada ou mescla células. Estilo de Moeda - Aplica formato monetário às células selecionadas. Estilo de Porcentagem - Aplica formato de porcentagem às células selecionadas. Separador de Milhares - Aplica o formato separador de milhar às células selecionadas. Aumentar Casas Decimais - Aumenta o número de casas decimais das células selecionadas. Diminuir Casas Decimais - Diminui o número de casas decimais das células selecionadas. Diminui recuo - possibilita diminuir ou remover o recuo. Aumenta recuo - permite recuar o texto a partir da borda esquerda de uma célula. Bordas - Insere borda à célula selecionada ou ao intervalo selecionado. Cor do Preenchimento - Adiciona, modifica ou remove a cor ou efeito de preenchimento do objeto selecionado. Cor da Fonte - Formata o texto selecionado com a cor desejada.


17

V. Fazendo Gráficos Apesar da apresentação dos dados através de tabelas ser mais precisa, a representação gráfica tem a vantagem de transmitir os dados de uma maneira mais rápida e viva, oferecendo uma visão imediata sobre o comportamento do fenômeno que estamos descrevendo. Elaborar gráficos é uma arte que somente pode ser adquirida através de prática, com os cuidados necessários para evitar posições tendenciosas, permitindo a visão clara dos pontos essenciais a serem notados. Portanto as regras básicas de elaboração de um gráfico são : simplicidade clareza veracidade O Excel possui diversos tipos de gráficos:

Construir o gráfico usando o Excel é muito simples, basta selecionar as células e escolher o tipo de gráfico e basta um “ click” e o gráfico está pronto, como ilustra a figura abaixo. O gráfico do Excel mostra no eixo horizontal duas colunas, no eixo vertical as porcentagem e a legenda. Esse é o resultado padrão da ferramenta. Mas faltam várias informações como: nomes dos eixos, nome das colunas 1 e 2, título e fonte. Reflexão: esse gráfico precisa de legenda?


18

Layout de gráfico


19

V.1. Diagrama de barras (uma variável qualitativa ou quantitativa discreta) Lista de procedimentos para apresentar os dados da tabela em um gráfico apropriado: 1) Selecione as porcentagens 2) Selecione a opção de tipo de gráfico

(1) Matricula no Posto de Saúde Não Sim Total

n 2 767 2152 2921

% 0 26 74 100

(2)

Resultado:

Não há necessidade de legenda, pois existe uma única variável de análise.


20

3) Selecione o gráfico e escolha a opção Layout de gráfico. Escolha um layout com nomes dos eixos vertical e horizontal.

(3)

Resultado: 80

70

Título do Eixo

60 50

40 Série1

30 20 10 0 1

2 Título do Eixo

4) Para acrescentar o nome das categorias do eixo horizontal selecione a opção Selecionar dados. Na opção (4) selecione as células com os nomes das categorias e na opção (5) informe o nome das variáveis.


21

(5)

(4)

V.2. Diagrama linear com uma e duas variáveis (escala aritmética e logarítmica) Passos: 1) Selecione as duas séries e escolha o gráfico de linha para representar as séries temporais


22

2) Insira os nomes dos valores possíveis do eixo X e os nomes das variáveis na legenda. Escolha o layout do gráfico com nomes dos eixos.

Resultado:

Coeficiente de mortalidade

30 25 20

15

Total

10

Junho

5 0 1968

1969

1970

1971

1972

1973

1974

Ano


23

3) Para alterar a escala aritmética para logarítmica, selecione o eixo Y e com o botão direito do mouse selecione a opção formatar eixo.

4) Escolha a opção Escala logaritmica


24

Resultado:


100

10 Total Junho 1 1968

1969

1970

1971

0,1

1972

1973

1974

Ano

Os resultados observados no gráfico (a) são diferentes do (b). Por quê? 100

25 20

15

Total

10

Junho

5



30

10 Total Junho 1 1968

1969

1970

1971

1972

1973

1974

0 1968

1969

1970

1971

1972

1973

1974

Ano

(a) Escala aritmética

0,1

Ano

(b) Escala logaritimica

Conclusão?


25

V.3. Histograma - intervalos com mesma amplitude Considerar os dados apresentados na tabela. Distribuição de recém-nascidos acometidos de síndrome de desconforto idiopático grave segundo peso ao nascer (g) Peso(g) No % 1000 |-- 1500 13 26 1500 |-- 2000 15 30 2000 |-- 2500 9 18 2500 |-- 3000 9 18 3000 |-- 3500 3 6 3500 |-- 4000 1 2 Total 50 100 Fonte: Hand DJ et al. A handbook of small data sets. Chapman&Hall, 1994. Para construir o histograma no Excel, faça: 1) Copie e cole a tabela no Excel 2) Selecione a coluna do % e escolha a opção de gráfico de barra 3) Selecione no layout de gráfico a opção de gráfico sem espaço entre as colunas

(3)

(2) (1)

4) Precisa formatar o gráfico acrescentando os nomes dos eixos X e Y, substituindo os valores de 1 a 6 com seus respectivos intervalos de valores disponíveis na primeira coluna da tabela. Colocar Título e fonte, caso necessário.


26

5) Histograma da variável peso ao nascer (kg)

35 30 25

%

20 15

10 5 0 1000 |-- 1500

1500 |-- 2000

2000 |-- 2500

2500 |-- 3000

3000 |-- 3500

3500 |-- 4000

Peso ano nascer(kg)


27

V.4. Diagrama de barras com duas variáveis Considere os dados apresentados na tabela a seguir Distribuição de recém-nascidos segundo condição caso - com defeitos do tubo neural; controle – recém-nascidos que não tinham defeitos do tubo neural e dieta materna. Dieta

Casos

Controles

Total

N

%

n

%

n

%

Boa

34

13,9

43

35,0

77

21,0

Razoável

110

45,1

48

39,0

158

43,0

Pobre

100

41,0

32

26,0

132

36,0

Total

244

100,0

123

100,0

367

100,0

Para fazer o gráfico com duas variáveis, faça: 1) Copie e cole a tabela no Excel e selecione as três colunas com as porcentagens. 2) Selecione o gráfico de barra e escolha a primeira opção de gráfico. 3) Selecione o gráfico de barra e escolha a segunda diferença entre o gráfico 1 e 2?

opção de gráfico. Qual a

4) Formatar o gráfico para apresentação dos resultados.

(2) (1)

(3)


28

VI. Tabela Dinâmica Dado um conjunto de observações, a tabela dinâmica permite o cruzamento entre as variáveis para obter medidas de resumo, como exemplo: total, média desvio padrão e outras. O resultado é apresentado em forma de tabela e gráfico. Procedimento para análise: 1) Abrir o arquivo

2)

Menu -> inserir -> tabela dinâmica -> selecionar o conjunto de dados -> OK


29

3) Construir uma tabela univariada: Selecione uma variável categórica e solte na coluna -> selecione uma variável categórica e solte na linha -> seleciona a variável (∑ valores)

(1)

(3) (4) (2)

(1) lista de variáveis (2) variável na linha (3) variável na coluna (4) variável usada para contar, somar ...

4) Exemplo: selecionar as variáveis: b2b (linha) e b2b (∑ valores) Resultado: A medida resumo padrão é a contagem


30

5) Para acrescentar a porcentagem na tabela, faça: (1) Selecione a variável b2b e arraste no campo (∑ valores). A coluna b2b será duplicada (2) No campo (∑ valores) selecione Contagem b2b2 e seguida a opção Configurações no Campo de Valor (3) Selecione a opção Contagem (4) Selecione a opção Mostrar valores como (5) Selecione a opção % do Total de Coluna (1)

(2)

(4)

(3)

(5)

Resultado:

Para formatar a tabela, faça: (1) selecione a tabela e Ctrl-c


31

(2) Selecione uma célula vazia e com o botão direito do mouse selecione a opção Colar Especial (3) Escolha a opção Valores

(2)

(3)

Resultado:

Editar tabela: b2b Não Sim Total

n 2 767 2152 2921

fi 0,00 0,26 0,74 1,00

Para transformar a coluna da frequência relativa (fi) em porcentagem, faça: Escolha uma célula vazia e digite o valor 100. Selecione o valor 100 e Ctrl-c e com o botão direito do mouse selecione a opção colar especial -> multiplicação.


32

Resultado: b2b Não Sim Total

n 2 767 2152 2921

fi 0,07 26,26 73,67 100,00

6) Construir uma tabela bivariada: Selecione uma variável linha (1) e uma variável coluna (2)

(2)

(1)

7) Para acrescentar o percentual linha ou coluna faça o procedimento descrito no item 6. Resultado: b3e

Dados

1 2 9 Total Contagem de b2b Total Contagem de b3e Contagem de b2b Contagem de b3e Contagem de b2b Contagem de b3e Contagem de b2b Contagem de b3e Contagem de b2b Contagem de b3e 1 0,60% 1 0,14% 0,00% 0,00% 2 0,07% 1 41 24,40% 222 30,20% 493 25,18% 11 18,33% 767 26,26% 2 126 75,00% 512 69,66% 1465 74,82% 49 81,67% 2152 73,67% Total Geral 168 100,00% 735 100,00% 1958 100,00% 60 100,00% 2921 100,00% b2b

Nota: Lembre-se de copiar e colar especial  valor para editar a tabela.


33

VII. Gráfico dinâmico Para construir o gráfico usando a opção de gráfico dinâmico, faça: 1) Na planilha de dados escolha o menu: Inserir -> gráfico dinâmico

2) Escolha uma variável qualitativa ou quantitativa discreta e arraste para a opção Campos de Eixos

3) Selecione a mesma variável e arraste para a opção (∑ valores). 4) Use o procedimento descrito no item VI (6) para apresentar a porcentagem no eixo vertical.

Nota: Lembre-se de copiar e colar especial  valor para editar gráfico.


34

VIII. Análise de Dados O Excel possui um conjunto de ferramentas estatísticas que permite ao usuário analisar e entender uma massa de dados.

VIII.1. Módulo de Análise de Dados Comandos para habilitar o módulo de Análise de Dados: (1) Personalizar a barra de ferramenta (2) Selecionar a opção de mais comandos (3) Selecionar a opção Suplementos (4) Selecionar a opção Ferramenta de análises (5) Selecione a opção ir (6) Seleciona a opção Ferramenta de análises

(1)

(3)

(4)

(5) (2)

(6)


35

A opção de Análise de dados estará disponível no menu Ferramenta:

VIII.2. Ferramentas Estatísticas As análises estatísticas disponíveis são:


36

VIII.3. Procedimentos para as análises

VIII.3.1. Análise Exploratória de Dados Ficar atento as variáveis numéricas com valores “missing”. Se o campo contém valor igual “.” o Excel não executará a tarefa e dará uma mensagem de erro. Recomendase a substituição do valor “.” para “branco”. 1) Abrir o arquivo de dados 2) Selecionar Ferramentas -> Análise de dados

3) Definição dos parâmetros Selecionar os campos numéricos para a análise Essa informação é importante para identificar na saída as variáveis analisadas

Selecionar a opção para estatística descritiva

Precisão da média -> Zα/2* erro padrão

Colocar a posição da observação


37

4) Resultados Devido ao erro na tradução a estatística MODA ficou sendo MODO. Saída original: Média Erro padrão Mediana Modo Desvio padrão Variância da amostra Curtose Assimetria Intervalo Mínimo Máximo Soma Contagem Maior(1) Menor(1) Nível de confiança(95,0%)

11,12222222 0,764576251 10,165 #N/D 4,587457504 21,04476635 -0,014037842 0,653220765 19,3 4 23,3 400,4 36 23,3 4

Média Erro padrão Mediana Modo Desvio padrão Variância da amostra Curtose Assimetria Intervalo Mínimo Máximo Soma Contagem Maior(1) Menor(1)

1,552174205 Nível de confiança(95,0%)

34,58333 1,122904 34,5 26 6,737422 45,39286 -0,54253 -0,06711 28 20 48 1245 36 48 20 2,279618

Após formatação do campo:

s alario Média E rro padrão Mediana Modo D es vio padrão V ariância da amos tra C urtos e As s imetria Intervalo Mínimo Máximo S oma C ontagem Maior(1) Menor(1) Nível de confiança(95,0% )


idade 11,12 Média 0,76 E rro padrão 10,17 Mediana Modo 4,59 D es vio padrão 21,04 V ariância da amos tra -0,01 C urtos e 0,65 As s imetria 19,30 Intervalo 4,00 Mínimo 23,30 Máximo 400,40 S oma 36,00 C ontagem 23,30 Maior(1) 4,00 Menor(1) 1,55 Nível de confiança(95,0% )

34,58 1,12 34,50 26,00 6,74 45,39 -0,54 -0,07 28,00 20,00 48,00 1245,00 36,00 48,00 20,00 2,28

38

VIII.3.2. Teste de hipóteses para as variâncias de duas populações O teste de hipóteses é unicaudal à direita ou à esquerda. Exemplo 1: Em treze estabelecimentos varejistas muito semelhantes, foram colocados dois tipos de publicidade, sendo colocados a cada um dos tipos de modo aleatório. O objetivo é estudar o impacto da publicidade sobre as vendas. A tabela a seguir fornece as vendas (em unidades monetárias) de acordo com o tipo de publicidade: Publicidade 1 (P1):

41 43 44 48 39 45

Publicidade 2 (P2):

32 53 55 58 40 60 43 Amostra 1

Amostra 2

Resultados: Teste-F: duas amostras para variâncias

Média Variância Observações gl F P(F<=f) uni-caudal F crítico uni-caudal


P1 P2 43,33333 48,71429 9,866667 109,9048 6 7 5 6 0,089775 0,009092 0,202008

Fcalculado Nível descritivo

39

Interpretação dos resultados: Estatística F <= 1

Formulação das hipóteses

F>1


P(F<=f)=

Nível descritivo

d

F e F crítico F e F crítico

H0 :

2 1

2 2

H1 :

2 1

2 2

H0 :

2 1

2 2

H1 :

2 1

2 2

d

 rejeição de H0

d

 não rejeição de H0

Resultado do teste:

F> F crítico  Rejeito H0

Unicaudal à direita

F < F crítico  não rejeito HO

Resultado do teste:

F< F crítico  Rejeito H0

Unicaudal esquerda

F > F crítico  não rejeito HO

à

Conclusão do exemplo 1: Teste de hipóteses: unicaudal à esquerda

H0 :

2 1

2 2

H1 :

2 1

2 2

F < F crítico (0,089 < 0,2020), rejeitamos a H0 ao nível de 5% de significância. Portanto, as variâncias não são iguais. Nota-se que, o nível descritivo é próximo de zero ( valor de

d

0 ). Portanto, para qualquer

a H0 será rejeitada.


40

VIII.3.3. Teste de hipóteses para as médias de duas populações independentes e com variâncias iguais Exemplo 2: Voltando ao exemplo 1 e supondo que H0 não foi rejeitada, será realizada o teste de hipóteses para as médias de duas populações independentes e com variâncias iguais: Procedimento: Amostra 1

Amostra 2 Valor que está sendo testado

Nível de significância

Resultados: Teste-t: duas amostras presumindo variâncias equivalentes

Média Variância Observações Variância agrupada Hipótese da diferença de média gl Stat t P(T<=t) uni-caudal t crítico uni-caudal P(T<=t) bi-caudal t crítico bi-caudal

P1 P2 43,33333 48,71429 9,866667 109,9048 6 7 64,4329 0 11 -1,20492 0,126759 1,795885 0,253517 2,200985

tcalculado Nível descritivo Valor de t encontrado na tabela t-Student

Obs: Se o teste for unicaudal a esquerda precisa acrescentar o sinal negativo no valor de t crítico (distribuição t-Student é simétrica)


41

Interpretação dos resultados: Estatística Stat t < 0


Stat t > 0


-


P(T<=t)=

Nível descritivo

d

H0 :

1

H1 :

1

H0 :

1

2

H1 :

1

2

H0 :

1

2

H1 :

1

2

d

d

Stat t e t crítico

2

2

 rejeito H0  não rejeito H0

Resultado do teste:

-t < -t crítico  rejeito HO

unicaudal à esquerda

-t > -t crítico  não rejeito H0 obs: os valores de t e t crítico são negativos

Stat t e t crítico Stat t e t crítico

Resultado do teste:

t > t crítico  rejeito H0

unicaudal à direita

t < t crítico  não rejeito HO

Resultado do teste:

-t crítico < t > t crítico  não rejeito H0

bicaudal

-t < - t crítico ou t > t crítico  rejeito HO


H0 :

1

H1 :

1

2

2

Stat t > t crítico (-1,20 > -1,79), não rejeitamos H0 ao nível de 5% de significância. Portanto, as vendas médias das duas publicidades são iguais, ou seja, não houve impacto das publicidades. Nota-se que, o nível descritivo é igual a 0,126, ou seja, para qualquer valor de que 0,12 a hipótese H0 não será rejeitada.


menor

42

VIII.3.4. Teste de hipóteses para as médias de duas população independentes e com variâncias diferentes Exemplo 3: Voltando ao exemplo 1 e supondo que H0 foi rejeitada, será realizada o teste de hipóteses para as médias de duas populações independentes e com variâncias diferentes: Procedimento: Amostra 1

Amostra 2 Valor que está sendo testado


Resultados: Teste-t: duas amostras presumindo variâncias diferentes

Média Variância Observações Hipótese da diferença de média gl Stat t P(T<=t) uni-caudal t crítico uni-caudal P(T<=t) bi-caudal t crítico bi-caudal

P1 P2 43,33333 48,71429 9,866667 109,9048 6 7 0 7 -1,292024 0,118683 1,894579 0,237366 2,364624




43



Stat t > 0


-


P(T<=t)=

Nível descritivo

d

H0 :

1

H1 :

1

H0 :

1

2

H1 :

1

2

H0 :

1

2

H1 :

1

2

d

d

Stat t e t crítico

2

2

 rejeição de H0  não rejeição de H0

Resultado do teste



-t> -t crítico  não rejeito H0 obs: os valores de t e t crítico são negativos


Resultado do teste

t> t crítico  rejeito H0



Resultado do teste


bicaudal



H0 :

1

H1 :

1

2

2

Stat t > t crítico (-1,29 > -1,89), não rejeitamos H0 ao nível de 5% de significância. Portanto, as vendas médias das duas publicidades são iguais, ou seja, não houve impacto das publicidades. Nota-se que, o nível descritivo é igual a 0,119, ou seja, para qualquer valor de que 0,119 a hipótese H0 não será rejeitada.


menor

44

VIII.3.5. Teste de hipóteses para as médias com duas populações pareadas (dependentes) O teste t para observações pareadas é usado para estudar o efeito de um experimento em que se observam os mesmos indivíduos duas vezes, isto é, uma vez antes, outra vez depois de administrar o experimento. Para realizar o teste no Excel informe na variável 1 os dados da amostra 2 (efeito depois do tratamento) e na variável 2 os dados da amostra 1 (antes do tratamento), pois o objetivo é verificar o efeito após o experimento ou tratamento. Exemplo 4: Dez pessoas selecionadas aleatoriamente foram usadas num teste para medir as suas capacidades em termos de ar inspirado, antes e depois de um tratamento. Se y for a capacidade da x for a capacidade da população antes do tratamento e população depois do tratamento. Mediu-se a capacidade de ar inspirado antes e depois do tratamento e os resultados estão no quadro abaixo. Indivíduo A B C D E F G H I J

Volume de ar inspirado Antes 2750 2360 2950 2830 2250 2100 2560 2305 2430 2550

Depois 2850 2380 2930 2860 2320 2150 2610 2300 2435 2650

Procedimento:

Amostra 2 (depois)

Amostra 1 (antes)

Valor que está sendo testado



45

Resultados: Teste-t: duas amostras em par para médias

Média Variância Observações Correlação de Pearson Hipótese da diferença de média gl Stat t P(T<=t) uni-caudal t crítico uni-caudal P(T<=t) bi-caudal t crítico bi-caudal

Depois Antes 2548,5 2508,5 73511,39 73811,39 10 10 0,988199 0 9 3,033361 0,007085 1,833113 0,014169 2,262157





H0 : D

0

H1 : D

0

onde: Di = xi – yi ; D Stat t > 0


H0 : D

0

H1 : D

0

onde: Di = xi – yi ; D -


H0 : D

0

H1 : D

0

onde: Di = xi – yi ; D P(T<=t)=

d

Nível descritivo

d

d

Stat t e t crítico

= média de Di

= média de Di

= média de Di

 rejeito H0  não rejeito H0

Resultado do teste:



-t > -t crítico  não rejeito H0 obs: os valores de t e t crítico são negativos


Resultado do teste:

t> t crítico  rejeito H0



Resultado do teste:


Bicaudal



46

Conclusão do exemplo 4: Teste de hipóteses: unicaudal à direita

H0 : D

0

H1 : D

0

Stat t > t crítico (3,03 > 1,83), rejeitamos H0 ao nível de 5% de significância. Portanto, há diferença entre as médias do volume de ar inspirado após e antes do tratamento. Ou seja, após o tratamento os pacientes apresentaram um volume de ar inspirado acima do observado anteriormente. Nota-se que, o nível descritivo é próximo de zero ( valor de

d

0 ). Portanto, para qualquer

a H0 será rejeitada.

VIII.4. Outras funções estatísticas Há outras funções estatísticas disponíveis no Excel que podem ser utilizadas para executar cálculos simples ou complexos. Procedimento de análise: 1) Selecione o menu Inserir -> selecione a opção Função -> selecione a categoria Estatística -> escolha a função


47

IX. Regressão Linear Simples Exemplo: A analista de mercado tem interesse em estabelecer uma relação entre o valor de venda e a avaliação do imóvel. Para o estudo foi selecionada uma amostra de cinco propriedades. Procedimento de análise: 1) Arquivo de dados

2) Ferramenta -> Análise de Dados -> Regressão


48

3) Entrar com as informações de X (variável independente) e Y (variável dependente) e selecionar os campos marcados

Variável dependente Variável independente

(1)

(3)

(2) Obs: (1) Usar quando o intercepto (B0) do modelo não for significativo (2) Verifica a distribuição de probabilidade da variável dependente (3) Análise gráfica dos resíduos


49

4) Resultados: RESUMO DOS RESULTADOS Estatística de regressão R múltiplo 0,989762411 R-Quadrado 0,97962963 R-quadrado ajustado 0,972839506 Erro padrão 0,605530071 Observações 5

(5) 1

Adequação do modelo ajustado

-1

ANOVA gl Regressão Resíduo Total

Interseção (6) Valor avaliado (7)

SQ 1 3 4

Coeficientes -2,2 2,3

0

(4)

MQ F F de significação 52,9 52,9 144,2727 0,001241545 1,1 0,366667 54

Erro padrão Stat t valor-P 95% inferiores 95% superiores Inferior 95,0%Superior 95,0% 0,81240384 -2,708013 0,073287 -4,785434026 0,385434026 -4,785434026 0,385434 0,191485422 12,01136 0,001242 1,690607356 2,909392644 1,690607356 2,909393

RESULTADOS DE RESÍDUOS

(1)

(2)

(3) RESULTADOS DE PROBABILIDADE

Observação Previsto(a) Valor de vendaResíduos Resíduos padrão 1 2,4 -0,4 -0,76277 2 4,7 0,3 0,572078 3 7 -8,88178E-16 -1,69E-15 4 9,3 0,7 1,334848 5 11,6 -0,6 -1,144155

Percentil

Valor de venda 10 30 50 70 90

2 5 7 10 11

Obs: (1) Teste de hipóteses dos parâmetros B0 e B1 (2) Nível descritivo do teste de hipóteses (1) (3) Intervalo de confiança da estimativa do parâmetro (4) Teste de hipóteses da adequação do modelo ajustado (5) Correlação de Pearson (6) Parâmetro B0 (intercepto) (7) Parâmetro B1 (inclinação da reta)


50

0

Uso do Excel para análises estatísticas - fsp.usp.br

Recommend Documents