Conta.se excel

samuele agnolin
  • 21 maggio 2016
  • livello: intermedio   5 minuti
conta.se excel

Vuoi contare con excel quante volte un particolare prodotto compare in un elenco di acquisti? Quante volte un nome azienda è presente in un elenco di vendite? Il conta se è la funzione che ti serve!

Il CONTA.SE è la funzione di Excel che permette di contare quanti elementi di un dato intervallo rispondono ad un criterio specificato.

Quante volte in un intervallo è presente un particolare codice prodotto, quanti sono gli importi che superano un determinato valore, quante le celle che contengono un testo specificato, quante le date di scadenza inferiori ad oggi.

Il CONTA.SE permette quindi di contare una parte del tutto. Se pensiamo al CONTA.VALORI, funzione che conta quante celle non vuote ci sono in un intervallo, siamo un gradino più in alto in termini di risultati ottenibili e di complessità della formula.

Conta.se come si scrive

La sintassi della funzione, che appartiene alla categoria STATISTICHE, è la seguente:
=CONTA.SE(intervallo;criterio)
Il che equivale a =CONTA.SE(dove_cercare;che_cosa_cercare)

L’intervallo è l’insieme di celle su cui vogliamo andare a contare.
Il criterio è la condizione che la singola cella deve soddisfare per essere conteggiata.

Se ho un elenco in cui voglio contare quante volte compare il prodotto AX250 nell’intervallo B2:B1000 scrivo:
=CONTA.SE(B2:B1000;”AX250″)

Se voglio sapere quante sono le quantità presenti nella colonna C che superano il valore 100 scrivo:
=CONTA.SE(C2:C1000;”>100″) (da notare la particolarità dei doppi apici anche se il criterio è numerico)

Quante sono le date di scadenza superiori alla data odierna scrivo:
=CONTA.SE(C2:C1000;”>OGGI()”)

Ora che abbiamo capito l’utilizzo base della funzione proviamo a sviluppare un esempio più articolato che ci permetta di capire come sfruttare tutte le potenzialità di questo utilissimo CONTA.SE.
Di seguito puoi trovare anche esempi su come utilizzare il conta.se per contare quante celle sono diverse da vuoto e quante celle contengono un particolare valore.



Conta.se esempio

La tabella sottostante visualizza i nomi degli attori che sono stati candidati all’Oscar come miglior attore protagonista negli ultimi anni. Vogliamo contare quante volte Leonardo DiCaprio è andato in nomination, il che equivale a contare quante volte è presente nell’elenco.

elenco per conta se

Non vale dare la risposta prima di utilizzare il conta se. :-)

Per trovare il risultato scriviamo nella cella =CONTA.SE(A3:A110;”Leonardo DiCaprio”) oppure richiamiamo l’elenco delle funzioni, scegliamo la funzione conta se dal gruppo di funzioni STATISTICHE e popoliamo i due criteri.
funzioni statistiche conta se

Il risultato è 4!

risultato conta se
 
Lo screencast seguente mostra i passaggi effettuati:

conta.se excel screencast




 
Possiamo migliorare ulteriormente la nostra funzione: dato che “Leonardo DiCaprio” è scritto in una cella esterna alla tabella possiamo scrivere:
=CONTA.SE(A3:A110;C6)

riferimento cella esterna

Meglio fare riferimento sempre ad una cella esterna e non ad una appartenente all’elenco. Rischieremmo infatti di trovarci dei risultati non corretti nel caso l’elenco subisse anche solo un ordinamento, perché questo potrebbe portare un valore nella cella di riferimento diverso da quello che c’era nel momento in cui abbiamo scritto la funzione.

 

E se volessimo trovare il risultato contrario, ossia contare quante sono le celle in cui non è presente DiCaprio? La sintassi sarebbe questa:

=CONTA.SE(A3:A110;”<>Leonardo DiCaprio”), dove ‘<>’ sta per ‘diverso da’.

conta se diverso da

 

Se hai letto il mio tutorial su SOMMA.SE o se conosci già la funzione potrai notare una forte analogia tra il CONTA.SE e il SOMMA.SE: i due parametri del CONTA.SE sono esattamente uguali ai primi due parametri del SOMMA.SE. I due parametri servono infatti per individuare le righe che corrispondono al criterio (“Leonardo DiCaprio”) che abbiamo specificato.

