Como calcular um Z-Score usando o Microsoft Excel

Um Z-Score é um valor estatístico que informa quantos desvios padrão um valor específico é da média de todo o conjunto de dados. Você pode usar as fórmulas AVERAGE e STDEV.S ou STDEV.P para calcular a média e o desvio padrão de seus dados e, em seguida, usar esses resultados para determinar o Z-Score de cada valor.

O que é um Z-Score e o que as funções AVERAGE, STDEV.S e STDEV.P fazem?

Um Z-Score é uma maneira simples de comparar valores de dois conjuntos de dados diferentes. É definido como o número de desvios padrão da média do ponto de dados. A fórmula geral é assim:

 = (DataPoint-AVERAGE (DataSet)) / DESVPAD (DataSet) 

Este é um exemplo para ajudar a esclarecer. Digamos que você queira comparar os resultados do teste de dois alunos de álgebra ministrados por diferentes professores. Você sabe que o primeiro aluno obteve 95% no exame final em uma aula, e o aluno na outra classe teve 87%.

À primeira vista, o nível de 95% é mais impressionante, mas e se o professor da segunda turma fez um exame mais difícil? Você pode calcular o Z-Score da pontuação de cada aluno com base nas pontuações médias de cada turma e no desvio padrão das pontuações de cada turma. A comparação das notas Z dos dois alunos pode revelar que o aluno com a pontuação de 87% se saiu melhor em comparação com o restante da turma do que o aluno com a pontuação de 98% em comparação com o restante da turma.

O primeiro valor estatístico que você precisa é a "média" e a função "MÉDIA" do Excel calcula esse valor. Ele simplesmente soma todos os valores em um intervalo de células e divide essa soma pelo número de células contendo valores numéricos (ignora células em branco).

O outro valor estatístico que precisamos é o 'desvio padrão' e o Excel tem duas funções diferentes para calcular o desvio padrão de maneiras ligeiramente diferentes.

As versões anteriores do Excel tinham apenas a função "STDEV", que calcula o desvio padrão enquanto trata os dados como uma "amostra" de uma população. O Excel 2010 dividiu isso em duas funções que calculam o desvio padrão:

• STDEV.S: esta função é idêntica à função anterior "STDEV". Calcula o desvio padrão enquanto trata os dados como uma "amostra" de uma população. Uma amostra de uma população pode ser algo como os mosquitos particulares coletados para um projeto de pesquisa ou carros que foram separados e usados ​​para testes de segurança de acidentes.
• STDEV.P: Esta função calcula o desvio padrão ao tratar o dados como toda a população. Uma população inteira seria algo como todos os mosquitos na Terra ou todos os carros em uma produção de um modelo específico.

O que você escolhe é baseado no seu conjunto de dados. A diferença geralmente será pequena, mas o resultado da função “STDEV.P” sempre será menor que o resultado da função “STDEV.S” para o mesmo conjunto de dados. É uma abordagem mais conservadora supor que há mais variabilidade nos dados.

Vamos ver um exemplo

Para nosso exemplo, temos duas colunas ("Valores" e "Z". -Score ”) e três células“ auxiliares ”para armazenar os resultados das funções“ AVERAGE ”,“ STDEV.S ”e“ STDEV.P ”. A coluna "Valores" contém dez números aleatórios centrados em torno de 500, e a coluna "Z-Score" é onde calcularemos o Z-Score usando os resultados armazenados nas células 'auxiliares'.

Primeiro, calcularemos a média dos valores usando a função "MÉDIA". Selecione a célula onde você armazenará o resultado da função “AVERAGE”.

Digite a seguinte fórmula e pressione enter - ou - use o menu “Fórmulas”. / p>

 = MÉDIA (E2: E13) 

Para acessar a função através do menu "Fórmulas", selecione a lista suspensa "Mais funções", selecione a opção "Estatística" e, em seguida, clique em "AVERAGE".

Na janela Argumentos da Função, selecione todas as células na coluna "Valores" como a entrada para o campo "Número1". . Você não precisa se preocupar com o campo "Número2".

Agora pressione "OK".

Em seguida, precisamos calcular o desvio padrão dos valores usando a função "STDEV.S" ou "STDEV.P". Neste exemplo, mostraremos a você como calcular os dois valores, começando com "STDEV.S." Selecione a célula onde o resultado será armazenado.

Para calcular a desvio padrão usando a função "STDEV.S", digite esta fórmula e pressione Enter (ou acesse-a através do menu "Fórmulas").

 = STDEV.S (E3: E12) 

Para acessar a função através do menu “Fórmulas”, selecione o menu suspenso “Mais funções”, selecione a opção “Estatística”, role um pouco para baixo e clique no comando “STDEV.S”.

