Baixe Estadística usando excel e outras Notas de estudo em PDF para Estatística, somente na Docsity! 1 Maximiliano Domingos Xavier ESTATÍSTICA USANDO EXCEL UNIDADE I REVISANDO O EXCEL REVISANDO FUNÇÕES • Função Soma Ex: =SOMA(A1:A8) A fórmula irá somar todos os valores que se encontram no endereço A1 até o endereço A8. Os dois pontos indicam até, ou seja, some de A1 até A8. A fórmula será sempre a mesma, só mudará os devidos endereços dos valores que você deseja somar. Veja o outro exemplo: A B C D E 1 10 25 15 10 =SOMA(A1:D1) 2 3 Neste exemplo estamos somando todos os valores do endereço A1 até o endereço D1. A fórmula seria digitada como no exemplo, e ao teclar ENTER o valor apareceria. No caso a resposta seria 60. • Função Média Calcula-se a média de uma faixa de valores, após somados os valores e divididos pela quantidade dos mesmos. Exemplo: Suponhamos que desejasse saber qual a média de idade numa tabela de dados abaixo: A B C 1 IDADE 2 15 3 16 4 25 5 30 6 MEDIA IDADE =MEDIA(A2:A5) • Função Cont.Valores Conta-se o número de indivíduos numa determinada faixa de valores. 2 Maximiliano Domingos Xavier Exemplo: Suponhamos que desejasse saber o total de alunos desta turma que está listada na tabela abaixo: A resposta seria 4. A B C 1 ALUNOS 2 JOÃO 3 FRANCISCO 4 CARLOS 5 MARIA 6 TOTAL =CONT.VALORES(A2:A5) • Função Condicional SE Suponhamos que desejasse criar um Controle de Notas de Aluno, onde ao se calcular a média, ele automaticamente especificasse se o aluno fora aprovado ou não. Então Veja o exemplo abaixo. Primeiramente, você precisa entender o que desejar fazer. Por exemplo: quero que no campo situação ele escreva Aprovado somente se o aluno tirar uma nota Maior ou igual a 7 na média, caso contrário ele deverá escrever Reprovado, já que o aluno não atingiu a condição para passar. Veja como você deve escrever a fórmula utilizando a função do SE> A B C 1 ALUNO MÉDIA SITUAÇÃO 2 Márcio 7 =SE(B2>=7;”Aprovado”;”Reprovado”) 3 “Bem, agora que você lembrou como usar algumas funções básicas do Excel daremos início as nossas planilhas eletrônicas.” EXERCÍCIO Usando a planilha que recebeu do instrutor. Calcule e formate a planilha deixando-a como no exemplo abaixo. 5 Maximiliano Domingos Xavier UNIDADE II Estatística Descritiva Algumas das funções estatísticas e todas as ferramentas de análise do Excel utilizadas neste curso, geralmente, não são incluídas ao iniciar o Excel. Sugerimos que o aluno realize a verificação seguinte antes de darmos início ao curso. • No menu Ferramentas escolha Suplementos. O Excel apresentará a caixa de diálogo Suplementos com os Suplementos disponíveis. • O suplemento Ferramentas de análise deve estar selecionado como mostra a figura seguinte. O objetivo da Estatística Descritiva é organizar, resumir, analisar e interpretar observações disponíveis. 1 - VARIÁVEIS Variável é uma característica da unidade elementar que pode ter valores diferentes entre as unidades medidas. Exemplo: A tabela seguinte registra parte do Cadastro de Funcionários da empresa. Quais são as unidades elementares e as variáveis deste cadastro? Nome Idade Cargo Sexo Peso Escolaridade João 27 Supervisor M 62 kg 2º Grau Alex 38 Chefe M 78 kg 3º Grau Estela 34 Gerente F 65 kg 3º Grau Ana 32 Secretária F 58 kg 1º Grau Solução. As seis variáveis de cada funcionário da empresa compõem uma unidade elementar: Nome, Idade, Cargo, Sexo, Peso e Escolaridade. 6 Maximiliano Domingos Xavier 1.1 - Classificação das variáveis 1.1.1 - Variáveis Quantitativas: Refere-se a quantidades medidas numa escala numérica. As variáveis quantitativas podem ser do tipo: Discretas e Contínuas • Variáveis Discretas – Referem-se a variáveis numéricas que assumem somente números inteiros positivos. Ex: Quant. De vendas diárias de uma empresa; Quant. De peças defeituosas num lote de produção. • Variáveis Contínuas – Assumem qualquer valor dos números reais. Ex: Valor da venda diária de uma empresa; Consumo mensal de energia elétrica. 1.1.2 – Variáveis Qualitativas: Referem-se a variáveis não numéricas. As variáveis qualitativas poder ser classificadas como nominais e ordinais. • Variáveis nominais – Não tem ordenamento nem hierarquia. Ex: Sexo, Estado Civil. • Variáveis ordinais – São equivalentes as variáveis nominais, porém incluindo uma ordem. Ex: Bom, Regular, Ruim; Primeira, Segunda, Terceira. 1.1.3 – Seqüência Temporal. Ex: As cotações diárias de uma ação na bolsa; A demanda de energia elétrica diária. 2 – POPULAÇÃO E AMOSTRA • O grupo completo de unidades elementares de pessoas, objetos ou coisas é denominado população. • Um subconjunto de unidades elementares selecionados numa população é denominado amostra. 2.1 – Tipos de Pesquisa • CENSO – Pesquisa que coleta a totalidade de uma população em busca da observação de determinadas características. • AMOSTRAGEM – Pesquisa que coleta um pequeno grupo de indivíduos de uma população em busca da observação de determinadas características. Amostra Muitas aplicações de estatística operam com amostras retiradas de uma população sobre a qual se deseja obter respostas. Simplesmente amostrar não é suficiente, a amostra deve ser representativa da população. Uma amostra representativa tem as mesmas características da população de onde foi retirada. 7 Maximiliano Domingos Xavier Gerando Dígitos Aleatórios com o Excel O Excel 2003 dispõe das funções ALEATÓRIO e ALEATÓRIOENTRE para gerar dígitos aleatórios e da ferramenta de análise Amostragem para extrair amostras com reposição de uma população. ALEATÓRIO() A função matemática ALEATÓRIO dá um grupo de dígitos aleatórios entre 0,00...0 e 1,00...0 com a quantidade de dígitos depois da vírgula definida pelo leitor, por exemplo: 0,236; 0,86945, etc. ALEATÓRIOENTRE(inferior; superior) A função estatística ALEATÓRIOENTRE dá um grupo de dígitos aleatórios entre os grupos inferior e superior definidos nos argumentos da função. Inserindo numa célula da planilha Excel a fórmula =ALEATÓRIOENTRE(0;599), o Excel fornecerá como resultado um grupo de dígitos entre o grupo 000 e o grupo 599. Ferramenta de Análise Amostragem 3 - TABELAS DE FREQÜÊNCIA A freqüência do valor de uma variável é o número de repetições desse valor. • Freqüência Absoluta: É a contagem das repetições de cada valor dentro da variável; • Freqüência Relativa: É o resultado de dividir sua freqüência absoluta pelo tamanho da amostra. • Freqüência Acumulada: É a soma das freqüências absolutas ou relativas desde o valor inicial da variável. 10 Maximiliano Domingos Xavier Alunos de Estatística 0% 10% 20% 30% 40% 50% 60% 70% 18-23 23-28 28-33 33-38 38-43 43-48 48-53 Idade % Fonte: Base de Dados Alunos de Estatística – UFRN 2007. 4 – GRÁFICO DE PARETO Para que serve o Gráfico de Pareto? O gráfico de Pareto deve ser utilizado sempre que temos que estabelecer prioridades a partir de um número variado de informações e dados. Ajuda a dirigir nossa atenção e esforços para problemas verdadeiramente importantes, aumentando nossas chances de obtenção de bons resultados. Utilizaremos a tabela abaixo para demonstrar o gráfico de pareto Causas de não realização de um gol Eventos Frequência A Passe errado 52 B Chute errado a gol 26 C Falta sofrida 10 D Impedimento 06 E Erro do juiz 02 F Queda do Jogador 01 G Defesa do goleiro 01 Total 98 Tabela 1.2 11 Maximiliano Domingos Xavier Fonte: Tabela 1.2 Assim, considerando o processo de melhoria da qualidade, o gráfico de Pareto é utilizado nas seguintes situações: • Na seleção de problemas que serão como projetos de melhoria de qualidade; • Na identificação das causas fundamentais de problema; • Na comparação entre o antes e o depois de uma ação corretiva. 5 – MEDIDAS DE ORDENAMENTO Com as tabelas de freqüências e os métodos gráficos organizamos e começamos a análise dos valores de uma variável. Agora iniciamos os métodos numéricos para resumir e analisar os valores de uma variável, seja uma amostra ou a própria população. Em algumas situações o interesse está em conhecer a posição de um determinado valor em relação ao grupo de valores. Por exemplo, qual a posição de um retorno de 15% ao ano com relação às rentabilidades anuais das aplicações do mercado financeiro? Quantos retornos do mercado financeiro são maiores que 15 %. 5.1 - Percentil Um percentil é uma medida da posição relativa de uma unidade obsservacional em relação a todas as outras. O p-ésimo porcentil tem no mínimo p% dos valores abaixo daquele ponto e no mínimo (100 - p)% dos valores acima. Por exemplo, • se uma altura de 1,80m é o 90o. percentil de uma turma de estudantes, então 90% da turma tem alturas menores que 1,80m e 10% têm altura superior a 1,80m. 12 Maximiliano Domingos Xavier • se o peso de uma pessoa de 75kg é o 40o. percentil de um conjunto de empregados. então 40% dos empregados pesam menos que 75kg e 60% pesam mais. No Excel a função estatística PERCENTIL dá o valor que divide a matriz em duas partes, uma menor que o argumento k e a outra maior que k. O argumento k é um valor entre 0 e 1 correspondendo respectivamente a 0% e 100% do número de valores da matriz. Alguns percentis da variável idade da Base de Dados Alunos Estatística - UFRN 2007. Formula: =PERCENTIL(Matriz; k) Percentil 10 = 19 Percentil 20 = 20 Percentil 50 = 22 Percentil 82 = 26 Percentil 94 = 38 5.2 - Quartil Os percentis dividem o intervalo de variação dos valores da variável em cem partes iguais. Dividindo os valores ordenados da variável em quatro quartos iguais se obtém três quartis denominados: primeiro quartil, segundo quartil e terceiro quartil. Numa variável com n valores ordenados de forma crescente: • O primeiro quartil Q1 é um valor tal que 25% dos valores da variável são menores e os restantes 75% são maiores. • O segundo quartil Q2 é um valor tal que 50% dos valores da variável são menores e os restantes 50% são maiores. • O terceiro quartil Q3 é um valor tal que 75% dos valores da variável são menores e os restantes 25% são maiores. Quartis da variável idade da Base de Dados Alunos Estatística - UFRN 2007. Formula: =QUARTIL(Matriz; quarto) Quartil 1 (25%) = 20 Quartil 2 (50%) = 22 Quartil 3 (75%) = 22 6 - MEDIDAS DE TENDÊNCIA CENTRAL As medidas de tendência central nos dão uma noção sobre qual valor representa o centro de uma distribuição ordenada (rol), ou simplesmente um valor que melhor represente essa distribuição 15 Maximiliano Domingos Xavier 7.3 – Coeficiente de Variação O coeficiente de variação CV é o resultado de dividir o desvio padrão pela sua média. A medida relativa CV permite a comparação de distribuições, pois seu resultado é o desvio padrão por unidade de média. Comparando duas variáveis, a variável que tiver menor CV tem menor dispersão ou variabilidade. 8 – INCLINAÇÃO DE UMA DISTRIBUIÇÃO O coeficiente de inclinação é o resultado de comparar a distribuição de frequências dos valores de uma variável com uma distribuição normal. O resultado da função DISTORÇÃO deve ser interpretado da seguinte forma: • Se o coeficiente de inclinação for igual a zero, a distribuição de frequências será simétrica. • Se o coeficiente de inclinação for negativo, a distribuição de frequências terá inclinação esquerda ou negativa. • Se o coeficiente de inclinação for positivo, a distribuição de frequências terá inclinação direita ou positiva. 9 – CURTOSE DE UMA DISTRIBUIÇÃO Se duas distribuições de frequências têm a mesma dispersão e inclinação não será suficiente para supor que as duas tenham a mesma forma, característica denominada curtose. A curtose é medida pelo coeficiente de curtose que compara a distribuição de frequências de amostras com a distribuição normal. O resultado desta função deve ser interpretado da seguinte forma: • Se o coeficiente de curtose for igual a zero, então a distribuição de frequências será a própria distribuição normal. • Se o coeficiente de curtose for negativo, então a distribuição será achatada, plana. • Se o coeficiente de curtose for positivo, a distribuição será concentrada ao redor da média, distribuição com pico. No Excel: = CURT(núm1;núm2;...;núm30) No Excel: =DISTORÇÃO(núm1;núm2;...;núm30) 16 Maximiliano Domingos Xavier 10 - FERRAMENTA DE ANÁLISE ESTATÍSTICA DESCRITIVA No menu Ferramentas escolhemos Análise de Dados e recebemos a caixa de diálogo com todas as ferramentas de análise disponíveis. Depois de escolher Estatística Descritiva e pressionar o botão Ok o Excel apresenta a caixa de diálogo Estatística descritiva. Para utilizar a ferramenta de análise Estatística Descritiva utilizaremos a Variável Idade da planilha Base de Dados Alunos de Estatística – UFRN 2007. 17 Maximiliano Domingos Xavier 11 - BOXPLOT 11.1 – Criando o boxplot no Excel 1. O primeiro passo é gerar as estatísticas para o conjunto de dados conforme descrito abaixo, e na mesma seqüência. Dados 1 Dados 2 1º Quartil 155 170 Mínimo 149 155 Mediana 160 173,5 Máximo 173 181 3º Quartil 165 175,75 2. Selecione toda a informação, incluindo os rótulos de dados e depois selecione Inserir > Gráfico e escolha Linha. Selecione o sub-tipo ‘linha com marcadores exibidos a cada valor de dado’. Clique em avançar, e selecione ‘séries em: linhas’ e depois click em ‘concluir’. 3. Agora serão executados comando sobre o gráfico. Clique com o botão direito do mouse sobre uma das linhas do gráfico. Selecione ‘formatar série de dados’, vá para ‘opções’ e selecione ‘linhas de máximo/mínimo’ e ‘barras superiores/inferiores’. A ‘largura do espaçamento’ poderá variar entre diversos valores, pois isso não importa para o Boxplot. O resultado deverá ser algo do tipo: 4. As linhas serão removidas uma a uma, inicialmente clicando com o botão direito do mouse sobre a linha. Selecionar ‘formatar série de dados’ em ‘padrões’ observar o campo ‘linha’ e marcar sobre ‘nenhuma’, da mesma forma para o campo ‘marcador’, deixando apenas o marcador para a ‘linha da mediana’. Isso deve ser feito para todas as linhas. Em seguida, clique com o botão direito sobre a área do gráfico, selecione ‘opções de gráfico’, na guia ‘linhas de grade’ desmarque todas as opções de linha e na ‘guia legenda’ desmarque a opção ‘mostrar legenda’. O resultado final será como está abaixo. 140 145 150 155 160 165 170 175 180 185 F M 140 145 150 155 160 165 170 175 180 185 F M 1º Quartil Mínimo Mediana Máximo 3º Quartil 20 Maximiliano Domingos Xavier UNIDADE III APLICAÇÕES 1 – Criando um gráfico dinâmico para acompanhamento de processos. 21 Maximiliano Domingos Xavier 2 – Transformando uma planilha comum em um Banco de Dados. Relatório de Anomalias - Planilha Relatório de Anomalias – Banco de Dados 22 Maximiliano Domingos Xavier 3 - Montando uma Árvore de Análises para definir qual o caminho indicativo que deverá ser seguido na hora de detalhar os níveis da informação. ÁRVORE DE ANÁLISES Gráficos seguindo a ordem da Árvore Eficiência por Unidade 91,9% 91,2% 44,2% 55,8% 90,8% 91,0% 91,2% 91,4% 91,6% 91,8% 92,0% Tecelagem 1 Tecelagem 2 % E fi ci ên ci a 0,0% 10,0% 20,0% 30,0% 40,0% 50,0% 60,0% % P ro d u çã o Eficiência da "Tecelagem 2" por tecnologia 90,7% 91,5% 91,4% 91,1% 89,5% 90,0% 90,5% 91,0% 91,5% 92,0% L5200 ZAX % E fic iê n ci a 90,9% 91,0% 91,0% 91,1% 91,1% 91,2% 91,2% 91,3% 91,3% 91,4% 91,4% 91,5% % M et a Eficiência da "Tecelagem 2 - L5200" por Artigo 90,3% 92,7% 91,4% 92,1% 89,0% 89,5% 90,0% 90,5% 91,0% 91,5% 92,0% 92,5% 93,0% RIOLEEN RIOLEEN-157 % E fi ci ên ci a 88,0% 88,5% 89,0% 89,5% 90,0% 90,5% 91,0% 91,5% 92,0% 92,5% % M et a Eficiência por Grupo/Turno "L5200" 92,9% 92,8% 92,4% 92,2% 92,2% 91,8% 91,6% 91,4% 91,0% 91,0% 90,7% 90,6% 90,1% 87,7% 86,9% 82,0% 84,0% 86,0% 88,0% 90,0% 92,0% 94,0% 4C 3A 5B 4B 2C 2B 5C 4A 2A 3C 3B 5A 1C 1B 1A Acima da Meta Tolerância Abaixo da Meta Meta