A função STDEV.B retorna o valor do desvio padrão calculado para um intervalo especificado de valores numéricos.

A função STDEVG é utilizada para determinar o desvio padrão de uma população de valores numéricos e retorna o desvio padrão, visto que os valores passados ​​são toda a população, não uma amostra.

A função STDEV retorna o desvio padrão para algum intervalo de números que são uma amostra, não a população inteira.

STDLONGPA retorna o desvio padrão para toda a população passada como seus argumentos.

Exemplos de uso de STDEV.V, STDEV.G, STDEV e STDEVPA

Exemplo 1. A empresa tem dois gerentes de aquisição de clientes. Os dados sobre o número de clientes atendidos por dia por cada gestor são registrados em planilha Excel. Determine qual dos dois funcionários trabalha com mais eficiência.

Tabela de dados inicial:

Primeiro, vamos calcular o número médio de clientes com os quais os gerentes trabalhavam diariamente:

MÉDIA(B2:B11)

Esta função calcula a média aritmética para o intervalo B2:B11 contendo o número de clientes recebidos diariamente pelo primeiro gerente. Da mesma forma, calculamos o número médio de clientes por dia para o segundo gerente. Nós temos:

Com base nos valores obtidos, parece que ambos os gestores trabalham de forma aproximadamente igual. No entanto, uma forte dispersão nos valores do número de clientes para o primeiro gerente é visualmente visível. Vamos calcular o desvio padrão usando a fórmula:

STDV B(B2:B11)

B2:B11 - o intervalo dos valores estudados. Da mesma forma, determinamos o desvio padrão para o segundo gerente e obtemos os seguintes resultados:

Como você pode ver, os indicadores de desempenho do primeiro gerente são caracterizados por alta variabilidade (dispersão) de valores e, portanto, a média aritmética não reflete a imagem real da eficiência do trabalho. O desvio 1.2 indica um trabalho mais estável e, portanto, eficiente do segundo gestor.



Um exemplo de uso da função STDEV no Excel

Exemplo 2. Em dois grupos diferentes de estudantes universitários, foi realizado um exame na mesma disciplina. Avalie o desempenho dos alunos.

Tabela de dados inicial:

Vamos determinar o desvio padrão dos valores para o primeiro grupo usando a fórmula:

STDEV(A2:A11)

Vamos fazer um cálculo semelhante para o segundo grupo. Como resultado, obtemos:

Os valores obtidos indicam que os alunos da segunda turma se prepararam muito melhor para o exame, já que a dispersão dos valores de avaliação é relativamente pequena. Observe que a função STDEV converte o valor do texto "pass" para o valor numérico 0 (zero) e o leva em consideração no cálculo.

Exemplo de função STDEV.G no Excel

Exemplo 3. Determinar a eficácia da preparação dos alunos para o exame de todas as turmas da universidade.

Nota: ao contrário do exemplo anterior, não será analisada uma amostra (vários grupos), mas sim todo o número de alunos - a população geral. Os alunos reprovados no exame não são contados.

Preencha a tabela de dados:

Para avaliar a eficácia, vamos operar com dois indicadores: a pontuação média e a dispersão dos valores. Para determinar a média aritmética, usamos a função:

MÉDIA(B2:B21)

Para determinar o desvio, introduzimos a fórmula:

STDV H(B2:B21)

Como resultado, obtemos:

Os dados obtidos indicam um desempenho um pouco abaixo da média (<4), величина разброса характеризует довольно большое количество студентов, получивших 5 и 3 соответственно (учитывая, что анализировались только данные из диапазона от 3 до 5).

Exemplo de função STDEVPA no Excel

Exemplo 4. Analise o desempenho dos alunos com base nos resultados de aprovação no exame, levando em consideração os alunos que não passaram neste exame.

Ficha de dados:

Neste exemplo, também estamos analisando a população, mas alguns dos campos de dados contêm valores de texto. Para determinar o desvio padrão, usamos a função:

STDEVPA(B2:B21)

Como resultado, obtemos:

Uma alta dispersão de valores na sequência indica um grande número de alunos que não passaram no exame.

Recursos do uso de STDEV.V, STDEV.G, STDEV e STDEVPA

