Tabelle riepilogative con array dinamici

Una tabella riepilogativa condensa un lungo elenco di righe grezze in un blocco compatto e leggibile: una riga per categoria con i totali accanto. Immagini un registro delle vendite con centinaia di righe che si trasforma in una tabella ordinata con ogni regione e il relativo fatturato totale.

Il metodo tradizionale consisteva nell'usare una tabella pivot manuale da aggiornare. Il metodo moderno usa formule a matrice dinamica che si aggiornano autonomamente non appena cambiano i dati. Nessun pulsante, nessun aggiornamento manuale.

In questa lezione combinerà tre strumenti potenti: UNIQUE per elencare le categorie, SUMIFS per calcolare il totale di ciascuna e FILTER per estrarre le righe corrispondenti. Insieme costruiscono un riepilogo dinamico.

Immagini un foglio denominato Sales con tre colonne: Regione nella colonna A, Prodotto nella colonna B e Importo nella colonna C, nelle righe dalla 2 alla 200.

Il nostro obiettivo è creare un riepilogo che mostri ogni regione distinta e il totale delle relative vendite. La prima difficoltà consiste nell'ottenere un elenco pulito delle regioni senza inserirle manualmente, perché in seguito potrebbero comparire nuove regioni.

• A2:A200 contiene molti nomi di regioni ripetuti, ad esempio Est, Ovest, Est, Nord.
• Vogliamo soltanto: Est, Ovest, Nord, ciascuno elencato una sola volta.

Questo elenco distinto è la base dell'intero riepilogo.

La funzione UNIQUE prende un intervallo e restituisce ogni valore una sola volta. Il risultato si espande, ovvero una sola formula riempie tante celle quanti sono i valori distinti.

Inserisca questa formula nella cella E2: l'elenco delle regioni comparirà automaticamente nelle celle sottostanti:

Se in seguito viene aggiunta una nuova regione ai dati, l'elenco espanso aumenta autonomamente. Non dovrà mai modificare la formula.

Ora ci serve il totale degli Importi per ogni regione nella colonna E. SUMIFS somma i valori di un intervallo solo quando un altro intervallo corrisponde a una condizione.

La struttura è SUMIFS(sum_range, criteria_range, criteria). Inserisca questa formula in F2, accanto alla prima regione:

Il riferimento E2# è il dettaglio fondamentale. Il simbolo # indica l'intero intervallo espanso a partire da E2. Pertanto, questa singola formula calcola il totale di ogni regione prodotta da UNIQUE.

Il riferimento all'intervallo espanso E2# punta sempre all'intero blocco prodotto da una formula, indipendentemente da quanto diventi grande. È questo che rende dinamico il riepilogo.

Quando UNIQUE trova 3 regioni, E2# è alto 3 celle e SUMIFS restituisce 3 totali. Quando i dati arrivano a 5 regioni, entrambi gli intervalli si espandono insieme, senza modificare nulla.

• E2 = solo la singola cella iniziale.
• E2# = l'intera matrice espansa che inizia da E2.

Prenda confidenza con il simbolo #: è il cuore delle formule per i dashboard.

Un riepilogo è più leggibile quando i totali sono ordinati. Racchiuda l'elenco delle regioni in SORT per visualizzare le categorie in ordine alfabetico, oppure ordini l'intera tabella in base al totale.

Per elencare le regioni in ordine alfabetico in E2:

Poiché i totali in F fanno ancora riferimento a E2#, l'ordinamento delle regioni riallinea automaticamente i totali. Le due colonne rimangono sincronizzate.

A volte desidera visualizzare le righe sottostanti di una categoria, non soltanto un totale. FILTER restituisce ogni riga che soddisfa una condizione e la espande nelle celle successive.

Per mostrare tutte le righe delle vendite in cui Regione è uguale al valore nella cella H1:

Se H1 contiene Est, otterrà ogni riga relativa a Est. Modifichi H1 in Ovest e il blocco si aggiornerà immediatamente. Questa è la base di una vista di dettaglio in un dashboard.

FILTER restituisce un errore #CALC! quando non trova corrispondenze. Per mantenere un risultato ordinato, fornisca il messaggio alternativo come terzo argomento facoltativo.

Il terzo argomento viene visualizzato quando non ci sono corrispondenze:

Ora una regione senza vendite mostra una nota chiara invece di un errore. Nei dashboard aggiunga sempre questo valore alternativo, così una selezione imprevista non comprometterà il layout.

Un riepilogo mostra spesso quanti ordini ha ricevuto ogni regione, non soltanto il relativo valore. COUNTIFS conta le righe che soddisfano una condizione, proprio come SUMIFS ma senza un intervallo di somma.

Inserisca questa formula nella colonna G, accanto ai totali:

Ora il riepilogo a tre colonne mostra Regione, Vendite totali e Numero di ordini, tutti alimentati dall'unico elenco espanso delle regioni in E2#. Tutto si aggiorna insieme.

Ecco la procedura completa, con gli elementi affiancati:

• E2: =SORT(UNIQUE(Sales!A2:A200)) elenca le regioni.
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) calcola il totale di ciascuna.
• G2: =COUNTIFS(Sales!A2:A200, E2#) conta gli elementi di ciascuna.

Solo la formula di E2 viene inserita; F e G si espandono grazie al riferimento #. Aggiunga una nuova vendita in qualsiasi punto di Sales e tutte e tre le colonne si aggiorneranno senza fare clic.

Un riepilogo basato su formule offre vantaggi concreti rispetto all'inserimento manuale dei valori o all'aggiornamento di una tabella pivot:

• In tempo reale: ricalcola i risultati non appena cambiano i dati.
• Dimensioni automatiche: le nuove categorie compaiono automaticamente grazie a UNIQUE e al riferimento #.
• Trasparente: chiunque può leggere la logica direttamente nella cella.

Lo svantaggio è che gli intervalli espansi hanno bisogno di spazio vuoto in cui espandersi; parleremo degli intervalli bloccati in una lezione successiva. Per ora, lasci spazio libero sotto le formule.

Ha creato una tabella riepilogativa che si mantiene aggiornata autonomamente:

• UNIQUE elenca ogni categoria una sola volta ed espande il risultato.
• SORT ordina l'elenco per renderlo più leggibile.
• SUMIFS e COUNTIFS calcolano il totale e il numero di elementi di ogni categoria usando il riferimento all'intervallo espanso E2#.
• FILTER estrae le righe corrispondenti per un'analisi di dettaglio, con un messaggio alternativo quando non trova corrispondenze.

Poiché ogni formula si basa sull'elenco espanso, l'aggiunta di nuovi dati aggiorna l'intero riepilogo senza interventi manuali. Nella prossima lezione ricreerà report completi in stile tabella pivot usando interamente le formule.