Se si lavora con parecchi dati in google sheet può essere capitato di aver sentito/letto da qualche parte della funziona ARRAYFORMULA. In google sheet, ARRAYFORMULA è una funzione utile ma spesso poco conosciuta. Vediamo quindi di capire un po’ cos’è e come funziona.
Cos’è la funzione ARRAYFORMULA di Google Sheets?
Il nome di questa funzione può sembrare un po’ poco intuitivo, specialmente se confrontato con le più intuitive e frequenti IF, SUM il cui nome ci dice già molto. Questo ci fa pensare che sia una funzione strana e difficile da usare, ma in realtà nelle giuste occasioni si rivela molto utile.
Andando avanti anche la definizione ufficiale non è molto chiara, si può leggere infatti nella guida veloce la definizione breve.
La guida riporta infatti il seguente riepilogo:
Attiva la visualizzazione di valori restituiti da una formula matrice in più righe e/o colonne e l’utilizzo di funzioni non matriciali con matrici.
In pratica questa descrizione ci dice che con la funzione ARRAYFORMULA possiamo usare funzioni normali applicate a matrici invece che a valori singoli.
Nonostante questa definizione il modo migliore per capire come funziona questa funzione probabilmente è tramite un esempio.
Esempio di utilizzo della formula ARRAYFORMULA in Google Sheet
Prendiamo come esempio il seguente caso: abbiamo il foglio riportato qui sotto e vogliamo calcolare il costo totale di tutti i prodotti della lista.
Il metodo più intuitivo e più comune è quello di creare una formula nella colonna D con il prodotto delle colonne B e C per avere il costo totale per singolo oggetto, e poi fare la somma della colonna per ottenere il totale.
Questo è il metodo più comune, ma implica la necessità di occupare la colonna D con dei dati che potrebbe non servirci visualizzare, se vogliamo solo il costo totale non ci interessa il parziale per singolo oggetto.
Ci viene quindi in aiuto la preziosa funzione ARRAYFORMULA, che in una singola funzione fa tutti i conti necessari senza dover occupare inutilmente una colonna in più.
La formula:
In breve, la formula che ci permette di fare tutto insieme e questa
=ArrayFormula(SUM(B2:B5 * C2:C5))Come funziona?
Di solito quando utilizziamo la moltiplicazione lo facciamo specificando le due singole celle da moltiplicare. In questo caso stiamo utilizzando due range, ma se scrivessimo la formula semplicemente come moltiplicazione fra due range avremmo un errore, nella cella troveremmo scritto #VALUE! invece del risultato.
=(B2:B5 * C2:C5)Dobbiamo quindi dire a Google Sheets che quella che vogliamo è una ArrayFormula, ovvero una formula applicata a dei range. Lo possiamo fare in due modi: possiamo aggiungere manualmente ArrayFormula prima delle parentesi, oppure possiamo utilizzare la scorciatoia da tastiera Ctrl + Shift + Enter (Cmd + Shift + Enter su un Mac).
Ci ritroveremo quindi con questa formula:
=ArrayFormula(B2:B5 * C2:C5)E questo risultato:
A questo punto avremo la formula nella cella D6 che però ancora genererà una riga per ogni riga degli intervalli di partenza (D6 = B2*C2, D7= B3*C3 ecc…).
La nostra ArrayFormula combinerà quindi i due array di partenza in un array risultato eseguendo la moltiplicazione del primo valore del primo array con il primo valore del secondo array, poi del secondo valore del primo array con il secondo valore del secondo array e così via.
Questo funziona solamente se i due array sono della stessa lunghezza, in caso contrario avremo delle celle con errore #N/A.
A questo punto ci stiamo avvicinando al nostro obiettivo, abbiamo infatti in un certo senso ricreato ciò che avremmo messo nella colonna D (la lista del costo totale per singolo oggetto). Ci basterà quindi a questo punto solamente sommare tutti i valori appena generati. Per fare questo dobbiamo aggiungere la funzione SUM dentro la nostra funzione ArrayFormula, per arrivare alla formula finale.
=ArrayFormula(SUM(B2:B5 * C2:C5))Ottenendo quindi il risultato finale che attendevamo.
Un altro esempio di ArrayFormula in Google Sheet
La funzione ARRAYFORMULA può essere utilizzata sia su array “verticali” che su array “orizzontali” anche insieme. Vediamo ad esempio come ricreare l’intero schema delle tabelline con una singola formula.
Partiremo da due array, uno verticale e uno orizzontale contenenti i numeri da 1 a 10.
Tutto ciò che ci servirà per riempire l’intero schema sfruttando la funzione ARRAYFORMULA è una singola formula nella cella B2.
=ARRAYFORMULA(A2:A11*B1:K1)Una volta premuto invio, la tabella si riempirà con i prodotti dell’array verticale con quello orizzontale. Avremo quindi ottenuto l’intero schema delle tabelline con una singola formula.
Ulteriori informazioni
Potete trovare altre informazioni nella pagina della documentazione dei google docs relativa alla funzione ARRAYFORMULA.