Desvio padrão do Excel. Como calcular o coeficiente de variação e outras estatísticas no Excel

Uma das principais ferramentas de análise estatística é o cálculo do desvio padrão. Este indicador permite estimar o desvio padrão para uma amostra ou para uma população. Vamos aprender como usar a fórmula do desvio padrão no Excel.

Vamos determinar imediatamente qual é o desvio padrão e como é sua fórmula. Esta quantidade é a raiz quadrada da média aritmética dos quadrados da diferença entre todas as quantidades da série e sua média aritmética. Existe um nome idêntico para este indicador - desvio padrão. Ambos os nomes são completamente equivalentes.

Mas, naturalmente, no Excel o usuário não precisa calcular isso, pois o programa faz tudo por ele. Vamos aprender como calcular o desvio padrão no Excel.

Cálculo no Excel

Você pode calcular o valor especificado no Excel usando duas funções especiais DESV.V(com base na população da amostra) e DESV.G(com base na população em geral). O princípio de seu funcionamento é absolutamente o mesmo, mas podem ser chamados de três maneiras, que discutiremos a seguir.

Método 1: Assistente de Função

Método 2: guia Fórmulas

Método 3: inserir manualmente a fórmula

Também existe uma maneira de evitar chamar a janela de argumentos. Para fazer isso, você deve inserir a fórmula manualmente.

Como você pode ver, o mecanismo de cálculo do desvio padrão no Excel é muito simples. O usuário só precisa inserir números da população ou referências às células que os contêm. Todos os cálculos são realizados pelo próprio programa. É muito mais difícil entender o que é o indicador calculado e como os resultados do cálculo podem ser aplicados na prática. Mas entender isso já diz mais respeito ao campo da estatística do que aprender a trabalhar com software.

Para encontrar o valor médio no Excel (seja numérico, texto, porcentagem ou outro valor), existem muitas funções. E cada um deles tem características e vantagens próprias. Na verdade, nesta tarefa podem ser estabelecidas certas condições.

Por exemplo, os valores médios de uma série de números no Excel são calculados usando funções estatísticas. Você também pode inserir manualmente sua própria fórmula. Vamos considerar várias opções.

Como encontrar a média aritmética dos números?

Para encontrar a média aritmética, você precisa somar todos os números do conjunto e dividir a soma pela quantidade. Por exemplo, as notas de um aluno em ciência da computação: 3, 4, 3, 5, 5. O que está incluído no trimestre: 4. Encontramos a média aritmética usando a fórmula: =(3+4+3+5+5) /5.

Como fazer isso rapidamente usando funções do Excel? Tomemos por exemplo uma série de números aleatórios em uma string:

Ou: crie a célula ativa e simplesmente insira a fórmula manualmente: =MÉDIA(A1:A8).

Agora vamos ver o que mais a função AVERAGE pode fazer.

Vamos encontrar a média aritmética dos dois primeiros e dos três últimos números. Fórmula: =MÉDIA(A1:B1,F1:H1). Resultado:



Média de condição

A condição para encontrar a média aritmética pode ser um critério numérico ou textual. Usaremos a função: =AVERAGEIF().

Encontre a média aritmética dos números maiores ou iguais a 10.

Função: =MÉDIASE(A1:A8,">=10")

O resultado do uso da função AVERAGEIF sob a condição ">=10":

O terceiro argumento – “Intervalo médio” – é omitido. Em primeiro lugar, não é obrigatório. Em segundo lugar, o intervalo analisado pelo programa contém APENAS valores numéricos. As células especificadas no primeiro argumento serão pesquisadas de acordo com a condição especificada no segundo argumento.

Atenção! O critério de pesquisa pode ser especificado na célula. E faça um link para ele na fórmula.

Vamos encontrar o valor médio dos números usando o critério de texto. Por exemplo, a média de vendas do produto “mesas”.

A função ficará assim: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Intervalo – uma coluna com nomes de produtos. O critério de pesquisa é um link para uma célula com a palavra “tabelas” (você pode inserir a palavra “tabelas” em vez do link A7). Intervalo de média – as células das quais os dados serão retirados para calcular o valor médio.

Como resultado do cálculo da função, obtemos o seguinte valor:

Atenção! Para um critério de texto (condição), o intervalo médio deve ser especificado.

Como calcular o preço médio ponderado no Excel?

Como descobrimos o preço médio ponderado?

Fórmula: =SOMAPRODUTO(C2:C12,B2:B12)/SOMA(C2:C12).

Usando a fórmula SUMPRODUCT, descobrimos a receita total após a venda de toda a quantidade de mercadorias. E a função SUM soma a quantidade de mercadorias. Ao dividir a receita total da venda de mercadorias pelo número total de unidades de mercadorias, encontramos o preço médio ponderado. Este indicador leva em consideração o “peso” de cada preço. Sua participação na massa total de valores.

Desvio padrão: fórmula no Excel

Existem desvios padrão para a população geral e para a amostra. No primeiro caso, esta é a raiz da variância geral. No segundo, a partir da variância amostral.

Para calcular este indicador estatístico, é compilada uma fórmula de dispersão. A raiz é extraída dele. Mas no Excel existe uma função pronta para encontrar o desvio padrão.

O desvio padrão está vinculado à escala dos dados de origem. Isto não é suficiente para uma representação figurativa da variação da faixa analisada. Para obter o nível relativo de dispersão dos dados, o coeficiente de variação é calculado:

desvio padrão / média aritmética

A fórmula no Excel é assim:

STDEV (intervalo de valores) / MÉDIA (intervalo de valores).

