Desvio Padrão no Excel

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

Desvio padrão Excel

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.

Desvio padrão Excel

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:

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Desvio padrão Excel

A tela de parâmetros é apresentada a seguir.

Desvio padrão Excel

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).

Desvio padrão Excel

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).

Desvio padrão Excel
Desvio padrão Excel

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:

Desvio padrão Excel

Podemos renomear os campos criados para nomes mais amigáveis. A imagem a seguir ilustra os resultados com 2 casas decimais.

Desvio padrão Excel

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.

Desvio padrão Excel

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.

Baixe a planilha