Ci eravamo lasciati, un paio di settimane fa con la promessa di approfondire ulteriormente l’argomento delle matrici e delle formule matriciali, ed allora eccoci qua!
Le costanti ed il loro uso nelle matrici di Excel
In Microsoft Excel, le costanti di matrice sono semplicemente un insieme di valori statici. Questi valori non cambiano mai quando si copia una formula in altre celle o valori.
Abbiamo già visto un esempio di costanti di matrice create da una lista della spesa all’inizio dello scorso articolo sull’argomento. Ora, vediamo quali altri tipi di matrice esistono e come si creano.
Esistono 3 tipi di costanti di matrice in Excel:
Le matrici orizzontali
Una matrice orizzontale si sviluppa in una riga. Per creare una matrice orizzontale, digitate i valori separati da barre retroverse (backslash) e racchiudeteli poi tra parentesi graffe, ad esempio {1\2\3\4}.
Per inserire una matrice orizzontale in un foglio di calcolo, selezionate il numero corrispondente di celle vuote in una riga, digitare la formula ={1\2\3\4} nella barra della formula, e premete Ctrl + Maiusc + Invio. Il risultato sarà simile a questo:
Come potete vedere nello screenshot, Microsoft Excel avvolge le costanti di matrice in un altro insieme di parentesi graffe, allo stesso modo di quando si sta inserendo una formula matriciale.
Le matrici verticali
Le matrici verticali si sviluppano in una colonna. Si creano allo stesso modo delle matrici orizzontali, con la sola differenza che si delimitano gli elementi con un punto, per esempio:
={11.22.33.44}
Le matrici multi-dimensionali
Per creare una matrice multi-dimensionale in Excel, separate gli elementi di ogni riga con i punti, e delimitate ogni colonna con una backslash. Ecco l’esempio di una matrice bidimensionale:
={"a"\"b"\"c".1\2\3}
Lavorare con le costanti di matrice in Excel
Le costanti di matrice sono uno dei cardini delle formule matriciali di Excel. Le seguenti informazioni e suggerimenti potrebbero aiutarvi ad usarle nel modo più efficiente.
Gli elementi di una matrice di costanti
Una matrice di costanti in Excel può contenere numeri, valori di testo, valori booleani (VERO e FALSO) e valori di errore, separati da backslash o punti.
È possibile immettere un valore numerico come un intero, un decimale o in notazione scientifica. Se si utilizzano valori di testo, devono essere circondati tra virgolette (“), come in qualsiasi formula di Excel.
Una matrice di costanti non può includere altre matrici, riferimenti di cella, intervalli, date, nomi definiti, formule o funzioni di Excel.
Dare un nome alle matrici di costanti
Per far sì che una matrice di costanti sia più facile da usare, è consigliabile dargli un nome:
- Passate alla scheda FORMULE > Nomi Definiti e fate clic su “Definisci nome“. In alternativa, premere Ctrl + F3 e fare clic su Nuovo
- Digitate il nome che volete attribuire alla matrice nella casella di testo “Nome:“
- Nella casella “Riferito a:“, inserite gli elementi della matrice di costanti circondati tra parentesi, preceduti dal segno uguale (=). Per esempio:
={"Lun"\"Mar"\"Mer"\"Gio"\"Ven"\"Sab"\"Dom"}
- Fare clic su OK per salvare la matrice denominata e chiudere la finestra
Per inserire la matrice denominata in un foglio, selezionate il numero di celle necessarie in una riga o in una colonna, digitate il nome della matrice (senza le parentesi graffe) nella barra della formula preceduto con il segno “=” e premete Ctrl + Maiusc + Invio.
Il risultato dovrebbe essere simile a questo:
Prevenire gli errori
Se la matrice non dovesse funzionare correttamente, fate attenzione ai seguenti punti:
- che gli elementi della vostra matrice siano delimitati con il carattere corretto: backslash per le costanti di matrice orizzontali e punto per quelle verticali;
- che abbiate selezionato un intervallo di celle che corrisponda esattamente al numero di elementi nella vostra matrice. Se si selezionano più celle, ogni cella in più avrà l’errore #N/D. Se si seleziona un numero inferiore di celle, verrà inserita solo una parte della matrice.
Come utilizzare le costanti di matrice nelle formule di Excel
Ora che siete pratici con il concetto di costante di matrice, vediamo come è possibile utilizzarle nelle formule di Excel per agevolare nella pratica i vostri compiti.
Esempio 1. Sommare gli N numeri più grandi/più piccoli in un intervallo
Iniziamo con la creazione di una costante di matrice verticale contenente i numeri che si desidera sommare. Ad esempio, se si desiderasse sommare i 3 numeri più piccoli o più grandi di un intervallo, la costante di matrice sarà {1.2.3}.
Poi, si prende la funzione =GRANDE() o =PICCOLO(), si specifica l’intero intervallo di celle da valutare nel primo parametro e si include la matrice nel secondo. Infine, si incorpora il tutto nella funzione =SOMMA(), in questo modo:
- Sommare i 3 numeri più grandi: =SOMMA(GRANDE(range;{1.2.3}))
- Sommare i 3 numeri più piccoli: =SOMMA(PICCOLO(range;{1.2.3}))
Non dimenticate di premere Ctrl + Maiusc + Invio al momento di inserire la formula matriciale, e si otterrà il seguente risultato:
In modo simile, è possibile calcolare la media degli N valori più piccoli o più grandi in un intervallo:
- Media dei 3 numeri più grandi: =MEDIA(GRANDE(range;{1.2.3}))
- Media dei 3 numeri più piccoli: =MEDIA(PICCOLO(range;{1.2.3}))
Esempio 2. Formula matriciale per contare le celle con condizioni multiple
Supponiamo, che si disponga di una lista di ordini e che vogliate sapere quante volte un dato venditore abbia venduto determinati prodotti.
Il modo più semplice sarebbe quello di utilizzare una formula =CONTA.PIÙ.SE() con più condizioni. Tuttavia, se si desidera includere molti prodotti, la formula CONTA.PIÙ.SE potrebbe diventare troppo grande. Per renderla più compatta, è possibile utilizzare CONTA.PIÙ.SE insieme con SOMMA e includere una matrice di costanti in uno o più argomenti, ad esempio:
=SOMMA(CONTA.PIÙ.SE(Range1;"Criterio1";range2;{"Criterio1";"Criterio2"}))
La vera formula potrebbe essere così:
=SOMMA(CONTA.PIÙ.SE(B2:B10;"Giorgio";C2:C10;{"Mele"."Pere"}))
La nostra matrice di esempio è costituita da due soli elementi, poiché l’obiettivo è quello di dimostrare l’approccio. Nelle vostre formule matriciali reali, è possibile includere tutti gli elementi che volete, a condizione che la lunghezza totale della formula non superi i 8.192 caratteri in Excel 2013, 2010 e 2007 (1024 caratteri in Excel 2003 e precedenti) e il computer sia abbastanza potente da elaborare grandi matrici.
Anche questa seconda tappa del nostro percorso all’interno delle matrici di Excel è giunta al termine.
La prossima volta faremo un rapido excursus sull’utilizzo degli operatori logici all’interno delle formule matriciali. Vi aspetto!