E se avessimo bisogno di cercare una parte del testo contenuto nelle celle? Se dovessimo ad esempio trovare in un elenco di codici prodotto tutti quelli che contengono la sigla B100? Se qui sapessimo che Leonardo di Caprio è scritto a volte anceh come “L. Di Caprio” o “Di Caprio”? Dovremmo sfruttare i caratteri jolly asterisco ‘*’ o punto di domanda ‘?’.

Conta.se e caratteri jolly

Quando vogliamo cercare un testo all’interno di una stringa contenente altro testo oltre a quello che cerchiamo possiamo sfruttare il carattere asterisco.

Il carattere “*” viene interpretato da Excel come “un testo di qualsiasi lunghezza”, compresa lunghezza pari a zero.

Per contare quindi le occorrenze di Leonardo DiCaprio potremmo scrivere:
=CONTA.SE(A3:A110;”*caprio*”)
In questo modo riusciremmo a contare anche tutte le celle, anche quelle in cui il nome dell’attore è scritto in maniera abbreviata.

conta se caratteri jolly

 

E se dovessimo far riferimento ad una cella esterna alla tabella dove c’è scritto “Caprio”? La sintassi della funzione sarebbe:
=CONTA.SE(A3:A110;”*”&C6&”*”)

conta se caratteri jolly cella esterna

La sintassi sfrutta la modalità di concatena di Excel. Se vuoi qualche esempio pratico leggi il mio tutorial sul concatena.

La funzione conta se conterà quindi quante celle dell’intervallo A3:A110 contengono al loro interno la scritta “Caprio”. L’asterisco indica da zero ad un numero qualsiasi di caratteri, quindi con il conta.se excel cercherà le celle che iniziano, contengono o terminano con “Caprio” (o “caprio”, la funzione non fa differenza tra maiuscole e minuscole).



Conta.se con spazi vuoti

Il conta se con l’asterisco può essere utile anche nei casi in cui ho degli spazi vuoti finali all’interno delle celle dove devo contare. Capita a volte di lavorare su estrapolazioni effettuate da database, dove magari sono stati messi in automatico degli spazi finali.

Quanto abbiamo degli spazi scrivere il criterio seguito dall’asterisco può consentirci di risolvere la situazione:
se scriviamo
=CONTA.SE(B2:B1000;“AX250”)
contiamo tutte le celle il cui contenuto è esattamente “AX250”
mentre se scriviamo
=CONTA.SE(B2:B1000;“AX250*”)
contiamo le celle il cui contenuto è esattamente “AX250” ma anche quelle in cui AX250 è seguito da spazi

Se gli spazi fossero all’inizio della cella potremmo scrivere
=CONTA.SE(B2:B1000;“*AX250”)

Se fossero all’inizio e alla fine scriveremmo
=CONTA.SE(B2:B1000;“*AX250*”)

Abbiamo finora utilizzato criteri testual (AX250, “Leonardo DiCaprio” e “*Caprio*”) ma i criteri possono riguardare anche numeri e date. Vediamo qualche esempio.

Conta.se con criteri numerici

Come contare quanti prodotti a magazzino hanno una quantità superiore a 300? O quanti clienti hanno acquistato per un importo superiore a 10.000?

Nel criterio del conta.se è possibile specificare criteri numerici utilizzando i classici operatori maggiore, minore ed uguale. Quindi nel criterio possiamo scrivere “300” (che equivale a scrivere “=300” oppure anche 300, senza doppi apici), “<>300” (diverso da 300), “>300”, “>=300” (con il simbolo di uguale scritto sempre dopo e non prima dei simboli > e <).

Nella figura sottostante sono riportati un paio di utilizzi degli operatori in un breve elenco di prodotti a magazzino.

conta se criteri numerici

Da notare il fatto che i criteri in cui vengono usati degli operatori richiedono i doppi apici anche quando sono numerici.

Tutti i vari esempi sono stati svolti utilizzando il conta se Excel 2016, ma le osservazioni fatte in questo articolo valgono anche se utilizziamo i conta se excel 2010 o di altre versioni.



Conta se diverso da vuoto

Come fare per contare solo le celle il cui valore è diverso da vuoto? Un esempio veloce e pratico.

La funzione che in excel conta se diverso da vuoto è =CONTA.SE(A2:A6;”<>”). Nel criterio bisogna specificare il diverso (tramite minore e maggiore). Excel così valuta l’espressione come se fosse implicitamente scritto diverso da vuoto.

