Se avete bisogno di contare i valori di testo univoci presenti in un intervallo, è possibile utilizzare una formula che utilizzi le seguenti funzioni: FREQUENZA, CONFRONTA, RIF.RIGA e MATR.SOMMA.PRODOTTO.
Oppure è anche possibile utilizzare CONTA.SE, come spiegato in un recente articolo, anche se, a livello prestazionale, la soluzione presentata qui è molto più efficiente.
Supponete di avere un elenco con i nomi dei dipendenti con le ore con le ore che hanno prestato lavorando sul “Progetto X”, e volete sapere quanti dipendenti hanno lavorato su quel progetto. Guardando i dati, potete vedere che i nomi degli stessi dipendenti appaiono più di una volta, però quello che volete è il conteggio dei nomi univoci.
I nomi dei dipendenti appaiono nell’intervallo B3:B12. Per ottenere il conteggio dei nomi univoci, è possibile utilizzare la seguente formula:
=MATR.SOMMA.PRODOTTO(--(FREQUENZA(CONFRONTA(B3:B12;B3:B12;0);RIF.RIGA(B3:B12)-RIF.RIGA(B3)+1)>0))
Come funziona questa formula
Questa formula è più complicata di come potrebbe essere una formula simile che utilizzi FREQUENZA per contare valori numerici univoci, perché FREQUENZA non funziona con valori non numerici. Quindi, una parte della formula è esclusivamente necessaria per trasformare i dati non numerici in dati numerici che FREQUENZA possa gestire.
Iniziando ad analizzarla dall’interno, troviamo la funzione CONFRONTA viene utilizzata per ottenere la posizione di ogni elemento che appare nei dati. Poiché CONFRONTA restituisce solo la posizione della “prima corrispondenza”, i valori che compaiono più di una volta nei dati restituiscono lo stesso numero.
Siccome CONFRONTA riceve una matrice di valori come argomento valore (il primo argomento della funzione), restituirà anche una matrice di posizioni. Queste vengono utilizzate per alimentare la funzione FREQUENZA nell’argomento matrice_dati.
{1.1.1.4.4.6.6.6.9.9}
L’argomento matrice_classi della funzione FREQUENZA è invece costruito con questa parte della formula:
RIF.RIGA(B3:B12)-RIF.RIGA(B3)+1
che utilizza il numero di riga di ogni voce nei dati e il numero di riga della prima voce dei dati per costruire una matrice sequenziale come la seguente:
{1.2.3.4.5.6.7.8.9.10}
Il risultato è che FREQUENZA restituisce una matrice di valori che indica il conteggio di ogni valore che appare nella matrice di dati. FREQUENZA ha una funzione speciale che restituisce automaticamente “0” per tutti i numeri che compaiono più di una volta nella matrice di dati, quindi la matrice di ritorno si presenta così:
{3.0.0.2.0.3.0.0.2.0.0}
Successivamente, ciascuno di questi valori è convertito in VERO o FALSO dalla costruzione “>0”, e quindi a 1 o 0 tramite il doppio unario (doppio trattino). Ciò avviene perché MATR.SOMMA.PRODOTTO ha bisogno di valori numerici, non può funzionare direttamente con testo o valori logici.
All’interno della funzione MATR.SOMMA.PRODOTTO la matrice finale si presenta così:
{1.0.0.1.0.1.0.0.1.0.0}
Infine, MATR.SOMMA.PRODOTTO somma semplicemente questi valori e restituisce il totale, che in questo caso è 4.
Gestire le celle vuote nell’intervallo
Se una delle celle nell’intervallo fosse vuota, e si desiderasse utilizzare FREQUENZA invece di CONTA.SE, avrete bisogno di utilizzare una formula più complessa che includa la funzione SE:
=SOMMA(SE(FREQUENZA(SE(B3:B12<>""; CONFRONTA(B3:B12;B3:B12;0));RIF.RIGA(B3:B12)-RIF.RIGA(B3)+1);1))
Poiché la porzione della formula della funzione SE contiene una matrice, la formula diventa automaticamente una formula matriciale che richiede CTRL+Maiusc+Invio per essere inserita. Questo è il motivo per cui MATR.SOMMA.PRODOTTO è stata sostituita con SOMMA.
Il motivo che ci ha obbligato ad inserire un costrutto SE è che CONFRONTA restituirebbe un errore #N/D se il suo argomento valore contenesse valori vuoti. Testando la presenza di valori vuoti con “dati <> “”“, e inserendo CONFRONTA come valore se vero, la matrice risultante conterrà numeri combinati con FALSO:
{1.1.FALSO.4.FALSO.6.6.6.9.9}
che viene fornito alla funzione FREQUENZA come matrice_dati. FREQUENZA quindi restituirà una matrice come questa:
{2.0.0.1.0.3.0.0.2.0.0}
Gli elementi di questa matrice vengono convertiti con 1 o FALSO con la funzione SE esterna. Il risultato è così:
{1.FALSO.FALSO.1.FALSO.1.FALSO.FALSO.1.FALSO.FALSO}
La funzione SOMMA addiziona gli 1 e restituisce 4.
Utilizzare CONTA.SE invece di FREQUENZA per contare valori univoci
Un altro modo per contare valori numerici univoci è usare CONTA.SE anziché FREQUENZA. Si tratta di una formula molto più semplice, ma fate attenzione che utilizzando CONTA.SE su insiemi di dati più grandi, contare valori univoci può causare problemi di prestazioni. La formula basata su FREQUENZA, sebbene più complessa, riesce ad effettuare i calcoli molto più velocemente.
Questo articolo è basato su questa pagina da Exceljet.net
