Come utilizzare le formule di matrice dinamica in Microsoft Excel

Se lavori quotidianamente con i fogli di calcolo, avrai notato che non appena iniziano a crescere in righe e colonne, Le formule classiche di Excel sono un po' carenti. per eseguire calcoli complessi in tutta comodità. È qui che entrano in gioco le formule di matrice dinamica, una di quelle novità che, una volta padroneggiate, vi ritroverete a usare in quasi tutti i libri.

Nelle versioni moderne di Excel (specialmente in Microsoft 365Abbiamo un nuovo motore di calcolo che consente a una singola formula di generare più risultati contemporaneamente, estendendosi a più celle adiacenti senza richiedere la copia manuale. Grazie a questo comportamento, “overflow” di risultati in massaOra è molto più facile ordinare, filtrare, generare elenchi o eseguire calcoli avanzati senza ricorrere a Tricks rare o a combinazioni di tasti come Ctrl+Maiusc+Invio.

Che cosa è l'overflow nelle formule di matrici dinamiche?

Nel nuovo modello di calcolo di Excel, una formula può restituire non solo un valore, ma un intero set organizzato di risultati; questo set è solitamente chiamato array o intervallo di output in overflowQuando ciò accade, Excel posiziona automaticamente quei valori nelle celle vicine, espandendo la formula verso il basso, verso destra o in entrambe le direzioni, a seconda delle dimensioni del risultato.

Ad esempio, quando si scrive la formula =ORDINA(D2:D11,1,-1) In una singola cella (ad esempio, F2), Excel genera un elenco ordinato in ordine decrescente in base all'intervallo D2:D11. Il risultato occupa 10 righe, ma la formula viene inserita solo in una cella; le restanti posizioni vengono riempite automaticamente da Excel utilizzando questo meccanismo di overflow.

Le formule che possono modificare la dimensione del loro risultato in base ai dati di origine sono note come formule di matrice dinamicaQuando queste formule restituiscono un intervallo di risultati che si estende oltre la cella in cui è stata scritta la formula, si dice che la formula è "sovraccaricata" e l'area che occupa è chiamata intervallo di overflow.

In pratica, ciò significa che molte funzioni, come SORT, FILTER, RANDOMAR, SEQUENCE o UNIQUE, sono ora progettate per restituire matrici di dati complete e pronte all'uso, in un modo molto più diretto e leggibile rispetto alle vecchie formule di matrice.

Come si comporta l'intervallo di overflow in Excel

Quando si conferma una formula di matrice dinamica premendo solo il tasto Invio, Excel analizza il risultato e regola automaticamente la dimensione dell'intervallo di output per contenere tutti i valori restituiti dalla formula. Quindi, posiziona ogni elemento dell'array nella cella corrispondente all'interno dell'intervallo eccedente.

Se si scrive una di queste formule su un elenco o una tabella di dati, può essere molto pratico convertire i dati di origine in un Tabella Excel con riferimenti strutturatiLe tabelle si adattano automaticamente quando si aggiungono o si rimuovono righe, quindi le formule di matrice dinamica che le utilizzano si aggiornano senza che sia necessario intervenire.

È importante sapere che le formule di overflow non funzionano all'interno delle tabelle stesse: Non è consentito l'overflow all'interno di una tabella Excel.Invece, dovresti posizionare le formule nella griglia normale (all'esterno della tabella) e utilizzare la tabella solo come origine dati. Le tabelle sono pensate per archiviare record, non per espandere un intero intervallo di output al loro interno.

Ogni volta che si fa clic su una cella all'interno dell'intervallo di overflow, Excel disegna un riquadro evidenziato attorno all'intero set di celle interessate. In questo modo è molto facile individuarle a colpo d'occhio. quanto si estende la formula e quali celle dipendono da essaNon appena si seleziona una cella al di fuori di tale intervallo, il bordo scompare.

