VARIANCE - Excel e Planilhas Google

Este tutorial demonstra como usar o Função Excel VARIANCE no Excel para estimar a variação com base em uma determinada amostra.

Visão geral da função VARIANCE

A função VARIANCE Calcula a variação da estimativa com base em uma determinada amostra.

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

(Observe como as entradas da fórmula aparecem)

Sintaxe da função VARIANCE e entradas:

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

números- Valores para obter Variância

Como calcular a variação no Excel

A variância informa como os valores em um conjunto de dados estão espalhados em relação à média. Matematicamente falando, a variância é a média da diferença quadrática de cada pontuação da média (mas vamos chegar a isso em breve).

O Excel oferece várias funções para calcular a variação - VAR.S, VAR.P, VARA, VARPA e duas funções mais antigas, VAR e VARP.

Antes de nos aprofundarmos nessas funções e aprender como usá-las, vamos falar sobre a variação e como ela é calculada.

Qual é a variação?

Ao analisar os dados, um primeiro passo comum é calcular a média. Obviamente, esta é uma estatística útil para calcular, mas não fornece uma visão completa do que está acontecendo com seus dados.

Pegue o seguinte conjunto de dados, que pode ser um grupo de resultados de teste com pontuação de 100:

1	48,49,50,51,52

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

Em seguida, pegue o seguinte conjunto de resultados de teste:

1	10,25,50,75,90

A média deste intervalo é tb 50 - mas obviamente temos dois intervalos de dados muito diferentes aqui.

Por si só, a média não pode dizer nada sobre como as pontuações estão espalhadas. Não diz se os valores estão todos agrupados como nos primeiros exemplos ou separados como no segundo. A variação pode ajudá-lo a aprender isso.

A variância também é usada como um ponto base para uma variedade de procedimentos estatísticos mais complexos.

Como a variação é calculada

Vamos trabalhar com um exemplo básico e calcular a variância manualmente. Dessa forma, você saberá o que está acontecendo nos bastidores quando realmente começar a colocar as funções de variação do Excel em ação.

Digamos que temos um conjunto de dados representando três cartas de jogar, um 4, um 6 e 8.

Para calcular a variação, você trabalha com este processo:

1) Calcule a média

Primeiro, calculamos a média. Sabemos que nosso intervalo de dados é 4, 6, 8, então a média será:

1	(6 + 4 + 8) / 3 = 6

Eu confirmei isso abaixo com a função AVERAGE do Excel <>:

1	= MÉDIA (C4: C6)

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

Em seguida, subtraímos a média de cada um dos nossos valores.

Eu fiz isso com a seguinte fórmula:

1	= C4- $ H $ 4

A média é armazenada em H4, então eu apenas subtraio isso de cada valor na tabela. Os cifrões aqui apenas “bloqueiam” aquela referência de célula a H4, de forma que quando eu copio na coluna, ela permanece a mesma.

Os resultados:

Nós temos:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Precisamos obter a média dessas diferenças da média, mas a média desses três valores é zero! Portanto, precisamos enfatizar as diferenças, o que fazemos ao elevá-las ao quadrado.

3) Quadrar as diferenças

Vamos adicionar uma nova coluna e elevar ao quadrado os números na coluna D:

1

= D4 * D4

Ok, assim é melhor. Agora que a média das diferenças não chega a zero, podemos calcular a variância.

4) Calcule a média das diferenças quadradas

Aqui encontramos uma bifurcação na estrada. Existem duas maneiras de calcular a variação e a que você usa depende do tipo de dados que você possui.

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

Os dados populacionais significam que você tem a totalidade dos dados de que precisa, por exemplo, se você deseja a idade média dos professores em uma escola específica e tem os dados de idade para cada professor nessa escola, você tem os dados da população.

Dados de amostra significam que você não tem todos os seus dados, apenas uma amostra retirada de uma população maior. Portanto, se você quiser a idade média dos professores em todo o país e só tiver dados sobre os professores de uma escola, terá dados de amostra.

Em nosso exemplo, temos dados populacionais. Estamos interessados ​​apenas em nossos três cartões - essa é a população, e não retiramos uma amostra deles. Portanto, podemos apenas tirar a média das diferenças quadradas da maneira normal:

1	= MÉDIA (E4: E8)

Portanto, a variância de nossa população é 2.666.

Se este era dados de amostra (talvez tivéssemos retirado esses três cartões de um conjunto maior), calcularíamos a média da seguinte forma:

1	Variância da amostra = (4 + 0 + 4) / (3 - 1)

Ou:

1	Variância da amostra = 8/2 = 4

Por que dividir por n-1 com dados de amostra, em vez de apenas n?

A resposta curta a esta pergunta é “Porque dá a resposta certa”. Mas imagino que você queira um pouco mais do que isso! Este é um tópico complexo, então darei apenas uma breve visão geral aqui.

