Utilizzo della formula matrice con Excel

Spesso capita di dover estrarre dati specifici da tabelle create con Excel, ma talvolta non è così facile come sembra arrivare ai risultati voluti. Nei casi più complessi si è costretti a ricorrere all’aiuto di funzioni create in VBA (Visual Basic for Applications), ma non sempre è necessario.
Le “Formule Matrice” permettono infatti di ottenere risultati sorprendenti.

• Matrice: è un intervallo rettangolare di valori (o di formule) che vengono trattati da Excel come unica entità.
• Formula matrice: è una formula che ha come risultato una matrice ed agisce su uno o più insiemi di valori denominati argomenti matrice.

In generale le formule in forma di matrice vengono create allo stesso modo delle altre formule ma, prima di utilizzarle, dobbiamo ricordare alcune regole fondamentali:

• Ogni argomento contenuto in una matrice deve avere lo stesso numero di righe e colonne.
• La matrice deve essere inserita tramite la combinazione Ctrl+Shift+Invio (le parentesi graffe {} che contengono la formula non devono essere inserite manualmente!).

Per capire come funziona una matrice in Excel, possiamo esaminare la seguente tabella che rappresenta un elenco di iscritti ad una palestra.

(Tab. 1)

Calcolo di più risultati
Per calcolare il risultato nella colonna “G” potremmo utilizzare il metodo “tradizionale” scrivendo in G2 il prodotto E2*F2 e poi ricopiando in basso la formula, oppure, sfruttando le matrici:

• selezionare l’intervallo G2:G9
• inserire la formula “=E2:E9*F2:F9”
• confermare con Ctrl+Shift+Invio (tutte le celle verranno automaticamente avvalorate con il risultato)
  

Gli elementi corrispondenti degli intervalli E2:E9 e F2:F9 vengono moltiplicati ed il risultato viene inserito nella corrispondente cella dell’intervallo G2:G9.

E’ obbligatorio ricordae che, essendo l’intervallo matrice un’unica entità (caratterizzato dalle parentesi graffe), non è possibile inserire celle all’interno dell’intervallo, né modificare singolarmente una cella: può essere editato solo l’intervallo matrice completo.

Nella formula precedente il risultato si presenta sotto forma di matrice estesa in un intervallo
comprendente più celle, ma è possibile ottenere anche un risultato singolo, elaborando gli
elementi della matrice stessa, tramite l’utilizzo delle funzioni di Excel.

Calcolo di un singolo risultato
Per ottenere, ad esempio, il totale globale dei pagamenti useremo la formula:
=SOMMA(E2:E9*F2:F9)
Quando Excel esegue E2:E9*F2:F9 crea una matrice nascosta che contiene il risultato del prodotto. Con la funzione SOMMA vengono poi sommati tutti gli elementi di tale matrice.
Rifacendoci sempre alla Tab. 1, supponiamo di voler contare quante persone abitano a Pistoia ed hanno più di 20 anni:
=SOMMA((B2:B9=”Pistoia”)*(C2:C9>20))
oppure quante persone hanno l’età compresa fra i 10 ed i 30 anni e sono iscritte da più di 3 anni:
=SOMMA((C2:C9>9)*(C2:C9<31)*(F2:F9>3))
Ovviamente la funzione SOMMA può essere sostituita con MEDIA, MIN, MAX, ecc. In questo caso però dobbiamo aggiungere nella formula la funzione “SE” in quanto non si tratta più della somma degli elementi della matrice, ma dell’estrapolazione di un singolo elemento della tabella con caratteristiche specifiche.
Calcoliamo per esempio l’età massima di chi abita a Pistoia:
=MAX(SE(B2:B9=”Pistoia”;C2:C9;0))
oppure l’età media delle donne:
=MEDIA(SE(D2:D9=”F”;C2:C9))
oppure l’età minima degli uomini che hanno più di 3 mesi di iscrizione:
=MIN(SE(D2:D9=”M”;SE(F2:F9>3;C2:C9)))
Come abbiamo visto, una volta capito il procedimento, è possibile sbizzarrirsi ed ottenere il risultato voluto con pochi sacrifici.
Questo metodo di calcolo ha purtroppo anche qualche svantaggio in quanto troppe formule di matrici rallentano il ricalcolo, l’apertura ed il salvataggio dei fogli di lavoro che le contengono.
Vanno quindi usate con cura ed occorre sempre valutare se sono strettamente necessarie, oppure se è possibile usare, in alternativa, altre funzioni di Excel.