sábado, 14 de enero de 2017

Buscar en dos direcciones combinando las funciones Coincidir, Dirección e Indirecto


Descargar el ejemplo xls.

Ya había escrito una nota referente a este tema, usando las funciones Coincidir e Indice. Accidentalmente vuelvo a retomarlo por causa de una necesidad que vi en una plantilla. Se trataba de una referencia de celda que cambiaba al insertar una columna; por lo que la referencia quedaba desactualizada cada vez que se ejecutaba la mencionada acción, solo respecto de la columna, puesto que la referencia de fila era constante.

En la presente nota, se tiene una base datos con las ventas por días en siete sedes, lo que se pretende es consultar las ventas de cualquier día, en cualquiera de las siete sedes. 



Para tal fin, se implementaron dos listas validadas (opción validación de datos-pestaña Datos) con las sedes y los días respectivamente. 

Lista de sedes:















Lista de días: esto desde la hoja "ventas". También se puede hacer creando un nombre para el rango de datos. Véase la nota: asignar nombres a celdas y rangos










Pasando a la consulta que quiero ilustrar, debo mencionar que el truco consiste en usar la función dirección para establecer la referencia de celda correspondiente; para finalmente consultar el valor con la función Indirecto. Una de las ventajas que encuentro de esta forma de trabajar, es que no se hace referencia a una matriz de datos; lo que le confiere mayor flexibilidad a la búsqueda, ya que no se requiere actualizar la formulación si se ingresan nuevos datos.

Lo primero que debo hacer es usar la función coincidir para traducir cada valor de las listas sede y día en una referencia de posición. En otras palabras, lo que busco es construir una dirección en términos de fila y columna en donde se encuentra el dato consultado.

He usado las celdas B2 y D2 concatenadas como argumento de búsqueda, dado que en la hoja "ventas" los nombres de las sedes estan así: sede1, sede2, sede3, etcétera.




Ahora hago lo mismo con los días:



Ahora que ya tenemos nuestra referencia de fila y de columna, vamos a usar la función Dirección para que la presente en forma dirección de celda:


El resultado es:



La celda F27 de la hoja "ventas".

Para comprender un poco más el funcionamiento de "Dirección", veamos los argumentos:

=DIRECCION(fila; columna; [abs]; [a1]; [hoja])

fila: número de fila.
Columna: número de columna.
abs: es opcional y puede ser un número entre 1 y 4 que indica el tipo de referencia absoluta que se aplicará (1 = ref. absoluta en fila y col., 2 = ref. absoluta solo en fila, 3 = ref. absoluta solo en col. y 4 ref. relativa en fila y col.). Si no se especifica nada, dejará la referencia absoluta en fila y columna, como en nuestro caso.
a1: especifica el estilo de referencia a presentar, A1 o F1C1.
hoja: es el nombre de la hoja de donde se obtiene la referencia, si se omite, se extraerá de la hoja activa. En nuestro caso especificamos el nombre de la hoja "ventas".

Visto lo anterior, solo resta hacer nuestra consulta mediante la función Indirecto; ya había escrito una nota acerca de esta función: hacer consultas con la función indirecto.





Comprobamos que para la sede 4, el día 26; las ventas fueron de 5521 unidades.


 


No hay comentarios.:

Publicar un comentario