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 em 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

Existe também uma maneira pela qual você não precisará 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.

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 de armazém atacadista Foram vendidos 120 comprimidos 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. EM neste exemplo A fórmula de cálculo é a seguinte (150-120)/120=25%. A fórmula é fácil de verificar: 120+25%=150.

Prestar atenção! Se trocarmos os números antigos e novos, teremos uma fórmula para calcular a margem.

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 mais de maneira 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 impossibilidade de calcular o 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.

Dentre os diversos indicadores utilizados nas estatísticas, é necessário destacar o cálculo da variância. Deve-se notar que realizar este cálculo manualmente é uma tarefa bastante tediosa. Felizmente, o Excel possui funções que permitem automatizar o procedimento de cálculo. Vamos descobrir o algoritmo para trabalhar com essas ferramentas.

A dispersão é um indicador de variação, que é o quadrado médio dos desvios da expectativa matemática. Assim, expressa a dispersão dos números em torno do valor médio. O cálculo da variância pode ser realizado tanto para a população geral quanto para a amostra.

Método 1: cálculo baseado na população

Para calcular este indicador em Excel para a população em geral, utilize a função DISP.G. A sintaxe desta expressão é a seguinte:

DISP.G(Número1;Número2;…)

Um total de 1 a 255 argumentos pode ser usado. Os argumentos podem ser os seguintes: valores numéricos, bem como referências às células nas quais estão contidos.

Vamos ver como calcular esse valor para um intervalo com dados numéricos.

Método 2: cálculo por amostra

Ao contrário do cálculo de um valor baseado em uma população, no cálculo de uma amostra o denominador não indica o número total de números, mas um a menos. Isso é feito para fins de correção de erros. O Excel leva essa nuance em consideração em função especial, que se destina a este tipo de cálculo - DISP.V. Sua sintaxe é representada pela seguinte fórmula:

DISP.B(Número1;Número2;…)

O número de argumentos, como na função anterior, também pode variar de 1 a 255.

Como você pode ver, o programa Excel pode facilitar muito o cálculo da variância. Essa estatística pode ser calculada pelo aplicativo, seja a partir da população ou da amostra. Neste caso, todas as ações do usuário se resumem apenas a especificar o intervalo de números a serem processados, e o principal Trabalho Excel faz isso sozinho. Obviamente, isso economizará uma quantidade significativa de tempo do usuário.

Neste artigo falarei sobre como encontrar o desvio padrão. Este material é extremamente importante para uma compreensão completa da matemática, por isso um tutor de matemática deve dedicar uma aula separada ou mesmo várias para estudá-la. Neste artigo você encontrará um link para um tutorial em vídeo detalhado e compreensível que explica o que é o desvio padrão e como encontrá-lo.

Desvio padrão permite avaliar a dispersão dos valores obtidos a partir da medição de um determinado parâmetro. Indicado pelo símbolo (letra grega "sigma").

A fórmula de cálculo é bastante simples. Para encontrar o desvio padrão, você precisa tirar a raiz quadrada da variância. Então agora você tem que perguntar: “O que é variação?”

O que é variação

A definição de variância é assim. A dispersão é a média aritmética dos desvios quadrados dos valores da média.

Para encontrar a variação, execute os seguintes cálculos sequencialmente:

• Determine a média (média aritmética simples de uma série de valores).
• Em seguida, subtraia a média de cada valor e eleve ao quadrado a diferença resultante (você obtém diferença quadrada).
• A próxima etapa é calcular a média aritmética das diferenças quadradas resultantes (você pode descobrir o porquê exatamente dos quadrados abaixo).

Vejamos um exemplo. Digamos que você e seus amigos decidam medir a altura de seus cães (em milímetros). Como resultado das medições, você obteve as seguintes medidas de altura (na cernelha): 600 mm, 470 mm, 170 mm, 430 mm e 300 mm.

Vamos calcular a média, a variância e o desvio padrão.

