Le formule matriciali in Excel sono uno strumento estremamente potente e uno dei più difficili da padroneggiare. Una singola formula matriciale di Excel può eseguire più calcoli e sostituire migliaia di formule standard. Il 90% degli utenti di Excel non ha però mai utilizzato le funzioni matriciali nei loro fogli di lavoro semplicemente perché ha timore di iniziare ad impararle.
In effetti, le formule sotto forma di matrice sono una delle caratteristiche più disorientanti di Excel. L’obiettivo di questo tutorial è quello di rendere la curva di apprendimento più semplice e agevole possibile.
Cosa sono le matrici in Excel?
Prima di iniziare a parlare delle funzioni e delle formule matriciali di Excel, cerchiamo di capire cosa significhi il termine “matrice” (array). Essenzialmente, una matrice in Excel è un insieme di elementi. Gli elementi possono essere testo o numeri e possono risiedere in una singola riga o colonna, o in più righe e colonne.
Ad esempio, dovendo mettere la propria lista della spesa settimanale in un formato matriciale di Excel, questa sarebbe simile alla seguente:
{"Latte"\ "Uova"\ "Burro"\ "Cereali"}
Quindi, selezionando le celle da A1 a D1 e digitando nella barra della formula la matrice sopra indicata preceduta dal segno di uguale (=) e premendo la combinazione di tasti CTRL + MAIUSC + INVIO, si otterrà il seguente risultato:
Quello che avete appena fatto è creare una matrice unidimensionale orizzontale. Nulla di terribile finora, giusto?
Ora, cos’è una formula matriciale di Excel? La differenza tra una formula matriciale e una formula standard di Excel è che una formula matriciale elabora diversi valori invece di uno solo. In altre parole, una formula matriciale in Excel valuta tutti i singoli valori di un array ed esegue diversi calcoli che possono coinvolgere uno o più elementi della matrice secondo le condizioni espresse nella formula.
Una formula matriciale, non solo può considerare diversi valori simultaneamente, può anche restituire diversi valori alla volta. Quindi, i risultati restituiti da una formula matriciale di Excel rappresentano anch’essi una matrice.
Le formule matriciali sono disponibili in tutte le versioni di Excel: Excel 2013, Excel 2010, Excel 2007 e precedenti.
E ora, sembra essere giunto il momento di creare la prima formula matriciale!
Semplice esempio di formula matriciale di Excel
Supponiamo di avere i pezzi venduti in colonna B, i loro prezzi nella colonna C, e che si desideri calcolare il totale di tutte le vendite.
Naturalmente, nulla vi vieta di calcolare i subtotali in ogni riga con qualcosa di semplice come = B2 * C2 e poi sommare questi valori:
Tuttavia, una formula matriciale può permettervi di risparmiare qualche battuta sui tasti in quanto è Excel che memorizza per voi i risultati intermedi piuttosto che doverli scrivere in una colonna aggiuntiva. Quindi, tutto quello che serve è una sola formula matriciale e 2 passaggi veloci:
- Selezionate una cella vuota e immettete la seguente formula:
=SOMMA(B2:B7*C2:C7)
- Premete CTRL + MAIUSC + INVIO per completare la formula matriciale.
Una volta fatto questo, Microsoft Excel circonda la formula con {parentesi graffe}, che altro non sono che una indicazione visiva della presenza di una formula matriciale.
Quello che fa la formula è moltiplicare i valori di ogni singola riga della matrice specificata (celle B2:C7), calcolare i totali parziali e restituire il totale:
Questo semplice esempio mostra come può essere potente una formula matriciale di Excel. Basti pensare quanto tempo è possibile risparmiare inserendo una formula matriciale in una singola cella quando si lavora con centinaia e migliaia di righe di dati.
Perché usare formule matriciali in Excel?
Le formule matriciali di Excel sono lo strumento più comodo per eseguire calcoli sofisticati e portare a termine compiti complessi. Una singola formula matriciale può sostituire centinaia di formule standard.
Vedremo diversi esempi del loro utilizzo nel prosieguo di questo articolo e nei seguenti che pubblicheremo sul medesimo argomento.
Come inserire formule matriciali in Excel (Ctrl + Maiusc + Invio)
Avrete già intuito che per inserire formule matriciali in Excel è richiesta la combinazione di tasti CTRL + MAIUSC + INVIO. E’ un “tocco magico” che trasforma una formula standard in una formula matriciale.
Ci sono 4 cose importanti da tenere a mente:
- Una volta che abbiamo finito di digitare la formula e premuto Ctrl + Maiusc + Invio, Excel racchiude automaticamente la formula tra parentesi graffe {}. Quando si seleziona questa cella (oppure celle), è possibile vedere le parentesi graffe nella barra della formula, il che ci fornisce un indizio che è presente una formula matriciale.
- Digitando manualmente le parentesi graffe intorno ad una formula, questa non verrà convertita in una formula matriciale. È necessario premere Ctrl + Maiusc + Invio per completare una formula matriciale.
- Ogni volta che si modifica una formula matriciale, le parentesi graffe scompaiono e si deve premere nuovamente Ctrl + Maiusc + Invio per salvare le modifiche apportate alla formula.
- Se si dimentica di premere Ctrl + Maiusc + Invio, la formula si comporta come un formula standard e Excel processerà solo il primo valore (o valori) presenti nella matrice specificata.
Utilizzare il tasto F9 per valutare porzioni di una formula matriciale
Quando si lavora con formule matriciali in Excel, è possibile osservare come esse calcolino e memorizzino i loro elementi (array interni) per mostrare il risultato finale che si vede in una cella. Per fare questo occorre selezionare uno o più argomenti tra parentesi di una funzione, e quindi premere il tasto F9.
Formule matriciali a “cella singola” e “multi cella”
Le formule matriciali di Excel possono restituire il risultato in una singola cella oppure in più celle. Una formula matriciale inserita in un intervallo di celle è chiamato formula multi-cella. Una formula matriciale residente in una singola cella è detta formula a cella singola.
Esistono alcune funzioni matriciali di Excel che sono progettate per restituire matrici multi-cella, ad esempio MATR.TRASPOSTA, TENDENZA, FREQUENZA, REGR.LIN, ecc.
Altre funzioni, come SOMMA, MEDIA, AGGREGA, MAX, MIN, sono invece in grado di calcolare espressioni matriciali quando sono inserite in una singola cella utilizzando Ctrl + Maiusc + Invio.
I seguenti esempi mostrano come utilizzare formule matriciali a cella singola oppure multi cella.
Esempio 1. Una formula matriciale a cella singola
Supponiamo di avere due colonne indicanti il numero di articoli venduti in 2 mesi diversi, diciamo le colonne B e C, e che si voglia trovare il massimo incremento delle vendite.
Normalmente, avremmo inserito una colonna aggiuntiva, poniamo la colonna D, dove calcolare la variazione delle vendite per ogni prodotto, utilizzando una formula come =C2-B2, per poi trovare il valore massimo di quella colonna aggiuntiva =MAX(D:D).
Una formula matriciale di Excel non ha bisogno di una colonna aggiuntiva in quanto memorizza perfettamente i risultati intermedi. Quindi, è sufficiente inserire la seguente formula e premere Ctrl + Maiusc + Invio:
=MAX(C2:C6-B2:B6)
Esempio 2. Una formula matriciale multi-cella
Nell’esempio iniziale, supponiamo di dover pagare il 10% di tasse per ogni vendita e che si voglia calcolare l’importo dell’imposta per ogni prodotto con una formula.
Selezionate l’intervallo di celle in una colonna vuota, ad esempio D2:D7, e immettere la seguente formula nella barra della formula:
=B2:B7*C2:C7*0,1
Una volta premuto Ctrl + Maiusc + Invio, Excel inserirà un’istanza della formula matriciale in ogni cella dell’intervallo selezionato, ottenendo il seguente risultato:
Esempio 3. Usare una funzione matriciale di Excel per restituire una matrice multi cella
Come già detto, Microsoft Excel fornisce alcune cosiddette “funzioni matriciali” che sono appositamente progettate per lavorare con matrici multi cella. MATR.TRASPOSTA è una di queste funzioni e ci accingiamo ad utilizzarla per trasporre la tabella di cui sopra, vale a dire convertire righe in colonne:
- Selezionate un intervallo di celle vuote in cui desiderate ottenere la tabella trasposta. Dal momento che stiamo convertendo righe in colonne, accertatevi di selezionare tante righe quante sono le colonne della tabella di origine e viceversa. In questo esempio, selezioneremo 7 colonne e 4 righe.
- Premete F2 per entrare in modalità modifica
- Inserite la funzione matriciale =MATR.TRASPOSTA($A$1:$D$7) e premete Ctrl + Maiusc + Invio.
Il risultato dovrebbe essere simile a questo:
Alcuni punti di attenzione
Quando si lavora con le formule matriciali multi cella in Excel, assicuratevi di seguire le seguenti regole per ottenere i risultati corretti:
- Selezionate l’intervallo di celle in cui si desidera l’output dei risultati prima di inserire la formula;
- Per eliminare una formula matriciale multi cella, occorre selezionare tutte le celle che la contengono e premere CANC oppure selezionare l’intera formula nella barra della formula, premere CANC e quindi premere Ctrl + Maiusc + Invio;
- Non è possibile modificare, spostare o eliminare il contenuto di una singola cella in una formula matriciale di Excel. Né è possibile inserire nuove celle o cancellare celle esistenti da una formula matriciale multi cella. Ogni volta che si provasse ad eseguire queste operazioni, Microsoft Excel genererebbe il seguente messaggio di errore: “Impossibile modificare parte di una matrice“;
- Per ridurre una formula matriciale, cioè applicarla ad un minor numero di celle, è necessario prima eliminare la formula esistente e poi inserirne una nuova;
- Per espandere una formula matriciale, cioè applicarla ad un maggior numero di celle, selezionate tutte le celle che contengono la formula attuale, più le celle vuote dove desiderate applicarla. Premete F2 per passare alla modalità di modifica, sistemate i riferimenti nella formula e premete Ctrl + Maiusc + Invio per aggiornarla;
- Non è possibile utilizzare formule matriciali multi cella in una tabella di Excel;
- E’ importante inserire una formula matriciale multi cella in un intervallo di celle della stessa dimensione della matrice che sarà restituita dalla formula. Se la formula matriciale di Excel producesse una matrice più grande dell’intervallo selezionato, i valori in eccesso non verrebbero visualizzati sul foglio di lavoro. Se la matrice restituita dalla formula fosse invece più piccola dell’intervallo selezionato, nelle celle in eccesso apparirebbero degli errori #N/D.
Ovviamente le cose da dire sulle matrici di Excel non sono terminate qua. Ci sono sicuramente molti altri argomenti da approfondire e lo faremo nei prossimi interventi.
Se l’argomento ti interessa, leggi Come usare le matrici e le formule matriciali – Parte 2.
