Desvio Padrão - Excel e Planilhas Google

Este tutorial demonstra como usar o Função de desvio padrão do Excel no Excel para calcular o desvio padrão para uma população inteira.

Visão geral da função DESVIO PADRÃO

A função DESVIO PADRÃO Calcula o desvio padrão do cálculo para uma população inteira.

Para usar a função de planilha do Excel DESVIO PADRÃO, selecione uma célula e digite:

(Observe como as entradas da fórmula aparecem)

Sintaxe da função DESVIO PADRÃO e entradas:

1	= STDEV (número1, [número2], …)

números- Valores para obter a variação padrão

Como calcular o desvio padrão no Excel

Sempre que estiver lidando com dados, você desejará executar alguns testes básicos para ajudá-lo a entendê-los. Você normalmente começará calculando a média, usando a função AVERAGE do Excel <>.

Isso lhe dá uma ideia de onde está o “meio” dos dados. E a partir daí, você vai querer ver como os dados estão espalhados em torno desse ponto médio. É aqui que entra o desvio padrão.

O Excel oferece várias funções para calcular o desvio padrão - STDEV, STDEV.P, STDEV.S e DSTDEV. Iremos chegar a todos eles, mas primeiro, vamos aprender qual é o desvio padrão é, exatamente.

Qual é o desvio padrão?

O desvio padrão dá uma ideia de quão longe seus pontos de dados estão da média. Pegue o seguinte conjunto de dados de pontuações de teste em 100:

1	48,49,50,51,52

A média desse conjunto de dados é 50 (some todos os números e divida por n, onde n é o número de valores no intervalo).

Agora olhe para este próximo conjunto de dados:

1	10,25,50,75,90

A média deste conjunto de dados é tb 50 - mas os dois intervalos contam uma história muito diferente. Se você acabou de usar a média, pode pensar que os dois grupos são quase iguais em sua aptidão - e, em média, são.

Mas, no primeiro grupo, temos 5 pessoas que obtiveram pontuações muito semelhantes e medíocres. E no segundo grupo, nós dois jogadores de alto desempenho equilibrados por alguns de pontuadores ruins, com uma pessoa no meio. o espalhar das pontuações é muito diferente, tornando sua interpretação dos dados muito diferente também.

O desvio padrão é uma medida dessa dispersão.

Como o desvio padrão é calculado

Para entender o que é o desvio padrão e como ele funciona, pode ser útil trabalhar com um exemplo à mão. Dessa forma, você saberá o que está acontecendo "por baixo do capô" assim que chegarmos às funções do Excel que você pode usar.

Para calcular o desvio padrão, você trabalha através deste processo:

1) Calcule a média

Vamos pegar nosso primeiro conjunto de dados acima: 48,49,50,51,52

Já sabemos a média (50), que eu confirmei aqui com a função AVERAGE do Excel <>:

1	= MÉDIA (C4: C8)

2) Subtraia a média de cada valor no conjunto de dados

Eu fiz isso com a seguinte fórmula:

1	= C4- $ H $ 4

Nossa média está em H4, e eu "travei" a referência da célula colocando os cifrões antes da coluna e da linha (pressionando F4). Isso significa que posso copiar a fórmula para baixo na coluna sem a atualização da referência da célula.

O resultado:

Agora, vamos fazer uma pausa aqui por um segundo. Se você der uma olhada na nova coluna, verá que os números aqui somam zero. A média desses números também é zero.

Claro, a propagação de nossos dados não pode ser zero - sabemos que há alguma variação nisso. Precisamos de uma maneira de representar essa variação, sem que a média seja zero.

3) Quadrar as diferenças

Podemos conseguir isso quadrando as diferenças. Então, vamos adicionar uma nova coluna e elevar ao quadrado os números na coluna D:

1

= D4 * D4

Isso está parecendo melhor. Agora temos alguma variação, e a quantidade de variação está relacionada a quão longe cada pontuação está da média.

4) Calcule a variância - a média das diferenças quadradas

A próxima etapa é obter a média dessas diferenças quadradas. Na verdade, existem duas maneiras de fazer isso ao calcular o desvio padrão.

• Se você estiver usando dados populacionais, você simplesmente pega a média (soma os valores e divide por n)
• Se você estiver usando dados de amostra, você soma os valores e divide por n-1

Dados populacionais significam que você tem o “conjunto completo” de seus dados, por exemplo, você tem dados sobre cada aluno em uma determinada classe.

Dados de amostra significam que você não tem todos os seus dados, apenas uma amostra retirada de uma população maior. Normalmente, seu objetivo com dados de amostra é fazer uma estimativa de qual é o valor na população maior.

