El libro contendrá dos hojas. En la hoja "Nombres" estará la lista de las 20 personas seleccionables. Además, usaremos esta hoja para realizar cálculos auxiliares.
En la hoja "Puestos" crearemos los tres equipos. Los equipos formados se mostrarán en forma gráfica utilizando cuadros de texto agrupados de cuatro en cuatro.
En el Equipo A ya se han seleccionado tres nombres (C3:C5). En la lista de C6 no se muestran los nombres utilizados. Los nombres elegidos aparecen como etiquetas en sus correspondientes cuadros de texto.
Para poner listas en las celdas de la columna C de la hoja "Puestos" vamos a crear un "nombre" con ayuda de la función DESREF; le llamaremos Lista_Nombres. Así mismo, crearemos el "nombre" Ocupados para referirnos a los datos que hayamos puesto en el rango C3:C14. Accedemos a Fórmulas + Asignar nombre y creamos los "nombres" siguientes:
| Lista_Nombres | =DESREF(Nombres!$A$2;0;0;CONTARA(Nombres!$A:$A)-1;1) |
| Ocupados | =Puestos!$C$3:$C$14 |
Seleccionamos C3:C14, vamos a Datos + Validación de datos y elegimos Lista + Lista_Nombres.
Elegimos tres nombres cualesquiera en las tres primeras celdas (por ejemplo, Gregorio, Miren y Oscar). De este modo, Gregorio ocupa el primer puesto del Equipo A; Miren, el segundo; y Oscar, el tercero. Para que estos nombres aparezcan en los bloques de nuestro esquema gráfico:
Hacemos clic en el primer cuadro de texto y, en la barra de fórmulas escribimos: =$C$3
Hacemos clic en el segundo cuadro de texto y, en la barra de fórmulas escribimos: =$C$4
Hacemos clic en el tercer cuadro de texto y, en la barra de fórmulas escribimos: =$C$5
Continuamos escribiendo estas fórmulas en los restantes cuadros de texto y las ponemos en Arial + Negrita + 10 ppp + Rojo. Naturalmente, como aún no hemos elegido ningún nombre en las celdas del rango C6:C14, los cuadros correspondientes no contendrán nada.
Del mismo modo, los bloques ovalados los rellenamos con los datos de la columna B en Arial + Negrita + 10 ppp + Azul.
Aquellos nombres que ya hayan sido usados deberán ser marcados como ocupados; el resto estarán disponibles. Lo haremos en las columnas B y C de la hoja "Nombres".
En B2:
=SI(A2="";"";SI(CONTAR.SI(Ocupados;A2)>=1;"Ocupado";"Disponible"))
En C2:
=SI(B2="Disponible";A2;"")
Extendemos las fórmulas hasta las fila 40. De esta forma dejamos la hoja preparada para añadir nuevos nombres en el futuro.
Vamos a ordenar alfabéticamente los nombres de la columna C. Para ello, primero determinaremos, en la columna D, el número de orden de cada nombre disponible y, luego, pondremos la lista ordenada en la columna E. Pero antes, para facilitar el trabajo, creamos otro "nombre":
| Disponibles | =DESREF(Nombres!$C$1;1;0;CONTARA(Nombres!$A:$A)-1;1) |
En D2:
=SI(C2="";"";CONTAR.SI(Disponibles;"<="&C2)-CONTAR.BLANCO(Disponibles))
Extendemos la fórmula hasta la fila 40 y creamos otro "nombre" para esta nueva columna:
| Orden | =DESREF(Nombres!$D$1;1;0;CONTARA(Nombres!$A:$A)-1;1) |
En E2:
=SI.ERROR(INDICE(Lista_Nombres;COINCIDIR(FILA()-1;Orden;0));-1)
Extendemos la fórmula hasta la fila 40 y creamos el último "nombre":
| Lista_Disponibles | =DESREF(Nombres!$E$1;1;0;CONTARA(Nombres!$E:$E)-1-CONTAR(Nombres!$E:$E);1) |
La hoja "Nombres" habrá quedado así:
La columna E contiene la lista de los nombres que aún no han sido utilizados. Ésta es la lista que debe aparecer en las celdas de la columna C de la hoja "Puestos". Por tanto, hay que hacer este cambio.
En la hoja "Puestos", seleccionamos el rango C3:C14, vamos a Datos + Validación de datos y cambiamos Lista_Nombres por Lista_Disponibles.
Para terminar, probamos el ejercicio y añadimos nuevos nombres a la columna A de la hoja "Nombres". Estos nuevos nombres se incorporarán automáticamente a las listas de C3:C14.
Descargar archivo (SE15-Puestos.xlsx)
No hay comentarios:
Publicar un comentario