Ligando celdas a objetos en Excel – Uso de Validación de Datos, INDICE, COINDICIR, y otras funciones Excel un tanto exóticas

martes, junio 20, 2006

Ayer hablaba de la "cámara fotográfica" de Excel. Vimos que esta herramienta permite tomar "instantáneas" de porciones de datos de una hoja Excel, y que esta "foto" es dinámica.
Lo que hace esta herramienta es ligar un rango de celdas al objeto (la imagen), como se puede ver aquí


De manera similar, se puede ligar (referenciar) una celda a un objeto en la hoja de cálculo. Por ejemplo, a un rectángulo. Todo lo que tenemos que hacer es insertar el objeto en la hoja y, estando este seleccionado, tipear la referencia en la barra de fórmulas. Utilizando esta técnica podemos armar un modelo como este


Este modelo nos permite distribuir operarios en distintas máquinas, representando la distribución en forma gráfica. Usando solamente fórmulas y validación de datos, este modelo nos permite controlar que no ubiquemos un mismo operario más de una vez. A medida que vamos ubicando a los operarios, sólo los disponibles aparecen en la lista despegable. Como es costumbre en este blog, el archivo está a vuestra disposición.Paso a explicar cómo funciona el modelo. Cada uno de los rectángulos que representan a los operarios, está ligado a la celda correspondiente en el rango C4:C15 (cuyo nombre es "ocupados").



Este rango contiene validación de datos que genera una lista desplegable que está contenida en el rango E4:E19 de la hoja "operarios", al que dimos el nombre de "lista_operarios". Este es un rango dinámico definido por esta fórmula:

lista_operarios =DESREF(operarios!$E$4,0,0,CONTARA(operarios!$E$4:$E$19)-CONTAR.BLANCO(operarios!$E$4:$E$19),1)

Como ven, este rango contiene sólo los nombres de los operarios disponibles (aquellos que todavía no han sido ubicados en alguna máquina).
Para lograr este efecto, hemos construido algunas columnas auxiliares en la hoja "operarios":

Columna A, Operarios: contiene la lista de todos los operarios (a ver si alguien adivina en quienes está inspirada la lista).

Columna B, Ocupado: muestra quienes están ocupados y quienes disponibles usando esta fórmula

=SI(CONTAR.SI(ocupados,A4)>=1,"ocupado","disponible")
El nombre "ocupados" se refiere al rango =Hoja1!$C$4:$C$15

Columna C, Disponibles: sólo aparecen los nombres de los disponibles.

Columna D, No. De orden: muestra el número de fila del operario disponible, o blanco si no lo está. Aquí utilizamos la fórmula

=SI(CELDA("contenido",C4)="","",FILA(C4))

La función CELDA analiza, en este caso, el contenido de la celda. Aquí la usamos en una proposición lógica para saber si la formula de C4, por ejemplo, da como resultado BLANCO o no. No podemos utilizar la función ESBLANCO, ya que todas las celda en el rango C4:C15 contienen una fórmula y por lo tanto ESBLANCO daría FALSO en todos los casos.

Columna E, lista de operarios: para mostrar sólo los nombres de los disponibles en forma dinámica, usamos la siguiente fórmula:

=SI(ESERROR(INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0))),"",INDICE(disponibles,COINCIDIR(K.ESIMO.MAYOR($D$4:$D$19,FILA()-3),$D$4:$D$19,0)))

La primer parte (ESERROR…..) cumple la única función de evitar resultados #NUM cuando la celda de referencia no contiene un valor numérico (como en D10, por ejemplo).


Categorías: Funciones&Formulas_, Varios_

7 comments:

Edwin Arnaldo 03 enero, 2009 15:59  

Excelente este tip, me gustaria en lo que se pueda me ayude a conseguir el archivo, lastiosamente el link del archivo no funciona, agradeceria me avisara cuando lo envie...

Nuevamente gracias y bendiciones

Jorge L. Dunkelman 04 enero, 2009 22:17  

Hola
el archivo en cuestión quedó atrapado en el sitio que cerró (y que no tuve la precaución de guardar una copia en mi máquina). En cuanto tengo el tiempo de rehacerlo estaré corregiendo el enlace.

Paredux 04 octubre, 2009 11:46  

Hola,

por casualidad he encontrado este blog y he visto esta función que me serviría muchísmo para un proyecto que quiero hacer.

Se que ha pasado mucho tiempo pero podría ser posible que ya tuvieses el archivo disponible y el enviarmelo via email a paredux@gmail.com?

Muchas gracias.

Jorge L. Dunkelman 04 octubre, 2009 18:46  

Fijate en mi comentario del 4 de enero. Lo lamento! Espero poder reemplazar el enlace en las próximas semanas.

Anónimo,  29 mayo, 2011 03:53  

Hola Jorge
un placer saludarte
Se que ha pasado mucho tiempo pero podría ser posible que ya tuvieses el archivo disponible y si puedes enviarmelo via email a winny0104@mail.com

Muchas gracias.

Jorge L. Dunkelman 30 mayo, 2011 06:19  

Tal como puse en el comentario anterior, no guardé una copia del archivo. Sigue en pie la promesa de rehacerlo.

Anónimo,  18 abril, 2013 23:40  

Me sirvio muchismo tu ejemplo, 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