In questo capitolo
Che cosa sono le matrici
Creare formule che usano matrici
Usare funzioni che restituiscono matrici di dati
Che cosa sono le matrici
Creare formule che usano matrici
Usare funzioni che restituiscono matrici di dati
Excel è un software davvero sofisticato, ricco di funzioni in grado di semplificare moltissime operazioni. Inoltre, Excel consente di chiedere alle funzioni di operare su interi insiemi di valori, matrici, che aprono la porta a valutazioni analitiche ancora più avanzate.
Una matrice è costituita da un insieme di due o più valori (per esempio il contenuto di due o più celle del foglio di lavoro o perfino di due o più intervalli di celle). Alcune funzioni accettano fra i propri argomenti anche delle matrici.
Potreste chiedervi: “Ma cosa cambia nell’utilizzare una matrice di valori rispetto ai singoli argomenti, specificati uno dopo l’altro?”. Avete ragione… almeno in parte. Per esempio, la funzione SOMMA può accettare fino a 255 argomenti. Non è la stessa cosa fornire alla funzione 255 valori oppure una matrice di 255 valori? Ebbene, sì e no. La “sostanza” è la stessa, ma l’approccio a matrici semplifica le cose, come vedremo fra breve.
Vi è anche un altro aspetto delle funzioni a matrici. Alcune funzioni restituiscono una matrice ma, nella maggior parte dei casi, una funzione restituisce un unico valore, in un’unica cella. In questo capitolo vedremo come una funzione sia anche in grado di restituire più valori all’interno di più celle.
Una matrice è come una “scatola” che può contenere più elementi.
In Excel, una matrice contiene una serie di valori o di riferimenti a celle.
Le matrici vengono utilizzate esclusivamente per formule e funzioni. In pratica, l’associazione di più valori a formare un’unica entità ha il solo scopo di calcolare dei risultati. Una matrice è un oggetto differente da una comune area con nome (un intervallo di celle) che potete creare con Excel. Le aree con nome entrano a far parte del foglio di lavoro e diventa possibile farvi riferimento in qualsiasi momento.
Le aree con nome vengono create utilizzando la finestra di dialogo Nuovo nome, rappresentata nella Figura 3.1. Al contrario, non esiste una finestra di dialogo o un metodo per creare le matrici impiegate da funzioni o formule. Le matrici, al contrario, sono incorporate direttamente nelle formule.
Figura 3.1 Creazione di un’area con nome tramite la finestra di dialogo Nuovo nome.
Le aree con nome possono essere impiegate con grande facilità nelle formule. Per esempio, se una cartella di lavoro contiene un’area chiamata Vendite, allora i valori contenuti in tutte le celle dell’area Vendite possono essere sommati con una semplice formula:
=SOMMA(Vendite)
Supponete che l’area Vendite contenga tre celle, le quali contengono i tre valori seguenti: 10, 15 e 20. Naturalmente questi valori potrebbero essere inseriti direttamente nella funzione SOMMA nel modo seguente:
=SOMMA(10;15;20)
Questa è quasi una matrice, ma non ancora. Excel riconosce dei valori come una matrice quando essi sono racchiusi fra parentesi graffe ({ e }). Pertanto, per inserire la matrice di valori nella funzione, occorre scrivere:
=SOMMA({10;15;20})
Le parentesi graffe chiedono a Excel di trattare il gruppo di valori come una matrice. A questo punto vi chiederete quale utilizzo possa avere una matrice, ma già dal prossimo paragrafo vedremo come l’utilizzo delle matrici con le normali funzioni, per esempio come SOMMA, possa fornire risultati sofisticati.
Per inserire dei valori sotto forma di una matrice all’interno di una funzione, racchiudeteli fra parentesi graffe. Le parentesi graffe non sono la stessa cosa delle parentesi quadre o delle parentesi angolari. Sulle tastiere italiane rappresentano, in pratica, le “maiuscole” delle parentesi quadre: le parentesi quadre si ottengono con Alt Gr + [ e Alt Gr + ]; le parentesi graffe si ottengono con Alt Gr + Maiusc + [ e Alt Gr + Maiusc + [.
Le matrici possono essere impiegate in formule e funzioni. Ma quando gli argomenti di una funzione sono matrici, vengono inseriti in modo differente, come vedremo più avanti. L’utilizzo di matrici consente di risparmiare molti passi e di fornire un risultato complesso impiegando un’unica formula.
Le matrici possono essere utili nei casi in cui sia necessario eseguire dei calcoli intermedi, sulla base dei quali può essere calcolato il risultato finale. Ogni possibilità di risparmiare tempo e fatica è sempre benvenuta, giusto?
Ecco un esempio: la funzione SOMMA viene normalmente impiegata per sommare insieme alcuni numeri. Se dobbiamo sommare solo una manciata di numeri, non vale la pena di ricorrere a una formula a matrice, ma come fare per sommare i risultati di altri calcoli? Il prossimo esempio mostra come utilizzare una matrice per semplificare il calcolo del risultato finale.
La Figura 3.2 mostra un piccolo portafoglio di azioni. La Colonna A elenca la sigla, la Colonna B contiene il numero di azioni in nostro possesso e la Colonna C contiene la quotazione aggiornata di ogni azione.
Figura 3.2 Un portafoglio di azioni.
Il compito consiste nel calcolare il valore totale del portafoglio. Normalmente si dovrebbe procedere nel modo seguente.
1.Moltiplicare il numero di azioni per il relativo prezzo, il tutto per cinque volte, tanti quanti sono i gruppi di azioni.
2.Sommare tutti i risultati prodotti dai calcoli del Passo 1.
La Figura 3.3 mostra un modo molto semplice per farlo. La Colonna D contiene le formule necessarie per calcolare il valore per tutti i gruppi di azioni del portafoglio. Per farlo occorre moltiplicare il numero di azioni per il relativo prezzo. Per esempio, la cella D4 contiene la formula =B4*C4. La cella D10 somma poi tutti i risultati intermedi utilizzando la formula =SOMMA(D4:D8).
Figura 3.3 Calcolo del valore di un portafoglio di azioni nel “vecchio modo”.
Il metodo rappresentato nella Figura 3.3 richiede la creazione di alcuni calcoli aggiuntivi, quelli contenuti nella Colonna D. Questi calcoli sono necessari per conoscere il valore in nostro possesso per ogni tipo di azione; ma noi siamo interessati a sapere il valore dell’intero portafoglio.
Fortunatamente, esistono delle alternative a questo approccio standard. Una consiste nell’incorporare le moltiplicazioni direttamente nella funzione SOMMA, nel modo seguente:
=SOMMA(B4*C4;B5*C5;B6*C6;B7*C7;B8*C8)
Naturalmente questo approccio funziona, ma è, quantomeno, macchinoso. E se il nostro portafoglio fosse composto da venti tipi di azioni? Quanto sarebbe lunga la formula?
Un’altra alternativa consiste nell’impiegare la funzione MATR.SOMMA.PRODOTTO. Questa funzione somma i prodotti, esattamente come gli altri metodi presentati. Il limite, tuttavia, consiste nel fatto che MATR.SOMMA.PRODOTTO può essere utilizzata solo per calcolare una somma. Per esempio, non può calcolare una media.
In molte situazioni di questo tipo, la cosa migliore consiste nell’utilizzare una funzione a matrice. La Figura 3.4 mostra il risultato corretto, ottenuto utilizzando la funzione SOMMA impiegata come una funzione a matrice. Notate che la formula nella Barra della formula inizia e termina con una parentesi graffa.
Figura 3.4 Calcolo del valore di un portafoglio di azioni utilizzando una funzione a matrice.
La sintassi è importante. La funzione opera su due intervalli: il primo contiene le celle che contengono il numero di azioni e l’altro contiene le celle contenenti le quotazioni delle rispettive azioni. I valori vengono moltiplicati nella funzione specificando l’operatore di moltiplicazione (*):
{=SOMMA(B4:B8*C4:C8)}
Per concludere una formula a matrice, si utilizza la combinazione di tasti Ctrl + Invio. Questa particolare combinazione di tasti deve essere inserita solo quando la formula è conclusa, non prima. Notate la mancanza dei subtotali (per le azioni) nelle celle D4:D8. Confrontate la Figura 3.4 con la Figura 3.5 e vedrete la differenza.
Per trasformare una formula comune in una formula a matrice utilizzate la combinazione di tasti Ctrl + Maiusc + Invio. Dovete utilizzare questa combinazione di tasti dopo aver inserito la formula, invece di premere semplicemente il tasto Invio.
Ecco come utilizzare una matrice con la funzione SOMMA.
1.Inserite due colonne di valori.
I due elenchi devono avere le stesse dimensioni.
2.Posizionate il cursore nella cella in cui deve comparire il risultato.
3.Inserite =SOMMA( per iniziare la funzione.
Notate che non occorre ancora inserire la parentesi graffa in questa fase.
4.Fate clic sulla prima cella del primo elenco e, mantenendo premuto il tasto sinistro del mouse, trascinate il puntatore verso il basso, fino a selezionare la prima colonna di valori. Quindi rilasciate il tasto sinistro del mouse.
5.Inserite il segno di moltiplicazione (*).
6.Fate clic sulla prima cella del secondo elenco e, mantenendo premuto il tasto sinistro del mouse, trascinate il puntatore fino a selezionare l’intero secondo elenco.
7.Rilasciate il tasto del mouse.
8.Inserite la parentesi chiusa, ).
9.Premete la combinazione di tasti Ctrl + Invio per concludere la funzione a matrice.
Non premete semplicemente il tasto Invio se intendete creare una formula a matrice con la funzione SOMMA.
Le funzioni a matrice sono utili per risparmiare passi nelle operazioni matematiche. Potete applicare questi esempi a varie funzioni, come MEDIA, MIN, MAX e così via.
Ecco un altro esempio. Supponete di dover gestire una flotta di taxi e di dover calcolare il costo del carburante per chilometro. È facile farlo per un unico veicolo. Basta dividere la spesa totale di carburante per i chilometri totali percorsi in un determinato arco di tempo. Il calcolo avrà il seguente aspetto:
costo benzina al km = spesa totale benzina ÷ km percorsi
Ma come calcolare con facilità questi stessi valori per un’intera flotta di veicoli? La Figura 3.5 mostra come fare. Nella Colonna A sono elencati i veicoli, nella Colonna B compaiono i chilometri totali percorsi nel mese e nella Colonna C compare la spesa totale di carburante.
Il risultato è calcolato da un’unica formula, contenuta nella cella C21. Utilizzando la funzione MEDIA in una formula a matrici, il risultato viene restituito senza richiedere calcoli intermedi. La formula ha il seguente aspetto: {=MEDIA(C6:C17/B6:B17)}.
Figura 3.5 Semplificazione estrema dei calcoli utilizzando una formula a matrice.
Alcune funzioni restituiscono intere matrici di dati. Invece di fornire un unico risultato, come fa la maggior parte delle funzioni, queste funzioni restituiscono più valori. Il numero di valori effettivamente restituiti dipende dal numero di argomenti della funzione. I valori restituiti occuperanno, quindi, un intervallo di celle.
Le funzioni a matrici di Excel accettano matrici come argomenti e restituiscono matrici di dati.
Un buon esempio è rappresentato dalla funzione MATR.TRASPOSTA. Questa interessante funzione viene utilizzata per riorientare i dati. I dati situati in un determinato intervallo di righe e colonne vengono trasposti, ovvero vengono “ruotati” in termini di colonne e righe. La Figura 3.6 mostra cosa accade.
Le celle da B3 a B10 contengono informazioni relative ai dipartimenti di un’azienda. I dipartimenti sono elencati all’interno della Colonna B. Notate che l’area di dati da B3 a D10 occupa tre colonne e otto righe. L’area include una riga di intestazioni.
Figura 3.6 Trasposizione dei dati.
Le celle da B16 a B18 contengono i dati trasposti. Si tratta degli stessi dati, ma ora occupano otto colonne e tre righe. Si tratta, in termini di numero di celle, di un insieme di celle uguali all’area originaria. Altrettanto importante è che l’area ha le stesse dimensioni, ma invertite. In pratica l’area 3 × 8 è diventata un’area 8 × 3. Il numero totale di celle rimane, però, 24. L’area trasposta non è stata alterata: cambia solo la disposizione dei dati.
Ogni singola cella dell’intervallo B16: D18 contiene la stessa formula: {=MATR.TRASPOSTA(B3:D10)}. Tuttavia, questa funzione è stata inserita una volta sola.
In dettaglio, ecco come utilizzare la funzione MATR.TRASPOSTA.
1.Inserire dei dati, in modo che occupino almeno due celle adiacenti.
Un’area di dati che si estende su più righe che colonne è particolarmente efficace nel mostrare l’utilità della funzione.
2.In un altro punto del foglio di lavoro, selezionate un’area che offra lo stesso numero di celle, anche se invertite in termini di righe e colonne.
Per esempio:
•se l’area originaria era di 2 colonne per 6 righe, selezionate un’area di 6 colonne per 2 righe;
•se l’area originaria era di 1 colonna per 2 righe, selezionate un’area di 2 colonne per 1 riga;
•se l’area originaria era di 200 colonne per 201 righe, selezionate un’area di 201 colonne per 200 righe;
•se l’area originaria era di 5 colonne e 5 righe, selezionate un’area delle stesse dimensioni (un’area quadrata viene trasposta in un’altra area quadrata).
La Figura 3.7 mostra un’area di dati e l’area selezionata per ricevere i dati trasposti. I dati originali occupano 11 colonne e 3 righe. L’area selezionata occupa 3 colonne e 11 righe.
Figura 3.7 Preparazione di un’area per la ricezione dei dati trasposti.
3.Inserite =MATR.TRASPOSTA( per iniziare la funzione.
Poiché l’area di ricezione e già selezionata, ciò che inserite andrà nella prima cella di tale area.
4.Fate clic sulla prima cella dei dati originari e trascinate il puntatore del mouse sull’intera area dei dati originali, mantenendo premuto il tasto sinistro del mouse. Una volta selezionata l’intera aerea, rilasciate il tasto sinistro del mouse.
La funzione ora mostrerà l’intervallo dell’area originaria. La Figura 3.8 mostra la situazione in questa fase.
5.Inserite la parentesi chiusa, ).
6.Premete Ctrl + Maiusc + Invio per concludere la funzione.
Nota: I dati trasposti non necessariamente assumono la formattazione dell’area originaria. Potreste quindi dover riformattare l’area. La Figura 3.9 mostra il risultato dell’utilizzo della funzione MATR.TRASPOSTA e della successiva formattazione dei dati trasposti.
Figura 3.8 Completamento della funzione.
Figura 3.9 Dati trasposti dopo la formattazione.
Ma questo non è, un po’, uno spreco di tempo? Excel può trasporre con facilità i dati utilizzando la finestra di dialogo Incolla speciale. Possiamo copiare un intervallo di dati e poi utilizzare questa finestra di dialogo per incollare i dati ottenendo lo stesso risultato della funzione…. O forse no?
La Figura 3.10 mostra la finestra di dialogo Incolla speciale, nella quale è selezionata la casella Trasponi. Anche questa operazione esegue una trasposizione dei dati. Non dovete neppure selezionare il numero corretto di righe e colonne per eseguire la trasposizione. Semplicemente i dati vengono trasposti.
Figura 3.10 Uso della finestra di dialogo Incolla speciale per eseguire la trasposizione dei dati.
Tuttavia, i dati trasposti con la finestra di dialogo Incolla speciale, vengono semplicemente copiati nella nuova area. Al contrario, con la funzione MATR.TRASPOSTA, nelle celle di destinazione viene inserita una formula che fa riferimento ai dati originali; questo è l’elemento chiave. Quando i dati verranno modificati nell’area originaria, tale cambiamento si rifletterà anche sulla nuova area, trasposta con la funzione MATR.TRASPOSTA.
Potete trasporre i dati in due modi. L’area riempita con una funzione MATR.TRASPOSTA fa riferimento ai dati originari e si aggiornerà ogni volta che tali dati verranno modificati. La finestra di dialogo Incolla speciale crea solo una copia trasposta dei valori: aggiornando i valori originali non si produrrà alcun intervento sui dati trasposti.