As funções STDEV e STDEVPA têm uma notação de sintaxe idêntica como:

FUNÇÃO(valor1; [valor2];…)

Descrição:

• FUNÇÃO - uma das duas funções discutidas acima;
• valor1 é um argumento obrigatório que caracteriza um dos valores da amostra (ou da população em geral);
• [valor2] é um argumento opcional que caracteriza o segundo valor do intervalo estudado.

Notas:

1. Nomes, valores numéricos, arrays, referências a intervalos de dados numéricos, valores lógicos e referências a eles podem ser passados ​​como argumentos para funções.
2. Ambas as funções ignoram valores nulos e dados de texto contidos no intervalo de dados passado.
3. As funções retornam o código de erro #VALUE! se valores de erro ou dados de texto foram passados ​​como argumentos que não podem ser convertidos em valores numéricos.

As funções STDEV.V e STDEV.G têm a seguinte notação de sintaxe:

FUNÇÃO(número1,[número2],…)

Descrição:

• FUNCTION – qualquer uma das funções STDEV.V ou STDEV.G;
• número1 - um argumento obrigatório caracterizando o valor numérico retirado da amostra ou de toda a população geral;
• number2 é um argumento opcional que caracteriza o segundo valor numérico do intervalo estudado.

Nota: ambas as funções não incluem números representados como dados de texto, nem os valores lógicos VERDADEIRO e FALSO no processo de cálculo.

Notas:

1. O desvio padrão é muito utilizado em cálculos estatísticos quando encontrar a média de um intervalo de valores não dá uma ideia correta da distribuição dos dados. Demonstra o princípio de distribuição de valores em relação ao valor médio em uma determinada amostra ou em toda a sequência. O Exemplo 1 irá considerar visualmente a aplicação prática deste parâmetro estatístico.
2. As funções STDEV e STDEV.V devem ser usadas para analisar apenas uma parte da população geral e calcular de acordo com a primeira fórmula, enquanto STDEV.G e STDEV.V devem receber dados de toda a população como entrada e calcular usando a segunda fórmula .
3. O Excel contém as funções internas STDEV e STDEV, mantidas para compatibilidade com versões mais antigas do Microsoft Office. Eles podem não ser incluídos em versões posteriores do programa, portanto, seu uso não é recomendado.
4. Duas fórmulas comuns são usadas para encontrar o desvio padrão: S=√((∑_(i=1)^n▒(x_i-x_average)^2)/(n-1)) e S=√((∑_( i= 1)^n▒(x_i-x_av)^2)/n), onde:

• S é o valor desejado do desvio padrão;
• n é o intervalo de valores considerado (amostra);
• x_i é um valor único da amostra;
• x_av é a média aritmética para o intervalo em consideração.

A estatística usa um grande número de indicadores, e um deles é o cálculo de variância no Excel. Se você fizer isso manualmente, levará muito tempo, você pode cometer muitos erros. Hoje veremos como decompor fórmulas matemáticas em funções simples. Vejamos alguns dos métodos de cálculo mais simples, rápidos e convenientes que permitirão que você faça tudo em questão de minutos.

Calculando a variação

A dispersão de uma variável aleatória é a expectativa matemática do desvio quadrado de uma variável aleatória de sua expectativa matemática.

Calculamos pela população geral

Para calcular o tapete. expectativa no programa, será utilizada a função VARI.G, e sua sintaxe é a seguinte "= VARI.G (Número1; Número2; ...)".

É possível aplicar no máximo 255 argumentos, não mais. Os argumentos podem ser números simples ou referências às células nas quais são especificados. Vejamos como calcular a variação no Microsoft Excel:

1. O primeiro passo é selecionar a célula onde será exibido o resultado dos cálculos e, em seguida, clicar no botão "Inserir função".

2. O shell de gerenciamento de recursos será aberto. Lá você precisa procurar a função "DISP.G", que pode estar na categoria "Estatístico" ou "Lista alfabética completa". Quando ele for encontrado, selecione-o e clique em OK.

3. A janela de argumentos da função será aberta. Nela, você precisa selecionar a linha "Número 1" e na planilha selecionar um intervalo de células com uma linha numérica.

