Recollir dades de diferents fulls de càlcul sense utilitzar scripts

esEspañol (Spanish) enEnglish (English)

Darrerament m’han demanat diverses vegades com realitzar aquesta operació. Diversos professors tenen classe amb uns mateixos alumnes (tot i que no exactament els mateixos) i tots avaluen els mateixos aspectes. Cadascú té el seu full amb els seus alumnes i es vol poder recollir tota la informació per a poder fer mitjanes. A més, també es pot crear un full per alumne per tal que només vegi els seus resultats.

A Catalunya, això pot ser útil per recollir les competències transversals, que les han d’avaluar tots els professors però s’ha de posar una sola qualificació.

En aquest article detallaré una possible manera de fer-ho.

Comencem pel full de cada professor. Per fer-ho senzill, en l’exemple suposaré només 3 professors que avaluen a 5 alumnes (tot i que cadascú només n’avalua a 3) i cada un registra 4 aspectes diferents.

El full d’un dels professors podria ser de la següent forma (en l’exemple cada aspecte es puntua entre 1 i 4). Per fer proves, va bé que hi hagi valors introduïts, així es veu ràpidament si s’importen bé.

El full general, podria ser de la següent forma.

De moment el full no recull res, però ja si han indicat les adreces dels 3 fulls de professors.

Quin fórmula cal utilitzar per tal que a cada alumne li apareguin les qualificacions que li corresponen?

Utilitzarem la fórmula IMPORTRANGE (que ja vaig presentar en un article fa un temps). Però primer haurem de connectar els fulls, ja que si no es fa, la fórmula importrange combinada amb altres fórmules no funcionarà.

Per tant, en qualsevol cel·la (en l’exemple utilitzo la B3), inserim la següent fórmula (que més endavant esborrarem).

=IMPORTRANGE(P3;”Sheet1!B3″)

Caldrà canviar el nom Sheet1 pel nom del full del llibre del professor 1.

Segurament, ens apareixerà un error (no sé per què, en alguna ocasió se m’han connectat els fulls sense demanar permís). Quan posem el cursor sobre la cel·la, apareixerà el missatge per poder donar permís i connectar els fulls.

Un cop connectat el primer full, canviarem la fórmula per tal de connectar el segon full. Ara indicarem

=IMPORTRANGE(P3;”Sheet1!B3″)

I això ho faríem amb tots els fulls. Un cop tots connectats, eliminaríem la fórmula.

Ara sí, introduirem la fórmula per tal que reculli les dades de cada alumne. En la cel·la B3 indicarem la següent fórmula.

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

Si volem evitar que apareguin errors en cas que l’alumne no estigui en el full del professor, podem afegir la fórmula IFERROR. Quedaria de la següent manera.

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

Ja podem copiar la fórmula a la resta d’alumnes.

Només apareixen les qualificacions dels alumnes que estaven en el full del professor.

El mateix podem fer amb els fulls dels altres professors. A la cel·la F3 indicarem la mateixa fórmula que hem inserit a B3, però canviant $P$3 per $P$4 que és on s’indica l’adreça del full del segon professor.

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

I també la copiarem a tots els alumnes.

Repetim el procediment amb el tercer full de professor.

Qualsevol canvi que faci un professor en el seu full es veurà reflectit en aquest full resum.

Calcular la mitjana serà més o menys senzill. Podem crear una nova pestanya i ordenar-la de la següent manera.

En la cel·la B2 podem indicar la següent fórmula.

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

D’aquesta manera apareixerà la mitjana dels professors que hagin indicat aquest aspecte.

Només cal copiar aquesta fórmula a la resta de cel·les (des de B2 fins E6) i ja tindrem totes les mitjanes calculades.

Full de resultats per a cada alumne

Podem fer que cada alumne vegi els seus resultats? Haurem de crear un full de càlcul per a cada alumne i compartir-li amb permís de només lectura (es pot fer amb un script, però en el títol he dit que tot seria sense scripts, així que ho farem manualment). Però comencem pel full del primer alumne.

El full pot tenir un aspecte com el següent.

Com es pot veure cal indicar l’adreça del full resum, on hi haurà els resultats. Per evitar que els alumnes intentin accedir-hi i en demanin permís (ja que no tenen permís ni de lectura del full resum), jo amagaria la fila 5.

Igual que hem fet abans, cal connectar els full. En la cel·la B3 posem la fórmula senzilla de l‘importrange.

=IMPORTRANGE(B5;”Sheet1!B3″)

Un cop connectats, esborrem la fórmula i introduïm la següent.

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

Ara ja només cal fer una còpia d’aquest full i canviar la cel·la A3. Si hi posem Alumno 2,  apareixeran els resultats del segon alumne. Així de senzill.

Plantilles

Aquí enllaço les plantilles de l’exemple, per si algú se’n vol fer una còpia. Perquè funcioni caldrà canviar els enllaços, ja que les vostres còpies tindran unes altres adreces.

Full resum General

Full professor 1

Full professor 2

Full professor 3

Full alumne 1

esEspañol (Spanish) enEnglish (English)

Deixa un comentari

L'adreça electrònica no es publicarà. Els camps necessaris estan marcats amb *

Aquest lloc utilitza Akismet per reduir el correu brossa. Aprendre com la informació del vostre comentari és processada