Per decenni una formula in un foglio di calcolo ha restituito un solo valore in una sola cella. Le matrici dinamiche hanno cambiato questa regola: una formula scritta in una cella può restituire un intero elenco, che si «espande» automaticamente nelle celle sottostanti e adiacenti. Sono disponibili in Excel per Microsoft 365 e nelle versioni più recenti, in Excel per il web, e funzioni analoghe esistono in Google Sheets e nelle versioni recenti di LibreOffice Calc. Cambiano il modo di costruire report ed elenchi.
Il concetto di espansione
Se in una cella vuota scrivi =A2:A10, in una versione con matrici dinamiche non ottieni un solo valore ma nove: la formula occupa A2:A10 copiati a partire dalla cella in cui l'hai scritta. Solo la prima cella contiene la formula; le altre mostrano il risultato con un bordo sottile che delimita l'area espansa.
Due conseguenze pratiche:
- se una cella dell'area di destinazione non è vuota, compare l'errore #ESPANSIONE! (#SPILL!): basta liberarla;
- per fare riferimento all'intero risultato, qualunque sia la sua lunghezza, si usa l'operatore
#: se la formula è in F2,F2#indica tutta l'area espansa.
UNICI: l'elenco dei valori distinti
=UNICI(B2:B5000)
restituisce l'elenco degli agenti senza ripetizioni, nell'ordine in cui compaiono. Quando nei dati arriva un nuovo agente, l'elenco si allunga da solo.
Due argomenti facoltativi meritano attenzione: il secondo permette di confrontare colonne invece di righe, il terzo, se impostato a VERO, restituisce solo i valori che compaiono una sola volta. Quest'ultima opzione è comoda per trovare i codici non ripetuti in un elenco.
ORDINA e ORDINA.PER
=ORDINA(UNICI(B2:B5000))
restituisce gli agenti in ordine alfabetico. ORDINA accetta anche l'indice della colonna su cui ordinare e la direzione: =ORDINA(A2:D5000; 4; -1) ordina l'intero elenco per la quarta colonna in modo decrescente.
ORDINA.PER (SORTBY) ordina un intervallo in base a un altro, anche se quest'ultimo non fa parte del risultato. Per esempio, elencare i nomi dei prodotti ordinati per fatturato senza mostrare il fatturato:
=ORDINA.PER(A2:A200; D2:D200; -1)
FILTRO: estrarre le righe che servono
FILTRO è probabilmente la più utile delle tre:
=FILTRO(A2:D5000; C2:C5000="Nord"; "Nessun risultato")
restituisce tutte le righe in cui la zona è Nord. Il terzo argomento è il valore mostrato quando nessuna riga soddisfa la condizione; senza di esso compare un errore.
Più condizioni
Le condizioni si combinano con la moltiplicazione (per l'E) e con l'addizione (per l'O), racchiudendo ciascuna tra parentesi:
=FILTRO(A2:D5000; (C2:C5000="Nord")*(D2:D5000>1000))
=FILTRO(A2:D5000; (C2:C5000="Nord")+(C2:C5000="Centro"))
La logica è semplice: ogni condizione produce una serie di VERO e FALSO, cioè di 1 e 0. Il prodotto vale 1 solo se entrambe sono vere; la somma vale almeno 1 se almeno una lo è. Le funzioni E e O non funzionano in questo contesto, perché restituiscono un unico risultato per l'intero intervallo.
Combinare le funzioni
La vera forza sta nell'annidarle. Alcuni esempi:
| Obiettivo | Formula |
|---|---|
| Clienti distinti della zona Nord, in ordine alfabetico | =ORDINA(UNICI(FILTRO(B2:B5000; C2:C5000="Nord"))) |
| I cinque ordini più alti | =PRENDI(ORDINA(A2:D5000; 4; -1); 5) |
| Numero di clienti distinti | =CONTA.VALORI(UNICI(B2:B5000)) |
PRENDI (TAKE) è disponibile nelle versioni più recenti di Excel; dove manca, si ottiene lo stesso risultato con INDICE e SEQUENZA.
Un report che si aggiorna da solo
Uno schema molto pratico: in F2 scrivi =ORDINA(UNICI(B2:B5000)) per l'elenco degli agenti; in G2 scrivi
=SOMMA.PIÙ.SE(D2:D5000; B2:B5000; F2#)
Grazie a F2#, SOMMA.PIÙ.SE calcola un totale per ogni agente dell'elenco e anche la colonna G si espande da sola. Aggiungi un agente ai dati e il riepilogo si allunga senza che tu debba copiare formule.
Precauzioni
- Compatibilità. Un file con FILTRO aperto in una versione di Excel che non supporta le matrici dinamiche mostra errori o formule convertite. Se il file circola fuori dal tuo gruppo, verifica le versioni in uso.
- Non scrivere dentro l'area espansa. Qualsiasi valore inserito blocca l'intero risultato.
- Le tabelle di Excel non accettano formule espanse al loro interno: metti le formule dinamiche fuori dalla tabella, facendo riferimento alle sue colonne.
- Intervalli interi con cautela. Formule come
FILTRO(A:D; ...)su colonne intere funzionano, ma su file grandi rallentano il ricalcolo.
Nel mio lavoro le matrici dinamiche hanno sostituito quasi del tutto le colonne di appoggio che prima servivano per estrarre elenchi: i fogli sono più corti e si capisce meglio da dove arriva ogni risultato.
In sintesi
- Una formula a matrice dinamica restituisce più valori che si espandono nelle celle vicine.
- UNICI elimina le ripetizioni, ORDINA e ORDINA.PER ordinano, FILTRO estrae le righe.
- In FILTRO le condizioni si combinano con
*per l'E e+per l'O. - L'operatore
#fa riferimento all'intero risultato espanso. - Libera l'area di destinazione per evitare #ESPANSIONE! e verifica la compatibilità.
