martes, septiembre 22, 2009

Búsquedas complejas en matriz de Excel

En los últimos meses varios lectores me han consultado sobre cómo resolver este ejercicio (lo que me lleva a pensar que se trata de un típico ejercicio de los que aparecen en los cursos de Excel)



Búsquedas complejas en matriz de Excel

Dadas estas tablas se pide mostrar la lista de deportes que practica un socio ingresando el número de socio. Se permite usar cualquier número de fórmulas.

Como es obvio, la solución es bastante complicada y no creo que sea de interés general para el lector promedio de este blog. De todas maneras, hay mucho que aprender de este ejercicio:

1 – cómo construir fórmulas complejas


2 – cómo usar fórmulas matriciales


3 – y lo más importante: cómo no organizar datos en Excel.

Empecemos por mostrar una captura de pantalla que muestra cómo funcionará nuestra solución:





La fórmula matricial que crea la lista de de los deportes de cada socio (la fórmula en el rango B26:F26) es

=INDICE(deportes,,K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION((COINCIDIR(B25,num_socio,0)+13),2)&":"&DIRECCION((COINCIDIR(B25,num_socio,0)+13),6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION((COINCIDIR(B25,num_socio,0)+13),2)&":"&DIRECCION((COINCIDIR(B25,num_socio,0)+13),6)),1)*COLUMNA(1:5)),COLUMNA(1:5)))

Esta fórmula la crearemos en varios pasos. El primero es crear una fórmula que nos dé el número de socio dado su nombre. Esto lo hacemos con la función BUSCARV.

Primero definimos tres rangos nominados (rangos que ponemos dentro de nombres)


nombre_Socios =Hoja1!$A$3:$A$10


num_socio=Hoja1!$A$14:$A$21


socios=Hoja1!$A$3:$B$10

El nombre “nombre_Socios” nos sirve para crear una lista desplegable en la celda B24

Búsquedas complejas en matriz de Excel

En la celda B25 ponemos esta fórmula con BUSCARV para obtener el número de socio a partir del nombre



El próximos paso es crear una fórmula transitoria en la celda B26 (más adelante prescindiremos de esta celda)

Búsquedas complejas en matriz de Excel

Esta fórmula nos da el número de fila en la Tabla de Actividades que corresponde al socio buscado.


Ahora crearemos una serie de fórmulas en pasos intermedios para luego sintetizarlas un una única fórmula (la que expusimos más arriba).


En el rango B27:F27 ponemos esta fórmula matricial (y nuevamente recordamos que las fórmulas matriciales son introducidas pulsando simultáneamente Ctrl+Mayúsculas+Enter)

Búsquedas complejas en matriz de Excel

La función HALLAR nos permite determinar qué columnas de la fila correspondiente contienen una “x”. Para determinar la fila usamos


DIRECCION(B25,2)&":"&DIRECCION(B25,6)

donde B25 nos da el número de fila del socio. Usamos INDIRECTO para que el resultado de la concatenación de ambas funciones DIRECCION sea interpretada como rango y no como simple texto.

En el rango B28:F28 ponemos esta fórmula matricial

Búsquedas complejas en matriz de Excel

Este artilugio nos permite generar un vector con los primeros cinco número enteros (también podríamos haber ingresado los números directamente, pero para nuestra única fórmula necesitaremos un vector).


Ahora vamos a combinar las fórmulas de las filas 27 y 28 en una única fórmula matricial en la fila 29

=SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5))


Búsquedas complejas en matriz de Excel

Esta fórmula nos permite prescindir de las fila 27 y 28, que pasamos a borrar (además he cambiado el socio para que no resulte una serie de números sucesivos)

Búsquedas complejas en matriz de Excel

Como puede apreciarse, esta fórmula nos da los números de columna en la tabla donde aparece una “x” en la fila del socio.


Ahora tenemos que resolver el problema de las celdas en blanco. Es decir, queremos que no haya celdas en blanco entre los números que surgen de la fórmula.

Para lograrlo tendremos que echar mano a la función K.ESIMO.MENOR, combinándola con la fórmula anterior:


=K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA($B$13:$F$13)-1),COLUMNA(1:5))

Búsquedas complejas en matriz de Excel

Ahora tenemos los valores relevantes en forma sucesiva y donde no hay valores el resultado es #NUM, lo que como veremos no representa ningún problema.


A esta altura del partido (para aquellos bravos lectores que han logrado llegar a esta parte de la nota) es obvio que podemos obtener los nombres de los deportes con la función INDICE (también ésta en forma matricial)


