Excel è cambiato. La Microsoft ha modificato il motore di calcolo di Excel. Questa modifica ha rivoluzionato il modo di lavorare con Excel.
Di cosa si tratta?
In passato, ogni volta che hai usato Excel, hai accettato una semplice regola operativa: una formula, una cella.
Questa regola era valida per tutte le formule, comprese quelle avanzate.
Il cambiamento nel motore di calcolo ha consentito l'introduzione delle funzioni a matrice dinamica.
In altri termini, ora Excel consente a una singola formula di restituire più risultati. La formula è così in grado di espandersi in più celle.
Le funzioni a matrice dinamica sono disponibili nelle seguenti versioni:
- Excel 365;
- Excel Online;
- Excel 2024;
- Excel 2021.
La Microsoft ha originariamente reso disponibile la modifica al motore di calcolo di Excel (pertanto l’utilizzo delle funzioni a matrice dinamica) solo per gli iscritti versione Beta per testare le nuove funzionalità .
A partire da luglio 2020 l’aggiornamento è stato esteso a tutti gli utenti con l'abbonamento a Microsoft 365.
Purtroppo, nella versione di Excel 2019 e in quelle precedenti non è possibile sfruttare le funzioni a matrice dinamica.
Il termine matrice dinamica potrebbe sembrarti complicato.
In questo articolo ti mostrerò alcuni esempi di utilizzo e ti aiuterò a capire come utilizzarle.
Sono sicuro che apprezzerai la semplicità e la potenza di queste nuove funzioni in Excel.
Introduzione
Per comprendere le funzioni a matrice dinamica in Excel partiamo da un semplice esempio.
Inseriamo la formula seguente nella cella C2:
Che cosa ti aspetti restituisca questa formula?
Per rispondere alla domanda analizziamo due diversi scenari:
- prima delle funzioni a matrice dinamica
- e dopo l'introduzione delle funzioni a matrice dinamica.
Prima delle funzioni a matrice dinamica
Come anticipato in precedenza, nella versione di Excel 2019 e in quelle precedenti, una formula poteva restituire un solo risultato in una singola cella.
Per spiegare meglio questo concetto inseriamo, in tre differenti celle (C2, C5, C9), la formula precedente come mostrato nelle immagini successive.
Contrariamente a quello che potresti aspettarti, Excel non mostra tutti i valori presenti nell’intervallo di celle da A2 ad A8.
Infatti, nella cella C2, viene mostrato solo il valore 8.
Questo perché Excel immagina che vogliamo ottenere il valore presente nella cella A2 in quanto in linea con essa.
Ora inseriamo la stessa formula nella cella C5.
Anche in questo caso Excel immagina che vogliamo ottenere il valore presente nella cella A5, pertanto restituisce il valore 18.
Infine, inseriamo la formula nella cella C9.
In questo caso, poiché la cella C9 non è in linea con nessuno dei dati di origine, Excel restituisce l'errore #VALORE!.
Questa ipotesi prende il nome di intersezione implicita.
Osservando i risultati precedenti deduciamo che Excel risponde in modo diverso a seconda della posizione della formula rispetto all’origine dei dati.
In sintesi, ogni volta che la formula è in linea con i dati di origine, Excel restituisce il valore presente in corrispondenza della stessa riga (intersezione implicita).
Se la formula NON è in linea con i dati di origine, Excel restituisce l'errore #VALORE!.
Nella versione di Excel 2019 e in quelle precedenti, per ignorare l'intersezione implicita, puoi utilizzare la seguente combinazione di tasti quando inserisci le formule.
Con le funzioni a matrice dinamica
Supponiamo ora di inserire la formula iniziale all’interno di una delle nuove versioni di Excel (Excel 365, Excel Online, Excel 2024, Excel 2021).
Nell’immagine riportata in basso, la cella C2 contiene la formula ma incredibilmente Excel restituisce tutti i valori presenti in tutte e sette le celle (da C2 a C8).
Il processo che consente a una formula di riempire più celle è chiamato espansione, mentre l'intervallo di celle riempite dalla formula è chiamato intervallo di espansione.
L'espansione nelle funzioni a matrice dinamica
Ogni volta che inserisci in una cella una funzione a matrice dinamica viene visualizzato un bordo blu.
Questo bordo indica tutte le celle all'interno dell'intervallo di espansione. Tutto ciò che si trova al suo interno viene calcolato dalla cella prima cella in alto.
Quando selezioni una qualsiasi cella all'interno dell'intervallo di espansione, la barra della formula visualizza la formula che determina quel risultato.
Tuttavia, puoi modificare la formula solo nella prima cella in alto. Se selezioni una cella diversa (ad esempio, la cella C3), noterai che la formula non può essere modificata. Infatti, la formula sarà disattivata e sarà di colore grigio.
Inoltre, se fai doppio nella cella (ad esempio, C3) non visualizzerai alcuna formula perché non si trova realmente in quella cella.
Cosa succede se sono presenti dei dati nell'intervallo di espansione?
Fortunatamente, a differenza di ciò che potresti aspettare, i dati esistenti non verranno sovrascritti. Al contrario, verrà restituito l'errore #ESPANSIONE! (in inglese #SPILL!) nella prima cella in alto (la cella C2 nel l’esempio in basso).
Se fai clic nella cella che contiene l'errore #ESPANSIONE!, Excel visualizza una linea blu tratteggiata che indica l'intervallo di espansione e ti consente di individuare l’elemento che sta generando l’errore.
A questo punto puoi spostare o cancellare il valore che genera l'errore di #ESPANSIONE! oppure inserire la formula in una cella differente.
L'errore #ESPANSIONE! in Excel può verificarsi quando:
- l'intervallo di espansione non è vuoto ovvero quando sono presenti dei dati nelle celle sottostanti la formula;
- l'intervallo di espansione include una cella unita;
- l'intervallo di espansione è troppo grande;
- l'intervallo di espansione è sconosciuto;
- l'intervallo di espansione è incluso in una tabella.
Le funzioni a matrice dinamica
L'introduzione delle funzioni a matrice dinamica rappresenta un grande cambiamento non solo nel modo in cui Excel pensa a tutte le formule ma soprattutto per chi utilizza Excel.
Inizialmente, quando la Microsoft ha annunciato la disponibilità del nuovo motore di calcolo, le funzioni a matrice dinamica erano solo sei. Oggi le funzioni che sfruttano il nuovo motore di calcolo sono tante.
Ecco un elenco delle principali delle funzioni a matrice dinamica suddivise per libreria di funzioni e per versione da cui è disponibile.
Funzioni a matrice dinamica: le nuove Funzioni di testo
Versione 2021
- MATRICE.A.TESTO (ARRAYTOTEXT in inglese): restituisce una matrice di valori di testo da qualsiasi intervallo specificato.
- VALORE.A.TESTO (VALUETOTEXT in inglese): restituisce testo da un valore specificato.
Versione 2024
- DIVIDI.TESTO (TEXTSPLIT in inglese): divide il testo in righe o colonne utilizzando i delimitatori.
- TESTO.PRECEDENTE (TEXTBEFORE in inglese): restituisce il testo che si trova prima dei caratteri di delimitazione o la stringa specificata.
- TESTO.SUCCESSIVO (TEXTAFTER in inglese): restituisce il testo che si trova dopo i caratteri di delimitazione o la stringa specificata.
Microsoft 365
- REGEX.TEST (REGEXTEST in inglese): controlla se l’input corrisponde al criterio e restituisce VERO o FALSO.
- REGEX.ESTRAI (REGEXEXTRACT in inglese): estrae le stringhe che, all'interno del testo fornito, corrispondono allo schema di estrazione specificato.
- REGEX.SOSTITUISCI (REGEXREPLACE in inglese): sostituisce con il testo di sostituzione le stringhe che, all'interno del testo fornito, corrispondono al criterio specificato.
- RILEVALINGUA (DETECTLANGUAGE in inglese): identifica la lingua di un testo specificato.
- TRADUCI (TRANSLATE in inglese): traduce un testo da una lingua a un'altra usando il servizio Microsoft Translation.
Funzioni a matrice dinamica: le nuove funzioni di Ricerca e riferimento
Versione 2021
- CERCA.X (XLOOKUP in inglese): esegue la ricerca in un intervallo o in una matrice e restituisce l’elemento corrispondente di una seconda matrice o intervallo. Se non trova una corrispondenza restituisce la corrispondenza più vicina (approssimativa).
- CONFRONTA.X (XMATCH in inglese): restituisce la posizione relativa di un elemento in una matrice o in un intervallo di celle.
- DATI.ORDINA (SORT in inglese): consente di ordinare il contenuto di un intervallo o di una matrice.
- DATI.ORDINA.PER (SORTBY in inglese): consente di ordinare il contenuto di un intervallo o di una matrice in base ai valori in un intervallo o una matrice corrispondente.
- FILTRO (FILTER in inglese): consente di filtrare un intervallo di dati in base a criteri definiti.
- UNICI (UNIQUE in inglese): restituisce un elenco di valori univoci in un elenco o un intervallo.
Versione 2024
- A.COL (TOCOL in inglese): restituisce la matrice in una singola colonna.
- A.RIGA (TOROW in inglese): restituisce la matrice in una singola riga.
- A.CAPO.RIGA (WRAPROWS in inglese): trasforma una matrice di righe in una matrice a due dimensioni distribuendo i valori in righe in base un numero specificato di elementi.
- A.CAPO.COL (WRAPCOLS in inglese): trasforma una matrice di colonne in una matrice a due dimensioni distribuendo i valori in colonne in base un numero specificato di elementi.
- ESCLUDI (DROP in inglese): consente di escludere un numero specificato di righe o colonne dall'inizio o dalla fine di una matrice.
- ESPANDI (EXPAND in inglese): espande una matrice in base alle dimensioni di riga e colonna specificate.
- IMMAGINE (IMAGE in inglese): restituisce un'immagine da un'origine specificata.
- INCLUDI (TAKE in inglese): restituisce un numero specificato di righe o colonne all'inizio o alla fine di una matrice.
- SCEGLI.COL (CHOOSECOLS in inglese): restituisce le colonne specificate da una matrice.
- SCEGLI.RIGA (CHOOSEROWS in inglese): restituisce le righe specificate da una matrice.
- STACK.ORIZ (HSTACK in inglese): accoda le matrici orizzontalmente e in sequenza per restituire una matrice più grande.
- STACK.VERT (VSTACK in inglese): accoda le matrici verticalmente e in sequenza per restituire una matrice più grande.
Microsoft 365
- ESCLUDI.VUOTE (TRIMRANGE in inglese): esclude le righe o le colonne vuote in un intervallo o in una matrice.
- RAGGRUPPAPER (GROUPBY in inglese): consente di aggregare, ordinare e filtrare i dati in base ai campi specificati.
- PIVOTPER (PIVOTBY in inglese): consente di aggregare, ordinare e filtrare i dati in base ai campi di riga e colonna specificati.
Funzioni a matrice dinamica: le nuove funzioni Logiche
Versione 2021
- LET: assegna i risultati dei calcoli ai nomi. Utile per archiviare valori e calcoli intermedi definendo i nomi all’interno della formula. Questi nomi sono validi solo all’interno della funzione LET.
Versione 2024
- LAMBDA: consente di creare funzioni personalizzate, riutilizzabili, utilizzando un nome descrittivo.
Funzioni di supporto per la funzione LAMBDA:
- MAKEARRAY: restituisce una matrice calcolata di una riga e di una dimensione di colonna specificate applicando una funzione LAMBDA.
- MAP: restituisce una matrice costituita dalla mappatura di ogni valore (di una matrice) a un nuovo valore applicando un'espressione LAMBDA per creare un nuovo valore.
- PERCOL (BYCOL in inglese): applica un'espressione LAMBDA a ogni colonna e restituisce una matrice dei risultati.
- PERRIGA (BYROW in inglese): applica un'espressione LAMBDA a ogni riga e restituisce una matrice dei risultati.
- REDUCE: riduce una matrice a un valore accumulato tramite l’applicazione di una funzione LAMBDA a ogni valore e restituendo il valore totale nell'accumulatore.
- SCAN: analizza una matrice tramite l’applicazione di una funzione LAMBDA a ogni valore e restituisce una matrice contenente ogni valore intermedio.
Funzioni a matrice dinamica: le nuove funzioni Informative
Versione 2024
Funzione di supporto per la funzione LAMBDA:
- ISOMITTED: controlla se il valore in un'espressione LAMBDA è mancante e restituisce VERO o FALSO.
Funzioni a matrice dinamica: le nuove funzioni Matematiche e trigonometriche
Versione 2021
- MATR.CAUSALE (RANDARRAY in inglese): restituisce una matrice di numeri casuali (compresi tra 0 e 1). È possibile indicare il numero di righe e colonne da restituire, il valore minimi o massimo e se restituire numeri interi o decimali.
- SEQUENZA (SEQUENCE in inglese): genera un elenco di numeri sequenziali in una matrice (ad esempio 1, 2, 3, 4, 5).
Microsoft 365
- PERCENTUALE.DI (PERCENTOF in inglese): somma i valori del sottoinsieme e divide il risultato per tutti i valori.
Sebbene, ciascuna di queste funzioni non verrà trattata in questo articolo nel dettaglio, l’obiettivo è mostrare che la nuova capacità di espansione ora viene estesa anche a molte funzioni esistenti.
Quali funzioni è possibile espandere in Excel?
Non tutte le funzioni sono soggette all’espansione.
Questo dipende dagli argomenti di ciascuna funzione.
Ad esempio, le funzioni standard (SOMMA, MEDIA, MAX, MIN) non si espandono da sole.
Questo perché queste funzioni accettano un intervallo di valori e restituiscono sempre un singolo valore.
In generale, qualsiasi funzione contenente un argomento che accetta un singolo valore è probabile che si espanda. Quel singolo valore è definito tecnicamente come scalare.
Prendiamo come esempio la funzione CERCA.VERT.
Il primo argomento della funzione CERCA.VERT rappresenta il valore da cercare.
In genere, questo è un singolo valore (ovvero uno scalare).
Il risultato appena ottenuto è valido in tutte le versioni di Excel.
Ora, inseriamo due valori come primo argomento della funzione CERCA.VERT (questo non è possibile nella versione Excel 2019 ed in quelle precedenti).
In questo caso, la formula si espanderà alle celle in basso e restituirà il risultato per ciascuno dei valori di ricerca.
In generale, si verifica un’espansione ogni volta che utilizziamo più scalari.
Passiamo ad un altro esempio.
Prendiamo in considerazione i dati presenti nell’intervallo di celle da A2 a B11 in cui vengono riportate le quantità prodotte da alcune regioni.
Supponiamo che l'obiettivo sia calcolare la quantità totale per ciascuna regione.
Ancora una volta analizziamo due differenti scenari:
- prima delle funzioni a matrice dinamica
- e con le funzioni a matrice dinamica.
Prima delle funzioni a matrice dinamica …
Per calcolare le quantità prodotte da ciascuna regione, utilizziamo la funzione SOMMA.SE.
Inseriamo la seguente formula nella cella E2.
Per calcolare il risultato per ogni regione, la formula precedente deve essere copiata nelle celle sottostanti.
Pertanto, ciascuna delle celle dell'intervallo da E2 ad E5 conterrà una formula come mostrato nella seguente immagine.
In ciascuna formula, l'unico argomento che varia è il criterio; questo è necessario per fare riferimento rispettivamente alle celle D3, D4 e D5.
Con le funzioni a matrice dinamica …
Grazie al nuovo motore di calcolo di Excel è possibile ottenere più risultati da una singola formula.
Invece di quattro formule, una per ogni cella, è sufficiente inserirne una per avere i risultati in quattro celle come dimostrato nel seguente esempio.
Rispetto alla soluzione che non prevedeva l’utilizzo delle matrici dinamiche, in questo esempio, invece di un singolo valore (come criterio), abbiamo fornito una matrice di valori (le celle da D2 a D5).
Pertanto, Excel ha eseguito tutti e quattro i calcoli e ha riversato i risultati nelle celle da E2 a E5.
Inoltre, rispetto alla formula precedente non è stato necessario utilizzare i dollari.
Riferimenti assoluti e riferimenti relativi con le funzioni a matrice dinamica
Sono numerose le novità che derivano dalla modifica del motore di calcolo di Excel introdotto dalla Microsoft.
Tra queste anche un nuovo modo di usare i riferimenti di cella assoluti, relativi o misti.
Infatti, con l'utilizzo delle funzioni a matrice dinamica, non è più necessario bloccare gli intervalli con il simbolo del dollaro ($).
I riferimenti relativi e assoluti diventano meno importanti o meglio obsoleti.
Grazie al nuovo approccio "una formula, più risultati", non è più necessario bloccare gli intervalli con il simbolo del dollaro ($) poiché la formula si trova in una sola cella.
Vediamo alcuni esempi (attraverso un confronto tra le diverse versioni) per capire meglio.
Riferimenti assoluti
Riferimenti relativi
Riferimenti misti
L’utilizzo dei riferimenti di cella assoluti, relativi o misti è sempre stato uno dei motivi di confusione soprattutto per gli utenti di Excel meno esperti.
Ora questo non sarà più un problema!
Grazie alle funzioni a matrice dinamica sarà possibile ottenere risultati corretti senza usare il dollaro!
Il simbolo del cancelletto (#) nelle funzioni a matrice dinamica
Perché utilizzare il simbolo del cancelletto (#) nelle funzioni a matrice dinamica?
Riprendiamo un esempio precedente.
Inseriamo nella cella D2 la funzione UNICI in modo da ottenere i valori univoci delle regioni presenti nell’intervallo di celle A2:A11.
Poiché si tratta di una funzione a matrice dinamica, la formula riversa i risultati nelle celle sottostanti.
A questo punto possiamo riscrivere la funzione SOMMA.SE per fare riferimento a tutte le celle dell'intervallo di espansione utilizzando il simbolo del cancelletto (#).
Il simbolo del cancelletto # rappresenta una nuova metodologia di riferimento introdotta dalla Microsoft.
Utilizzando D2#, facciamo riferimento a tutte le celle presenti nell'intervallo di espansione (D2, D3, D4 e D5).
Tuttavia, è possibile fare riferimento all'intero intervallo di espansione, utilizzando D2#, solo se la cella contiene una funzione a matrice dinamica.
Un vantaggio significativo è che se l'intervallo di espansione cambia dimensione, la funzione UNICI e la funzione SOMMA.SE si espandono automaticamente per includere il nuovo valore presente nell'intervallo come dimostrato dalla formula inserita nella cella E2.
Matrici costanti e matrici dinamiche in Excel
Prima dell’introduzione delle matrici dinamiche, in Excel venivano utilizzate le matrici costanti.
Sebbene il discorso delle matrici possa risultare piuttosto complicato in realtà è più semplice di quanto possa sembrare.
Vediamo qualche esempio per capire come funzionano le matrici in Excel.
Utilizziamo la funzione CERCA.VERT inserendo la seguente formula nella cella B10.
Per ottenere i restanti risultati è necessario estendere ciascuna formula e modificarne l’indice.
Qualora volessi inserire più valori puoi sfruttare le parentesi graffe.
In questo caso, se utilizzi la versione di Excel 2019 o precedenti, dovrai:
- seleziona l’intervallo di celle da B10 a E10
- inserisci la formula in basso
- utilizza la combinazione di tasti Ctrl + MAIUSC + Invio
Il risultato sarà il seguente:
Nella formula precedente, i valori inseriti tra le parentesi graffe rappresentano una matrice costante.
Inserendo la formula precedente in una versione di Excel successiva alla 2019, il risultato sarà il seguente:
Con il vecchio metodo è necessario utilizzare più formule per ottenere questo risultato. Al contrario, con le funzioni a matrici dinamica possiamo usarne solo una.
Excel utilizza i valori inseriti tra le parentesi graffe (2, 3, 4 e 5 ovvero la matrice costante) e riversa i risultati nelle celle C10, D10 e E10.
Infine, ecco una ulteriore importante osservazione.
Con le matrici costanti, le colonne sono separate dalle barre rovesciate (in inglese backslash) mentre le righe sono separate da punti.
Cerchiamo di capire meglio quanto appena accennato attraverso due differenti esempi nelle differenti versioni di Excel.
Per distribuire i valori tra le righe usiamo il punto come separatore tra i valori.
Mentre, per distribuire i valori tra le colonne usiamo la barra rovesciata come separatore tra i valori.
Di seguito l’esempio precedente con l’utilizzo dei punti.
Le funzioni a matrice dinamica ed il simbolo della chiocciola @
Il simbolo della chiocciola "@" viene già utilizzato nelle tabelle per fare riferimento ad elementi presenti nella stessa riga.
Pertanto, nelle tabelle i riferimenti strutturati hanno già incorporata l'intersezione implicita.
Tuttavia, il simbolo della chiocciola "@" può essere anche utilizzato per far funzionare Excel nel vecchio modo: prima dell’introduzione delle funzioni a matrice dinamica.
Ripartiamo dall'esempio visto all’inizio dell’articolo.
Per fare in modo che Excel restituisca solo i valori presenti nella stessa riga, possiamo aggiungere il simbolo della chiocciola utilizzando così il vecchio metodo di intersezione implicita.
Perché dovresti di utilizzare questo simbolo se il motore di Excel è cambiato?
Lo so che un ritorno al vecchio modo di lavorare con le formule di Excel potrebbe sembrarti improbabile. Tuttavia, il simbolo della chiocciola serve semplicemente a garantire che le formule create nella versione di Excel 2019 o in quelle precedenti continuino a calcolare lo stesso risultato.
Pertanto, il simbolo della chiocciola garantisce la retrocompatibilità tra versioni di Excel.
In altri termini, ciò significa che le cartelle di lavoro create nelle versioni precedenti di Excel ma aperte nella versione più recente non sfruttano la capacità di espandersi.
Conclusione
Immagino che tutti questi nuovi concetti possano sembrarti inizialmente confusi.
Il cambiamento nel motore di calcolo di Excel è una vera e propria rivoluzione che ha cambiato il modo di lavorare con Excel.
Sono sicuro che presto riscurirai ad apprezzare questi nuovi poteri e ti renderai conto che Excel è diventato più facile da usare.
Vuoi semplificare e velocizzare il tuo lavoro grazie alle nuove e potenti funzioni a matrice dinamica di Microsoft Excel 365?
Approfitta del nuovo corso su Dynamic Arrays per Excel 365!
Impara a gestire le nuove funzioni a matrice dinamica e scopri come queste funzioni possono rivoluzionare il tuo modo di lavorare!
