Neste artigo você aprenderá detalhadamente sobre como usar o Desvio Padrão no Excel.
1. CONCEITO
O desvio padrão é uma medida que indica o quanto uniforme é um conjunto de dados ao expressar o seu grau de dispersão em torno da média. Quanto menor for o desvio padrão, mais homogêneos são os dados, estando os valores mais próximos da média. Um alto desvio implica dizer que os dados estão espalhados longe da média. Podemos ter duas abordagens: desvio populacional e desvio amostral. É discutível se devemos escolher o desvio amostral ou o desvio populacional, porém é senso comum que, quanto maior o tamanho da amostra, melhor será para a análise. Amostras muito pequenas são mais sujeitas às imprecisões das conclusões das análises (resultados enviesados).
2. FÓRMULAS
Matematicamente, o desvio padrão pode ser expresso pela raiz quadrada: da soma de todos os quadrados das diferenças de cada elemento em relação à média, dividida pelo número de elementos (quantidade de registros dos dados). Esta fórmula expressa o desvio populacional. Para o desvio amostral, a fórmula é bem similar, exceto pelo fato de a divisão não ser pelo número de elementos (quantidade de registros dos dados), e sim, pelo número de elementos subtraído de um.
2.1. Desvio padrão populacional
Sendo:
σ: desvio padrão populacional
xi: cada valor no conjunto de dados da população
µ: média aritmética da população
N: número de elementos da população
2.2. Desvio padrão amostral
Sendo:
Sx: desvio padrão amostral
xi: cada valor no conjunto de dados da amostra
x: média aritmética da amostra
n: número de elementos da amostra
Obs.: em todos os resultados de cálculos apresentados, os valores encontram-se arredondados.
3. EXEMPLOS
Para os 2 conjuntos de dados a seguir temos a mesma média: 5. No entanto, o segundo conjunto de dados possui valores claramente mais distantes da média. Matematicamente podemos encontrar os valores dos desvios de cada conjunto e categoricamente provar o porquê o desvio padrão do segundo conjunto é maior do que o do primeiro conjunto.
Conjunto 1 – 4,5 – 5,0 – 4,5 – 5,0 – 5,0 – 6,0
Conjunto 2 – 7,0 – 5,0 – 2,0 – 1,0 – 1,0 – 14,0
Seguindo a fórmula do desvio populacional, o conjunto 1 apresentado possui desvio = 0,50, enquanto que o conjunto 2 possui desvio padrão aproximadamente 4,58. Seguindo a fórmula do desvio amostral, o conjunto 1 possui desvio = 0,55, enquanto que o conjunto 2 possui desvio aproximadamente 5,02. Em qualquer abordagem, seja populacional ou amostral, o desvio do segundo conjunto é bem superior ao desvio do primeiro conjunto. As fórmulas são apresentadas a seguir.
4. COMO CALCULAR O DESVIO PADRÃO NO EXCEL
4.1. Usando funções apropriadas
No Excel podemos calcular o desvio padrão construindo fórmulas ao empregar funções estatísticas, como DESVPAD.A (desvio amostral) e DESVPAD.P (desvio populacional). Ambas ignoram valores lógicos e de texto. Há ainda as funções DESVPADA (desvio amostral) e DESVPADPA (desvio populacional) que levam em consideração valores lógicos e de texto. Outras duas funções podem ser ainda encontradas em modo de compatibilidade: DESVPAD e DESVPADP. Para inserir estas funções basta digitar o sinal de igual (ou arroba – @) seguido de “DESVP”. O catálogo de funções do Excel exibirá as opções disponíveis.
Todas estas funções são de fácil aplicação, requerendo como argumento apenas o intervalo (contínuo ou não) dos dados. Para os conjuntos 1 e 2 supracitados ficariam assim calculados os desvios padrões, tanto populacionais quanto amostrais:
4.2. Usando funções mais básicas
Podemos reproduzir o cálculo do desvio padrão usando funções mais genéricas. Aninhar as funções RAIZ, SOMA, MÉDIA E LINS permite calcular o desvio em ambas abordagens citadas. A imagem a seguir mostra como ficariam as fórmulas, que são matriciais. Considere a necessidade de fechar a fórmula pressionando o atalho CTRL + SHIFT + ENTER (a depender da versão do Excel utilizada), em vez apenas ENTER.
5. CALCULANDO O DESVIO PADRÃO EM DADOS FILTRADOS
Através da função SUBTOTAL podemos calcular o desvio padrão de modo que o resultado seja sensível ao filtro. Ao empregarmos a função SUBTOTAL temos o leque de opções que podemos aplicar. Podemos escolher a opção desejada através de seu número correspondente. Podemos, por exemplo, escolher o número 7 para calcular o desvio amostral ou ainda o número 8 para calcular o desvio populacional.
A imagem a seguir mostra uma tabela de nome fDados em que precisamos calcular o desvio padrão dos dados do último campo, rotulado com a palavra Valor. Veja o desvio populacional e amostral, calculados no primeiro momento sem a aplicação de filtro nos dados.
Já a próxima imagem considera a mesma tabela de dados, sendo desta vez apenas com os dados presentes no filtro (dados visíveis). Selecionamos apenas a unidade federativa MG e isso resultou em um desvio populacional de 7,27 e em um desvio padrão amostral de 8,39.
Note que os resultados são diferentes quando comparamos os desvios calculados com e sem o filtro empregado. Cada filtro empregado poderá alterar os valores visíveis no último campo e consequentemente poderá alterar o desvio calculado para tais dados.
A função SUBTOTAL permite também desconsiderar itens que foram ocultados manualmente (linhas ocultas da tabela). Podemos usar os parâmetros 108 ou 107, neste caso.
6. CALCULANDO O DESVIO PADRÃO COM O SUPLEMENTO DE ANÁLISE DE DADOS
Podemos ativar o suplemento de análise de dados para calcular o desvio (amostral, neste caso). Para ativar este suplemento, acesse Arquivo > Opções > Suplementos. No rodapé da janela de opções, em Gerenciar, clique no botão Ir. Aparecerá uma janela similar (ou idêntica) à janela a seguir.
Marque a opção “Ferramentas de Análise” e confirme com o botão “Ok”. Com isso, o acesso ao suplemento poderá ser feito através da Guia Dados, no grupo Análise.
Ao acionar o suplemento, aparecerá a janela a seguir. Basta escolher a opção Estatística Descritiva para ir até a tela de parâmetros.
A tela de parâmetros é apresentada a seguir.
Em “Intervalo de entrada” é necessário indicar a faixa de valores. Se você optar por selecionar os dados incluindo o nome do campo, deverá marcar a opção “Rótulos na primeira linha”. O resultado da análise poderá ser colocado em uma nova planilha ou pasta de trabalho a serem criadas, ou ainda na mesma planilha, em um intervalo a ser indicado (“Intervalo de saída”).
Na configuração acima definida vamos criar uma planilha nomeada como “Análise” e faremos um resumo estatístico descritivo com os dados presentes entre D3 e D25. Ao clicar no botão “Ok”, a planilha “Análise” apresentará os resultados da análise, contendo o desvio padrão amostral (na célula B7 da imagem a seguir, com 5 casas decimais).
7. CALCULANDO DESVIO PADRÃO USANDO DAX
Podemos empregar funções em DAX para calcular o desvio padrão. Usamos a função STDEV.P para calcular em DAX o desvio padrão populacional e STDEV.S para calcular em DAX o desvio padrão amostral. As imagens a seguir apresentam os cálculos em DAX usando os mesmos dados do exemplo anterior (tabela fDados).
8. CALCULANDO DESVIO PADRÃO DIRETO NA TABELA DINÂMICA
Podemos criar tabelas dinâmicas e resumir os dados para obter o desvio padrão. Após arrastar o campo com os valores para o local apropriado, devemos escolher uma das opções a seguir:
Podemos renomear os campos criados para nomes mais amigáveis. A imagem a seguir ilustra os resultados com 2 casas decimais.
Ao arrastar o campo dos estados para o quadrante de linhas obteremos os desvios de cada UF, bem como o totalizador geral. Note que para MG os desvios são os mesmos apresentados na seção 5 deste artigo.
Download Planilha Desvio Padrão Excel
Realize o download da planilha de exemplo neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