O coeficiente de variação é calculado em percentagem. Portanto, definimos o formato percentual na célula.

O conceito de desvio percentual refere-se à diferença entre dois valores numéricos em forma de porcentagem. Vamos dar um exemplo específico: digamos que um dia foram vendidos 120 comprimidos em um armazém atacadista e no dia seguinte - 150 peças. A diferença nos volumes de vendas é óbvia; mais 30 tablets foram vendidos no dia seguinte. Ao subtrair o número 120 de 150, obtemos um desvio igual ao número +30. Surge a pergunta: o que é desvio percentual?

Como calcular o desvio percentual no Excel

O desvio percentual é calculado subtraindo o valor antigo do novo valor e depois dividindo o resultado pelo valor antigo. O resultado deste cálculo de fórmula no Excel deve ser exibido em formato de porcentagem de célula. Neste exemplo, a fórmula de cálculo é a seguinte (150-120)/120=25%. A fórmula é fácil de verificar: 120+25%=150.

Observação! Se trocarmos os números antigos pelos novos, obteremos fórmula para calcular marcação.

A figura abaixo mostra um exemplo de como apresentar o cálculo acima como uma fórmula Excel. A fórmula da célula D2 calcula o desvio percentual entre os valores de vendas do ano atual e do ano passado: =(C2-B2)/B2

É importante atentar para a presença de parênteses nesta fórmula. Por padrão, no Excel, a operação de divisão sempre tem precedência sobre a operação de subtração. Portanto, se não colocarmos parênteses, primeiro o valor será dividido e depois outro valor será subtraído dele. Tal cálculo (sem parênteses) será errôneo. Fechar a primeira parte de um cálculo em uma fórmula com parênteses aumenta automaticamente a prioridade da operação de subtração acima da operação de divisão.

Insira a fórmula corretamente entre parênteses na célula D2 e ​​simplesmente copie-a nas células vazias restantes do intervalo D2:D5. Para copiar a fórmula da maneira mais rápida, basta mover o cursor do mouse até o marcador do cursor do teclado (no canto inferior direito) para que o cursor do mouse mude de uma seta para uma cruz preta. Em seguida, basta clicar duas vezes com o botão esquerdo do mouse e o Excel preencherá automaticamente as células vazias com a fórmula e determinará o intervalo D2:D5, que precisa ser preenchido até a célula D5 e nada mais. Este é um hack de vida do Excel muito útil.



Fórmula alternativa para cálculo do desvio percentual no Excel

Numa fórmula alternativa que calcula o desvio relativo dos valores de vendas do ano corrente, divida imediatamente pelos valores de vendas do ano anterior, e só então é subtraído um do resultado: =C2/B2-1.

Como você pode ver na figura, o resultado do cálculo da fórmula alternativa é o mesmo da anterior e, portanto, correto. Mas a fórmula alternativa é mais fácil de escrever, embora possa ser mais difícil para alguns ler para compreender o princípio do seu funcionamento. Ou é mais difícil compreender qual o valor que uma determinada fórmula produz como resultado de um cálculo se não estiver assinada.

A única desvantagem desta fórmula alternativa é a incapacidade de calcular desvio percentual para números negativos no numerador ou no substituto. Mesmo se usarmos a função ABS na fórmula, a fórmula retornará um resultado errado se o número no substituto for negativo.

Como o Excel tem como padrão a prioridade da operação de divisão sobre a operação de subtração, não há necessidade de usar parênteses nesta fórmula.

O desvio padrão é um indicador clássico de variabilidade da estatística descritiva.

Desvio padrão, desvio padrão, desvio padrão, desvio padrão da amostra (eng. desvio padrão, STD, STDev) - um indicador de dispersão muito comum em estatísticas descritivas. Mas porque a análise técnica é semelhante à estatística; este indicador pode (e deve) ser utilizado na análise técnica para detectar o grau de dispersão do preço do instrumento analisado ao longo do tempo. Denotado pelo símbolo grego Sigma "σ".

Obrigado a Carl Gauss e Pearson por nos permitirem usar o desvio padrão.

Usando desvio padrão na análise técnica, transformamos isso "índice de dispersão""V "indicador de volatilidade“, mantendo o significado, mas alterando os termos.

O que é desvio padrão

Mas além dos cálculos auxiliares intermediários, o desvio padrão é bastante aceitável para cálculo independente e aplicações em análise técnica. Como observou um leitor ativo de nossa revista bardana: “ Ainda não entendo porque é que o desvio padrão não está incluído no conjunto de indicadores padrão dos centros de negociação nacionais«.

Realmente, o desvio padrão pode medir a variabilidade de um instrumento de forma clássica e “pura”. Mas, infelizmente, este indicador não é tão comum na análise de títulos.

Aplicando desvio padrão

Calcular manualmente o desvio padrão não é muito interessante, mas útil para experiência. O desvio padrão pode ser expresso fórmula STD=√[(∑(x-x ) 2)/n] , que soa como a raiz da soma das diferenças quadradas entre os elementos da amostra e a média, dividida pelo número de elementos na amostra.

Se o número de elementos na amostra exceder 30, o denominador da fração sob a raiz assume o valor n-1. Caso contrário, n é usado.

Passo a passo cálculo do desvio padrão:

1. calcular a média aritmética da amostra de dados
2. subtraia esta média de cada elemento da amostra
3. elevamos ao quadrado todas as diferenças resultantes
4. somar todos os quadrados resultantes
5. divida o valor resultante pelo número de elementos na amostra (ou por n-1, se n>30)
6. calcular a raiz quadrada do quociente resultante (chamado dispersão)