Un altro dettaglio importante è che, in un intervallo di overflow, solo la prima cella (quella nell'angolo in alto a sinistra) contiene effettivamente la formula. Le altre celle mostrano i risultati, ma se ne selezioni una, vedrai la formula visualizzata in grigio nella barra della formula. Non sarà possibile modificarlo direttamente.È sempre necessario modificare la cella di origine; dopo averla modificata e premuto Invio, Excel ricalcolerà immediatamente l'intero intervallo eccedente.

Errori di sovrapposizione e messaggio #OVERFLOW!

Affinché una formula di matrice dinamica si espanda senza problemi, Excel necessita che l'area di output sia libera. Se dati, formule o altri elementi occupano una qualsiasi delle celle in cui dovrebbero apparire i risultati, si verificherà un errore. Sovrapposizione dell'intervallo di overflow ed Excel non riesce a completare l'operazione.

In tal caso, invece del risultato previsto, verrà visualizzato un messaggio di errore. #TRABOCCA! nella cella in cui hai inserito la formula. In questo modo Excel ti avvisa che c'è un blocco che impedisce alla matrice di essere inserita in tutte le celle necessarie per contenere i risultati.

Se selezioni la formula in questione, Excel ti mostrerà, con un bordo tratteggiato, l'intervallo in cui intende eccedere, incluse le celle che bloccano il processo. Questa visualizzazione ti consente di individuare facilmente le cellule problematiche in modo che possano essere svuotati o il loro contenuto spostato in un'altra posizione.

Una volta rimossi i dati che impediscono l'espansione (ad esempio, valori non corretti o formule precedenti), quando si ricalcolerà il foglio, Excel causerà l'overflow della formula come previsto. In molte situazioni, è sufficiente eliminare un paio di celle per far scomparire all'istante l'errore #OVERFLOW!.

È anche possibile che la formula stessa sia progettata in modo errato e restituisca un array troppo grande per lo spazio disponibile. In questi casi, è consigliabile rivedere la formula. sia la logica della formula che lo spazio libero attorno per garantire che la gamma di output possa aumentare in base alle necessità.

Differenze tra le formule di matrice dinamica e le formule CSE legacy

Prima dell'avvento delle matrici dinamiche, le formule delle matrici venivano introdotte con la famosa combinazione Ctrl+Maiusc+Invio (CSE)Queste formule legacy sono ancora supportate in Excel per motivi di compatibilità con le versioni precedenti, ma l'approccio consigliato oggi è quello di lavorare con il nuovo modello dinamico, che è più semplice e meno soggetto a errori.

Uno dei grandi vantaggi delle formule di matrice dinamica è che È sufficiente inserire la formula una sola volta nella cella in alto a sinistra.I risultati rimanenti vengono visualizzati automaticamente grazie all'overflow. Nelle vecchie formule CSE, era necessario selezionare l'intero intervallo in cui si desiderava visualizzare i risultati e quindi confermare la formula con Ctrl+Maiusc+Invio.

Le formule dinamiche possono anche adattare le proprie dimensioni quando i dati di origine cambiano. Se si aggiungono righe all'origine dati, l'array può espandersi; se si eliminano dati, può contrarsi, il tutto senza dover modificare manualmente l'intervallo. Con gli array CSE legacy, se l'area di ritorno era troppo piccolaI risultati sono stati troncati e, se eccessivi, si potrebbero verificare errori come #N/D.

Un'altra interessante differenza è che molte funzioni classiche, come RAND, ROW o COLUMN, ora vengono valutate in un contesto a cella singola (1x1). Se si desidera generare più risultati casuali o sequenze di numeri basate su righe e colonne, si consiglia di utilizzare funzioni come MATRICE o SEQUENZA CASUALEche sono progettati per restituire matrici complete e funzionano perfettamente con il nuovo motore dinamico.

Inoltre, in passato si verificava un fenomeno noto come "interruzione CSE", in base al quale alcune formule di matrice legacy che dipendevano l'una dall'altra potevano essere calcolate indipendentemente e restituire risultati incoerenti o difficili da correggereCon le matrici dinamiche, questo comportamento scompare: se sono presenti riferimenti circolari, Excel li contrassegnerà come tali anziché interrompere la logica della formula.

Anche la modifica di una formula di matrice dinamica è più comoda. È sufficiente modificare la cella di origine e il resto si aggiorna automaticamente; con le formule CSE, questo era necessario. modificare l'intero intervallo interessato in una voltaQuesta operazione diventava piuttosto complessa con intervalli di grandi dimensioni. Inoltre, quando un foglio conteneva un intervallo attivo con una formula CSE, non era possibile inserire o eliminare righe o colonne che interferivano con tale intervallo finché la formula di matrice ereditata non veniva rimossa o modificata.

Utilizzo delle funzioni chiave con array dinamici

Tra le funzioni più potenti che sfruttano gli array dinamici ci sono FILTRO, ORDINA, ORDINA PER, UNICO, ARRAY CASUALE e SEQUENZARestituiscono tutti intervalli completi, quindi si adattano perfettamente al comportamento di overflow e sono disponibili strumenti utili come Formula di Excel Bot che ne rendono più facile l'utilizzo.

Con la funzione FILTER, ad esempio, è possibile estrarre solo le righe che soddisfano determinati criteri da una tabella dati, restituendo un set di risultati che si aggiorna automaticamente quando i dati di origine cambiano. Questa funzione si combina molto bene con UNIQUE, che consente ottenere elenchi di valori senza duplicati da colonne con molti dati ripetuti.

La funzione MATRIZALEAT genera un intervallo di numeri casuali in un blocco, ideale per simulazioni o test; SECUENCIA, invece, restituisce matrici con serie di numeri consecutivi, consentendo di personalizzare l'incremento e la dimensione della matrice in base alle proprie esigenze. Entrambe si basano sul nuovo modello di matrice e sono progettate per riempire grandi aree del foglio contemporaneamente.

Infine, SORT e SORTBY semplificano notevolmente la creazione di elenchi ordinati da colonne o tabelle esistenti. Invece di utilizzare l'ordinamento manuale o complesse combinazioni di funzioni, ora è possibile scrivere una singola formula che restituisce i dati ordinati e si adatta automaticamente alle variazioni dei valori originali.

Tutte queste funzioni si combinano tra loro e, grazie all'overflow, consentono di creare soluzioni molto avanzate senza la necessità di macro o formule di matrice legacy difficili da gestire e, per automatizzare le cartelle di lavoro, Script di Office in Excel Web può essere di grande aiuto.

Differenze di calcolo tra matrici dinamiche e matrici ereditate

Se stai ancora lavorando con vecchi libri che utilizzano formule di matrice CSE, è importante tenere presente che quando li converti nel loro la dinamica equivalente può modificare leggermente il comportamento In alcuni casi. Sebbene nella maggior parte degli scenari la conversione sia diretta, è consigliabile rivedere il risultato.

Il modo usuale per convertire da un array legacy a uno dinamico è quello di individuare la prima cella dell'intervallo dell'array, copiare il testo della formula, eliminare l'intero vecchio intervallo e quindi riscrivi la formula solo nella cella in alto a sinistra Utilizzando il nuovo approccio, Excel gestirà automaticamente il superamento dei risultati.

Durante la transizione, prestare particolare attenzione alle funzioni che in precedenza si basavano sul comportamento di intersezione implicito o su valutazioni speciali all'interno delle formule CSE. Excel ora ha la operatore di intersezione implicito (@)che serve a replicare il vecchio comportamento in certi casi, ma la raccomandazione generale rimane quella di riscrivere esplicitamente le formule sfruttando le nuove funzioni.

Excel offre anche un supporto limitato quando una matrice dinamica fa riferimento a dati presenti in un'altra cartella di lavoro. Il corretto funzionamento è garantito solo quando Entrambi i file sono aperti contemporaneamenteSe si chiude la cartella di lavoro di origine, le formule di matrice dinamica collegate potrebbero restituire l'errore #REF! quando si tenta di aggiornare, quindi è importante tenerlo presente per i modelli complessi con più riferimenti esterni; in questi casi, vedere come salvare e condividere cartelle di lavoro Per evitare problemi.

Sebbene le formule CSE continueranno a esistere per garantire la compatibilità con le versioni precedenti, Microsoft stessa consiglia di smettere di crearle nei nuovi progetti e di optare per array dinamici. Leggibilità, facilità di manutenzione e robustezza Queste nuove formule le rendono l'opzione preferita per il futuro.

Intervallo di overflow e modifica pratica sul foglio

Quando una formula trabocca, l'area che occupa è nota come intervallo di traboccamento. In questo intervallo, come già accennato, solo la prima cella contiene la formula vera e propria. Le celle rimanenti visualizzano i risultati, ma sono "controllati" dalla cella di origine, il che significa che l'intera matrice si comporta come un'unità.

Ad esempio, se scrivi la funzione =MAIUSCOLA(E7:E19) In una singola cella, vedrai come Excel restituisce, su più righe, il testo di quell'intervallo convertito in maiuscolo. Se selezioni una qualsiasi delle celle nell'intervallo di output, vedrai il risultato, ma quando guardi la barra della formula, noterai che il contenuto appare in grigio, a indicare che Non è direttamente modificabileEventuali modifiche devono essere apportate nella cella in cui è stata scritta per la prima volta la formula.

Questo comportamento ha un chiaro vantaggio: impedisce di modificare accidentalmente solo una parte dell'intervallo lasciando invariato il resto, il che spesso porta a errori difficili da individuare. Se è necessario modificare la formula, è sufficiente modificarla una sola volta nella cella di origine, premere Invio ed Excel la aggiornerà automaticamente. ricalcola l'intero blocco in modo coerente.

Nell'uso quotidiano, questo influisce anche sul modo in cui si eliminano o si spostano i dati. Se si tenta di eliminare una singola cella all'interno dell'intervallo di overflow, Excel avviserà che si sta modificando una matrice dinamica. Per evitare problemi, di solito è meglio eliminare o tagliare l'intera cella. direttamente la cella sorgente, che trascina il resto dell'intervallo.oppure modificare la formula per restituire una matrice di dimensioni diverse.

Quando si combinano intervalli di overflow con altre formule, tenere presente che queste celle vengono ricalcolate insieme. Qualsiasi riferimento a una matrice dinamica deve essere fatto con attenzione, sfruttando il nuovo motore senza creare riferimenti circolari inutili o conflitti con altre parti del foglio.

Esempi di formule di matrice avanzate con dati reali

Molte formule di matrice classiche hanno ancora senso, soprattutto quando si lavora con intervalli ampi e si ha bisogno operazioni condizionali complesseDiamo un'occhiata ad alcuni esempi tipici che potrebbero risultare molto utili nei report e nei modelli.

Immagina di avere un intervallo denominato Dati che include alcuni valori di errore come #N/D. Se applichi la funzione SOMMA direttamente a quell'intervallo, otterrai un errore. Per evitare questo problema, puoi utilizzare un array che ignori gli errori con una formula come questa: =SOMMA(SE(ERRORE(Dati);"";Dati)), che crea internamente un array in cui gli errori vengono sostituiti da stringhe vuote prima della somma.

Seguendo la stessa idea, puoi anche contare quanti errori ci sono in un intervallo. Una formula come =SOMMA(SE(ERRORE(Dati);1;0)) Genera un array con 1 in caso di errore e 0 in caso contrario. La somma totale indica il numero di celle errate. Puoi anche semplificarlo in =SOMMA(SE(ERRORE(Dati);1)) e fare un passo avanti verso =SOMMA(SE(ERRORE(Dati)*1)), sfruttando il fatto che TRUE*1 è 1 e FALSE*1 è 0.

Un altro scenario comune è la somma di valori in base a condizioni. Ad esempio, potresti avere un intervallo chiamato Vendite e voler sommare solo i valori positivi utilizzando una formula come =SOMMA(SE(Vendite>0;Vendite))In questo caso, la funzione SE genera un array con i valori che soddisfano la condizione e con valori falsi per i restanti, che SOMMA ignora nella pratica.

Se è necessario applicare più di una condizione alla volta, è possibile moltiplicare le espressioni logiche per emulare un'operazione "AND" e quindi sommarle. Un esempio tipico sarebbe: =SOMMA((Vendite>0)*(Vendite<=5)*(Vendite))Questo calcola la somma delle vendite maggiori di 0 e minori o uguali a 5. Tuttavia, questo approccio richiede che l'intervallo non contenga celle di testo, altrimenti potrebbero verificarsi errori.

Per emulare un “OR”, è possibile utilizzare somme di espressioni logiche: ad esempio, =SOMMA(SE((Vendite<5)+(Vendite>15);Vendite))dove i valori inferiori a 5 o superiori a 15 vengono sommati. In questo modo, è possibile eseguire operazioni avanzate senza utilizzare direttamente le funzioni AND e OR, che restituiscono un singolo valore logico e non un array completo di risultati.

Calcoli statistici e confronti con formule matriciali

Le formule di matrice sono molto utili anche per calcolare le medie o effettuare confronti tra intervalli completiUn esempio classico riguarda il calcolo della media di un set di dati escludendo gli zeri. Se l'intervallo si chiama Vendite, la formula =MEDIA(SE(Vendite<>0;Vendite)) Crea un array con solo i valori diversi da zero e li passa a AVERAGE, omettendo così i record che non forniscono informazioni.

Un altro caso interessante è il confronto di due intervalli della stessa dimensione, ad esempio MyData e YourData. Se si desidera sapere quante celle differiscono tra loro, è possibile utilizzare =SOMMA(SE(DatiMiei=DatiTuoi;0;1))Questa formula genera un array di 0 quando i valori corrispondono e di 1 quando sono diversi, quindi somma i risultati. Se i due intervalli sono identici, la formula restituirà 0.

Questo confronto può essere ulteriormente semplificato con =SOMMA(1*(DatiMiei<>DatiTuoi)), facendo sì che il confronto logico (MyData<>YourData) generi TRUE o FALSE, che vengono poi trasformati in 1 e 0 moltiplicandoli per 1. Ancora una volta, la chiave è sfruttare il comportamento delle espressioni booleane all'interno degli array.

È anche possibile utilizzare formule di matrice per individuare il valore massimo in un intervallo e ottenerne la posizione. Se si dispone di un intervallo denominato Dati, un'opzione è =MIN(SE(Dati=MAX(Dati);RIGA(Dati);»»))Questa formula crea un array in cui solo le celle il cui valore è uguale al massimo contengono il numero di riga; le restanti vengono convertite in una stringa vuota. MIN restituisce quindi il numero di riga più piccolo tra questi candidati, ovvero la prima occorrenza del valore massimo.

Se invece della riga desideri il riferimento completo alla cella, puoi combinare ADDRESS con la logica sopra usando qualcosa come =INDIRIZZO(MIN(SE(Dati=MAX(Dati);RIGA(Dati);»»));COLONNA(Dati))in modo da ottenere un riferimento come “$B$7” che identifica esattamente dove si trova il valore massimo nell'intervallo.

Esempio pratico: vendite per prodotto utilizzando formule matriciali

Per comprendere meglio il potenziale delle formule di matrice, immagina una piccola tabella di vendita di veicoli con colonne per venditore, tipo di veicolo, unità vendute, prezzo unitario e vendite totaliSupponiamo che le colonne C e D contengano rispettivamente il numero di articoli venduti e il prezzo unitario.

Se copi questa tabella in Excel, puoi selezionare l'intervallo E2:E11 e inserire una formula come =C2:C11*D2:D11Nelle versioni precedenti, era necessario confermare la formula con Ctrl+Maiusc+Invio per convertirla in una matrice classica che restituisse la moltiplicazione riga per riga in blocco. Excel calcolava quindi le vendite totali per ogni riga moltiplicando le unità per il prezzo unitario.

Il dettaglio chiave è che dovresti sempre selezionare tutte le celle che conterranno i risultati prima di scrivere la formula matriciale. In caso contrario, potrebbero essere calcolati solo alcuni valori oppure potrebbe essere necessario ripetere il processo più volte, il che risulta inefficiente.

In questo contesto, è anche comune utilizzare una formula matriciale a cella singola per ottenere il totale complessivo di tutte le vendite. Ad esempio, è possibile andare alla cella B13 e immettere =SOMMA(C2:C11*D2:D11)Quando si conferma la formula (nel modello classico con Ctrl+Maiusc+Invio), Excel moltiplica ogni coppia di valori in C e D e somma tutti i prodotti. restituire un singolo valore aggregato.

Sebbene questi esempi si basino sul comportamento ereditato degli array CSE, le idee di base sono le stesse utilizzate con gli attuali array dinamici: eseguire operazioni su intervalli interi contemporaneamente e restituire risultati multipli o aggregati senza la necessità di formule ausiliarie intermedie in ogni riga.

Oggi, molti di questi compiti possono essere risolti combinando funzioni dinamiche e overflow, creando fogli di calcolo più pulito, più facile da leggere e più semplice da manteneresoprattutto quando il volume dei dati aumenta o quando più utenti lavorano sullo stesso libro.

Padroneggiare l'uso delle formule di matrice dinamica in Excel cambia completamente il modo in cui si creano i fogli di calcolo: comprendendo come funziona l'overflow, come si comportano gli intervalli di output, in che modo differiscono dalle vecchie formule CSE e come applicare esempi pratici per ignorare gli errori, eseguire somme condizionali o confrontare intervalli, si finisce per lavorare con modelli molto più flessibili, automatizzati e affidabili, risparmiando tempo a ogni aggiornamento e riducendo al minimo gli errori manuali.