¿Por qué comprar una costosa herramienta de gestión de asistencia para su startup si puede realizar un seguimiento de la asistencia del equipo en Excel? ¡Sí! Puede crear un rastreador de asistencia en Excel fácilmente. En este artículo, aprenderemos cómo hacerlo.
Paso 1: cree 12 hojas para cada mes en un libro de trabajo
Si planea realizar un seguimiento de la asistencia durante un año, deberá crear la hoja de cada mes en Excel.
Paso 2: agregue columnas para cada fecha en la hoja de cada mes.
Ahora cree una tabla que contenga los nombres de sus compañeros de equipo, una columna para los totales y 30 (o el número de días al mes) columnas con la fecha y el día de la semana como encabezados de columna.
Para obtener el nombre del día de la semana, puede buscar en el calendario o puede usar la fórmula para copiarlo en el resto de las celdas.
= TEXTO (fecha, "ddd") |
Usted puede leer sobre ello aquí.
Formatee los fines de semana y feriados oscuros y rellénelos con valores fijos como Fin de semana / Feriado como se muestra en la imagen a continuación.
Haz lo mismo con cada hoja.
Paso 3. Corrija las posibles entradas usando la validación de datos para cada celda abierta.
Ahora todos pueden insertar su asistencia en la hoja, pero pueden ingresar texto al azar. Algunos pueden escribir P por presente, o Presente, o por, etc. La uniformidad de datos es obligatoria en cualquier sistema de gestión de asistencia.
Para permitir que los usuarios solo escriban P o A para presente y ausente respectivamente, podemos usar la validación de datos.
Seleccione cualquier celda, vaya a los datos en la cinta y haga clic en validación de datos. Seleccione la lista de opciones y escriba A, P en el cuadro de texto.
Presiona OK.
Copie esta validación para todo el rango abierto de datos (rango abierto significa celda donde el usuario puede insertar valores).
Paso 3: Bloquear todas las celdas excepto donde sea necesario ingresar la asistencia.
Seleccione la fecha en una columna de fecha. Por ejemplo, seleccione 1 de enero. Ahora haga clic en el rango seleccionado y vaya al formato de celda. Ve a la protección. Desmarque la casilla de verificación bloqueada. Presiona OK. Ahora copie este rango a todos los rangos de fechas abiertos.
Esto permitirá la entrada a estas celdas solo cuando protejamos las hojas de trabajo usando el menú de protección de hojas de trabajo. Así tus fórmulas, formatos estarán intactos y los usuarios solo podrán modificar su asistencia.
Paso 4: Calcular los días actuales de los compañeros de equipo
Entonces, ¿cómo se calculan los días actuales? Bueno, todos tienen sus propias fórmulas para calcular la asistencia. Discutiré el mío aquí. Puede realizar cambios según los requisitos de su hoja de asistencia.
Cuento el número total de días presentes como el total de días en un mes, menos el número de días ausentes. Esto mantendrá bajo control los días festivos y los fines de semana. Se contarán automáticamente como días laborables.
Entonces, la fórmula de Excel para contar los días actuales será como:
= COUNT (fechas) -COUNTIF (rango_de_ asistencia, "A") |
De forma predeterminada, esto mantendrá a todos presentes durante todo el mes hasta que los haya marcado como ausentes en la hoja.
En el ejemplo, la fórmula es:
= COUNT ($ C $ 2: $ AG $ 2) -COUNTIF (C3: AG3, "A") |
Escribí esta fórmula en la celda B3 y luego la copié. Puedes ver que los 27 días se muestran como un regalo. Aunque no he llenado todas las celdas de presencia. Puede mantenerlo de esta manera si desea que estén presentes de forma predeterminada. O si desea que estén ausentes por defecto, marque todas las celdas como ausentes. Esto mantendrá solo el recuento de días presentes en el cálculo actual.
Paso 5: proteja la hoja
Ahora que hemos hecho todo en esta hoja. Protejámoslo para que nadie pueda alterar la fórmula o el formato de la hoja.
Vaya a la pestaña de revisión en la cinta. Busque el menú Proteger hoja. Haz click en eso. Se abrirá un cuadro de diálogo que le pedirá los permisos que desea otorgar a los usuarios. Marque todos los permisos que le gustaría permitir. Solo quiero que el usuario pueda completar la asistencia sin nada más. Así que lo dejaré como está.
Debe utilizar una contraseña que pueda recordar fácilmente. De lo contrario, cualquiera puede desbloquearlo y modificar el libro de asistencia.
Ahora bien, si intenta modificar las celdas de no asistencia, Excel no le permitirá hacerlo. Sin embargo, puede alterar las celdas de asistencia ya que las hemos desprotegido.
Paso 6: Realice el procedimiento anterior para todas las hojas del mes.
Haga lo mismo para cada hoja de mes. La mejor manera es copiar la misma hoja y hacer 12 hojas con ella. Desprotegerlos y realizar los cambios necesarios y luego protegerlos nuevamente.
Prepare la hoja maestra de asistencia
Aunque tenemos todas las hojas listas para usarse para completar la asistencia, no tenemos un lugar para monitorearlas todas.
A la administración le gustaría ver toda la asistencia en un solo lugar en lugar de en diferentes hojas. Necesitamos crear una hoja maestra de asistencia.
Paso 7: Prepare la tabla maestra para monitorear la asistencia en un lugar en Excel
Para eso, prepare una tabla que contenga el nombre de los compañeros de equipo como encabezados de fila y el nombre del mes como encabezados de columna. Vea la imagen a continuación.
Paso 7: Consultar la asistencia del equipo de cada hoja de mes
Para buscar asistencia en la hoja podemos tener una fórmula VLOOKUP simple pero luego tendremos que hacerlo 12 veces por cada hoja. Pero sabe que podemos tener una fórmula para buscar en varias hojas.
Use esta fórmula en la celda C3 y cópiela en el resto de las hojas.
= BUSCARV ($ A3, INDIRECTO (C $ 2 & "! $ A $ 3: $ B $ 12"), 2,0) |
Como sabemos que todas las hojas tienen una asistencia total en el rango B3: B12, usamos la función INDIRECTA para obtener valores de varias hojas. Cuando copia esta fórmula a la derecha, busca valores en las hojas de febrero.
Precaución: asegúrese de que los nombres de las hojas y los encabezados de las columnas en el maestro sean los mismos; de lo contrario, esta fórmula no funcionará.
Paso 8: Utilice la función Suma para obtener todos los días actuales del año de un compañero de equipo.
Esto es opcional. Si lo desea, puede calcular el total de días actuales de sus empleados a lo largo del año simplemente usando la fórmula de la suma.
Y eso es todo. Tenemos listo nuestro Sistema de Gestión de Asistencia Excel. Puede modificar esto según sus necesidades. Úselo para el cálculo de salario, cálculo de incentivos o cualquier otra cosa. Esta herramienta no te fallará.
Puede realizar cambios para calcular los días festivos y los fines de semana por separado en cada hoja. Luego réstelos del total de días actuales para calcular el total de días hábiles. También puede incluir L para licencia en el menú desplegable para marcar la licencia de los empleados.
Así que sí, chicos, así es como pueden crear un excelente sistema de gestión de asistencia para su inicio. Es barato y muy flexible. Espero que este tutorial le ayude a crear su propio libro de trabajo de asistencia en Excel. Si tiene alguna pregunta, hágamelo saber en la sección de comentarios a continuación.
Búsqueda de tablas de variables usando INDIRECTO: Para buscar desde una variable de tabla en Excel, podemos usar la función INDIRECTA. La función INDIRECTO tomará el rango de texto y lo convertirá en el rango de asistencia real.
Utilice ÍNDICE y COINCIDIR para buscar valor: La fórmula INDEX-MATCH se utiliza para buscar de forma dinámica y precisa un valor en una tabla determinada. Esta es una alternativa a la función BUSCARV y supera las deficiencias de la función BUSCARV.
Utilice BUSCARV de dos o más tablas de búsqueda | Para buscar desde varias tablas, podemos adoptar un enfoque IFERROR. Para buscar en varias tablas, se toma el error como un cambio para la siguiente tabla. Otro método puede ser un enfoque If.
Cómo hacer una búsqueda sensible a mayúsculas y minúsculas en Excel | La función BUSCARV de Excel no distingue entre mayúsculas y minúsculas y devolverá el primer valor coincidente de la lista. INDEX-MATCH no es una excepción, pero se puede modificar para que distinga entre mayúsculas y minúsculas. Veamos cómo …
Búsqueda de texto que aparece con frecuencia con criterios en Excel | La búsqueda aparece con mayor frecuencia en el texto en un rango que usamos la función INDEX-MATCH con MODE. Este es el método.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo utilizar la función BUSCARV de Excel| Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar Excel Función COUNTIF| Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.