Un caso interesante con fórmula matricial

Las fórmulas matriciales son de gran ayuda para resolver problemas complejos en Excel. Pero muchos usuarios, aunque habrán oído hablar de ellas, no las han usado lo suficiente para descubrir todo su potencial. Si tuviera que mencionar una razón para ello, sería la siguiente: para la mayoría de los usuarios no es sencillo entender la lógica detrás de las fórmulas matriciales. Y si tuviera que dar una razón más, sería la siguiente: Casi todo lo que se puede hacer con una fórmula matricial puede hacerse también por otros medios que, en muchos casos, involucran el uso de columnas y fórmulas auxiliares, filtros y tal vez uno que otro copiar y pegar.

En este tutorial te propongo un nuevo caso a resolver mediante una fórmula matricial más o menos complicada. Como siempre, para que puedas seguir este tutorial, te dejo el archivo de ejemplo donde se muestra la información del caso que se tiene y lo que deseamos que haga la fórmula matricial:

Caso a resolver con fórmula matricial

Descarga desde Dropbox

Como podrás ver en el archivo, el caso es el siguiente: Se tiene una lista de empleados en las columna B y su estado correspondiente (activo o inactivo) en la columna C. Se pide diseñar una fórmula en la columna F para devolver todos los nombres de empleados en estado activo. La fórmula debe actualizarse al ingresar nuevos empleados y al cambiar el estado de los ya existentes.

Por supuesto que la información de los empleados activos puede obtenerse sin ayuda de ninguna fórmula matricial. Basta utilizar un filtro o un filtro avanzado y la información estará disponible rápidamente. El problema de este enfoque es por supuesto que la información no se actualizará automáticamente cada vez que se agregue un nuevo empleado o se cambie el estado de uno existente. Para lograr tal grado de flexibilidad, uno puede optar por el uso de fórmulas matriciales, que aunque más complicadas, sí resuelven el problema de un solo golpe. Este es precisamente el poder parcialmente ignorado de las fórmulas matriciales.

Como siempre, la respuesta la encontrarás más abajo, pero antes de visualizarla, te invito a que intentes hacer el ejercicio por tu cuenta. La satisfacción de haber resuelto un problema complejo, es de lo mejor que uno puede experimentar en su vida profesional. Así que, adelante, es todo tuyo.

La idea general es la siguiente: utilizaremos la fórmula ÍNDICE, la cual, dado un rango, un número de filas y un número de columnas, devuelve el valor de la celda que se encuentra en esa posición del rango. Por supuesto, el rango será B1:C100 y la columna será la columna B, que es la columna 1 del rango.

Todo lo demás se enfocará en obtener los números de fila correctos. Por supuesto, los números de fila correctos son los números de fila en los que el estado es Activo.

Por supuesto, nos gustaría que la lista fuera un rango continuo, es decir, que si un empleado inactivo se encuentra entre dos empleados activos, al hacer el filtro nos gustaría que los dos empleados activos quedaran en celdas contiguas. Si este no fuera algo importante, obviamente podríamos utilizar solo una fórmula SI que devolviera vacío al encontrar un empleado en estado inactivo.

El primer paso

La siguiente fórmula permite discriminar con un número grande (en este caso 1000), las celdas del rango C4:C100 cuyo valor es "Inactivo"

=SI(C4:C100="Activo",1,1000)

Recuerda que para definir esta fórmula matricial, debes seleccionar todo el rango F4:F100, escribir la fórmula y presionar Ctrl+Shift+Enter. Utilizando esta fórmula como base, podemos ahora multiplicar este resultado por el número de fila correspondiente. Observa que lo obtendremos es básicamente esto:

Si el estado es Activo, obtendremos el número de fila.
Si el estado es Inactivo, obtendremos el número de fila multiplicado por 1000.

Ordenar la lista

Ahora procedemos a ordenar la lista para que los valores mayores a 1000 queden hasta el final. Esto lo podemos hacer utilizando la fórmula K.ESIMO.MENOR:

=K.ESIMO.MENOR(FILA(A4:A100)*
SI(C4:C100="Activo",1,1000),FILA(A1:A97))

Observa que esta fórmula incluye como argumento FILA(A1:A97) que devuelve los números del 1 al 97, los cuales son los valores de K que se utilizarán para el ordenamiento.

Reemplazar valores por cadena vacía

Ahora nos gustaría discriminar con una cadena vacía los valores en los que el estado Inactivo. Dado que estos valores son siempre mayores que 1000, podemos utilizar:

=SI(K.ESIMO.MENOR(FILA(A4:A100)*
SI(C4:C100="Activo",1,1000),FILA(A1:A97))>1000,"",K.ESIMO.MENOR(FILA(A4:A100)*
SI(C4:C100="Activo",1,1000),FILA(A1:A97)))

Agregar ÍNDICE y SI.ERROR

¡Y ya lo tenemos! Lo que sigue es agregar la fórmula ÍNDICE y la fórmula SI.ERROR para discriminar los valores de error. La fórmula completa luciría así:

=SI.ERROR(INDICE(B1:C100,SI(K.ESIMO.MENOR(FILA(A4:A100)*
SI(C4:C100="Activo",1,1000),FILA(A1:A97))>1000,"",K.ESIMO.MENOR(FILA(A4:A100)*
SI(C4:C100="Activo",1,1000),FILA(A1:A97))),1),"")

¡Intenta agregar nuevos valores a la lista o cambiar los ya existentes y verás lo bien que la fórmula hace su trabajo!

¿Y a ti, te resulta difícil entender la lógica detrás de una fórmula matricial? ¿Usas fórmulas matriciales frecuentemente? Déjame tus comentarios o inquietudes.

¿Te ha gustado?

¡Compárteme!

Comentarios: 1

#1
Marlon (sábado, 14 marzo 2020 15:11)

Woow.. Es algo que no había visto antes en Excel..