Primeiro vamos encontrar o valor médio. Como você já sabe, para fazer isso é necessário somar todos os valores medidos e dividir pelo número de medições. Progresso do cálculo:

Média mm.

Então, a média (média aritmética) é 394 mm.

Agora precisamos determinar desvio da altura de cada cão em relação à média:

Finalmente, para calcular a variância, elevamos ao quadrado cada uma das diferenças resultantes e, em seguida, encontramos a média aritmética dos resultados obtidos:

Dispersão mm 2 .

Assim, a dispersão é 21704 mm 2.

Como encontrar o desvio padrão

Então, como podemos agora calcular o desvio padrão, conhecendo a variância? Como nos lembramos, tire a raiz quadrada disso. Ou seja, o desvio padrão é igual a:

Mm (arredondado para o número inteiro mais próximo em mm).

Usando este método, descobrimos que alguns cães (por exemplo, Rottweilers) são cães muito grandes. Mas também existem cães muito pequenos (por exemplo, dachshunds, mas você não deveria dizer isso a eles).

O mais interessante é que o desvio padrão traz consigo informações úteis. Agora podemos mostrar quais dos resultados de medição de altura obtidos estão dentro do intervalo que obtemos se traçarmos o desvio padrão da média (para ambos os lados dela).

Ou seja, utilizando o desvio padrão, obtemos um método “padrão” que nos permite saber qual dos valores é normal (média estatisticamente) e qual é extraordinariamente grande ou, inversamente, pequeno.

O que é desvio padrão

Mas... tudo será um pouco diferente se analisarmos amostra dados. Em nosso exemplo consideramos população em geral. Ou seja, os nossos 5 cães eram os únicos cães do mundo que nos interessavam.

Mas se os dados forem uma amostra (valores selecionados de uma grande população), então os cálculos precisam ser feitos de forma diferente.

Se houver valores, então:

Todos os demais cálculos são realizados de forma semelhante, incluindo a determinação da média.

Por exemplo, se os nossos cinco cães são apenas uma amostra da população de cães (todos os cães do planeta), devemos dividir por 4, não 5, nomeadamente:

Variância da amostra = milímetros 2.

Neste caso, o desvio padrão da amostra é igual a mm (arredondado para o número inteiro mais próximo).

Podemos dizer que fizemos alguma “correção” no caso em que nossos valores são apenas uma pequena amostra.

Observação. Por que diferenças exatamente quadradas?

Mas por que consideramos exatamente as diferenças quadradas ao calcular a variância? Digamos que ao medir algum parâmetro você recebeu o seguinte conjunto de valores: 4; 4; -4; -4. Se simplesmente somarmos os desvios absolutos da média (diferenças)... os valores negativos se anulam com os positivos:

.

Acontece que esta opção é inútil. Então talvez valha a pena tentar os valores absolutos dos desvios (ou seja, os módulos desses valores)?

À primeira vista, dá certo (o valor resultante, aliás, é chamado de desvio médio absoluto), mas não em todos os casos. Vamos tentar outro exemplo. Deixe a medição resultar no seguinte conjunto de valores: 7; 1; -6; -2. Então o desvio absoluto médio é:

Uau! Novamente obtivemos um resultado 4, embora as diferenças tenham um spread muito maior.

Agora vamos ver o que acontece se elevarmos ao quadrado as diferenças (e depois tirarmos a raiz quadrada de sua soma).

Para o primeiro exemplo será:

.

Para o segundo exemplo será:

Agora é uma questão completamente diferente! Quanto maior for a dispersão das diferenças, maior será o desvio padrão... que é o que pretendíamos.

Na verdade, em este método A mesma ideia é usada no cálculo da distância entre pontos, só que aplicada de forma diferente.

E do ponto de vista matemático, usar quadrados e raízes quadradas oferece mais benefícios do que poderíamos obter com valores de desvio absoluto, tornando o desvio padrão aplicável a outros problemas matemáticos.

Sergey Valerievich lhe disse como encontrar o desvio padrão

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 Karl 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)