4. Depois disso, na célula onde a função foi inserida, serão exibidos os resultados dos cálculos.

É assim que você pode encontrar facilmente a variação no Excel.

Fazendo um cálculo de amostra

Nesse caso, a variância da amostra no Excel é calculada com o denominador indicando não o número total de números, mas um a menos. Isso é feito para um erro menor usando a função especial VAR.V, cuja sintaxe é =VAR.V(Número1;Número2;…). Algoritmo de ação:

• Como no método anterior, você precisa selecionar uma célula para o resultado.
• No assistente de funções, você deve encontrar "VAR.V" na categoria "Lista alfabética completa" ou "Estatística".

• Em seguida, uma janela aparecerá, e você deve proceder da mesma forma que no método anterior.

Vídeo: Calcular a variação no Excel

Conclusão

A variação no Excel é calculada de maneira muito simples, muito mais rápida e conveniente do que fazê-la manualmente, porque a função de expectativa matemática é bastante complicada e pode levar muito tempo e esforço para calculá-la.

A função de desvio padrão já é da categoria de matemática superior relacionada à estatística. No Excel, existem várias opções para usar a Função de Desvio Padrão:

• função STDEV.
• função STDEV.
• função STDEV

Precisaremos dessas funções nas estatísticas de vendas para identificar a estabilidade das vendas (análise XYZ). Esses dados podem ser usados ​​tanto para precificação quanto para a formação (correção) da matriz de sortimento e para outras análises úteis de vendas, sobre as quais com certeza falarei em artigos futuros.

Prefácio

Vejamos primeiro as fórmulas em linguagem matemática, e depois (abaixo no texto) analisaremos detalhadamente a fórmula no Excel e como o resultado resultante é aplicado na análise das estatísticas de vendas.

Assim, o Desvio Padrão é uma estimativa do desvio padrão de uma variável aleatória x em relação à sua expectativa matemática com base em uma estimativa imparcial de sua variância)))) Não tenha medo de palavras incompreensíveis, seja paciente e você entenderá tudo!

Descrição da fórmula: O desvio padrão é medido em unidades da própria variável aleatória e é utilizado no cálculo do erro padrão da média aritmética, na construção de intervalos de confiança, no teste estatístico de hipóteses, na medição de uma relação linear entre variáveis ​​aleatórias. Definido como a raiz quadrada da variância de uma variável aleatória

Agora o desvio padrão é uma estimativa do desvio padrão de uma variável aleatória x com relação à sua expectativa matemática com base em uma estimativa imparcial de sua variância:

Dispersão;

- eu-ésimo elemento de amostra;

Tamanho da amostra;

Média aritmética da amostra:

Deve-se notar que ambas as estimativas são tendenciosas. No caso geral, é impossível construir uma estimativa imparcial. No entanto, uma estimativa baseada em uma estimativa de variância imparcial é consistente.

regra de três sigma() - quase todos os valores de uma variável aleatória normalmente distribuída estão no intervalo. Mais estritamente, com aproximadamente 0,9973 de probabilidade, o valor de uma variável aleatória normalmente distribuída está no intervalo especificado (desde que o valor seja verdadeiro e não obtido como resultado do processamento da amostra). Usaremos um intervalo arredondado de 0,1

Se o valor verdadeiro for desconhecido, você deve usar not, mas s. Assim, a regra de três sigma é transformada na regra de três s. É essa regra que nos ajudará a determinar a estabilidade das vendas, mas falaremos mais sobre isso depois...

Agora Função de Desvio Padrão no Excel

Espero não ter sobrecarregado você com matemática? Talvez alguém precise dessa informação para um resumo ou algum outro propósito. Agora vamos mastigar como essas fórmulas funcionam no Excel...

Para determinar a estabilidade das vendas, não precisamos nos aprofundar em todas as opções de funções de desvio padrão. Usaremos apenas um:

função STDEV

STDEV(número 1;número 2;... )

Número1, Número2,..- de 1 a 30 argumentos numéricos correspondentes à população geral.

Agora vejamos um exemplo:

Vamos criar um livro e uma planilha improvisada. Você pode baixar este exemplo em Excel no final do artigo.

Continua!!!

Olá de novo. Nós iremos!? Ganhei um minuto grátis. Vamos continuar?

E assim a estabilidade das vendas com a ajuda Funções STDEV

Para maior clareza, vamos pegar alguns produtos improvisados:

Na análise, seja uma previsão, uma pesquisa ou qualquer outra coisa relacionada à estatística, é sempre necessário tirar três períodos. Pode ser uma semana, mês, trimestre ou ano. É possível e até melhor tomar tantos períodos quanto possível, mas não menos de três.

Mostrei especificamente vendas exageradas, onde você pode ver a olho nu o que está sendo vendido de forma consistente e o que não está. Isso tornará mais fácil entender como as fórmulas funcionam.

E assim temos as vendas, agora precisamos calcular os valores médios de vendas por período.

Fórmula de valor médio AVERAGE(dados do período) no meu caso, a fórmula se parece com isso =AVERAGE(C6:E6)

Esticamos a fórmula para todos os produtos. Isso pode ser feito segurando o canto direito da célula selecionada e arrastando-a para o final da lista. Ou coloque o cursor na coluna com o produto e pressione as seguintes combinações de teclas:

Ctrl + Down move o cursor para o final da lista.

Ctrl + Direita, o cursor se moverá para o lado direito da tabela. Mais uma vez à direita e chegaremos à coluna com a fórmula.

Agora nós prendemos

Ctrl + Shift e pressione para cima. Então selecionamos a área de alongamento da fórmula.

E a combinação de teclas Ctrl + D irá esticar a função onde precisamos dela.

Lembre-se dessas combinações, elas realmente aumentam sua velocidade no Excel, principalmente quando você trabalha com grandes arrays.

A próxima etapa, a própria função de desvio padrão, como eu disse, vamos usar apenas um STDEV

Prescrevemos a função e nos valores da função colocamos os valores de vendas de cada período. Se você tiver vendas na tabela uma após a outra, você pode usar o intervalo, como na minha fórmula =SDV(C6:E6) ou listar as células necessárias com um ponto e vírgula =SDV(C6;D6;E6)

Aqui estão todos os cálculos e prontos. Mas como você sabe o que vende consistentemente e o que não vende? Vamos apenas colocar a convenção XYZ onde,

X é estável

Y - com pequenos desvios

Z - não estável

Para fazer isso, usamos intervalos de erro. se ocorrerem flutuações dentro de 10%, assumiremos que as vendas são estáveis.

Se estiver entre 10 e 25 por cento, será Y.

E se os valores de variação excederem 25% - isso não é estabilidade.

Para definir corretamente as letras para cada produto, usaremos a fórmula IF com mais detalhes. Na minha tabela, esta função ficará assim:

