Le Matrici Dinamiche di Excel

Formule di Matrice Dinamica in Excel: Come l’Espansione Automatica rivoluziona l’analisi dei dati.

Le Funzioni di matrice dinamica di Excel rappresentano un'innovazione significativa nel modo in cui i dati possono essere gestiti e visualizzati.
Queste Funzioni sono in grado di restituire matrici di dimensioni variabili, consentendo a una singola formula di produrre più risultati distribuiti su un intervallo di celle.
Questo processo, chiamato "Spill" (o espansione automatica), permette alla formula di "espandersi" automaticamente sul Foglio di calcolo, popolando le celle adiacenti con i risultati generati.
L'introduzione delle matrici dinamiche ha portato un importante cambiamento nell'impostazione delle formule di Excel, facilitando il lavoro con insiemi di dati complessi.
A differenza delle formule tradizionali, che restituivano un singolo valore, le matrici dinamiche permettono di gestire agevolmente più valori contemporaneamente.
In questo modo, è possibile creare calcoli avanzati e analisi multidimensionali con efficienza e flessibilità, riducendo la necessità di di ricorrere a formule complesse e nidificate.

Le Funzioni di matrice dinamica

[1] - Intervallo espansione dati
[2] - Esempio di Funzione di matrice dinamica
[3] - Principali Funzioni di matrice dinamica
[4] - Applicazione delle matrici dinamiche alle vecchie Funzioni di Excel 
[5] - Operatore # "operatore di intervallo di espansione" ("spill range" operator)
[6] - L'operatore di intersezione implicita: @
[7] - Il nuovo codice errore: #ESPANSIONE!
[8] - Prima del 2020....c'era una volta CTRL+ALT+INVIO
[9] - Sintesi e considerazioni conclusive

::NOTA. L'aggiornamento sulle matrici dinamiche è stato rilasciato agli abbonati a Office 365 nel gennaio 2020.
Attualmente, queste funzionalità sono disponibili negli abbonamenti a Microsoft 365 e da Excel 2021 e successive versioni.

Intervallo espansione dati

L'intervallo di espansione dei dati delle matrici dinamiche in Excel è una caratteristica che consente di gestire e manipolare i dati in modo più flessibile e automatico.
Quando si utilizza una Funzione che genera una matrice dinamica, come FILTRO, ORIDINA, UNICI etc., Excel crea automaticamente un intervallo di celle che si espande o si contrae in base ai risultati della Funzione.
Ecco alcuni termini chiave per comprendere questo concetto:
Matrice dinamica: Quando una formula produce più risultati (ad esempio, quando filtra un elenco), i risultati possono essere restituiti come una matrice. I risultati di queste formule possono riempire automaticamente più celle contemporaneamente.
Espansione automatica: Se i dati sorgente da cui una formula trae le informazioni vengono aggiornati, l'intervallo di espansione si adeguerà automaticamente per riflettere i nuovi dati.
Ad esempio, se una Funzione  FILTRO restituisce un elenco di nomi da un elenco e successivamente vengono aggiunti altri nomi all'elenco originale, la matrice espansa aggiornerà automaticamente l'elenco filtrato.
Interazione con le celle: Quando i risultati di una formula a matrice dinamica vengono restituiti, non è necessario immettere manualmente o modificare ciascuna cella. Questo riduce la possibilità di errori e semplifica la gestione dei dati.

::Nota. È fondamentale assicurarsi che le celle adiacenti all'intervallo di destinazione siano vuote; in caso contrario, Excel non potrà inserire i risultati. Se lo spazio necessario per espandere i dati non è disponibile, Excel mostrerà il messaggio di errore #ESPANSIONE! (#SPILL!).

Esempio di Funzione di matrice dinamica

Nel sottostante screenshot, viene illustrata l’applicazione di una Funzione di matrice dinamica di Excel (la Funzione UNICI).
Questa Funzione, inserita a partire dalla cella C2, genera automaticamente un intervallo di celle chiamato intervallo di espansione, che contiene tutti i risultati prodotti dalla Funzione stessa.
La Funzione UNICI è progettata per restituire un elenco di valori unici da un intervallo di dati specificato, eliminando i duplicati.
Quando viene applicata la Funzione UNICI, si crea automaticamente una "matrice" di valori distinti che si espande verso il basso o verso destra, occupando tutte le celle necessarie per restituire l’intero risultato.

Principali Funzioni di matrice dinamica

Excel offre diverse Funzioni di matrice dinamica che consentono di lavorare con insiemi di dati in modo più flessibile e potente. Ecco le principali Funzioni di matrice dinamica, con una breve descrizione per ciascuna.

FILTRO: Filtra i dati in base a una condizione specificata e restituisce solo i valori che soddisfano tale condizione. È particolarmente utile per estrarre un sottoinsieme di dati che risponde a determinati criteri.
Per approfondimento: La Funzione FILTRO

