Funzione CONTA.SE a due criteri. (con formula matriciale)

La funzione CONTA.SE(dove cercare; cosa cercare) restituisce un valore numerico indicante quante volte un valore (da cercare) è presente in un intervallo di celle, intervallo che può essere esteso su molte righe e colonne (intervallo dove cercare), e prevede un solo criterio di ricerca, cioè è possibile cercare un solo determinato valore (testuale, numerico, data, ora). Nella tabella sotto, se volessimo contare quante "mele" ci sono, nell'intervallo A2:C6, scriveremmo in un'altra cella la seguente formula:

  • =CONTA.SE(A2:C6;"mele") ed otterremmo in risposta il valore 3

Quando però sia necessario confrontare due valori e contare quante volte un valore è presente SE in un altro intervallo è presente un'altro valore, la funzione CONTA.SE non può lavorare e si può usare una formula matriciale, modificando completamente le funzioni da usare.

Infatti, pur volendo contare SE le occorrenze di un valore sono presenti in presenza di un altro valore, dovremo usare le funzioni SOMMA() e SE(), impostando una formula condizionale (matriciale - vedi a fine articolo).

Vediamo una possibile tabella di esempio: desideriamo contare quanto prodotto "mele" proviene dall'Italia.

  A B C D
1  Nome Prodotto  Tipo Prodotto  Provenienza  
2

Renette

mele *

Italia *

 
3

Golden

mele

Israele

 
4

Wiliams

pere

Italia

 
5

Spadone

pere

Italia

 
6

Deliziose

mele *

Italia *

 

In pratica dobbiamo contare nella colonna B quante volte è presente la parola "mele" (in B) in associazione alla parola "Italia" presente nella colonna C. Come si nota, solo due volte si combina la presenza di "mele" con "Italia" (le voci con l'asterisco).

Intanto bisogna rimarcare che in excel spesso si possono sfruttare più passaggi (cioè usare più formule divise su più celle, e tra l'altro celle poste in aree del foglio distanti (fuori vista)), per ottenere il risultato voluto; ad esempio possiamo usare una formula condizionale posta nella cella F2  =SE(E(B2="mele";C2="Italia");1;0) trascinando poi fino a F6, ed otterremo questo:

  A B C D E F G
1  Nome Prodotto  Tipo Prodotto  Provenienza        
2

Renette

mele *

Italia *

    1  
3

Golden

mele

Israele

    0  
4

Wiliams

pere

Italia

    0  
5

Spadone

pere

Italia

    0  
6

Deliziose

mele *

Italia *

    1  

Abbiamo usato la funzione SE() e la funzione E() (vedi articolo in questa sezione) che ci consente di verificare fino a 30 argomenti (uguaglianze, in questo caso); quindi la formula recita: SE B2 è UGUALE a "mele", E C2 è UGUALE a "Italia", allora in F2 ci metti 1, altrimenti ci metti 0 ; a questo punto per ottenere la nostra risposta (desideriamo contare quanto prodotto "mele" proviene dall'Italia) è sufficiente in un'altra cella inserire la funzione SOMMA(F2:F6) per ottenere 2

Abbiamo in definitiva, ottenuta la nostra funzione CONTA.SE a due criteri, giusto? Non occorre aggiungere che se i criteri da verificare fossero più di due, basterà inserire nella funzione E() tante uguaglianze (o divergenze) quanti sono i criteri.

Volendo però risolvere con una sola funzione, ed usare una sola cella, potremo usare la seguente formula matriciale (ricordo che per inserire una matriciale si devono premere contemporaneamente i tasti CTRL + MAIUSCOLE + INVIO al posto del solo INVIO):

  • {=SOMMA(SE((B2:B6="mele")*(C2:C6="Italia");1;0))}   e otterremo 2 come risultato

Le parentesi graffe che identificano una formula come matriciale, NON si devono scrivere (o copiare), ma si ottengono SOLO con la sequenza dei tasti sopra descritta.

La formula funziona così: si sommano (SOMMA) tanti 1 quante sono le condizioni (SE) riscontrate vere dal confronto dei valori contenuti negli indici dei due intervalli (indici di matrice); si cerca l'abbinamento "mele" - "Italia", e SE trovato, si restituisce 1 che viene sommato.

 

Buon lavoro.

 

prelevato sul sito www.ennius.altervista.org