Uma pesquisa de opinião política é um bom exemplo de dados de amostra - os pesquisadores pesquisam, digamos, 1.000 pessoas para ter uma ideia do que um país ou estado inteiro está pensando.

Aqui não temos uma amostra. Temos apenas cinco membros da família com mentalidade estatística que desejam calcular o desvio padrão de um teste que todos fizeram. Temos todos os pontos de dados e não estamos fazendo uma estimativa de um grupo maior de pessoas. Estes são os dados da população - então podemos apenas tirar a média aqui:

1	= MÉDIA (E4: E8)

OK, então temos 2. Essa pontuação é conhecida como "variância" e é o ponto base para muitos testes estatísticos, incluindo o desvio padrão. Você pode ler mais sobre a variação em sua página principal: como calcular a variação no Excel <>.

5) Obtenha a raiz quadrada da variação

Nós elevamos nossos números ao quadrado antes, o que obviamente aumenta um pouco os valores. Portanto, para trazer o número de volta em linha com as diferenças reais das pontuações em relação à média, precisamos estabelecer a raiz quadrada do resultado da etapa 4:

1	= SQRT (H4)

E temos nosso resultado: o desvio padrão é 1,414

Porque fizemos a raiz quadrada de nossos números anteriormente quadrados, o desvio padrão dado nas mesmas unidades que os dados originais. Portanto, o desvio padrão aqui é 1,414 pontos de teste.

Desvio padrão quando os dados estão mais espalhados

Anteriormente, tínhamos um segundo intervalo de dados de exemplo: 10,25,50,75,90

Apenas por diversão, vamos ver o que acontece quando calculamos o desvio padrão nesses dados:

Todas as fórmulas são exatamente as mesmas de antes (observe que a média geral ainda é 50).

A única coisa que mudou foi a dispersão das pontuações na coluna C. Mas agora, nosso desvio padrão é muito maior, em 29,832 pontos de teste.

Claro, uma vez que temos apenas 5 pontos de dados, é muito fácil ver que a distribuição das pontuações é diferente entre os dois conjuntos. Mas quando você tem centenas ou 1.000 pontos de dados, você não pode dizer isso apenas digitalizando rapidamente os dados. E é exatamente por isso que usamos o desvio padrão.

As funções do Excel para calcular o desvio padrão

Agora que você sabe como funciona o desvio padrão, não precisa passar por todo o processo para chegar ao desvio padrão. Você pode apenas usar uma das funções integradas do Excel.

O Excel possui várias funções para esse fim:

• P calcula o desvio padrão para dados populacionais (usando o método exato que usamos no exemplo acima)
• S calcula o desvio padrão para dados de amostra (usando o método n-1 que mencionamos anteriormente)
• STDEV é exatamente igual a STDEV.S. Esta é uma função mais antiga que foi substituída por STDEV.S e STDEV.P.
• STDEVA é muito semelhante a STDEV.S, exceto que inclui células de texto e células Booleanas (VERDADEIRO / FALSO) ao fazer seu cálculo.
• STDEVPA é muito semelhante a STDEV.P, exceto que inclui células de texto e células Booleanas (VERDADEIRO / FALSO) ao fazer seu cálculo.

Nossa, muitas opções aqui! Não se deixe intimidar - na grande maioria dos casos, você usará STDEV.P ou STDEV.S.

Vamos examinar cada um deles, começando com STDEV.P, já que esse é o método que acabamos de trabalhar.

A função STDEV.P do Excel

STDEV.P calcula o desvio padrão para dados populacionais. Você o usa assim:

1	= STDEV.P (C4: C8)

Você define um argumento em STDEV.P: o intervalo de dados para o qual deseja calcular o desvio padrão.

Este é o mesmo exemplo que vimos passo a passo acima quando calculamos o desvio padrão manualmente. E como você pode ver acima, obtemos exatamente o mesmo resultado - 1,414.

Observação STDEV.P ignora quaisquer células que contenham texto ou valores booleanos (VERDADEIRO / FALSO). Se você precisar incluí-los, use STDEVPA.

A função STDEV.S do Excel

STDEV.S calcula o desvio padrão para dados de amostra. Use-o assim:

1	= STDEV.S (C4: C8)

Novamente, é necessário um argumento - a faixa de dados para a qual você deseja saber o desvio padrão.

Antes de entrarmos em um exemplo, vamos discutir a diferença entre STDEV.S e STDEV.P.