UNICI: Restituisce un elenco di valori univoci da un intervallo o da una matrice, eliminando automaticamente i duplicati.
Questa Funzione è utile per creare liste senza ripetizioni da un insieme di dati più ampio.
Per approfondimento: La Funzione UNICI

SEQUENZA: Crea una serie di numeri in sequenza su righe o colonne. Questa Funzione è utile per generare numeri consecutivi, spesso utilizzati in combinazione con altre formule.
Per approfondimento: Funzione SEQUENZA

DATI.ORDINA: Ordina i dati in ordine crescente o decrescente. Può essere utilizzata per classificare un insieme di dati secondo un criterio specifico, come numeri, testo o date.
Per approfondimento:  La Funzione DATI.ORDINA

DATI.ORDINA.PER: Ordina i dati in base a uno o più criteri. La Funzione di fatto sviluppa e si affianca alla precedente Funzione DATI.ORDINA e prevede la possibilità di ordinare una matrice sulla base di una o più colonne.
Per approfondimento: La Funzione DATI.ORDINA.PER

MATR.CASUALE: Crea una matrice di numeri casuali, utile per simulazioni o test.
Per approfondimento: La Funzione MATR.CASUALE

Applicazione delle matrici dinamiche alle vecchie Funzioni di Excel

Nelle versioni di Excel, precedenti alla 2021 e 365 come ad esempio Excel 2016 o Excel 2019, se si applica la Funzione ANNULLA.SPAZI  a un intervallo di celle, ad esempio A2:A7, la formula restituirà solo il risultato della prima cella dell’intervallo.
Quindi, applicando: =ANNULLA.SPAZI(A2:A7) Excel elabora solo A2 e ignora le celle successive, mostrando un unico risultato.
Nell'Excel supportato con le matrici dinamiche (come Excel 365), la stessa formula =ANNULLA.SPAZI(A2:A7) funziona in modo diverso.
Invece di restituire un solo valore, la Funzione elabora automaticamente tutte le celle dell’intervallo A2:A7 e restituisce i risultati in un intervallo di espansione.
Ogni cella nell'intervallo di output contiene il risultato della Funzione ANNULLA.SPAZI applicata a ciascuna cella dell'intervallo originale, come mostrato nel sottostante screenshot.
Questa impostazione rende le formule più versatili e permette di lavorare con set di dati più ampi senza dover copiare la formula in ogni cella.

Operatore #  "operatore di intervallo di espansione" ("spill range" operator)

Il simbolo # in Excel viene utilizzato per fare riferimento all'intero intervallo di celle generato da una formula di matrice dinamica.
Quando una formula crea un gruppo di valori che si espande automaticamente su più celle, Excel considera queste celle come un "intervallo di fuoriuscita o espansione" o "spill range".

In Excel, aggiungendo il simbolo "#" dopo l'indirizzo di una cella contenente una formula di matrice, il programma estende automaticamente il riferimento all'intero intervallo di celle generate dalla formula stessa.
Questo rende più semplice lavorare con i riferimenti di formule che producono matrici, facilitando il calcolo e l'analisi dei dati prodotti in modo dinamico.

Esempio di applicazione

