Encontrar el encabezamiento en una matriz con Excel

martes, septiembre 08, 2009

Supongamos esta tabla de datos, que muestra la ubicación de ciertos agentes en ciertas zonas por día de la semana


matriz en Excel

Para encontrar qué agente estará en qué zona en determinada fecha podemos usar una fórmula como ésta

=INDICE(agentes,COINCIDIR(C12,fechas,0),COINCIDIR(C13,zonas,0))


dónde usamos los nombres

agentes =indice!$C$3:$F$9


fechas =indice!$B$3:$B$9


zonas =indice!$C$2:$F$2


Es decir, dados los valores de la fecha y la zona en la matriz, podemos encontrar el agente.
Pero la pregunta es, ¿cómo encontramos la zona sabiendo la fecha y el agente? Es decir, ¿en qué zona se encontrará Roberto el 04/09/2009? Es decir, tenemos que hacer una búsqueda “hacia arriba”.


Empezamos por agregar dos nuevos nombres para hacer más legible nuestra fórmula


tabla =zona!$C$3:$F$10
agente =zona!$C$4:$C$10

En la celda C13 ponemos la fecha, en la celda C14 el nombre del agente y en la celda C15 esta fórmula:

=DESREF(tabla,0, COINCIDIR(C14, DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla)),0)-1,1,1)

matriz en Excel

Para explicar esta fórmula veamos cuál es la función de cada uno de sus componentes.

Empezamos con


=DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla))


Si ponemos esta fórmula en un rango de una fila por cuatro columnas obtenemos los nombres que corresponden a la fecha en la matriz (nótese que entrado la fórmula como matricial)

matriz en Excel

La formula anterior es uno de los argumentos de


=COINCIDIR(C14, DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla)),0)


Si pegamos esta fórmula en una celda veremos que el resultado es 4

matriz en Excel

ya que el valor de C14 es “Mario” y este valor es el cuarto en el vector que hemos obtenido con la fórmula anterior.


Si reducimos ahora la fórmula en C15 a =DESREF(tabla,0,4) vemos que el “ancla” de DESREF la celda C3, con 0 filas des desvío y 4 columnas a la derecha.




Technorati Tags:

7 comments:

Felipe Padilla,  09 septiembre, 2009 10:32  

Muchas gracias Jorge por tu explicación.
Esperaba tu respuesta tarde o temprano.

Un fuerte saludo!!!

Felipe Padilla,  09 septiembre, 2009 10:57  

Despues de mirarlo detenidamente, me doy cuenta de que no es la solución que te solicite por correo electronico.
La solución para mi necesidad es la que puedes encontrar aqui: http://www.ayudaexcel.com/foro/temas-resueltos/6485-solucionado-obtener-2-datos-al-proporcionar-un-parametro.html

Pero tiene un problema, y es que da error si el agente se duplica en la misma fecha o en la misma localidad.

Saludos.

Jorge L. Dunkelman 09 septiembre, 2009 18:52  

Felipe, la nota está inspirada en tu consulta pero no es la respuesta. Creo que tampoco la nota del enlace la resuelve ya que lo que estás buscando es una forma de exponer todas las fechas y localidades de cada agente. Para esa tarea estoy preparando otra nota.
Gracias por el enlace que le será útil a más de un lector.

Jorge L. Dunkelman 09 septiembre, 2009 19:22  

Después de una segunda lectura de la solución de ayudaexcel.com, veo que si dá respuesta a tu consulta. Existen algunos problemas cuando un agente se repite como has señalado, pero supongo que se puede corregir la fórmula.

Anónimo,  08 octubre, 2009 11:41  

Hola Jorge:
A partir de lo que explicas en esta nota, me acordé que hace poco no he podido resolver un problema que esta nota me ha recordado. Tal vez lo tengas resuelto en alguna otra nota pero no estoy seguro. Un día un amigo me planteó lo siguiente:
Tenemos una matriz de 2 dimensiones (por ejemplo 10 filas y 10 columnas) con números que en principio no se repiten. Este rango tiene a su vez un rótulo por la izquierda y otro por arriba. Tengo que obtener 2 cosas:

1) Mostrar en una columna los 10 más altos. No hay problema ya que lo resuelvo con K.ESIMO.MAYOR
2) Mostrar en otra(s) columna(s) paralela(s) a quién corresponde ese número, es decir, obtener tanto el rótulo por la izquierda como el de arriba. Aquí es donde tengo el problema.

La diferencia con tu ejemplo es que tú partes de saber la fecha, es decir, uno de los rótulos y en mi caso no es así.
Debe haber una solución pero no se me ocurre ninguna. Si hace falta alguna aclaración, te mandaría el libro o cuaderno (qué lío con la traducción) donde tengo el ejemplo.

Un saludo y gracias de antemano,
Sergio

Jorge L. Dunkelman 08 octubre, 2009 16:29  

Hola Sergio
hay una nota en el blog donde muestro una función definida por el usuario (UDF) para obtener la dirección de un valor en una matriz.
Podemos usar esta fórmula para obtener la dirección de la celda donde se encuentra el valor. Luego podemos usar una fórmula con INDIRECTO, DIRECCION y ENCONTRAR para "armar" la referencia a las celdas que contienen el rótulo de fila y de columna.
Posiblemente elabore un poco más la solución y la publique. Te agradecería que me mandes el cuaderno con el ejemplo.

Anónimo,  27 marzo, 2014 20:26  

Vaya Crack Jorge.

Me ha servido enormemente este artículo, muchas gracias.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP