Funció ARRAYFORMULA en fulls de càlcul de Google

esEspañol (Spanish) enEnglish (English)

Crec sincerament que els fulls de càlcul són una de les eines més útils per a mestres i professors. Ho he dit més d’un cop i, per a mi, dominar-los hauria de ser una de les competències digitals d’un docent. És cert que en aquest blog no hi he dedicat gaire articles. Fa un parell d’anys vaig dedicar-ne un a la fórmula IMPORTRANGE. Avui parlaré d’una altra fórmula que també ens pot ser molt útil, ARRAYFORMULA. És especialment útil quan treballem amb fulls de càlcul on es recullen respostes de formularis de Google.

Imaginem que volem tenir un full de càlcul amb algunes dades dels alumnes (nom, cognom, nombre de germans…). Amb els formularis de Google és molt senzill. Creem el formulari, enviem l’enllaç als alumnes i ells mateixos contesten.

Un cop els alumnes han contestat, podem obrir el formulari en mode edició i, en l’apartat Respostes, podem crear el full de càlcul.

Se’ns obrirà un full de càlcul amb totes les respostes dels alumnes.

Suposem ara que vulguem treballar una mica amb aquestes dades. Per exemple, volem fer una cosa tan senzilla com afegir un camp on el nom i el cognom estiguin junts. Anem a la primera columna lliure i utilitzem la següent fórmula:

=B2 & ” ” & C2

Efectivament, ens apareix el nom i el cognom en un única cel·la.

Si volem que aquest camp estigui per a tots els alumnes, haurem de copiar la fórmula a tots els alumnes. Cap problema.

Però, què passarà si algun alumne contesta el formulari tard? Es podria pensar que només cal copiar la fórmula en tota la columna E i així ja ho tenim a punt per qualsevol nombre de respostes. Però no és així.

Quan es respon un formulari, Google insereix una fila nova al final de la darrera resposta i, per tant, no respecte cap fórmula (ni format) que haguéssim previst. Si es respon el formulari després d’haver posat la fórmula, apareixeria de la següent forma.

O sigui, caldria tornar a copiar i enganxar la fórmula. Com ho podem fer per tenir el full preparat i que quan els alumnes contesten no calgui anar copiant fórmules?

Una opció és fer servir un complement per a fulls de càlculs que es diu copyDown. És una opció, però en molts casos hi ha una solució més simple. Utilitzar la fórmula ARRAYFORMULA.

Aquesta formula converteix una fórmula que retorna un valor (en el nostre cas el nom i el cognom junts) en una fórmula que retorna molts valors. O sigui, que amb una sola fórmula en el primer alumne, ja tindrem el camp per a tots.

Només caldria posar la següent fórmula:

=ARRAYFORMULA(B2:B & ” ” & C2:C)

Fixeu-vos que la fórmula és la mateixa que havíem posat, però canviant els valors (B2 i C2) per rangs (B2:B i C2:C). La fórmual ARRAYFORMULA indica al full que agafi tots els valors que hi ha en el rang B2:B (desde B2 fins al final de la columna B2) i apliqui la fórmula.

Per que ens funcioni, haurem d’eliminar les fórmules que havíem copiat, ja que sinó el full es queixarà que es sobreescriurien cel·les.

En aquesta versió que acabem de fer, només hi ha una sola fórmula en la cel·la E2. Per tant, si ara es contesta el formulari, no caldrà tocar res i tot seguirà funcionant.

Encara que no s’utilitzin formularis, la fórmula també és molt útil per estalviar-nos feina. Amb un sola fórmula ja ens funciona, no cal ni copiar i enganxar fórmules.

Per a usuaris avançats, aquesta fórmula es pot combinar amb altres COUNTIF, AVERAGE, VLOOKUP, IMPORTRANGE… Això sí, no és compatible amb totes. Per exemple, no funciona ni amb FILTER ni amb QUERY.

esEspañol (Spanish) enEnglish (English)

2 Responses to “Funció ARRAYFORMULA en fulls de càlcul de Google”

  1. Elizabeth Nava ha dit:

    No puedo generar el formulario…me manda el siguiente mensaje “La referència de cel·la està fora de l’interval” ya he intentado todo y no puedo. Y cuando baje el complemento hace un mes podía trabajar sin problema….:-(

    • Jaume Feliu ha dit:

      Apreciada Elizabeth,

      soy un profesor que comparte su trabajo de forma gratuita en este blog. No soy ninguna empresa, no gano dinero y no es mi trabajo. Lo desarrollo para mi centro y lo hago público por si le puede ser útil a otros profesores.
      Supongo que hablas de CoRubrics, aunque has dejado el comentario en un artículo que nada tiene que ver con CoRubrics. Desconozco la causa de tu problema, pero el complemento funciona igual que hace un mes, no ha habido ninguna actualización. Así que solo puedo recomendarte que empieces de nuevos con una hoja de cálculo nueva y que te asegures que indicas al menos 1 alumno.

Deixa un comentari

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.