Recoger datos de diferentes hojas de cálculo sin utilizar scripts

caCatalà (Catalán) enEnglish (Inglés)

Últimamente me han pedido varias veces cómo realizar esta operación. Varios profesores tienen clase con unos mismos alumnos (aunque no exactamente los mismos) y todos evalúan los mismos aspectos. Cada uno tiene su hoja con sus alumnos y se quiere poder recoger toda la información para poder hacer medias. Además, también se puede crear una hoja por alumno para que sólo vea sus resultados.

En este artículo detallaré una posible manera de realizarlo.

Empecemos por la hoja de cada profesor. Para hacerlo sencillo, en el ejemplo supondré solo 3 profesores que evalúan a 5 alumnos (aunque cada uno sólo evalúa a 3) y cada uno registra 4 aspectos diferentes.

La hoja de uno de los profesores podría ser de la siguiente forma (en el ejemplo cada aspecto se puntúa entre 1 y 4). Para hacer pruebas va bien que se hayan introducido valores, así se ve rápidamente si se importan bien.

La hoja general, podría ser de la siguiente forma.

De momento la hoja no recoge nada, pero ya se han indicado las direcciones de los 3 hojas de profesores.

¿Qué fórmula utilizar para que a cada alumno le aparezcan las calificaciones que le corresponden?

Utilizaremos la fórmula IMPORTRANGE (que ya presenté en un artículo hace un tiempo). Pero primero tendremos que conectar las hojas, ya que si no se hace, la fórmula importrange combinada con otras fórmulas no funcionará.

Por tanto, en cualquier celda (en el ejemplo utilizo la B3), insertamos la siguiente fórmula (que más adelante borraremos).

=IMPORTRANGE(P3;»Sheet1!B3″)

Habrá que cambiar el nombre Sheet1 por el nombre de la hoja del libro del profesor 1.

Seguramente, nos aparecerá un error (no sé por qué, en alguna ocasión se me han conectado las hojas sin pedir permiso). Cuando ponemos el cursor sobre la celda, aparecerá el mensaje para poder dar permiso y conectar las hojas.

Una vez conectada la primera hoja, cambiaremos la fórmula para conectar la segunda hoja. Ahora indicaremos

=IMPORTRANGE(P3;»Sheet1!B3″)

Y eso lo haríamos con todas las hojas. Una vez todos conectados, eliminaríamos la fórmula.

Ahora sí, introduciremos la fórmula para que recoja los datos de cada alumno. En la celda B3 indicaremos la siguiente fórmula.

=FILTER(IMPORTRANGE($P$3;»Sheet1!B3:E5″);IMPORTRANGE($P$3;»Sheet1!A3:A5″)=$A3)

Si queremos evitar que aparezcan errores en caso de que el alumno no esté en la hoja del profesor, podemos añadir la fórmula IFERROR. Quedaría de la siguiente manera.

=IFERROR(FILTER(IMPORTRANGE($P$3;»Sheet1!B3:E5″);IMPORTRANGE($P$3;»Sheet1!A3:A5″)=$A3);»»)

Ya podemos copiar la fórmula al resto de alumnos.

Sólo aparecen las calificaciones de los alumnos que estaban en la hoja del profesor.

Lo mismo podemos hacer con las hojas de los otros profesores. En la celda F3 indicaremos la misma fórmula que hemos insertado en B3, pero cambiando $P$3 por $P$4 que es donde se indica la dirección de la hoja del segundo profesor.

=IFERROR(FILTER(IMPORTRANGE($P$4;«Sheet1!B3:E5»);IMPORTRANGE($P$4;«Sheet1!A3:A5»)=$A3);«»)

Y también la copiaremos a todos los alumnos.

Repetimos el procedimiento con la tercera hoja de profesor.

Cualquier cambio que haga un profesor en su hoja se verá reflejado en esta hoja resumen.

Calcular la media será más o menos sencillo. Podemos crear una nueva pestaña y ordenarla de la siguiente manera.

En la celda B2 podemos indicar la siguiente fórmula.

=AVERAGEIF(Sheet1!$B$2:$M$7;B$1;Sheet1!$B3:$M3)

De este modo aparecerá la media de los profesores que hayan indicado este aspecto.

Basta copiar esta fórmula al resto de celdas (desde B2 hasta E6) y ya tendremos todas las medias calculadas.

Hoja de resultados para cada alumno

¿Podemos hacer que cada alumno vea sus resultados? Tendremos que crear una hoja de cálculo para cada alumno y compartirla con permiso de sólo lectura (se puede hacer con un script, pero en el título he dicho que todo sería sin scripts, así que lo haremos manualmente). Pero empecemos por la hoja del primer alumno.

La hoja puede tener un aspecto como el siguiente.

Como se puede ver hay que indicar la dirección de la hoja resumen, donde estarán los resultados. Para evitar que los alumnos intenten acceder y pidan permiso (ya que no tienen permiso ni de lectura en la hoja resumen), yo escondería la fila 5.

Al igual que hemos hecho antes, hay que conectar las hojas. En la celda B3 introducimos la fórmula sencilla del importrange.

=IMPORTRANGE(B5;»Sheet1!B3″)

Una vez conectados, borramos la fórmula y introducimos la siguiente.

=FILTER(IMPORTRANGE($B$5;»Sheet2!B2:E6″);IMPORTRANGE($B$5;»Sheet2!A2:A6″)=$A3)

Ahora ya sólo hay que hacer una copia de esta hoja y cambiar la celda A3. Si ponemos Alumno 2, aparecerán los resultados del segundo alumno. Así de sencillo.

Plantillas

Aquí enlazo las plantillas del ejemplo, por si alguien se quiere hacer una copia. Para que funcione habrá que cambiar los enlaces, ya que las copias tendrán otras direcciones.

Hoja resumen General

Hoja profesor 1

Hoja profesor 2

Hoja profesor 3

Hoja alumno 1

caCatalà (Catalán) enEnglish (Inglés)

2 thoughts on “Recoger datos de diferentes hojas de cálculo sin utilizar scripts

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.