Formula generica
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Sommario
Per contare valori di testo univoci in un intervallo, è possibile utilizzare una formula che utilizza diverse funzioni: FREQUENZA, CORRISPONDENZA, RIGA e SUMPRODUCT. Nell'esempio mostrato, la formula in F5 è:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
che restituisce 4, poiché ci sono 4 nomi univoci in B5: B14.
Nota: un altro modo per contare valori univoci è utilizzare la funzione CONTA.SE. Questa è una formula molto più semplice, ma può essere eseguita lentamente su set di dati di grandi dimensioni. Con Excel 365 puoi utilizzare una formula più semplice e veloce basata su UNIQUE.
Spiegazione
Questa formula è più complicata di una formula simile che utilizza FREQUENZA per contare valori numerici univoci perché FREQUENZA non funziona con valori non numerici. Di conseguenza, gran parte della formula trasforma semplicemente i dati non numerici in dati numerici che FREQUENCY può gestire.
Lavorando dall'interno verso l'esterno, la funzione CONFRONTA viene utilizzata per ottenere la posizione di ogni elemento che appare nei dati:
MATCH(B5:B14,B5:B14,0)
Il risultato di MATCH è un array come questo:
(1;1;1;4;4;6;6;6;9;9)
Poiché CONFRONTA restituisce sempre la posizione della prima corrispondenza, i valori che compaiono più di una volta nei dati restituiscono la stessa posizione. Ad esempio, poiché "Jim" compare 3 volte nell'elenco, viene visualizzato in questo array 3 volte come numero 1.
Questo array viene inserito in FREQUENCY come argomento data_array . L' argomento bins_array è costruito da questa parte della formula:
ROW(B5:B14)-ROW(B5)+1)
che costruisce un elenco sequenziale di numeri per ogni valore nei dati:
(1;2;3;4;5;6;7;8;9;10)
A questo punto, la FREQUENZA è configurata in questo modo:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENZA restituisce una matrice di numeri che indicano un conteggio per ogni numero nella matrice di dati, organizzata per bin. Quando un numero è già stato contato, FREQUENZA tornerà a zero. Questa è una caratteristica fondamentale nel funzionamento di questa formula. Il risultato di FREQUENCY è un array come questo:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Nota: FREQUENZA restituisce sempre un array con un elemento in più rispetto a bins_array .
Ora possiamo riscrivere la formula in questo modo:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Successivamente, controlliamo i valori maggiori di zero (> 0), che converte i numeri in VERO o FALSO, quindi utilizziamo un doppio negativo (-) per convertire i valori VERO e FALSO in 1 e 0. Ora abbiamo:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Infine, SUMPRODUCT somma semplicemente i numeri e restituisce il totale, che in questo caso è 4.
Gestire le celle vuote
Le celle vuote nell'intervallo faranno sì che la formula restituisca un errore # N / D. Per gestire le celle vuote, puoi utilizzare una formula di matrice più complicata che utilizza la funzione SE per filtrare i valori vuoti:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Nota: l'aggiunta di IF lo trasforma in una formula di matrice che richiede control-shift-enter.
Per ulteriori informazioni, vedere questa pagina.
Altri modi per contare valori univoci
Se hai Excel 365, puoi utilizzare la funzione UNICA per contare valori univoci con una formula molto più semplice.
Una tabella pivot è anche un ottimo modo per contare valori univoci.
