La
funzione SOMMA.SE (SUMIF) è utile quando, all'interno di un piccolo
database, si vuole sommare solo i valori su una determinata colonna
quando è rispettato un certo criterio su una colonna adiacente.
Spesso il criterio da rispettare è il fatto che all'interno della
cella utilizzata come criterio ci sia un ben determinato valore.
Cosa fare però se si vuole sommare
solo i valori per esempio sulla colonna C quando si trova un ben
determinato valore sulla colonna A e un ben determinato valore sulla
colonna B?
Vediamo questo esempio:
Che formula inserire
nelle celle E11 e F11 per sommare solo quei valori per cui compare
sulla colonna B il valore pere e sulla colonna C il
valore rosse? Ovviamente in questo esempio è facile
rendersi conto che è sufficiente sommare la riga 3 e la riga 8.
Totale 26 e 52. Vediamo invece come fare per scrivere una formula
che faccia questa cosa senza che siamo noi a selezionare una per una
le celle da sommare.
la prima soluzione, la
più banale è quella di inserire una ulteriore colonna concatenando
i valori di B e C ed eseguire il SOMMA.SE (SUMIF) su questa nuova
colonna "concatenata":
la formula della cella
B3 è: =C3&D3
Questo approccio ha
due problemi:
-
Bisogna modificare
il database
-
Se si volesse
inserire una riga sommando il dato di pere cassetta è
necessario inserire una nuova colonna con la concatenazione
delle colonne C ed E.
La soluzione, per
quanto semplice ed efficace non è molto pratica. Possiamo fare di
meglio... senza usare la funzione SOMMA.SE (SUMIF)!
Utilizziamo la
funzione MATR.SOMMA.PRODOTTO (SUMPRODUCT) in questo modo:
a primo sguardo la
formula sembra ben strana.. eppure funziona!
Vediamo come funziona:
per prima cosa MATR.SOMMA.PRODOTTO di solito è usata per
moltiplicare membro a membro tra di loro due range di celle e poi
sommare i prodotti. Tipico uso è per il calcolo delle medie pesate.
Qui invece non ci sono due range ma c'e' un solo elemento. Il dato
è fatto da tre componenti: due parentesi e un range (E3:E9)
moltiplicati tra di loro.
Vediamo il contenuto delle parentesi. Sono due condizioni:
B3:B9=B11
e C3:C9=C11
Le due scritture
indicano un range = una cella.Il risultato è un range di dimensioni
uguali al range di partenza in cui ogni valore è sostituito con
VERO o FALSO a seconda che il corrispondente elemento del range di
partenza sia uguale oppure no al valore indicato dalla cella sulla
destra del segno di uguale.
Nell'esempio sopra la formula B3:B9=B11
restituisce il seguente risultato (essendo B11=pere):
VERO
FALSO
FALSO
VERO
FALSO
VERO
FALSO
la seconda parentesi (C3:C9=C11)
restituisce il seguente risultato (essendo C11=rosse):
VERO
FALSO
VERO
FALSO
VERO
VERO
FALSO
La composizione
attraverso il segno di moltiplicazione dei due diventa un range
così fatto:
VERO
FALSO
FALSO
FALSO
FALSO
VERO
FALSO
con due soli campi a
VERO, quelli in corrispondenza delle righe 3 e 8. Esattamente quello
che volevamo. Di fatto, quello che è stato ottenuto è un AND
logico tra le due colonne. Adesso arriva il trucco. Poiché Excel
"confonde" il valore VERO con 1 e FALSO con 0 la
precedente colonna può essera anche riscritta come:
1
0
0
0
0
1
0
Adesso eseguire un
MATRICE.SOMMA.PRODOTTO tra la colonna che ci interessa sommare e la
colonna di 1 e 0 di fatto significa semplicemente sommare i valori
in corrispondenza dei valori 1, cioè delle righe per cui sono vere
entrambe le condizioni imposte.
E' banale a questo
punto capire come fare per estendere questa logica a più condizioni
contemporanee.
Se desiderate il
foglio excel di esempio, potete scaricarlo da qui
|
