O Z-Score representa uma métrica estatística que quantifica o número de desvios padrão que um determinado valor se distancia da média de um conjunto de dados. Para determinar o Z-Score, são utilizadas as funções AVERAGE e STDEV.S ou STDEV.P, que calculam respectivamente a média e o desvio padrão dos dados. Com estes resultados, é possível calcular o Z-Score para cada valor individual.
O que é um Z-Score e como funcionam as funções AVERAGE, STDEV.S e STDEV.P?
O Z-Score é uma ferramenta eficaz para comparar dados provenientes de conjuntos distintos. Ele indica o número de desvios padrão que um ponto de dados específico se encontra em relação à média do seu conjunto. A fórmula geral para o cálculo do Z-Score é:
=(PontoDeDado-MÉDIA(ConjuntoDeDados))/DESVPAD(ConjuntoDeDados)
Para ilustrar, considere o cenário onde se deseja comparar o desempenho de dois alunos de álgebra, cada um instruído por um professor diferente. Um aluno obteve 95% na prova final, enquanto o outro alcançou 87%. À primeira vista, a pontuação de 95% parece superior, mas se o exame do segundo professor fosse mais desafiador, como poderíamos realmente comparar os desempenhos? Calculando o Z-Score de cada aluno com base na média e no desvio padrão de cada turma, podemos obter uma comparação mais precisa. O aluno com a pontuação de 87%, por exemplo, pode ter um Z-Score superior, indicando um desempenho melhor em relação aos seus colegas.
A primeira medida estatística necessária é a ‘média’, que é calculada pela função “AVERAGE” do Excel. Esta função soma todos os valores dentro de um intervalo de células e divide a soma pelo número de células que contêm valores numéricos, ignorando células vazias.
A segunda medida essencial é o ‘desvio padrão’, que pode ser calculado no Excel através de duas funções distintas, cada uma com uma abordagem ligeiramente diferente.
Em versões mais antigas do Excel, a função “STDEV” era utilizada para calcular o desvio padrão, tratando os dados como uma ‘amostra’ de uma população. No Excel 2010, esta função foi dividida em duas:
- STDEV.S: Funciona como a função “STDEV” original, calculando o desvio padrão ao tratar os dados como uma amostra. Por exemplo, mosquitos coletados para pesquisa ou carros usados em testes de colisão.
- STDEV.P: Calcula o desvio padrão considerando os dados como a população total. Por exemplo, todos os mosquitos da Terra ou todos os carros produzidos de um modelo específico.
A escolha entre STDEV.S e STDEV.P depende da natureza do conjunto de dados. A diferença entre os resultados geralmente é pequena, mas o resultado de “STDEV.P” será sempre menor do que o de “STDEV.S” para o mesmo conjunto de dados. Utilizar STDEV.S é geralmente uma abordagem mais conservadora, pois assume maior variabilidade nos dados.
Exemplo Prático: Calculando o Z-Score no Excel
Para este exemplo, criaremos duas colunas (“Valores” e “Z-Score”) e três células auxiliares para armazenar os resultados das funções “MÉDIA”, “DESVPAD.S” e “DESVPAD.P”. A coluna “Valores” conterá dez números aleatórios próximos a 500, e a coluna “Z-Score” será utilizada para calcular o Z-Score usando os resultados das células auxiliares.
Inicialmente, calculamos a média dos valores com a função “AVERAGE”. Selecione a célula onde deseja exibir o resultado da média.
Insira a seguinte fórmula na célula e pressione Enter ou acesse a função através do menu “Fórmulas”:
=AVERAGE(E2:E13)
Alternativamente, você pode acessar a função no menu “Fórmulas”, selecionando “Mais Funções”, depois “Estatística” e finalmente “MÉDIA”.
Na janela “Argumentos da função”, selecione as células da coluna “Valores” como entrada para o campo “Número1”. Não há necessidade de preencher o campo “Número2”.
Clique em “OK”.
Agora, calcularemos o desvio padrão usando as funções “STDEV.S” e “STDEV.P”. Primeiro, selecionamos a célula onde o resultado de “STDEV.S” será exibido.
Para calcular o desvio padrão com “STDEV.S”, digite a seguinte fórmula e pressione Enter, ou acesse-a através do menu “Fórmulas”:
=STDEV.S(E3:E12)
Para acessar pelo menu “Fórmulas”, selecione “Mais Funções”, depois “Estatística”, e localize e clique em “STDEV.S”.
Na janela “Argumentos da função”, insira as células da coluna “Valores” como entrada para o campo “Número1”. Não preencha o campo “Número2”.
Pressione “OK”.
Agora, calcule o desvio padrão usando a função “STDEV.P”. Selecione a célula onde o resultado será exibido.
Para calcular o desvio padrão com “STDEV.P”, digite esta fórmula e pressione Enter, ou utilize o menu “Fórmulas”:
=STDEV.P(E3:E12)
Para acessar através do menu “Fórmulas”, selecione “Mais Funções”, depois “Estatística”, e clique em “STDEV.P”.
Na janela “Argumentos da função”, insira as células da coluna “Valores” como entrada para o campo “Número1”. Não preencha o campo “Número2”.
Pressione “OK”.
Agora que temos a média e o desvio padrão, podemos 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 na coluna “Z-Score”. Usaremos o resultado de “STDEV.S” neste exemplo, mas “STDEV.P” também pode ser usado.
Digite a seguinte fórmula e pressione Enter:
=(E3-$G$3)/$H$3
Alternativamente, utilize os passos seguintes para inserir a fórmula:
- Clique na célula F3 e digite = (
- Selecione a célula E3.
- Digite o sinal de menos –
- Selecione a célula G3 e pressione F4 para adicionar “$” (referência absoluta).
- Digite )/
- Selecione a célula H3 (ou I3 se estiver usando “STDEV.P”) e pressione F4 para adicionar “$”.
- Pressione Enter
O Z-Score foi calculado para o primeiro valor. Neste exemplo, o resultado é -0.15945, que significa que o valor está 0.15945 desvios padrão abaixo da média. Para verificar, multiplique o desvio padrão por este resultado e verifique se o resultado é igual à diferença entre o valor e a média.
Para calcular os Z-Scores para os valores restantes, selecione a coluna ‘Z-Score’ a partir da célula com a fórmula.
Pressione Ctrl + D para copiar a fórmula da primeira célula para as outras células selecionadas.
A fórmula foi ‘preenchida’ para todas as células e cada uma referencia as células corretas “AVERAGE” e “STDEV.S” ou “STDEV.P” por causa dos caracteres “$”. Se surgirem erros, verifique se os caracteres “$” foram adicionados.
Calculando o Z-Score sem Células Auxiliares
Células auxiliares podem armazenar resultados temporários, como os resultados das funções “AVERAGE”, “STDEV.S” e “STDEV.P”, mas nem sempre são necessárias. É possível calcular o Z-Score sem estas células utilizando as seguintes fórmulas generalizadas.
Para usar a função “STDEV.S”:
=(Valor-MÉDIA(Valores))/DESVPAD.S(Valores)
Para usar a função “STDEV.P”:
=(Valor-MÉDIA(Valores))/DESVPAD.P(Valores)
Ao inserir os intervalos de células para os “Valores”, use referências absolutas (“$” com F4). Assim, ao ‘preencher’, você não calculará a média ou o desvio padrão de intervalos de células diferentes em cada fórmula.
Se o conjunto de dados for muito extenso, é preferível usar células auxiliares. Isso evita que o Excel calcule o resultado das funções “MÉDIA” e “DESVPAD.S” ou “DESVPAD.P” repetidamente, poupando recursos e acelerando o processamento. Além disso, referências como “$G$3” são mais leves que “AVERAGE($E$3:$E$12)”, otimizando o uso da memória RAM.