In questo tutorial imparerai come contare i valori univoci in Excel utilizzando le formule (funzioni COUNTIF e SUMPRODUCT).
Come contare i valori univoci in Excel
Supponiamo di avere un set di dati come mostrato di seguito:
Ai fini di questo tutorial, chiamerò l'intervallo A2: A10 come NAMES. In futuro utilizzeremo questo intervallo denominato nelle formule.
Vedi anche: Come creare intervalli denominati in Excel.
In questo set di dati, c'è una ripetizione nell'intervallo NAMES. Per ottenere il conteggio dei nomi univoci da questo set di dati (A2:A10), possiamo utilizzare una combinazione di funzioni CONTA.SE e SOMMA.PRODOTTO come mostrato di seguito:
=SUMPRODOTTO(1/COUNTIF(NOMI,NOMI))
Come funziona questa formula?
Analizziamo questa formula per capire meglio:
- CONTA.SE(NOMI,NOMI)
- Questa parte della formula restituisce un array. Nell'esempio precedente, sarebbe {2;2;3;1;3;1;2;3;2}. I numeri qui indicano quante volte si verifica un valore nell'intervallo di celle specificato.
Ad esempio, il nome è Bob, che compare due volte nell'elenco, quindi restituirebbe il numero 2 per Bob. Allo stesso modo, Steve si verifica tre volte e quindi viene restituito 3 per Steve.
- Questa parte della formula restituisce un array. Nell'esempio precedente, sarebbe {2;2;3;1;3;1;2;3;2}. I numeri qui indicano quante volte si verifica un valore nell'intervallo di celle specificato.
- 1/COUNTIF(NOMI,NOMI)
- Questa parte della formula restituirebbe una matrice - {0,5;0,5;0,333333333333333;1;0,333333333333333;1;0,5;0,333333333333333;0,5}
Poiché abbiamo diviso 1 per l'array, restituisce questo array.
Ad esempio, il primo elemento dell'array restituito sopra era 2. Quando 1 è diviso per 2, restituisce 0,5.
- Questa parte della formula restituirebbe una matrice - {0,5;0,5;0,333333333333333;1;0,333333333333333;1;0,5;0,333333333333333;0,5}
- SUMPRODUCT(1/COUNTIF(NAMES,NAMES))
- SUMPRODUCT aggiunge semplicemente tutti questi numeri. Nota che se Bob compare due volte nell'elenco, l'array sopra restituisce .5 ovunque il nome di Bob sia apparso nell'elenco. Allo stesso modo, poiché Steve appare tre volte nell'elenco, l'array restituisce .3333333 ogni volta che appare il nome di Steve. Quando aggiungiamo i numeri per ogni nome, restituisce sempre 1. E se aggiungiamo tutti i numeri, restituisce il conteggio totale dei nomi univoci nell'elenco.
Questa formula funziona correttamente finché non hai celle vuote nell'intervallo. Ma se hai celle vuote, restituirebbe un #DIV/0! errore.
Come gestire le celle BLANK?
Per prima cosa capiamo perché restituisce un errore quando c'è una cella vuota nell'intervallo. Supponiamo di avere il set di dati come mostrato di seguito (con la cella A3 vuota):
Ora se usiamo la stessa formula che abbiamo usato sopra, la parte CONTA.SE della formula restituisce un array {2;0;3;1;3;1;2;3;1}. Poiché non c'è testo nella cella A3, il suo conteggio viene restituito come 0.
E poiché stiamo dividendo 1 per l'intero array, restituisce un #DIV/0! errore.
Per gestire questo errore di divisione in caso di celle vuote, utilizzare la formula seguente:
=SOMMA.PRODOTTO((1/CONTA.SE(NOMI,NOMI&”")))
Una modifica che abbiamo apportato a questa formula è la parte dei criteri della funzione CONTA.SE. Abbiamo usato NAMES&”” invece di NAMES. In questo modo, la formula restituiva il conteggio delle celle vuote (in precedenza restituiva 0 dove c'era una cella vuota).
NOTA: questa formula conterebbe le celle vuote come un valore univoco e lo restituirebbe nel risultato.
Nell'esempio sopra, il risultato dovrebbe essere 5, ma restituisce 6 poiché la cella vuota viene conteggiata come uno dei valori univoci.
Ecco la formula che si occupa delle celle vuote e non la conta nel risultato finale:
=SOMMA.PRODOTTO((NOMI””)/CONTA.SE(NOMI,NOMI&””))
In questa formula, invece di 1 come numeratore, abbiamo usato NOMI””. Questo restituisce un array di TRUE e FALSE. Restituisce FALSE ogni volta che è presente una cella vuota. Poiché TRUE equivale a 1 e FALSE equivale a 0 nei calcoli, le celle vuote non vengono conteggiate poiché il numeratore è 0 (FALSE).
Ora che abbiamo pronto lo scheletro di base della formula, possiamo fare un ulteriore passo avanti e contare diversi tipi di dati.
Come contare i valori univoci in Excel che sono testo
Useremo lo stesso concetto discusso sopra per creare la formula che conterà solo i valori di testo che sono univoci.
Ecco la formula che conterà i valori di testo univoci in Excel:
=SOMMA.PRODOTTO((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&”")))
Tutto quello che abbiamo fatto è usare la formula ISTEXT(NAMES) come numeratore. Restituisce VERO quando la cella contiene testo e FALSO in caso contrario. Non conterà le celle vuote, ma conterà le celle che hanno una stringa vuota ("").
Come contare i valori univoci in Excel che sono numerici
Ecco la formula che conterà i valori numerici univoci in Excel
=SOMMA.PRODOTTO((VAL.NUMERO(NOMI))/CONTA.SE(NOMI,NOMI&""))
Qui, stiamo usando ISNUMBER(NAMES) come numeratore. Restituisce VERO quando la cella contiene un tipo di dati numerico e FALSO in caso contrario. Non conta le celle vuote.
