miércoles, 9 de mayo de 2012

Cómo construir rangos con fórmulas matriciales en Excel.

Últimamente me llegan, recurrentemente, consultas sobre cómo construir fórmulas matriciales y cómo emplearlas; asi que me he animado a subir una nueva entrada tratando este asunto de Matriciales en Excel. En concreto, trataré de explicar la utilidad de trabajar con matriciales para conseguir rangos de trabajo, y emplear éstos en otras funciones que requieren como argumento una matriz.
Anticipar, que no todo es posible con estas potentes fórmulas, y que en ocasiones es mucho más ágil trabajar con otras herramientas igual de poderosas, como las Tablas dinámicas.

En la entrada de hoy daré respuesta a la consulta de un lector, que solicitaba obtener sobre un origen de datos, cierta información condicionada; en particular daré una doble solución, una con fórmulas matriciales y otra mediante Tablas dinámicas, empleando además la Segmentación de datos.
Veamos el origen de datos y qué información es la que se requiere:

Cómo construir rangos con fórmulas matriciales en Excel.


Como vemos la idea es completar los tres informes, uno para cada categoría (niño, joven o adulto) según los tres menores tiempos obtenidos.
En primer lugar asignaremos una función K.ESIMO.MENOR 'matricial', esto es, incorporaremos como primer argumento de la función una fórmula matricial que devolvería únicamente los tiempos marcados por aquellos con categoría 'niño':
{=K.ESIMO.MENOR(SI($A$2:$A$10=$C$12;$C$2:$C$10;"");B13)}
con la fórmula SI($A$2:$A$10=$C$12;$C$2:$C$10;"") conseguimos trabajar sólo con los tiempos de los niños; y al anidar ese rango en la función K.ESIMO.MENOR e ir indicándole la posición de tiempo, conseguimos los tres mejores tiempos de aquellos con categoría 'niño'.

Cómo construir rangos con fórmulas matriciales en Excel.

Repetiriamos la fórmula para cada tabla y categoría ('joven' o 'adulto')

Con estos 'tiempos' completos, podemos proceder a conseguir los Nombres de quienes hayan hecho esos tiempos. Asi que nuevamente conseguimos rangos de celdas mediante fórmulas matriciales.
Mediante el uso de la funciín INDICE y COINCIDIR, como tantas veces, conseguiremos el nombre correspondiente:
{=INDICE(SI($A$2:$A$10=$C$12;$B$2:$B$10;"");COINCIDIR(C13;SI($A$2:$A$10=$C$12;$C$2:$C$10;"");0))}
Con la matricial SI($A$2:$A$10=$C$12;$B$2:$B$10;"") tendríamos el rango de nombres sólo de la categoría a estudio ('niño' para este caso); y con COINCIDIR(C13;SI($A$2:$A$10=$C$12;$C$2:$C$10;"");0) encontramos la posición del tiempo buscado en el rango de tiempos exclusivos de los 'niño'.

Cómo construir rangos con fórmulas matriciales en Excel.


Copiando estas dos matriciales conseguiríamos el siguiente resultado:

Cómo construir rangos con fórmulas matriciales en Excel.


Explicado el uso de estas matriciales para conseguir nuestro objetivo, veremos ahora como llegar al mismo resultado empleando tablas dinámicas, otra herramienta de Excel.
Construiremos una Tabla dinámica sobre el origen de datos (rango A1:C10), llevándonos al área de filas el campo Nombre, y al área de datos el campo Tiempo:

Cómo construir rangos con fórmulas matriciales en Excel.


Desde la barra de herramientas de Tabla dinámica, pestaña de Opciones, Grupo de Ordenar y Filtrar, seleccionaremos Insertar Segmentación de datos, agregando el campo 'Categoría.
Además, daremos una ordenación al campo 'Nombre' sobre el campo Tiempo:

Cómo construir rangos con fórmulas matriciales en Excel.


Por último, seleccionando el Filtro del campo Nombre, marcaremos Filtros de valor>Diez mejores:

Cómo construir rangos con fórmulas matriciales en Excel.


Y en particular, elegiremos mostrar los tres tiempos inferiores:

Cómo construir rangos con fórmulas matriciales en Excel.


Con esta configuración de tabla dinámica llegamos a este resultado:

Cómo construir rangos con fórmulas matriciales en Excel.


Consiguiendo los mismos resultados presionando la categoría deseado en el Segmento de datos.

2 comentarios:

  1. COMO PODRIA HACER, SI TENGO UN FORMATO DE ENTRADA DE SALIDA DE PRODUCTOS RETORNABLES. QUIERO SABER CUANTOS PRODUCTOS DE UN TIPO HAN SALIDO A UNA DETERMINADA TIENDA. PONIENDO COMO CELDA A ELEGIR LA TIENDA A LA QUE FUE DESTINADA.

    ResponderEliminar
    Respuestas
    1. Hola Vladx,
      no das muchas pistas de cómo tienes configurada tu hoja, pero probablemente con un SUMAR.SI o quizá un CONTAR.SI sobre la tabla con datos, y tomando como criterio la tienda elegida.
      Slds

      Eliminar