Attenzione al fatto che scrivere =CONTA.SE(A2:A6;”<>”) è diverso dallo scrivere =CONTA.SE(A2:A6;”*”).

conta se diverso da vuoto

La funzione =CONTA.SE(A2:A6;”*”) restituisce infatti il numero di celle contenti testo, quindi ignora celle contenenti numeri e celle vuote, come ci viene spiegato anche nella guida ufficiale della funzione.

Conta.se date

Il conta.se può valutare anche una data o un riferimento ad un data?
Sì possiamo usare le date in modi diversi:
=CONTA.SE(B2:B6;”05/05/2015″) oppure =CONTA.SE(B2:B6;”>05/05/2015″) oppure =CONTA.SE(B2:B6;F6)

Possiamo anche far riferimento alla data attuale e scrivere:
=CONTA.SE(B2:B6;”>”&OGGI())

date e conta se

 

E se dovessimo valutare un intervallo tra due date?
Il modo più semplice per valutare un range di date è utilizzare la funzione CONTA.PIU’.SE, presente dalla versione 2010, che permette di specificare più criteri. Uno dei criteri sarà >data_iniziale, il secondo sarà <data_finale.

Se volessimo però ottenere il risultato solamente con il CONTA.SE dovremmo utilizzare due conta.se facendo la differenza tra i due risultati, come mostrato nel paragrafo successivo.

Conta se compreso tra due valori

Il conta.se può restituirci il numero delle celle comprese tra due valori numerici o due date attraverso una differenza tra il conteggio del valore più alto e quello del valore più basso.

Dobbiamo quindi utilizzare un conta se excel a due condizioni, come mostrato nell’esempio seguente.

conta se compreso tra due valori

Conta.se con altre funzioni

Il conta se può essere utilizzato anche in combinazione con altre funzioni, come si è visto prima quando è stata utilizzata insieme alla funzione OGGI().

Posso contare quante celle hanno un valore superiore alla media?

=CONTA.SE(B2:B6;”>”&MEDIA(B2:B6))

conta se maggiore media

 

Posso contare tutte le celle che hanno lunghezza maggiore di 2?
Sì, potremmo usare i caratteri jolly in questo modo:
=CONTA.SE(A2:A6;”???*”)
I punti di domanda indicano che ci devono essere per forza tre caratteri/numeri, l’asterisco indica che dopo i i primi 3 ci può essere un testo con lunghezza qualsiasi, zero compreso.

conta celle lunghezza maggiore valore

 

Lo stesso risultato potrebbe essere trovato attraverso la scrittura in forma matriciale di Excel e l’utilizzo della funzione LUNGHEZZA.

La forma matriciale sarebbe
=SOMMA(SE(LUNGHEZZA(A2:A6)>2;1;0))
ricordando di dare conferma con la combinazione CTRL+SHIFT+INVIO

Il conta se può sfruttare quindi altre funzioni, ma può essere a sua volta utilizzato da altre funzioni o funzionalità?
Sì, una situazione tipica di utilizzo del conta se è quella relativa all’utilizzo della formattazione condizionale.



Formattazione condizionale e conta se

Vogliamo colorare in maniera automatica le celle il cui valore si ripete più di 2 volte all’interno di un particolare intervallo.

Se volessimo applicarlo all’esempio degli attori in nomination utilizzato all’inizio dell’articolo potremmo voler colorare le righe degli attori candidati più di 3 volte negli ultimi anni.

Il risultato deve essere il seguente:

risultato finale formattazione condizionale

Dobbiamo usare la formattazione condizionale. I passaggi saranno i seguenti

 A  Selezioniamo l’intervallo

formattazione condizionale selezione intervallo

 B  Scegliamo Formattazione condizionale – Nuova regola… – Utilizza una formula per determinare le celle da formattare

nuova regola utilizza una formula

 C  Scriviamo l’espressione =CONTA.SE($A$3:$A$92;$A3)>2

formattazione condizionale conta superiori a valore

 

Conta.se excel esempi

Casi pratici sul conta.se di excel, esempi che possono essere utili per applicare le diverse tipologie di criteri, da quelle testuali semplici quelle con caratteri jolly, da quelle numeriche a quelle che fanno uso di funzioni.

Esempi con il conta se di Excel 2016 che possono essere applicati anche con le altre versioni precedenti.

L’elenco di partenza è il seguente:

