Questo tutorial mostra come usare ileccel CONTA.SE e CONTA.SE Funzionezioni in Excel per contare i dati che soddisfano determinati criteri.
Panoramica della funzione CONTA.SE
Puoi utilizzare la funzione CONTA.SE in Excel per contare le celle che contengono un valore specifico, contare le celle che sono maggiori o uguali a un valore, ecc.
(Notare come appaiono gli input della formula)
Sintassi e argomenti della funzione CONTA.SE:
=CONTA.SE (intervallo, criteri)
gamma - L'intervallo di celle da contare.
criteri - I criteri che controllano quali celle devono essere contate.
Che cos'è la funzione CONTA.SE?
La funzione CONTA.SE è una delle funzioni meno recenti utilizzate nei fogli di calcolo. In parole povere, è ottimo per scansionare un intervallo e dirti quante celle soddisfano quella condizione. Vedremo come funziona la funzione con testo, numeri e date; così come alcune delle altre situazioni che potrebbero verificarsi.
Esempio di base
Iniziamo guardando questo elenco di elementi casuali. Abbiamo alcuni numeri, celle vuote e alcune stringhe di testo.
Se vuoi sapere quanti elementi corrispondono esattamente ai criteri, puoi specificare cosa vuoi cercare come secondo argomento. Un esempio di questa formula potrebbe essere simile
=CONTA.SE(A2:A9, "Mela")
Questa formula restituirebbe il numero 3, poiché ci sono 3 celle nel nostro intervallo che soddisfano tali criteri. In alternativa, possiamo usare un riferimento di cella invece di codificare un valore. Se scrivessimo "Apple" nella cella G2, potremmo cambiare la formula in
=CONTA.SE(A2:A9, G2)
Quando si tratta di numeri, è importante distinguere tra numeri e numeri che sono stati memorizzati come testo. In genere, non si mettono le virgolette attorno ai numeri quando si scrivono le formule. Quindi, per scrivere una formula che controlli il numero 5, dovresti scrivere
=CONTA.SE(A2:A9, 5)
Infine, potremmo anche verificare la presenza di celle vuote utilizzando una stringa di lunghezza zero. Scriveremmo quella formula come
=CONTA.SE(A2:A9, "")
Nota: questa formula conterà sia le celle veramente vuote, sia quelle vuote come risultato di una formula, come una funzione SE.
Partite parziali
La funzione CONTA.SE supporta l'uso di caratteri jolly, "*" o "?", nei criteri. Diamo un'occhiata a questo elenco di gustosi prodotti da forno:
Per trovare tutti gli elementi che iniziano con Apple, potremmo scrivere “Apple*”. Quindi, per ottenere una risposta di 3, la nostra formula in D2 è
=CONTA.SE(A2:A5, "Mela*")
Nota: La funzione CONTA.SE non fa distinzione tra maiuscole e minuscole, quindi puoi anche scrivere "mela*" se lo desideri.
Tornando ai nostri prodotti da forno, potremmo anche voler scoprire quante torte abbiamo nella nostra lista. Possiamo trovarlo inserendo il carattere jolly all'inizio del nostro termine di ricerca e scrivere
=CONTA.SE(A2:A5, "*torta")
Questa formula dà il risultato di 2.
Possiamo anche utilizzare i caratteri jolly per verificare la presenza di celle con testo. Torniamo al nostro elenco di dati originale.
Per contare il numero di celle che hanno almeno del testo, quindi non contando numeri o celle vuote, possiamo scrivere
=CONTA.SE(A2:A9, "*")
Puoi vedere che la nostra formula restituisce correttamente un risultato di 4.
Operatori di confronto in CONTA.SE
Quando abbiamo scritto i criteri finora, abbiamo insinuato che il nostro operatore di confronto è "=". In effetti, avremmo potuto scrivere questo:
=CONTA.SE(A2:A9, "=Mela")
È un carattere in più da scrivere, quindi di solito viene omesso. Tuttavia, ciò significa che puoi utilizzare gli altri operatori come maggiore di, minore di o non uguale a. Diamo un'occhiata a questo elenco di età registrate:
Se volessimo sapere quanti bambini hanno almeno 5 anni, possiamo scrivere un confronto "maggiore o uguale a" in questo modo:
=CONTA.SE(A2:A8, ">=5")
Nota: L'operatore di confronto viene sempre fornito come stringa di testo e quindi deve essere racchiuso tra virgolette.
Allo stesso modo, puoi anche controllare gli elementi che sono inferiori a un determinato valore. Se abbiamo bisogno di scoprire quanti sono meno di 8, possiamo scrivere
=CONTA.SE(A2:A8, "<8")
Questo ci dà il risultato desiderato di 5. Ora immaginiamo che tutti i bambini di 6 anni stiano andando in gita. Quanti bambini rimarranno? Possiamo capirlo usando un confronto "non uguale a" come questo:
=CONTA.SE(A2:A8, "6")
Ora possiamo vedere rapidamente che abbiamo 6 bambini che non hanno 6 anni.
In questi esempi di confronto finora, abbiamo codificato a fondo i valori che volevamo. Puoi anche usare un riferimento di cella. Il trucco è che devi concatenare l'operatore di confronto con il riferimento di cella. Diciamo che mettiamo il numero 7 nella cella C2 e vogliamo che la nostra formula in D2 mostri quanti bambini hanno meno di 7 anni.
La nostra formula in D2 deve assomigliare a questa:
=CONTA.SE(A2:A8, "<"&C2)
Nota: Prestare particolare attenzione quando si scrivono queste formule se è necessario inserire un elemento tra virgolette o all'esterno. Gli operatori sono sempre all'interno delle virgolette, i riferimenti di cella sono sempre all'esterno delle virgolette. I numeri sono fuori se stai facendo una corrispondenza esatta, ma dentro se stai facendo un operatore di confronto.
Lavorare con le date
Abbiamo visto come puoi dare un testo o un numero come criterio, ma che dire quando dobbiamo lavorare con le date? Ecco un rapido elenco di esempio con cui possiamo lavorare:
Per contare quante date ci sono dopo il 4 maggio, dobbiamo fare attenzione. I computer memorizzano le date come numeri, quindi dobbiamo assicurarci che il computer utilizzi il numero corretto. Se scrivessimo questa formula, otterremmo il risultato corretto?
=CONTA.SE(A2:A9, "
La risposta è "forse". Poiché abbiamo omesso l'anno dai nostri criteri, il computer supporrà che intendiamo l'anno in corso. Se tutte le date con cui stiamo lavorando sono per l'anno in corso, otterremo la risposta corretta. Se ci sono alcune date che sono nel futuro, tuttavia, otterremmo la risposta sbagliata. Inoltre, una volta iniziato l'anno successivo, questa formula restituirà un risultato diverso. In quanto tale, questa sintassi dovrebbe probabilmente essere evitata.
Poiché può essere difficile scrivere le date correttamente all'interno di una formula, è consigliabile scrivere la data che si desidera utilizzare in una cella, quindi è possibile utilizzare quel riferimento di cella all'interno della formula CONTA.SE. Quindi, scriviamo la data del 7 maggio 2020 nella cella C2, quindi possiamo inserire la nostra formula in C4.
La formula in C4 è
=CONTA.SE(A2:A9, "<"&C2)
Ora sappiamo che il risultato di 7 è corretto e la risposta non cambierà inaspettatamente se apriremo questo foglio di calcolo in futuro.
Prima di lasciare questa sezione, è comune utilizzare la funzione OGGI quando si lavora con le date. Possiamo usarlo proprio come faremmo con un riferimento di cella. Ad esempio, potremmo cambiare la formula precedente in questo modo:
=CONTA.SE(A2:A9, "<"&OGGI())
Ora la nostra formula si aggiornerà comunque con l'avanzare del tempo reale e avremo un conteggio degli elementi inferiore a quello attuale.
Criteri multipli e COUNTIFS
La funzione CONTA.SE originale ha ottenuto un miglioramento nel 2007 quando è uscito CONTA.SE. La sintassi tra i due è molto simile, con quest'ultimo che consente di fornire intervalli e criteri aggiuntivi. Puoi facilmente utilizzare CONTA.SE in qualsiasi situazione in cui CONTA.SE esiste. È solo una buona idea sapere che esistono entrambe le funzioni.
Diamo un'occhiata a questa tabella di dati:
Per scoprire quante persone ci sono nei livelli retributivi da 1 a 2, potresti scrivere una somma delle funzioni CONTA.SE come questa:
=CONTA.SE(B2:B7, ">=1")-CONTA.SE(B2:B7, ">2")
Questa formula funzionerà, poiché trovi tutto ciò che è superiore a 1, ma poi sottrai il numero di record che si trovano oltre il punto di interruzione. In alternativa, puoi usare COUNTIFS in questo modo:
=CONTA.SE(B2:B7, ">=1", B2:B7, "<=2")
Quest'ultimo è più intuitivo da leggere, quindi potresti voler usare quel percorso. Inoltre, COUNTIFS è più potente quando è necessario considerare più colonne. Diciamo che vogliamo sapere quante persone ci sono nella gestione e nel livello retributivo 1. Non puoi farlo solo con un CONTA.SE; dovresti scrivere
=CONTA.SE(A2:A7, "Gestione", B2:B7, 1)
Questa formula ti darebbe il risultato corretto di 2. Prima di lasciare questa sezione, consideriamo una logica di tipo Or. E se volessimo scoprire quante persone ci sono nel Management o? Dovresti aggiungere alcuni COUNTIFS insieme, ma ci sono due modi per farlo. Il modo più semplice è scriverlo in questo modo:
=COUNTIF(A2:A7, "HR")+COUNTIF(A2:A7, "Management")
Puoi anche utilizzare un array e scrivere questa formula di array:
=SOMMA(CONTA.SE(A2:A7, {"HR", "Gestione"}))
Nota: Le formule di matrice devono essere confermate utilizzando "Ctrl+Shift+Invio" e non solo "Invio".
Come funzionerà questa formula, vedrà che hai fornito un array come input. Calcolerà quindi il risultato di due diverse funzioni CONTA.SE e le memorizzerà in una matrice. La funzione SUM aggiungerà quindi tutti i risultati nel nostro array per creare un singolo output. Pertanto, la nostra formula sarà valutata in questo modo:
=SOMMA(CONTA.SE(A2:A7, {"HR", "Gestione"})) =SOMMA({2, 3}) =5
Conta valori univoci
Ora che abbiamo visto come utilizzare un array con la funzione CONTA.SE, possiamo fare un ulteriore passo avanti per aiutarci a contare quanti valori univoci sono presenti in un intervallo. Per prima cosa, diamo un'occhiata di nuovo al nostro elenco di dipartimenti.
=SOMMA(1/CONTA.SE(A2:A7,A2:A7))
Possiamo vedere che ci sono 6 celle di dati, ma ci sono solo 3 elementi diversi. Per far funzionare la matematica, avremmo bisogno che ogni elemento valga 1/N, dove N è il numero di volte in cui un elemento viene ripetuto. Ad esempio, se ogni HR valeva solo 1/2, quando li sommavi otterresti un conteggio di 1, per 1 valore univoco.
Torna al nostro CONTA.SE, progettato per calcolare quante volte un articolo appare in un intervallo. In D2, scriveremo la formula dell'array
=SOMMA(1/CONTA.SE(A2:A7, A2:A7))
Come funzionerà questa formula, è per ogni cella nell'intervallo di A2: A7, controllerà per vedere quante volte appare. Con il nostro campione, questo produrrà una serie di
{2, 2, 3, 3, 3, 1}
Quindi, trasformiamo tutti quei numeri in frazioni facendo qualche divisione. Ora il nostro array sembra
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Quando sommiamo tutti questi, otteniamo il risultato desiderato di 3.
Conteggio con due o più condizioni - La funzione Conteggi
Finora abbiamo lavorato solo con la funzione CONTA.SE. La funzione CONTA.SE può gestire solo un criterio alla volta. Per CONTA.SE con più criteri è necessario utilizzare la funzione CONTA.SE. CONTA.SE si comporta esattamente come CONTA.SE. Basta aggiungere criteri extra. Diamo un'occhiata al seguente esempio.
=CONTA.SE(B2:B7;"=130")
CONTA.SE e CONTA.SE in Fogli Google
La funzione CONTA.SE e CONTA.SE funziona esattamente allo stesso modo in Fogli Google come in Excel:
