La varianza es una medida de dispersión que describe la desviación comparativa entre los valores de los datos y la media. Es la medida de dispersión más utilizada en estadística, calculada sumando y elevando al cuadrado la desviación de cada valor de datos de la media. La fórmula para calcular la varianza se proporciona a continuación:
s 2 – varianza muestral;
x av: media muestral;
norte — tamaño de la muestra (número de valores de datos),
(x i – x avg) es la desviación del valor promedio para cada valor del conjunto de datos.
Para comprender mejor la fórmula, veamos un ejemplo. No me gusta mucho cocinar, así que rara vez lo hago. Sin embargo, para no pasar hambre, de vez en cuando tengo que acudir al fogón para implementar el plan de saturar mi cuerpo de proteínas, grasas y carbohidratos. El siguiente conjunto de datos muestra cuántas veces cocina Renat cada mes:
El primer paso para calcular la varianza es determinar la media muestral, que en nuestro ejemplo es 7,8 veces por mes. El resto de los cálculos se pueden facilitar utilizando la siguiente tabla.
La fase final del cálculo de la varianza se ve así:
Para aquellos a quienes les gusta hacer todos los cálculos de una sola vez, la ecuación quedaría así:
Usando el método de conteo crudo (ejemplo de cocina)
Hay mas método efectivo cálculo de la varianza, conocido como método de "conteo bruto". Aunque la ecuación puede parecer bastante engorrosa a primera vista, en realidad no da tanto miedo. Puede asegurarse de esto y luego decidir qué método le gusta más.
es la suma de cada valor de datos después de elevar al cuadrado,
es el cuadrado de la suma de todos los valores de los datos.
No pierdas la cabeza ahora mismo. Pongamos todo esto en una tabla y verás que aquí hay menos cálculos que en el ejemplo anterior.
Como puede ver, el resultado fue el mismo que cuando se utilizó el método anterior. Ventajas este método se vuelven evidentes a medida que aumenta el tamaño de la muestra (n).
Cálculo de varianza en Excel
Como probablemente ya habrás adivinado, Excel tiene una fórmula que te permite calcular la varianza. Además, a partir de Excel 2010, puedes encontrar 4 tipos de fórmulas de varianza:
1) VARIANCE.V: devuelve la varianza de la muestra. Los valores booleanos y el texto se ignoran.
2) DISP.G: devuelve la varianza de población. Los valores booleanos y el texto se ignoran.
3) VARIANCE: devuelve la varianza de la muestra, teniendo en cuenta los valores booleanos y de texto.
4) VARIANCE: devuelve la varianza de la población, teniendo en cuenta los valores lógicos y de texto.
Primero, comprendamos la diferencia entre una muestra y una población. El propósito de la estadística descriptiva es resumir o mostrar datos para que pueda obtener rápidamente una visión general, una descripción general, por así decirlo. La inferencia estadística le permite hacer inferencias sobre una población basándose en una muestra de datos de esa población. La totalidad representa todo. posibles resultados o medidas que sean de nuestro interés. Una muestra es un subconjunto de una población.
Por ejemplo, nos interesa la totalidad de un grupo de estudiantes de uno de los universidades rusas y necesitamos determinar la puntuación media del grupo. Podemos calcular el rendimiento medio de los estudiantes, y luego la cifra resultante será un parámetro, ya que en nuestros cálculos participará toda la población. Sin embargo, si queremos calcular el GPA de todos los estudiantes de nuestro país, entonces este grupo será nuestra muestra.
La diferencia en la fórmula para calcular la varianza entre una muestra y una población es el denominador. Donde para la muestra será igual a (n-1), y para la población general solo n.
Ahora veamos las funciones para calcular la varianza con terminaciones. A, en cuya descripción se dice que el cálculo tiene en cuenta el texto y valores booleanos. EN en este caso al calcular la varianza de una determinada matriz de datos, donde no hay valores numéricos Excel interpretará el texto y los valores booleanos falsos como iguales a 0 y los valores booleanos verdaderos como iguales a 1.
Entonces, si tiene una matriz de datos, calcular su varianza no será difícil usando una de las funciones de Excel enumeradas anteriormente.
Buenas tardes
En este artículo, decidí ver cómo funciona la desviación estándar en Excel usando la función ESTANDARDEVAL. Simplemente no lo he descrito ni comentado desde hace mucho tiempo, y también simplemente porque es una función muy útil para quienes estudian matemáticas superiores. Y ayudar a los estudiantes es sagrado; sé por experiencia lo difícil que es dominarlo. En realidad, las funciones de desviación estándar se pueden utilizar para determinar la estabilidad de los productos vendidos, crear precios, ajustar o formar un surtido, y nada menos. análisis útiles tus ventas.
Excel utiliza varias variaciones de esta función de varianza:
Teoría matemática
Primero, un poco sobre la teoría de cómo. lenguaje matemático puedes describir la función Desviación Estándar para usarlo en Excel, para analizar, por ejemplo, datos de estadísticas de ventas, pero hablaremos de eso más adelante. Te lo advierto de inmediato, escribiré muchas palabras incomprensibles...)))), si hay algo más abajo en el texto, busca inmediatamente una aplicación práctica en el programa.
¿Qué hace exactamente la desviación estándar? Produce una estimación de la desviación estándar. variable aleatoria X en relación con ella expectativa matemática basado en una estimación insesgada de su varianza. De acuerdo, suena confuso, ¡pero creo que los estudiantes entenderán de qué estamos hablando en realidad!
Primero necesitamos determinar la “desviación estándar”, para luego calcular la “desviación estándar”, la fórmula nos ayudará con esto: 
La fórmula se puede describir de la siguiente manera: se medirá en las mismas unidades que las medidas de una variable aleatoria y se utiliza al calcular el error medio aritmético estándar cuando se realizan construcciones. intervalos de confianza, al probar hipótesis para estadística o al analizar una relación lineal entre cantidades independientes. La función se define como Raíz cuadrada de la varianza de las variables independientes.
Ahora podemos definir y Desviación Estándar es un análisis de la desviación estándar de una variable aleatoria X en relación con su perspectiva matemática basado en una estimación insesgada de su varianza. La fórmula está escrita así: 
Observo que las dos estimaciones están sesgadas. En casos generales No es posible construir una estimación imparcial. Pero una estimación basada en una estimación de la varianza insesgada será consistente.
Implementación práctica en Excel.
Bueno, ahora dejemos la aburrida teoría y veamos en la práctica cómo funciona la función ESTANDARDEVAL. No consideraré todas las variaciones de la función de desviación estándar en Excel, una es suficiente, pero en ejemplos. Como ejemplo, veamos cómo se determinan las estadísticas de estabilidad de ventas.
Primero, fíjate en la ortografía de la función, y como puedes ver, es muy sencilla:
DESVIACIÓN ESTÁNDAR.Г(_número1_;_número2_; ….), donde:
Ahora creemos un archivo de ejemplo y, en base a él, consideremos cómo funciona esta función. 
Dado que para realizar cálculos analíticos es necesario utilizar al menos tres valores, como en principio en cualquier análisis estadístico, tomé condicionalmente 3 períodos, esto podría ser un año, un trimestre, un mes o una semana. En mi caso, un mes. Para una máxima confiabilidad, recomiendo tomar tantos períodos como sea posible, pero no menos de tres. Todos los datos de la tabla son muy simples para mayor claridad de funcionamiento y funcionalidad de la fórmula.
Primero, necesitamos calcular el valor promedio por mes. Usaremos la función PROMEDIO para esto y obtendremos la fórmula: = PROMEDIO(C4:E4). 
Ahora, de hecho, podemos encontrar la desviación estándar usando la función STANDARDEVAL.G, en cuyo valor debemos ingresar las ventas del producto para cada período. El resultado será una fórmula de la siguiente forma: =DESVIACIÓN ESTÁNDAR.Г(C4;D4;E4). 
Bueno, la mitad del trabajo está hecho. Próximo paso formamos “Variación”, esta se obtiene dividiendo por el valor promedio, desviación estándar y convirtiendo el resultado en porcentajes. Obtenemos la siguiente tabla: 
Bueno, los cálculos básicos están completos, solo queda averiguar si las ventas son estables o no. Tomemos como condición que las desviaciones del 10% se consideren estables, del 10 al 25% son desviaciones pequeñas, pero cualquier valor superior al 25% ya no es estable. Para obtener el resultado según las condiciones usaremos una lógica y para obtener el resultado escribiremos la fórmula:
SI(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))
Todos los rangos se toman para mayor claridad; sus tareas pueden tener condiciones completamente diferentes. 
Para mejorar la visualización de datos, cuando tu tabla tenga miles de posiciones, debes aprovechar para aplicar ciertas condiciones que necesites o utilizar para resaltar ciertas opciones con un esquema de colores, esto quedará muy claro.
Primero, seleccione aquellos a los que aplicará formato condicional. En el panel de control "Inicio", seleccione "Formato condicional" y en el menú desplegable, seleccione "Reglas para resaltar celdas" y luego haga clic en el elemento del menú "El texto contiene...". Aparece un cuadro de diálogo en el que ingresa sus condiciones.
Una vez que haya escrito las condiciones, por ejemplo, "estable" - verde, "normal" - amarillo e "inestable" - rojo, obtenemos una tabla hermosa y comprensible en la que puede ver a qué prestar atención primero.
Usando VBA para la función STDEV.Y
Cualquier persona interesada puede automatizar sus cálculos mediante macros y utilizar la siguiente función:
Función MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Para cada x en Arr aSum = aSum + x "calcula la suma de los elementos de la matriz aCnt = aCnt + 1 "calcula el número de elementos Siguiente x aAver = aSum / aCnt "valor promedio para cada x In Arr tmp = tmp + (x - aAver) ^ 2 "calcula la suma de los cuadrados de la diferencia entre los elementos de la matriz y el valor promedio Next x MyStDevP = Sqr(tmp / aCnt ) "calcular STANDARDEV.G() Función final
Función MyStDevP(Arr) Dim x, aCnt &, aSum #, aAver#, tmp# Para cada x en Arr unaSuma = unaSuma + x "calcular la suma de los elementos de la matriz |
Las estadísticas utilizan una gran cantidad de indicadores y uno de ellos es el cálculo de la varianza en Excel. Si lo hace usted mismo manualmente, le llevará mucho tiempo y podrá cometer muchos errores. Hoy veremos cómo descomponer fórmulas matemáticas en funciones simples. Veamos algunos de los métodos de cálculo más simples, rápidos y convenientes que le permitirán hacer todo en cuestión de minutos.
Calcular la varianza
La varianza de una variable aleatoria es la expectativa matemática de la desviación al cuadrado de una variable aleatoria de su expectativa matemática.
Calculamos en base a la población general.
Para calcular la estera. Esperando que el programa use la función DISP.G, y su sintaxis se ve así: “=DISP.G(Número1;Número2;…)”.
Se pueden utilizar un máximo de 255 argumentos, no más. Los argumentos pueden ser números primos o referencias a las celdas en las que se especifican. Veamos cómo calcular la varianza en Microsoft Excel:
1. El primer paso es seleccionar la celda donde se mostrará el resultado del cálculo y luego hacer clic en el botón "Insertar función".
2. Se abrirá el shell de administración de funciones. Allí debes buscar la función “DISP.G”, que puede estar en la categoría “Estadística” o “Lista alfabética completa”. Cuando lo encuentre, selecciónelo y haga clic en "Aceptar".
3. Se abrirá una ventana con los argumentos de la función. En él debe seleccionar la línea "Número 1" y en la hoja seleccionar el rango de celdas con la serie numérica.
4. Después de esto, los resultados del cálculo se mostrarán en la celda donde se ingresó la función.
Así es como puedes encontrar fácilmente la variación en Excel.
Hacemos cálculos en base a la muestra.
En este caso, la varianza muestral en Excel se calcula con el denominador indicando no el número total de números, sino uno menos. Esto se hace para un error menor usando la función especial DISP.V, cuya sintaxis es =DISP.V(Número1;Número2;...). Algoritmo de acciones:
- Como en el método anterior, debes seleccionar la celda para el resultado.
- En el Asistente de funciones, debería encontrar "DISP.B" en la categoría "Lista alfabética completa" o "Estadística".
- A continuación, aparecerá una ventana y deberás proceder de la misma forma que en el método anterior.
Vídeo: Calcular la varianza en Excel
Conclusión
La varianza en Excel se calcula de manera muy simple, mucho más rápida y conveniente que hacerlo manualmente, porque la función de expectativa matemática es bastante compleja y calcularla puede requerir mucho tiempo y esfuerzo.
Entre los muchos indicadores que se utilizan en estadística, cabe destacar el cálculo de la varianza. Cabe señalar que realizar este cálculo manualmente es una tarea bastante tediosa. Afortunadamente, Excel cuenta con funciones que te permiten automatizar el procedimiento de cálculo. Descubramos el algoritmo para trabajar con estas herramientas.
La dispersión es un indicador de variación, que es el cuadrado promedio de las desviaciones de la expectativa matemática. Por tanto, expresa la dispersión de números alrededor del valor medio. El cálculo de la varianza se puede realizar tanto para la población general como para la muestra.
Método 1: cálculo basado en la población
Para calcular este indicador en Excel para la población general, utilice la función DISP.G. La sintaxis de esta expresión es la siguiente:
DISP.G(Número1;Número2;…)
En total se pueden utilizar de 1 a 255 argumentos. Los argumentos pueden ser valores numéricos o referencias a las celdas que los contienen.
Veamos cómo calcular este valor para un rango con datos numéricos.
Método 2: cálculo por muestra
A diferencia de calcular un valor en base a una población, al calcular una muestra, el denominador no indica el número total de números, sino uno menos. Esto se hace con el fin de corregir errores. Excel tiene en cuenta este matiz en una función especial diseñada para este tipo de cálculo: DISP.V. Su sintaxis está representada por la siguiente fórmula:
DISP.B(Número1;Número2;…)
El número de argumentos, como en la función anterior, también puede oscilar entre 1 y 255.
Como puede ver, el programa Excel puede facilitar enormemente el cálculo de la varianza. Esta estadística puede ser calculada por la aplicación, ya sea a partir de la población o de la muestra. En este caso, todas las acciones del usuario se reducen a especificar el rango de números a procesar, y Excel hace el trabajo principal por sí mismo. Por supuesto, esto ahorrará una cantidad significativa de tiempo al usuario.
La función de desviación estándar ya pertenece a la categoría de matemáticas superiores relacionadas con la estadística. Hay varias opciones para usar la función Desviación estándar en Excel:
- Función ESTÁNDAR.
- Función DESVIACIÓN ESTÁNDAR.
- función DESVEST
Necesitaremos estas funciones en las estadísticas de ventas para identificar la estabilidad de las ventas (análisis XYZ). Estos datos se pueden utilizar tanto para fijar precios como para crear (ajustar) la matriz de surtido y para otros análisis de ventas útiles, de los que definitivamente hablaré en futuros artículos.
Prefacio
Veamos primero las fórmulas en lenguaje matemático y luego (a continuación en el texto) analizaremos en detalle la fórmula en Excel y cómo se utiliza el resultado resultante en el análisis de estadísticas de ventas.
Entonces, la desviación estándar es una estimación de la desviación estándar de una variable aleatoria. X con respecto a su expectativa matemática basada en una estimación imparcial de su varianza)))) ¡No tengas miedo de las palabras incomprensibles, ten paciencia y entenderás todo!
Descripción de la fórmula: La desviación estándar se mide en unidades de medida de la propia variable aleatoria y se utiliza al calcular el error estándar de la media aritmética, al construir intervalos de confianza, al probar estadísticamente hipótesis, al medir la relación lineal entre variables aleatorias. . Definida como la raíz cuadrada de la varianza de la variable aleatoria.
Ahora la desviación estándar es una estimación de la desviación estándar de una variable aleatoria. X en relación con su expectativa matemática basada en una estimación insesgada de su varianza:
Dispersión;
- iº elemento de la selección;
Tamaño de la muestra;
Media aritmética de la muestra:
Cabe señalar que ambas estimaciones están sesgadas. En el caso general, es imposible elaborar una estimación insesgada. Sin embargo, la estimación basada en la estimación de la varianza insesgada es consistente.
regla tres sigma(): casi todos los valores de una variable aleatoria distribuida normalmente se encuentran en el intervalo. Más estrictamente, con una probabilidad de aproximadamente 0,9973, el valor de una variable aleatoria distribuida normalmente se encuentra en el intervalo especificado (siempre que el valor sea verdadero y no se obtenga como resultado del procesamiento de la muestra). Usaremos un intervalo redondeado de 0,1
Si se desconoce el valor real, entonces deberías usar no, pero s. Así, la regla de tres sigma se transforma en regla de tres. s. Es esta regla la que nos ayudará a determinar la estabilidad de las ventas, pero hablaremos de eso más adelante...
Ahora la función de desviación estándar en Excel
Espero no haberte aburrido demasiado con las matemáticas. Quizás alguien necesite esta información para un ensayo o algún otro propósito. Ahora veamos cómo funcionan estas fórmulas en Excel...
Para determinar la estabilidad de las ventas no es necesario profundizar en todas las opciones de las funciones de desviación estándar. Usaremos solo uno:
función DESVEST
DESVEST(numero 1;Número 2;... )
Número1, número2,..- de 1 a 30 argumentos numéricos correspondientes a la población general.
Ahora veamos un ejemplo:
Creemos un libro y una mesa improvisada. Descargará este ejemplo en Excel al final del artículo.
¡¡¡Continuará!!!
Hola de nuevo. ¿¡Bien!? Tuve un minuto libre. ¿Continuemos?
Y así la estabilidad de las ventas con la ayuda. Funciones DESVEST
Para mayor claridad, tomemos algunos productos improvisados:
En análisis, ya sea un pronóstico, una investigación o cualquier otra cosa relacionada con las estadísticas, siempre es necesario tomar tres períodos. Podría ser una semana, un mes, un trimestre o un año. Es posible, e incluso mejor, tomar tantos períodos como sea posible, pero no menos de tres.
Específicamente mostré ventas exageradas, donde a simple vista se puede ver qué se vende consistentemente y qué no. Esto facilitará la comprensión de cómo funcionan las fórmulas.
Y entonces tenemos ventas, ahora necesitamos calcular los valores promedio de ventas por período.
La fórmula para el valor promedio es PROMEDIO (datos del período), en mi caso la fórmula se ve así = PROMEDIO (C6: E6)
Aplicamos la fórmula a todos los productos. Esto se puede hacer tomando la esquina derecha de la celda seleccionada y arrastrándola hasta el final de la lista. O coloque el cursor en la columna con el producto y presione las siguientes combinaciones de teclas:
Ctrl + Abajo mueve el cursor al principio de la lista.
Ctrl + Derecha, el cursor se mueve al lado derecho de la tabla. Una vez más a la derecha y llegaremos a la columna con la fórmula.
Ahora sujetamos
Ctrl + Shift y presione hacia arriba. De esta manera seleccionaremos el área donde se dibujará la fórmula.
Y la combinación de teclas Ctrl + D arrastrará la función donde la necesitemos.
Recuerde estas combinaciones, realmente aumentan su velocidad en Excel, especialmente cuando trabaja con matrices grandes.
La siguiente etapa, la función de salida estándar en sí, como ya dije, usaremos solo una DESVEST
Escribimos la función y establecemos los valores de ventas de cada período en los valores de la función. Si tiene ventas en la tabla una tras otra, puede usar un rango, como en mi fórmula =DESVEST(C6:E6) o enumerar las celdas requeridas separadas por punto y coma =DESVEST(C6;D6;E6)
Ahora todos los cálculos están listos. Pero, ¿cómo saber qué se vende de forma constante y qué no? Pongamos la convención XYZ donde,
X es estable
Y - con pequeñas desviaciones
Z - no estable
Para hacer esto, utilizamos intervalos de error. Si se producen fluctuaciones dentro del 10%, asumiremos que las ventas son estables.
Si está entre el 10 y el 25 por ciento, será Y.
Y si el valor de variación supera el 25%, esto no es estabilidad.
Para configurar correctamente las letras de cada producto utilizaremos la fórmula SI. Conozca más sobre. En mi tabla esta función se verá así:
SI(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))
En consecuencia, ampliamos todas las fórmulas para todos los nombres.
Intentaré responder inmediatamente a la pregunta: ¿Por qué los intervalos del 10% y del 25%?
De hecho, los intervalos pueden ser diferentes, todo depende de la tarea específica. Específicamente les mostré valores de ventas exagerados, donde la diferencia es visible a simple vista. Evidentemente, el producto 1 no se vende de forma constante, pero la dinámica muestra un aumento de las ventas. Dejamos este producto en paz...
Pero aquí está el producto 2, ya hay una desestabilización evidente. Y nuestros cálculos muestran Z, lo que nos dice que las ventas no son estables. El Producto 3 y el Producto 5 muestran un rendimiento estable; tenga en cuenta que la variación está dentro del 10%.
Aquellos. El producto 5 con puntuaciones de 45, 46 y 45 muestra una variación del 1%, que es una serie numérica estable.
Pero el Producto 2 con los indicadores 10, 50 y 5 muestra una variación del 93%, lo cual NO es una serie numérica estable.
Después de todos los cálculos, puede poner un filtro y filtrar la estabilidad, de modo que si su tabla consta de varios miles de artículos, podrá identificar fácilmente cuáles no son estables en ventas o, por el contrario, cuáles son estables.
"Y" no funcionó en mi tabla, creo que para mayor claridad de la serie numérica, es necesario agregarla. Dibujaré el Producto 6...
Verá, la serie numérica 40, 50 y 30 muestra una variación del 20%. No parece haber un gran error, pero la propagación sigue siendo significativa...
Y así para resumir:
10.50.5 - Z no es estable. Variación superior al 25%
40,50,30 - Y puedes prestar atención a este producto y mejorar sus ventas. Variación inferior al 25% pero superior al 10%
45,46,45 - X es estabilidad, no necesitas hacer nada con este producto todavía. Variación inferior al 10%
¡Eso es todo! Espero haberme explicado todo claramente, si no pregunta lo que no queda claro. Y le estaré agradecido por cada comentario, ya sea elogio o crítica. Así sabré que me estás leyendo y que a ti, que es muy IMPORTANTE, te interesa. Y en consecuencia, aparecerán nuevas lecciones.
