Padroneggiare le formule matriciali di Excel può essere una strada abbastanza lunga e, in alcuni dei recenti articoli che abbiamo pubblicato, abbiamo mosso i primi passi in questa direzione. Per chi volesse ripassare le basi della materia, ecco qualche link interessante:
- Come usare le matrici e le formule matriciali – Parte 1
- Come usare le matrici e le formule matriciali – Parte 2
- Come usare le matrici e le formule matriciali – Parte 3
Tuttavia, come con qualsiasi altra capacità, la strada per padroneggiarla è lastricata con la pratica. Così, oggi ci concentreremo su alcuni esempi avanzati di formule matriciali cercando di renderli sia significativi che divertenti.
Esempio 1. Contare le celle che soddisfano determinate condizioni
L’introduzione delle funzioni SOMMA.SE e CONTA.SE, così come le loro controparti “plurali” in Excel 2007, ha reso l’uso delle formule matriciali superfluo in molti scenari. Tuttavia, ci sono compiti in cui una formula matriciale è la soluzione più efficace, se non l’unica.
Supponiamo di avere due colonne di numeri, colonna A (Budget) e la colonna B (Incasso effettivo). Si vuole contare quante volte la colonna B è maggiore o uguale alla colonna A, quando la colonna B è maggiore di 0.
Questa attività richiede il confronto tra due intervalli e questo è esattamente ciò per cui le formule matriciali di Excel sono stati progettate.
Quindi, esprimiamo le condizioni (B2:B10>=A2:A10) e (B2:B10>0), unendole tra loro con l’asterisco (*), che funge da operatore E nelle formule matriciali, e inseriamo questa espressione come argomento della funzione SOMMA:
=SOMMA((B2:B10>=A2:A10)*(B2:B10>0))
Ricordatevi di premere Ctrl + Maiusc + Invio per inserire correttamente la formula matriciale di Excel.
I prossimi due paragrafi sono destinati a coloro che amano guardare sotto il cofano. Se non siete interessati a dettagli tecnici, è possibile saltare direttamente al prossimo esempio di formula.
Per una migliore comprensione di questa formula in particolare e delle formule matriciali di Excel in generale, selezioniamo le due espressioni tra parentesi della funzione SOMMA e premiamo F9 per vedere le matrici rappresentate dalle formule. Se volete maggiori informazioni su come funziona il tasto F9, vi invitiamo a consultare Utilizzare il tasto F9 per valutare porzioni di una formula matriciale.
Quindi, quello che abbiamo sono due matrici di valori booleani, dove VERO equivale a 1 e FALSO a zero. Poiché stiamo usando l’operatore matriciale (*) nella formula, SOMMA sommerà solo le righe che hanno VERO (1) in entrambe le matrici, come mostrato nella figura seguente:
Esempio 2. Utilizzare più di una funzione nelle formule matriciali di Excel
Le formule matriciali possono contenere diverse funzioni di Excel contemporaneamente ed eseguire quindi più calcoli all’interno di una singola formula.
Ad esempio, se si dispone di una tabella che elenca le vendite di più prodotti da diversi venditori, e voleste conoscere la vendita massima realizzata da una data persona per un determinato prodotto, è possibile scrivere una formula matriciale in base al seguente schema:
=MAX(SE((intervallo_venditori="nome")*(intervallo_prodotti="nome"); intervallo_vendite;""))
Supponendo che i nomi dei venditori siano nella colonna A, i nomi dei prodotti nella colonna B e le vendite nella colonna C, la seguente formula restituisce la più grande vendita che Giorgio ha fatto relativamente alle mele:
=MAX(SE(($A$2:$A$9="giorgio")*($B$2:$B$9="mele"); $C$2:$C$9;""))
Naturalmente, è possibile sostituire i nomi nella formula con riferimenti di cella in modo che gli utenti possano semplicemente digitare i nomi in alcune celle senza modificare la formula matriciale:
Nello screenshot qui sopra, vengono utilizzate le seguenti formule matriciali di Excel (non dimenticate di premere Ctrl + Maiusc + Invio per inserirle correttamente):
- Massimo: =MAX(SE(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2); $C$2:$C$9;””))
- Minimo: =MIN(SE(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2); $C$2:$C$9;””))
- Media: =MEDIA(SE(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2); $C$2:$C$9;””))
- Totale: =SOMMA(SE(($A$2:$A$9=$F$1)*($B$2:$B$9=$F$2); $C$2:$C$9;””))
Esempio 3. Formula matriciale per contare tutti i caratteri in un determinato intervallo
Questo esempio di formula matriciale mostra come sia possibile contare il numero di caratteri, spazi inclusi, in un intervallo di celle.
La formula è molto semplice:
=SOMMA(LUNGHEZZA(intervallo))
È possibile utilizzare la funzione LUNGHEZZA per restituire la lunghezza della stringa di testo in ogni singola cella, poi si utilizza la funzione SOMMA per sommare questi numeri.
Ad esempio, la formula matriciale =SOMMA(LUNGHEZZA(A1:A10)) calcola il numero totale di tutti i caratteri, inclusi gli spazi, nell’intervallo A1:A10.
Esempio 4. Formula matriciale per contare le occorrenze di un determinato carattere in un intervallo
Se volete sapere quante volte compare un determinato carattere o un gruppo di caratteri in un determinato intervallo di celle, una formula matriciale con la funzione LUNGHEZZA può essere nuovamente di aiuto. In questo caso, la formula è un po’ più complessa:
=SOMMA((LUNGHEZZA(intervallo)-LUNGHEZZA(SOSTITUISCI(intervallo; carattere; "")))/LUNGHEZZA(carattere))
Ed ecco un esempio pratico. Supponiamo di avere un elenco degli ordini in cui una cella può contenere diversi numeri d’ordine, separati da virgole o qualsiasi altro delimitatore. Ci sono diversi tipi di ordine e ciascuno ha il suo identificatore univoco (il primo carattere del numero d’ordine).
Supponendo che gli ordini sono nelle celle B2:B5 e l’identificativo univoco in E1, la formula sarà la seguente:
=SOMMA((LUNGHEZZA(B2:B5)-LUNGHEZZA(SOSTITUISCI(B2:B5; E1; "")))/LUNGHEZZA(E1))
Esempio 5. Sommare valori presenti ogni ennesima riga
Se si desidera sommare tutte le righe oppure ogni ennesima riga di una tabella, sono necessarie le funzioni SOMMA e RESTO combinate in una formula matriciale:
=SOMMA((--(RESTO(RIF.RIGA(intervallo); Nma riga)=0))*(intervallo))
La funzione RESTO restituisce il resto arrotondato all’intero più vicino dopo che il numero viene diviso per il divisore. Noi vi inseriamo la funzione RIF.RIGA per ottenere il numero della riga, e poi la dividiamo per l’ennesima riga (ad esempio per 2 per sommare ogni seconda cella) e controlliamo se il resto è zero. Se lo è, allora la cella viene sommata.
L’operatore doppio unario (–) viene utilizzato per convertire i valori booleani non numerici VERO e FALSO restituiti da RESTO in 1 e 0 in modo che la funzione SOMMA possa aggiungere tali numeri.
Ad esempio, per contare ogni altra cella nell’intervallo B2:B10, si utilizza una delle seguenti formule:
Contare le righe pari (2a, 4a, etc.):
=SOMMA((--(RESTO(RIF.RIGA($B2:B10); 2)=0))*(B2:B10))
Contare le righe dispari (1a, 3a, etc.):
=SOMMA((--(RESTO(RIF.RIGA($B2:B10); 2)=1))*(B2:B10))
Per ottenere una formula universale che possa sommare i valori di tutte le Nme righe specificate e funzionare correttamente con ogni intervallo indipendentemente dalla loro posizione in un foglio di lavoro, la formula deve essere un po’ migliorata:
=SOMMA((--(RESTO((RIF.RIGA($B$2:$B$7)-RIF.RIGA($B$2)); E1)=E1-1))*($B$2:$B$7))
Dove E1 è ogni ennesima riga che si desidera sommare.
Formule matriciali di Excel – Limitazioni e alternative
Le formule matriciali sono di gran lunga una delle caratteristiche più potenti di Excel, ma non sono onnipotenti. Ecco elencati i principali limiti delle matrici di Excel.
1. Grandi matrici possono rallentare Excel
Sebbene Microsoft Excel non imponga alcun limite alla dimensione delle matrici che si utilizzano nei fogli di lavoro, siete in realtà limitati dalla memoria disponibile sul computer, perché il ricalcolo di formule con grandi matrici richiede molto tempo. Quindi, mentre in teoria è possibile creare matrici enormi, costituite da centinaia o migliaia di elementi, in pratica questo non è consigliato perché può rallentare drasticamente le cartelle di lavoro.
2. Le matrici di intere colonne non sono ammesse
Non è possibile creare una matrice che includa un’intera colonna o più colonne per una ragione evidente che è spiegata qua sopra. Le formule matriciali in Excel sono molto avide di risorse e Microsoft adotta misure preventive per evitare che Excel si blocchi.
3. Un limite alle formule matriciali facenti riferimento ad un altro foglio
In Excel 2003 e versioni precedenti, un dato foglio di lavoro può contenere un massimo di 65.472 formule matriciali che si riferiscono ad un altro foglio. Nelle versioni più moderne di Excel 2013, 2010 e 2007, le formule matriciali con riferimenti ad altri fogli sono limitate soltanto dalla memoria disponibile.
4. Fare il debug delle formule matriciali
Se la formula matriciale restituisce un risultato errato, assicuratevi di aver premuto Ctrl + Maiusc + Invio quando la inserite. Se avete fatto, selezionate parti della formula e premete F9 per valutare e mettere a punto.
5. Alternative alle formule matriciali
Se trovate le formule matriciali di Excel troppo complesse e confuse, è possibile utilizzare una delle funzioni di Excel che può naturalmente elaborare matrici di dati (senza premere Ctrl + Maiusc + Invio). Un buon esempio è la funzione MATR.SOMMA.PRODOTTO che moltiplica i valori nelle matrici specificate e restituisce la somma di tali prodotti. Un altro esempio è la funzione INDICE di Excel con un valore vuoto o a 0 nell’argomento riga o col, al fine di restituire una matrice di valori rispettivamente dall’intera colonna o riga, rispettivamente.
Questo è tutto per oggi, grazie per la lettura!