Como já discutimos, STDEV.S deve ser usado em dados de amostra - quando seus dados são uma parte de um conjunto maior. Então, vamos supor que, em nosso exemplo acima, mais pessoas tenham feito o teste. Queremos estimar o desvio padrão de todos que fizeram o teste, usando apenas essas cinco pontuações. Agora estamos usando dados de amostra.

Agora, o cálculo difere da etapa (4) acima, quando calculamos a variância - a média da diferença quadrática de cada pontuação da média geral.

Em vez de usar o método normal - somar todos os valores e dividir por n, somaríamos todos os valores e dividiríamos por n-1:

1	= SOMA (E4: E8) / (CONTAR (E4: E8) -1)

Nesta fórmula:

• SUM obtém a soma das diferenças quadradas
• COUNT retorna nosso n, que subtraímos 1 de
• Em seguida, simplesmente dividimos nossa soma por nosso n-1

Desta vez, a média das diferenças quadradas é 2,5 (você deve se lembrar que era 2 anteriormente, então é um pouco maior).

Então, por que dividimos por n-1 em vez de n ao lidar com dados de amostra?

A resposta é bastante complexa, e se você está apenas tentando executar seus números para entender seus dados, não é algo com que você realmente precise se preocupar. Apenas certifique-se de usar STDEV.S para dados de amostra e STDEV.P para dados populacionais, e você ficará bem.

Se você está realmente curioso para saber o porquê, consulte a página principal sobre como calcular a variação no Excel <>.

OK, agora temos a variância da amostra, então, para obter o desvio padrão da amostra, obteríamos apenas a raiz quadrada da variância:

1	= SQRT (H4)

Temos 1.581.

STDEV.S faz todos os cálculos acima para nós e retorna o desvio padrão da amostra em apenas uma célula. Então, vamos ver o que acontece …

1	= STDEV.S (C4: C8)

Sim, 1.581 novamente.

A função STDEV do Excel

A função STDEV do Excel funciona exatamente da mesma maneira que STDEV.S - ou seja, ela calcula o desvio padrão para uma amostra de dados.

Você o usa da mesma maneira:

1	= STDEV (C4: C8)

Novamente, obtemos o mesmo resultado.

Nota importante: STDEV é uma “Função de Compatibilidade”, o que basicamente significa que a Microsoft está se livrando dela. Ainda funciona por enquanto, portanto, as planilhas antigas continuarão funcionando normalmente. Mas em versões futuras do Excel, a Microsoft pode descartá-lo completamente, então você deve usar DESVPAD.S em vez de DESVPAD sempre que possível.

A função STDEVA do Excel

STDEVA também é usado para calcular o desvio padrão para uma amostra, mas tem algumas diferenças importantes que você precisa saber:

• Os valores VERDADEIROS são contados como 1
• Valores FALSE são contados como 0
• Strings de texto são contadas como 0

Use-o da seguinte maneira:

1	= STDEVA (C4: C8)

Mais quatro amigos e familiares deram suas pontuações nos testes. Eles são mostrados na coluna C e a coluna D indica como STDEVA interpreta esses dados.

Como essas células são interpretadas como valores baixos, isso cria uma distribuição muito maior entre nossos dados do que vimos antes, o que aumentou muito o desvio padrão, agora em 26,246.

A função STDEVPA do Excel

STDEVPA calcula o desvio padrão para uma população da mesma forma que STDEV.P. No entanto, também inclui valores booleanos e strings de texto no cálculo, que são interpretados da seguinte maneira:

• Os valores VERDADEIROS são contados como 1
• Valores FALSE são contados como 0
• Strings de texto são contadas como 0

Você o usa assim:

1	= STDEVPA (C4: C12)

Filtrando dados antes de calcular o desvio padrão

No mundo real, você nem sempre terá os dados exatos de que precisa em uma mesa bem organizada. Freqüentemente, você terá uma grande planilha cheia de dados, que precisará filtrar antes de calcular o desvio padrão.

Você pode fazer isso facilmente com as funções de banco de dados do Excel: DSTDEV (para amostras) e DSTDEVP (para populações).

Essas funções permitem criar uma tabela de critérios, na qual você pode definir todos os filtros de que precisa. As funções aplicam esses filtros nos bastidores antes de retornar o desvio padrão. Dessa forma, você não precisa tocar em um Autofiltro ou puxar os dados para uma folha separada - DSTDEV e SDTDEVP podem fazer tudo isso por você.

Saiba mais na página principal das funções Excel DSTDEV e DSTDEVP <>.

Função DESVIO PADRÃO no Planilhas Google

A função DESVIO PADRÃO funciona exatamente da mesma forma no Planilhas Google e no Excel: