Control de asistencia

Vamos a hacer una hoja de control de asistencia para un curso de formación que se desarrollará los jueves (3 horas) y los viernes (2 horas) desde el 6 de septiembre de 2011 hasta el 30 de mayo de 2012. La aplicación deberá ser lo más versátil posible adaptándose automáticamente a cualquier circunstancia para que se pueda usar en cursos venideros.

El libro tendrá 7 hojas. La hoja "Datos" contendrá información básica: inicio y fin de cada una de las tres fases del curso, número de horas que se impartirá cada día, días festivos y resumen de las horas impartidas.

La hoja "Primera" tendrá una lista de participantes en el curso y espacio para poder anotar las asistencias los días que haya cursillo.

Las hojas "Segunda" y "Tercera" son similares a la hoja "Primera" pero las fechas de las columnas corresponden a cada una de las siguientes fases del curso. La aplicación deberá crear automáticamente las hojas "Primera", "Segunda" y "Tercera", poniendo las fechas de los encabezados y los bordes de las celdas. Además, habrá otras tres hojas auxiliares: "AuxP", "AuxS" y "AuxT".

En las celdas F7:L7 de la hoja "Datos" ponemos las horas que se van a impartir cada día. En el ejemplo que vamos a desarrollar habrá clase únicamente los jueves (3 horas) y los viernes (2 horas).

Si un día no hay clase, en la celda inferior colocaremos un cero; en caso contrario, un uno. Esto nos servirá para hacer cálculos más adelante.

En F8:
=SI(F7>0;1;0)    [Extender la fórmula hasta la celda L8]

Ahora, debemos trabajar con la hoja "AuxP" que nos servirá para obtener los datos con los que construiremos la tabla de la hoja "Primera". Cuando la terminemos, su aspecto será:

En la fila 2 pondremos todas las fechas comprendidas entre el inicio del curso y una fecha posterior a la terminación de la primera parte. Dejaremos un margen suficiente pensando en futuros cursos.

En la fila 3 pondremos cero los días que no haya cursillo y dejaremos los días cuando haya cursillo. Para que se vea mejor, sustituiremos el formato de fecha por el formato general.

En la fila 4 dejaremos en blanco las celdas con ceros y mantendremos las fechas de las restantes. Aquí usaremos el formato de fecha.

En la fila 5 numeraremos las fechas de la fila 4.

En la fila 6 colocaremos todas las fechas de la fila 4 sin dejar celdas en blanco ayudándonos de la numeración de la fila 5.

Comencemos a poner las fórmulas de la hoja "AuxP".

En C2:

=Datos!D5

Hemos puesto en C2 la fecha de comienzo del curso.

En D2:

=C2+1    [Extender la fórmula hasta la celda DM2]

Esto nos garantiza que la primera fase del curso quede dentro de este rango de fechas.

En C3:

=C2*ELEGIR(DIASEM(C2;2);Datos!$F$8;Datos!$G$8;Datos!$H$8;Datos!$I$8;Datos!$J$8;Datos!$K$8;Datos!$L$8)   [Extender la fórmula hasta la celda DM3]

Puesto que en la próxima fila nos referiremos a los días festivos de la hoja "Datos", vamos a crear una "nombre" que haga referencia a esta lista. Será un "nombre" dinámico ya que la lista de días festivos cambia cada año y pretendemos hacer una hoja que sirva para otras ocasiones.

Accedemos a Fórmulas + Asignar nombre y creamos el "nombre" Festivos con la siguiente definición:

Festivos

=DESREF(Datos!$C$15;1;0;CONTARA(Datos!$C$16:$C$200);1)

En C4:

=SI(C3=0;"";SI(ESERROR(COINCIDIR(C3;Festivos;0));C3;""))   [Extender la fórmula hasta la celda DM4]

En C5:

=SI(C4="";"";CONTAR($C$4:C4))   [Extender la fórmula hasta la celda DM5]

En este momento crearemos otros dos "nombres": DisponiblesP (que hará referencia a la fila 4) y OrdenP (que abarcará los datos de la fila 5).

DisponiblesP	=DESREF(AuxP!$C$4;0;0;1;CONTARA(AuxP!$4:$4)-1)
OrdenP	=DESREF(AuxP!$C$5;0;0;1;CONTARA(AuxP!$5:$5)-1)

En C6:

=INDICE(DisponiblesP;COINCIDIR(COLUMNA()-2;OrdenP))   [Extender la fórmula hasta la celda DM6]

Si nos fijamos bien, comprobaremos que en la fila 6 están contenidos los jueves y viernes (sólo los no festivos) de la primera fase del cursillo. En realidad, la lista se extiende unos cuantos días más, ya que hemos tenido la precaución de prever futuros cursos. Cuando llegamos al 23 de diciembre ya no hay más jueves o viernes no festivos en la fila 1, por lo que se repite esta fecha (el 29 de diciembre es el último día, pero es festivo).

Este es el momento de crear la tabla de asistencia de la primera fase del curso.

En la celda D4 de la hoja "Primera" ponemos formato personalizado ddd y escribimos:

=AuxP!C6   [Resultado: jue]

En la celda E4 de la hoja "Primera" volvemos a poner el formato personalizado ddd y escribimos:

=SI(O(AuxP!D6=AuxP!C6;AuxP!D6>Datos!$D$6);"";AuxP!D6)   [Resultado: vie]

Extendemos la fórmula de la celda E4 hasta DL4.

En la celda D2 ponemos formato personalizado dd y escribimos:

=D4   [Resultado: 08]

En la celda D3 ponemos formato personalizado mmm y escribimos:

=D4   [Resultado: sep]

Extendemos ambas fórmulas hasta la columna DL.

En D26:

=SI.ERROR(ELEGIR(DIASEM(D4;2);Datos!$F$7;Datos!$G$7;Datos!$H$7;Datos!$I$7;Datos!$J$7;Datos!$K$7;Datos!$L$7);0)    [Extender la fórmula hasta la celda DL26]

Seleccionamos D26:DL26 y ponemos el formato personalizado: #;;

Creamos una celda combinada con B26:C26, ponemos el formato personalizado _)_)"HORAS " *. #.##0_)_)_) y escribimos la fórmula:

=SUMA(D26:DL26)   [Resultado: 60]

Ahora, usaremos el formato condicional para poner los bordes.

Seleccionamos D5:DL24 y accedemos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula: =D$2<>"". Pulsamos Formato y, en la pestaña Bordes, elegimos Contorno.

El formato debe ser igual en D26:DL26. En las filas 2, 3 y 4 podemos variar ligeramente el formato para eliminar la línea de separación entre las filas 2 y 3. Si ponemos una línea de puntos entre la 3 y la 4 se leerá mejor la fecha.

El último paso en esta hoja será dejarla preparada para que se imprima correctamente. Establecemos el área de impresión accediendo a Diseño de página y haciendo clic en el vértice inferior derecho del grupo.

Se mostrará el cuadro de diálogo Configurar página. En la pestaña Página, seleccionamos orientación Horizontal. En Márgenes, pondremos 1 cm. en todos excepto en el superior, que pondremos 2 cm. En la pestaña Hoja pondremos B2:AA26 en el apartado Área de impresión; en el apartado Repetir columnas a la izquierda pondremos $B:$C. Con el último dato le indicamos a Excel que si la impresión ocupa más de una hoja, en todas ellas se muestren los nombres de los alumnos.

Pulsando Vista preliminar veremos cómo quedaría la hoja si la imprimiéramos, pero todavía no vamos a hacerlo. 

Si en F7:L7 de la hoja "Datos" cambiamos los días en los que se imparte el curso, tendremos que cambiar a mano el Área de impresión. Pero lo que pretendemos es que se ajuste automáticamente sin intervención del usuario. Veamos cómo podemos conseguirlo.

Vamos a Fórmulas + Administrador de nombres para obtener la lista de "nombres" que hemos creado. Veremos que Excel ha añadido dos nuevos nombres: Área_de_impresión y Títulos_a_imprimir. En la columna Ámbito, nos indica que estos nombres sólo están vigentes en la hoja "Primera".

Conseguiremos que el Área de impresión se ajuste automáticamente sustituyendo el valor del "nombre" Área_de_impresión por una fórmula dinámica. Para ello, seleccionamos Área_de_impresión, pulsamos el botón Editar y sustituimos su valor por el siguiente:

Área_de_impresión

=DESREF(Primera!$B$2;0;0;25;115-CONTAR.SI(Primera!$26:$26;"=0"))

Ya podemos imprimir la hoja.

Las hojas "Segunda" y "Tercera" se construyen de la misma manera.

El último paso consistirá en poner las horas de las fases en el rango D10:D13 de la hoja "Datos":

En D10:

=Primera!B26

En D11:

=Segunda!B26

En D12:

=Tercera!B26

En D13:

=SUMA(D10:D12)

Descargar archivo (SZ002-Control_asistencia.xlsx)

Descargar archivo (SZ002-Control_asistencia.xls)