SE(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Assim, esticamos todas as fórmulas para todos os nomes.

Vou tentar responder imediatamente à pergunta: Por que os intervalos de 10% e 25%?

Na verdade, os intervalos podem ser diferentes, tudo depende da tarefa específica. Mostrei especificamente para vocês valores de vendas exagerados, onde a diferença é visível a “olho”. É óbvio que o produto 1 não é vendido de forma consistente, mas a dinâmica mostra um aumento nas vendas. Deixe este item em paz...

Mas produto 2, já há desestabilização no rosto. E nossos cálculos mostram Z, que nos informa sobre a instabilidade das vendas. O item 3 e o item 5 apresentam desempenho estável, observe que a variação está dentro de 10%.

Aqueles. O item 5 com pontuação de 45, 46 e 45 apresenta variação de 1%, que é uma série numérica estável.

Mas o Produto 2 com pontuações de 10, 50 e 5 mostra uma variação de 93%, que NÃO é uma série numérica estável.

Depois de todos os cálculos, você pode colocar um filtro e filtrar a estabilidade, portanto, se sua tabela for composta por vários milhares de itens, você poderá selecionar facilmente quais não são estáveis ​​nas vendas ou, ao contrário, quais são estáveis.

"Y" não funcionou na minha tabela, acho que para maior clareza da série numérica, ele precisa ser adicionado. Eu vou sortear Bens 6...

Você vê, a série numérica 40, 50 e 30 mostra 20% de variação. Parece que não há grande erro, mas ainda assim o spread é significativo ...

E assim resumindo:

10,50,5 - Z não é estável. Variação acima de 25%

40,50,30 - Você pode prestar atenção neste produto e melhorar suas vendas. Variação inferior a 25%, mas superior a 10%

45,46,45 - X é estabilidade, nada precisa ser feito com este produto ainda. Variação inferior a 10%

Isso é tudo! Espero ter explicado tudo claramente, se não, pergunte o que não está claro. E serei grato a você por cada comentário, seja elogio ou crítica. Então eu vou saber que você está lendo eu e você, o que é muito IMPORTANTE, interessante. E, consequentemente, novas lições aparecerão.

A intervenção da gestão é necessária para identificar as causas dos desvios.

Para construir um gráfico de controle, utilizo os dados originais, a média (μ) e o desvio padrão (σ). No Excel: μ = MÉDIA($F$3:$F$15), σ = STDEV($F$3:$F$15)

O próprio gráfico de controle inclui: dados brutos, média (μ), limite de controle inferior (μ - 2σ) e limite de controle superior (μ + 2σ):

Baixar nota em formato , exemplos em formato

Olhando para este mapa, notei que os dados brutos mostram uma tendência linear muito distinta em direção a uma participação decrescente de despesas gerais:

Para adicionar uma linha de tendência, selecione a linha de dados no gráfico (no nosso exemplo, pontos verdes), clique com o botão direito do mouse e selecione a opção "Adicionar linha de tendência". Na janela Formatar Trendline que se abre, experimente as opções. Eu estabeleci uma tendência linear.

Se os dados iniciais não estiverem dispersos de acordo com o valor médio, então não é correto descrevê-los pelos parâmetros μ e σ. Para descrição, em vez do valor médio, uma linha de tendência linear e bordas de controle equidistantes desta linha de tendência são mais adequadas.

O Excel permite que você crie uma linha de tendência usando a função PREVISÃO. Precisaremos de uma linha adicional A3: A15 para valores X conhecidos eram uma série contínua (números de trimestres não formam uma série tão contínua). Em vez do valor médio na coluna H, introduzimos a função PREVISÃO:

O desvio padrão σ (função STDEV no Excel) é calculado pela fórmula:

Infelizmente, não encontrei uma função no Excel para tal definição do desvio padrão (em relação à tendência). O problema pode ser resolvido usando uma fórmula de matriz. Quem não está familiarizado com fórmulas de matriz, sugiro que leia primeiro.

Uma fórmula de matriz pode retornar um único valor ou uma matriz. No nosso caso, a fórmula de matriz retornará um único valor:

Vamos dar uma olhada em como a fórmula de matriz funciona na célula G3

SUM(($F$3:$F$15-$H$3:$H$15)^2) define a soma das diferenças ao quadrado; de fato, a fórmula calcula a seguinte soma = (F3 - H3) 2 + (F4 - H4) 2 + ... + (F15 - H15) 2

COUNT($F$3:$F$15) – número de valores no intervalo F3:F15

SQRT(SOMA(($F$3:$F$15-$H$3:$H$15)^2)/(CONTAGEM($F$3:$F$15)-1)) = σ

O valor de 6,2% é o ponto do limite inferior de controle = 8,3% - 2 σ

Aspas curvas em ambos os lados de uma fórmula indicam que é uma fórmula de matriz. Para criar uma fórmula de matriz, depois de inserir a fórmula na célula G3:

H4 - 2*RAIZ(SOMA(($F$3:$F$15-$H$3:$H$15)^2)/(CONTAGEM($F$3:$F$15)-1))

você precisa pressionar não Enter, mas Ctrl + Shift + Enter. Não tente digitar chaves no teclado - a fórmula de matriz não funcionará. Se você quiser editar uma fórmula de matriz, faça-o da mesma forma que com uma fórmula normal, mas novamente, após a edição, pressione Ctrl + Shift + Enter em vez de Enter.

Uma fórmula de matriz que retorna um único valor pode ser "arrastada" como uma fórmula regular.

Como resultado, obtivemos um gráfico de controle construído para dados com tendência de queda.

P.S. Depois que a nota foi escrita, pude refinar as fórmulas usadas para calcular o desvio padrão para dados com tendência. Você pode se familiarizar com eles no arquivo Excel.

Temos que lidar com o cálculo de valores como variância, desvio padrão e, claro, o coeficiente de variação. É o cálculo deste último que deve receber atenção especial. É muito importante que todo iniciante que está começando a trabalhar com um editor de planilhas possa calcular rapidamente a dispersão relativa dos valores.

O que é o coeficiente de variação e por que ele é necessário?

Assim, parece-me que seria útil fazer uma pequena digressão teórica e compreender a natureza do coeficiente de variação. Este indicador é necessário para refletir o intervalo de dados em relação ao valor médio. Em outras palavras, mostra a razão entre o desvio padrão e a média. É costume medir o coeficiente de variação em termos percentuais e usá-lo para mostrar a homogeneidade da série temporal.

O coeficiente de variação se tornará um assistente indispensável no caso de você precisar fazer uma previsão com base nos dados de uma determinada amostra. Este indicador destacará os principais intervalos de valores que serão mais úteis para previsões subsequentes, além de limpar a amostra de fatores insignificantes. Portanto, se você vir que o valor do coeficiente é 0%, declare com confiança que a série é homogênea, o que significa que todos os valores nela são iguais entre si. Se o coeficiente de variação assumir um valor superior a 33%, isso indica que você está lidando com uma série heterogênea na qual os valores individuais diferem significativamente da média da amostra.

Como encontrar o desvio padrão?

Como precisamos usar o desvio padrão para calcular o indicador de variação no Excel, seria bastante apropriado descobrir como calculamos esse parâmetro.

Do curso de álgebra escolar, sabemos que o desvio padrão é a raiz quadrada extraída da variância, ou seja, esse indicador determina o grau de desvio de um determinado indicador da amostra total em relação ao seu valor médio. Com sua ajuda, podemos medir a medida absoluta de flutuação da característica em estudo e interpretá-la claramente.

Calcular o coeficiente no Excel

Infelizmente, o Excel não possui uma fórmula padrão que permita calcular o indicador de variação automaticamente. Mas isso não significa que você tenha que fazer os cálculos em sua cabeça. A ausência de um modelo na "Barra de Fórmulas" em nada diminui as habilidades do Excel, então você pode facilmente forçar o programa a realizar o cálculo que você precisa digitando manualmente o comando apropriado.

Para calcular o indicador de variação no Excel, você precisa se lembrar do curso de matemática da escola e dividir o desvio padrão pela média amostral. Ou seja, na verdade, a fórmula se parece com isso - STDEV(intervalo de dados especificado) / AVERAGE(intervalo de dados especificado). Você precisa inserir essa fórmula na célula do Excel na qual deseja obter o cálculo necessário.

Lembre-se de que, como o coeficiente é expresso em porcentagem, a célula com a fórmula precisará ser formatada de acordo. Você pode fazer isso da seguinte maneira:

1. Abra a guia Início.
2. Encontre a categoria nele " Formatar células"E selecione a opção necessária.

Alternativamente, você pode definir o formato de porcentagem para a célula clicando com o botão direito do mouse na célula da tabela ativada. No menu de contexto que aparece, semelhante ao algoritmo acima, você precisa selecionar a categoria “Formato da célula” e definir o valor necessário.

Selecione "Porcentagem" e, opcionalmente, insira o número de casas decimais

Talvez o algoritmo acima pareça complicado para alguém. Na verdade, calcular o coeficiente é tão simples quanto somar dois números naturais. Depois de concluir esta tarefa no Excel, você nunca mais voltará a soluções multissilábicas tediosas em um bloco de anotações.

Ainda não conseguiu fazer uma comparação qualitativa do grau de dispersão dos dados? Perdeu no tamanho da amostra? Então agora mesmo vá direto ao assunto e domine na prática todo o material teórico que foi apresentado acima! Deixe que a análise estatística e o desenvolvimento da previsão não lhe causem mais medo e negatividade. Economize energia e tempo com