=INDICE(B13:F13,,K.ESIMO.MENOR(SI(ESERROR(HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),"",HALLAR("x",INDIRECTO(DIRECCION(B25,2)&":"&DIRECCION(B25,6)),1)*COLUMNA(1:5)),COLUMNA(1:5)))

Búsquedas complejas en matriz de Excel

Podemos borrar ahora todas las filas intermedias, ya que esta fórmula no las utiliza. Sólo nos queda eliminar los resultados #NUM. Lo que haremos es ocultarnos usando formato condicional

Búsquedas complejas en matriz de Excel

Con esto hemos terminado nuestra tarea, quedando sólo aplicar un poco de cosmética como quitar las líneas de división.



Búsquedas complejas en matriz de Excel

A pesar de toda la pirotecnia que hemos mostrado aquí, desde el punto de vista de diseño y manejo de datos se trata de un mal ejemplo.

Las fórmulas matriciales se caracterizan por ser muy “pesadas”, haciendo que el cálculo de la hoja sea muy lento cada vez que se produce un cambio. En nuestro ejemplo esta sobrecarga no tiene importancia, pero si tuviéramos una lista de de varios miles de socios, el trabajo con este modelo sería un suplicio.


Los mismos datos pueden ser organizados, por ejemplo, en una única tabla (lista, en términos de Excel clásico) y con un simple Autofiltro puede obtenerse todos los cortes neesarios.

Búsquedas complejas en matriz de Excel

Podemos ir más lejos y separar los datos en dos tablas, socios y deportes, y generar el reporte con el MS Query.

Búsquedas complejas en matriz de Excel




Technorati Tags:

9 comentarios:

  1. Muy interesante, como siempre, este artículo.
    Pero creo que viene a corroborar lo poco amigable que es Excel para hacer fórmulas con tablas.

    Como comparativa, el mismo resultado hecho con Google Docs&Spreadsheets sería tan simple como:

    =FILTER(cabecero;FILTER(deportes;numSocio=VLOOKUP(nombre;socios;2;FALSE))="x")

    donde la parte de VLOOKUP es la misma de BUSCARV, en inglés, y todo el resto se hace simplemente anidando 2 FILTER, desde luego mucho más simples y fáciles de usar.

    socios es la 1ª tabla del ejemplo
    deportes es la 2ª tabla del ejemplo
    cabecero es la 1ª fila de deportes
    numSocio es la 1ª columna de deportes
    nombre es la celda donde se rellena el nombre a buscar.

    Desde luego Excel tiene muchas otras cosas que no tiene Google, pero creo que esto de las fórmulas matriciales le debería dar un repasito algún pensador de Micrsoft.

    Un saludo. Javier

    ResponderBorrar
  2. Hola Jorge, te comento que estas en lo cierto con respecto al origen del ejercicio, al menos aca por San Luis lo estan dando en varios cursos de Excel avanzado que dictar diversas instituciones.
    Muy bueno tu blog, te envio un saludo grande.
    Victor

    ResponderBorrar
  3. Hola Jorge,

    Acabo de descubrir tu página, que me ha resuelto una duda respecto a menus desplegables, y solo quería agradecerte el esfuerzo y dedicación al compartir tus conocimientos con el resto de los mortales, así como felcitarte por tu página. La he colocado directamente en favoritos y creo que me será de mucha utilidad.

    Muchas gracias.
    Diego

    ResponderBorrar
  4. como lo puedo desarrollar en excel 2007 ????????? ayudame, es diferente, por q no se como pongo eses fotmulas.... eduer_159@hotmail.com ayudame porfa

    ResponderBorrar
  5. No, no es diferente. Te sugiero que copies el ejercicio y las fórmulas y luega lo vayas modificando de acuerdo a tus necesidades.

    ResponderBorrar
  6. PERO, EN LO DE LOS VECTORES COMO PONGO?? SI AL PONER =COLUMNA(1:5) ME VAN ES A QUEDAR LAS 5 CELDAS CON EL NUMERO 1 AYUDAME

    ResponderBorrar
  7. En la primera parte de la nota hay un enlace a la nota que explica qué son las fórmulas matriciales. Te sugiero que la leas y, por favor, no escribas todo en mayúsculas (es como si estuvieras gritando).

    ResponderBorrar
  8. Respuestas
    1. Hola Luis, el ejercicio está resuelto con la fórmula que aparece en la primer parte del post. El resto es la explicación de como construir la fórmula.

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.