Le formule matriciali sono formule nelle quali al
posto di un singolo valore abbiamo un insieme di valori in forma di
matrice; la formula effettua un ciclo di operazione per tutti i valori
della matrice. In Excel i valori possono essere passati alla formula
per riferimento di celle sotto forma di riga ( matrice unidimensionale
orizzontale ) oppure sotto forma di colonna ( matrice unidimensionale
verticale ) o ancora come elenco organizzato in righe e colonne (
matrice bidimensionale ). Vedremo che è anche possibile passare dei
valori costanti sotto forma di matrice di costanti.
Il risultato di una formula matriciale può essere restituito in più
celle ( formula multicella) oppure in una singola cella ( formula
monocella).
Si supponga per esempio di avere un elenco come quello sottostante.
(fig.1)
Potremmo essere interessati ad avere il fatturato
totale e normalmente creeremmo una formula nella cella E2 del tipo “=
C2*D2”; copieremmo con riferimenti relativi la formula nelle
sottostanti celle fino a E12 e infine in un’altra cella otterremmo il
totale con “SOMMA(E2:E12)”. In sostanza avremmo bisogno di 12 formule.
Procediamo, invece, con formule matriciali, ripartendo dalla
situazione della fig1:
Ora una semplice somma in E13 ci darà il valore
di 9896. Sembrerebbe che non ci abbaiamo guadagnato granché rispetto
alla procedura normale a parte che una sola formula per 11 celle.
Intanto questo ultimo punto non è marginale. Se tentiamo infatti di
cancellare il contenuto di una sola cella dell’intervallo E2:E12 ( per
esempio la cella E5 ) avremo un messaggio di errore che ci dice
che non possiamo modificare una parte di una matrice: ricordiamo che
Excel ci sta restituendo su più celle il risultato di una singola
formula. Abbiamo un elemento di sicurezza, che non si ha nelle
formula normali copiate con riferimento relativo per la possibile
successiva manipolazione accidentale di una delle formule. La potenza
delle formule matriciali si misura però per un altro aspetto.
Inseriamo in una cella, per es., la G2 la seguente formula:
=SOMMA(C2:C12*D2:D12) seguita da Ctrl + Maiusc + Invio
Nella barra della
formula vedremo{=SOMMA(C2:C12*D2:D12)}
: osserviamo lo stesso risultato di 9896; cancelliamo ora il contenuto
delle celle E2:E12 e ci accorgiamo che la nostra formula nella cella
G2 mantiene lo stesso risultato dimostrandosi indipendente dalla
formula multicella appena cancellata. Cosa è accaduto? Excel ha
risolto la formula iterando per ogni cella dell’intervallo C2:C12 e di
quello D2:D12 allocando nella memoria e non nel foglio di lavoro i
valori delle celle, risolvendo la formula attraverso questi passaggi:
{=SOMMA({280;175;300;48;300;700;280,175;150;175;120}*{2;4;3;7;5;3;3;2;8;6;3})}
{=SOMMA({560;700;900;336;1500;2100;840;350;1200;1050;360})}
{=9896}
9896
Excel memorizza un array di costanti che non risiedono sul file sul
quale si sta lavorando. La rappresentazione dei passaggi intermedi
tiene conto della sintassi dell’utilizzo delle matrici di costanti.
Queste nelle formule matriciali sono inserite come serie di valori
separati dal simbolo “;” nell’impostazione italiana ( in USA
l’elemento separatore sarebbe la virgola “,” ) inseriti tra una coppia
di parentesi graffe. Contrariamente alle parentesi graffe che si
trovano all’esterno della formula e che bisogna inserire con Ctrl +
Maiusc + Invio le parentesi graffe delle matrici di costanti vanno
inserite manualmente ( eventualmente se non sono rappresentate sulla
tastiera con la combinazione Alt + 0123 e Alt + 0125 oppure con
l’utilità inserisci simbolo). Se infatti inserissimo in una cella
=SOMMA({280;175;300;48;300;700;280,175;150;175;120}*{2;4;3;7;5;3;3;2;8;6;3})
seguito da Ctrl + Maiusc + Invio otterremmo sempre
9896 .
Un utilizzo
frequente delle formule matriciali sono le somme condizionali, cioè
somme nelle quali ogni singolo addendo prima di essere sommato deve
soddisfare 1 o più criteri. In realtà quando il criterio è semplice e
singolo si può ricorrere anche ad una formula ordinaria utilizzando la
funzione SOMMA.SE. Questa ha 3 argomenti, dei quali solo i primi 2
sono obbligatori. Il primo è l’intervallo contenente i valori da
sottoporre alla verifica del criterio; il secondo è il criterio; il
terzo è l’intervallo somma, cioè l’intervallo contenente i valori da
sommare). Nel caso della tabella di esempio la formula =SOMMA.SE(A2:A12;"Rossi";E2:E12)
ci restituirà il fatturato del solo cliente Rossi, indipendentemente
dalla tipologia degli acquisti ( nel nostro esempio 2410 ). La formula
SOMMA.SE assume che in caso di omissione del terzo argomento
l’intervallo contenete i valori da sommare sia il primo; ovviamente in
questo caso l’intervallo deve contenere valori numerici. Se per
esempio vogliamo sapere qual’è il fatturato di tutti gli ordini
superiori a 1000 scriveremo =SOMMA.SE(E2:E12;">1000") con risultato
5850. La formula ordinaria SOMMA.SE non ci aiuta in caso di criteri
complessi o multipli. Come risolvere per esempio il problema di sapere
quale è il fatturato di Bianchi e Rossi relativi ai soli cellulari?
Inseriamo in una cella
=SOMMA(E2:E12*(B2:B12="cellulare")*((A2:A12="Rossi") +
(A2:A12="Bianchi"))) e premiamo Ctrl + Maiusc + Invio
Risolviamo passo passo la formula ottenuta
{=SOMMA(E2:E12*(B2:B12="cellulare")*((A2:A12="Rossi") +
(A2:A12="Bianchi")))}
equivale a
{=SOMMA(E2:E12*(B2:B12="cellulare")*({VERO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO}+
{FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO}))}
Tenendo conto che i valori booleani VERO e FALSO con gli operatori
+ , - , * , / , < , > valgono
rispettivamente 1 (VERO) e 0 (FALSO) avremo:
{=SOMMA(E2:E12*(B2:B12="cellulare")*{1;1;0;0;1;0;0;1;1;0;0})}
e procedendo anche per il criterio della prima parte della formula
{=SOMMA({560;700;900;336;1500;2100;840;350;1200;1050;360}*{FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;FALSO;VERO;FALSO}
*{1;1;0;0;1;0;0;1;1;0;0})}
{=SOMMA({560;700;900;336;1500;2100;840;350;1200;1050;360}*{0;1;0;0;0;0;0;1;0;1;0}
*{1;1;0;0;1;0;0;1;1;0;0})}
Ora abbiamo una serie di prodotti a 3 fattori:
basta che un fattore sia 0 che il prodotto pure sarà 0
{=SOMMA({0;700;0;0;0;0;0;350;0;0;0})}
1050
Questi esempi ci hanno fatto capire l’”anatomia” delle formule
matriciali. Nella prossima parte vedremo come esse permettano di
effettuare tasks interessanti come per esempio: l’individuazione di
duplicati o di valori univoci in una serie; il confronto tra 2 serie
di valori per conoscere se i valori di una serie test rientrano in
quelli di una serie di confronto; la media di una serie di valori che
soddisfano un determinato criterio ( per esempio esclusione di valori
= 0 o negativi ); la somma di ricorrenze di valori multipli in una
serie; l’utilizzo combinato di formule matriciali e di formule
definite dall’utente, ecc
|