Registro y análisis de datos

En este apartado trataremos de las posibilidades que tiene la Hoja de Cálculo para sacar partido a un conjunto de datos que obtengamos.

Nos servirá de base para la explicación un ejemplo de Educación Física que utiliza los registros obtenidos por una clase en cinco pruebas físicas. Normalmente en las clases de la materia se suele trabajar con un mayor número de pruebas, pero para simplificar el ejemplo nos vamos a limitar a cinco: Abdominales, Flexibilidad, Lanzamiento de balón, Cooper (carrera continua de 12´) y 60 metros. Para estas pruebas existen unos baremos establecidos que puntúan de 0 a 10 según la edad y género de cada participante. El ejemplo está pensado para una clase de 4º de la E.S.O. Los baremos para este curso quedan recogidos en la Hoja 4ESO del archivo marcas.ods.

Nota. Para ver mejor el contenido de las hojas es recomendable establecer un 75% o así en menú Ver> Escala.

El objetivo del modelo con el que vamos a trabajar es recoger las marcas de todos los alumnos de una clase dos veces en un mismo curso, de forma que podamos medir su progresión de una a otra medición. El modelo nos permitirá puntuar cada registro de forma que podamos determinar la nota global, elaborar un informe global de la clase así como informes individuales de tantos alumnos como queramos.

Abre, si no lo has hecho ya, el modelo marcas.ods. Como puedes comprobar, está compuesto de cuatro hojas: la última, como hemos dicho, recoge los baremos correspondientes a este nivel y a estas pruebas. La primera, resultados, es la hoja de registro de todos los datos. La hoja infor glob recoge un informe general del curso, mientras que infor indiv contiene un informe del alumno que elijamos.

Veamos el proceso de elaboración de cada una:

Registro de datos

Las primeras celdas de la primera hoja permiten rellenar los datos relativos al grupo con el que se trabaja, el curso durante el que se han tomado los datos así como el nombre del profesor/a, datos todos ellos que se rescatarán en otros puntos del archivo.

La hoja de registro en sí se basa en conjuntos de celdas como el que aparece en la imagen. Los datos se recogen en las celdas de fondo blanco. En las grises se calcula la puntuación mientras que en las de fondo violeta aparece la progresión.

Para calcular la puntuación echamos mano de una fórmula como la siguiente:

=SI(C4="M";BUSCARV(D4; 4ESO.$A$3:$C$63; 2; 0);SI(C4="F";BUSCARV(D4; 4ESO.$A$3:$C$63; 3; 0); " "))

Para medir el progreso, la fórmula utilizada es:

=SI(ESBLANCO(D4); " "; SI(E4=0;" "; (G4-E4)/E4))

El cálculo es en realidad (G4-E4)/E4, que divide la diferencia de las mediciones entre la primera de ellas. Al dotar a la celda de formato porcentaje expresa el % de mejora (o, si es negativo, de pérdida) de una a otra medición.

Para evitar problemas, añadimos unos condicionantes en caso de que no existan datos, con la función ESBLANCO(), o que la primera medición sea 0, con lo que no se puede dividir entre esa cantidad (en ese caso, de hecho, podríamos decir que si la segunda marca ya no es cero, el aumento en porcentaje sería de alguna forma infinito, lo cuál por cierto haría muy feliz al alumno sobre todo si lo que ha sacado es un 1)

Lo mismo hacemos por supuesto con las otras cuatro pruebas. Para facilitar el registro de las marcas, sería cómodo fijar la parte correspondientes a los datos del alumnado. Eso lo conseguimos sin más que seleccionar las columnas C y D (entre las cuales estaría el límite de las dos partes) y menú Ventana> Fijar. Para anularlo bastaría con desmarcar la opción.

La hoja de registro de datos se completa con unas últimas columnas en las que se calcula el total de puntuación en cada evaluación entre las cinco pruebas y la media de las cinco que es la nota final. Igual que en cada prueba, en el total se calcula el progreso habido.

También se incluye una última fila, en la que, mediante la función =PROMEDIO( ) se calculan las marcas y notas medias de las clases en cada prueba y evaluación.

Resumen de resultados

Una vez que se han rellenado todos los datos, la segunda hoja (infor glob) ofrece un análisis global del grupo con el que se está trabajando. En él se incluyen los siguientes elementos:

