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

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_