🔝To translate this blog post to your language, select it in the top left Google box.
Este artículo no trata sobre el registro de estudiantes en Estados Unidos, a quienes Trump les quita la VISA y los expulsa del país:
Tampoco sirve para matar estudiantes, como pasa en Irán, o para matar a madres de estudiantes, como pasa en Estados Unidos, sino que sirve para registrar los calificativos de los estudiantes, como dicen en Perú, o sus calificaciones, como decimos en España:
Calificativos de estudiantes peruanos:
Son descriptores cualitativos del desempeño del estudiante. No son números, sino categorías como:
- AD (Logro destacado)
- A (Logro esperado)
- B (En proceso)
- C (En inicio)
Es decir, expresan cómo va el estudiante respecto a los aprendizajes, más que poner una nota numérica.
Calificaciones de estudiantes españoles:
Notas numéricas (0–10) o sus equivalentes cualitativos (Insuficiente, Suficiente, Bien, Notable, Sobresaliente).
Es el término estándar para las notas de los estudiantes.
🧑🏼🏫 Debo ser sincero, todavía no sé cómo mejorar las calificaciones de los estudiantes porque no soy docente, pero si sigues leyendo descubrirás cómo mejorar un registro de notas de estudiantes utilizando fórmulas de Excel, lo que te permitirá optimizar tus plantillas Excel en futuros proyectos.
🥸 El ejemplo original sirve para registrar los calificativos de estudiantes peruanos, pero se puede extrapolar fácilmente al registro de calificaciones de estudiantes españoles, ya que las notas del 0 al 10 son más fáciles de calcular que las letras utilizadas en los calificativos: AD, A, B, C.
🧑🏼🎓 ¿Qué mejoras adicionales implementarías para automatizar el registro de estudiantes?
¿Qué mejoras aprecias a simple vista?
El 8 de diciembre un docente de un colegio peruano escribió su primer mensaje en el foro de Excel:
Saludos a la distancia
Soy docente en un colegio de nivel secundario (13 años – 17 años) por mi trabajo me hice un registro de calificativos, así como de asistencia, esto lo hago al inicio del año académico... El problema que tengo es que durante el año hay estudiantes que se trasladan a otros colegios y también ingresan al nuestro, ello me conlleva a actualizar mi registro varias veces al año y este trabajo lo hago de forma manual...
¡Muchas gracias de antemano, mil gracias…!!!
Y adjuntaba un archivo .xlsm de 2 MB de tamaño, creado en 2013 y guardado el 8 de diciembre de 2025, por lo que parece ser que este docente lo lleva usando más de 12 años.
El 29 de diciembre me di cuenta de que no había recibido ninguna respuesta en tres semanas, así que le respondí:
Como prefiero las fórmulas adjunto una revisión con una nueva hoja de CONTROL, con varias tablas usadas en el resto de hojas...
El 4 de enero le volví a escribir:
Como no te has conectado desde el 14 de diciembre, no has visto la ayuda que te ofrezco desde el 29 de diciembre...
Adjunto una versión muy mejorada, hecha solamente con fórmulas, por lo que muevo este tema al subforo: Funciones y Fórmulas
El 5 de enero escribió su segundo mensaje:
Ante todo te pido mil disculpas por no responder tu mensaje y darte las gracias por dedicar tu valioso tiempo a resolver mi problema, soy aun novato en estos azares. Aun no he podido dar solución a mi problema pero te agradezco mucho por tu ayuda, me das nuevas luces, nuevos caminos para solucionar mi problema así como también mejoraste mi plantilla, mil gracias.
Y sabiendo que había vuelto al hilo, le dije:
Hola Álvaro, en primer lugar: gracias por contestar y que sigas siendo parte activa de este foro de Excel.
En segundo lugar: SOLICITO tu permiso para preparar un artículo en mi blog explicando los cambios que he hecho a tu plantilla, por si te sirve a ti o a cualquiera que le pueda ayudar el Registro de Estudiantes con sus calificaciones.
Y le expliqué las mejoras que había hecho en su Registro de Estudiantes, contestándome:
Claro que te doy permiso para lo que veas por conveniente.
Una consulta… Si se retira los estudiante 1, 5, 7, 10 y 20 del colegio, entonces ya no debería aparecer en el registro, pero estos aún persiste o ¿estoy haciendo algo mal?. Así mismo ingreso un estudiante nuevo, ¿Cómo seria el proceso?
Y eso es justamente lo que estoy haciendo ahora: escribir este artículo en mi blog por si puede facilitarle la vida a cualquier docente que necesite registrar los calificativos o las calificaciones de sus estudiantes, o a cualquiera que quiera aprender a mejorar una hoja de cálculo con fórmulas.
Para comprender cómo era el Registro de Estudiantes original, lo mejor es descargar la versión que el docente adjuntó en su primer mensaje, desde este enlace:
El archivo contiene 13 hojas:
- DATOS: Con las Áreas de conocimiento por Competencias y sus Capacidades.
- INICIO: Con los Datos Principales: Año; Nivel; Institución; Lugar; Área; Docente; Grado; Sección; Competencias y sus Capacidades.
- 4 BIMESTRES: Listado de Estudiantes por Área curricular, con los calificativos por Competencias y sus Capacidades. Además de los Logros de cada bimestre.
- 4 LOGROS BIMESTRALES: Gráficos de columnas con el Cuadro del Nivel de Logro de cada bimestre.
- RESUMEN ANUAL: Listado de Estudiantes por Área curricular, con los 4 calificativos bimestrales. El Calificativo Final de Área y la Situación Final de cada estudiante.
- LOGRO ANUAL: Gráfico de columnas con el Cuadro del Nivel de Logro anual.
- NOTA ANUAL: Listado de Estudiantes con el Calificativo Anual de Competencias (CAC), el Calificativo Anual de Área (CAA) y la conclusión descriptiva de final de período lectivo.
Descarga el Registro de Estudiantes, que he mejorado solo con fórmulas, desde este enlace:
Abre el archivo y presiona el botón: Habilitar edición cuando aparezca el aviso de VISTA PROTEGIDA.
El archivo es compatible con versiones desde Excel 2010, no contiene macros y tiene las hojas protegidas sin contraseña para evitar que los usuarios alteren las fórmulas.
ATENCIÓN: Se puede modificar este libro de Excel respetando esta licencia:
Creative Commons — Atribución-NoComercial-CompartirIgual-No portada — CC BY-NC-SA 4.0
En este vídeo explico cómo usar el nuevo Registro de Estudiantes con las mejoras que indico más adelante.
Lo primero que me llamó la atención fue el tipo del archivo original: Libro de Excel habilitado para macros (*.xlsm)
Como no contenía ninguna macro, lo guardé como: Libro de Excel (*.xlsx), lo que redujo su tamaño a menos de la mitad, pasando de 2 MB a 900 KB.
Eliminar las filas que no se usan, pues la versión original contenía 1.000 filas en cada una de las 13 hojas.
ATENCIÓN: Antes de eliminar las filas, se debe asegurar que no se borran celdas con valores o con fórmulas.
Para ello seleccionar todas las filas y seleccionar en la cinta de opciones:
Inicio > Buscar y seleccionar > Ir a Especial...
Llamar dos veces a Ir a Especial..., una para seleccionar: Constantes, y otra para seleccionar: Celdas con fórmulas.
Se pueden eliminar las filas si aparece el mensaje: ⚠️ No se encontraron celdas.
AVISO: Cada vez que se eliminen filas, se debe Guardar (Control + G) el archivo, para que actualice la última fila ocupada...
También se deben eliminar las columnas no usadas siguiendo el mismo procedimiento que para eliminar filas.
Esta mejora reduce el tamaño del archivo de 900 KB a 330 KB. ¡6 veces menos que el archivo original de 2 MB!
Imagina si el archivo tiene miles de filas no ocupadas, que hacen que el archivo se ralentice demasiado al abrirlo o modificarlo... Eliminando miles de celdas no usadas siempre he conseguido mejorar la respuesta de los archivos Excel. Por lo que os propongo que apliquéis esta mejora, siempre que haya filas o columnas que se puedan eliminar sin afectar a datos o fórmulas del archivo.
He añadido una nueva hoja oculta 'CONTROL' con varias tablas.
Estas tablas sirven para centralizar las listas de los desplegables de la validaciones de datos, que estaban dispersas en las hojas originales. Esta mejora ayuda al mantenimiento y automatización del Registro de Estudiantes.
- Tabla con la lista de años, desde el año 2015 al 2030.
- Tabla con dos Niveles: PRIMARIA y SECUNDARIA.
- Tabla de Instituciones Educativas.
- Tabla con los Lugares de estudio.
- Tabla con las Áreas Curriculares.
- Tabla de Docentes.
- Tabla de Grados: PRIMERO; SEGUNDO; TERCERO; CUARTO; QUINTO.
- Tabla de Secciones.
- Tabla de Bimestres: I; II; III; IV.
- Tabla de Unidades: 1 y 2; 3 y 4; 5 y 6; 7 y 8.
- Tabla de Calificativos: AD; A; B; C.
- Tabla con la lista de Estudiantes de un Área Curricular.
Observa que la tabla de Calificativos contiene el valor de cada calificativo y su logro final.
En todas las hojas, crear o modificar las listas desplegables con validaciones de datos, con origen en las tablas de la nueva hoja 'CONTROL', pues en el archivo original estaban en cualquier parte, o incluso "hardcodeadas" en la misma validación.
Lo primero que hice fue crear unos cuantos nombres nuevos, seleccionando desde la cinta de opciones: Fórmulas > Administrador de nombres
En la imagen de la derecha están todas las listas creadas. Por ejemplo la lista de las áreas curriculares desde la tabla de áreas:
ListaÁREAS: =TablaÁREAS[ÁREAS CURRICULARES]
En la versión original de la hoja 'INICIO', el origen de la validación de datos de la celda B15 era:
AREAS: =DATOS!$J$2:$J$13
Este rango de la hoja 'DATOS' estaba en una columna oculta en el archivo original, lo que es muy mala praxis en la gestión de los datos.
Con esta mejora los datos de cada lista desplegable están en una tabla dentro de la hoja 'CONTROL'.
AVISO: Aunque esta hoja esté oculta, es fácil mantenerla, asegurando que no la va a poder modificar directamente cualquier docente que use el Registro de Estudiantes, siempre que se proteja el libro Excel con una contraseña... (Cosa que no suelo hacer en los archivos que comparto públicamente, pues los protejo sin contraseña para que se puedan estudiar y analizar)
Para ver la ventana de la derecha, selecciona la celda B15 y en la cinta de opciones, selecciona: Datos > Validación de datos
Con esta mejora, todas las celdas con listas desplegables, tienen su Origen en una de las listas creadas con el Administrador de nombres.
Añadir una columna auxiliar en la hoja 'INICIO' para obtener dinámicamente las CAPACIDADES de cada una de las COMPETENCIAS.
La fórmula auxiliar de la celda H19 arrastrada hacia abajo es:
=SI($B$15="";0 ;SI(A19<>"";FILA(A19);H18))
En la celda B15 está el Área seleccionada con la lista desplegable.
Las COMPETENCIAS están en celdas combinadas, lo que obliga a crear la fórmula auxiliar comentada, que sirve para enumerar la fila en la que está escrita cada competencia. Por ejemplo, la primera competencia está en la fila 19 y la segunda está en la fila 25...
La fórmula con la que obtener las CAPACIDADES está en la celda B19 y es arrastrada hacia abajo:
=SI.ERROR(BUSCARV(INDICE(ESPACIOS(RangoCOMPETENCIAS);H19-FILA($A$18));COMPETENCIAS;2+FILA()-H19;FALSO)&"";"")
Esta fórmula depende de las celdas auxiliares en el rango H19:H45
Con esta mejora, si se añaden o se quitan capacidades a una competencia, sus capacidades serán obtenidas dinámicamente.
En las hojas originales de los 4 bimestres no se sabe que capacidades se evalúan en cada competencia, pues muestra las capacidades numeradas del 1 al 6, lo que puede inducir a error al introducir los calificativos, al desconocer sobre qué capacidad se aplican.
Esta mejora consiste en describir las capacidades reales de cada competencia, como se observa en la siguiente imagen.
Aquí no voy a explicar las fórmulas con las que consigo obtener los nombres de las capacidades, pues son muy fáciles y están en la versión mejorada del Registro de Estudiantes, que puedes descargar más arriba.
Originalmente los nombres de los estudiantes estaban en el rango B17:B63 de la hoja del primer bimestre, lo que obligaba a mantener esta hoja y acordarse que los estudiantes estaban escritos en esta hoja.
Los nombres de los estudiantes, mostrados en cualquier hoja, se obtienen ahora de la tabla de estudiantes, lo que permitirá automatizar algún día la lista de estudiantes de cada nivel, grado y sección, creando más columnas en la tabla...
Para obtener las columnas de LOGRO en las hojas de bimestres, originalmente hacían falta 49 columnas auxiliares ocultas en el rango BC:CY, con fórmulas para transformar los calificativos en valores numéricos. Por ejemplo, la fórmula de la celda BC17 era:
=SI(F17="AD";4;SI(F17="A";3;SI(F17="B";2;SI(F17="C";1;""))))
Y la fórmula de la celda BI17 era:
=SI(BC17="";"";REDONDEAR(PROMEDIO(BC17:BH17);0))
Esta es la mejora que más me ha costado implementar, pues cada grupo de hasta 7 columnas auxiliares se ha reducido a modificar las fórmulas de la correspondiente columna de LOGRO.
He eliminado todas estas columnas auxiliares, que son un problema si cambian los criterios calificativos para adaptarlos a la docencia en otros países. Como por ejemplo con las calificaciones en España, que obligarían a modificar cientos de fórmulas ocultas.
La mejora consiste en eliminar las 49 columnas auxiliares, calculando directamente en las columnas de LOGRO los calificativos promedio, con fórmulas como la de la celda BA17:
=SI($B17="";0;SI.ERROR(INDICE(ListaCALIFICATIVOS;
COINCIDIR(REDONDEAR(SUMA(
BUSCARV(AY17;RangoCALIFICATIVOS;2;FALSO);
BUSCARV(AZ17;RangoCALIFICATIVOS;2;FALSO)) /
SUMAPRODUCTO(--(AY17:AZ17<>0));0);
ListaCALIFICAVALORES;0));0))
Esta fórmula matricial es válida en cualquier versión desde Excel 2010 a Excel para Microsoft 365.
Lo mismo ocurre en la hoja original 'RESUMEN ANUAL', con 5 columnas auxiliares ocultas en el rango J:N, con las conversiones a valores numéricos de los calificativos, para obtener el calificativo promedio en la columna con la calificación final de área.
Se han eliminado las 5 columnas auxiliares, sustituyendo las fórmulas de la columna G, para obtener el calificativo directamente, como se observa en esta imagen.
La hoja original 'NOTA ANUAL' contenía 25 columnas ocultas en el rango Y:AW con las que calcular las 4 columnas con los Calificativos Anuales de Competencia (CAC).
He eliminado las 25 columnas auxiliares, y he calculado los 4 CAC directamente en sus columnas, con fórmulas como la de la celda V17.
He limpiado los formatos condicionales en las 4 hojas de bimestre, añadiendo un nuevo formato para marcar en color amarillo las celdas que se deben editar.
Seleccionar en la cinta de opciones la pestaña: Inicio > Formato condicional > Administrar reglas...
Y seleccionar en Mostrar reglas de formato para: Esta hoja
La primera regla cambia a amarillo el color de fondo si no existe el estudiante y hay calificativos escritos manualmente. O si existen capacidades en la fila 8 y no se han definido calificativos manualmente. O si existe el estudiante y hay calificativos en columnas sin capacidades. Esta es la fórmula booleana:
=O(Y($B17="";F17<>"";NO(ESFORMULA(F17)));Y($B17<>"";F$8<>"";F17="");Y($B17<>"";F$8="";F17<>""))
Las otras 4 reglas son para cambiar el color de los calificativos en las columnas de LOGRO y en las columnas de RESUMEN.
En la siguiente imagen se aprecian los cambios de color de los formatos condicionales del primer bimestre.
Los colores amarillos nos indican que:
- El estudiante número 3 no existe, por lo que hay que suprimir manualmente todos los calificativos de la fila 19.
- Falta añadir manualmente los calificativos de desempeño para las 3 capacidades de las columnas AO, AP y AT.
He protegido todas las hojas sin contraseña, excepto la hoja 'CONTROL' que está oculta, pero protegida para que no se muestre por defecto, lo que he hecho seleccionando en la cinta de opciones: Revisar > Proteger libro
Para poder editar celdas en hojas protegidas, antes de proteger las hojas se debe seleccionar el rango de celdas que se quiera editar y seleccionar en la cinta de opciones: Inicio > Formato > Formato de celdas...
Elegir la pestaña: Proteger, y en esta ventana desmarcar: Bloqueada
Recomiendo proteger las hojas y el libro con contraseña cuando lo emplee un docente, para que no tenga la tentación de cambiar las fórmulas por su cuenta...
No cuento las micro-mejoras que he incluido en este proyecto porque no acabaría este artículo, ¡y ya es demasiado extenso!
Aunque tengo que decir que una de esas mejoras es referente a la Accesibilidad:
Cuando en un libro de Excel aparece en la barra de estado: Accesibilidad: todo correcto, indica que su contenido es accesible para todos los usuarios, incluidas las personas con discapacidades.
En la imagen de la derecha aparecen los problemas de accesibilidad del archivo original, con 5 faltas del texto alternativo en los gráficos.
Lo más laborioso ha sido eliminar las 73 celdas combinadas... Aquí no voy a explicar como he eliminado cada una de las celdas combinadas, pero sí diré que los lectores de pantalla (Narrador | Microsoft Windows), diseñados para personas con discapacidad visual, no pueden leer correctamente tablas con celdas combinadas.
Sugiero realizar esta mejora de accesibilidad en todos vuestros proyectos, pues algún día puede ser que tú mismo lo agradezcas...
El docente que pidió ayuda para mejorar el Registro de Estudiantes hizo una última consulta:
Si se retira los estudiante 1, 5, 7, 10 y 20 del colegio, entonces ya no debería aparecer en el registro, pero estos aún persiste o ¿estoy haciendo algo mal?. Así mismo ingreso un estudiante nuevo, ¿Cómo seria el proceso?
Respuesta: Los procesos a realizar en el Registro de Estudiantes mejorado son:
Para retirar estudiantes del colegio:
1) Suprimir los estudiantes retirados del colegio en la tabla de estudiantes de la hoja 'CONTROL', con lo que ya no aparecerán esos estudiantes en el resto de hojas.
2) Suprimir los calificativos de las celdas que aparecen en color amarillo en las 4 hojas bimestrales, en las filas de los estudiantes suprimidos.
Para ingresar estudiantes nuevos:
1) Incluir el nombre del estudiante nuevo en una fila que no esté usada en la tabla de estudiantes de la hoja 'CONTROL', con lo que aparecerá ese estudiante en el resto de hojas.
2) Editar los calificativos del nuevo estudiante en las 4 hojas bimestrales.
AVISO: Si quieres usar este Registro de Estudiantes en tus proyectos educativos, tendrás que crear copias para cada nivel, área, grado y sección, pues eso aún no está automatizado...
Si has llegado a leer hasta aquí. ¡Enhorabuena por ser un lector empedernido como yo!
Estimado lector, tengo algunas consultas que hacerte:
¿Eres docente? ¿De que país?
¿Qué tipo de calificativos usas?
¿Qué tipo de calificaciones usas?
¿Quieres una versión con calificaciones del 0 al 10?
¿Qué mejoras propones para este proyecto?
¿Cómo lo automatizo?
¿Puedes esperar hasta el próximo año académico a recibir más mejoras?
Gracias anticipadas por tus comentarios y por tus respuestas, que nos ayudarán a mejorar juntos cada día...