Nell'immagine sottostante, sono presenti due formule: una in D2 e una in E8.
Nella cella C2 è inserita la Funzione =DATI.ORDINA(A2:A7;;1)
Nella cella E8 viene fatto riferimento ad un calcolo sulla matrice dinamica presente a partire dalla cella C2. Nello specifico la Funzione inserita è la seguente: =CONTA.VALORI(C2#)

Nella cella D2 è presente la Funzione DATI.ORDINA per ordinare un intervallo di celle.
Gli argomenti inseriti n questa Funzione sono i seguenti:
matrice: è l'intervallo da ordinare, in questo caso A2:A7, che contiene l'elenco dei venditori.
[indice_ordinamento]: viene lasciato vuoto (;) perché in questo caso viene ordinanto un intervallo costituito da una sola colonna, quindi non è necessario specificare un indice.
[ordinamento]: inserendo il codice 1, si imposta un ordine crescente (alfabetico in questo caso).
La Funzione DATI.ORDINA organizza alfabeticamente i nomi presenti nell'intervallo A2:A7 e mostra il risultato a partire dalla cella D2. Grazie alle matrici dinamiche di Excel, il risultato si espande automaticamente in verticale, creando un intervallo di espansione in D2:D7.

La Funzione inserita nella cella E8 utilizza CONTA.VALORI insieme all'operatore #.
Unico argomento della Funzione è intervallo. In questo caso come riferimento viene utilizzato C2#
L'operatore # è utilizzato in Excel per fare riferimento a un intervallo di espansione generato da una formula di matrice dinamica.
In questo esempio, C2# indica tutte le celle dell'intervallo di espansione derivato dalla Funzione presente nella cella C2.

In sintesi.
La formula in D2 =DATI.ORDINA(A2:A7;;1) ordina alfabeticamente i venditori in A2:A7 e inserisce il risultato ordinato a partire da D2, espandendosi automaticamente fino a D7.
La formula in E8 (=CONTA.VALORI(C2#)) conta il numero di celle non vuote nell'intervallo di espansione generato a partire da C2, sfruttando il simbolo # per riferirsi all'intero intervallo di espansione.

L'operatore di intersezione implicita: @

In Excel con le matrici dinamiche, è stata introdotta una novità nel linguaggio delle formule: il carattere @, conosciuto come operatore di intersezione implicita.
L'intersezione implicita è un comportamento della formula che riduce una serie di valori a un singolo valore.
Nelle versioni precedenti di Excel, ogni cella poteva contenere solo un valore singolo; quindi, questo tipo di comportamento era predefinito e non richiedeva un operatore speciale.
Con la nuova versione di Excel, tutte le formule sono considerate formule di array (matrice dinamica) per impostazione predefinita.
L'operatore di intersezione implicita, rappresentato dal simbolo @, serve per evitare il comportamento di matrice dinamica, nel caso in cui non si voglia ottenere un array di risultati.
In altre parole, se si desidera che una formula restituisca solo un valore singolo, basta inserire il simbolo @ davanti al nome della Funzione, e questa si comporterà come una formula non di matrice dimamica, simile al funzionamento delle versioni precedenti di Excel.
Per fare un esempio pratico, si può osservare lo screenshot presente nella successiva immagine.

Nell'immagine viene evidenziata l'applicazione dell'operatore di intersezione implicita @ in una formula di Excel.
La formula =@UNICI(A2:A12) è usata per ottenere solo il primo valore univoco dalla colonna "Venditori" (che va da A2 ad A12).
Normalmente, la Funzione UNICI restituisce un elenco di valori univoci presenti in un intervallo.
Senza l'operatore @, questa Funzione espanderebbe automaticamente i risultati su più celle, visualizzando tutti i nomi univoci dell'elenco.
Operatore @: Il simbolo @ davanti alla Funzione UNICI indica a Excel di applicare l'intersezione implicita, riducendo il risultato dell’matrice dimanica a un singolo valore, cioè il primo valore dell’elenco univoco.
Risultato: In questo caso, la formula =@UNICI(A2:A12) in C3 restituisce solo il primo nome univoco dell'intervallo, che è "Marco".

Il nuovo codice errore: #ESPANSIONE!

Nelle formule di matrice dinamica di Excel l'errore #ESPANSIONE! si verifica quando il risultato di una Funzione, (Ad esempio FILTRO nella sottostante immagine), non riesce ad espandersi nelle celle adiacenti.
L'errore #ESPANSIONE! si verifica quando Excel trova dati già presenti nelle celle in cui dovrebbe espandere i risultati della formula. Le formule di matrice dinamica, come FILTRO, richiedono uno spazio libero attorno alla cella di origine per poter visualizzare tutti i valori generati. Se le celle circostanti contengono dati o altre formule, Excel non riesce ad estendere automaticamente il risultato, generando quindi questo errore.

 

Prima del 2020....c'era una volta CTRL+ALT+INVIO

nel 2020, prima dell'introduzione delle matrici dinamiche su Excel 365, lavorare con le Funzioni matriciali in Excel richiedeva un processo piuttosto complesso.
Per effettuare calcoli su più insiemi di dati, si doveva utilizzare la combinazione di tasti CTRL+SHIFT+INVIO.
Questa sequenza trasformava una formula standard in una formula matriciale, abilitando il calcolo simultaneo su più valori.
Senza questa combinazione, Excel non era in grado di gestire automaticamente formule espanse su più celle, quindi si doveva selezionare l'intervallo di celle desiderato, inserire la formula e poi premere i tre tasti insieme per indicare a Excel che si trattava di una Funzione matriciale.
Questo metodo, noto come "CSE" (Control+Shift+Enter), era l'unico modo per utilizzare formule matriciali avanzate, come la somma o il prodotto di diversi intervalli di dati.
Le formule CSE, però, presentavano delle limitazioni: una volta inserite, l'intervallo di celle con i risultati era "bloccato," e non si potevano modificare o eliminare singolarmente i valori restituiti. Per modificare o aggiornare la formula, si doveva selezionare tutto l'intervallo e riscrivere la Funzione completa usando sempre CTRL+SHIFT+INVIO.
Questo rendeva il lavoro con le matrici complesso e poco flessibile rispetto alle matrici dinamiche.
Con l'introduzione delle matrici dinamiche in Excel 365, l'approccio CSE è diventato obsoleto.
Le nuove Funzioni di matrice dinamica, come MATR.CASUALE, espandono automaticamente i risultati su più celle senza la necessità di combinazioni di tasti specifiche, rendendo l'utilizzo delle matrici molto più intuitivo e immediato.
L'uso della modalità CSE (CTRL+SHIFT+INVIO) rimane rilevante solo per le versioni di Excel precedenti a Excel 365 e Excel 2019, dove è ancora l'unico metodo per utilizzare formule matriciali in grado di effettuare calcoli su più insiemi di dati simultaneamente.

Sintesi e considerazioni conclusive

Le matrici dinamiche in Excel rappresentano un'importante evoluzione nella gestione e nell'analisi dei dati, offrendo un notevole miglioramento nella flessibilità e nell'efficienza del lavoro sui fogli di calcolo.
Questa Funzioni consente di creare formule che restituiscono più valori su un'area di celle, senza dover inserire formule individuali per ciascuna cella o intervallo.

Di seguito, vengono proposti alcuni aspetti chiave sull'utilità e sugli aspetti innovativi introdotti con le matrici dinamiche.

1. Automatizzazione e Semplificazione dei Calcoli Complessi. Le matrici dinamiche automatizzano molti calcoli complessi che, in precedenza, richiedevano l’utilizzo di formule di matrice con combinazioni di CTRL+SHIFT+INVIO per essere eseguite.
Con le matrici dinamiche, le Funzioni come UNICI, ORDINA, FILTRO e altre, possono espandere i risultati in automatico, coprendo più celle. Questo elimina la necessità di ripetere le Funzioni e riduce notevolmente la possibilità di errori manuali.

2. Velocità di applicazione e incremento dell'Efficienza. Le matrici dinamiche velocizzano il lavoro poichè le Funzioni sono in grado di adattarsi dinamicamente alle modifiche nei dati.
Ad esempio, una formula di filtro dinamico aggiornerà automaticamente i risultati quando l'origine dei dati cambia.
Questo aspetto riduce il bisogno di aggiornamenti manuali e consente agli utenti di concentrarsi sull'analisi dei dati anziché sulla gestione tecnica del foglio di calcolo.

3. Riduzione del Sovraccarico di Celle e Miglioramento delle Performance. Con le matrici dinamiche, è possibile sostituire numerose formule individuali con una singola formula di matrice che si espande automaticamente.
Questo riduce il numero di formule complesse o ridondanti e, in molti casi, migliora le prestazioni complessive del foglio di calcolo, specialmente quando si lavora con dataset estesi.
Inoltre, riducendo il sovraccarico di celle con formule duplicate, si rende il foglio più snello e con meno possibilità di errori.

4. Versatilità e Maggiore Flessibilità nella Manipolazione dei Dati. Le nuove Funzioni di matrice dinamica offrono una flessibilità superiore, permettendo analisi dei dati più complesse.
Funzioni come SEQUENZA e FILTRO consentono di generare dati dinamici e di creare report o dashboard aggiornati in tempo reale.
Questa versatilità risulta particolarmente utile per coloro che lavorano con grandi quantità di dati e devono analizzare rapidamente sottogruppi o individuare informazioni specifiche senza creare formule complesse.

5. Cambio di Paradigma nella Logica di Costruzione delle Formule. L'introduzione delle matrici dinamiche cambia il modo in cui gli utenti pensano e costruiscono le formule.
Ora, tutte le formule in Excel sono trattate come potenziali formule di matrice dinamica (array).
L'operatore @, inoltre, permette di creare un'intersezione implicita: quando una formula restituisce un solo valore anziché una matrice, @ visualizza il dato solo nella prima cella della matrice.

6. Maggiore affidabilità sui Dati. Con le matrici dinamiche, è più facile assicurarsi che i dati rimangano coerenti attraverso l'intero foglio di calcolo. Le formule di matrice dinamica, infatti, aggiornano automaticamente i risultati in base alle modifiche dei dati di origine, mantenendo così una maggiore precisione e affidabilità. Questo è particolarmente utile per creare report che devono riflettere in tempo reale eventuali cambiamenti nei dati di base.

Conclusione. L'introduzione delle matrici dinamiche in Excel non rappresenta solo un aggiornamento tecnico, ma un vero e proprio salto di qualità per chi utilizza Excel.
Questi nuovi strumenti rendono l’analisi dei dati più potente e versatile, consentendo operazioni che prima richiedevano formule complesse o approcci più manuali.
Per chi è abituato ai metodi tradizionali, le matrici dinamiche possono richiedere un breve periodo di apprendimento, ma i benefici in termini di efficienza, precisione e semplicità rendono questa funzionalità molto utili per un utilizzo di Excel avanzato e professionale.

Indice Generale Argomenti: EXCEL

[Valter Borsato: 09/11/2024]