viernes, 4 de junio de 2010

La combinación de COINCIDIR, INDICE e INDIRECTO.

Nuevamente aplicaremos estas tres funciones, que por otro lado suelen ir siempre de la mano cuando hablamos de mostrar datos de base de datos. En esta ocasión trabajaremos sobre una base de datos de diferentes 'Tiendas', donde se cruzan 'Productos' en los diferentes 'Meses' del año; se trata de localizar algún valor que cruce con estas tras variables.
La estructura de la base de datos que nos encontramos es un poco peculiar, por lo que deberemos emplear, además de estas tres funciones COINCIDIR, INDICE e INDIRECTO, la función DIRECCION, celdas validadas y nombres a diferentes rangos de celdas. Todas son funciones o herramientas ya vistas en distintos momentos en este blog... pero de forma especial vamos a combinarlas.

Aunque el ejercicio planteado por un lector del blog es algo largo, por simplificar, supondremos que tenemos datos de 3 'Tiendas' y sólo 5 'Productos'.
Veamos cual es nuestra base de datos (VER FICHERO ADJUNTO):


haz click en la imagen


Los nombres asignados a los diferentes rangos han sido:
Eco =Datos!$AB$3:$AM$14
Metro =Datos!$D$3:$O$14
Wong =Datos!$P$3:$AA$14

meses =Hoja2!$C$3:$C$14
producto=Hoja2!$D$3:$D$7
Tiendas =Hoja2!$B$3:$B$5

Vemos como se ha definido un nombre para cada rango de celdas con datos de una 'Tienda' (Metro, Wong y Eco).
Lo que tenemos que calcular es, teniendo como datos el nombre de la tienda y el mes, la cantidad total vendida durante ese mes de todos los productos.

Como queremos algo dinámico crearemos un par de celdas validadas con los Meses (A2) y Tiendas (A3):



Sobre estas celdas generaremos nuestra fórmula que nos devolverá el dato requerido.
La primera parte de la fórmula será para indicarle a la función en que rango de meses (de qué tienda) ha de buscar; para ello construimos con la función DIRECCION un rango adaptable:
="Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11)
observemos cómo se ha empleado el valor de la celda validad A3, convenientemente convertida con la función INDIRECTO, para determinar el comienzo y fin del rango, combinando este rango con la función COLUMNA(ref) que nos devuelve el número de columna de la referencia indicada (si es un rango de celdas nos muestra la columna inicial); combinando todas las funciones obtenemos
'Datos!$D$3:$O$3' para la tienda Metro, o
'Datos!$P$3:$AA$3' para la tienda Wong, o
'Datos!$AB$3:$AM$3' para Eco.
Y todo de forma automática asociado a la celda validada A3.
Estos valores lo convertimos en algo entendible para Excel aplicándole nuevamente la función INDIRECTO:
INDIRECTO("Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11))

¿Y por qué tantas funciones anidadas?...por que necesitamos conocer en qué posición se encuentra el mes seleccionado con la otra de las celdas validada A2.
Esto lo conseguimos con la función COINCIDIR:
=COINCIDIR(A2;fórmula anterior;0)
ya sabemos qué número de columna del rango de la tienda seleccionada tiene el dato buscado.

Por último, seleccionaremos, con la función INDICE, el valor requerido; que se encontrará asociada indiscutiblemente al 'mes' y 'tienda' (en nuestro ejemplo, se encuentra en la fila 12):
=INDICE(INDIRECTO(A3);12;COINCIDIR(A2;INDIRECTO("Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11));0))

Adjunto fichero para mejor análisis.

INDIRECTO INDICE COINCIDIR
INDIRECTO INDICE C...
Hosted by eSnips

13 comentarios:

  1. CUAL ES EL PROCEDIMIENTO PARA BAJAR LOS FICHEROS

    ResponderEliminar
  2. Hola, buenos días...
    para descargarse los ficheros adjuntos sólo haz click en el enlace habilitato, se abrirá la página concreta de snips.com donde podrás bien visualizarlo o bien presionar la opción download..
    Un saludo

    ResponderEliminar
  3. como se haria tenien como datos el mes y la cantidad para conocer el nombre de la tienda.
    desde ya muchas gracias .
    interente paguina y de gran ayuda.

    ResponderEliminar
  4. Gracias a ti por el comentario.
    disculpa la demora en la respuesta, pero me ha sido imposible contestar hasta ahora por motivos personales.
    Tu cuestión es dificil de contestar tal cual la planteas, ya que no se si tu problema tiene la misma estructura de datos que el ejemplo de este post, si las cantidades son valores únicos o repetidos... todas ellas son variables importantes para darte una respuesta concreta.
    Sin embargo puedes ver un ejemplo similar al que expones en ver ejemplo.
    Si no te sirve ninguno de los dos, envíame tu cuestión a
    excelforo@gmail.com

    Un saludo

    ResponderEliminar
  5. No se visualizan bien los ficheros adjuntos......y para bajarlos
    tenemos que pagar

    ResponderEliminar
    Respuestas
    1. Hola,
      lamentablemente el sitio de almacenamiento cambió sus políticas y ahora parece que requiere un código; cuando me decidí hace tres años por este espacio no era así, siendo totalmente gratuíto.
      Cualquier pago realizado en este soporte va 100% integro para ellos, yo no recibo nada.
      En adelante, cualquier fichero que suba al blog, lo haré a otro lugar gratuito.
      Siento las molestias.

      Eliminar
  6. entonces coloca este archivo en el otro servidor gratuito para descargarlo

    ResponderEliminar
    Respuestas
    1. Hola Luis Enrique,
      gracias por el interés... mientras tanto, si necesitas el fichero envíame un correo a
      excelforo@gmail.com
      Un cordial saludo

      Eliminar
  7. Ismael muchísimas gracias por tu ayuda, super útil la información.
    Me ahorraste unas buenas horas de investigación.
    100% recomendado.
    Saludos!.-

    ResponderEliminar
  8. Ismael buenos días y muchas gracias por sus aportes. Agradezco si me puede colaborar con lo siguiente: tengo una tabla de datos únicos; necesito primero realizar una búsqueda de un dato único de la tabla y que me de la fila y la columna donde se encuentra, ya que a las filas y columnas les asignaré un valor que multiplicarán en un fórmula. He encontrado que dando la fila y columna halla el valor; necesito lo contrario. Espero haberme hecho entender.

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.