Formula generica
=SUMPRODUCT(1/COUNTIF(data,data))
Sommario
Per contare il numero di valori univoci in un intervallo di celle, puoi utilizzare una formula basata sulle funzioni COUNTIF e SUMPRODUCT. Nell'esempio di spettacolo, la formula in F6 è:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Spiegazione
Lavorando dall'interno verso l'esterno, CONTA.SE è configurato su valori nell'intervallo B5: B14, utilizzando tutti questi stessi valori come criteri:
COUNTIF(B5:B14,B5:B14)
Poiché forniamo 10 valori per i criteri, otteniamo un array con 10 risultati come questo:
(3;3;3;2;2;3;3;3;2;2)
Ogni numero rappresenta un conteggio: "Jim" appare 3 volte, "Sue" 2 volte e così via.
Questo array è configurato come un divisore con 1 come numeratore. Dopo la divisione, otteniamo un altro array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Tutti i valori che si verificano in una sola volta nell'intervallo verranno visualizzati come 1, ma i valori che si verificano più volte verranno visualizzati come valori frazionari che corrispondono al multiplo. (ovvero un valore che appare 4 volte nei dati genererà 4 valori = 0,25).
Infine, la funzione SUMPRODUCT somma tutti i valori nella matrice e restituisce il risultato.
Gestire le celle vuote
Un modo per gestire celle vuote o vuote è regolare la formula come segue:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Concatenando una stringa vuota ("") ai dati, impediamo che gli zeri finiscano nell'array creato da CONTA.SE quando ci sono celle vuote nei dati. Questo è importante, perché uno zero nel divisore farà sì che la formula generi un errore # DIV / 0. Funziona perché l'utilizzo di una stringa vuota ("") per i criteri conterà le celle vuote.
Tuttavia, sebbene questa versione della formula non genererà un errore # DIV / 0 quando con celle vuote, includerà celle vuote nel conteggio. Se vuoi escludere le celle vuote dal conteggio, usa:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Ciò ha l'effetto di annullare il conteggio delle celle vuote rendendo il numeratore zero per i conteggi associati.
Prestazioni lente?
Questa è una formula fresca ed elegante, ma calcola molto più lentamente delle formule che utilizzano la FREQUENZA per contare valori univoci. Per set di dati più grandi, potresti voler passare a una formula basata sulla funzione FREQUENZA. Ecco una formula per i valori numerici e una per i valori di testo.
