FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FÓRMULAS.

Hola  a todos:

Hoy quiero escribir acerca de varios temas en este post, uno de ellos es la utilización de las fórmulas matriciales para obtener datos relevantes en conjuntos de información elevados.

Para poder ilustrar el ejercicio, vamos a realizar un ejemplo, imaginad que tenéis a los alumnos de dos clases (1º -A y 1º-B) y que os han enviado las notas por alumno de las siguientes asignaturas, de Física, Química y Matemáticas:

Como podéis ver, hay de todo … ¡desde un 10 hasta un 1!, (no he querido poner un cero, creo que todo el mundo, por el mero hecho de estar en un aula ya merece ese punto de cortesía).

Ahora resulta que necesitáis saber cual es la mediana, pero no el dato general, para este ejemplo, la mediada de todos los estudiantes de 1ªA en Física, esto lo podéis hacer mediante las siguientes fórmulas matriciales:

Hallando el PERCENTIL 50 que equivale a la mediana:

{=PERCENTIL(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000));50%)}

O directamente usando la función MEDIANA

{=MEDIANA(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

En ambos casos el resultado será el mismo, particularmente me gusta usar la fórmula de los percentiles 🙂

El resultado es el siguiente:

Efectivamente, el resultado es un 6,5. Como se puede apreciar en ambas fórmulas matriciales se combinan los criterios de la CLASE y de la ASIGNATURA, para obtener el resultado específico de los datos seleccionados.

Ahora que tenemos la mediana, queremos obtener en las dos columnas siguientes el resultado mínimo y el máximo para el mismo colectivo, es decir que el máximo debería ser el 10 y el mínimo el 4. Las fórmulas a utilizar serían las siguientes:

Para hallar el valor máximo de las notas de los estudiantes de 1:

{=MAX(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

O el mínimo:

{=MIN(SI($C$2:$C$65000=C2;SI($D$2:$D$65000=D2; $E$2:$E$65000)))}

El resultado es el esperado:

Y como resulta que queremos automatizar esta información cada cierto tiempo, hemos decidido programar estos cálculos en VBA.

Como sabéis, en VBA podemos utilizar la propiedad  .Formula que representa la fórmula del objeto en notación del estilo A1. Esto es muy útil, dado que podemos expresar el resultado del cálculo, no como valores, sino como la propia fórmula, es decir que dejará el resultado con la fórmula introducida.

Para el caso de las fórmulas matriciales, debemos especificar en el código .FormulaArray =
Teniendo en cuenta esto, ya podemos automatizar el cálculo, esta sería la macro:

Sub VARIAS_MATRICIALES()
'Definimos variables
Dim i As Integer, fin As Integer, elimina As Integer
With Sheets("DATOS")
elimina = Application.CountA(Worksheets("DATOS").Range("A:A"))
If elimina > 0 Then Worksheets("DATOS").Range("F2:H" & elimina).ClearContents
fin = Application.CountA(.Range("A:A"))
'Aplicamos fórmula matricial para hallar la mediana, el valor máximo y el mínimo según criterios indicados
For i = 2 To fin
.Cells(i, 6).FormulaArray = "=percentile(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)),50%)"
.Cells(i, 7).FormulaArray = "=max(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)))"
.Cells(i, 8).FormulaArray = "=min(if($C$2:$C$65000=C" & i & ",if($D$2:$D$65000=D" & i & " , $E$2:$E$65000)))"
Next
End With
End Sub

Obviamente, para este ejemplo, he utilizado unas fórmulas determinadas, pero se pueden usar múltiples funciones, por ejemplo, la desviación estándar, la suma, el promedio, cuartiles,  etc.

Me ha parecido útil este post por la versatilidad que nos ofrecen las matriciales a la hora de obtener datos relevantes para nuestros análisis y por compartir el uso de la propiedad .Formula en vuestros proyectos VBA.

Estas fórmulas pueden ser ideales para multitud de tareas, análisis de bandas salariales, ventas, etc y además se pueden introducir en tablas o gráficos dinámicos que agregarán valor añadido a nuestros análsis.

Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

Descarga el archivo de ejemplo pulsando en: FUNCIONES MATRICIALES PARA OBTENER MEDIANA, MAX, MIN Y OTRAS FÓRMULAS