- Introduzione alle funzioni CONTA.SE e CONTA.SE di Excel
- Utilizzo dei criteri NUMBER nelle funzioni CONTA.SE di Excel
- Utilizzo dei criteri di TESTO nelle funzioni di Excel
- Utilizzo dei criteri DATA nelle funzioni CONTA.SE e CONTA.SE di Excel
- Utilizzo dei CARATTERI WILDCARD nei criteri nelle funzioni CONTA.SE e CONTA.SE
Excel ha molte funzioni in cui un utente deve specificare uno o più criteri per ottenere il risultato. Ad esempio, se desideri contare le celle in base a più criteri, puoi utilizzare le funzioni CONTA.SE o CONTA.SE in Excel.
Questo tutorial copre vari modi di utilizzare uno o più criteri nella funzione CONTA.SE e CONTA.SE in Excel.
Sebbene in questo tutorial mi concentrerò principalmente sulle funzioni CONTA.SE e CONTA.SE, tutti questi esempi possono essere utilizzati anche in altre funzioni di Excel che accettano più criteri come input (come SOMMA.SE, SOMMA.SE, MEDIA.SE e MEDIA.SE).
Introduzione alle funzioni CONTA.SE e CONTA.SE di Excel
Diamo prima un'occhiata all'utilizzo delle funzioni CONTA.SE e CONTA.SE in Excel.
Funzione CONTA.SE di Excel (accetta criteri singoli)
La funzione CONTA.SE di Excel è più adatta per le situazioni in cui si desidera contare le celle in base a un singolo criterio. Se vuoi contare in base a più criteri, usa la funzione CONTA.PI.SE.
Sintassi
=CONTA.SE(intervallo, criteri)
Argomenti di input
- gamma - l'intervallo di celle che si desidera contare.
- criteri - i criteri che devono essere valutati rispetto all'intervallo di celle affinché una cella venga conteggiata.
Funzione CONTA.PI.SE di Excel (richiede più criteri)
La funzione CONTA.SE di Excel è più adatta per le situazioni in cui si desidera contare le celle in base a più criteri.
Sintassi
=COUNTIFS(intervallo_criteri1, criteri1, [intervallo_criteri2, criteri2]… )
Argomenti di input
- intervallo_criteri1 - L'intervallo di celle per il quale si desidera valutare in base ai criteri1.
- criteri1 - i criteri che si desidera valutare per intervallo_criteri1 per determinare quali celle contare.
- [campo_criteri2] - L'intervallo di celle per il quale si desidera valutare in base ai criteri2.
- [criteri2] - i criteri che si desidera valutare per intervallo_criteri2 per determinare quali celle contare.
Ora diamo un'occhiata ad alcuni esempi di utilizzo di più criteri nelle funzioni CONTA.SE in Excel.
Utilizzo dei criteri NUMBER nelle funzioni CONTA.SE di Excel
#1 Conta le celle quando i criteri sono UGUALI a un valore
Per ottenere il conteggio delle celle in cui l'argomento dei criteri è uguale a un valore specificato, è possibile immettere direttamente i criteri o utilizzare il riferimento di cella che contiene i criteri.
Di seguito è riportato un esempio in cui contiamo le celle che contengono il numero 9 (il che significa che l'argomento dei criteri è uguale a 9). Ecco la formula:
=CONTA.SE($B$2:$B$11,D3)
Nell'esempio sopra (nella foto), i criteri sono nella cella D3. Puoi anche inserire i criteri direttamente nella formula. Ad esempio, puoi anche usare:
=CONTA.SE($B$2:$B$11,9)
#2 Conta le celle quando i criteri sono MAGGIORI DI un valore
Per ottenere il conteggio delle celle con un valore maggiore di un valore specificato, utilizziamo l'operatore maggiore di (">"). Potremmo usarlo direttamente nella formula o usare un riferimento di cella che ha i criteri.
Ogni volta che utilizziamo un operatore nei criteri in Excel, dobbiamo inserirlo tra virgolette. Ad esempio, se il criterio è maggiore di 10, allora dobbiamo inserire ">10" come criterio (vedi immagine sotto):
Ecco la formula:
=CONTA.SE($B$2:$B$11”>10″)
Puoi anche avere i criteri in una cella e utilizzare il riferimento di cella come criterio. In questo caso, NON è necessario inserire i criteri tra virgolette doppie:
=CONTA.SE($B$2:$B$11,D3)
Potrebbe anche esserci un caso in cui desideri che i criteri siano in una cella, ma non lo vuoi con l'operatore. Ad esempio, potresti volere che la cella D3 abbia il numero 10 e non >10.
In tal caso, è necessario creare un argomento di criteri che sia una combinazione di operatore e riferimento di cella (vedi foto sotto):
=CONTA.SE($B$2:$B$11”>”&D3)
NOTA: quando si combinano un operatore e un riferimento di cella, l'operatore è sempre tra virgolette. L'operatore e il riferimento di cella sono uniti da una e commerciale (&).
#3 Conta le celle quando i criteri sono INFERIORI a un valore
Per ottenere il conteggio delle celle con un valore inferiore a un valore specificato, utilizziamo l'operatore minore di ("<"). Potremmo usarlo direttamente nella formula o usare un riferimento di cella che ha i criteri.
Ogni volta che utilizziamo un operatore nei criteri in Excel, dobbiamo inserirlo tra virgolette. Ad esempio, se il criterio è che il numero deve essere inferiore a 5, allora dobbiamo inserire "<5" come criterio (vedi foto sotto):
=CONTA.SE($B$2:$B$11”,<5″)
Puoi anche avere i criteri in una cella e utilizzare il riferimento di cella come criterio. In questo caso, NON è necessario inserire i criteri tra virgolette doppie (vedi foto sotto):
=CONTA.SE($B$2:$B$11,D3)
Inoltre, potrebbe esserci un caso in cui desideri che i criteri siano in una cella, ma non lo vuoi con l'operatore. Ad esempio, potresti volere che la cella D3 abbia il numero 5 e non <5.
In tal caso, è necessario creare un argomento di criteri che sia una combinazione di operatore e riferimento di cella:
=CONTA.SE($B$2:$B$11”<“&D3)
NOTA: quando si combinano un operatore e un riferimento di cella, l'operatore è sempre tra virgolette. L'operatore e il riferimento di cella sono uniti da una e commerciale (&).
#4 Contare le celle con criteri multipli - Tra due valori
Per ottenere un conteggio dei valori tra due valori, è necessario utilizzare più criteri nella funzione CONTA.SE.
Ecco due metodi per farlo:
METODO 1: Utilizzo della funzione CONTA.PI.SE
La funzione CONTA.SE può gestire più criteri come argomenti e conta le celle solo quando tutti i criteri sono VERI. Per contare le celle con valori compresi tra due valori specificati (diciamo 5 e 10), possiamo usare la seguente funzione CONTA.SE:
=CONTA.SE($B$2:$B$11”,>5″,$B$2:$B$11”,<10″)
NOTA: La formula precedente non conta le celle che contengono 5 o 10. Se si desidera includere queste celle, utilizzare gli operatori maggiore di uguale a (>=) e minore di uguale a (<=). Ecco la formula:
=CONTA.SE($B$2:$B$11”,>=5″,$B$2:$B$11”,<=10″)
Puoi anche avere questi criteri nelle celle e utilizzare il riferimento di cella come criterio. In questo caso, NON è necessario inserire i criteri tra virgolette doppie (vedi foto sotto):
È inoltre possibile utilizzare una combinazione di riferimenti di celle e operatori (dove l'operatore viene immesso direttamente nella formula). Quando si combinano un operatore e un riferimento di cella, l'operatore è sempre tra virgolette. L'operatore e il riferimento di cella sono uniti da una e commerciale (&).
METODO 2: Utilizzo di due funzioni CONTA.SE
Se disponi di più criteri, puoi utilizzare CONTA.SE o creare una combinazione di funzioni CONTA.SE. Anche la formula seguente farebbe la stessa cosa:
=CONTA.SE($B$2:$B$11”,>5″)-CONTA.SE($B$2:$B$11”,>10″)
Nella formula sopra, troviamo prima il numero di celle che hanno un valore maggiore di 5 e sottraiamo il conteggio delle celle con un valore maggiore di 10. Questo ci darebbe il risultato come 5 (che è il numero di celle che hanno valori maggiori di 5 e minori di 10).
Se vuoi che la formula includa sia 5 che 10, usa invece la formula seguente:
=COUNTIF($B$2:$B$11”>=5″)-COUNTIF($B$2:$B$11”>10″)
Se vuoi che la formula escluda sia "5" che "10" dal conteggio, usa la seguente formula:
=COUNTIF($B$2:$B$11”>=5″)-COUNTIF($B$2:$B$11”>10″)-COUNTIF($B$2:$B$11,10)
È possibile avere questi criteri nelle celle e utilizzare i riferimenti di cella oppure è possibile utilizzare una combinazione di operatori e riferimenti di cella.
Utilizzo dei criteri di TESTO nelle funzioni di Excel
#1 Conta le celle quando i criteri sono UGUALI a un testo specificato
Per contare le celle che contengono una corrispondenza esatta del testo specificato, possiamo semplicemente usare quel testo come criterio. Ad esempio, nel set di dati (mostrato sotto nella foto), se voglio contare tutte le celle con il nome Joe, posso usare la formula seguente:
=CONTA.SE($B$2:$B$11”Giovane”)
Poiché si tratta di una stringa di testo, devo inserire i criteri di testo tra virgolette.
Puoi anche avere i criteri in una cella e quindi utilizzare quel riferimento di cella (come mostrato di seguito):
=CONTA.SE($B$2:$B$11,E3)
NOTA: è possibile ottenere risultati errati se sono presenti spazi iniziali/finali nei criteri o nell'intervallo di criteri. Assicurati di pulire i dati prima di utilizzare queste formule.
#2 Conta le celle quando i criteri NON sono UGUALI a un testo specificato
Simile a quello che abbiamo visto nell'esempio sopra, puoi anche contare le celle che non contengono un testo specificato. Per fare ciò, dobbiamo usare l'operatore non uguale a ().
Supponiamo di voler contare tutte le celle che non contengono il nome JOE, ecco la formula che lo farà:
=CONTA.SE($B$2:$B$11”Giovane”)
Puoi anche avere i criteri in una cella e utilizzare il riferimento di cella come criterio. In questo caso, NON è necessario inserire i criteri tra virgolette doppie (vedi foto sotto):
=CONTA.SE($B$2:$B$11,E3)
Potrebbe anche esserci un caso in cui desideri che i criteri siano in una cella ma non lo vuoi con l'operatore. Ad esempio, potresti volere che la cella D3 abbia il nome Joe e non Joe.
In tal caso, è necessario creare un argomento di criteri che sia una combinazione di operatore e riferimento di cella (vedi foto sotto):
=CONTA.SE($B$2:$B$11,””&E3)
Quando si combinano un operatore e un riferimento di cella, l'operatore è sempre tra virgolette. L'operatore e il riferimento di cella sono uniti da una e commerciale (&).
Utilizzo dei criteri DATA nelle funzioni CONTA.SE e CONTA.SE di Excel
Excel memorizza la data e l'ora come numeri. Quindi possiamo usarlo nello stesso modo in cui usiamo i numeri.
#1 Conta le celle quando i criteri sono UGUALI a una data specificata
Per ottenere il conteggio delle celle che contengono la data specificata, utilizzeremmo l'operatore uguale a (=) insieme alla data.
Per utilizzare la data, consiglio di utilizzare la funzione DATE, poiché elimina qualsiasi possibilità di errore nel valore della data. Quindi, ad esempio, se voglio utilizzare la data 1 settembre 2015, posso utilizzare la funzione DATA come mostrato di seguito:
=DATA(2015,9,1)
Questa formula restituirebbe la stessa data nonostante le differenze regionali. Ad esempio, 01-09-2015 sarebbe il 1° settembre 2015 secondo la sintassi della data degli Stati Uniti e il 9 gennaio 2015 secondo la sintassi della data del Regno Unito. Tuttavia, questa formula ritornerebbe sempre il 1 settembre 2105.
Ecco la formula per contare il numero di celle che contengono la data 02-09-2015:
=CONTA.SE($A$2:$A$11,DATA(2015,9,2))
#2 Conta le celle quando i criteri sono PRIMA o DOPO a una data specificata
Per contare le celle che contengono la data prima o dopo una data specificata, possiamo usare gli operatori minore/maggiore di.
Ad esempio, se voglio contare tutte le celle che contengono una data successiva al 02 settembre 2015, posso utilizzare la formula:
=CONTA.SE($A$2:$A$11”>”&DATA(2015,9,2))
Allo stesso modo, puoi anche contare il numero di celle prima di una data specificata. Se si desidera includere una data nel conteggio, utilizzare l'operatore e "uguale a" insieme all'operatore "maggiore di/minore di".
Puoi anche utilizzare un riferimento di cella che contiene una data. In questo caso, è necessario combinare l'operatore (tra virgolette) con la data utilizzando una e commerciale (&).
Vedi esempio sotto:
=CONTA.SE($A$2:$A$11”>”&F3)
#3 Contare le celle con criteri multipli - Tra due date
Per ottenere un conteggio dei valori tra due valori, è necessario utilizzare più criteri nella funzione CONTA.SE.
Possiamo farlo utilizzando due metodi: una singola funzione CONTA.SE o due funzioni CONTA.SE.
METODO 1: Utilizzo della funzione CONTA.PI.SE
La funzione CONTA.SE può accettare più criteri come argomenti e conta le celle solo quando tutti i criteri sono VERI. Per contare le celle con valori tra due date specificate (diciamo il 2 settembre e il 7 settembre), possiamo utilizzare la seguente funzione CONTA.SE:
=COUNTIFS($A$2:$A$11”,>”&DATA(2015,9,2),$A$2:$A$11”<“&DATA(2015,9,7))
La formula sopra non conta le celle che contengono le date specificate. Se vuoi includere anche queste date, usa gli operatori maggiore di uguale a (>=) e minore di uguale a (<=). Ecco la formula:
=CONTA.SE($A$2:$A$11”,>=”&DATA(2015,9,2),$A$2:$A$11”<=”&DATA(2015,9,7))
Puoi anche avere le date in una cella e utilizzare il riferimento di cella come criterio. In questo caso non è possibile avere l'operatore con la data nelle celle. È necessario aggiungere manualmente gli operatori nella formula (tra virgolette) e aggiungere il riferimento di cella utilizzando una e commerciale (&). Guarda la foto qui sotto:
=CONTA.SE($A$2:$A$11”>”&F3,$A$2:$A$11”<“&G3)
METODO 2: Utilizzo delle funzioni CONTA.SE
Se disponi di più criteri, puoi utilizzare una funzione CONTA.SE oppure creare una combinazione di due funzioni CONTA.SE. Anche la formula seguente farebbe il trucco:
=CONTA.SE($A$2:$A$11”,>”&DATA(2015,9,2))-CONT.SE($A$2:$A$11”,>”&DATA(2015,9,7))
Nella formula sopra, troviamo prima il numero di celle che hanno una data dopo il 2 settembre e sottraiamo il conteggio delle celle con date dopo il 7 settembre. Questo ci darebbe il risultato come 7 (che è il numero di celle che hanno date dopo il 2 settembre e entro o prima del 7 settembre).
Se non vuoi che la formula conti sia il 2 settembre che il 7 settembre, usa invece la formula seguente:
=CONTA.SE($A$2:$A$11”,>=”&DATA(2015,9,2))-CONT.SE($A$2:$A$11”,>”&DATA(2015,9,7))
Se vuoi escludere entrambe le date dal conteggio, usa la seguente formula:
=CONTA.SE($A$2:$A$11”,>”&DATA(2015,9,2))-CONT.SE($A$2:$A$11”,>”&DATA(2015,9,7)-CONTA.SE($A $2:$A$11,DATA(2015,9,7)))
Inoltre, puoi avere le date dei criteri nelle celle e utilizzare i riferimenti delle celle (insieme agli operatori tra virgolette uniti usando la e commerciale).
Utilizzo dei CARATTERI WILDCARD nei criteri nelle funzioni CONTA.SE e CONTA.SE
Ci sono tre caratteri jolly in Excel:
- * (asterisco) - Rappresenta un numero qualsiasi di caratteri. Ad esempio, ex* potrebbe significare excel, excels, example, expert, ecc.
- ? (punto interrogativo) - Rappresenta un singolo carattere. Ad esempio, Tr?mp potrebbe significare Trump o Tramp.
- ~ (tilde) - Viene utilizzato per identificare un carattere jolly (~, *, ?) nel testo.
È possibile utilizzare la funzione CONTA.SE con caratteri jolly per contare le celle quando un'altra funzione di conteggio incorporata fallisce. Ad esempio, supponiamo di avere un set di dati come mostrato di seguito:
Ora prendiamo vari esempi:
#1 Conta le celle che contengono testo
Per contare le celle con del testo, possiamo usare il carattere jolly * (asterisco). Poiché l'asterisco rappresenta un numero qualsiasi di caratteri, conterebbe tutte le celle che contengono testo. Ecco la formula:
=CONTA.SE($C$2:$C$11,”*”)
Note: La formula sopra ignora le celle che contengono numeri, celle vuote e valori logici, ma conterebbe le celle che contengono un apostrofo (e quindi appaiono vuote) o le celle che contengono una stringa vuota (="") che potrebbe essere stata restituita come parte di una formula.
Ecco un tutorial dettagliato sulla gestione dei casi in cui è presente una stringa vuota o un apostrofo.
Ecco un tutorial dettagliato sulla gestione dei casi in cui sono presenti stringhe vuote o apostrofi.
Di seguito è riportato un video che spiega diversi scenari di conteggio delle celle contenenti testo.
#2 Conta le celle non vuote
Se stai pensando di utilizzare la funzione CONTA.VALORI, ripensaci.
Provalo e potrebbe fallire. CONTA.VALORI conterà anche una cella che contiene una stringa vuota (spesso restituita dalle formule come ="" o quando le persone inseriscono solo un apostrofo in una cella). Le celle che contengono stringhe vuote sembrano vuote ma non lo sono e quindi vengono conteggiate dalla funzione CONTA.VALORI.
CONTA.VALORI conterà anche una cella che contiene una stringa vuota (spesso restituita dalle formule come ="" o quando le persone inseriscono solo un apostrofo in una cella). Le celle che contengono stringhe vuote sembrano vuote ma non lo sono e quindi vengono conteggiate dalla funzione CONTA.VALORI.
Quindi, se usi la formula = COUNTA (A1: A11), restituisce 11, mentre dovrebbe restituire 10.
Ecco la correzione:
=COUNTIF($A$1:$A$11”?*”)+COUNT($A$1:$A$11)+SUMPRODOTTO(-ISLOGICO($A$1:$A$11))
Comprendiamo questa formula scomponendola:
- CONTA.SE($N$8:$N$18”,?*”) - Questa parte della formula restituisce 5. Ciò include qualsiasi cella che contiene un carattere di testo. UN ? rappresenta un carattere e * rappresenta un numero qualsiasi di caratteri. Quindi, la combinazione ?* nei criteri impone a Excel di contare le celle che contengono almeno un carattere di testo.
- COUNT($A$1:$A$11) - Conta tutte le celle che contengono numeri. Nell'esempio sopra, restituisce 3.
- SUMPRODUCT(-ISLOGICAL($A$1:$A$11) - Conta tutte le celle che contengono valori logici. Nell'esempio sopra, restituisce 2.
#3 Conta le celle che contengono testo specifico
Supponiamo di voler contare tutte le celle in cui il nome del rappresentante di vendita inizia con J. Ciò può essere facilmente ottenuto utilizzando un carattere jolly nella funzione CONTA.SE. Ecco la formula:
=CONTA.SE($C$2:$C$11”,J*”)
Il criterio J* specifica che il testo in una cella deve iniziare con J e può contenere qualsiasi numero di caratteri.
Se vuoi contare le celle che contengono l'alfabeto in qualsiasi punto del testo, affiancalo con un asterisco su entrambi i lati. Ad esempio, se vuoi contare le celle che contengono l'alfabeto "a", usa *a* come criterio.
Questo articolo è insolitamente lungo rispetto agli altri miei articoli. Spero ti sia piaciuto. Fatemi sapere i vostri pensieri lasciando un commento.
Potresti anche trovare utili i seguenti tutorial di Excel:
- Conta il numero di parole in Excel.
- Conta le celle in base al colore di sfondo in Excel.
- Come sommare una colonna in Excel (5 modi davvero semplici)