Na janela Argumentos da Função, selecione todas as células na coluna “Valores” como a entrada para o campo “Número1”. Você não precisa se preocupar com o campo "Número2" aqui.

Agora pressione "OK".

Em seguida, calcularemos o desvio padrão usando a função "STDEV.P". Selecione a célula onde o resultado será armazenado.

Para calcular o desvio padrão usando a função “STDEV.P”, digite esta fórmula e pressione Enter (ou acesse através do menu “Fórmulas”.

= STDEV.P (E3: E12)

Para acessar a função através do menu “Fórmulas”, selecione a opção “Mais funções”. para baixo, selecione a opção "Estatística", role um pouco para baixo e clique na fórmula "STDEV.P".

Na janela Argumentos da Função, selecione todos os células na coluna “Valores” como entrada para o campo “Número1”. Novamente, você não precisa se preocupar com o campo "Número2".

Agora pressione "OK".

Agora que calculamos a média e o desvio padrão de nossos dados, temos tudo o que precisamos para calcular o Z-Score. Podemos usar uma fórmula simples que referencia as células contendo os resultados das funções “AVERAGE” e “STDEV.S” ou “STDEV.P”.

Selecione a primeira célula no “Z-Score” coluna. Usaremos o resultado da função "STDEV.S" para este exemplo, mas você também pode usar o resultado de "STDEV.P".

Digite o seguinte formula e aperta Enter:

 = (E3- $ G $ 3) / $ H $ 3 

Alternativamente, você pode usar os seguintes passos para entrar na fórmula ao invés de digitar:

• Clique na célula F3 e digite = (
• Selecione a célula E3. (Você pode pressionar a tecla de seta para a esquerda uma vez ou usar o mouse)
• Digite o sinal de menos -
• Selecione a célula G3 e pressione F4 para adicionar os caracteres “$” para fazer uma referência 'absoluta' à célula (ela passará por “G3” > “$ G $ 3 ″ & Gt; "G $ 3" ​​> "$ G3" > "G3" se você continuar pressionando F4)
• Tipo) /
• Selecione a célula H3 (ou I3 se você estiver usando “STDEV.P”) e pressione F4 para adicionar os dois caracteres “$”.
• Pressione Enter

IMAGEM_15

A pontuação foi calculada para o primeiro valor. É 0.15945 desvios padrão abaixo da média. Para verificar os resultados, você pode multiplicar o desvio padrão por este resultado (6.271629 * -0.15945) e verificar se o resultado é igual à diferença entre o valor e a média (499-500). Ambos os resultados são iguais, então o valor faz sentido.

Vamos calcular as Z-Scores do resto dos valores. Realce toda a coluna 'Z-Score' começando com a célula que contém a fórmula.

Pressione Ctrl + D, que copia a fórmula na célula superior para baixo através de todas as outras células selecionadas.

Agora, a fórmula foi "preenchida" para todas as células, e cada uma sempre fará referência à "AVERAGE" e "STDEV.S" corretas. ou células "STDEV.P" por causa dos caracteres "$". Se você receber erros, volte e verifique se os caracteres "$" estão incluídos na fórmula digitada.

Calculando o Z-Score sem usar células 'Auxiliares'

Células auxiliares armazena um resultado, como aqueles que armazenam os resultados das funções “AVERAGE”, “STDEV.S” e “STDEV.P”. Eles podem ser úteis, mas nem sempre são necessários. Você pode pulá-los ao calcular um Z-Score usando as seguintes fórmulas generalizadas.

Aqui está um usando a função "STDEV.S":

 = (Valor-AVERAGE (Values)) / STDEV.S (Values) 

E um usando a função “STEV.P”:

 = (Valor-MÉDIA (Valores)) / STDEV.P (Valores) 

Ao inserir as faixas de células para os “Valores ”Nas funções, certifique-se de adicionar referências absolutas (“ $ ”usando F4) para que, quando você 'preencha', não esteja calculando a média ou o desvio padrão de um intervalo diferente de células em cada fórmula.

Se você tiver um grande conjunto de dados, pode ser mais eficiente usar células auxiliares, pois ele não calcula o resultado das funções “AVERAGE” e “STDEV.S” ou “STDEV.P” todas as vezes, economizando recursos do processador e acelerando o tempo que leva para calcular os resultados.

Além disso, “$ G $ 3” leva menos bytes para armazenar e menos RAM para carregar do que “AVERAGE ($ E $ 3: $ E $ 12 ). Isso é importante porque a versão padrão de 32 bits do Excel é limitada a 2 GB de RAM (a versão de 64 bits não tem limitações quanto à quantidade de RAM que pode ser usada).

Via: How to Geek