Pues lo más fácil como siempre es usa una de las cientos de funciones que trae Excel
=Desvest(B4:F4)
Es un listado muy simple de delitos, muchos se preguntan en clase para que puede servir sacar la desviación estándar de eso. Bien, si la desviación por tipo de delito fuera cero significaría que todos los tipos de delito se reparten del mismo modo, hay la misma cantidad de cada tipo. En cambio si la desviación es alta, indica que la zona se especializa en algún tipo.
Pero evidentemente no es lo que nos interesa si no conocer el proceso de la fórmula y tener una opción de programación que se pueda ajustar a necesidades específicas. Por ejemplo, teniendo una macro que calcule la desviación estándar, podré más adelante utilizarla en un filtro y calcular la desviación solo de ciertos valores, o calcular la desviación de grupos específicos por separado, etc.
Hacerlo por partes tampoco implica un gran reto. Tenemos que
Y esto se traduce para el anterior ejemplo en lo siguiente:
Y visto en fórmulas del siguiente modo:
Es decir, primero a cada monto de delitos se le resta el promedio previamente calculado y se coloca en la celda de abajo. Luego esto se eleva al cuadrado. todos los cuadrados se suman, eso de divide entre el número de casos, menos uno y a eso se le saca raiz cuadrada. Es muy importante que primero se divide por el número de casos menos uno y luego se saca la raíz y no al revés. Esto es por el mismo concepto de desviación estándar. Queremos saber como se comportan los datos con respecto a la media, si es un grupo homogéneo nos dará desviación baja y un grupo heterogéneo una desviación alta. Como no hay una medida para ello. Hemos sumado la variacion total cuando restamos a cada caso la media, pero la variación sumada no nos dirá nada con respecto a la media pues será cero ya que hay lo mismo arriba que abajo de la media. Por eso la multiplicamos por si misma y así tenemos una cantidad que depende de la variación total y es positiva. Como nuestra unidad debe ser lo mismo que metemos, le sacamos raíz hasta el último.
Pues ahora el asunto es como hacer la macro de desviavión estándar. Supongamos nuestro caso del mismo modo, solamente borraremos el espacio vacío que hay en la columna A.
Haremos una macro que estando con la celda activa en la casilla F4, al lado de los datos, calcule la desviación de todo lo que esté a la izquierda. Lo importante será el proceso, pues se puede ajustar para calcularla con datos hacia abajo, hacia arriba o hasta en diagonal si a alguien le sirviera eso.
Lo primero que necesitaremos será saber cual es la localización del cursor. O en que columna, esto se obtiene con la función
ActiveCell.Column
Y esto lo podemos asociar a una variable, por ejemplo que se llame columna:
Columna=ActiveCell.Column
Que en el caso de la anterior gráfica vale 6, pues la columna de la celda activa es la F que es la sexta.
Ahora si, calculamos el promedio de los datos:
For n = 1 To Columna - 1
ActiveCell.Offset(0, -1).Range("A1").Select
DatoASumar = ActiveCell.Value
Suma = Suma + DatoASumar
Next
Esto es, el cursor de la celda F irá cuando valga n=2, a la E pues ActiveCell.Offset(0,-1).Range("A1").Select lo que hace es hacer una celda (-1) en renglón a la izquierda a l cursor (select). Añade a un acumulador la suma hasta que llega a la celda A en este caso que es la primera y está a cinco lugares del inicio.
Con la siguiente macroinstruccion lo que haremos será anotar los resultados en una forma ordenada en la columna A
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = Suma
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Suma"
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.Value = Suma / (Columna - 1)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Promedio"
Promedio = Suma / (Columna - 1)
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToLeft).Select
Ahora estamos en el renglon de los datos en la columna A, y con la siguiente macroinstrucción comenzamos a calular la varianza:
For Derecha = 1 To Columna - 1
DatoAPotenciar = ActiveCell.Value
DatoAPotenciarMenosPromedioCuadrado = (DatoAPotenciar - Promedio) * (DatoAPotenciar - Promedio)
'ActiveCell.Offset(5, 0).Range("A1").Select
'ActiveCell.Value = DatoAPotenciarMenosPromedioCuadrado
'ActiveCell.Offset(-5, 0).Range("A1").Select
Varianza = Varianza + DatoAPotenciarMenosPromedioCuadrado
ActiveCell.Offset(0, 1).Range("A1").Select
Next
Finalmente el programa completo es el siguiente.
Sub DesviavionEstandar()
Columna = ActiveCell.Column
Suma = 0
For n = 1 To Columna - 1
ActiveCell.Offset(0, -1).Range("A1").Select
DatoASumar = ActiveCell.Value
Suma = Suma + DatoASumar
Next
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = Suma
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Suma"
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.Value = Suma / (Columna - 1)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Promedio"
Promedio = Suma / (Columna - 1)
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToLeft).Select
Varianza = 0
For Derecha = 1 To Columna - 1
DatoAPotenciar = ActiveCell.Value
DatoAPotenciarMenosPromedioCuadrado = (DatoAPotenciar - Promedio) * (DatoAPotenciar - Promedio)
'ActiveCell.Offset(5, 0).Range("A1").Select
'ActiveCell.Value = DatoAPotenciarMenosPromedioCuadrado
'ActiveCell.Offset(-5, 0).Range("A1").Select
Varianza = Varianza + DatoAPotenciarMenosPromedioCuadrado
ActiveCell.Offset(0, 1).Range("A1").Select
Next
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = Varianza / (Columna - 2)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Varianza"
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SQRT(R[-1]C)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "Desviacion estándar"
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub
Se pega en el area de macros y hará lo que buscamos. La salida es la siguiente:
Se puede apreciar que es el mismo resultado que el anterior y que se puede aplicar a una lista horizontal de datos tan grande como se quiera.
No hay comentarios:
Publicar un comentario