Pense assim: se você tomar uma amostra de dados de uma população, esses valores tenderão a estar mais próximos da média do amostra do que eles são para a média do população.

Isso significa que se você apenas dividir por n, estará subestimando um pouco a variância da população. Dividir por n-1 corrige um pouco isso.

Com nosso conjunto de três cartas, estamos em um bom lugar para testar essa teoria. Como existem apenas três cartas, existe um pequeno número de amostras que podemos tirar.

Vamos pegar amostras de dois cartões. Vamos pegar um cartão, colocá-lo de volta, embaralhar e escolher outro cartão. Isso significa que podemos escolher nove combinações de duas cartas.

Com apenas nove amostras possíveis, podemos calcular todas as variações amostrais possíveis usando os dois métodos (dividir por n e dividir por n-1), tirar a média delas e ver qual delas nos dá a resposta certa.

Na tabela abaixo, eu coloquei tudo para fora. Cada linha da tabela é uma amostra diferente e as colunas B e C mostram os dois cartões que foram escolhidos em cada amostra. Em seguida, adicionei mais duas colunas: uma onde calculei a variância dessa amostra de dois cartões dividindo por n, e outra onde dividi por n - 1.

Dê uma olhada:

À direita da tabela, mostrei as médias das colunas D e E.

A média da coluna D, ao dividir por n, nos dá uma variância de 1,333.

A média da coluna E, ao dividir por n-1, nos dá uma variância de 2,666.

Já sabemos pelo nosso exemplo anterior que a variância da população é 2.666. Portanto, dividir por n-1 ao usar dados de amostra nos dá estimativas mais precisas.

As funções do Excel para calcular a variação

Agora que você viu um exemplo de como a variação é calculada, vamos passar para as funções do Excel.

Você tem várias opções aqui:

• P retorna a variância para dados populacionais (usando o método de divisão por n)
• S retorna a variância para dados de amostra (divide por n-1)
• VAR é uma função mais antiga que funciona exatamente da mesma maneira que VAR.S
• VARA é o mesmo que VAR.S, exceto que inclui células de texto e valores booleanos
• VARPA é o mesmo que VAR.P, exceto que inclui células de texto e valores booleanos

Vamos examiná-los um por um.

A função VAR.P do Excel

VAR.P calcula a variância para dados populacionais (usando o método de divisão por n). Use-o assim:

1	= VAR.P (C4: C6)

Você define apenas um argumento em VAR.P: o intervalo de dados para o qual deseja calcular a variação. Em nosso caso aqui, são os valores do cartão em C4: C6.

Como você pode ver acima, VAR.P retorna 2.666 para nosso conjunto de três cartas. Este é o mesmo valor que calculamos manualmente anteriormente.

Observe que VAR.P ignora completamente as células que contêm texto ou valores booleanos (TRUE / FALSE). Se você precisar incluí-los, use VARPA.

A função VAR.S do Excel

VAR.S calcula a variância para dados de amostra (dividindo por n-1). Você o usa assim:

1	= VAR.S (C4: C6)

Novamente, há apenas um argumento - seu intervalo de dados.

Nesse caso, VAR.S retorna 4. Obtivemos o mesmo valor na etapa 4 quando fizemos o cálculo manual acima.

VAR.S ignora completamente as células que contêm texto ou valores booleanos (TRUE / FALSE). Se você precisar incluí-los, use VARA.

A função VAR do Excel

VAR é completamente equivalente a VAR.S: ele calcula as variâncias dos dados de amostra (usando o método n-1). Veja como usá-lo:

1	= VAR (C4: C6)

VAR é uma “função de compatibilidade”. Isso significa que a Microsoft está removendo essa função do Excel. No momento, ele ainda está disponível para uso, mas você deve usar o VAR.S, para que suas planilhas permaneçam compatíveis com futuras versões do Excel.

A função VARA do Excel

VARA também retorna a variação dos dados de amostra, mas tem algumas diferenças importantes para VAR e VAR.S. Ou seja, inclui valores booleanos e de texto em seu cálculo:

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

Veja como você o usa:

1	= VARA (C4: C11)

Adicionamos mais cinco linhas à tabela: J, Q, K, TRUE e FALSE. A coluna D mostra como VARA interpreta esses valores.

Como agora temos um novo lote de valores baixos em nossa tabela, a variação aumentou para 10,268.

A função VARPA do Excel

VARPA calcula a variação para dados populacionais. É semelhante a VAR.P, exceto que também inclui valores booleanos e strings de texto no cálculo:

• 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	= VARPA (C4: C12)

Adicionamos mais cinco linhas à tabela: J, Q, K, TRUE e FALSE. A coluna D mostra como VARPA interpreta esses valores.

Como resultado da adição deste grupo de valores inferiores aos dados, a variância aumentou para 8,984.

Função VARIANCE no Planilhas Google

A função CORREL funciona exatamente da mesma forma no Planilhas Google e no Excel: