Control de asistencia de personal en Excel gratis

Llevas Excel abierto, delante, y quieres montar un control de asistencia de personal gratis, sin comprar un programa ni pagar una suscripción. En este artículo construyes, paso a paso, una hoja de asistencia para cuatro personas y cinco fechas con una lista desplegable, tres colores automáticos, los totales por persona y un porcentaje de asistencia. Todo con herramientas que ya trae Excel: Validación de datos, Formato condicional, CONTAR.SI y formato de porcentaje. Funciona en Microsoft 365, Excel 2024, 2021, 2019 y 2016, y también en Excel para la web y en Mac; al final, guardas el libro como .xlsx para compartirlo.

Qué es una hoja de control de asistencia

Una hoja de control de asistencia es una tabla donde, para cada persona, anotas si vino, faltó o llegó tarde en cada día del periodo. Aquí vamos a usar un libro con una sola hoja, llamada "Asistencia", y los datos se organizan así: nombres de las personas en la columna A, fechas en la fila 1 y, en cada celda de cruce, un código de estado. Los tres estados son P (Presente), Ausente y T (Tarde). Más adelante, cada estado tendrá un color de relleno distinto para verlo de un vistazo.

La idea es que la hoja se lea sola: con un golpe de vista ves quién faltó y quién llegó tarde, sin tener que repasar texto. Por eso, antes de empezar, conviene tener claros los tres códigos que vas a usar y mantenerlos siempre iguales en todas las hojas que crees.

Cómo preparar las filas y los estados de asistencia

Empieza por colocar los datos desde A1, sin dejar filas ni columnas en blanco entre medias. En la columna A escribes los nombres de las personas, uno por fila; en la fila 1, las fechas como encabezado, cada una en su columna. En las celdas de cruce escribes los estados, siempre con la misma grafía: P, Ausente o T (sin acentos, con la primera letra en mayúscula y el resto en minúscula). Esta coherencia es la que luego permitirá que la Validación de datos y el Formato condicional reconozcan los valores sin equivocarse.

Una lista desplegable para registrar cada estado

Ahora conviertes cada celda de cruce en una celda con lista desplegable, para que nadie escriba "Presente" con la palabra entera, ni "t" minúscula, ni un valor inventado. Seleccionas el rango donde se anotan los estados (aquí, B2:F5) y abres Validación de datos.

Verás un cuadro con tres pestañas. En la primera, "Configuración", eliges qué se permite escribir en cada celda del rango.

Al aceptar, cada celda de B2:F5 muestra una flecha a la derecha. Si haces clic, aparecen las tres opciones, y escribir un valor distinto será imposible: Excel mostrará un mensaje de error y no dejará salir de la celda hasta que elijas uno de los tres códigos.

Una lista de estados escrita dentro del cuadro

En el ejemplo anterior, la lista P;Ausente;T se escribió directamente en el campo "Origen" del propio cuadro, separados por punto y coma. Es la forma más rápida cuando los códigos son pocos y no van a cambiar. Si más adelante añades un cuarto estado, por ejemplo "V" de Vacaciones, vuelves a abrir Validación de datos sobre el mismo rango, editas el Origen y separas con punto y coma: P;Ausente;T;V.

Una lista de estados tomada de la hoja

Otra opción es escribir los estados en celdas de la propia hoja y decirle a Validación de datos que los lea de ahí. Por ejemplo, en H2:H4 escribes P, Ausente y T. Después, en Origen escribes la referencia al rango, con dólares para que la lista no se mueva si copias la validación a otro lado.

Esta forma es útil si los estados van a cambiar con el tiempo: si añades un valor en H5, basta con ampliar la validación al rango =$H$2:$H$5 sin tener que recordar la lista exacta.

Colores que distinguen PRESENTE, AUSENTE y TARDE

Con la Validación de datos ya no se puede escribir cualquier cosa, pero la hoja sigue siendo "blanca con letras": para verla de un vistazo hay que añadir color. Eso se hace con Formato condicional, que pinta cada celda de un color según el texto que contenga.

Vas a crear tres reglas, una por código. La primera es para la P (Presente) en verde.

Repites el proceso para "Ausente" (relleno rojo) y para "T" (relleno amarillo). Las tres reglas se aplican al mismo rango B2:F5, y Excel las evalúa en orden: si una celda contiene "P", la pinta verde; si contiene "Ausente", roja; si contiene "T", amarilla. Si una celda está vacía, no se le aplica ninguna.

Una regla para cada código de asistencia

Esta es la tabla que guía las tres reglas del Formato condicional. Si tu equipo usa más estados, por ejemplo "V" de Vacaciones, añade una cuarta regla con su color y amplia también la lista de Validación de datos.

Totales de asistencia por persona

Con la hoja ya pintada, el siguiente paso es calcular, para cada persona, cuántos días tiene de cada estado. En la columna G vas a poner los totales. Primero, días presentes: cuentas cuántas celdas del rango B2:F2 contienen exactamente la letra "P".

=CONTAR.SI(B2:F2;"P")

CONTAR.SI (en inglés, COUNTIF) cuenta, dentro de un rango, las celdas que cumplen una condición. Aquí la condición es que la celda contenga "P". Copias la fórmula a G3, G4 y G5 con el controlador de relleno (el cuadradito de la esquina inferior derecha de la celda).

Días presentes y días con otro estado

Ahora añades las dos columnas siguientes, una por cada estado, y replicas la fórmula. Como la condición cambia, también cambia el segundo argumento de CONTAR.SI.

Copia cada fórmula al resto de filas. La idea es que, para cada persona, los tres totales (P + Ausente + T) sumen 5, el número de fechas de la fila 1. Si no suman 5, algo falla: o falta un código en alguna celda, o una celda tiene un valor que no es P, Ausente ni T.

Un porcentaje de asistencia calculado con el total

Con los días presentes ya calculados, el porcentaje de asistencia se obtiene dividiendo entre los días laborables del periodo. Aquí el periodo es una semana, con 5 días laborables. En la celda K1 escribes "Días laborables" y en L1 el valor 5. Después, en J2 calculas el porcentaje para la primera persona.

=G2/$L$1

El dólar en $L$1 es importante: al copiar la fórmula a las filas de abajo, la división siempre apuntará a la misma celda de control. Sin el dólar, la referencia bajaría a L2, L3, etc., y daría error. Aplica formato de porcentaje a la columna J (Ctrl+1 > Número > Porcentaje) para que el resultado se vea como 80,00 % en vez de 0,8.

Cálculo manual y cálculo automático

Una manera de entender la fórmula anterior es comparar el cálculo manual con el automático. Si una persona tiene 4 días presentes de 5 laborables, a mano escribes 4/5 y obtienes 0,8, que con formato de porcentaje se ve 80,00 %. La fórmula =G2/$L$1 hace exactamente eso: toma el 4 de la celda G2 y lo divide entre el 5 de la celda L1. La ventaja es que, al cambiar los estados de la semana, la fórmula se actualiza sola; a mano tendrías que recalcular cada porcentaje.

=10/12

Esa fórmula en una celda cualquiera da 0,8333…, que con formato de porcentaje se ve 83,33 %. Es el mismo razonamiento: días presentes partido por días totales, y Excel se encarga del resto.

Una hoja lista para guardar y compartir

Antes de cerrar el libro, repasa cuatro cosas. Primero, que la Validación de datos siga en su sitio: cambia un valor de la lista y comprueba que la flecha sigue saliendo. Segundo, que el Formato condicional pinte los tres colores: prueba a poner una "T" en una celda y mira que se vuelve amarilla. Tercero, que los totales de cada fila sumen exactamente 5. Cuarto, que el porcentaje se vea como 80,00 %, no como 0,8. Cuando todo esté bien, guarda el libro.

El formato .xlsx es el libro de Excel habitual: lo abren todas las versiones desde Excel 2007, Excel para la web, Excel para Mac y la app de móvil. Si el libro llevara macros, la extensión sería .xlsm; aquí no hace falta, porque todo se ha hecho con herramientas de la cinta.

Dudas al montar una hoja de asistencia en Excel