Fórmulas estadísticas condicionales

Las fórmulas estadísticas condicionales en Excel son muy útiles y muy fáciles de utilizar. Con estas fórmulas puedes obtener información de resumen de tus datos. Si aun no estás familiarizado con estas fórmulas o si quieres recordar cómo se utilizan, entonces este tutorial es para ti.

Lo primero que tienes qué hacer es descargar el siguiente archivo de ejemplo que hemos preparado para ti. Con él podrás seguir paso a paso el tutorial para el uso de las fórmulas estadísticas condicionales:

El archivo muestra la lista de estudiantes becados en la carrera de Matemáticas de una prestigiosa universidad. La lista muestra información relevante de cada estudiante como el género y el turno. Además muestra el semestre que estudian y la calificación al finalizar el ciclo escolar:

Ahora, estamos interesados en obtener información de resumen de estos datos. La siguiente imagen muestra la información que necesitamos obtener de la lista.

Como se puede ver, requerimos información dividida principalmente en dos ramificaciones: hombres y mujeres. De acuerdo a la imagen, necesitamos obtener, para cada género, la cantidad de estudiantes, la calificación promedio, mínima y máxima y la cantidad por turno.

El último recuadro requiere un poco de información adicional. Para conservar la beca, los estudiantes tienen que obtener una calificación mínima de 6.5 por lo que los estudiantes que tengan una calificación menor, perderán la beca. La tabla nos pide indicar cuántos de los estudiantes que perderán la beca serán hombres y cuántas mujeres.

La información que se necesita es pues, información de resumen como cuenta, promedio, mínimo, máximo, etc, que cumplan con un conjunto de condiciones. Excel posee fórmulas específicas para manejar este tipo de situaciones. Comenzaremos con la fórmula CONTAR.SI la cual nos ayudará a determinar la cantidad de mujeres y de hombres. En la celda "H3" escribe la siguiente fórmula:

=CONTAR.SI(E:E,"F")

Esta fórmula, como se puede inferir de sus parámetros, devuelve el resultado de la cuenta de todas las celdas el en rango "E:E" (género) que cumplen con la condición de que el valor de la celda sea "F" (es decir, femenino). Con esto podemos ver que el número de estudiantes mujeres es de 34

Se puede hacer algo similar para la cantidad de hombres. Solo basta escribir en la celda "I3" lo siguiente:

=CONTAR.SI(E:E,"M")

Ahora para determinar el promedio de cada género, utilizaremos la fórmula PROMEDIO.SI, que funciona de forma muy similar a la anterior solo que en lugar de una cuenta, nos devuelve un promedio. En este caso las fórmulas que hay que escribir en las celdas "H4" e "I4" respectivamente son:

=PROMEDIO.SI(E:E,"F",B:B)

=PROMEDIO.SI(E:E,"M",B:B)

Observa que en este caso, la fórmula requiere tres argumentos. El primero será el rango del que se evaluará el criterio (género). El segundo será la condición a evaluar (masculino o femenino) y el tercero será el rango del que se obtendrá el promedio (el rango de calificaciones). Con estas fórmulas obtenemos:

La calificación máxima y mínima se obtienen de una forma un poco diferente. Aunque algunas versiones de Excel cuentan con la versión MAX.SI.CONJUNTO, en este tutorial utilizaremos la fórmula MAX acompañada de una fórmula matricial. Esto puede sonar complicado pero realmente es muy sencillo. Estas son las fórmulas que utilizaremos para la calificación máxima:

=MAX(SI(E:E="F",B:B))

=MAX(SI(E:E="M",B:B))

Estas fórmulas funcionarán correctamente solo si se convierten en fórmulas matriciales. Para hacer esto, al ingresar cada una de las fórmulas, en lugar de presionar Enter para aceptar la fórmula, presiona la combinación de teclas siguiente:

Control+Shift+Enter

El resultado debería verse similar a esto:

Ya que sabemos el truco, haremos lo mismo con la calificación mínima. Estas son las fórmulas que utilizaremos para las celdas "H6" e "I6" respectivamente

=MIN(SI(E:E="F",B:B))

=MIN(SI(E:E="M",B:B))

¡No olvides teclear Control+Shift+Enter para que la fórmula sea matricial! El resultado será el siguiente:

Aunque para la cantidad de estudiantes en cada turno (M,V,S) podemos utilizar nuevamente una fórmula matricial, en esta ocasión hemos optado por una fórmula ya normal incluida prácticamente en todas las versiones de Excel. Se trata de la fórmula CONTAR.SI.CONJUNTO. Esta fórmula nos permite contar un conjunto de celdas dado un conjunto de condiciones. Esto es precisamente lo que necesitamos en este caso, ya que requerimos, por ejemplo, contar la cantidad de mujeres (primer condición) que están en el turno matutino (segunda condición). Estas son las fórmulas que utilizaremos en las celdas "H7" e "I7":

=CONTAR.SI.CONJUNTO(E:E,"F",D:D,"M")

=CONTAR.SI.CONJUNTO(E:E,"M",D:D,"M")

Y en las celdas "H8" e "I8":

=CONTAR.SI.CONJUNTO(E:E,"F",D:D,"V")

=CONTAR.SI.CONJUNTO(E:E,"M",D:D,"V")

Y en las celdas "H9" e "I9":

=CONTAR.SI.CONJUNTO(E:E,"F",D:D,"S")

=CONTAR.SI.CONJUNTO(E:E,"M",D:D,"S")

Ahora solo falta determinar la cantidad de estudiantes que perderán la beca. Como dijimos, esto sucederá si la calificación del estudiante es menor a 6.5. Utilizamos entonces la fórmula CONTAR.SI.CONJUNTO de esta forma en las celdas "H11" e "I11:

=CONTAR.SI.CONJUNTO(E:E,"F",B:B,"<6.5")

=CONTAR.SI.CONJUNTO(E:E,"M",B:B,"<6.5")

Con esto queda completo el tutorial. Como siempre si tienes dudas o comentarios no dudes en hacérnoslo saber.