lunes, 23 de abril de 2012

Búsqueda compleja en una tabla

Una editorial publica cinco revistas de otros tantos deportes: boxeo, ciclismo, fútbol, natación y tenis. Los clientes se suscriben a una o más revistas y los datos se almacenan en una tabla (B2:H10):

El objetivo del ejercicio es elegir un cliente (C12) y obtener su número de registro (G12) y la lista de revistas a las que está suscrito (C14:G14).

Como en otras ocasiones, resolveremos el problema usando celdas auxiliares (J3:N9); luego, agruparemos todas las fórmulas en una fórmula compuesta. Las celdas auxiliares ya no serán necesarias y las podremos eliminar.

Combinamos las celdas C12 y D12 dejando espacio suficiente para los nombres (Inicio + Combinar y centrar). Justificamos el texto a la izquierda.

Hacemos clic en C12, accedemos a Datos + Validación de datos y asociamos la lista de nombres (B3:B10) a la celda combinada:

Elegimos un nombre cualquiera; por ejemplo, Juan Gómez

En G12 ponemos el número de afiliado del suscriptor seleccionado:
=BUSCARV(C12;B3:C10;2;FALSO)     [Resultado: 1389]

En K3 obtendremos el número de la fila en la que está la persona elegida:
=COINCIDIR(C12;B3:B10;0)+2     [Resultado: 6]

Ahora, debemos comprobar qué celdas del rango D6:H6 contienen una "X" (son las suscripciones del afiliado). El rango D6:H6 lo formaremos con DIRECCION; así, DIRECCION(K3;4) devolverá $D$6; DIRECCION(K3;8) devolverá $H$6. Combinando ambas fórmulas de esta manera: DIRECCION(K3;4)&":"&DIRECCION(K3;8), obtendremos $D$6:$H$6. Con INDIRECTO obtenemos los valores de las celdas, y con HALLAR comprobaremos qué celdas tienen "X"; las que tengan "X" devolverán un 1; las restantes, devolverán un error.

Seleccionamos J5:N5 y escribimos:
=HALLAR("X";INDIRECTO(DIRECCION(K3;4)&":"&DIRECCION(K3;8)))     [Terminamos con Ctrl + Mayús + Intro]

En la siguiente fila numeraremos (del 1 al 5) las celdas en las que hemos encontrado una "X".

Seleccionamos J6:N6 y escribimos:
=SI(ESNUMERO(J5:N5);{1\2\3\4\5};"")     [Terminamos con Ctrl + Mayús + Intro]

Con esta fórmula matricial comprobamos si hay un número en cada celda del rango J5:N5 y, en caso de haberlo, le asignamos el valor correspondiente de la matriz {1\2\3\4\5}.

En J7:N7 obtenemos el número menor, el segundo menor, el tercero... de los números de rango J6:N6.

Seleccionamos J7:N7 y escribimos:
=K.ESIMO.MENOR(J6:N6;{1\2\3\4\5})      [Terminamos con Ctrl + Mayús + Intro]

Comparando esta lista de números con los valores del encabezado (D2:H2) obtendremos las revistas a las que se ha suscrito el cliente.

Seleccionamos J8:N8 y escribimos:
=INDICE(D2:H2;1;J7:N7)      [Terminamos con Ctrl + Mayús + Intro]

Seleccionamos J9:N9 y escribimos la primera fórmula compuesta:
=INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}))      [Terminamos con Ctrl + Mayús + Intro]

La fórmula definitiva la pondremos en C14:G14. Utilizaremos la función SI.ERROR para eliminar los valores #¡NUM! que hay en J9:N9.

Seleccionamos C14:G14 y escribimos la segunda y definitiva fórmula compuesta:
=SI.ERROR(INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}));"")      [Terminamos con Ctrl + Mayús + Intro]

Esta fórmula es definitiva; no se necesitan las columnas J a N. Podemos eliminarlas tranquilamente.

Nota: Como en Excel 2003 no existe la función SI.ERROR, podemos utilizar el formato condicional para poner a las celdas que contienen #¡NUM! el mismo color que el del fondo; de este modo, no se verá el código de error.

Si no queremos usar fórmulas matriciales y no nos importa conservar las celdas auxiliares, podemos resolver el ejercicio de la manera que se muestra a continuación:

En C12, G12 y K3 mantenemos las mismas fórmulas y elegimos a Martín Redondo.

En J5:
=SI(INDIRECTO(DIRECCION($K$3;COLUMNA(D1)))="X";D2;"")

Extendemos la fórmula hacia la derecha.

Martín Redondo se ha suscrito a las revistas de ciclismo, natación y tenis.

En J6:
=(J5<>"")*COLUMNA(D2)

Extendemos la fórmula hacia la derecha.

Hemos obtenido las columnas en las que están los nombres de las revistas de Martín Redondo.

En J7:
=JERARQUIA(J6;J6:N6;0)

Extendemos la fórmula hacia la derecha.

Hemos ordenado jerárquicamente la fila anterior.

En J8:
=INDICE($J$5:$N$5;0;COINCIDIR(COLUMNA(A1);$J$7:$N$7;0))

Extendemos la fórmula hacia la derecha.

Ya hemos conseguido los nombres de las revistas, aunque en orden invertido con respecto al original. Sólo nos falta eliminar el error #N/A. Lo haremos en C14:G14.

En C14:
=SI.ERROR(J8;"")

Extendemos la fórmula hacia la derecha.




4 comentarios:

  1. Demasiado complejo y complicado, se podría realizar mucho más sencillo, y directamente sin la necesidad de utilizar Tablas de ayuda.

    Tan sencillo como:

    El Número está claro: BuscarV
    Revistas: =SI(SUMAPRODUCTO(($B$3:$B$10=$C$12)*(D3:D10="X"));D2;"")

    Un Saludo
    Paques Vidal

    ResponderEliminar
  2. De la forma explicada por mi parte, es cierto que no salen una al lado de la otra, dejarán una celda vacia donde no haya la "X", pero es algo más sencilla, para aquellos que no tienen tantos conocimientos.
    Dicho esto, Chapeau por lo realizado y la forma de explicarlo
    Enhorabuena por el aporte

    Paques Vidal

    ResponderEliminar
  3. Le felicito por la contribución, quienes como yo aprendemos por aquellos que desinteresadamente publican sus conocimientos. Que el señor le bendiga. Éxitos.

    ResponderEliminar