1. Los datos de grupo, curso y profesor/a, rescatados de la primera hoja.
2. Los resultados globales del grupo por prueba, los cuáles se obtienen de la fila de promedios de la primera hoja, pero con una mejora: las notas suspensas aparecen en rojo y las aprobadas en verde.

Para conseguir ese efecto, definimos en menú Formato> Eslilo y formato> Nuevo los estilos suspenso (con fondo rojo suave y color de fuente blanco) y aprobado (fondo verde suave y color de fuente blanco)

Después seleccionamos las celdas de puntuación y menú Formato> Formato condicional, asignándole uno u otro formato según la nota sea menor que 5 o mayor o igual que 5.

Un gráfico estadístico que recoge las puntuaciones por prueba comparando las dos evaluaciones. Para elaborarlo seleccionamos con el ratón solamente la columna con los nombres de las pruebas y las dos correspondientes a puntuación. (para lo que nos ayudamos de la tecla Ctrl )

Como las puntuaciones oscilan entre 0 y 10 obligamos en las Propiedades del eje Y que la escala oscile entre esos valores.

3. Una tabla que analiza los resultados por género.

En la última tabla de la hoja recogemos resultados globales pero diferenciados por género. Para ello vamos a utilizar dos funciones que nos permiten operar sólo para algunos valores cada vez: CONTAR.SI y SUMAR.SI

En primer lugar, para contar el número de alumnas y el de alumnos, usamos fórmulas del tipo:

=CONTAR.SI(resultados.$C$4:$C$31; "F")

Que realiza un recuento en la columna C (correspondiente al género) sólo de aquellas celdas que incluyen “F” (femenino). Lo mismo hacemos con “M” para recontar el número de alumnos.

Para calcular la nota media de, por ejemplo, las alumnas, debemos sumar solamente las notas medias de las alumnas y dividir esta suma entre el número de alumnos. Después hacemos lo mismo con los alumnos. Lo conseguimos con esta fórmula:

La función=SUMAR.SI suma sólo aquellas celdas que cumplen una determinada condición, de manera que:

(1) Es el rango de celdas donde comprobamos la condición (la columna de género)

(2) Es la condición (en este caso que aparezca un determinado texto) para que el valor se incorpore a la suma.

(3) Es el rango de las celdas que sumamos si se cumple la condición (este rango no tiene por qué ser el mismo que aquel en el que comprobamos la condición). Es la columna de las notas en la evaluación correspondiente (en este caso la primera).

Una vez hecha la suma condicionada la dividimos entre el número de alumnas, (4), para calcular la media.

Datos individuales

La última Hoja se dedica a mostrar la información de un alumno que elijamos. Esta hoja incluye:

(1) Número y nombre del alumno/a. Se pide el número de lista y la hoja localiza el nombre mediante una función =BUSCARV().

(2) Un resumen de sus puntuaciones en las pruebas. Todas ellas se consiguen mediante fórmulas del tipo:

=BUSCARV(E7;resultados.B4:AB31; 4; 0)

Donde buscamos el nombre en un rango que incluye todos los resultados y sólo hay que determinar la columna donde se sitúan los datos de la prueba correspondiente.

Al igual que hicimos en el resumen global, incluimos un Formato Condicionado de manera que salgan los suspensos en fondo rojo y los aprobados en verde.

(3) Un gráfico de barras que recoge los resultados anteriores. Para que se visualice mejor, en la propiedades del Eje X, desmarcamos la opción Mostrar etiqueta y por el contrario, en las propiedades de una de las series de barras, marcamos dicha opción. Además, como hicimos en el gráfico anterior obligamos a que la escala sea de 0 a 10.

(4) Una última tabla que calcule la posición de los resultados del alumno/a respecto del conjunto de la clase. Con este fin, utilizamos una función =BUSCARV() que nos busque la puntuación de cada evaluación.

Para saber su posición en la clase introducimos la siguiente fórmula:

=JERARQUÍA(F35;resultados.AC4:AC31)

Que devuelve el lugar que ocupa un valor (en este caso la puntuación del alumno en una de las evaluaciones) dentro de un rango (la columna de las puntuaciones de todo el grupo para esa evaluación.

Este es el contenido del modelo. Las dos hojas resumen, global e individual, están diseñadas para ocupar un folio, lo que facilita su impresión. Se puede comprobar con menú Archivo> Vista preliminar.