Su questo elenco proviamo a rispondere ad una serie di quesiti.

1. Qual è il numero delle donne?
Troviamo la soluzione utilizzando il criterio testuale “F”. Scriviamo =CONTA.SE(B2:B495;”F”)

2. Qual è il numero di clienti con età maggiore di 40?
Qui il criterio è numerico e l’operatore sarà maggiore uguale: =CONTA.SE(E2:E495;”>=40″)

3. Qual è il numero di clienti il cui codice inizia con ‘RTX2’?
Per questa domanda utilizziamo il carattere jolly asterisco: =CONTA.SE(A2:A495;”rtx2*”)

4. Qual è il numero di clienti di Torino?
Il criterio è testuale, scrivere “to” o “To” non fa differenza: =CONTA.SE(D2:D495;”to”)

5. Qual è il numero di clienti la cui provincia inizia con la lettera ‘T’?
Anche qui utilizziamo asterisco, tutto ciò che inizia per “t” =CONTA.SE(D2:D495;”t*”)

6. Qual è il numero di clienti il cui importo è maggiore della media degli importi?
Dobbiamo comporre il criterio con il concatena: =CONTA.SE(F2:F495;”>”&MEDIA(F2:F495))

conta se excel esempi

8 Comments

You can post comments in this post.


  • salve,
    mia figlia ha 10 anni e frequenta la 5 elementare.L’insegnante di informatica le ha assegnato un compito in cui deve creare una battaglia navale con il Conta se di Excell.
    Aiutoooooo!come si fa?
    Grazie e’ urgentissimo
    Stefania

    stefania valenza 8 mesi ago Reply


  • Complimenti davvero! un sito davvero utile!

    Jessie 5 mesi ago Reply


    • Grazie mille Jessie! A breve spero di riuscire ad aggiungere altri contenuti :-)

      samuele 5 mesi ago Reply


  • salve, complimenti per le spiegazioni molto dettagliate.
    Avrei un problema con i caratteri jolly, dentro una cella io inserisco il codice di un articolo e altri dettagli, come i pezzi acquistati, se ha preso omaggi ecc ecc, un esempio:
    [15345] 2pz + 3 omaggi
    Bene, non riesco ad uscire da questo problema, vorrei fare un conta.se che mi cerchi la corrispondenza del codice 15345 in confronto nel database su un altro foglio, e contando tutte le volte che viene scritto, so il totale delle vendite che ho eseguito su quell’ articolo.
    Le formule come questa, non sono adoperabili per me, siccome io NON POSSO scrivere a mano migliaia di codici, vorrei poter selezionare una cella, cosi poi le trascino..
    =CONTA.SE(A2:A5;”*mele*”)

    GRAZIE !!!

    CORRADO ANNI 2 mesi ago Reply


    • Ciao Corrado,

      ipotizziamo che il tuo codice si trovi su A1. La formula potrebbe essere =CONTA.SE(A2:A5;”*”&A1&”*”).

      In questo modo con il simbolo & concateniamo gli asterischi prima e dopo al codice che devi cercare.

      samuele 1 mese ago Reply


  • Buongiorno
    Sono un allenatore di una squadra di calcio giovanile.
    Ho realizzato su un foglio excel (PRESENZE ALLENAMENTI) un elenco (31 nomi) dove per segnare le presenze o le assenze utilizzo un elenco a discesa con i valori preimpostati sul Foglio 2 (P=presente, Pr= Presente con ritardo, A=Assente, Ai=assente per infortunio, Am=malattia).
    In fondo a questo elenco (PRESENZE ALLENAMENTI) ho inserito una riga di “Totale presenze” dove scrivo le presenze di quel giorno di allenamenti.
    Ho provato ad usare la funzione =CONTA.SE(intervallo;”P”) ma mi restituisce sempre il valore “0 (zero)”.
    Se provo la stessa funzione su un altro foglio EXCEL dove le celle sono riempite manualmente (senza elenco a discesa) funziona regolarmente.
    Mi sa dire, per cortesia dove sbaglio o se devo usare altre funzioni? Grazie.

    giancarlo curcio 2 mesi ago Reply


    • Ciao Giancarlo,

      io controllerei l’origine dati dell’elenco a discesa: c’è forse uno spazio dopo le varie voci?

      Se vuoi girarmi uno stralcio del file a samuele@amicoexcel.it lo guardo volentieri.

      samuele 1 mese ago Reply


Inserisci un commento